Как сделать выпадающий список с автоматической подстановкой данных в Microsoft Excel

Когда вы работаете с большими таблицами в Microsoft Excel, вам часто приходится вводить те же данные снова и снова. Не только это утомительно, но и чревато ошибками. Представьте себе, что вы вручную печатаете имена сотрудников или товаров в списке.

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

В этой статье мы разберем, как сделать такой «умный» список за несколько минут. Он значительно ускорит вашу работу, сделав ее более профессиональной и аккуратной.

Способ 1: Создание простого выпадающего списка с проверкой данных

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

1. Выделите ячейку B2, где должен располагаться выпадающий список с названиями отделов. Перейдите на вкладку «Данные» и найдите группу «Работа с данными».
2. 
3. Нажмите кнопку «Проверка данных» в верхней части ленты инструментов. Откроется диалоговое окно настройки ограничений для выбранной ячейки.
4. 
5. В поле «Тип данных» выберите вариант «Список». Данная настройка позволит создать выпадающий список с заранее определенными значениями.
6. 
7. В поле «Источник» введите варианты через точку с запятой: IT;Продажи;HR;Маркетинг (вам же нужно ввести нужное количество своих пунктов списка). Каждое значение будет доступно для выбора в выпадающем списке.
8. 
9. Убедитесь, что установлена галочка «Список допустимых значений» для отображения стрелки выпадающего списка. Снимите галочку с пункта «Игнорировать пустые ячейки», если требуется обязательное заполнение поля.
10. 
11. Нажмите «OK» для применения настроек. В ячейке B2 появится стрелка, указывающая на наличие выпадающего списка с вариантами отделов.
12. 
13. Скопируйте ячейку B2 и вставьте форматирование в остальные ячейки столбца «Отдел». Выделите нужный диапазон, нажмите Ctrl + V и выберите «Специальная вставка» — «Условия на значения».
14. 

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

Способ 2: Использование именованного диапазона для выпадающего списка

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

1. Создайте вспомогательную область на листе или отдельном листе. В диапазоне H1:H4 введите названия отделов: «IT», «Продажи», «HR», «Маркетинг».
2. 
3. Выделите созданный диапазон H1:H4 и перейдите в поле имени слева от строки формул. Введите название Отделы и нажмите Enter для создания именованного диапазона.
4. 
5. Вернитесь к ячейке B2 и откройте «Проверка данных» через вкладку «Данные». В поле «Источник» введите формулу =Отделы вместо списка значений.
6. 
7. Примените настройки нажатием кнопки «OK». Выпадающий список теперь ссылается на именованный диапазон, что позволяет изменять варианты через редактирование ячеек H1:H4.
8. 
9. Для добавления нового отдела расширьте диапазон до H5 и введите дополнительное значение. Выделите обновленный диапазон H1:H5, перейдите в «Диспетчер имен» на вкладке «Формулы» и измените область действия имени «Отделы».
10. 

Если список не отображается или содержит ошибку, проверьте правильность указания диапазона в поле «Источник». Убедитесь, что ссылка на ячейки использует абсолютную адресацию со знаками доллара, например, «1 доллар США — 10 долларов США».

Способ 3: Автоматическая подстановка данных на основе выбора из списка

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

1. Создайте справочную таблицу в диапазоне H1:I5 с именами сотрудников в столбце H и соответствующими отделами в столбце I. Например: H1 — «Иванов И.И.», I1 — «IT».
2. 
3. В ячейке A2 создайте выпадающий список с именами сотрудников, используя проверку данных. В поле «Источник» укажите диапазон $H$2:$H$5 или создайте именованный диапазон «Сотрудники».
4. 
5. В ячейке B2 введите формулу автоматической подстановки отдела: =ВПР(A2;$H$2:$I$5;2;ЛОЖЬ). Формула найдет выбранного сотрудника в справочной таблице и вернет соответствующий отдел.
6. 
7. Скопируйте формулу из B2 в остальные ячейки столбца B. При выборе сотрудника из выпадающего списка в столбце A автоматически отобразится его отдел в столбце B.
8. 
9. Для обработки пустых ячеек оберните формулу в функцию ЕСЛИ: =ЕСЛИ(A2="";"";ВПР(A2;$H$1:$I$5;2;ЛОЖЬ)). Такая конструкция предотвратит отображение ошибки #Н/Д в незаполненных строках.
10. 

Проверьте, может ли формула ВПР возвращать ошибку #Н/Д, используя функцию СЖПРOБЕЛЫ: =ВПР(СЖПРOБЕЛЫ(A2);$H$1:$I$5;2;ЛОЖЬ).

Способ 4: Создание связанных выпадающих списков

Создание такой системы требует использования функции ДВССЫЛ и именованных диапазонов для каждой категории данных. Это позволяет ограничивать выбор во втором списке в зависимости от значения, выбранного в первом списке.

1. Создайте отдельные именованные диапазоны для каждого отдела. Для IT-отдела в диапазоне J1:J3 введите должности: «Программист», «Системный администратор», «Тестировщик». Выделите диапазон и создайте имя IT.
2. 
3. Аналогично создайте диапазоны для других отделов: «Продажи» с должностями «Менеджер», «Консультант», «Директор по продажам»; «HR» с вариантами «Рекрутер», «HR-менеджер», «Специалист по кадрам».
4. 
5. В столбце B настройте выпадающий список с отделами по предыдущим инструкциям. В другом свободном столбце создайте второй список, в поле «Источник» которого укажите формулу =ДВССЫЛ(B2).
6. 
7. При выборе отдела в столбце B, список в столбце C автоматически покажет соответствующие должности. Функция ДВССЫЛ обращается к именованному диапазону, название которого совпадает с выбранным значением.
8. Для корректной работы убедитесь, что названия отделов в выпадающем списке точно совпадают с именами созданных диапазонов. Избегайте пробелов и специальных символов в названиях диапазонов.

Если связанные списки не работают правильно, проверьте, что имена диапазонов функции ДВССЫЛ не содержат пробелов и специальных символов. Если необходимо, замените пробелы на подчеркивания в названиях отделов и соответствующих именах диапазонов.

Как вы можете видеть, создание умных выпадающих списков с использованием автоподстановки в Microsoft Excel — не такая уж сложная задача. Чтобы добиться успеха, необходимо правильно подготовить исходные данные, а также использовать связку функций ДВССЫЛ и ПРОВЕРКА ДАННЫХ. Этот инструмент значительно ускорит вашу работу и поможет вам избежать ошибок при вводе.

Не бойтесь пробовать что-то новое. Попробуйте использовать этот подход для своих задач, включая списки товаров, клиентов или любые другие часто используемые данные. Со временем это станет вашей привычной и неотъемлемой практикой.

Если вы освоите этот прием, вы станете еще более эффективным пользователем Microsoft Excel. Благодаря доверию рутине программы ваши таблицы станут удобнее, а вы сможете сосредоточиться на более важных вещах.