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

Раскрывающийся список в Excel (или раскрывающийся список) — это список в ячейке Excel, из которого вы можете выбрать одно из нескольких предопределенных значений. Это удобно для быстрого и правильного заполнения данных: не вводите руками, а просто выбирайте. Вы также можете настроить элемент управления здесь, чтобы пользователи не могли вводить значения, не указанные в списке.

Выпадающий список работает следующим образом: выберите ячейку, и справа от нее появится кнопка со стрелкой вниз (хотя стрелка больше похожа на треугольник).

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

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

Чтобы создать раскрывающийся список, выберите ячейку, в которой он должен отображаться (или группу ячеек), и перейдите на вкладку «Данные» -> «Проверка данных.

В появившемся окне укажите тип данных — Список, поставьте галочку напротив строки «Список допустимых значений».

Источником данных может быть:

Текст — пишется через точку с запятой «;» а без знака равно «=», например
Материалы; Заработная плата; Амортизация

Ссылки на ячейки:
= $ A $ 1: $ A $ 7

Названный диапазон:
= IntervalName

так далее

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

Связанные раскрывающиеся списки — это списки, в которых раскрывающиеся значения не отображаются «просто так», а зависят от уже заполненных данных. Тогда для выбранной группы появится только список ее названий.

Выпадающие списки мы создаем по-разному, для разных таблиц с исходными данными.

Способ 1. Названия групп в заголовках столбцов, в строках — элементы группы.

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

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

Исходные данные: таблица с названиями групп в заголовках столбцов.

В этом методе используется только простая формула: КОСВЕННАЯ (что необычно для обычных пользователей) и умная таблица, отформатированная в Excel (их также иногда называют «умными» таблицами). Вместо этого вы получаете взаимозависимые списки и используете их по принципу «сделал и забыл». Вам не нужно будет беспокоиться о том, что интервал «улетит», и вы переделаете всю архитектуру данных. Сделайте это один раз и используйте.

Справка:

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

Отформатированная таблица — это таблица, имеющая собственное имя, свойства и структуру. Эта таблица представляет собой диапазон, называемый «саморасширяющимся». По мере добавления новых данных края таблицы автоматически «захватывают» новое значение.

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

создать форматированную таблицу легко — выберите диапазон ячеек и перейдите в Главное меню -> Форматировать как таблицу -> выберите тип таблицы, который вам нравится. Готово: форматированная таблица создана.

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

Формула INDIRECT передает значения из ячейки, адрес которой записан в самой формуле в виде текстовой строки.

Например, впишите адрес ячейки A1 в ячейку B1. Косвенная формула (B1) «увидит», какой адрес записан в ячейку B1, и результатом вычисления формулы будет текст, записанный в ячейке A1. Эту же формулу можно записать, указав адрес ячейки в кавычках — ДВССЫЛ («А1»).

С помощью INDIRECT вы можете ссылаться на ячейку по адресу, используя другие формулы, такие как CONCATENATE, & или IF и т.д. Таким образом, формула на рисунке INDIRECT (B1 & C1) ссылается на текст в ячейке A1. После нажатия Enter в ячейке, в которую была введена КОСВЕННАЯ формула (B1 и C1), появится значение ячейки A1, в нашем случае это «текст».

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

Шаг 1. Создайте справочный источник данных в виде отформатированной интеллектуальной таблицы.

Выделите таблицу со статьями и преобразуйте ее в интеллектуальную таблицу: выберите в меню «Главная» -> «Форматировать как таблицу.

В появившемся окне обязательно установите флажок «Таблица с заголовками». Если нет, наденьте его.

Назовите созданную таблицу: выделите любую ячейку в таблице, перейдите на вкладку «Дизайн», введите имя — «Источник».

Исходная таблица создана. Теперь вы можете ссылаться на таблицу и ее элементы по имени. Например, название заголовка таблицы будет выглядеть так: = Source [#Headers]

Столбец таблицы: = Источник [Материалы]

Чтобы вызвать эту формулу, нажмите равно = и выберите столбец, его имя появится в строке формул.

Шаг 2. Создайте выпадающий список с группами.

Выделите ячейки в столбце «группа».
Перейдите в меню «Данные» -> «Проверка данных.
В появившемся окне выберите тип данных — Список и в строке Источник введите формулу = ДВССЫЛ («Источник [#Headers]»)

Готовый! В столбце «группа» появился раскрывающийся список.

Шаг 3. Создайте выпадающий список со статьями.

Выберите в таблице столбец «статья.
Перейдите в меню «Данные» -> «Проверка данных.
В появившемся окне выберите тип данных — Список и в строке Источник введите формулу: = КОСВЕННО («Источник [» & $ G3&»]»)
В формуле $ G3 — первая ячейка в столбце «Группа». Столбец обозначен знаком доллара $, и строка доступна для редактирования.

Готовый! В столбце «статья» отображается только список статей, входящих в группу.

Теперь вы можете добавлять новые группы и статьи в отформатированную интеллектуальную таблицу. Например, давайте добавим новый столбец «Другое», и эта группа сразу же появится в раскрывающемся списке.

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

Исходные данные: таблица с названиями групп в первом столбце, элементы группы во втором столбце.

В сети, собственно, можно найти несколько вариантов реализации этого метода. Но у всех есть один недостаток: такой список надо «администрировать». Поскольку таблица всегда должна быть отсортирована по именам групп, группы не могут быть расположены произвольно. Если группы станут «несовместимыми», формула, с помощью которой все это делается (СМЕЩЕНИЕ) не будет работать и список будет создан с ошибкой. Пользователь должен всегда сортировать первый столбец или добавлять данные в алфавитном порядке. И вам также придется где-то отдельно записывать названия самих групп, причем это тоже придется делать «вручную».

Мы выяснили, как это обойти: с помощью сводных таблиц, которые будут организовывать данные за нас. Это заменит постоянное «администрирование» простой процедурой «обновления».

Для создания списков мы используем форматированные (умные) таблицы, сводные таблицы, формулы OFFSET + SEARCH + COUNT, COUNTIF и менеджер имен.

Справка:

Формула СМЕЩ

СМЕЩЕНИЕ возвращает ссылку на диапазон ячеек в пределах указанного количества ячеек от оригинала. Ссылка определяется исходя из размера указанного в формуле диапазона — количества строк и столбцов. Другими словами, с помощью этой формулы вы можете «сказать» Excel, сколько ячеек оно должно уместиться и какой диапазон «взять».

Синтаксис формулы СМЕЩЕНИЕ:

OFFSET (ref; line_offset; column_offset; [height]; [width]), где

ссылка — ссылка, по которой рассчитывается смещение, может быть адресом ячейки или группы ячеек;
line_offset — количество строк для отсчета вверх или вниз от начальной ссылки;
column_offset — количество столбцов для подсчета слева или справа от начальной ссылки;
[высота] — количество строк возвращаемой ссылки (необязательно);
[ширина] — количество столбцов возвращаемой ссылки (необязательно).

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

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

Синтаксис MATCH:

ПОИСКПОЗ (искомое_значение; искомое_массив; [тип_сопоставления])

lookup_value — это значение, которое мы ищем. Это может быть число, текст, логическое значение или ссылка на ячейку;
lookup_array — диапазон ячеек, в котором мы будем искать искомое значение;
[match_type] — это число -1, 0 или 1, которое показывает, как сравнивать значение поиска с ячейками в отсканированном массиве. Не волнуйтесь, если вы не понимаете, когда и что делать ставки, потому что в 90% случаев вам придется выбрать ноль.

Более подробную информацию об этой формуле можно найти в видеоуроке: Какая формула лучше, чем ВПР, и работает с несколькими критериями

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

COUNT просто подсчитывает количество непустых ячеек в диапазоне.

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

Практически то же самое, что и СУММЕСЛИ, только проще: он подсчитывает количество значений, удовлетворяющих определенному условию.

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

Шаг 1. Преобразуйте исходные данные в отформатированную интеллектуальную таблицу.

Выделите таблицу со статьями и преобразуйте ее в умную таблицу: перейдите в Главное меню -> Форматировать как таблицу.

В появившемся окне обязательно установите флажок «Таблица с заголовками». Если нет, наденьте его.

Назовите таблицу: На вкладке «Дизайн» введите имя таблицы — «Статьи».

Отформатированная таблица «статьи» создана.

Шаг 2. Создайте две сводные таблицы: одну с именами групп, другую с элементами.

Для чего мы используем сводные таблицы? Во-первых, чтобы не создавать вручную список групп, а во-вторых, как было сказано выше, не сортировать вручную каталоги статей (что пользователи иногда забывают делать, и это важно, иначе формула СМЕЩЕНИЕ «срабатывает» с ошибкой). «Ручную» работу сделает за нас кнопка «Обновить» в меню «Данные» — мы нажимаем ее каждый раз, когда появляются новые статьи.

Создайте свою первую сводную таблицу с группами элементов.
Выделите любую ячейку таблицы с исходными данными, перейдите в меню «Вставка» -> «Сводная таблица». Добавьте сводную таблицу к существующему листу и разместите группы в области строк.

Создайте вторую сводную таблицу со статьями: меню «Вставка» -> «Сводная таблица». Разместите группы и статьи в области строк.

Форматируем сводную таблицу статьями и делаем ее похожей на справочник.
Выделите любую ячейку в таблице, перейдите на вкладку «Дизайн» -> «Макет отчета» -> «Показать в табличной форме». У нас почти получится нужная нам таблица, но в ней автоматически появятся промежуточные итоги. Чтобы отключить их, перейдите в: Промежуточные итоги -> Не отображать промежуточные итоги.

Скройте строку «Общий итог» в обеих таблицах поиска. Перейдите на вкладку «Дизайн» -> «Общие итоги» -> «Отключить для строк и столбцов.

В результате у вас получится два каталога, как на изображении ниже. Для удобства расположите таблицы рядом друг с другом на одном листе — из первой строки и в столбцы A, C и D, как на рисунке (это поможет вам понять формулу СМЕЩЕНИЯ).

Шаг 3. Создайте именованные диапазоны с помощью диспетчера имен.

Откройте диспетчер имен: в меню Формулы -> Диспетчер имен.

В появившемся окне нажмите кнопку «Создать».

- Введите имя «GroupList» и формулу, которая будет определять диапазон:
  = СМЕЩЕНИЕ (1,1,0 $ A $, СЧЁТ (A $ A: A) -1,1)

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

OFFSET ($ A $ 1; 1; 0; COUNT ($ A: $ A) — 1; 1) — определяет адрес ячеек с именами групп.

- $ A $ 1 — первая ячейка в ссылке на группу.
- Следующие числа — 1; 0 — это отступ от первой ячейки из 1 строки и 0 столбцов (отступ необходим, потому что первая ячейка содержит имя столбца).
- COUNT ($ A: $ A) — 1 Подсчитывает количество непустых ячеек в столбце A. Вычтите -1, поскольку имени столбца не должно быть в списке.
- Последняя 1 в формуле — это количество столбцов.

Щелкните ОК. Имена листов в формуле появятся сами по себе.

Аналогичным образом создайте список статей в диспетчере имен.
Введите имя для ArticleGroup и для диапазона введите формулу:
= СМЕЩЕНИЕ ($ C $ 1; ПОИСК ($ G2; $ C: $ C; 0) -1; 1; СЧЁТЕСЛИ ($ C: $ C, $ G2); 1)

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

OFFSET ($ C $ 1; SEARCH ($ G2; $ C: $ C; 0) — 1; 1; COUNTIF ($ C: $ C; $ G2); 1) — определяет адрес ячеек с именами статьи из группы, использующие ПОИСК, который ищет группу статей.

$ C $ 1 — первая ячейка в столбце группы.
MATCH ($ G2; $ C: $ C; 0) — 1 Определяет, сколько строк нужно сделать отступ от первой ячейки. ПОИСКПОЗ ищет имя выбранной группы в таблице с данными (столбец $ G) среди ячеек словаря (столбец $ C). В адресе ячейки $ G2 мы не «фиксируем» номер строки знаком $, чтобы формула работала для каждой ячейки в столбце.

Следующая цифра 1 — это отступ в 1 столбец вправо, например, перейдите в столбец «статьи», где вам нужно получить данные.
COUNTIF ($ C: $ C; $ G2) — подсчитывает количество ячеек в столбце $ C, где имена групп такие же, как в столбце данных. Опять же, мы не «фиксируем» номер строки ячейки $ G2 знаком $.
Последняя 1 в формуле — это количество столбцов.

Шаг 4. Создайте выпадающие списки.

Выделите ячейки в столбце «группы», перейдите в меню «Данные» -> «Проверка данных». Установите тип данных на Список, источник = ГруппыСписок.

То же самое и со статьями. Тип данных — Список, Источник = Группы статей

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