Как в Гугл Таблице сделать выпадающий список в ячейке: инструкция
В этой статье:
- Как создать выпадающий список и как с ним работать
- Добавить цвет в выпадающий список
- Группируем данные с помощью GROUP BY и PIVOT
- Устранение неполадок в таблицах Google
- Связанные выпадающие списки
- Как сделать простой выпадающий список в Гугл таблицах
- Удалить или редактировать существующее выпадающее меню
- Кейс «QUERY и выпадающий список»
- Query по нескольким диапазонам данных
- Строим сводную таблицу со средними/максимальными значениями по тематикам
- Кейс «Считаем средний чек, выбирая данные с определенной даты»
Как создать выпадающий список и как с ним работать
Ниже мы приведем две инструкции: с ручным вводом значений списка и с указанием диапазона. Первый — проще, а второй подойдет для работы с большим количеством значений.
Создание выпадающего списка
Самый простой вариант. Подойдет, чтобы быстро “собрать”, к примеру, несложную форму для опроса. Ну или любую другую форму, где не требуется обработки больших массивов данных. Сначала вы должны разобраться, как вообще создать Гугл Таблицу, а потом, как в Гугл Таблице сделать выпадающий список в одной ячейке или сразу в нескольких:
- Левой кнопкой мыши (ЛКМ) кликаем на нужную ячейку или выделяем сразу несколько вниз.
- По выделенному участку щелкаем правой кнопкой мыши (ПКМ) и в меню выбираем “Проверка данных”.
- В окне “Проверка данных” выставляем значения по таблице ниже ↓
- Жмем на сохранить.
Готово. Теперь вы знаете, как создать выпадающий список в Google Таблицах.
Настраиваем окно “Проверка данных” | Что делать |
Диапазон ячеек | Это поле не трогаем |
Правила |
|
Для неверных данных | Оставить “Показывать предупреждение” или изменить на “Запрещать ввод данных”,* чтобы создать выпадающий список в Гугл Таблице. |
Оформление | Поставить флажок на “Показывать текст справки”. Откроется поле с предупреждением по умолчанию. Вы можете заменить его на свой текст. |
*В первом случае, если в ячейку будут вручную введены данные не из списка значений, появится окошко с предупреждением и указанием на необходимость поправки. При запрете ввода данных в ячейку вписать ничего нельзя, работает только значок .
Еще о работе с выпадающим списком
С тем, как сделать в Гугл Таблицах выпадающий список, мы разобрались. Осталось упомянуть еще несколько вариантов настроек, доступных для использования. В окне “Проверка данных”, в строке “Правила”, вы можете выбрать следующие настройки:
- Число → В диапазоне (Не в диапазоне, Больше, Больше или равно, Меньше, Меньше или равно и т. д.) → вписать числа.
- Текст → Содержит (Не содержит, Равняется, Является допустимым URL / адресом электронной почты) → вписать нужный текст.
- Дата → Является допустимой датой (Равняется, До, После, Указана или до и т. д.) → указать дату.
Обратите внимание: ячейки можно подсвечивать разными цветами (и в зависимости от содержимого в том числе. Для этого выделите ПКМ одну или несколько ячеек, выберите “Условное форматирование” и в форме справа назначьте правила выделения цветом.
Добавить цвет в выпадающий список
Одна из лучших особенностей Google Sheets – возможность настраивать электронную таблицу. Например; Если что-то срочно, вы можете покрасить клетки в красный цвет. Чтобы сделать это с помощью раскрывающегося списка, выполните следующие действия:
- Перейдите и щелкните стрелку раскрывающегося списка.
- Из меню, которое появляется; прокрутите вниз и выберите «Условное форматирование».
- С правой стороны Google Sheets появится всплывающее окно: в правом нижнем углу вы увидите иконку с ведром с краской; нажмите на это
- Выберите из доступных вариантов цвета.
- Нажмите «Готово»
Это меню также предлагает вам некоторые другие функции, такие как; Жирный, курсив и подчеркнутый текст. Вы также можете добавить другую линейку или выбрать цветовую гамму, если хотите.
Группируем данные с помощью 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. Пользователи вынуждены писать специальные скрипты и прикреплять их к таблице. Только так содержимое ячеек “ведет себя” нужным образом. Это требует определенных знаний в программировании — даже для того, чтобы взять готовый скрипт из сети и подогнать его под свои потребности.
Мы нашли один вариант, который вы, вероятно, сможете использовать, выбрав наиболее подходящий, чтобы сделать раскрывающийся связанный список в Гугл Таблицах. Скрипт выглядит так:
// Имя рабочего листа var tsheet = ‘Результат’; //заменить на имя листа, в котором должна проводиться проверка данных // диапазон проверки: var rownum = 100; //строка, на которой проверка закончится var vcol = 2; //номер колонки (не буква), которая проверяется // диапазон с условиями var ccols = 3; //номер колонки (не буква), в которой берет начало список условий var clen = 10; //сколько условий берется в расчет, максимум //…………………………………………………………………….. for (var i = 2; i <= rownum; i++) { // Set the data validation var ss = SpreadsheetApp.getActiveSpreadsheet(); var sh = ss.getSheetByName(tsheet); var cell = sh.getRange(i, vcol, 1, 1); var range = sh.getRange(i, ccols, 1, clen); var rule = SpreadsheetApp.newDataValidation().requireValueInRange(range).build(); cell.setDataValidation(rule); } |
Обратите внимание: между знаками “//” находится пояснительная информация — ее можно не удалять, на работоспособность скрипта она не влияет. Вы изменяете только цифры рядом с пояснениями, чтобы сделать в Гугл Таблице связанный выпадающий список.
Подготовка таблицы
Сначала нужно открыть новый документ в Гугл Таблицы и создать в нем еще один лист (“+” в левом нижнем углу окна). Переименовать Лист 1 в “Результат”, а Лист 2 в “Данные”.
Первый этап — работа с листом “Данные”. Как в Гугл Таблицах сделать выпадающий список в ячейке:
- В ячейке А1 указываем название / характеристику первого уровня списка. Начиная с А2 вводим соответствующие значения. Повторений каждого должно быть столько, сколько будет для него значений второго уровня.
- В ячейке D1 указываем название / характеристику первого уровня списка. Начиная с D2 вводим соответствующие значения (по одному разу).
Выглядит все это вот так:
Второй этап, работа с листом “Результат”:
- Переходим к листу “Результат” и выделяем интересующий диапазон ячеек. В данном случае — от А2 и до А13 включительно.
- Кликаем ПКМ по выделенному полю и в меню выбираем “Проверка данных”.
- В окне “Проверка данных” первую строку не изменяем. Ставим курсор в поле правее “Значения из диапазона” → переключаемся на лист “Данные” → выделяем значения из столбца D, начиная с D2 + столько пустых ячеек вниз, сколько нужно → Ок → Сохранить.
Что мы видим перед нажатием на “Сохранить”:
Подготовка окончена. Осталось прикрепить скрипт к таблице.
Как сделать простой выпадающий список в Гугл таблицах
Чтобы реализовать выпадающий список красиво и удобно (не испортив внешний вид таблицы), мы будем использовать два листа. Для этого давайте сперва добавим второй лист как это описано тут и переименуем их, как это описано в соответствующей статье здесь.
Лист на котором будет отображаться результат я так и назвал Результат, а лист, который сразу был под названием Лист 2, я назвал Данные, на нем я размещу исходные данные.
После того как мы сделали эти простые действия, приступим к заполнению данных. Для этого перейдем на лист который мы назвали Данные и добавим некоторые данные, у меня это Ягоды, Фрукты и Овощи, расположенные по порядку в ячейках A1:A3:
Теперь перейдем на наш главный лист Результат, где мы будем делать сам выпадающий список. Поставим курсор где нам необходимо, в моем случае разницы нет и я размещу выпадающий список в ячейке A3.
Теперь переходим в панели меню по следующему пути: Данные -> Проверка данных:
Откроется вот такое контекстное меню:
В котором мы видим следующие пункты:
- Диапазон ячеек – здесь мы видим название нашего листа и адрес ячейки в которой будет наш выпадающий список на данном листе;
- Правила – здесь мы будем задавать правила для отображения нашего списка. По умолчанию значение стоит Значения из диапазона, оно нам как раз и нужно, так что ничего не трогаем и оставляем как есть. А вот в поле справа от значения нам необходимо указать путь до наших данных на втором листе, в нашем случае это: ‘Данные’!A1:A3
Слово Данные – это ссылка на лист с нашими исходными данными, взятая в одинарные кавычки, затем восклицательный знак и номера ячеек с нашими данными. - Ниже мы видим чек бокс Показывать раскрывающийся список в ячейке – он выделен по умолчанию и это значит, что справа ячейки с нашим выпадающим списком будет треугольничек. Если он вам по каким-то причинам не нужен, то снимите чек бокс.
- Для неверных данных – здесь два радио бокса: показывать предупреждение и запрещать ввод данных. По умолчанию стоит показывать предупреждение и это значит, что если вы введете не соответствующее значение из исходных данных, то всплывет сообщение с ошибкой.
А если выберете запрещать ввод данных, то при неверном (несоответствующем) исходным данным значении появится предупреждающий pop-up с текстом «Данные, которые вы ввели в ячейку A3, не соответствуют правилам проверки». - Оформление – в данном пункте мы видим чекбокс «Показывать текст справки для проверки данных:» и ниже поле, где нам предлагается готовый вариант сообщения, который можно исправить на свое. Именно это сообщение будет всплывать при введении не правильных значений, по умолчанию стоит: «Введите значение из диапазона ‘Данные’!A1:A3»
Все! Жмем кнопку Сохранить и наслаждаемся результатом своего труда:
Удалить или редактировать существующее выпадающее меню
Если вы хотите обновить выпадающее меню или удалить его, вы можете выполнить следующие инструкции:
- Выделите коррелированные строки или столбцы.
- Нажмите на «Данные»
- Нажмите «Проверка данных»
- Внесите изменения или нажмите «Удалить проверку», чтобы удалить раскрывающееся меню.
- Нажмите Сохранить »
Ваши 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 (этот результат видно на всплывающей подсказке в верхнем левом углу) — средний чек с учетом количества клиентов, рассчитанный через среднее взвешенное.
- https://googlechro-me.ru/vypadayushchij-spisok-v-gugl-tablicah/
- https://qna.habr.com/q/134617
- https://tehnografi.com/%D0%BA%D0%B0%D0%BA-%D1%81%D0%B4%D0%B5%D0%BB%D0%B0%D1%82%D1%8C-%D0%B2%D1%8B%D0%BF%D0%B0%D0%B4%D0%B0%D1%8E%D1%89%D0%B8%D0%B9-%D1%81%D0%BF%D0%B8%D1%81%D0%BE%D0%BA-%D0%B2-google-sheets/
- https://shagabutdinov.ru/query_sheets/
- https://google-suite.info/google-tablicy-vypadayushhij-spisok/