Как сделать выпадающий список в Excel?

Работая в Excel, пользователи имеют дело с постоянными и периодически меняющимися данными. К первым можно отнести список девяти сотрудников условной организации на Листе1 файла Пример3.xlsx.

Данные о получаемой ими зарплате, меняются (по мере роста квалификации и опыта) значительно чаще. Такие данные удобно хранить отдельно от списка сотрудников, например, на отдельном Листе2.

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

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

Выпадающий список с данными из перечня

Ниже речь пойдёт о ВЫПАДАЮЩЕМ СПИСКЕ С ДАННЫМИ по зарплате. Выпадающие списки бывают и у ряда инструментов на их Панели. Для исключения путаницы будем называть списки ИНСТРУМЕНТОВ не выпадающими, а раскрывающимися.

1. Выделить совокупность ячеек (B1–B10), в которые будет вставляться выпадающий список данных.

2. В Панели инструментов перейти на вкладку «Данные», открыть раскрывающийся список инструмента «Работа с данными», и щёлкнуть «Проверка данных».

3. Открыть список «Тип данных», и выбрать «Список».

В том же окне появляется поле «Источник», в котором должны быть вставлены ячейки с данными о зарплате на Листе2.

4. Щёлкнуть взятый в красную рамку пункт с маленькой наклонной красной стрелкой.

5. Перейти на лист2, выделить ячейки B1–10 и щёлкнуть взятый в красную рамку пункт с маленькой красной стрелкой, направленной вниз.

6. В окне «Проверка вводимых значений щёлкнуть OK.

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

Создание выпадающего списка вручную

Данные по зарплате необязательно хранить в определённых ячейках Excel. После выполнения шагов 1–3 первого раздела (см. выше), их можно непосредственно набирать в поле «Источник», разделяя точкой с запятой.

Создание выпадающего списка данных посредством функции СМЕЩ

В категории «Ссылки и массивы» Excel есть функция СМЕЩ, которую можно использовать для организации выпадающих списков данных.

Синтаксис функции СМЕЩ

Первые три аргумента, выделенные жирным шрифтом, – обязательны. Ссылка – адрес ячейки начала отображения. Смещения по строкам и столбцам – путь к ней. Под высотой подразумевается количество ячеек отображения (в нашем случае – 10), а под шириной – число столбцов, если их более одного.

Разобравшись с синтаксисом, прейдём к его конкретному использованию.

1. Выделить ячейку C1 на Листе2, начиная с которой будет виден создаваемый выпадающий список данных.

2. Выполнить шаги 2 и 3 первого раздела.
3. Щёлкнуть в поле «Источник» и ввести в него следующую формулу.

Первый аргумент – ячейка начала перечня данных. Нули второго и третьего аргументов – отсутствие смещения по стокам и столбцам, число 9 четвёртого аргумента – количество зарплат.

4. После нажатия OK на Листе2 появится список, созданный при помощи функции СМЕЩ.

Динамическое изменение данных выпадающего списка

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

1. Выделить список данных для создаваемого выпадающего списка. В качестве таковых возьмём список фамилий и имён работников организации на Листе1.

2. На Панели инструментов щёлкнуть «Форматировать как таблицу», затем – один из предлагаемых стилей для неё.

3. В появившемся окошке «Форматирование таблицы» щёлкнуть OK для подтверждения диапазона ячеек.

Выделенный диапазон принял формат таблицы, выбранный при выполнении шага 2.

4. Выделить диапазон данных, взятых на скриншоте в красную рамку. Как видно, программа Excel воспринимает его под именем «Таблица1», которое нас устраивает.

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

5. Выбрать ячейку для представления выпадающего списка, например, C1.
6. Перейти на вкладку «Данные», щёлкнуть инструмент «Работа с данными», затем – дважды «Проверка данных».

7. В окне «Проверка вводимых значений» открыть «Тип данных», и выбрать «Список».
8. В появившемся поле «Источник» щёлкнуть взятый в красную рамку пункт с маленькой наклонной красной стрелкой.

9. Выделить данные таблицы, для которой создаётся выпадающий список, затем – щёлкнуть взятый в красную рамку пункт с маленькой красной стрелкой, направленной вниз.

На Листе1, рядом с ячейкой C1, появляется треугольник созданного выпадающего списка.

Добавленный в таблицу «Романов Виктор» появится и в выпадающем списке. При удалении записи изменение произойдёт и выпадающем списке. Таким образом, цель достигнута.

Взаимосвязанные выпадающие списки

Выпадающие списки можно связывать друг с другом. Например, выбрав значение из «управляющего» списка, отображать разные данные списка «управляемого». Рассмотрим, как это сделать, на основе данных Листа3 файла Пример3.xlsx. В нём – раздельные списки сотрудниц и сотрудников организации. Цель – создать два связанных выпадающих списка. При выборе «женщин» или «мужчин» в первом из них, второй должен отображать список соответственно сотрудниц или сотрудников.

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

1. Выделить все заполненные ячейки столбцов A и B.
2. Перейти на вкладку «Формулы», открыть раскрывающийся список инструментов «Определённые имена», и щёлкнуть «Создать из выделенного».

3. В появившемся окошке «Создание имён…» поставить галочку «в строке выше» и щёлкнуть OK.

4. Выделить свободную ячейку, например, D1, и создать в нём первый выпадающий список из имён диапазонов «Женщины» и «Мужчины»., как это делалось выше. естественно, что в качестве «Источника» должны быть указаны имена заглавных ячеек «Женщины» и «Мужчины».

При создании второго выпадающего списка надо будет одновременно связать его с первым.

5. Выделить свободную ячейку, например, F1.
6. Как обычно, перейти на вкладку «Данные» и щёлкнуть «Проверка данных».

7. В новом окне, как всегда, выбрать «Список».
8. А вот в качестве «Источника» ввести готовую функцию =ДВССЫЛ(D1), ссылающуюся на ячейку D1 с уже готовым первым выпадающим списком, затем нажать OK.

Поскольку второй выпадающий список управляется первым, то он появится в ячейке F1 только после выбора в первом списке одного из имён. Выбор в ячейке D1 «Женщины» потянет за собой данные этого именованного списка в виде фамилий и имён пяти сотрудниц, как это видно на скриншоте.

Ниже представлен синтаксис функции ДВССЫЛ категории «Ссылки и массивы».

Выделение ячеек Excel с выпадающими списками

Индикатор выпадающего списка – треугольник, не сохраняется при переходе к другим ячейкам, или при закрытии и последующем открытии файла Excel. Но при необходимости нетрудно выделять ячейки с выпадающими списками.

1. На вкладке «Главная» открыть раскрывающийся список инструмента «Редактирование»
2. Открыть список инструмента «Найти и выделить».
3. Щёлкнуть инструмент «Выделить группу ячеек».

4. В одноимённом окне активировать «Проверка данных», затем – «всех», и щёлкнуть OK.

Результат произведённых действий представлен на скриншоте. Как видно, выделена ячейка C1, в которой был недавно создан выпадающий список. При наличии нескольких таких ячеек будут отображены все.

Создание выпадающего списка на основе данных внешнего источника

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

Единственный обязательный аргумент – ссылка на ячейки, расположенный в другой книге (файле) Excel.

Предположим, что имя внешнего файла – Другой.xlsx, а данные, на основе которых создаётся выпадающий список, находятся в ячейках A1–A10 его Листа3. В таком случае в поле «Источник» должно быть введено следующее выражение:

=ДВССЫЛ(“[Другой1.xlsx]Лист1!$A$1:$A$10”),

причём файл Другой1.xlsx должен быть открыт.

Выпадающий список с поиском

Рассмотренные выше выпадающие списки создавались на основе существующих обычных списков данных, в частности, сотрудников гипотетической организации. Такие списки могут быть очень длинными. При необходимости ненужные записи можно отфильтровать посредством функции ПОИСК. Её синтаксис выглядит так.

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

Предположим, что на Листе1 файла Пример3.xlsx нам нужно «пропустить» только тех сотрудников, в фамилиях или именах которых встречаются буквы «ро».

Щёлкнуть свободную ячейку, например, C2, затем вызвать функцию ПОИСК, и заполнить обязательные аргументы, как показано на скриншоте (верхний аргумент писать без кавычек), затем щёлкнуть OK.

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

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

К счастью, функциональные возможности Excel очень велики. Например, посредством функции ЕЧИСЛО числа можно преобразовать в удобную логическую форму, как показано на скриншоте.

Очевидно, что в подлежащий созданию выпадающий снимок пройдут только «истинные» записи.