Суммируйте столбец из определенной строки вниз

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

Например, я могу использовать это уравнение SUM (C: C), чтобы суммировать все числа, найденные в столбце C. Однако, учитывая, что в верхней части страницы есть заголовки и заголовки столбцов, я хотел бы начать мой диапазон по строке 6. Я думал, что SUM (C6: C) сделает это, но он не работает в Excel.

Это необходимо, поэтому мой SUM всегда прав, независимо от того, сколько строк данных я добавлю в свой документ в будущем.

Спасибо.

Ответы

Ответ 1

=Sum(C:C)-Sum(C1:C5)

Суммируйте все, а затем удалите сумму значений в ячейках, которые вам не нужны, вам не нужны параметры Volatile Offset, Indirect или Array.

Просто для удовольствия, если вам не нравится этот метод, вы также можете использовать:

=Sum($C$6:INDEX($C:$C,MATCH(9.99999999999999E+307,$C:$C))

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

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

=COUNTIF(C6:INDEX($C:$C,MATCH(REPT("Z",255),$C:$C)),"T")

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

=Sum($C$6:$C$65536)

или

=CountIF($C$6:$C$65536,"T")

Вышеупомянутое будет делать то, что вы запрашиваете в Excel 2003 и ниже

=Sum($C$6:$C$1048576)

или

=CountIF($C$6:$C$1048576,"T")

Оба будут работать в Excel 2007 +

Все вышеперечисленные функции просто игнорируют все пустые значения под последним значением.

Ответ 2

Вы все, кажется, любите усложнение. Просто нажмите на столбец (чтобы выделить весь столбец), нажмите и удерживайте клавишу CTRL и нажмите на ячейки, которые вы хотите исключить (от C1 до C5 в вашем случае). Теперь вы выбрали весь столбец C (справа до конца листа) без начальных ячеек. Все, что вам нужно сделать сейчас, это щелкнуть правой кнопкой мыши и "Определить имя" для вашего выбора (например, asdf). В формуле вы используете SUM (asdf). И теперь вы сделали. Удачи

Всегда найди самый легкий путь;)

Ответ 3

Если вы не возражаете против использования OFFSET(), который является изменчивой функцией, которая пересчитывает каждый раз при изменении ячейки, то это хорошее решение, которое является одновременно динамическим и многократно используемым:

=OFFSET($COL:$COL, ROW(), 1, 1048576 - ROW(), 1)

где $ COL - буква столбца, с которым вы собираетесь работать, а ROW() - функция строки, которая динамически выбирает ту же строку, что и ячейка, содержащая эту формулу. Вы также можете заменить функцию ROW() на статическое число ($ ROW).

=OFFSET($COL:$COL, $ROW, 1, 1048576 - $ROW, 1)

Вы можете дополнительно очистить формулу, определив именованную константу для 1048576 как 'maxRows'. Это можно сделать в меню "Определить имя" на вкладке "Формулы".

=OFFSET($COL:$COL, $ROW, 1, maxRows - $ROW, 1)

Быстрый пример: для суммирования от C6 до конца столбца C вы можете сделать следующее:

=SUM(OFFSET(C:C, 6, 1, maxRows - 6, 1))

или   = СУММА (СМЕЩЕНИЕ (C: C, ROW(), 1, maxRows - ROW(), 1))

Ответ 4

Нечто подобное сработало для меня (ссылки на столбцы C и D от строки 8 до конца столбцов, в Excel 2013, если применимо):

=SUMIFS(INDIRECT(ADDRESS(ROW(D$8), COLUMN())&":"&ADDRESS(ROWS($C:$C), COLUMN())),INDIRECT("C$8:C"&ROWS($C:$C)),$C$2)