Подсчет от определенной ячейки до пустой ячейки

Question

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

Какая формула имеет диапазон, который включает в себя ячейки от D3 до следующей пустой ячейки, которая будет обновляться и включать новые данные, когда последняя пустая ячейка будет заполнена данными нового дня?

Answer 1

Позвольте мне убедиться, что у меня есть это прямо:

• У вас есть много данных в столбце D, которые вы хотите собрать.
• Строки 1 и 2 являются строками заголовка и содержат текст.
• Строки 3 и ниже в столбце D содержат итоговые значения.
• Под данными, подлежащими сумме, находится ячейка, в которую вы хотите поместить итоговую сумму.
• Могут быть или не быть пустые ячейки между данными, подлежащими суммированию, и ячейкой, которая отображает данные.

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

=SUM(INDIRECT(CONCATENATE("D3:D",CELL("row")-1)))

Прохождение:

• SUM суммирует все числовые значения (автоматически исключая пробелы и нечисловые значения) в указанных ячейках.
• INDIRECT позволяет построить ссылку на ячейку по формуле.
• CONCATENATE позволяет объединять части строк и результаты формул в одну строку.
• «D3:D» - это первая часть ссылки, которую мы в конечном итоге хотим передать в SUM.
  • Это определяет диапазон ячеек, который начинается в D3 и заканчивается в другой ячейке в столбце D.
  • Поскольку он предоставляется в виде строки вместо фактической ссылки на ячейку, он не будет изменен, если ячейка будет перемещена или скопирована - диапазон всегда будет указывать на столбец D и всегда начинаться со строки 3.
• CELL позволяет получать информацию о ячейке. Здесь мы получаем номер строки для текущей ячейки. Это то, что позволяет формуле автоматически корректировать номер строки независимо от того, где он находится.
• -1 используется для вычитания 1 из номера строки, возвращенного CELL, таким образом ссылаясь на строку выше текущей ячейки.

Для D303 это будет фактически так же, как:

=SUM(D3:D302)

Answer 2

Я думаю, что мой ответ здесь: В Excel 2010, как я могу создать шаблон с функцией автоматически заканчивается в последней ячейке с данными? может быть полезным.

Я настоятельно рекомендую таблицы, если вы можете, они также имеют встроенную функцию общей строки.

Если нет, воспользуйтесь динамическим именованным диапазоном.


РЕДАКТИРОВАТЬ
Как мы уже говорили, вы хотите иметь возможность использовать динамический именованный диапазон в качестве исходного диапазона для сводной таблицы:

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

=OFFSET(Sheet1!$F$1,0,0,COUNTA(Sheet1!$F$1:$F$12),COUNTA(Sheet1!$F$1:$G$1))

Здесь вы можете видеть, что вместо окончания формулы с помощью ),) мы добавили ссылку на столбец, которая возвращает диапазон столбцов, покрывая 1-ю строку по всем необходимым столбцам.

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


ЗАМЕТКИ
Обертывание OFFSET в COUNT любого типа не будет работать, если вы хотите использовать именованный диапазон в сводной таблице, но в этом нет необходимости, поскольку вы можете просто запустить счетчик в сводной таблице.

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