Связанные выпадающие списки в Excel

Выпадающий список в 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)

Пояснения к формуле:

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

Выпадающие списки готовы. Форматированные смарт-таблицы позволят «зафиксировать» все данные, а сводные таблицы помогут избежать ошибок, отсортировать справочник и составить список групп.

Связанные выпадающие списки в Excel

Связанные выпадающие списки

Способ 1. Связанные выпадающие списки из таблицы с группами в заголовках столбцов

Справка:

Форматированная («умная») таблица Excel

Формула ДВССЫЛ

Пошаговая инструкция по созданию связанных выпадающих списков

Способ 2. Связанные выпадающие списки из таблицы с группами в первом столбце и элементами — во втором

Справка:

Формула СМЕЩ

Формула ПОИСКПОЗ

Формула СЧЁТЗ

Формула СЧЁТЕСЛИ

Пошаговая инструкция по созданию списков

Автор: Станислав Салостей

Похожие записи

Добавить комментарий Отменить ответ