Полно́ статей о настройке зависимых списков в MS Excel, и невозможно придумать что-то совершенно новое на эту тему; однако организационным структурам требуется определенный тип зависимых списков по следующим причинам:
Может быть много уровней в иерархии отделов
Структура может часто изменяться
Названия отделов могут содержать любые символы
В этой статье мы продемонстрируем наш практический опыт маппинга позиций со структурой отделов при помощи инструмента «Проверка вводимых значений» MS Excel.
На Рисунке 1 показан пример организационной структуры. Подразделения образуют первый уровень. Отделы формируют второй уровень и ниже:
![Рисунок 1. Пример организационной структуры Рисунок 1. Пример организационной структуры](https://habrastorage.org/getpro/habr/upload_files/8ea/00b/865/8ea00b86560714353824d6a2d7e72d4d.jpg)
На Рисунке 2 показана та же структура в табличной форме. Список отсортирован по столбцу «Родительский отдел» для дальнейшего использования в формуле.
![Рисунок 2. Табличное представление организационной структуры Рисунок 2. Табличное представление организационной структуры](https://habrastorage.org/getpro/habr/upload_files/076/5d4/a72/0765d4a728718985a0e3771b610453cd.jpg)
На Рисунке 3 показан пример сопоставления позиций с организационной структурой, где:
Столбец «Подразделение уровень 1» использует фиксированный выпадающий список подразделений из диапазона $A$2:$A$3
Столбцы с отделами (от F до L) содержат выпадающие списки, вычисляемые по следующей формуле, как в ячейке F2:
=СМЕЩ($A$1;ПОИСКПОЗ(E2;$B$2:$B$39;0);0;СЧЁТЕСЛИ($B$2:$B$39;E2);1)
![Рисунок 3. Пример маппинга позиций с организационной структурой Рисунок 3. Пример маппинга позиций с организационной структурой](https://habrastorage.org/getpro/habr/upload_files/cd2/7b3/692/cd27b3692b97928b8339804f6315f7fc.jpg)
Подводя итог, можно сказать, что многоуровневые зависимые выпадающие списки в MS Excel могут быть настроены с использованием одной отсортированной таблицы, «Проверки вводимых значений» и одной формулы, написанной с помощью функций СМЕЩ, ПОИСКПОЗ и СЧЁТЕСЛИ.