SQL – популярный язык программирования, который применяется при работе с базами данных (БД). Хотя для операций с базами данных в пакете Microsoft Office имеется отдельное приложение — Access, но программа Excel тоже может работать с БД, делая SQL запросы. Давайте узнаем, как различными способами можно сформировать подобный запрос.

Читайте также: Как создать базу данных в Экселе

Создание SQL запроса в Excel

Язык запросов SQL отличается от аналогов тем, что с ним работают практически все современные системы управления БД. Поэтому вовсе не удивительно, что такой продвинутый табличный процессор, как Эксель, обладающий многими дополнительными функциями, тоже умеет работать с этим языком. Пользователи, владеющие языком SQL, используя Excel, могут упорядочить множество различных разрозненных табличных данных.

Способ 1: использование надстройки

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

Скачать надстройку XLTools

1. После того, как вы скачали файл надстройки xltools.exe, следует приступить к его установке. Для запуска инсталлятора нужно произвести двойной щелчок левой кнопки мыши по установочному файлу. После этого запустится окно, в котором нужно будет подтвердить согласие с лицензионным соглашением на использование продукции компании Microsoft — NET Framework 4. Для этого всего лишь нужно кликнуть по кнопке «Принимаю» внизу окошка.
2. После этого установщик производит загрузку обязательных файлов и начинает процесс их установки.
3. Далее откроется окно, в котором вы должны подтвердить свое согласие на установку этой надстройки. Для этого нужно щелкнуть по кнопке «Установить».
4. Затем начинается процедура установки непосредственно самой надстройки.
5. После её завершения откроется окно, в котором будет сообщаться, что инсталляция успешно выполнена. В указанном окне достаточно нажать на кнопку «Закрыть».
6. Надстройка установлена и теперь можно запускать файл Excel, в котором нужно организовать SQL запрос. Вместе с листом Эксель открывается окно для ввода кода лицензии XLTools. Если у вас имеется код, то нужно ввести его в соответствующее поле и нажать на кнопку «OK». Если вы желаете использовать бесплатную версию на 14 дней, то следует просто нажать на кнопку «Пробная лицензия».
7. При выборе пробной лицензии открывается ещё одно небольшое окошко, где нужно указать своё имя и фамилию (можно псевдоним) и электронную почту. После этого жмите на кнопку «Начать пробный период».
8. Далее мы возвращаемся к окну лицензии. Как видим, введенные вами значения уже отображаются. Теперь нужно просто нажать на кнопку «OK».
9. После того, как вы проделаете вышеуказанные манипуляции, в вашем экземпляре Эксель появится новая вкладка – «XLTools». Но не спешим переходить в неё. Прежде, чем создавать запрос, нужно преобразовать табличный массив, с которым мы будем работать, в так называемую, «умную» таблицу и присвоить ей имя.
   Для этого выделяем указанный массив или любой его элемент. Находясь во вкладке «Главная» щелкаем по значку «Форматировать как таблицу». Он размещен на ленте в блоке инструментов «Стили». После этого открывается список выбора различных стилей. Выбираем тот стиль, который вы считаете нужным. На функциональность таблицы указанный выбор никак не повлияет, так что основывайте свой выбор исключительно на основе предпочтений визуального отображения.
10. Вслед за этим запускается небольшое окошко. В нем указываются координаты таблицы. Как правило, программа сама «подхватывает» полный адрес массива, даже если вы выделили только одну ячейку в нем. Но на всякий случай не мешает проверить ту информацию, которая находится в поле «Укажите расположение данных таблицы». Также нужно обратить внимание, чтобы около пункта «Таблица с заголовками», стояла галочка, если заголовки в вашем массиве действительно присутствуют. Затем жмите на кнопку «OK».
11. После этого весь указанный диапазон будет отформатирован, как таблица, что повлияет как на его свойства (например, растягивание), так и на визуальное отображение. Указанной таблице будет присвоено имя. Чтобы его узнать и по желанию изменить, клацаем по любому элементу массива. На ленте появляется дополнительная группа вкладок – «Работа с таблицами». Перемещаемся во вкладку «Конструктор», размещенную в ней. На ленте в блоке инструментов «Свойства» в поле «Имя таблицы» будет указано наименование массива, которое ему присвоила программа автоматически.
12. При желании это наименование пользователь может изменить на более информативное, просто вписав в поле с клавиатуры желаемый вариант и нажав на клавишу Enter.
13. После этого таблица готова и можно переходить непосредственно к организации запроса. Перемещаемся во вкладку «XLTools».
14. После перехода на ленте в блоке инструментов «SQL запросы» щелкаем по значку «Выполнить SQL».
15. Запускается окно выполнения SQL запроса. В левой его области следует указать лист документа и таблицу на древе данных, к которой будет формироваться запрос.

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

    Далее пишется текст команды, которую вы хотите применить к выбранным объектам. Команды составляются при помощи специальных операторов. Вот основные операторы SQL:

    • ORDER BY – сортировка значений;
    • JOIN – объединение таблиц;
    • GROUP BY – группировка значений;
    • SUM – суммирование значений;
    • DISTINCT – удаление дубликатов.

    Кроме того, в построении запроса можно использовать операторы MAX, MIN, AVG, COUNT, LEFT и др.

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

    После того, как запрос составлен и соответствующие настройки произведены, жмем на кнопку «Выполнить» в нижней части окна. После этого введенная операция будет произведена.

Урок: «Умные» таблицы в Экселе

Способ 2: использование встроенных инструментов Excel

Существует также способ создать SQL запрос к выбранному источнику данных с помощью встроенных инструментов Эксель.

1. Запускаем программу Excel. После этого перемещаемся во вкладку «Данные».
2. В блоке инструментов «Получение внешних данных», который расположен на ленте, жмем на значок «Из других источников». Открывается список дальнейших вариантов действий. Выбираем в нем пункт «Из мастера подключения данных».
3. Запускается Мастер подключения данных. В перечне типов источников данных выбираем «ODBC DSN». После этого щелкаем по кнопке «Далее».
4. Открывается окно Мастера подключения данных, в котором нужно выбрать тип источника. Выбираем наименование «MS Access Database». Затем щелкаем по кнопке «Далее».
5. Открывается небольшое окошко навигации, в котором следует перейти в директорию расположения базы данных в формате mdb или accdb и выбрать нужный файл БД. Навигация между логическими дисками при этом производится в специальном поле «Диски». Между каталогами производится переход в центральной области окна под названием «Каталоги». В левой области окна отображаются файлы, расположенные в текущем каталоге, если они имеют расширение mdb или accdb. Именно в этой области нужно выбрать наименование файла, после чего кликнуть на кнопку «OK».
6. Вслед за этим запускается окно выбора таблицы в указанной базе данных. В центральной области следует выбрать наименование нужной таблицы (если их несколько), а потом нажать на кнопку «Далее».
7. После этого открывается окно сохранения файла подключения данных. Тут указаны основные сведения о подключении, которое мы настроили. В данном окне достаточно нажать на кнопку «Готово».
8. На листе Excel запускается окошко импорта данных. В нем можно указать, в каком именно виде вы хотите, чтобы данные были представлены:
   • Таблица;
   • Отчёт сводной таблицы;
   • Сводная диаграмма.

   Выбираем нужный вариант. Чуть ниже требуется указать, куда именно следует поместить данные: на новый лист или на текущем листе. В последнем случае предоставляется также возможность выбора координат размещения. По умолчанию данные размещаются на текущем листе. Левый верхний угол импортируемого объекта размещается в ячейке A1.

   После того, как все настройки импорта указаны, жмем на кнопку «OK».

9. Как видим, таблица из базы данных перемещена на лист. Затем перемещаемся во вкладку «Данные» и щелкаем по кнопке «Подключения», которая размещена на ленте в блоке инструментов с одноименным названием.
10. После этого запускается окно подключения к книге. В нем мы видим наименование ранее подключенной нами базы данных. Если подключенных БД несколько, то выбираем нужную и выделяем её. После этого щелкаем по кнопке «Свойства…» в правой части окна.
11. Запускается окно свойств подключения. Перемещаемся в нем во вкладку «Определение». В поле «Текст команды», находящееся внизу текущего окна, записываем SQL команду в соответствии с синтаксисом данного языка, о котором мы вкратце говорили при рассмотрении Способа 1. Затем жмем на кнопку «OK».
12. После этого производится автоматический возврат к окну подключения к книге. Нам остается только кликнуть по кнопке «Обновить» в нем. Происходит обращение к базе данных с запросом, после чего БД возвращает результаты его обработки назад на лист Excel, в ранее перенесенную нами таблицу.

Способ 3: подключение к серверу SQL Server

Кроме того, посредством инструментов Excel существует возможность соединения с сервером SQL Server и посыла к нему запросов. Построение запроса не отличается от предыдущего варианта, но прежде всего, нужно установить само подключение. Посмотрим, как это сделать.

1. Запускаем программу Excel и переходим во вкладку «Данные». После этого щелкаем по кнопке «Из других источников», которая размещается на ленте в блоке инструментов «Получение внешних данных». На этот раз из раскрывшегося списка выбираем вариант «С сервера SQL Server».
2. Происходит открытие окна подключения к серверу баз данных. В поле «Имя сервера» указываем наименование того сервера, к которому выполняем подключение. В группе параметров «Учетные сведения» нужно определиться, как именно будет происходить подключение: с использованием проверки подлинности Windows или путем введения имени пользователя и пароля. Выставляем переключатель согласно принятому решению. Если вы выбрали второй вариант, то кроме того в соответствующие поля придется ввести имя пользователя и пароль. После того, как все настройки проведены, жмем на кнопку «Далее». После выполнения этого действия происходит подключение к указанному серверу. Дальнейшие действия по организации запроса к базе данных аналогичны тем, которые мы описывали в предыдущем способе.

Как видим, в Экселе SQL запрос можно организовать, как встроенными инструментами программы, так и при помощи сторонних надстроек. Каждый пользователь может выбрать тот вариант, который удобнее для него и является более подходящим для решения конкретно поставленной задачи. Хотя, возможности надстройки XLTools, в целом, все-таки несколько более продвинутые, чем у встроенных инструментов Excel. Главный же недостаток XLTools заключается в том, что срок бесплатного пользования надстройкой ограничен всего двумя календарными неделями.

Мы рады, что смогли помочь Вам в решении проблемы.

Задайте свой вопрос в комментариях, подробно расписав суть проблемы. Наши специалисты постараются ответить максимально быстро.

Помогла ли вам эта статья?

Да Нет

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

Рассмотрим один из них, который делается на базовых инструментах Excel.

SQL запрос на базовых инструментах ExcelПосле открытия программы Excel ищем на панели «Данные» и жмем на кнопку. Откроется панель, идем «получение внешних данных» — «из других источников», после нажатия на кнопку «из других источников» — работаем с кнопкой «из мастера подключений внешних данных»

Нажимая на эту кнопку, запускаем мастер подключений данных.

На экране увидите новое окошко мастера подключений, и выбираем из предложенных вариантов «ODBC DSN». После выбора жмем «далее» и попадаем в следующее окно меню. Делаем выбор в пользу «MS Access Database», подтверждаем выбор, нажимая на кнопку «далее».

После всех вышеописанных действий перед нами выскочит окно

«Выбор базы данных». Переходим в этом окошке в «имя базы данных» и выбираем, как указано на рисунке ниже. Следует отметить, что выбор формата должен быть mdb, accdb. И соответственно выбираем, где лежит файл базы данных сначала диск, смотрим вниз окошка, а затем и нужную папку. Обнаружив необходимую папку – жмем «ОК»

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

В открывшемся меню мастера подключений находим кнопку «Готово». и жмем на нее.

Теперь мы попадаем на лист Excel и видим открытое окно «Импорт данных». Следующим действием нам предлагается выбрать нужный нам вариант просмотра данных. Вариантов три: таблица, отчет сводной таблицы и сводная диаграмма. Выбираем один из предлагаемых вариантов и указываем где мы хотим это видеть. Тут два варианта: текущий лист или новый лист. По умолчанию данные расположатся на текущем листе и начнутся с А1 ячейки. Жмем «ОК».

Мастер переместил данные таблицы из БД на наш лист. Следующим действием идем на «Данные», затем «Подключения» жмем «Подключения»

Таким образом, выходим на окно «Подключения к книге».

Тут видим название уже знакомой нашей базы данных, выбираем ее, если есть еще список других БД, и жмем на кнопку «свойства».

Выскакивает окно под названием «Свойства подключения».

Нам в этом окне нужна кнопка «Определение». Находим «Текст команды» и жмем «ОК».

Excel нас откинет к окну «Подключение к книге». Находим «Обновить»

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

Таким образом, запросы SQL в Excel выполнили свои задачи.

1. Перейдите на вкладку «Данные» и выберите «Из других источников», как показано ниже.

2. В выпадающем меню выберите “Из мастера подключения данных”.

3. Откроется Мастер подключения данных. Из доступных вариантов выберите “ODBC DSN” и нажмите “Далее”.

4. Появится окно «Подключение к источнику данных ODBC». Там будет показан список баз данных, доступных в вашей организации. Выберите соответствующую базу данных и нажмите “Далее”.

5. Появится окно выбора базы данных и таблицы.

6. Мы можем выбрать базу данных и таблицу, откуда хотим получать данные. Соответственно, выберите нужную базу данных и таблицу.

7. В окне “Сохранить файл подключения к данным и завершить” выберите «Завершить». Это окно выберет имя файла на основе вашего выбора на предыдущих экранах.

8. Появится окно импортирования данных, где мы можем выбрать нужные варианты и нажать OK.

9. В выпадающем меню выберите “Из мастера подключения данных”.

10. Перейдите на вкладку «Данные» и нажмите на «Соединения». В следующем окне нажмите на «Свойства».

11. В следующем окне перейдите на вкладку «Определения».

12. В поле “Текст команды” введите SQL-запрос и нажмите OK. Excel отобразит результат согласно запроса.

13. Теперь перейдите в Microsoft Excel и проверьте, что результаты соответствуют указанному SQL-запросу.

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

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

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

Добавить «SQL запросы» в Excel 2016, 2013, 2010, 2007

Подходит для: Microsoft Excel 2016 – 2007, desktop Office 365 (32-бит и 64-бит).

Скачать надстройку XLTools

Как работать с надстройкой:

• Как превратить данные Excel в реляционную базу данных и подготовить их к работе с SQL запросами
• Как создать и выполнить запрос SQL SELECT к таблицам Excel
• Операторы Left Join, Order By, Group By, Distinct и другие SQLite команды в Excel
• Как объединить две и более Excel таблиц с помощью надстройки «SQL запросы»

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

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

1. Выделите диапазон данных > На вкладке «Главная» нажмите «Форматировать как таблицу» > Примените стиль таблицы.
2. Выберите эту таблицу > Откройте вкладку «Конструктор» > Напечатайте имя таблицы.
   Напр., «КодТовара».
3. Повторите эти шаги для каждого диапазона, который планируете использовать в запросах.
   «КодТовара», «ЦенаРозн», «ОбъемПродаж», т.д.
4. Готово, теперь эти таблицы будут служить реляционной базой данных и готовы к SQL запросам.

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

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

1. Нажмите кнопку «Выполнить SQL» на вкладке XLTools > Откроется окно редактора.
2. В левой части окна находится дерево данных со всеми доступными таблицами Excel.
   Нажатием на узлы открываются/сворачиваются поля таблицы (столбцы).
3. Выберите целые талицы или конкретные поля.
   По мере выбора полей, в правой части редактора автоматически генерируется запрос SELECT.
   Обратите внимание: редактор запросов SQL автоматически подсвечивает систаксис.
4. Укажите, куда необходимо поместить результат запроса: на новый или существующий лист.
5. Нажмите кнопку «Выполнить» > Готово!

Операторы 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 в одну, если у них есть общее ключевое поле. Предположим, вам нужно объединить несколько таблиц по общему столбцу «КодТовара»:

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

Появились вопросы или предложения? Оставьте комментарий ниже.