ПРОЕКТ РАЗРАБОТЧИКОВ БИЗНЕС АНАЛИТИКИ

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

Масштабируемые динамические выпадающие списки в 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 нужно скопировать на все последующие ячейки столбца.

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

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

На этом все!

Еще больше полезных материалов вы можете найти в нашем Telegram-канале:

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

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

Для обсуждения вашей задачи напишите нам в WhatsApp. Проведем аудит и предложим решение.