Подумайте об этом: у вас есть список продуктов, список со временем растет, и вы хотите, чтобы ваша диаграмма Excel продуктов по сравнению с продажами автоматически отображала любые дополнительные продукты, которые вы добавляете. Или, может быть, это ежедневный отчет о продажах, и вы хотите, чтобы диаграмма автоматически расширялась, чтобы показать новые добавленные даты.

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

Оказывается это

К этому (не переделывая схему и даже не прикасаясь к ней вообще)

И может добавить подсластителя жизни в ваши списки проверки данных.

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

Так как же создавать эти динамические списки?

Просто. Существует два популярных способа создания динамических списков в Excel. Один из них — использовать ИНДЕКС и СЧЕТЧИК. Другой вариант — использовать OFFSET и COUNTA.

Технически, вам следует выбрать INDEX + COUNTA, а не OFFSET + COUNTA, так как он имеет лучшую производительность. Опять же, помните ключевое слово — технически. На практике тот вариант, который вам легче освоить, лучше.

ИНДЕКС + СЧЕТЧИК

Для первой диаграммы я использовал ИНДЕКС + СЧЕТЧИК. .

Для поля списка я создал именованный диапазон:
=Sheet1!$A$2:INDEX(Sheet1!$A:$A,COUNTA(Sheet1!$A:$A))

По сути, это
=A2:INDEX(A:A,COUNTA(A:A))

Что делает индексная часть, так это возвращает ссылку на ячейку последней ячейки строки в заполненном столбце A.

ИНДЕКС(A:A представляет собой список/массив всех ячеек в столбце A, а часть COUNTA(A:A) указывает Excel найти ячейку в позиции строки, равной количеству всех заполненных ячейки столбца А.

Теперь вы понимаете, как это работает.

СМЕЩЕНИЕ + СЧЕТЧИК

Для списка проверки данных я использовал OFFSET + COUNTA.

Именованный диапазон

=СМЕЩЕНИЕ(Лист2!$A$2,0,0,СЧЁТ(Лист2!$A:$A)-1,1)

Опять же, по своей сути формула

=СМЕЩЕНИЕ(A2,0,0,СЧЁТ(A:A)-1,1)

Легко понять.

Мы берем A2 в качестве начальной или контрольной точки, опускаем ноль строк вниз и ноль столбцов вправо, затем расширяем строки (высоту) вниз на количество непустых строк в столбце A минус один, чтобы избежать подсчета ячейки A1 (которая используется для заголовок поля), затем возьмите один столбец шириной (придерживаясь столбца A).

Результат

Наконец

Для диаграммы я просто заменил элементы легенды и метку оси динамическим именованным диапазоном.

И это делает магию!

Для списка проверки данных я просто указал =months в качестве источника. (месяцы — это именованный диапазон, который я создал с помощью формулы OFFSET + COUNTA)

И это все!

Теперь вы должны быть гуру динамических списков :)

Первоначально опубликовано на www.olafusimichael.com.