Выпадающий список в Excel (или выпадающий список) — это список в ячейке Excel, из которого вы можете выбрать одно из нескольких предопределенных значений. Это удобно, чтобы быстро и правильно заполнить данные: руками не вводить, а только выделять. Здесь же можно настроить элемент управления, чтобы пользователи не могли вводить значения, которых нет в списке.
Выпадающий список работает следующим образом: вы выбираете ячейку, и справа появляется кнопка со стрелкой вниз (хотя стрелка больше похожа на треугольник).
После нажатия кнопки списка появится список доступных значений, одно из которых можно выбрать.
В списке может быть много значений, но в сводке помещается до 8 строк. Если в списке более восьми значений, справа от них появится полоса прокрутки.
Чтобы создать раскрывающийся список, выберите ячейку, в которой он должен появиться (или группу ячеек), и перейдите на вкладку «Данные» -> «Проверка данных.
В появившемся окне указываем тип данных — Список, ставим галочку напротив строки «Список допустимых значений».
Источником данных может быть:
- Текст: пишется через точку с запятой «;» и без знака равенства «=», например
Материалы;Зарплата;Амортизация
- Ссылки на соты:
=$А$1:$А$7
- Именованный диапазон:
= Имя диапазона
так далее
Связанные выпадающие списки
Связанные выпадающие списки — это списки, в которых выпадающие значения появляются не «просто так», а на основе уже заполненных данных. Итак, для выбранной группы появится только список имен, входящих в нее.
Мы собираемся создавать выпадающие списки несколькими способами: для разных таблиц с исходными данными.
Способ 1. Сгруппируйте имена в заголовках столбцов, в строках — сгруппируйте элементы.
Способ 2. Названия групп — в первом столбце, элементы группы — во втором столбце.
Скачать пример
Способ 1. Связанные выпадающие списки из таблицы с группами в заголовках столбцов
Исходные данные: таблица с названиями групп в заголовках столбцов.
В этом методе используется только простая формула — ДВССЫЛ (хотя и непривычная для обычных пользователей) и отформатированная смарт-таблица Excel (иногда также называемая «умными» таблицами). Но вы получите взаимозависимые списки и будете использовать их по принципу «установил и забыл». Не нужно будет беспокоиться о том, что диапазон «улетит» и переделает всю архитектуру данных. Просто сделайте это один раз и используйте.
Справка:
Форматированная («умная») таблица Excel
Форматированная таблица — это таблица, имеющая собственное имя, свойства и структуру. Такая таблица представляет собой именованный «саморасширяющийся» диапазон. Когда в нее добавляются новые данные, границы таблицы автоматически «подхватывают» новое значение.
Отформатированная таблица имеет много преимуществ по сравнению с обычной таблицей. Поэтому на курсах и семинарах советую по возможности использовать такие таблицы.
Создать отформатированную таблицу очень просто: выберите диапазон ячеек и перейдите в меню «Пуск» -> «Форматировать как таблицу» -> выберите желаемое представление таблицы. Готово: форматированная таблица создана.
Формула ДВССЫЛ
Формула ДВССЫЛ передает значения из ячейки, адрес которой хранится в самой формуле, в виде текстовой строки.
Например, мы записываем адрес ячейки A1 в ячейку B1. Формула ДВССЫЛ (B1) «увидит», какой адрес находится в ячейке B1, и результатом формулы будет текст в ячейке A1. Эту же формулу можно записать, указав адрес ячейки в кавычках – ДВССЫЛ («А1»).
С ДВССЫЛОЙ вы можете ссылаться на ячейку по адресу, используя другие формулы, такие как СЦЕПИТЬ, & или ЕСЛИ и т д. Таким образом, формула на ДВССЫЛОМ рисунке (B1 и C1) относится к тексту в ячейке A1. После нажатия Enter в ячейке, где была введена формула ДВССЫЛ (B1 & C1), появится значение ячейки A1, в нашем случае это «текст».
Пошаговая инструкция по созданию связанных выпадающих списков
Шаг 1. Создайте ссылку на исходные данные в виде отформатированной смарт-таблицы.
- Выберите таблицу элементов и преобразуйте ее в смарт-таблицу: в меню выберите «Главная» -> «Форматировать как таблицу.
- В появившемся окне обязательно поставьте галочку напротив «Таблица с заголовками». Если его нет, наденьте его.
- Дайте созданной таблице имя: выберите любую ячейку в таблице, перейдите на вкладку «Макет», введите имя — «Шрифт».
Исходная таблица создана. Теперь вы можете обращаться к таблице и ее элементам по имени. Например, заголовок заголовка таблицы будет выглядеть так: =Источник[#Заголовки]
Столбец таблицы: =Источник[Материалы]
Чтобы появилась такая формула, нажмите = и выберите столбец, его имя появится в строке формул.
Шаг 2. Создайте выпадающий список с группами.
- Выделите ячейки в столбце группы».
- Перейдите в меню Данные -> Проверка данных.
- В появившемся окне выберите тип данных — Список, а в строке Источник введите формулу =ДВССЫЛ(«Источник[#Заголовки]»)
Готовый! В колонке «группа» появился выпадающий список.
Шаг 3. Создайте выпадающий список с элементами.
- Выделите столбец «элемент» в таблице.
- Перейдите в меню Данные -> Проверка данных.
- В появившемся окне выберите тип данных — Список, а в строке Источник введите формулу: =ДВССЫЛ(«Источник[«&$G3&»]»)
В формуле $G3 — это первая ячейка в групповом столбце. Ссылка на столбец «фиксируется» знаком доллара $, а строка может быть изменена.
Готовый! В столбце «Элемент» отображается только список статей, включенных в группу.
Теперь вы можете добавлять новые группы и элементы в отформатированную смарт-таблицу. Добавим, например, новую колонку «Другое», и эта группа сразу появится в выпадающем списке.
Способ 2. Связанные выпадающие списки из таблицы с группами в первом столбце и элементами — во втором
Исходные данные: таблица с названиями групп в первом столбце, элементы групп во втором столбце.
На самом деле в сети можно найти несколько вариантов реализации этого метода. Но у всех у них есть один недостаток: таким списком нужно «управлять». Поскольку таблица всегда должна быть отсортирована по именам групп, у вас не может быть случайных групп. Если группы идут «все равно», то формула, с помощью которой все это делается (СМЕЩЕНИЕ), работать не будет и список будет создан с ошибкой. Этим пользователям необходимо все время сортировать первый столбец или добавлять туда данные в алфавитном порядке. А еще нужно будет где-то отдельно прописать названия групп, и это тоже нужно будет сделать «вручную».
Мы выяснили, как решить эту проблему: с помощью сводных таблиц, которые упорядочат данные за нас. Это заменит постоянное «управление» простой процедурой «обновления».
Для создания списков мы используем форматированные (умные) таблицы, сводные таблицы, формулы OFFSET+MATCH+COUNT, COUNTIF и менеджер имен.
Справка:
Формула СМЕЩ
OFFSET возвращает ссылку на диапазон ячеек, который находится на указанном количестве ячеек от оригинала. Ссылка определяется с учетом размера диапазона, указанного в формуле — количества строк и столбцов. Другими словами, с помощью этой формулы вы можете «указать» Excel, на сколько ячеек вернуться и какой диапазон «захватить».
Синтаксис формулы OFFSET:
СМЕЩ(ссылка; смещение_строки; смещение_столбца; [высота]; [ширина]), где
- ссылка: ссылка, по которой рассчитывается смещение, может быть адресом ячейки или группы ячеек;
- line_offset — количество строк, которые нужно отсчитывать вверх или вниз от начальной ссылки;
- column_offset — количество столбцов, которые нужно считать слева или справа от начальной ссылки;
- [высота] — количество строк в возвращаемой ссылке (необязательно);
- [ширина] — количество столбцов в возвращаемой ссылке (необязательно).
Формула ПОИСКПОЗ
Найдите нужный нам элемент в диапазоне ячеек и задайте его порядковый номер в диапазоне.
Синтаксис ПОИСКПОЗ:
ПОИСКПОЗ(искомое_значение; искомый_массив; [тип_совпадения])
- lookup_value — это значение, которое мы ищем. Это может быть число, текст, логическое значение или ссылка на ячейку;
- lookup_array — диапазон ячеек, где мы будем искать искомое значение;
- [тип_сопоставления] — это число -1, 0 или 1, указывающее, как сопоставлять значение поиска с ячейками сопоставляемого массива. Не беспокойтесь, если вы не понимаете, когда и на что ставить, потому что в 90% случаев вам нужно выбрать ноль.
Подробнее об этой формуле можно посмотреть в видео-инструкции: Какая формула лучше ВПР и работает с разными критериями
Формула СЧЁТЗ
COUNT просто подсчитывает количество непустых ячеек в диапазоне.
Формула СЧЁТЕСЛИ
Почти то же самое, что СУММЕСЛИ, только проще: подсчитывает количество значений, удовлетворяющих определенному условию.
Пошаговая инструкция по созданию списков
Шаг 1. Преобразуйте исходные данные в отформатированную смарт-таблицу.
- Выберите таблицу элементов и преобразуйте ее в смарт-таблицу: перейдите в раздел «Главная» -> «Форматировать как таблицу.
- В появившемся окне обязательно поставьте галочку напротив «Таблица с заголовками». Если его нет, наденьте его.
- Дайте таблице имя: Во вкладке Макет введите название таблицы: «Статьи».
Отформатированная таблица «items» создана.
Шаг 2. Создайте две сводные таблицы: одну с именами групп, а вторую с элементами.
Почему мы используем сводные таблицы? Во-первых, чтобы не создавать вручную список групп, а во-вторых, как уже было сказано выше, не вручную сортировать каталоги статей (что пользователи иногда забывают сделать, и это важно, иначе формула СМЕЩ «срабатывает» с ошибкой) . «Ручная» работа будет сделана за нас с помощью кнопки «Обновить» в меню «Данные»; мы нажимаем его каждый раз, когда появляются новые статьи.
- Создайте свою первую сводную таблицу с группами элементов.
Выберите любую ячейку в таблице с исходными данными, перейдите в меню Вставка -> Сводная таблица. Добавьте сводную таблицу на существующий рабочий лист и разместите группы в области строк.
- Создайте вторую сводную таблицу с элементами: меню Вставка -> Сводная таблица. Разместите группы и элементы в области строки.
- Мы форматируем сводную таблицу с элементами и придаем ей вид ссылки.
Выберите любую ячейку в таблице, перейдите на вкладку «Макет» -> «Макет отчета» -> «Показать в табличной форме». Мы получим почти нужную нам таблицу, но в ней автоматически появятся промежуточные итоги. Чтобы отключить их, перейдите к: Промежуточные итоги -> Не показывать промежуточные итоги.
- Скройте строку «Общая сумма» в обеих таблицах поиска. Перейдите на вкладку «Макет» -> «Общие итоги» -> «Отключить для строк и столбцов.
В результате у вас получится две директории, как на рисунке ниже. Для удобства расположите таблицы рядом на одном листе, начиная с первой строки и в столбцах A, C и D, как на рисунке (это поможет понять формулу OFFSET).
Шаг 3: Создайте именованные диапазоны с помощью диспетчера имен.
- Откройте Менеджер имен: в меню Формулы -> Менеджер имен.
- В появившемся окне нажмите кнопку «Создать».
-
- Введите имя «GroupList» и формулу, которая будет определять диапазон:
=СМЕЩЕНИЕ($A$1,1,0,СЧЁТ($A:$A)-1,1)
- Введите имя «GroupList» и формулу, которая будет определять диапазон:
Пояснения к формуле:
OFFSET ($A$1 ; 1 ; 0 ; COUNTA($A:$A) – 1 ; 1) – указывает адрес ячеек с именами групп.
-
- $A$1 — первая ячейка в групповом каталоге.
- Следующие цифры 1; 0 — это отступ первой ячейки на 1 строку и 0 столбцов (отступ необходим, потому что первая ячейка имеет имя столбца).
- COUNT($A:$A) — 1 Подсчитывает количество непустых ячеек в столбце A. Вычитает -1, потому что имя столбца не должно быть в списке.
- Последняя цифра 1 в формуле — это количество столбцов.
Нажмите «ОК». Имена листов в формуле появятся сами по себе.
- Таким же образом создайте список статей в менеджере имён.
Введите название группы товаров и формулу для диапазона:
=СМЕЩЕНИЕ($C$1,ПОИСКПОЗ($G2,$C:$C,0)-1,1,СЧЁТЕСЛИ($C:$C,$G2),1)
Пояснения к формуле:
OFFSET($C$1 ; MATCH ($G2 ; $C:$C ; 0) — 1 ; 1 ; COUNTIF($C:$C ; $G2) ; 1) — определяет адрес ячеек с именами элементы из группы, используя ПОИСКПОЗ А, который ищет группы элементов.
- $C$1 — первая ячейка в столбце группы.
- ПОИСКПОЗ ($G2 ; $C:$C ; 0) – 1 Определяет, на сколько строк следует отступить от первой ячейки ПОИСКПОЗ находит имя выбранной группы в таблице данных (столбец $G) между ячейками поиска (столбец $C) . В адресе ячейки $G2 мы не «фиксируем» номер строки знаком $, чтобы формула работала для всех ячеек столбца.
- Следующая цифра 1 с отступом на 1 столбец вправо, то есть перейти к столбцу «предметы», откуда нужно взять данные.
- СЧЁТЕСЛИ($C:$C , $G2) – подсчитывает количество ячеек в столбце $C, где имена групп такие же, как в столбце данных. Здесь мы также не «фиксируем» номер строки в ячейке $G2 с помощью знака $.
- Последняя цифра 1 в формуле — это количество столбцов.
Шаг 4 – Создайте выпадающие списки.
Выберите ячейки в столбце «группы», перейдите в меню «Данные» -> «Проверка данных». Установите тип данных Список, источник = GroupsList.
То же самое верно и для статей. Тип данных: список, источник = ItemGroups
Выпадающие списки готовы. Форматированные смарт-таблицы позволят «зафиксировать» все данные, а сводные таблицы помогут избежать ошибок, отсортировать справочник и составить список групп.
Автор: Станислав Салостей
Похожие записи
Как добавить блок «Другое» на диаграмму в Power BI
09.02.2022 9 необычных диаграмм Excel
03.02.2022 Графики Excel для презентаций
02.02.2022 Факторный анализ в Power BI за выбранный и предыдущий год
10.10.2021 Промежуточные итоги в Power Pivot и Power BI
09.10.2021 Текущий, прошлый и предыдущий год в Power BI
08.10.2021 Как добавить блок «Другое» на график в Power BI
09.02.2022 9 необычных диаграмм Excel
03.02.2022 Графики Excel для презентаций
02.02.2022 Факторный анализ в Power BI за выбранный и предыдущий год
10.10.2021 Промежуточные итоги в Power Pivot и Power BI
09.10.2021 Текущий, прошлый и предыдущий год в Power BI
08.10.2021 Как добавить блок «Другое» на график в Power BI
09.02.2022 9 необычных диаграмм Excel
03.02.2022 Графики Excel для презентаций
02.02.2022
