☰

Excel: как автоматически обновить формулу суммы при добавлении столбца, сохранив тот же диапазон

tag-icon • tag-icon microsoft-excel worksheet-function
Excel: как автоматически обновить формулу суммы при добавлении столбца, сохранив тот же диапазон

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

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

Буду очень признателен за помощь, спасибо.

решение1

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

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

=SUM(A1:OFFSET(K1,0,-1))

В этом примере нужно ввести данные в ячейку K1, чтобы суммировать диапазон от A1 до J1. Функция OFFSETработает со ссылкой на ячейку, а не со значением в ячейке. OFFSET(cell, y, x)означает строки ячейки yниже и xстолбцы справа от cell.

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

=SUM(OFFSET(K1,0,-6):OFFSET(K1,0,-1))

даст вам последние шесть столбцов. Я не понимаю, что именно вы пытаетесь сделать.

решение2

Такая формула:

=СУММА(ДВССЫЛ(ЯЧЕЙКА("адрес",СМЕЩ(A2,0,СЧЁТA(2A:ZZ2)-3))&":"&ЯЧЕЙКА("адрес",СМЕЩ(A2,0,СЧЁТA(A2:ZZ2)-1))))

сначала подсчитаем количество ячеек со значениями в диапазоне A2:ZZ2
(из-за COUNTA(A2:ZZ2)этого также принудительно первый параметр OFFSET() становится равным A2)

CELL("address",OFFSET(A2,0,COUNTA(A2:ZZ2)-3))создаст строку с адресом ячейкиДВАстолбцы слева от последней ячейки (со значением) в диапазоне.( Во втором OFFSET() -3находится -1функция, которая находит последнее значение)

... и это создаст текст, сформированный путем CELL(...) & ":" & CELL(...)создания диапазона ячеек, соответствующего последним трем ячейкам в этой строке.

INDIRECT()затем «интерпретирует» этот текст диапазона ячеек и передает его в SUM(...), который затем может выполнить вычисления.

Примечание: чтобы получить ПОЛНЫЙ диапазон строк, используйте eg COUNTA(2:2)для второй строки.

Проверено в LibreOffice, должно работать в любой версии SpreadSheet.
Множество вызовов функции INDIRECT() могут замедлить работу вашей таблицы

Связанный контент