Как в Гугл Таблице сделать выпадающий список в ячейке: инструкция

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

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

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

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

• Левой кнопкой мыши (ЛКМ) кликаем на нужную ячейку или выделяем сразу несколько вниз.
• По выделенному участку щелкаем правой кнопкой мыши (ПКМ) и в меню выбираем “Проверка данных”.
• В окне “Проверка данных” выставляем значения по таблице ниже ↓

• Жмем на сохранить.

Готово. Теперь вы знаете, как создать выпадающий список в Google Таблицах.

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

Еще о работе с выпадающим списком

С тем, как сделать в Гугл Таблицах выпадающий список, мы разобрались. Осталось упомянуть еще несколько вариантов настроек, доступных для использования. В окне “Проверка данных”, в строке “Правила”, вы можете выбрать следующие настройки:

• Число → В диапазоне (Не в диапазоне, Больше, Больше или равно, Меньше, Меньше или равно и т. д.) → вписать числа.
• Текст → Содержит (Не содержит, Равняется, Является допустимым URL / адресом электронной почты) → вписать нужный текст.
• Дата → Является допустимой датой (Равняется, До, После, Указана или до и т. д.) → указать дату.

Обратите внимание: ячейки можно подсвечивать разными цветами (и в зависимости от содержимого в том числе. Для этого выделите ПКМ одну или несколько ячеек, выберите “Условное форматирование” и в форме справа назначьте правила выделения цветом.

Добавить цвет в выпадающий список

Одна из лучших особенностей Google Sheets – возможность настраивать электронную таблицу. Например; Если что-то срочно, вы можете покрасить клетки в красный цвет. Чтобы сделать это с помощью раскрывающегося списка, выполните следующие действия:

1. Перейдите и щелкните стрелку раскрывающегося списка.
2. Из меню, которое появляется; прокрутите вниз и выберите «Условное форматирование».
3. С правой стороны Google Sheets появится всплывающее окно: в правом нижнем углу вы увидите иконку с ведром с краской; нажмите на это
4. Выберите из доступных вариантов цвета.
5. Нажмите «Готово»

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

Группируем данные с помощью GROUP BY и PIVOT

Сгруппировать данные, используя QUERY, можно с помощью двух ключевых слов: GROUP BY и PIVOT, ниже рассмотрим примеры с ними.

Таблица, с которой мы будем работать:

Задачей будет вывести сумму продаж по каждой тематике, то есть сгруппировать данные по столбцу B.

Начнем с GROUP BY, текст функции будет таким:

=QUERY(‘Книги’!A1:C6;”select B, sum(C) group by B”)

Обратите внимание: чтобы функция работала, помимо группировки (group by B) нужна хотя бы одна аггрегирующая функция, в нашем случае это sum(C). Напишу, на всякий случай, все аггрегирующие функции для QUERY: sum(), max(), min(), avg() и count().

Результат нашей формулы:

С помощью GROUP BY возможна группировка и по нескольким столбцам, для этого просто перечислите их, как в функции ниже и не забудьте добавить эти столбцы в SELECT:

Группировка с помощью PIVOT.

Обратите внимание, что здесь в SELECT не нужно писать столбец B, по которому данные будут сгруппированы.

Пока отличие в том, что сгрупированные элементы расположены по столбцам, а не по строкам, как в GROUP BY.

Добавим еще один столбец для группировки.

Видите – два сгруппированных столбца отображаются в одном поле через запятую. В этом ключевое отличие PIVOT от GROUP BY, если там каждый столбец группировки занимает отдельный столбец, то в PIVOT получается нечто вроде сводной таблицы с уникальными полями из нескольких элементов. По этим полям, кстати, потом можно довольно просто искать нужное значение с помощью ГПР или ПОИСКПОЗ.

Устранение неполадок в таблицах Google

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

Связанные выпадающие списки

В настоящий момент нет функционала, позволяющего создавать связанные выпадающие списки в Гугл Таблицах — подобно тому, как это делается в Excel. Пользователи вынуждены писать специальные скрипты и прикреплять их к таблице. Только так содержимое ячеек “ведет себя” нужным образом. Это требует определенных знаний в программировании — даже для того, чтобы взять готовый скрипт из сети и подогнать его под свои потребности.

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

Обратите внимание: между знаками “//” находится пояснительная информация — ее можно не удалять, на работоспособность скрипта она не влияет. Вы изменяете только цифры рядом с пояснениями, чтобы сделать в Гугл Таблице связанный выпадающий список.

Подготовка таблицы

Сначала нужно открыть новый документ в Гугл Таблицы и  создать в нем еще один лист (“+” в левом нижнем углу окна). Переименовать Лист 1 в “Результат”, а Лист 2 в “Данные”.

Первый этап — работа с листом “Данные”. Как в Гугл Таблицах сделать выпадающий список в ячейке:

1. В ячейке А1 указываем название / характеристику первого уровня списка. Начиная с А2 вводим соответствующие значения. Повторений каждого должно быть столько, сколько будет для него значений второго уровня.
2. В ячейке D1 указываем название / характеристику первого уровня списка. Начиная с D2 вводим соответствующие значения (по одному разу).

Выглядит все это вот так:

Второй этап, работа с листом “Результат”:

• Переходим к листу “Результат” и выделяем интересующий диапазон ячеек. В данном случае — от А2 и до А13 включительно.
• Кликаем ПКМ по выделенному полю и в меню выбираем “Проверка данных”.
• В окне “Проверка данных” первую строку не изменяем. Ставим курсор в поле правее “Значения из диапазона” → переключаемся на лист “Данные” → выделяем значения из столбца D, начиная с D2 + столько пустых ячеек вниз, сколько нужно → Ок → Сохранить.

Что мы видим перед нажатием на “Сохранить”:

Подготовка окончена. Осталось прикрепить скрипт к таблице.

Как сделать простой выпадающий список в Гугл таблицах

Чтобы реализовать выпадающий список красиво и удобно (не испортив внешний вид таблицы), мы будем использовать два листа. Для этого давайте сперва добавим второй лист как это описано тут и переименуем их, как это описано в соответствующей статье здесь.

Лист на котором будет отображаться результат я так и назвал Результат, а лист, который сразу был под названием Лист 2, я назвал Данные, на нем я размещу исходные данные.

После того как мы сделали эти простые действия, приступим к заполнению данных. Для этого перейдем на лист который мы назвали Данные и добавим некоторые данные, у меня это Ягоды, Фрукты и Овощи, расположенные по порядку в ячейках A1:A3:

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

Теперь переходим в панели меню по следующему пути: Данные -> Проверка данных:

Откроется вот такое контекстное меню:

В котором мы видим следующие пункты:

• Диапазон ячеек – здесь мы видим название нашего листа и адрес ячейки в которой будет наш выпадающий список на данном листе;
• Правила – здесь мы будем задавать правила для отображения нашего списка. По умолчанию значение стоит Значения из диапазона, оно нам как раз и нужно, так что ничего не трогаем и оставляем как есть. А вот в поле справа от значения нам необходимо указать путь до наших данных на втором листе, в нашем случае это: ‘Данные’!A1:A3
  Слово Данные – это ссылка на лист с нашими исходными данными, взятая в одинарные кавычки, затем восклицательный знак и номера ячеек с нашими данными.
• Ниже мы видим чек бокс Показывать раскрывающийся список в ячейке – он выделен по умолчанию и это значит, что справа ячейки с нашим выпадающим списком будет треугольничек. Если он вам по каким-то причинам не нужен, то снимите чек бокс.
• Для неверных данных – здесь два радио бокса: показывать предупреждение и запрещать ввод данных. По умолчанию стоит показывать предупреждение и это значит, что если вы введете не соответствующее значение из исходных данных, то всплывет сообщение с ошибкой.
  А если выберете запрещать ввод данных, то при неверном (несоответствующем) исходным данным значении появится предупреждающий pop-up с текстом «Данные, которые вы ввели в ячейку A3, не соответствуют правилам проверки».
• Оформление – в данном пункте мы видим чекбокс «Показывать текст справки для проверки данных:» и ниже поле, где нам предлагается готовый вариант сообщения, который можно исправить на свое. Именно это сообщение будет всплывать при введении не правильных значений, по умолчанию стоит: «Введите значение из диапазона ‘Данные’!A1:A3»

Все! Жмем кнопку Сохранить и наслаждаемся результатом своего труда:

Удалить или редактировать существующее выпадающее меню

Если вы хотите обновить выпадающее меню или удалить его, вы можете выполнить следующие инструкции:

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

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

Кейс «QUERY и выпадающий список»

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

Итак, выпадающий список. Вначале создадим новый лист (допустим, наша исходная таблица огромна, и всю аналитику мы хотим производить на другом листе). Кликаем правой кнопкой мыши на ячейку А1, выбираем Проверка данных.

В Правилах выбираем Значение из списка, перечисляем все наши тематики через запятую и нажимаем Сохранить:

Список получился вот таким:

В соседнюю ячейку А2 впишем следующую формулу:

=QUERY(‘Книги’!A1:C13,”SELECT A, C WHERE B = ‘”&A1&”‘ ORDER BY C DESC”)

И разберем ее по частям:

• ‘Книги’!A1:C13 — исходный диапазон, таблица с продажами, книгами и тематиками.
• SELECT A, C — в сформированную функцией таблицу попадут данные из этих столбцов, то есть названия книг и продажи.
• WHERE B = ‘”&A1&”‘ отбирает только те книги, тематика (в столбце B) которых соответствует указанной в ячейке A1. Обратите внимание на синтаксис: текст из ячейки указывается между апострофов, которые относятся к тексту запроса. После них идут кавычки (мы закрываем текст запроса), амперсанд (присоединяем к тексту запроса текст из ячейки), адрес ячейки, еще один амперсанд, после которого в кавычках продолжается текст запроса.
• ORDER BY C DESC — сортируем данные по столбцу B (продажам) по убыванию.

Результат:

Изменив тематику в ячейке А1 на философию, мы получим книги только по философии, отсортированные по продажам. Удобно.

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

Query по нескольким диапазонам данных

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

Диапазоны указываются через точку с запятой в фигурных скобках:

=QUERY({Диапазон 1; Диапазон 2; Диапазон 3; Диапазон 4};…

Важно отметить: в таком случае столбцы внутри запроса обозначаются не буквами, как в других случаях (A, B, AH, CZ и так далее), а в виде ColN, где N – номер столбца.

Строим сводную таблицу со средними/максимальными значениями по тематикам

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

QUERY(‘Книги’!A1:D;”SELECT avg(C), avg(D) pivot B”;1)

Мы используем похожий диапазон (в отличие от предыдущего в нем есть продажи за 2015 и 2016 годы), извлекаем средние значения по столбцам C и D (SELECT avg(C), avg(D)) и группируем их по столбцу B (тематика).

Полученный результат транспонируем для удобного отображения (с помощью функции TRANSPOSE (ТРАНСП)):

Можно использовать и другие функции вместо avg (среднего), например max (максимальные значения):

Или отобразить и среднее, и максимум, но только по столбцу D:

SELECT avg(D), max(D)

Кейс «Считаем средний чек, выбирая данные с определенной даты»

На скриншоте массив данных, с которым мы будем работать:

Наша задача: отобрать строки с продажами начиная с 1 апреля и посчитать по ним средний чек, используя количество клиентов, то есть получить среднее взвешенное.

Начнем. Создадим QUERY с умножением количества клиентов (столбец B) на средний чек (столбец С) начиная с определенной даты:

Правильно использовать дату в формуле QUERY так:

• QUERY работает с датой только в формате yyyy-mm-dd. Чтобы перевести дату из ячейки Е1 в этот вид, используем формулу ТЕКСТ (TEXT) с условием «yyyy-mm-dd»;
• перед датой и перед апострофом нужно написать date;
• можно и не делать ссылку на ячейку с датой, а написать ее сразу в QUERY, тогда формула будет выглядеть так:

• дата с двух сторон обрамляется одиночными кавычками (‘).

Вернемся к тому, что у нас получилось. Наша формула выдала вот такой массив данных:

Это построчные произведения количества клиентов на средний чек. Нам нужно просуммировать их, для этого введем перед формулой СУММ (SUM):

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

Берем предыдущую формулу, меняем B*C на sum(B) и получаем такую конструкцию:

Наконец, совмещаем формулы:

Все работает, ура! 53 (этот результат видно на всплывающей подсказке в верхнем левом углу) — средний чек с учетом количества клиентов, рассчитанный через среднее взвешенное.