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

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

Для чего же нужны выпадающие списки в Гугл таблицах?

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

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

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

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

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

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

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

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

 
 

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

Сделать-то мы сделали выпадающий список, но теперь нам необходимо потренироваться как его использовать в работе.
 
Теперь добавим немного магии и воспользуемся условным форматированием для того, чтобы мы могли налету отличать данные которые выбраны в той или иной ячейке с выпадающим списком.
 
Допустим у нас есть некие данные, в нашем случае это Ягоды, Фрукты и Овощи. У вас это могут быть другие данные, но не это главное. Если у нас приличное количество выпадающих списков с различными данными, то выглядит все достаточно запутанно и вообще поди пойми где и что.
 
Чтобы как-то разбавить эту серую массу данных, нам потребуется инструмент условного форматирования. При помощи его мы в несколько кликов раскрасим наши товары в соответствующие цвета и нам будет значительно проще различать где какой вид продукта выбран.
 
Для начала выделим весь диапазон, в нашем случае это A1:C20

 
Затем пройдем путь в меню: Формат -> Условное форматирование или кликнем правой кнопкой мыши и в открывшемся контекстном меню выберем Условное форматирование.
 
В открывшемся окне справа мы увидим что мы применять будем форматирование к диапазону A1:C20. Ниже в форме Форматирование ячеек выберем Текст содержит, еще ниже в поле введем, например, Фрукты. Сразу увидим, что наши ячейки, которые содержат слово Фрукты, окрасились в серый цвет — так Гугл таблицы по умолчанию окрашивают ячейки.
 
Но нам же надо окрасить в разные цвета разные ячейки, поэтому выберем ниже способ форматирования, а именно окрасим ячейки с фруктами, скажем, оранжевым цветом. Для этого выберем нужный цвет в форме с изображенным ведерком.
 
Жмем Готово, наслаждаемся свежими красками в нашей серой таблице!
 
Теперь повторим эти действия с другими данными, нажав на кнопку Добавить правило справа, только теперь вводим в поле не Фрукты, а Ягоды и на последнем этапе Овощи, и наблюдаем вот такую картину:

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

Вот и все, надеюсь статья была полезной. Если у вас есть еще какие-то вопросы, то пишите их в комментариях, я с удовольствием на них отвечу!
 
 

Возможно вам будет интересно:

• Полный гайд по основным возможностям Гугл таблиц
• Формулы в Гугл таблицах

Здравствуйте, друзья! Думаю, многим из вас приходилось заполнять большие таблицы на компьютере однотипной информацией. Например, вводить возраст сотрудников, прописывать названия компаний, купивших или продавших определенный товар, печатать название проданных или полученных на склад продуктов, мебели и прочего. И если вводить текст приходится по несколько часов подряд, то очень велика вероятность ошибиться.

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

Чтобы сделать выпадающий список в Гугл таблице, нам потребуется два листа: на одном будут хранится и в него же будем добавлять данные, на втором, собственно, и будет сам список. В примере я первый лист с данными назвала «Сотрудники», а второй – «Список».

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

Дальше перейдите на лист, где нужно сделать раскрывающийся список.

Выделите ячейку, где он будет. Кликните по вкладке «Данные» и выберите «Проверка данных».

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

Здесь так же можно выбрать «Значение из списка». Потом в соседнем поле, через запятую, введите варианты, которые должны отображаться в выпадающем блоке. Например, «Катя, Вася, Максим, Оля, Ира».

Затем перейдите на вкладку с данными (у меня это «Сотрудники») и выделите диапазон, напечатанное в котором должно будет отображаться в выпадающем списке. Нажимайте «ОК».

Как видите, я выделила не только введенные фамилии, но и пустые строки ниже. В этом случае можно будет дописывать в них фамилии, и они автоматически будут добавляться в список.

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

Дальше идет блок «Для неверных данных». Если поставить марке напротив «показывать предупреждение»…

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

Если отметить маркером «запрещать ввод данных»…

То при введении в ячейку не добавленного варианта появится окошко с ошибкой, и введенный текст не сохранится.

В блоке «Оформление» можно отметить галочкой «Показывать текст справки…» и написать его в блоке, размещенном чуть ниже.

Теперь, если выбрано «запрещать ввод данных» и в ячейку введен текст не из списка, появится окно с сообщением, текст в котором будет тот, что вы написали.

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

После того, как сделаете все настройки, жмите «Сохранить».

Выглядит оно так: пустая выделенная ячейка с отображающимся рядом окном проверки, в которой напечатан ваш текст.

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

Если нужно сделать такой список не для одной ячейки, а для определенного диапазона, тогда выделите его (в примере это В2:В11 на листе «Список»), откройте вкладочку «Данные» и выберите знакомый пункт.

Теперь заполните все, как описано выше, и сохраняйте изменения.

В примере в столбце «Имя» для всех выбранных ячеек был создан выпадающий список.

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

Например, я дописала на листе с сотрудниками несколько фамилий. Если помните, в начале я говорила, что выделяю немного больше ячеек, чтобы можно было дописывать фамилии и они автоматически добавлялись в список. Но диапазон у меня был выделен А2: А11, а фамилий я дописала больше (до ячейки А13). Понятно, что две последние в списке не отобразятся. Поэтому давайте расскажу, как решить такую ситуацию.

На листе со списком нужно выделить ячейки, которые будем изменять (она может быть одна, или это может быть диапазон). Потом снова переходим на вкладку «Данные» – «Проверка…».

В блоке «Правила» нужно изменить диапазон ячеек. Можете его заново выделить, нажав на кнопку с девятью квадратиками, а можно просто вручную изменить число. Например, я А11 сменила на А13. Не забудьте сохранить изменения.

Как видите, в списке отображаются все фамилии, которые напечатаны на листе «Сотрудники».

Если постоянно приходится добавлять данные на лист (у меня это «Сотрудники»), то неудобно описанным выше образом постоянно увеличивать диапазон. Поэтому можно сделать следующим образом: выделите ячейки с выпадающим списком и выберите нужный пункт на вкладке.

Дальше в блоке «Правила», где нужно указывать диапазон, должно идти название листа, а потом два раза имя колонки через двоеточие, из которой брать данные. Поскольку у меня это фамилии, то будет написано А:А. Сохраните изменения.

После этого на листе «Сотрудники» сколько бы строк в колонке А не было заполнено, все они отобразятся в нужной ячейке на листе «Список». Но здесь есть нюанс: в список добавится все, что есть в ячейках столбца А.

Например, у меня вошли еще и названия столбцов. Поскольку они не нужны, можно их (Фамилия, Имя, Отчество) написать на листе с исходными данными («Сотрудники») рядом с текстом (в столбце А название «Фамилия», в В указать их, в С – «Имя», в D написать имена).

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

Ты получишь возможность сделать в табличках Google свои связанные выпадающие списки:

Всего в несколько простых шагов.

Шаг 1. Скачай себе файл с примером

Перейдя по ссылкам ниже, ты получишь доступ к готовым файлам:

1. Базовая версия (описана в видео  )
2. Новая версия — доработанная и улучшенная.

Совет. Эти файлы открыты для просмотра, но не для редактирования. Поэтому сразу создай свою копию: меню Файл → Создать копию.  Далее об этом я расскажу подробнее.

Сравни 2 версии скрипта:

Ты можешь попробовать обе версии, а так же получить известие в случае появления новых версий. Ключевые вопросы по проекту я постарался осветить ниже. Жду комментариев под статьей: интересуют твои пожелания, отзывы и мысли.

# ? =

Вопросы + Ответы

01 ? =

Быстродействие

Сразу возникает вопрос: а каков максимальный объем данных можно загрузить на лист с данными? Есть ли предел, выше которого скрипт работать уже перестанет?

Ответ, разумеется, предел есть! Но он зависит не от скрипта, а от скорости работы табличек в целом. Я рекомендую использовать скрипт с данными:

•  не более 5 000 строк для максимально комфортного использования.

Так ты не почувствуешь, что скрипт заметно тормозит. 

Я тестировал скрипт с табличкой в 200 000 строк. И он по-прежнему работал, хотя и медленно. Жду теперь твои результаты тестов скорости. 02 ? =

Почему не выходит редактировать файл с примером

Доступ на редактирование файла с готовым скриптом я

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

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

03 ? =

Где находится скрипт

Настройка скрипта вообще не составит тебе труда.

Для начала зайди в место, где этот скрипт находится. Для этого можно нажать меню «Инструменты → редактор скриптов». А еще, если ты пользуешься браузером Chrome, ты можешь воспользоваться сочетанием клавиш:

• , которое можно запомнить по первым буквам названия меню на английском Tools → Editor.

Совет: используй разные сочетания клавиш в табличках Google
(и не только). Это значительно ускорит тебе работу.

Но продолжим. Когда ты зайдешь в редактор скриптов, ты там увидишь текст моего скрипта. Он длинный:

Но тебе потребуется только поменять настройки, которые я специально разместил в самом начале скрипта. Тут потребуется хорошо потрудится: изменить пару цифр и букв, думаю, справишься 😉

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

04 ? =

Что конкретно менять в скрипте

Итак, ты уже открыл редактор скриптов и хочешь поменять настройки «под себя». Там так и будет написано: «Изменить настройки». Давай разберёмся по пунктам.

var TargetSheet = 'Основной'  // имя листа, на котором настроены выпадающие списки 

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

var LogSheet = 'Данные'  // имя листа с данными для списка 

↑ Создай лист с исходными данными для списка, а его название впиши в скрипт.

var NumOfLevels = 4  // количество уровней выпадающего списка 

↑ В нашем примере 4 связанных выпадающих списка. Если нужно, замени их количество на свое, впиши вместо 4 свое число.

var lcol = 2;  // номер колонки слева, с которой начинается первый список;  // A = 1, B = 2, etc. 

↑ Впиши номер колонки, с которой начинается твоя таблица.

var lrow = 2;  // номер строки, начиная с которой срабатывает список 

↑ Шапка таблицы не должна затрагиваться скриптом, поэтому впиши сюда номер строки, с которой начинаются твои данные.05 ? =

Как приготовить данные

Данные формируются на отдельном листе. В моем примере имеется 4 уровня вложенных списков:

• Планета → Страна → Континент → Город. 

При желании можно добавить еще парочку в конце:

• Район → Улица, 

или в начале:

• Вселенная → Галактика.

Как тебе сделать свой список? Просто сделай его по примеру моего:

1. Пропиши имена категорий-уровней таблицы в первой строчке, начиная с клетки A1.
2. Начни заполнять с последнего уровня: выпиши все возможные варианты.
3. Двигаясь к первому уровню, вводи все значения, не оставляя пустых ячеек. Некоторые названия придётся повторить, это необходимо для правильной работы скрипта.
4. Когда данные будут готовы, можно двигаться дальше. В будущем, их можно пополнять новыми данными.

Отлично! Данные готовы, осталось совсем немного настроить таблицу. Не спеши сразу заполнять все данные, для тестирования достаточно нескольких строк. Помни, что данные должны быть едиными:

• под ними ничего не записывай, 
• ячейки в них не объединяй.

06 ? =

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

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

Для получения списка уникальных значений, используй формулу:

= UNIQUE(A2:A) Она очень простая и выдает список уникальных значений. Эту формулу рекомендую написать на 4 колонки правее основных данных:

Либо вообще размести ее на новом листе:

= UNIQUE(Данные!A2:A) После того, как ты получишь список, переходи на свой рабочий лист. Выдели диапазон ячеек, в котором будет находиться будущий выпадающий список. Иди в меню Данные → Проверка данных… и выбери там свой список.

Совет. При выборе данных для выпадающего списка ты можешь учесть необходимость в добавлении новых значений в дальнейшем. Если данные содержатся в диапазоне A1:A20, то ты можешь выбрать бо́льший диапазон для проверки данных: A1:A100. Пустые ячейки будут игнорироваться.

После этой настройки связанные выпадающие списки уже можно использовать! Теперь поговорим о приятных деталях.07 ? =

Как увеличить количество связанных списков

Об этом я уже упомянул ранее. Для этого зайди в тело скрипта (Инструменты → редактор скриптов) и поменяй там одно число:

var NumOfLevels = 4  // количество уровней выпадающего списка 

↑ В нашем примере 4 связанных выпадающих списка. Вместо 4 поставь свое число.

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

Как видишь, это сделать очень легко.

08 ? =

Как скрипт автоматически заполняет единственное значение

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

Например, мы выбрали часть света = «Азия». В справке «Азии» соответствует только одна страна = «Китай «, а для «Китая» найден лишь один город = «Пекин». Вот, что произойдет при выборе «Азии»: скрипт автоматически вставит «Китай» и «Пекин» в нужные ячейки. Правда, удобно?

09 ? =

Как скрипт работает с дублями

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

Хорошая новость! В текущей версии скрипта списки могут содержать дубли, они будут нормально считываться.

10 ? =

Нужно ли сортировать исходную таблицу

Нет. Твои исходные данные могут быть сортированы в произвольном порядке. Это может быть удобно. Добавляя новые данные тебе не нужно беспокоиться о порядке строк. К тому же легче автоматизировать процесс.

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

Работа с другими типами данных

Пока реализована работа со следующими типами данных:

Если у тебя возникает проблема при работе с числами, обязательно напиши мне об этом в комментариях!

11 ? =

Про функцию «умного» удаления

Мне надоели остатки старых кусков проверки данных, которые появляются, когда я хочу удалить что-то в своих списках. Поэтому я настроил это:

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

• удалить все следы списков, которые были правее.

12 ? =

Лучше один раз увидеть

Это видео немного устарело, но описание в нем вполне подойдет и для последней версии скрипта:

13 ? =

Обратная связь

Жду комментариев.

Наиболее частым вопросом пользователей является:

• Как сделать несколько связанных списков одновременно?

Сделал файл для таких случаев:

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

Похожие записи:

Как сделать формат в excel? Как сделать фон рисунка прозрачным в excel? Как сделать фильтрацию в excel 2010? Как сделать фигурную диаграмму в excel?