Запрос на выборку данных в MS EXCEL (на основе элементов управления формы)

Суть запроса на выборку – выбрать из исходной таблицы строки, удовлетворяющие определенным критериям (подобно применению фильтра). В отличие от фильтра отобранные строки будут помещены в отдельную таблицу.

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

Задача

Необходимо отобразить всех сотрудников выбранного отдела.

Решение с помощью стандартного фильтра

Это можно легко сделать с помощью стандартного фильтра EXCEL. Выделите заголовки таблицы и нажмите CTRL+SHIFT+L. Через выпадающий список у заголовка Отделы выберите нужный отдел и нажмите ОК.

Будут отображены все сотрудники выбранного отдела.

Решение с помощью трехуровневого Связанного списка

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

Преимущества использования трехуровневого Связанного списка – субъективны. Кому-то нравится работать с фильтром, кому-то со списками. Работать со списками несколько быстрее и информативнее (выбрав дирекцию, автоматически получим список всех ее отделов). Кроме того, в отличие от фильтра отобранные строки будут помещены в отдельную таблицу - своеобразный отчет, который можно форматировать в стиль отличный от исходной таблицы. В этот отчет можно вынести не все столбцы, а только нужные (хотя после применения фильтра ненужные столбцы можно скрыть).

Основной недостаток – сложность реализации трехуровневого Связанного списка. Но, единожды его создав и поняв принцип работы, этот недостаток в достаточной мере компенсируется.

Алгоритм создания запроса на выборку следующий:

ШАГ 1

Сначала создадим Лист Списки, в котором будут содержаться перечень дирекций и названия отделов (см. файл примера).

Перечень дирекций (столбец А) будет извлекаться формулой массива из исходной таблицы с перечнем сотрудников:

=ЕСЛИОШИБКА(ИНДЕКС(Сотрудники[Дирекция];
ПОИСКПОЗ(0;СЧЁТЕСЛИ($A$1:A1;Сотрудники[Дирекция]);0));"")

Подробности работы этой формулы можно прочитать в статье Отбор уникальных значений.

Перечень отделов (диапазон B2:E8) будет извлекаться аналогичной формулой массива в соответствующие столбцы на Листе Списки:

=ЕСЛИОШИБКА(ИНДЕКС(Сотрудники[Отдел];
ПОИСКПОЗ(0;ЕСЛИ(B$1=Сотрудники[Дирекция];0;1)+
СЧЁТЕСЛИ($B$1:B1;Сотрудники[Отдел]);0));"")

Теперь создадим Динамический диапазон Дирекции:

=СМЕЩ(списки!$A$2;;;СЧЁТЕСЛИ(списки!$A$2:$A$18;"*?"))

ШАГ 2

Теперь создадим Лист Просмотр, в котором будут содержаться перечень сотрудников выбранного отдела и два списка (дирекции и отделы), сформированных на основе Элемента управления форм Список.

Первый список создадим для вывода перечня дирекций. Источником строк для него будет созданный ранее динамический диапазон Дирекции. Свяжем его с ячейкой А1.

Теперь создадим Динамический диапазон Выбранная_дирекция, который будет содержать название выбранной дирекции:

=СМЕЩ(списки!$A$2;;просмотр!$A$1;12)

Также создадим Динамический диапазон Отделы, который будет содержать перечень отделов выбранной дирекции и служить источником строк для второго списка:

=СМЕЩ(списки!$A$2;;просмотр!$A$1;
СЧЁТЕСЛИ(Выбранная_дирекция;"*?"))

И, наконец, для вывода фамилий сотрудников (ячейка B6), их номеров телефонов и комнат используем зубодробительную формулу:

=ЕСЛИОШИБКА(ИНДЕКС(Сотрудники[Сотрудник];
НАИМЕНЬШИЙ(ЕСЛИ((СТРОКА(Сотрудники[Телефон])*
(просмотр!$C$1=Сотрудники[Отдел]))=0;"";
СТРОКА(Сотрудники[Телефон])*(просмотр!$C$1=Сотрудники[Отдел]));
СТРОКА(Просмотр[[#Эта строка];
[Должность]])-СТРОКА(Просмотр[[#Заголовки];
[Должность]]))-СТРОКА(Сотрудники[[#Заголовки];[Отдел]]));"")

Связанные статьи

Похожие задачи
Прочитайте другие статьи, решающие похожие задачи в MS Excel. Это позволит Вам решать широкий класс подобных задач.
Средняя: 4.4 (5 оценок)

Комментарии

AlexNev (не проверено)

А как быть если нужно что бы значения из многоуровневого списка попадали в какую либо текущую выбранную ячейку? Есть реестр оборудования и для каждой единицы оборудования нужно выбрать соответствующее значение классификатора. Классификатор иерархический - 1 уровень класс, 2 группа, 3 подгруппа, 4 тип, 5 марка, 6 модель, причем марка может не указываться..

Creator

Статья о создании трехуровнего списка http://excel2.ru/articles/mnogourovnevyy-svyazannyy-spisok На основе идей из этой статьи можно построить и 6-и уровневый список. Если Марку (5-й уровень) не обязательно требуется выбирать, то ее либо можно опустить (тогда Модель будет определяться Типом) либо сделать значение "не выбрано" и вывести, например, все значения моделей (или какой-то конкретный список моделей). Если таблица будет содержать много записей, подумайте над использованием ACCESS вместо EXCEL (корректная работа иерархических структур в ACCESS обеспечивается самой базой данных и создавать их в ACCESS гораздо легче)

AlexNev (не проверено)

А у вас готовых решений для access'a нет?) Что бы в табличке данные поменять осталось) У меня сейчас задача "хотя бы отобразить".. т.е. что бы человек смог увидеть структуру представленную в экселе в виде таблицы из 6 столбцов уровней где нибудь в виде дерева) А уже думал какой нить простенький макрос запустить что бы вся эта структура в виде дерева каталогов вылилась) А там уже в файловом менеджере посмотреть можно будет. Но увы команда "Print" каталоги не создает(

Wasp

Access'а нет :)