Запросы в эксель как делать

Создание запроса с параметрами в Microsoft Query

При запросе данных в Excel можно использовать входное значение ( параметр), чтобы указать что-то о запросе. Для этого нужно создать запрос с параметрами в Microsoft Query.

• Параметры используются в предложении WHERE запроса— они всегда работают в качестве фильтра для извлечения данных.
• Параметры могут запрашивать у пользователя входное значение при запуске или обновлении запроса, использовать константы в качестве входного значения или использовать содержимое указанной ячейки в качестве входного значения.
• Параметр является частью запроса, который он изменяет, и его нельзя повторно использовать в других запросах.

Примечание Если вы хотите создать запросы с параметрами другим способом, см. создание запроса с параметрами (Power Query).

Последовательность действий

1. Щелкните Данные >Получить & Преобразование данных >Получить данные >из других источников > из Microsoft Query.
2. Следуйте шагам мастера запросов. На экране Мастер запросов — готово выберите Просмотр данных или изменение запроса в Microsoft Query и нажмите кнопку Готово. Откроется окно Microsoft Query и отобразит запрос.
3. Нажмите кнопку>SQL. В диалоговом SQL найдите предложение WHERE — строку, которая начинается со слова WHERE, обычно в конце SQL кода. Если предложение WHERE не существует, добавьте его, введя WHERE в новой строке в конце запроса.
4. После where введите имя поля, оператор сравнения (=, , LIKE и т. д.) и одно из следующих данных:
   • Для запроса generic parameter (?) введите вопросии (?). В подсказке, которая появляется при запуске запроса, не отображается полезная фраза.
   • Введите фразу, заключенную в квадратные скобки, для запроса параметра, который помогает вводить допустимые данные. Фраза отображается в запросе с параметрами при запуске запроса.
5. Завершив добавление условий с параметрами в предложение WHERE, нажмите кнопку ОК, чтобы запустить запрос. Excel запрос на в качестве значения для каждого параметра, Microsoft Query отобразит результаты.
6. Когда вы будете готовы загрузить данные, закройте окно Microsoft Query, чтобы вернуться к Excel. Откроется диалоговое окно Импорт данных.
7. Чтобы просмотреть параметры, нажмите кнопку Свойства. Затем в диалоговом окне Свойства подключения на вкладке Определение нажмите кнопку Параметры.
8. В диалоговом окне Параметры отображаются параметры, используемые в запросе. Выберите параметр в области Имя параметра, чтобы просмотреть или изменить параметр How value is obtained. Вы можете изменить запрос параметра, ввести определенное значение или указать ссылку на ячейку.
9. Нажмите кнопку ОК, чтобы сохранить изменения и закрыть диалоговое окно Параметры, а затем в диалоговом окне Импорт данных нажмите кнопку ОК, чтобы отобразить результаты запроса Excel.

Теперь в книге есть запрос с параметрами. При запуске запроса или обновлении подключения к данным Excel проверяет параметр, чтобы завершить предложение WHERE запроса. Если параметр запросит значение, Excel отобразит диалоговое окно Введите значение параметра для сбора входных данных. Вы можете ввести значение или щелкнуть ячейку со значением. Вы также можете указать, что указанное значение или ссылка всегда должны использоваться, а при использовании ссылки на ячейку можно указать, что Excel должно автоматически обновлять подключение к данным (то есть повторно выполнить запрос) при внесении изменений в указанную ячейку.

Запросы в Excel. Часть 1

Этой статьей я открываю цикл про выполнение запросов в Excel и оптимизацию работы с крупными таблицами Excel.

Для работы с большими массивами данных, представляющими собой таблицы с многими полями и большим количеством строк, но для ведения которых нет смысла покупать Access, удобно использовать таблицы Excel и делать из них выборки.

Вначале определимся, о каких таблицах идет речь. Постараюсь без лишних рассуждений. Достаточно часто встречаются случаи, когда есть много информации, которую удобно разместить в таблицу, но она получается очень большой. И это делает не удобным вытаскивание из неё части данных, которые периодически нужны для работы. Стоит отметить, что самый оптимальный вариант — это таблицы, в которых каждая строка содержит исчерпывающую информацию о некотором объекте. Например, информацию о сотрудниках или характеристиках изделия. Пожалуй, для наглядности будем пользоваться таблицей о сотрудниках, это классика жанра, понятная многим.

Пускай наша таблица содержит такие поля:

• Таб_N
• Фамилия
• Имя
• Отчество
• Пол
• Дата рождения
• ИНН
• Должность
• Отдел
• Последний отпуск (дата)
• Прием на работу (дата)
• Уволен (дата)

Думаю, для иллюстрации работы этого будет достаточно.

Теперь немного напомню, какие требования к таблице стоит соблюдать:

1. Она не упорядочена сверху вниз — поэтому я не вводил нумерацию, а оставил табельный номер. Новых сотрудников можно просто добавлять снизу или даже куда-нибудь в середину.
2. Нет одинаковых строк — у нас в любом случае за это отвечает табельный номер.
3. Данные в столбцах должны представлять собой логически неделимую информацию. Хорошо видно на Ф.И.О. Если мы укажем всё в одном столбце, то найти человека или отсортировать данные по имени-отчеству будет проблематично.

Ещё один момент! Этой таблице нужно присвоить ИМЯ. Допустим, банально, но привычно — Кадры. �� Я имею в виду не имя файла, а задать имя для таблицы средствами редактора. Это делается командой Имя таблицы, расположенной на закладке Конструктор таблицы в области Свойства. Благодаря имени мы сможет обращаться к таблице из других файлов (книг) Excel и видеть её поля и данные.

Итак, стоит помнить, что наша таблица будет аскетична и иметь статус основного хранилища актуальной информации, к которому мы будем обращаться с целью извлечения нужных данных. Поэтому её надо аккуратно заполнять и делать резервные копии.

Извлеченная информация будет по запросу выбираться и переноситься в другие файлы Excel и там приводиться к красивому виду с сортировками, выделениями цветом и шрифтами, дабы ее удобнее было воспринимать, анализировать и строить отчеты.

Вот такую таблицу мы получим:

Я не заносил туда много данных, лишь бы хватало данных проиллюстрировать возможности, и не сортировал ни по одному из столбцов.

В следующей статье рассмотрен собственно инструмент, при помощи которого мы будем обращаться к нашей таблице и выполнять запросы в Excel на выборку нужных данных.

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

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

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

Задача

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

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

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

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

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

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

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

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

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

ШАГ 1

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

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

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

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

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

ШАГ 2

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

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

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

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

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

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

Используйте возможности SQL для создания запросов в Excel и напрямую к таблицам Excel

Порой таблицы Excel постепенно разрастаются настолько, что с ними становится неудобно работать. Поиск дубликатов, группировка, сложная сортировка, объединение нескольких таблиц в одну, т.д. — превращаются в действительно трудоёмкие задачи. Теоретически эти задачи можно легко решить с помощью языка запросов SQL… если бы только можно было составлять запросы напрямую к данным Excel.

Инструмент XLTools «SQL запросы» расширяет Excel возможностями языка структурированных запросов:

Создание запросов SQL в интерфейсе Excel и напрямую к Excel таблицам
Автогенерация запросов SELECT и JOIN
Доступны JOIN, ORDER BY, DISTINCT, GROUP BY, SUM и другие операторы SQLite
Создание запросов в интуитивном редакторе с подстветкой синтаксиса
Обращение к любым таблицам Excel из дерева данных

Перед началом работы добавьте «Всплывающие часы» в Excel

«SQL запросы» – это один из 20+ инструментов в составе надстройки XLTools для Excel. Работает в Excel 2019, 2016, 2013, 2010, десктоп Office 365.

Скачать XLTools для Excel
– пробный период дает 14 дней полного доступа ко всем инструментам.

Как превратить данные Excel в реляционную базу данных и подготовить их к работе с SQL запросами

По умолчанию Excel воспринимает данные как простые диапазоны. Но SQL применим только к реляционным базам данных. Поэтому, прежде чем создать запрос, преобразуйте диапазоны Excel в таблицу (именованный диапазон с применением стиля таблицы):

Выделите диапазон данных На вкладке «Главная» нажмите Форматировать как таблицу Примените стиль таблицы.

Выберите таблицу Откройте вкладку «Конструктор» Напечатайте имя таблицы. Напр., «КодТовара».

Повторите эти шаги для каждого диапазона, который планируете использовать в запросах. Напр., «КодТовара», «ЦенаРозн», «ОбъемПродаж», т.д.

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

Как создать и выполнить запрос SQL SELECT к таблицам Excel

Надстройка «SQL запросы» позволяет выполнять запросы к Excel таблицам на разных листах и в разных книгах. Для этого убедитесь, что эти книги открыты, а нужные данные отформатированы как именованные таблицы.

Нажмите кнопку Выполнить SQL на вкладке XLTools Откроется окно редактора.

В левой части окна находится дерево данных со всеми доступными таблицами Excel. Нажатием на узлы открываются/сворачиваются поля таблицы (столбцы).

Выберите целые таблицы или конкретные поля.
По мере выбора полей, в правой части редактора автоматически генерируется запрос SELECT.
Внимание: редактор запросов SQL автоматически подсвечивает синтаксис.
Укажите, куда необходимо поместить результат запроса: на новый или существующий лист.
Нажмите «Выполнить» Готово!

Операторы Left Join, Order By, Group By, Distinct и другие SQLite команды в Excel

XLTools использует стандарт SQLite. Пользователи, владеющие языком SQLite, могут создавать самые разнообразные запросы:

LEFT JOIN – объединить две и более таблиц по общему ключевому столбцу
ORDER BY – сортировка данных в выдаче запроса
DISTINCT – удаление дубликатов из результата запроса
GROUP BY – группировка данных в выдаче запроса
SUM, COUNT, MIN, MAX, AVG и другие операторы

Совет: вместо набора названий таблиц вручную, просто перетягивайте названия из дерева данных в область редактора SQL запросов.

Как объединить две и более Excel таблиц с помощью надстройки «SQL запросы»

Вы можете объединить несколько таблиц Excel в одну, если у них есть общее ключевое поле. Предположим, вам нужно объединить несколько таблиц по общему столбцу «КодТовара»:

Нажмите Выполнить SQL на вкладке XLTools Выберите поля, которые нужно включить в объединённую таблицу. По мере выбора полей, автоматически генерируется запрос SELECT и LEFT JOIN.

Укажите, куда необходимо поместить результат запроса: на новый или существующий лист.
Нажмите «Выполнить» Готово! Объединённая таблица появится в считанные секунды.

Any questions or suggestions?

Антон С. Сделал все как написано выше. (данные получить с SQL не проблема и с помощью стандартных средств екселя). меняю значения в таблице и на сервер ничего не улетает. да и прилетают данные походу с соседней вкладки (копипастит попросту). выдаёт ошибку при обращении к хранимым процедурам. Мне необходимо получить таблицу которая храниться на сервере sql вносить и получать с нее данные через интерфейс excel. запросы к серверу почти все через хранимые процедуры(Там и компоновка и выборка и склейка данных). для разных отделов. . есть предложения. можно ли реализовать это через ваш плагин?? или придется всетаки через VBA?

Reply Август 11, 2020 at 23:42

Aleksandr Не нужны никакие надстройки для того чтобы писать даже сложные вложенные запросы и подзапросы к Excel как к базе данных. Изучите и используйте объекты ADODB.Connection и ADODB.Recordset и их основные свойства и методы.

Reply Апрель 7, 2020 at 15:34

Назар ПОдскажите как с помощью этой надстройки и sql написать фильт, который бы фильтровал столбец с «словосочетаниями» по условию «ключевых слов» в другом столбце, то есть, в столбце А1 собраны «ключевые запросы» в столбце А2 собраны «минус слова» Как отфильтровать А1 по словам из А2?

Reply Март 13, 2020 at 09:37

Maria Balobanova Назар, добрый день! Можно написать запрос с использованием оператора WHERE. Он как раз отфильтрует столбец A1 по какому-то минус-слову в столбце A2. Примерно так: SELECT tbl1.[Ключевые запросы], tbl1.[Минус слова] FROM [Таблица1] tbl1 WHERE tbl1.[Минус слова] =»aaa»

Reply Март 13, 2020 at 18:56

Виктория Скажите,как написать запрос? таблицы расположены на разных листах. Их нужно объединить по одному из полей.

Reply Август 7, 2019 at 21:14

Maria Balobanova Виктория, добрый день! Для начала необходимо отформатировать таблицы на этих листах в именованный диапазон с применением стиля таблицы. Далее открыть надстройку SQL Запросы — и в редакторе таблиц вы увидите все доступные таблицы. Выделяя нужные строки, сформируйте запрос с объединением по общему полю (LEFT JOIN). Выше есть описание похожего примера.

Reply Август 9, 2019 at 16:26

Bjorn Melgaard Добрый день Можно ли использовать в вашей надстройке параметрические запросы с Like и как? Например я хочу вывести города которые начинаются на введенную букву Заранее спасибо

Reply Июль 10, 2019 at 18:42

Maria Balobanova Спасибо за вопрос! Да, можно. SQL запрос примерно такой: SELECT tbl1.[Name] FROM [Table] tbl1 where tbl1.[Name] like ‘Абв%’

Reply Июль 10, 2019 at 21:34
Антон Здравствуйте, хотелось бы узнать работает ли функция Date в Вашей программе?
Reply Июнь 8, 2017 at 19:16

Maria Balobanova Антон, да, настройка «SQL Запросы» использует стантарт SQLite и поддерживает его операторы и функции, включая Date. Подробнее о синтаксисе здесь.

Reply Июнь 13, 2017 at 14:07
Daniel Какое максимальное количество строк может быть в исходной таблице?
Reply Декабрь 2, 2016 at 04:29

Maria Balobanova Daniel, надстройка XLTools SQL Запросы своих ограничений не устанавливает. Есть только ограничение Excel — размер листа лимитирован примерно в 1 млн строк (если точнее, 1 048 576 строк и 16 384 столбца). И, конечно, для обработки такого объема данных нужен достаточный размер оперативной памяти. Напишите, как получится — будет интересно узнать на рабочем примере!

Reply Декабрь 2, 2016 at 17:37

Игорь 70 тыс. строк не обрабатывает, пишет не достаточно памяти. Хотя установлено памяти (ОЗУ) 12 гб.

Reply Февраль 9, 2017 at 10:26

Maria Balobanova Игорь, чтобы разобраться, нам нужно больше информации об ошибке и типе запроса. Отправила Вам email.

Reply Февраль 9, 2017 at 13:38
Айнур как можно связать 3 таблицы, чтобы сделать запрос
Reply Апрель 28, 2016 at 15:01

Maria Balobanova Айнур, спасибо за вопрос! Связать три таблицы можно через LEFT JOIN, если у них есть общее поле/столбец. Например: SELECT . FROM [Table1] tbl1 LEFT JOIN [Table2] tbl2 ON tbl1.[общее_поле_1]=tbl2.[общее_поле_1] LEFT JOIN [Table3] tbl3 ON tbl2.[общее_поле_2]=tbl3.[общее_поле_2]

Reply Май 5, 2016 at 18:32

Алла Добрый вечер, Мария! Подскажите, пожалуйста, если я никогда не сталкивалась с SQL запросами, смогу ли я освоить работу с ними? Есть ли какие-нибудь видеоуроки для начинающих? Спасибо!

Reply Март 18, 2016 at 21:26

Maria Balobanova Алла, здравствуйте! Действительно, эта надстройка довольно продвинутая, а материалов у нас пока немного. Кроме общего описания выше, есть небольшое видео тренинга на английском языке. В будущем мы планируем создавать видеоуроки по применению надстройки. Пока могу посоветовать посмотреть в сети видео про основы основы языка SQL. Спасибо за интерес к XLTools!

Reply Март 18, 2016 at 22:20
Евгения Здраствуйте, Мария. Скажите, как можно скачать надстройку? Она запрашивает пароль.
Reply Март 5, 2016 at 14:33

Maria Balobanova Евгения, скачать надстройку XLTools для Вашей версии Excel можно по этой ссылке. Странно, что запрашивает пароль — возможно, речь об активации пробного периода? Посмотрите, пожалуйста, пошаговую инструкцию со скриншотами.

Reply Март 9, 2016 at 14:56
Игорь Возможно ли в будущем увидеть другие функции SQL, такие как UPDATE, INSERT, DELETE?
Reply Февраль 4, 2016 at 09:50

Maria Balobanova Игорь, пока надстройка SQL Запросы поддерживает операторы SELECT и UNION (синтаксис по стандарту SQLite). В будущем — да, рассматриваем возможность расширения функциональности, во многом по спросу пользователей — поэтому спасибо, что написали! Будем рады еще предложениям.

Reply Февраль 4, 2016 at 15:39
Павел что там с оператором Delete?добавили?
Reply Июнь 8, 2016 at 18:38

Maria Balobanova Павел, пока не добавили. Сейчас занимались функционалом Контроля Версий и установщиком для терминальных серверов. Дальше планируем взяться за SQL Запросы.

Reply Июнь 9, 2016 at 15:02

Никита Добрый день. Подскажите, можно ли выполнять запросы к таблицам в модели данных PowerPivot, которые отсутствуют на листах? Если нет, то планируется ли? Спасибо.

Reply Ноябрь 18, 2015 at 22:04
Maria Balobanova Никита, не совсем ясен вопрос, нужно уточнить. Ответила Вам по email.
Reply Ноябрь 23, 2015 at 18:10

Вадим Добрый день! Во-первых, в марте сего года Peter Liapin в комментариях указал, что «в одной из следующих версий мы поддержим возможность выполнения запроса к книге, даже если она не открыта». Хотел бы уточнить, реализована эта возможность или нет в текущей версии надстройки. Дело в том, что у Вас на сайте нет никакой информации о версиях надстроек,новостей о их доработке и узнать об изменениях можно только посредством данного комментария. Во-вторых, в магазине приложений Microsoft цена на данную надстройку на сегодняшний момент установлена в 220 рублей, а у Вас на сайте 698 рублей. В чем разница?

Reply Ноябрь 6, 2015 at 10:15

Вадим Здравствуйте! Хочу возвратиться к моему вопросу от 06 ноября прошлого года. Тогда Вы ответили мне, что, предположительно в декабре в новой версии надстройки XLTools Вы добавите возможность выполнения SQL запроса к закрытым книгам. Хотелось бы узнать, когда всё-таки появится у Вас эта новая версия.

Reply Февраль 11, 2016 at 12:24

Maria Balobanova Вадим, добрый день! Мы перенесли выпуск новой версии, поскольку планируем расширить функционал в т.ч. и других надстроек, помимо SQL Запросов. По срокам ориентировочно в конце февраля. Спасибо за интерес к XLTools!

Reply Февраль 11, 2016 at 13:31

Maria Balobanova Вадим, добрый день! Спасибо, что связались с нами. Отвечая на вопросы: 1. Возможность выполнения SQL запроса к закрытым книгам действительно стоит в плане на разработку, вероятно, что будет уже в следующей версии, ориентировочно в конце декабря. На сайте также в скором времени появится раздел по обновлениям. Сейчас действует версия XLTools 3.5. 2. Надстройка “SQL Запросы” существует в двух вариантах – надстройка с установкой с нашего сайта (для Excel 2007 и позднее, бессрочная лицензия, плюс пакет бесплатных надстроек, сейчас стоит 698 руб.), или надстройка по подписке в Магазине Microsoft (для Excel 2013 и позднее или Excel Online, требуется постоянное подключение к Интернет, стоимость 220 руб./мес.). По функционалу разницы не будет, для создания запросов используется стандарт SQLite. Выбор за Вами. В любом случае перед покупкой рекомендуем взять бесплатную пробную версию.

Reply Ноябрь 6, 2015 at 15:22

Виталий Просто не мог не поблагодарить еще раз. Задача: есть более 30000 позиций и 15 складов, нужно выбрать только товары, которые присутствуют хотя бы на одном из складов. За минуту готовится таблица, минуту на запрос и обработка вместе с сортировкой 4 сек. и это в Excel ФАНТАСТИКА.

Reply Сентябрь 30, 2015 at 12:50

Виталий Надстройка из разряда «must have».Все запросы выполняются в считанные секунды. Любой сложный поиск, фильтрация, отбор данных теперь не проблема. Тех. поддержка на высоте. Удачи и процветания!