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

Что такое и зачем нужен раскрывающийся список в Google таблице?

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

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

Кроме того, все время вручную вводить повторяющиеся данные – это просто потеря времени.

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

Список – это перечень определённых значений, из которых вам при заполнении ячейки необходимо выбрать только одно.

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

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

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

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

Для этого используют чекбокс.

Сейчас мы с вами рассмотрим, как это правильно сделать.

Видео

Создаем список из данных Google таблицы

Рассмотрим второй способ вставки списка в Google таблицу. Это более универсальный способ и он дает нам больше возможностей.

Выделяем диапазон H2:H21, в который вы будем вставлять фамилии менеджеров, которые занимались выполнением заказов.

Переходим в Меню -> Данные -> Проверка данных.

В Правилах выбираем Значения из диапазона. Этот пункт первым находится в списке правил, обычно он выбран по умолчанию.

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

Обратите внимание, что указать можно не только диапазон ячеек, который находится на листе с нашей таблицей. Мы с вами разместили эту дополнительную информацию на другом листе Google таблицы. Ведь наша таблица с заказами вполне может быть очень большой со множеством колонок и строк. И совсем не обязательно портить внешний вид рабочей таблицы лишними данным.  Кроме того, они могут вам потом просто мешать рядом с основными данными и создавать этим путаницу.

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

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

Совершенно аналогичным образом можно создать и список с чекбоксами. В качестве диапазона значений выберите на листе 2 ячейки A2:A3. Далее действуйте согласно приведённых выше рекомендаций.

Как сделать автоматически обновляемый зависимый список? : СМЕЩ+ПОИСКПОЗ+СЧЁТЕСЛИ

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

В ячейку F2 (зависимый выпадающий список адресов) вместо: =ДВССЫЛ(ПОДСТАВИТЬ(E2;"-";"_")) вставляем: =СМЕЩ($B$2;ПОИСКПОЗ(E2;$B$2:$B$18;0)-1;1;СЧЁТЕСЛИ($B$2:$B$18;E2);1)

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

Аргументы функции:

Ссылка – берем первую ячейку нашего списка, т.е. $B$2

Смещение по строкам – считает функция ПОИСКПОЗ, которая выдает порядковый номер ячейки с выбранным городом (E2) в заданном диапазоне ($B$2:$B$18)

Смещение по столбцам = 1, т.к. мы хотим сослаться на адреса в соседнем столбце (С)

Высота – вычисляем с помощью функции СЧЁТЕСЛИ, которая подсчитывает количество встретившихся в диапазоне ($B$2:$B$18) нужных нам значений – названий городов (E2)

Ширина = 1, т.к. нам нужен один столбец с адресами

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

С выпадающими списками в Google таблицах все немного иначе.

Как скрыть данные для выпадающих списков

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

Чтобы скрыть каждый столбец, выберите стрелку раскрывающегося списка справа от буквы столбца. выберите Скрыть столбец из списка.

Повторите это для всех других столбцов выпадающего

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

Если вам нужно получить доступ к этим спискам, что

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

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

Мы узнали, как сделать выпадающий список в Google Таблицах. Остается упомянуть еще несколько вариантов конфигурации, доступных для использования. В окне «Проверка данных» в строке «Правила» можно выбрать следующие настройки:

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

Обратите внимание: ячейки могут быть выделены разными цветами (и в зависимости от содержимого, в т.ч для этого выделите одну или несколько ячеек правой кнопкой мыши, выберите «Условное форматирование» и в форме справа назначьте цвет выделение правил.

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

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

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

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

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

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

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

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

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

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

Теги