В прошлой статье мы с вами сделали выпадающий список товаров с помощью именованного диапазона. Он отлично работает, но у него есть один недостаток - он не динамический.
Это значит, что если мы добавим в конец нашего списка товаров на листе [Список] новый товар, то наш именованный диапазон [lstТовары] не включит его в свой состав. Аналогично, если мы удалим товар из списка: диапазон не будет подстраиваться под новый размер списка.
Что же нам делать, чтобы при изменении размера списка товаров каждый раз не пересоздавать заново именованный диапазон? Есть некоторые хитрые способы обойти эту проблему, например, вставлять новые ячейки в середину диапазона. Это заставит именованный диапазон регулировать свою высоту.
Однако, это выглядит немного непрофессионально, а мы с вами хотим стать продвинутыми пользователями Excel. Поэтому было бы неплохо, если бы наш диапазон автоматически менял свою высоту при добавлении и удалении его позиций. И такой способ есть!
Для этого мы создадим динамический диапазон с помощью формулы, в которой будем использовать две новые функции: ИНДЕКС и СЧЁТЗ.
Очень полезную функцию ИНДЕКС мы с вами более подробно будем изучать позднее. Сегодня будет наше первое небольшое знакомство с ней.
Мы могли бы при создании именованного диапазона писать формулу сразу же в Диспетчере имён, но там не будет никаких подсказок, поэтому давайте сперва напишем её в свободной ячейке, а потом просто скопируем.
Итак, на листе [Список] у нас есть список товаров.
Введём в свободную ячейку, например, в C2 нашу формулу:
=ИНДЕКС(Список!$A:$A;2):ИНДЕКС(Список!$A:$A;СЧЁТЗ(Список!$A:$A))
Как видите, мы создали диапазон с помощью оператора двоеточия (:). И с каждой стороны от оператора диапазона использовали функцию ИНДЕКС. Эти функции создают первую и последнюю ячейку в списке и тем самым формируют диапазон.
Первая функция ИНДЕКС возвращает адрес ячейки в строке 2 столбца А. Столбец задаётся с помощью аргумента массива [Список!$A:$A], а строка с помощью аргумента номера строки [2]. Строка 2 вводится как постоянное значение, то есть наш диапазон будет всегда начинаться с ячейки А2.
Во второй функции ИНДЕКС в аргументе номера строки используется функция СЧЁТЗ. Эта функция подсчитывает количество непустых ячеек в столбце А. В нашей формуле она возвращает адрес последней непустой ячейки.
Вот, что у нас получилось:
Если у вас Excel версии 2021 или Excel 365, то вы увидите такую же картину: формула разольётся в массив данных, которые окружены синей границей. Если же у вас более старая версия, то массив не будет сам разливаться, однако формула всё равно будет работать.
Пришло время использовать её для создания именованного динамического диапазона. В прошлой статье мы уже создавали именованный диапазон для выпадающего списка. Поэтому давайте просто его отредактируем.
1. Скопируйте формулу из Строки формул.
2. Нажмите Формулы ➤ Диспетчер имен.
3. Выберите имя lstТовары и нажмите Изменить.
4. Удалите содержимое поля Диапазон и вставьте формулу. Нажмите OK.
После этого формула в ячейке С2 листа [Список] больше не нужна. Можно её и удалить.
Давайте добавим в наш список товаров новую категорию, например, "Куртки".
Теперь перейдём на лист [Продажи] и проверим наш выпадающий список. Там появились "Куртки".
Таким образом, когда вы добавляете новые товары в конец списка, они автоматически включаются в выпадающий список. Можете отсортировать в списке товары от А до Я с помощью инструмента сортировки и в выпадающем списке они тоже отсортируются.
Надеюсь, сегодняшняя тема была для вас понятна. Если остались какие-либо вопросы - задавайте их в комментариях. С сегодняшним примером вы можете ознакомиться в файле по ссылке.
Существует ещё один способ создания именованных диапазонов, причём нескольких за раз. Пример этого мы с вами рассмотрим в следующий раз.
Не переключайтесь;)