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

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

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

Задача

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

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

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

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

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

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

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

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

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

ШАГ 1

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

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

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

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

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

=ЕСЛИОШИБКА(ИНДЕКС(Сотрудники[Отдел]; ПОИСКПОЗ(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; СЧЁТЕСЛИ(Выбранная_дирекция;"*?"))

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

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