Как сделать выпадающий список в Excel и Google Таблицах

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

Разобрались, как заполнить выпадающий список в Excel и Google Таблицах. А также использовать его, чтобы настроить функции, фильтрацию или сортировку. 

Зависимый. Список, который формируется на основе столбца или листа с данными. Например, с именами и фамилиями сотрудников, клиентами или подрядчиками, статусами задач. 

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

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

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

Как настроить выпадающий список в Excel

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

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

Чтобы другой пользователь сразу видел, что ячейки нельзя заполнить вручную и просто вписать данные, преобразуем столбец со статусом в таблицу. Нажимаем на букву, обозначающую столбец. Затем кликаем последовательно: «Вставка» → «Таблица» → «Таблица с заголовками» → «ОК».

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

Снова выделяем столбец, затем зажимаем Ctrl и кликаем по названию — так мы исключаем слово «Статус» из элементов будущего списка. 

Затем нажимаем в меню сверху раздел «Данные» и кликаем по инструменту «Проверка данных». В появившемся окошке будет предупреждение о том, что выделенная область содержит ячейки без условий значения. Нажимаем «Да».

Дальнейшие шаги зависят от вида списка, который мы выбрали — зависимый или независимый.

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

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

С раскрывающимся списком можно сразу начать работу — выбрать значение и применить его к ячейке. Чтобы изменить цвет элемента, кликаем на столбец, переходим в меню сверху на главную и последовательно нажимаем: «Условное форматирование» → «Правила выделения ячеек» → «Равно». 

Затем в появившемся меню «Равно» даем адрес ячейки из исходного списка и указываем цвет заливки. Повторяем для каждого элемента выпадающего списка.

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

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

В открывшемся меню «Проверка вводимых значений» кликаем на «Источник» и вручную вводим варианты для выпадающего списка, обязательно разделив их точкой с запятой. Например: «Свободно;В работе;Опубликовано».

С выпадающим списком уже можно работать. Чтобы изменить цвет ячеек в зависимости от выбранного элемента, переходим на главную в меню сверху и нажимаем по порядку: «Условное форматирование» → «Правила выделения ячеек» → «Равно».

В открывшемся меню вписываем варианты для списка по одному и выбираем цвет.

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

Как настроить выпадающий список в Google Таблицах

Для примера будем вести таблицу с задачами маркетингового агентства. Наши клиенты и стоимость работ отличаются, а вот для столбца «Статусы» можно настроить постоянные варианты.

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

После чего кликаем правой кнопкой мыши и выбираем «Раскрывающийся список».

Действия дальше зависят от того, какой список мы хотим создать — зависимый или независимый. По каждому варианту есть советы в официальной Справке от Google.

Зависимый. В меню справа выбираем «Критерии», затем «Раскрывающийся список (из диапазона)». После этого действия появится строчка со значком таблицы — кликаем на него, затем сервис отобразит задачу: «Выберите диапазон данных». Кликаем сначала по листу с данными, затем по столбцу.

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

Когда все готово, нажимаем «ОК».

Сервис оставляет нас на листе с исходными данными, но в правой панели «Правила проверки данных» добавляет новое меню. Можно задать цвет каждому элементу списка и настроить общий стиль показа ячеек — через расширенные настройки. Выбираем стиль, нажимаем «Готово» и возвращаемся на лист.

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

Независимый. Повторяем шаги, которые мы указали выше — выделяем диапазон  и выбираем «Раскрывающийся список». В меню справа сразу указываем варианты статуса задачи. Можно выбрать цвет и стиль оформления ячеек списка. После заполнения нажимаем «Готово». 

Дальше столбец можно быстро заполнить с помощью раскрывающегося списка. Чтобы его изменить, кликаем на значок карандаша под возможными вариантами. 

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

Как совместить выпадающий список с другими инструментами

Выпадающий список — это уже хороший инструмент для упрощения работы в таблицах. Но его можно сделать еще интереснее, если совместить с другими возможностями Excel и Google Таблиц. 

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

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

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

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

Как еще больше упростить работу

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