Классическая задача: выбираем в выпадающем списке значение верхнего уровня (отдел, департамент, категорию товаров), а в соседнем столбце в выпадающем списке должны быть только соответствующие значения нижнего уровня (сотрудник, отдел, тип товара).
Это можно решать скриптами — у моих соавторов по каналу «Google Таблицы» такой есть, вот ссылка. Но здесь обсудим решение через формулы.
Итак, на отдельном листе имеем справочник: кто к какому отделу относится или какой товар к какой категории. То есть при выборе значения из верхней строки в «дочернем» выпадающем списке должны быть только соответствующие значения:
И есть основная таблица — например, список товаров или задач или чего угодно еще. У нас в примере задачи. Мы выбираем «отдел» (Замок, Оплот и так далее) в обычном выпадающем списке (источник — ячейки A1: E1 на листе, который изображен на скриншоте выше) и хотим, чтобы справа в столбце «Сотрудник» появлялся выпадающий список только с сущностями из этого «отдела».
Как решим? Мы повторим список сотрудников для каждой строки основной таблицы, и в проверка данных столбца C будем ссылаться на эти списки. Да, немного колхозно и много данных, но без скриптов решить такую задачу можно только так. Проверено на 11 тысячах строк — работает нормально.
По сути, мы создаем таблицу с таким же количеством строк, как основная. И в ней в каждой строке формируем данные для выпадающего списка нижнего уровня (сотрудники, например), куда отправляем значения исходя из выбранного значения в выпадающем списке верхнего уровня (отдел, например). Вот что нам нужно:
И вот эти списки справа будут выступать в качестве источников для проверки данных в столбце «Сотрудник». Только сформируем их на отдельном листе (его потом можно будет скрыть, чтобы не пугал коллег)
Вариант формулы для данных, которые представлены как в примере:
=MAP (столбец со значениями первого уровня ;LAMBDA (x; IFNA (ТРАНСП (ИНДЕКС (таблица со списками ;; XMATCH (x;названия списков первого уровня)));"")))
=MAP (столбец со значениями первого уровня ;LAMBDA (x; IFNA (ТРАНСП (ИНДЕКС (таблица со списками ;; XMATCH (x;названия списков первого уровня)));"")))
Что тут происходит? Разберем ее по шагам.
Функцией XMATCH мы находим порядковый номер каждого отдела из основной таблицы в справочнике:
Потом ИНДЕКСом / INDEX мы достаем сами списки. Даем функции ИНДЕКС весь справочник, а достаем оттуда нужный элемент по номеру столбца (который найден функцией XMATCH). Номер строки — второй аргумент — пропускаем:
ИНДЕКС (справочник; XMATCH (…))
Вот пример для одного «отдела» с первым номером:
ИНДЕКС (справочник; XMATCH (…))
Вот пример для одного «отдела» с первым номером:
Если у нас справочник столбцами, как в нашем примере, то надо будет транспонировать, то есть сделать столбик строкой с помощью функции ТРАНСП / TRANSPOSE:
Ну и чтобы не создавать много отдельных формул, мы используем функцию MAP — обрабатываем весь столбец в основной таблице, для каждого значения формируем строку — список сотрудников для выпадающего списка.
Здесь x - просто название переменной, по которой мы обращаемся к каждому очередному значению из массива, заданного внутри функции MAP, то есть к B2, B3 и всем остальным ячейкам столбца B на листе "Список задач". Эта переменная могла бы называться "y", "икс", "Отдел" или как-то иначе.
Чтобы для пустых значений не было ошибок #N/A, добавим функцию IFNA и заменим эти ошибки на пустую текстовую строку, на ничто:
— Теперь выделяем столбец C и создаем проверку данных (Данные - Настроить проверку данных):
В качестве диапазона ссылаемся на первый список (для первого значения), делаем ссылку относительной (то есть у номеров строк нет долларов) и получается, что в каждой строке забираем список для каждого очередного выпадающего списка.
Таблицы сами поправят ссылки на абсолютные, и получится, что мы во всех строках берем один и тот же список:
Меняем ссылку в проверке данных - нам нужны данные не из строки 2, а из той же строки, в которой мы выдаем выпадающий список, то есть доллары у номеров строк надо удалить:
='Вспомогательный лист'!$F2:$Z2
(столбцы можно взять с запасом, если ожидаем появление новых сотрудников в справочнике — например, вплоть до Z)
='Вспомогательный лист'!$F2:$Z2
(столбцы можно взять с запасом, если ожидаем появление новых сотрудников в справочнике — например, вплоть до Z)
Et voila:
