Конструктор запросов в эксель
Обновлено: 18.04.2024
"Ручной привод" в работе с данными, частое явление. Многие пользователи Excel, обрабатывают данные "привычным" для себя способом, с минимальной автоматизацией, тратя кучу времени. Мало, кто слышал и использует волшебный инструмент — Power Query.
Почему Power Query?
Power Query — технология подключения к данным, с помощью которой можно обнаруживать, подключать, объединять, преобразовывать и уточнять данные из различных источников для последующего анализа.
Функции Power Query доступны в Excel и Power BI .
Аргументы ЗА изучение надстройки :
1. Простой способ преобразовать данные, без использования формул и сводных таблиц;
2. Быстрый способ, вы можете много сделать с данными, в несколько кликов мыши;
3. Разовая настройка, сформируйте запрос один раз, и обновляйте его, когда происходит изменение данных в источнике, или настройте автоматическое обновление.
Возможности Power Query
Используя надстройку, вы сможете быстро:
- Загружать данные из широкого спектра источников и подключаться к ним:
- Собирать данные из файлов всех основных типов данных (XLSX, TXT, CSV, JSON, HTML, XML. ), по одному или несколько за раз, например из всех файлов указанной папки или непосредственно с листа(-ов) книги;
- Выполнять слияние источников данных для дальнейшего анализа и моделирования с помощью Power Pivot и PowerView ;
- Выполнять очистку данных от мусора;
- Причёсывать данные : исправлять регистр, числа-как-текст, разбирать текст на столбцы и склеивать обратно, делить дату на составляющие и т.д.;
- Настраивать представление таблиц : используя фильтры, сортировку, изменение порядка столбцов, транспонирование, добавление итогов, разворачивая кросс-таблицы в плоские и сворачивать обратно;
- Подтягивать данные из одной таблицы в другую по совпадению одного или нескольких параметров, заменяет знакомую многим функцию ВПР и ее аналоги.
Power Query: где искать, как установить?
Для Excel 2016, 2019 или Office 365 : надстройка уже находится на вкладке Данные ► Получить и преобразовать :
Для версий 2013 и 2010 : загрузите надстройку (официальный сайт Microsoft) выбрав версию, подходящую для вашего устройства. Как только вы загрузите файл, откройте его и следуйте инструкциям.
После этого автоматически откроется вкладка POWER QUERY на ленте:
Если вкладка не появилась, вам нужно ее отобразить:
- Перейдите на вкладкуФайл ► Параметры ► Надстройки;
- В опцияхНадстройкивыберитеНадстройки COM, нажмитеПерейти;
- Отметьте галочкойMicrosoft Power Query for Excel►ОК, вкладка появится на ленте.
Р едактор запросов
Окно редактора запросов, содержит следующие элементы:
- Лента редактора запросов : Файл, Главная, Преобразование, Добавление столбца, Просмотр;
- Запросы — окно с перечнем созданных запросов, можно свернуть / развернуть;
- Строка формул , можно отобразить или скрыть в меню Просмотр ► Панель формул ;
- Сетка предварительного просмотра , в которой выводятся результаты каждого шага запроса;
- Меню для редактирования данных , открывается при нажатии на шапку столбца правой кнопкой мыши;
- Свойства — редактируемое поле названия запроса;
- Примененные шаги запроса , включающая каждый шаг запроса. Шаги можно редактировать, выбирая в списке, изменять последовательность шагов, добавлять новые или удалять.
Статьи по теме:
Спасибо, что дочитали до конца!
Если Вам было интересно, ставьте лайк , пишите, что думаете в комментариях и подписывайтесь на канал, если еще не сделали этого.
Запрос — это набор инструкций, которые можно использовать для работы с данными. Для выполнения этих инструкций нужно выполнить запрос. Помимо возврата результатов (которые можно сортировать, группировать или фильтровать), запрос также может создавать, копировать, удалять и изменять данные.
В этой статье нет пошагових инструкций по созданию запросов.
Дополнительные сведения о отключенном режиме и том, как включить запросы на выполнение, см. в разделе "Выполнение запроса на выполнение действия".
В этой статье
Запуск запроса на выборку или перекрестного запроса
С помощью запросов на выборку и перекрестных запросов можно извлекать и представлять данные, а также поставлять формы и отчеты с данными. При запуске запроса на выборку или перекрестный запрос Access результаты отображаются в Режим таблицы.
Запуск запроса
Найдите запрос в области навигации.
Выполните одно из следующих действий:
Дважды щелкните запрос, который нужно выполнить.
Щелкните запрос, который вы хотите выполнить, и нажмите ввод.
Если запрос, который вы хотите выполнить, открыт в Конструктор, вы также можете запустить его, нажав кнопку "Выполнить" в группе "Результаты" на вкладке "Конструктор" в части Пользовательский интерфейс Microsoft Office Fluent.
Запуск запроса на действие
Существует четыре типа запросов на выполнение действий: запросы на удаление, запросы на обновление и запросы на таблицу. За исключением запросов на создание таблиц (которые создают новые таблицы), запросы на изменение данных в таблицах, на которых они основаны. Эти изменения нельзя отменить, например, нажав CTRL+Z. При внесении изменений с помощью запроса на изменение, который впоследствии вам больше не нужен, обычно необходимо восстановить данные из резервной копии. Поэтому перед выполнением запроса на действие следует всегда убедиться в том, что у вас есть свежий резервная копия данных.
Вы можете снизить риск выполнения запроса на действие, предварительно просмотрев данные, которые будут действовать. Это можно сделать двумя способами.
Просматривайте запрос на действие в представлении таблицы перед его запуском. Для этого откройте запрос в конструкторе, щелкните " Строка состояния Access", а затем выберите в shortcut-меню пункт "Таблица". Чтобы вернуться в конструктор, еще раз щелкните "Вид", а затем в shortcut-меню выберите пункт "Конструктор".
Измените запрос на выборку и запустите его.
Примечание: Обратите внимание на тип запроса на изменение (запрос на обновление, обновление, таблицу или удаление), с помощью чего вы сможете вернуться к этому типу после предварительного просмотра данных с помощью этого метода.
Запуск запроса на выборку в качестве запроса на выборку
Откройте запрос на действие в конструкторе.
На вкладке "Конструктор" в группе "Тип запроса" нажмите кнопку "Выбрать".
На вкладке Конструктор в группе Результаты нажмите кнопку Выполнить.
Запуск запроса
Когда вы будете готовы выполнить запрос на действие, дважды щелкните его в области навигации или щелкните его и нажмите ввод.
Доверие базе данных
Откроется диалоговое окно Параметры безопасности Microsoft Office.
Выберите Включить это содержимое, а затем кнопку ОК.
Запуск запроса с параметрами
При запрос с параметрами запроса в качестве значения при его запуске. При добавлении значения запрос с параметрами применяет его в качестве критерия поля. Поле, к которому оно применяет критерий, заданное в конструкторе запросов. Если при запросе значение не задано, запрос с параметрами интерпретирует входные данные как пустую строку.
Запрос с параметрами всегда является другим типом запроса. Большинство запросов с параметрами — это запросы на выборку или перекрестные запросы, но запросы на приложения, таблицы и обновления также могут быть запросами с параметрами.
Запрос с параметрами нужно выполнить в соответствии с его другим типом, но в целом использовать следующую процедуру:
Запуск запроса
Найдите запрос в области навигации.
Выполните одно из следующих действий:
Дважды щелкните запрос, который нужно выполнить.
Выберите запрос, который вы хотите выполнить, и нажмите ввод.
Когда появится запрос параметра, введите значение, которое будет применяться в качестве критерия.
Запуск специального SQL для конкретного запроса
Существуют три основных типа запросов SQL-запрос: запросы на объединение, запросы к проходимой части и запросы определения данных.
Запросы на объединение объединяют данные из нескольких таблиц, но не так, как другие запросы. В то время как в большинстве запросов данные объединяются путем объединения строк, запросы на объединение объединяют данные путем их объединения. Запросы на объединение отличаются от запросов на объединение, так как они не изменяют таблицы. Запросы на объединение добавили строки в наборе записей, которые не сохраняются после закрытия запроса.
Запросы, которые передаются через базу данных, не обрабатываются обл. базами данных, который поставляется с Access; вместо этого они передаются непосредственно на сервер удаленной базы данных, который обрабатывает и передает результаты обратно в Access.
Запросы определения данных — это особый тип запросов, которые не обработать данные; вместо этого запросы определения данных создают, удаляют или изменяют другие объекты базы данных.
SQL запросы, специфичные для конкретного запроса, невозможно открыть в конструкторе. Их можно открывать только в SQL представлении или запускать. За исключением запросов определения данных, при SQL запроса открывается в представлении таблицы.
Запуск запроса
Найдите запрос в области навигации.
Выполните одно из следующих действий:
Дважды щелкните запрос, который нужно выполнить.
Щелкните запрос, который вы хотите выполнить, и нажмите ввод.
Введите несоответствие в выражении.
Запрос может присоединяться к полям с разными типами данных.
Проверьте конструктор запросов и убедитесь, что тип данных в соединяемом поле одинаковый. Инструкции см. в разделе "Проверка полей, которые соединены" в запросе.
Запись удалена
Это может произойти, если объект или база данных повреждены.
Сжатие и восстановление базы данных. Инструкции см. в разделе "Сжатие и восстановление базы данных".
Циклая ссылка, вызванная псевдонимом
Псевдоним, присвоенный полю, является компонентом выражения для этого поля.
Псевдоним — это имя, которое предоставляется любому выражению в строке "Поле" на сетке конструктора запросов, которое не является фактическим полем. Access назначает псевдоним за вас, если вы не сделаете этого самостоятельно; например, EXPR1. После псевдонима следует двоеточие(:),а затем — выражение. При запуске запроса псевдоним становится именем столбца в таблице.
Измените псевдоним. Инструкции см. в разделе "Изменение псевдонима поля".
Эта ошибка может возникать, если значение вычисляемого поля больше значения, разрешенного значением свойства FieldSize поля. Это также происходит, если знаменатель вычисляемого поля имеет нулевое значение (0).
Убедитесь, что знаменатель вычисляемой поля не дает нулевых значение (0). При необходимости измените свойство FieldSize.
Запись, на которая ссылается, удалена.
Проверка соединенных полей в запросе
Чтобы проверить типы данных полей в запросе, взгляните на исходные таблицы в Конструкторе и проверьте свойства полей, которые нужно проверить.
Откройте запрос в конструкторе. Соединения отображаются как линии, соединяющие поля в исходных таблицах. Обратите внимание на имена таблиц и полей для каждого из них.
В области навигации щелкните правой кнопкой мыши каждую таблицу, в запросе для которых есть одно или несколько полей, и выберите "Конструктор".
1. Соединенные поля с разными типами данных.
2. Щелкните таблицу правой кнопкой мыши и выберите "Конструктор".
Для каждого типа данных сравните значения в столбце "Тип данных" на сетке конструктора таблиц для полей, участвующих в этом соединитеке.
1. Проверьте тип данных для полей, которые соединены, в конструкторе таблицы.
Чтобы переключиться на таблицу, чтобы увидеть ее поля, щелкните вкладку с именем этой таблицы.
Сжатие и восстановление базы данных
Запуск сжатий и восстановления базы данных в Access может повысить производительность базы данных. Эта программа создает копию файла базы данных и, если он является фрагментированной, переумногует оголовье хранения файла базы данных на диске. После завершения процесса сжатия и восстановления сжатая база данных будет освободить место на диске и обычно меньше исходной. Часто сжатие базы данных помогает обеспечить оптимальную производительность приложения базы данных, а также устранять ошибки, которые возникают из-за проблем с оборудованием, перебоев в подаче электроэнергии или перебоев в подаче электроэнергии и схожих причин.
После завершения операции сжатия скорость запроса увеличивается, так как данные, на которые они были перезаписаны, перезаписываются в таблицы на нестрогих страницах. Сканировать несколько страниц гораздо быстрее, чем просматривать фрагментированное страницу. Запросы также оптимизированы после каждого сжатия базы данных.
В ходе сжатия можно использовать исходное имя для сжатого файла базы данных или создать отдельный файл с другим именем. Если вы используете то же имя и база данных успешно сжата, Access автоматически заменит исходный файл сжатой версией.
Настройка параметра для автоматизации этого процесса
На вкладке Файл выберите пункт Параметры, чтобы открыть диалоговое окно Параметры Access.
Щелкните "Текущая база данных" и в меню "Параметры приложений"нажмите кнопку "Сжать при закрытии".
Это приводит к автоматическому сжатию и восстановлению базы данных при каждом ее закрытии.
Сжатие и восстановление базы данных вручную
Изменение псевдонима поля
Откройте запрос в конструкторе.
В сетке конструктора запросов наименуйте поля с псевдонимами. В конце имени поля будет двоеточие, как в поле "Имя":
Проверьте каждый псевдоним, чтобы убедиться, что он не совпадает с именем поля, которое является частью его выражения. В этом случае измените псевдоним.
Совет Чтобы упросить, сформированы ли данные на этом плане с помощью Power Query, выберите ячейку с данными, а если появится вкладка Лента контекстного запроса, данные загружаются из Power Query.
Знать, в какой среде вы сейчас? Power Query хорошо интегрирован с пользовательским интерфейсом Excel, особенно при импорте данных, работе с подключениями и редактировании таблиц, таблиц с Excel и именовых диапазонов. Чтобы избежать путаницы, важно в любой момент времени знать, в какой среде вы находитесь, в какой Excel или Power Query.
Знакомые Excel, лента и сетка
Лента редактора Power Query и предварительный просмотр данных
Например, работа с данными на Excel значительно отличается от Power Query. Кроме того, подключенные данные, которые вы видите на Excel, могут не работать с Power Query для их обработки. Это происходит только при загрузке данных на таблицу или в модель данных из Power Query.
Переименование я вкладок на таблицах Ямы лучше переименовывать по своему смыслу, особенно если их много. Особенно важно пояснить разницу между данными и данными, загруженными из редактора Power Query. Даже если у вас всего два листа: с таблицей Excel "Лист1"и запросом, созданным путем импорта таблицы Excel Таблица1,ее легко запутать. Всегда имеет смысл изменить названия ярлыков по умолчанию на более понятное. Например, переименуйте Лист1 в Таблицу данных и Таблицу1 в Таблицу запросов. Теперь понятно, какая вкладка с данными, а какая вкладка с запросом.
Вы можете создать запрос из импортируемых данных или пустой запрос.
Создание запроса из импортируемых данных
Это самый распространенный способ создания запроса.
Выберем ячейку в данных и выберите запрос> изменить.
Создание пустого запроса
Вы можете начать с нуля. Это можно сделать двумя способами.
Выберите Данные> Получить данные >из других источников > пустой запрос.
Выберите Данные> получить данные >запуск редактора Power Query.
На этом этапе вы можете вручную добавлять шаги и формулы, если хорошо знаете язык формул Power Query M.
Кроме того, можно выбрать команду На главная в группе Новый запрос. Выполните одно из указанных ниже действий.
Выберите Новый источник, чтобы добавить источник данных. Эта команда выглядит так же, как> "Получить данные" на Excel ленте.
Выберите Последние источники, чтобы выбрать источник данных, с помощью который вы работали. Эта команда выглядит так же, как> последние источники на Excel ленте.
Чтобы ввести данные вручную, выберите Ввести данные. Вы можете выбрать эту команду, чтобы попробовать редактор Power Query независимо от внешнего источника данных.
Предположим, что запрос действителен и не имеет ошибок, его можно загрузить обратно на таблицу или в модель данных.
Загрузка запроса из редактора Power Query
В редакторе Power Query сделайте следующее:
Чтобы загрузить на таблицу, выберите Главная> Закрыть & Загрузить > Закрыть & Загрузить.
Чтобы загрузить данные в модель данных, выберите Главная> Закрыть & Загрузить > Закрыть & Загрузка.
В диалоговом окне Импорт данных выберите добавить эти данные в модельданных.
Совет Иногда команда "Загрузить в" неатривна или отключена. Это может произойти при первом создании запроса в книге. В этом случае нажмите кнопку Закрыть & Загрузить, на новом > запросы данных & Connections > Запросы, щелкните запрос правой кнопкой мыши и выберите загрузить в . Кроме того, на ленте редактора Power Query выберите запрос> Загрузить в.
Загрузка запроса из области "Запросы и подключения"
В Excel может потребоваться загрузить запрос на другой таблицу или в модель данных.
В Excel выберите Запросы> запросы & Connections, а затем выберите вкладку Запросы.
В списке запросов найдите запрос, щелкните его правой кнопкой мыши и выберите загрузить в. Появится диалоговое окно Импорт данных.
Выберите, как вы хотите импортировать данные, а затем выберите ОК. Чтобы получить дополнительные сведения об использовании этого диалогового окна, выберите знак вопроса (?).
Существует несколько способов изменить запрос, загруженный на таблицу.
Изменение запроса на Excel данных
Чтобы изменить запрос, найдите ранее загруженную из редактора Power Query, выйдите ячейку в данных и выберите запрос > Изменить.
Изменение запроса в области "Запросы & подключения"
Возможно, вы & области Запросы и подключения удобнее использовать, если в одной книге много запросов и вы хотите быстро найти его.
В Excel выберите Запросы> запросы & Connections, а затем выберите вкладку Запросы.
В списке запросов найдите запрос, щелкните его правой кнопкой мыши и выберите изменить.
Изменение запроса в диалоговом окне "Свойства запроса"
В Excel выберите Data> Data & Connections > Запросы, щелкните запрос правой кнопкой мыши и выберите Свойства ,выберите вкладку Определение в диалоговом окне Свойства и нажмите кнопку Изменить запрос.
Совет Если вы работаете с запросом на > данных, в диалоговом окнеСвойства выберите вкладку Определение, а затем — Изменить запрос.
Модель данных обычно содержит несколько таблиц, расположенных в связи. Запрос загружается в модель данных с помощью команды Загрузить, чтобы отобразить диалоговое окно Импорт данных, а затем в поле Добавить эти данные в режим данныхl. Дополнительные сведения о моделях данных см. в дополнительных сведениях о том, какие источники данных используются в модели данных книги,Создание модели данных в Excelи Создание таблиц с помощью нескольких таблиц.
Чтобы открыть модель данных, выберите Power Pivot > Управление.
В нижней части окна Power Pivot выберите вкладку нужной таблицы.
Подтвердим, что отображается правильная таблица. Модель данных может иметь много таблиц.
Обратите внимание на имя таблицы.
Чтобы закрыть окно Power Pivot, выберите файл> Закрыть. Чтобы освободить память, может потребоваться несколько секунд.
Выберите > подключения & свойства >Запросы, щелкните запрос правой кнопкой мыши и выберите изменить.
Завершив внесение изменений в редакторе Power Query, выберите файл > Закрыть & загрузить.
Запрос на этом и в таблице в модели данных обновляются.
Если вы заметили, что загрузка запроса в модель данных занимает намного больше времени, чем загрузка на лист, проверьте действия Power Query, чтобы узнать, фильтруется ли текстовый столбец или структурированный столбец списка с помощью оператора Contains. Это действие приводит Excel повторно прогонять весь набор данных для каждой строки. Более того, Excel не могут эффективно использовать многопрочитанные выполнения. В качестве обходного решения попробуйте использовать другой оператор, например Равно или Начинает с.
Корпорация Майкрософт знает об этой проблеме и находится в стадии исследования.
Вы можете загрузить Power Query:
На один из них. В редакторе Power Query выберите Home> Close & Load > Close & Load.
В модель данных. В редакторе Power Query выберите Home> Close & Load > Close & Load To.
По умолчанию Power Query загружает запросы на новый таблицу при загрузке одного запроса и одновременно загружает несколько запросов в модель данных. Вы можете изменить поведение по умолчанию для всех книг или только для текущей книги. При настройке этих параметров Power Query не меняет результаты запроса на месте или в данных модели данных и примечаниях.
Кроме того, динамически переопределять стандартные параметры запроса можно в диалоговом окне Импорт, которое отображается после выбора параметра & Загрузитьв.
Глобальные параметры, которые применяются во всех книгах
В редакторе Power Query выберите Файл > параметры и параметры >параметры запроса.
В диалоговом окне Параметры запроса в левой части в разделе GLOBAL выберите Загрузка данных.
В разделе Загрузка Параметры запроса по умолчанию сделайте следующее:
Выберите Использовать стандартные параметры загрузки.
Выберите Указать настраиваемые параметры загрузкипо умолчанию, а затем выберите или отобирать параметр Загрузить на таблицу или Загрузить в модель данных.
Совет В нижней части диалогового окна можно выбрать восстановить параметры по умолчанию, чтобы вернуться к настройкам по умолчанию.
Параметры книги, которые применяются только к текущей книге
В диалоговом окне Параметры запроса в левой части в разделе ТЕКУЩАЯ КНИГА выберите Загрузка данных.
Выполните одно или несколько из указанных ниже действий.
В области Обнаружение типоввыберите или сберем для обнаружения типов столбцов и их заглавных колонок для неструктурированных источников.
По умолчанию они обнаруживаются. Если вы предпочитаете формировать данные самостоятельно, отоставьте этот параметр.
В области Связивыберите или отоберем создать связи между таблицами при первом добавлении в модель данных.
По умолчанию перед загрузкой в модель данных можно найти существующие связи между таблицами, например внешние ключи в реляционной базе данных, и импортировать их вместе с данными. Если вы предпочитаете делать это самостоятельно, сделайте это самостоятельно.
В области Связивыберите или отоберете update relationships when refreshing queries loaded to the Data Model (Связи) при обновлении запросов, загруженных в модель данных.
По умолчанию отношения не обновляются. При обновлении запросов, уже загруженных в модель данных, Power Query находит существующие связи между таблицами, например внешние ключи, в реляционной базе данных и обновляет их. Это может привести к удалению связей, созданных вручную после импорта данных, или к новым связям. Тем не менее, если вы хотите сделать это, выберите этот параметр.
В области Фоновыеданные выберите или отпустите разрешение предварительного просмотра данных для скачивания в фоновом режиме.
По умолчанию предварительный просмотр данных загружается в фоновом режиме. Если вы хотите сразу же видеть все данные, отобираем этот параметр.
При запросе данных в Excel можно использовать входное значение ( параметр), чтобы указать что-то о запросе. Для этого нужно создать запрос с параметрами в Microsoft Query.
Параметры используются в предложении WHERE запроса— они всегда работают в качестве фильтра для извлечения данных.
Параметры могут запрашивать у пользователя входное значение при запуске или обновлении запроса, использовать константы в качестве входного значения или использовать содержимое указанной ячейки в качестве входного значения.
Параметр является частью запроса, который он изменяет, и его нельзя повторно использовать в других запросах.
Примечание Если вы хотите создать запросы с параметрами другим способом, см. создание запроса с параметрами (Power Query).
Последовательность действий
Щелкните Данные > Получить & Преобразование данных > Получить данные > из других источников > из Microsoft Query.
Следуйте шагам мастера запросов. На экране Мастер запросов — готово выберите Просмотр данных или изменение запроса в Microsoft Query и нажмите кнопку Готово. Откроется окно Microsoft Query и отобразит запрос.
После where введите имя поля, оператор сравнения (=, , LIKE и т. д.) и одно из следующих данных:
Для запроса generic parameter (?) введите вопросии (?). В подсказке, которая появляется при запуске запроса, не отображается полезная фраза.
Введите фразу, заключенную в квадратные скобки, для запроса параметра, который помогает вводить допустимые данные. Фраза отображается в запросе с параметрами при запуске запроса.
Завершив добавление условий с параметрами в предложение WHERE, нажмите кнопку ОК, чтобы запустить запрос. Excel запрос на в качестве значения для каждого параметра, Microsoft Query отобразит результаты.
Когда вы будете готовы загрузить данные, закройте окно Microsoft Query, чтобы вернуться к Excel. Откроется диалоговое окно Импорт данных.
Чтобы просмотреть параметры, нажмите кнопку Свойства. Затем в диалоговом окне Свойства подключения на вкладке Определение нажмите кнопку Параметры.
В диалоговом окне Параметры отображаются параметры, используемые в запросе. Выберите параметр в области Имя параметра, чтобы просмотреть или изменить параметр How value is obtained. Вы можете изменить запрос параметра, ввести определенное значение или указать ссылку на ячейку.
Теперь в книге есть запрос с параметрами. При запуске запроса или обновлении подключения к данным Excel проверяет параметр, чтобы завершить предложение WHERE запроса. Если параметр запросит значение, Excel отобразит диалоговое окно Введите значение параметра для сбора входных данных. Вы можете ввести значение или щелкнуть ячейку со значением. Вы также можете указать, что указанное значение или ссылка всегда должны использоваться, а при использовании ссылки на ячейку можно указать, что Excel должно автоматически обновлять подключение к данным (то есть повторно выполнить запрос) при внесении изменений в указанную ячейку.
Суть запроса на выборку – выбрать из исходной таблицы строки, удовлетворяющие определенным критериям (подобно применению фильтра ). В отличие от фильтра отобранные строки будут помещены в отдельную таблицу.
Пусть имеется таблица с перечнем сотрудников (см. лист Сотрудники в файле примера ). Все сотрудники работают в дирекциях, а дирекции состоят из отделов и руководителей дирекций. В каждой строке таблицы содержится фамилия сотрудника, номер его телефона, номер его комнаты и наименование подразделения, к которому он относится.
Задача
Необходимо отобразить всех сотрудников выбранного отдела.
Решение с помощью стандартного фильтра
Это можно легко сделать с помощью стандартного фильтра EXCEL. Выделите заголовки таблицы и нажмите CTRL+SHIFT+L . Через выпадающий список у заголовка Отделы выберите нужный отдел и нажмите ОК.
Будут отображены все сотрудники выбранного отдела.
Решение с помощью трехуровневого Связанного списка
Другим подходом является использование трехуровневого Связанного списка с использованием элементов управления формы , где из исходной таблицы Сотрудники последовательно выбирая Дирекцию и Отдел, можно быстро отобразить всех сотрудников соответствующего отдела в отдельной таблице.
Преимущества использования трехуровневого Связанного списка – субъективны. Кому-то нравится работать с фильтром, кому-то со списками. Работать со списками несколько быстрее и информативнее (выбрав дирекцию, автоматически получим список всех ее отделов). Кроме того, в отличие от фильтра отобранные строки будут помещены в отдельную таблицу - своеобразный отчет, который можно форматировать в стиль отличный от исходной таблицы. В этот отчет можно вынести не все столбцы, а только нужные (хотя после применения фильтра ненужные столбцы можно скрыть).
Основной недостаток – сложность реализации трехуровневого Связанного списка . Но, единожды его создав и поняв принцип работы, этот недостаток в достаточной мере компенсируется.
Алгоритм создания запроса на выборку следующий:
ШАГ 1
Сначала создадим Лист Списки , в котором будут содержаться перечень дирекций и названия отделов (см. файл примера ).
Перечень дирекций (столбец А ) будет извлекаться формулой массива из исходной таблицы с перечнем сотрудников:
Подробности работы этой формулы можно прочитать в статье Отбор уникальных значений .
Перечень отделов (диапазон B 2: E 8 ) будет извлекаться аналогичной формулой массива в соответствующие столбцы на Листе Списки :
=ЕСЛИОШИБКА(ИНДЕКС(Сотрудники[Отдел]; ПОИСКПОЗ(0;ЕСЛИ(B$1=Сотрудники[Дирекция];0;1)+ СЧЁТЕСЛИ($B$1:B1;Сотрудники[Отдел]);0));"")
ШАГ 2
Теперь создадим Лист Просмотр , в котором будут содержаться перечень сотрудников выбранного отдела и два списка (дирекции и отделы), сформированных на основе Элемента управления форм Список .
Первый список создадим для вывода перечня дирекций. Источником строк для него будет созданный ранее динамический диапазон Дирекции. Свяжем его с ячейкой А1 .
Теперь создадим Динамический диапазон Выбранная_дирекция , который будет содержать название выбранной дирекции:
Также создадим Динамический диапазон Отделы , который будет содержать перечень отделов выбранной дирекции и служить источником строк для второго списка:
И, наконец, для вывода фамилий сотрудников (ячейка B 6 ), их номеров телефонов и комнат используем зубодробительную формулу:
Читайте также: