В данной статье мы научимся делать выпадающий список в Гугл таблицах, потренируемся его применять вместе с условным форматированием, используя встроенные инструменты Google Sheets.
- Для чего нужны выпадающие списки в Гугл таблицах
- Как сделать простой выпадающий список в Гугл таблицах
- Применяем выпадающий список на практике с использованием условного форматирования
Для чего же нужны выпадающие списки в Гугл таблицах?
Ну, во-первых списки очень облегчают работу с большим количеством одинаковых данных, когда вам в различных колонках необходимо использовать одни и те же значения, например количество проданных товаров в разные периоды времени. Товары-то одни и те же, а вот периоды разные, вот в таком случае к нам и спешат на помощь выпадающие списки.
Во-вторых, использование списков с заранее заготовленными значениями исключают ошибки при введении одинаковых данных. И потом, когда вам однажды понадобится делать проверки данных формулами, то допущенная помарочка в слове или цифре может натворить весьма немало неприятностей в расчетах. Тогда как списки из введенных единожды правильно данных исключат подобные казусы.
Как сделать простой выпадающий список в Гугл таблицах
Чтобы реализовать выпадающий список красиво и удобно (не испортив внешний вид таблицы), мы будем использовать два листа. Для этого давайте сперва добавим второй лист как это описано тут и переименуем их, как это описано в соответствующей статье здесь.
Лист на котором будет отображаться результат я так и назвал Результат, а лист, который сразу был под названием Лист 2, я назвал Данные, на нем я размещу исходные данные.
После того как мы сделали эти простые действия, приступим к заполнению данных. Для этого перейдем на лист который мы назвали Данные и добавим некоторые данные, у меня это Ягоды, Фрукты и Овощи, расположенные по порядку в ячейках A1:A3:
Теперь перейдем на наш главный лист Результат, где мы будем делать сам выпадающий список. Поставим курсор где нам необходимо, в моем случае разницы нет и я размещу выпадающий список в ячейке A3.
Теперь переходим в панели меню по следующему пути: Данные -> Проверка данных:
Откроется вот такое контекстное меню:
В котором мы видим следующие пункты:
- Диапазон ячеек – здесь мы видим название нашего листа и адрес ячейки в которой будет наш выпадающий список на данном листе;
- Правила – здесь мы будем задавать правила для отображения нашего списка. По умолчанию значение стоит Значения из диапазона, оно нам как раз и нужно, так что ничего не трогаем и оставляем как есть. А вот в поле справа от значения нам необходимо указать путь до наших данных на втором листе, в нашем случае это: ‘Данные’!A1:A3
Слово Данные – это ссылка на лист с нашими исходными данными, взятая в одинарные кавычки, затем восклицательный знак и номера ячеек с нашими данными. - Ниже мы видим чек бокс Показывать раскрывающийся список в ячейке – он выделен по умолчанию и это значит, что справа ячейки с нашим выпадающим списком будет треугольничек. Если он вам по каким-то причинам не нужен, то снимите чек бокс.
- Для неверных данных – здесь два радио бокса: показывать предупреждение и запрещать ввод данных. По умолчанию стоит показывать предупреждение и это значит, что если вы введете не соответствующее значение из исходных данных, то всплывет сообщение с ошибкой.
А если выберете запрещать ввод данных, то при неверном (несоответствующем) исходным данным значении появится предупреждающий pop-up с текстом «Данные, которые вы ввели в ячейку A3, не соответствуют правилам проверки». - Оформление – в данном пункте мы видим чекбокс «Показывать текст справки для проверки данных:» и ниже поле, где нам предлагается готовый вариант сообщения, который можно исправить на свое. Именно это сообщение будет всплывать при введении не правильных значений, по умолчанию стоит: «Введите значение из диапазона ‘Данные’!A1:A3»
Все! Жмем кнопку Сохранить и наслаждаемся результатом своего труда:
Выпадающий список в Гугл таблицах с использованием условного форматирования
Сделать-то мы сделали выпадающий список, но теперь нам необходимо потренироваться как его использовать в работе.
Теперь добавим немного магии и воспользуемся условным форматированием для того, чтобы мы могли налету отличать данные которые выбраны в той или иной ячейке с выпадающим списком.
Допустим у нас есть некие данные, в нашем случае это Ягоды, Фрукты и Овощи. У вас это могут быть другие данные, но не это главное. Если у нас приличное количество выпадающих списков с различными данными, то выглядит все достаточно запутанно и вообще поди пойми где и что.
Чтобы как-то разбавить эту серую массу данных, нам потребуется инструмент условного форматирования. При помощи его мы в несколько кликов раскрасим наши товары в соответствующие цвета и нам будет значительно проще различать где какой вид продукта выбран.
Для начала выделим весь диапазон, в нашем случае это A1:C20
Затем пройдем путь в меню: Формат -> Условное форматирование или кликнем правой кнопкой мыши и в открывшемся контекстном меню выберем Условное форматирование.
В открывшемся окне справа мы увидим что мы применять будем форматирование к диапазону A1:C20. Ниже в форме Форматирование ячеек выберем Текст содержит, еще ниже в поле введем, например, Фрукты. Сразу увидим, что наши ячейки, которые содержат слово Фрукты, окрасились в серый цвет — так Гугл таблицы по умолчанию окрашивают ячейки.
Но нам же надо окрасить в разные цвета разные ячейки, поэтому выберем ниже способ форматирования, а именно окрасим ячейки с фруктами, скажем, оранжевым цветом. Для этого выберем нужный цвет в форме с изображенным ведерком.
Жмем Готово, наслаждаемся свежими красками в нашей серой таблице!
Теперь повторим эти действия с другими данными, нажав на кнопку Добавить правило справа, только теперь вводим в поле не Фрукты, а Ягоды и на последнем этапе Овощи, и наблюдаем вот такую картину:
Теперь когда мы выберем нужные данные в выпадающем списке, ячейка эта будет окрашиваться в соответствующий цвет.
Вот и все, надеюсь статья была полезной. Если у вас есть еще какие-то вопросы, то пишите их в комментариях, я с удовольствием на них отвечу!
Отправить ответ
17 Комментарий на "Google таблицы — выпадающий список"
Здравствуйте. Приятно читать, когда всё разложено по полочкам. В продолжении темы, у меня возник следующий вопрос: а как тексту из выпадающего списка присвоить число, чтобы суммировать несколько выпадающих списков?
Не совсем понял вопрос
Я так думаю, что вопрос такой же как я задал ниже, т.е. если есть выпадающий список: Яблоки, груши, бананы, и в течении месяца я ставлю колличество сьеденых фруктов цифрами и в следующей колонки выбираю название фрукта из выпадающего списка, и в конце месяца хочу просумировать сколько бананов я сьел, или сколько яблок + груш я сьел.
Сделайте пост по связанным выпадающим спискам, пожалуйста
Сложная и большая тема, там надо все через костыли делать, если появится время напишу обязательно 🙂
Спасибо за статью. Такой вопрос: как сделать так, чтобы при выполнении условия в одной ячейке, менялся цвет закрашивания всей строки, её содержащей?
Очень просто! Проделайте эти манипуляции в разделе условное форматирование и у вас все получится: http://joxi.ru/82Q0J46i1WNYbm
Здравствуйте. Подскажите пожалуйста, как закрасить всю строку, никак не могу понять.
На текущий момент имею: столбец N, где каждая ячейка разделена на «правило» из 11 слов. При выбирании одного слова из 11 она закрашивается под определенный цвет.
Правило усл. форм.: применить к диапазону: N2:N2154.
Форматирование ячеек: Равно — 1 из 11 слов. Формат другое ок.
Вот таких правил 11 штук.
Какую формулу использовать, что бы при выборе слов, закрашивалась не одна ячейка а целая строчка??
Заранее благодарю.
Ответа не дождался и видимо не дождусь.
Решил вопрос следующим образом.
Применить к диапазону A1:M1574
ваша формула: =$M1=»забрать »
и все ровно
Ильнур, здравствуйте. Я редко читаю сообщения т.к. занят на работе днями. Решение вашей проблемы лежит в ссылках относительных и абсолютных, поиграйте с ними и должно получиться. Т.е. ваш первый вариант в первом сообщении в целом на верном пути, просто вам надо к одной из ячеек применить абсолютный путь, точно сейчас сказать не могу, поэтому поиграйте с ними на обоих ячейках.
Подскажите, если у меня идет в таблице расход денег, и есть 5 вариантов куда они могут идти, для обозначения этих 5 вариантов я создал выпадающий список, а как теперь мне быстро посчитать все расходы помеченные к примеру первым вариантом из выпадающего списка? Или сразу несколько к примеру мне надо суммировать все ячейки которые были помечены 3 выпадающими значениями.
спасибо
Добрый день!
долго курил следующий вопрос, но ответа пока не нашел. может, вы знаете ответ…
сделаем справочную таблицу из столбцов «ID» и «Description». можно ли в выпадающем списке показывать пользователю текстовые описания, типа «Фрукты» или «Овощи», из столбца «Description», а подставлять в ячейку уникальные ID, типа 1, 2 и т. д., из столбца «ID»?
если в Правилах выбрать диапазон, охватывающий оба столбца, то в выпадающем списке будет чересполосица типа:
1
Фрукты
2
Овощи
3
Ягоды
и т. д.
пытался в Правилах выбрать «Ваша формула» и воспользоваться функцией Query с запросом SQL, но при этом в принципе исчезает возможность показывать выпадающий список.
Это называется связанные выпадающие списки. Инструмента как такового у гугла нет, но есть хаки. Есть видео где можно посмотреть как это делается:
https://www.youtube.com/watch?v=YfVQ_udH7O4
https://www.youtube.com/watch?v=nhsl_iJbn40
Добрый день) Если я например хочу изменить значение из выпадающего списка, например, как в вашем примере, сначала были фрукты, но теперь нужно выбрать овощи, гугл не дает это сделать и выдает «Данные, которые вы ввели в ячейку H50, не соответствуют правилам проверки». Выпадающий список используется в качестве статусов, которые периодически меняются. Как быть?
Трудно сходу сказать, посмотреть бы вашу таблицу)
Благодарю. Очень полезно и доходчиво.