Excel - SUM до конца списка

У меня есть таблица, подобная базе данных в Excel 2003, с одной строкой заголовка, содержащей AutoFilters в строке 6, и данными из строки 7 в-row 160. Строки 1-5 представляют собой заголовок листа, заголовки групп столбцов и инструкции, от которых я не могу избавиться.

Я также показываю SUM(X6:X160) и SUBTOTAL(109,X6:X160) в этой области для числовых столбцов. Я не хочу показывать это в нижней части таблицы, потому что несколько пользователей часто добавляют строки в эту таблицу, и они регулярно уничтожали формулы.

Проблема:, когда пользователь добавляет данные в конец таблицы, нужно будет обновить формулы СУММЫ и СУББОТА, чтобы расширить диапазон, который, разумеется, в основном забыт.

Я мог бы сам расширить формулы, чтобы охватить все строки до -ser-row 500 и скрыть оставшиеся строки, поэтому пользователю потребуется "вставить" строки, когда будет достигнут видимый конец таблицы, что, в свою очередь, обновить формулы, но я не считаю это очень безопасным способом... зная моих пользователей.

Вопрос: Есть ли способ создать СУММУ И СУББОТА от X6 " до конца столбца X, где бы это ни было"?

Заключение

Я использовал SUM (OFFSET (....)), беря параметр "высота" из нового поля в заголовке, который отображает "количество записей" с помощью =COUNTA($A:$A)-1 (-1 для текста заголовка столбца это также учитывается), поскольку из бизнес-контекста ясно, что ключевое значение должно существовать в столбце A для любой допустимой записи данных, и пустые строки не разрешены (я могу обучать пользователей, которые по меньшей мере), плюс польза для пользователя не только просмотра СУММЫ, но и теперь COUNT записей в замороженной панели заголовка.

Ответ 1

Использование Offset() и Count() кажется самым популярным, и я уверен, что наиболее эффективно использовать.

=SUM(OFFSET($X$6,0,0,COUNT($X$6:$X$1000)))

Лично я часто использую косвенные() для вещей. Вероятно, он работает медленнее, но он работает. Это помогает мне увидеть диапазон, который создается. Однако осторожно, поскольку часть диапазона сохраняется в тексте, она не будет обновляться при перемещении формулы. Это может вас тронуть. Здесь вы все равно.

=SUM(INDIRECT("$X$6:$X$" & COUNT($X$6:$X$1000)))

Вы можете использовать динамические диапазоны, на которые указывает iDevlop, или помещать Offset() или Indirect() внутри Sum(), как я только что сделал.

В любом случае вы хотите быть осторожным, потому что косвенные() и смещение() Летучие функции. Кого обычно вы хотите избежать.

Я также нашел этого парня, который использует Index() и Match(), которые не являются неустойчивыми функциями.

EDIT:

Думая об этом (потому что я только что сделал на моей электронной таблице).

Если вы знаете, что ваши данные будут иметь разумный предел, скажем 1000, вы можете просто использовать =SUM($X$6:$X$1000), и он пропустит пробелы, даже для метода "Итого" 1 или 101 (средний).

Ответ 2

Ну, я думаю, вы можете использовать что-то вроде = SUM (C: C), чтобы суммировать все ячейки в столбце C, и он автоматически исключит текст, который я попробовал, и он работал

Ответ 3

Конечно! И мне гораздо лучше иметь общее количество сверху, так как вы также можете заморозить первые строки, чтобы сохранить эти итоговые значения.
Просто спросите Google о "динамическом диапазоне Excel". Вот несколько ссылок:
http://www.ozgrid.com/Excel/DynamicRanges.htm
http://support.microsoft.com/kb/830287

Все это выполняется с помощью функций Offset() и Count().

Ответ 4

Поместите эту формулу в последнюю строку вашего диапазона данных (не должно быть таблицы/списка), это может решить вашу проблему

=SUM(INDIRECT(("x6:x"&ROW()-1)))

или если у вас есть таблица/список, то вы можете использовать эту формулу

=sum([table header name])

как вы хотите суммировать столбец "Сумма" заголовок типа

=sum([Amount])

в последнем ряду вашего списка/таблицы