ПРОЕКТ РАЗРАБОТЧИКОВ БИЗНЕС АНАЛИТИКИ
Инструкция по созданию связанных выпадающих списков в Гугл таблицах
Масштабируемые динамические выпадающие списки в 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({'Списки'!D$4:R};"SELECT Col"&C5))). QUERY возвращает содержимое колонки, номер который мы узнали в колонке С, TRANSPOSE выводит этот список в одну строку, а IF, как и в прошлый раз, выключает формулы на пустых строках. ArrayFormula не работает с формулами, которые возвращают массив, а потому формулу из D5 нужно скопировать на все последующие ячейки столбца.
Теперь, когда на рабочем листе мы выберем имя категории, оно попадет на техлист, формулы узнают номер столбца, в котором указаны её подкатегории, и выведут их рядом с категорией в виде горизонтального списка, на который уже ссылается выпадающий список подкатегорий на рабочем листе.
На словах это сложнее, чем кажется :) Просто возьмите файл с примером, изучите его и применяйте в своих проектах. Ну а как на рабочем листе выводить цены подкатегорий - мы расскажем в следующий раз.
Базовые решения для вашего бизнеса
Удобные системы оптимизации с учетом особенностей вашей отрасли.
Удобные системы оптимизации с учетом особенностей вашей отрасли.
На этом все!
Еще больше полезных материалов вы можете найти в нашем Telegram-канале:
Хотите обсудить свой проект?
