Способ автоматического определения диапазонов в Excel?

Question

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

В некоторых случаях информация в сводной рабочей таблице будет ссылаться на последующие рабочие таблицы данных в качестве диапазона для сводной таблицы. Проблема в том, что неизвестно, сколько строк будет в диапазоне. Когда в качестве источника для сводной таблицы используется диапазон типа A $:D $, появляется элемент «(пустой)», который отображается. Это может быть подавлено с помощью функции фильтра, однако я обеспокоен тем, что это заставит шаблон пропустить некоторые строки, если будут добавлены новые категории.

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

Есть ли способ, чтобы диапазон автоматически заканчивался в последней строке с данными?

Answer 1

Есть несколько способов достижения динамических диапазонов:

Использование OFFSET и COUNTA в именованном диапазоне

Если вы используете =COUNTA($A:$A) , вы получите все элементы в столбце A. Убедитесь, что вы выбрали столбец, который всегда заполнен, например столбец ID . Если ваша таблица данных начинается где-то ниже, вы также должны учесть это: =COUNTA($A:$A)-COUNTA($A$1:$A10) .

Теперь вы можете использовать формулу =OFFSET($A$1,0,0,COUNTA($A:$A),4) чтобы определить диапазон, начинающийся в A1, который имеет ширину 4 столбца и содержит все строки.

Хитрость в использовании этого определенного диапазона состоит в том, чтобы ввести его как именованный диапазон! Для этого перейдите в Диспетчер имен (на вкладке Формула) и вставьте новое имя (например, данные). Здесь вместо предоставления ссылки на фиксированный диапазон скопируйте в приведенную выше формулу. (Примечание: если вы хотите редактировать формулу, не забудьте нажать F2 перед использованием клавиш со стрелками, иначе формула будет испорчена ссылками на ячейки). Убедитесь, что вы используете $ для всех ссылок на ячейки.

Теперь вы можете использовать данные имени в качестве источника для вашего центра. Если вы хотите убедиться, что используется полный диапазон, просто введите имя в поле «Имя» слева от строки формул (вам нужно ввести его там) или просто нажмите на маленький значок рядом с формулой в диспетчере имен. ,

Использование INDEX и COUNTA

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

Чтобы избежать этого, замените OFFSET на INDEX следующим образом:

=INDEX($A:$A,COUNTA($A:$A)):INDEX($D:D$,COUNTA($A:$A))

Это будет делать ту же работу, но является энергонезависимой. Кроме того, если вы вставите новый столбец перед столбцом D, эта формула изменится, а в первом варианте вам придется заменить 4 на 5 (или использовать COUNTA($1:$1)).

Использовать таблицу Excel

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

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

Тем не менее, имейте в виду, что вам нужно будет обновить сводную таблицу (таблицы) после любого обновления!