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

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

Вы можете добавить выпадающий список в любую ячейку Google Sheets. Вот как это сделать.

1. Откройте файл в Google Таблицах.
2. Выделите нужные ячейки.
3. Нажмите Данные и затем Настроить проверку данных.
4. Выберите вариант в пункте Правила:
   —Значения из диапазона. Укажите ячейки, которые нужно включить в список.
   —Значение из списка. Введите пункты списка через запятую, не разделяя их пробелами.
5. В ячейках появится значок в виде направленной вниз стрелки. Если вы хотите его убрать, снимите флажок Показывать раскрывающийся список в ячейке.
6. Если в ячейку с раскрывающимся списком ввести значение, которого нет в списке, появится предупреждение. Вы можете запретить вводить значения не из списка, выбрав вариант Запрещать ввод данных в разделе Для неверных данных.
7. Нажмите Сохранить. В ячейках появится раскрывающийся список. Если вы хотите, чтобы цвет ячеек менялся в зависимости от значений в них, используйте условное форматирование.

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

Свяжите сервисы между собой без программистов за 5 минут!

Как настроить выгрузку новых строк из Google Sheets в Мой Класс?

Инструкция по созданию связанных выпадающих списков в Гугл таблицах

Масштабируемые динамические выпадающие списки в Google Sheets на трех формулах. Детальное описание актуальной темы.

Олег Коваль
Автор статьи и разработчик HelpExcel

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

Рассмотрим подробно процесс создания.

Рассмотрим подробно процесс создания

Не хотите разбираться в непонятных формулах?

Настройте учет с помощью бесплатных шаблонов .

Файл с примером находится по ссылке

В чем идея? Как устроена эта таблица в общих чертах?

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

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

В колонке В укажем названия категорий, в нашем случае это имена складов. Далее в соседних колонках также списком указываем имена подкатегорий, в нашем случае — это различные товары. Здесь важно, чтобы заголовком этих списков было точное имя категории. Для удобства можно создать выпадающий список в ячейках D3, G3, J3 и так далее. Теперь формулы поймут, что «яблоки» и «груши» — это подкатегории категории » Продуктовый «.

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

В ячейке В5 формула =ArrayFormula( ‘Учет’!B5:B ), которая просто копирует на техлист то, что мы вводим на рабочем листе » Учет «.

Сэкономьте время и организуйте учет в компании на индивидуальной консультации с нашим специалистом.

Хотите обсудить свой проект?

Если вы задумываетесь об оптимизации процессов в компании и внедрении IT-систем, оставьте заявку на консультацию и мы свяжемся с вами для обсуждения деталей.

В ячейке С5 формула =ArrayFormula(IF( B5:B =»»;; MATCH( B5:B ; ‘Списки’!D3:R3 ))). MATCH возвращает номер столбца в диапазоне подкатегорий, которые мы создали на листе » Списки «.
«Склад IT» находится в четвертом столбце, если начинать считать со столбца D. ArrayFormula копирует эту формулу на весь столбец, а IF не позволяет ей срабатывать на пустых строках и возвращать ошибки поиска.

В ячейке D5 формула =IF( C5 =»»;; TRANSPOSE(QUERY(;»SELECT Col»& C5 ))) . QUERY возвращает содержимое колонки, номер который мы узнали в колонке С, TRANSPOSE выводит этот список в одну строку, а IF, как и в прошлый раз, выключает формулы на пустых строках. ArrayFormula не работает с формулами, которые возвращают массив, а потому формулу из D5 нужно скопировать на все последующие ячейки столбца.

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

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

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

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

Но открывать список могут лишь редакторы

Как выглядит у не редактора:

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

• Вопрос задан 08 янв.
• 71 просмотр

2 комментария

Простой 2 комментария

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

Выпадающий список — это супер полезный инструмент, который способствует более комфортной работе с информацией. Он позволяет вместить в ячейку сразу несколько значений, с которыми можно работать, как и с любыми другими. Чтобы выбрать нужное, достаточно щелкнуть на значок стрелки, после чего отобразится перечень значений. После выбора конкретного, ячейка автоматически заполняется им.

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

Исходные данные:

• Список адресов в разных городах

Задача:

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

Мы будем двигаться поэтапно, уделяя внимание всем возможностям данного инструмента.

Скачать файлы из этой статьи

Обзорное видео о работе с выпадающими списками в Excel и Google таблицах смотрите ниже. Приятного просмотра!

Выпадающий список в Excel

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

Выбираем ячейку, в которой будем создавать выпадающий список. Далее переходим к инструменту «Проверка данных», тип данных – «Список». В поле «Источник» указываем диапазон списка.

Выпадающий список готов!

Такой способ позволяет представить обычный диапазон в виде выпадающего списка. Повторы данных остались в списке (в диапазоне A2:A16 названия городов повторяются и в выпадающем списке они также повторяются). Это, конечно, не удобно. О том, как сделать выпадающий список уникальных значений в Excel мы поговорим далее, пока остановимся на этом варианте.

Как создать зависимый выпадающий список в Excel?

Существует несколько вариантов. Один из них, это сочетание именованных диапазонов и функции ДВССЫЛ .

Именованный диапазон в Excel – это ячейка (или диапазон ячеек), которой присвоено имя.

Функция ДВССЫЛ в Excel преобразовывает текст в ссылку.

Способ 1: именованные диапазоны + функция ДВССЫЛ

Для начала создадим именованные диапазоны с адресами. Имя каждому присвоим в соответствии с городом.

Алгоритм создания именованного диапазона: выделяем диапазон, далее «Формулы» – «Задать имя».

У нас получится 5 именованных диапазона: Волгоград, Воронеж, Краснодар, Москва и Ростов_на_Дону.

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

Поэтому, вместо дефисов в названии города Ростов-на-Дону мы укажем допустимый символ – нижнее подчеркивание.

Именованные диапазоны готовы.

Теперь выбираем ячейку для второго выпадающего списка, того, который будет зависимым. Переходим к инструменту «Проверка данных», тип данных – «Список». В поле «Источник» указываем функцию: =ДВССЫЛ(D2) , где D2 – это адрес ячейки с первым выпадающим списком городов.

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

Зависимый выпадающий список адресов готов.

Меняя значения в ячейке D2, меняются списки в ячейке E2. За исключением города Ростов-на-Дону. В выпадающем списке городов (ячейка D2), в названии используется дефис, а в именованном диапазоне – нижнее подчеркивание.

Чтобы устранить это несоответствие, перед тем как применять функцию ДВССЫЛ , обработаем значения функцией ПОДСТАВИТЬ .

Функция ПОДСТАВИТЬ заменяет определенный текст в текстовой строке на новое значение. Вместо: =ДВССЫЛ(D2) укажем: =ДВССЫЛ(ПОДСТАВИТЬ(D2;»-«;»_»))

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

Теперь зависимый выпадающий список работает и для города, содержащего в названии дефисы – Ростов-на-Дону. Вернемся к выпадающему списку городов.

Как автоматически обновить выпадающий список в Excel, при добавлении новых данных?

Для начала создадим из диапазона данных «умную» таблицу Excel. Сделать это можно сочетанием клавиш Ctrl+T .

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

Как сделать выпадающий список уникальных значений в Excel?

Надоело смотреть на повторяющиеся названия городов в выпадающем списке. Реализуем выпадающий список так, чтобы названия городов в нем не повторялись. Для этого, добавим слева вспомогательный столбец. Мы дали ему название – «Уникальные».

И включим новый столбец в диапазон «умной» таблицы. «Конструктор» – «Размер таблицы». Вместо =$B$1:$C$17 указываем: =$A$1:$C$17

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

В ячейку А2 добавим формулу массива, которая будет формировать список уникальных городов:

Чтобы Excel воспринял нашу формулу, как формулу массива, жмем Ctrl + Shift + Enter .

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

Из списка уникальных городов создадим именованный диапазон (мы назвали его — «Уникальные»), который затем используем в качестве источника для выпадающего списка городов.

«Проверка данных» – «Список». В источнике данных, вместо предыдущего диапазона с названиями городов =$B$2:$B$18 , задаем имя – =Уникальные

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

Чтобы их убрать, доработаем именованный диапазон «Уникальные». В диспетчере имен, вместо диапазона =Таблица1[Уникальные] используем: =СМЕЩ(Лист1!$A$2;0;0;СЧЁТЗ(Таблица1[Уникальные])-СЧИТАТЬПУСТОТЫ(Таблица1[Уникальные]))

где: Лист1!$A$2 – ячейка со значением первого пункта списка уникальных значений

Таблица1[Уникальные] – столбец с перечнем всех пунктов списка

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

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

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

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

В ячейку 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 таблицах

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

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

«Данные» – «Настроить проверку данных» – «Значение из диапазона»

Важное отличие от проверки данных Excel в том, что инструмент «Проверка данных» в Google таблицах автоматически выдает уникальные значения, и значит, нам не придется создавать вспомогательный столбец с расчетами.

Зависимый выпадающий список в Google таблицах

Возвращаемся к двум основным способам, которые мы рассмотрели в Excel.

Способ 1: именованные диапазоны + ДВССЫЛ

Создадим именованные диапазоны с адресами. Имя каждому присвоим в соответствии с городом.

Выделяем ячейки – «Данные» – «Настроить именованные диапазоны»

Указываем имя и жмем готово. У нас получится 5 именованных диапазонов: Волгоград, Воронеж, Краснодар, Москва и Ростов_на_Дону.

Также, как и в Excel, в Google таблицах к именам диапазонов есть список требований.

Поэтому, вместо дефисов в названии города Ростов-на-Дону укажем допустимый символ – нижнее подчеркивание.

В Google таблицах мы не сможем подобно Excel задать функцию ДВССЫЛ в инструменте «Проверка данных». Поэтому, разместим результат функции ДВССЫЛ в пустых ячейках правее. Не забываем добавить обработку значений от дефисов функцией ПОДСТАВИТЬ. Подробнее о том, для чего это нужно, мы говорили ранее в примере Excel.

В ячейке F1 введем: =ДВССЫЛ(ПОДСТАВИТЬ(D2;»_»;»-«))

Последний штрих в создании зависимого выпадающего списка, в разделе «Настроить проверку данных», в качестве диапазона указываем список из столбца F:F.

При дальнейшей работе вспомогательный столбец F можно скрыть. Минус такого метода – отсутствие динамичности. Если мы добавим новый город и адрес, то они не появятся в созданных выпадающих списках. Но это решаемо!

Как автоматически обновить выпадающий список в Google таблицах при добавлении новых данных?

В выпадающем списке городов, достаточно расширить диапазон и вместо =$A$2:$A$16 указать: =$A$2:$A . Теперь при добавлении нового города он автоматически появляется в выпадающем списке.

Как автоматически обновить зависимый выпадающий список в Google таблицах при добавлении новых данных?

Для того, чтобы зависимый выпадающий список автоматически обновлялся с добавлением новых данных, воспользуемся функцией СМЕЩ .

В ячейке G6 укажем:

Важно: для корректной работы этого способа, данные в столбце с городом должны быть отсортированы от А до Я, или от Я до А. Подробнее о том, как в данном случае работает функция СМЕЩ читайте выше в примере с Excel.

Заключительным этапом поместим результат функции СМЕЩ в диапазон выпадающего списка.

Скроем вспомогательные столбцы для удобства.

Работа выпадающих списков в Google таблицах хоть и схожа с Excel, но все же имеет свои отличительные особенности. Добавляем новые данные, сортируем список и пользуемся зависимыми, автоматически обновляемыми выпадающими списками.

Заключение

Теперь Вам известны несколько способов, как создать выпадающие списки в Excel и Google таблицах. Смотрите примеры и создавайте нужные Вам выпадающие списки.

Изучить работу в программе Excel Вы можете на наших курсах: бесплатные онлайн-курсы по Excel

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

У нас Вы можете заказать выполнение задач по MS Excel и Google таблицам

• 20 июля 2021
• 37750
• MS Excel Video File Google Sheets