Выпадающий список в Еxcel - как сделать и настроить

Данные из ячеек

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

1. Выбрать ячейку.
2. Последовательно открыть в меню вкладки: «Данные» → «Проверка».
3. После предыдущих действий откроется диалоговое окно. В поле «Параметры» в качестве проверочного критерия нужно выбрать строку «Список». После этого появится поле источника. Можно вручную ввести параметры значений, например, = $ B $ 2: $ B $ 8, где буквы — столбцы, а цифры — строки. Другой способ: кликнуть на поле «Источник», мышью выделить ячейки и нажать Enter. По умолчанию данные переместятся в C2.

Чтобы всё работало правильно, диапазон ссылок на ячейки должен быть абсолютным, например, $ D $ 5, а не относительным — C3 или C $ 3.

Ввод вручную

Если нужно показать всего 2 или 3 опции, тогда этот вариант будет оптимальным. В поле источника проверки необходимо ввести свои данные, например, «Включить» и «Отключить». Последовательность действий следующая:

1. Выбрать ячейку, где будут находиться элементы.
2. Перейти в «Проверку данных», последовательно открыв в панели управления вкладку «Данные».
3. На вкладке «Параметры» в диалоговом окне выбрать «Список».
4. Появится поле источника, куда и нужно ввести «Включить» и «Отключить», разделяя слова запятой. Перед тем как нажать Ok, стоит убедиться, что галочка в ячейке установлена.

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

Формулы Excel

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

1. Определить ячейку.
2. Зайти в инструмент «Проверка данных».
3. Выбрать «Список» на вкладке «Параметры» в открывшемся диалоговом окне.
4. В появившееся поле «Источник» ввести формулу: =СМЕЩ ($ B $ 3,0,0,6).
5. Перед тем как нажать Ok, стоит убедиться, что флажок на листе появился.

Для правильной работы формулы требуется несколько значений. Ссылка — начальная точка списка, строки и столбцы указываются как 0, поскольку смещать ссылочную массу не требуется. Последнее значение (в примере — 6) — количество элементов.

С помощью этого метода можно создать динамический выпадающий список, который при подстановке новых элементов будет обновляться в автоматическом режиме. Для этого необходимо внести некоторые коррективы в формулу: в поле источника вставить значения =СМЕЩ ($ B $ 3,0,0, СЧЁТЕСЛИ ($ B $ 3: $ B $ 100, «<>")).

Здесь появляется новая функция СЧЁТЕСЛИ, задача которой заключается в том, чтобы считать непустые ячейки в заданном диапазоне (в примере В3: В100). Чтобы всё правильно работало, не должно быть между заполненными ячейками пустых.

Если нужно список из A2 применить, например, к А3: A6, то это решается копированием и вставкой. Когда копия форматирования не нужна, последовательность действий следующая:

• скопировать ячейку со списком;
• выбрать поля, где будет размещаться копия;
• перейти в панель управления → «Вставить» → «Специальная вставка»;
• в диалоговом окне отметить «Параметры»;
• нажать Ok.

При этом копируется в Excel выпадающий список в ячейке, а не условия её форматирования.

Связанные или зависимые

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

1. Определить ячейку для основного перечня.
2. Пройдя уже знакомый путь, открыть диалоговое окно «Проверки данных» и выбрать «Список».
3. В поле «Источник» надо указать диапазон, содержащий элементы, которые будут отображаться в первом меню.
4. Нажать Ok. Один готов!
5. Выделить оба набора данных.
6. Перейти в «Формулы», найти команду «Создать из выделения» (можно использовать сочетание клавиш Ctrl + Shift + F3).
7. Во всплывающем окне «Создание имени» установить флажок «Верхняя строка» и удалить со всех остальных. При этом создаются 2 диапазона имён.
8. Нажать Ok.
9. Выберать ячейку, для которой требуется список.
10. Выполнить уже знакомые действия, которые приведут к диалоговому окну «Проверка данных». В поле «Источник» нужно ввести формулу = ДВССЫЛ (E5). Здесь Е5 — место, где должно выпасть меню. Затем Ok.

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

Когда категория состоит более чем из одного слова, в составление формулы надо включить функцию = ДВССЫЛ (ПОДСТАВИТЬ (Е5, «", «_")). Причина в том, что Excel не допускает пробелы в именованных диапазонах. Такой метод позволит убрать их и преобразовать в подчёркивания.

Поле поиска

Фильтр — наиболее часто используемая функция при работе с некоторым объёмом данных. Очевидно, что удобнее всего не пролистывать пол-листа в поиске нужного элемента, а воспользоваться поисковым полем. Сделать его можно всего за 3 шага:

1. Получение уникального списка. Необходимо выбрать все элементы, перейти во вкладку «Данные» и нажать кнопку «Удалить дубликаты». В открывшемся диалоговом окне выбрать столбец, в котором есть список, кликнуть Ok. Затем надо создать именованный диапазон. Для этого нужно выделить все данные, кликнуть по ним правой кнопкой мыши. В открывшемся меню выбрать «Присвоить имя». Заполнение поля «Имя» имеет особенность: не должно быть пробелов и цифр в начале. Диапазон будет выглядеть, к примеру, так: = Лист! $ A $ 2: $ A $ 9.
2. Создание окна. Сначала необходимо через настройку ленты активировать вкладку «Разработчик». В ней выбрать «Элементы управления» → «Вставить» → «Элементы управления ActiveX» → «Поле со списком». Затем на любом месте в листе кликнуть, после чего программа вставит поле. По нему надо щёлкнуть правой кнопкой мыши и выбрать «Свойства». В открывшемся окне внести в ListFillRange диапазон значений, который создан в шаге первом. Для строки ListRows используются данные, плюс количество столбцов в ColumnCount.
3. Настройка. Производится с помощью вспомогательных столбцов.

Можно скрыть исходные данные и вспомогательные столбцы, чтобы показывать только отфильтрованные записи.

Также можно разместить необработанные данные и вспомогательные столбцы на другом листе и там же создать динамический фильтр Excel. Настройка его довольно гибкая: если необходимо несколько окон поиска, их легко создать, выполнив те же шаги с некоторыми изменениями в формуле.

Радиокнопка в Excel, также называемая опциональной кнопкой, может использоваться для выбора вариантов или опций. Многие видели это на сотнях веб-страниц, где пользователей просят выбрать опцию, нажав на маленькую круглую форму рядом с текстом. Как только её кликают, появляется чёрная точка (обозначение, которое отмечено). То же можно создать в таблице, воспользовавшись вкладкой «Разработчик». Таких интерактивных элементов существует несколько видов в меню «Элементы управления формы».