Нужна доработка таблицы?
Нужна доработка таблицы?
Чтобы сделать зависимый (связанный) выпадающий список в Google Таблицах, создайте на отдельном листе справочник категорий и подкатегорий. Первый список настройте обычным способом: «Данные» → «Проверка данных» → критерий «Раскрывающийся список (из диапазона)» с диапазоном категорий. Для второго списка нужен динамический диапазон, который формируется формулой в отдельной ячейке или вспомогательном столбце: либо ФИЛЬТР/FILTER, отбирающая подкатегории по выбранной категории, либо ДВССЫЛ/INDIRECT с именованными диапазонами. На получившийся диапазон и укажите ссылку источником во второй «Проверке данных» (саму формулу в поле диапазона Google Таблицы не принимают, в отличие от Excel) — тогда подкатегории будут меняться в зависимости от выбора в первой ячейке.
Частые вопросы
Как сделать зависимый связанный выпадающий список в Google Таблицах?
Создайте отдельный лист-справочник со столбцами Категория и Подкатегория. Первую ячейку настройте через «Данные» → «Проверка данных» → «Раскрывающийся список (из диапазона)» с диапазоном категорий. Для второй ячейки сделайте вспомогательный столбец с формулой ФИЛЬТР/FILTER, которая отбирает подкатегории по выбранной категории, и укажите этот столбец диапазоном во второй «Проверке данных».
Как сделать связанный список через ДВССЫЛ (INDIRECT)?
На отдельном листе создайте по столбцу подкатегорий на каждую категорию и присвойте каждому именованный диапазон, совпадающий с названием категории («Данные» → «Именованные диапазоны»; пробелы замените на _). В отдельную вспомогательную ячейку впишите =ДВССЫЛ(A2), где A2 — ячейка с выбранной категорией; формула развернёт нужный именованный диапазон. Затем в «Проверке данных» второй ячейки выберите «Раскрывающийся список (из диапазона)» и укажите диапазоном эту вспомогательную ячейку с её результатом. В Google Таблицах, в отличие от Excel, вписать формулу ДВССЫЛ прямо в поле диапазона нельзя — она должна лежать в ячейке.
Почему зависимый список не обновляется при смене категории?
Частые причины: имя категории не совпадает с именем диапазона (лишний пробел, регистр, опечатка); в ДВССЫЛ указана не та ячейка (нужна относительная ссылка на ту же строку); либо во второй ячейке остался старый выбор — очистите её. При использовании ФИЛЬТР проверьте, что диапазон-источник захватывает все строки справочника.
Что выбрать — ФИЛЬТР (FILTER) или ДВССЫЛ (INDIRECT)?
ФИЛЬТР работает с одним плоским справочником Категория–Подкатегория и не требует множества именованных диапазонов, поэтому его проще расширять. ДВССЫЛ удобнее для небольшого числа категорий, но под каждую нужен отдельный столбец и именованный диапазон. Для крупного справочника выбирайте ФИЛЬТР.
Масштабируемые динамические выпадающие списки в Google Sheets на трех формулах. Детальное описание актуальной темы.
ПРОЕКТ РАЗРАБОТЧИКОВ БИЗНЕС АНАЛИТИКИ
Инструкция по созданию связанных выпадающих списков в Гугл таблицах
Олег Коваль
Автор статьи и разработчик HelpExcel
Часто встречается ситуация, когда необходим выпадающий список, зависимый от соседней ячейки. К примеру, мы выбрали склад и хотим выбирать товары из списка именно этого склада. Данное решение масштабируется на весь лист, как это бывает нужно в проектах, например, ДДС или других подобных.
Рассмотрим подробно процесс создания.
Рассмотрим подробно процесс создания
В чем идея? Как устроена эта таблица в общих чертах?
Выпадающий список в колонке С ссылается на строку в техлисте, которую мы с помощью формул заполняем нужными данными. Ячейка С5 берет данные со строки 5, С6 - со строки 6 и так далее. Содержимое этих строк зависит от значения в колонке В, то есть от имени категории.
Для начала создадим лист, в который мы будем записывать названия категорий и подкатегорий. Назовем его "Списки".
В колонке В укажем названия категорий, в нашем случае это имена складов. Далее в соседних колонках также списком указываем имена подкатегорий, в нашем случае - это различные товары. Здесь важно, чтобы заголовком этих списков было точное имя категории. Для удобства можно создать выпадающий список в ячейках D3, G3, J3 и так далее. Теперь формулы поймут, что "яблоки" и "груши" - это подкатегории категории "
Продуктовый".
Теперь создадим техлист, на котором будут только формулы. В дальнейшем его можно будет скрыть.
В ячейке В5 формула =ArrayFormula(
'Учет'!B5:B), которая просто копирует на техлист то, что мы вводим на рабочем листе "Учет".
В ячейке С5 формула =ArrayFormula(IF(B5:B="";; MATCH(B5:B;'Списки'!D3:R3))). MATCH возвращает номер столбца в диапазоне подкатегорий, которые мы создали на листе "Списки".
"Склад IT" находится в четвертом столбце, если начинать считать со столбца D. ArrayFormula копирует эту формулу на весь столбец, а IF не позволяет ей срабатывать на пустых строках и возвращать ошибки поиска.
В ячейке D5 формула =IF(C5="";; TRANSPOSE(QUERY({'Списки'!D$4:R};"SELECT Col"&C5))). QUERY возвращает содержимое колонки, номер который мы узнали в колонке С, TRANSPOSE выводит этот список в одну строку, а IF, как и в прошлый раз, выключает формулы на пустых строках. ArrayFormula не работает с формулами, которые возвращают массив, а потому формулу из D5 нужно скопировать на все последующие ячейки столбца.
Теперь, когда на рабочем листе мы выберем имя категории, оно попадет на техлист, формулы узнают номер столбца, в котором указаны её подкатегории, и выведут их рядом с категорией в виде горизонтального списка, на который уже ссылается выпадающий список подкатегорий на рабочем листе.
На словах это сложнее, чем кажется :) Просто возьмите файл с примером, изучите его и применяйте в своих проектах. Ну а как на рабочем листе выводить цены подкатегорий - мы расскажем в следующий раз.
На этом все!
Хотите обсудить свой проект?
