Подумайте об этом: у вас есть список продуктов, список со временем растет, и вы хотите, чтобы ваша диаграмма 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.