Excel/Calc INDIRECT+ АДРЕС + ROW + COLUMN чистая альтернатива

Question

Я не обычный пользователь Excel/Calc, но в настоящее время я использую LibreOffice Calc, чтобы записывать некоторые входы и выходы денег. Мои две функции ниже работают нормально, как и предполагалось, но все же я хотел бы узнать о более чистой альтернативе; особенно для первого.

Это рассчитывает ежедневный доход,

=INDIRECT(ADDRESS(ROW(),COLUMN()-2))-IF(ISNUMBER(INDIRECT(ADDRESS(ROW()-1,COLUMN()-2))),INDIRECT(ADDRESS(ROW()-1,COLUMN()-2)),0)-INDIRECT(ADDRESS(ROW(),COLUMN()-1))

и это рассчитывает чистый доход,

=SUM(INDIRECT(ADDRESS(2,COLUMN()-1)&":"&ADDRESS(ROW(),COLUMN()-1)))

Answer 1

Вы можете добиться того же самого результата намного легче с помощью стандартных ссылок на ячейки. Ваша первая формула использует только относительные ссылки. Предполагая, что формула введена в ячейку D3, она может быть записана как:

=B3-IF(ISNUMBER(B2),B2,0)-C3

Ваша вторая формула использует как абсолютные, так и относительные ссылки. На этот раз, если мы находимся в ячейке E3, это можно записать как:

=SUM(D$2:D3)

Обратите внимание на $ в D$2 . Это абсолютная ссылка, означающая, что при перетаскивании формулы вниз верхняя граница остается прежней, а нижняя граница расширяется вниз.

Answer 2

Как указывает Кайл, люди обычно просто используют относительную адресацию, и его ответ описывает, как это сделать. Вы можете видеть, насколько простыми становятся формулы.

Объяснение оригинальных формул

Примеры формул, которые вы включили в вопрос, возможно, были сделаны таким образом, чтобы подчеркнуть связь между адресами ячеек. Пока структура не меняется, этот стиль адресации фокусируется на логике физических отношений ячеек. Однако обратите внимание, что если структура электронной таблицы изменится, все сломается, потому что ссылки привязаны к исходной структуре.

Чтобы понять, что они делают, ADDRESS создает текстовое описание ссылки на ячейку. Это позволяет вам использовать вычисления, чтобы выяснить, куда должна указывать ссылка. Хорошая, короткая демонстрация здесь. INDIRECT преобразует текстовое описание ссылки на ячейку в фактическую ссылку.

Так что, если вы хотите более чистую альтернативу, самым чистым было бы просто использовать фактические ссылки на ячейки, как ответ Кайла. Если вы находитесь в ситуации, когда вам действительно нужно сосредоточиться на структуре электронных таблиц, есть еще несколько способов упростить это.

R1C1 адресация

Существует форма адресации под названием R1C1, которая выражает адреса как абсолютные или относительные строки и столбцы таким образом, чтобы их можно было вычислять и использовать напрямую, без необходимости использования функций ADDRESS, ROW и COLUMN.

Для абсолютного адреса используйте номер строки и номер столбца после меток R и C. Таким образом, A3 будет R3C1 .

Для относительной адресации оставьте номер, указывающий на текущую строку или столбец, или укажите соответствующий номер в квадратных скобках. Таким образом, ячейка в той же строке и двух столбцах слева будет обозначаться как RC[-2] .

И в Excel, и в Calc есть возможность изменить стиль адреса на R1C1, который будет применяться ко всему. Однако вы можете использовать его в определенных формулах, не делая этого, сделав его текстовой строкой и используя INDIRECT (для которого необязательный параметр стиля адресации должен быть установлен в ноль для распознавания стиля R1C1). Итак, первая формула:

=INDIRECT(ADDRESS(ROW(),COLUMN()-2))-IF(ISNUMBER(INDIRECT(ADDRESS(ROW()-1,COLUMN()-2))), 
 INDIRECT(ADDRESS(ROW()-1,COLUMN()-2)),0)-INDIRECT(ADDRESS(ROW(),COLUMN()-1))

станет:

=INDIRECT("RC[-2]",0)-IF(ISNUMBER(INDIRECT("R[-1]C[-2]",0)), 
 INDIRECT("R[-1]C[-2]",0),0)-INDIRECT("RC[-1]",0)

Вы можете видеть, что это своего рода сокращение для конструкции ADDRESS/ROW/COLUMN.

OFFSET

Типичный способ сделать это, хотя, когда вам нужно сделать относительные отношения очевидными, короче этого. Вы можете использовать функцию OFFSET. Краткая форма для ссылки на одну ячейку - OFFSET(reference,rows,columns) . Вы указываете начальную точку, а затем количество строк и столбцов от нее.

В ваших примерах формула определяет текущую ячейку, и вы можете сделать это в OFFSET, но, как правило, в этом нет необходимости. Фактическая ссылка на ячейку обычно используется. Как и в ответе Кайла, он обычно будет правильно переведен, если вы скопируете формулу в другую ячейку. OFFSET - это способ сделать отношение местоположения явным. Итак, если бы ваша первая формула была в ячейке C5 , она бы выглядела так:

=OFFSET(C5,0,-2)-IF(ISNUMBER(OFFSET(C5,-1,-2)), 
 OFFSET(C5,-1,-2),0)-OFFSET(C5,0,-1)