Используйте возможности 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 в таблицу (именованный диапазон с применением стиля таблицы):

  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. Нажмите «Выполнить» Готово! Объединённая таблица появится в считанные секунды.
Any questions or suggestions?
Антон С.Сделал все как написано выше. (данные получить с SQL не проблема и с помощью стандартных средств екселя). меняю значения в таблице и на сервер ничего не улетает. да и прилетают данные походу с соседней вкладки (копипастит попросту). выдаёт ошибку при обращении к хранимым процедурам. Мне необходимо получить таблицу которая храниться на сервере sql вносить и получать с нее данные через интерфейс excel. запросы к серверу почти все через хранимые процедуры(Там и компоновка и выборка и склейка данных). для разных отделов. ...есть предложения?... можно ли реализовать это через ваш плагин?? или придется всетаки через VBA?
Август 11, 2020 at 23:42
AleksandrНе нужны никакие надстройки для того чтобы писать даже сложные вложенные запросы и подзапросы к Excel как к базе данных. Изучите и используйте объекты ADODB.Connection и ADODB.Recordset и их основные свойства и методы.
Апрель 7, 2020 at 15:34
НазарПОдскажите как с помощью этой надстройки и sql написать фильт, который бы фильтровал столбец с "словосочетаниями" по условию "ключевых слов" в другом столбце, то есть, в столбце А1 собраны "ключевые запросы" в столбце А2 собраны "минус слова" Как отфильтровать А1 по словам из А2?
Март 13, 2020 at 09:37
Maria BalobanovaНазар, добрый день! Можно написать запрос с использованием оператора WHERE. Он как раз отфильтрует столбец A1 по какому-то минус-слову в столбце A2. Примерно так: SELECT tbl1.[Ключевые запросы], tbl1.[Минус слова] FROM [Таблица1] tbl1 WHERE tbl1.[Минус слова] ="aaa"
Март 13, 2020 at 18:56
ВикторияСкажите,как написать запрос? таблицы расположены на разных листах. Их нужно объединить по одному из полей.
Август 7, 2019 at 21:14
Maria BalobanovaВиктория, добрый день! Для начала необходимо отформатировать таблицы на этих листах в именованный диапазон с применением стиля таблицы. Далее открыть надстройку SQL Запросы - и в редакторе таблиц вы увидите все доступные таблицы. Выделяя нужные строки, сформируйте запрос с объединением по общему полю (LEFT JOIN). Выше есть описание похожего примера.
Август 9, 2019 at 16:26
Bjorn Melgaard Добрый день Можно ли использовать в вашей надстройке параметрические запросы с Like и как? Например я хочу вывести города которые начинаются на введенную букву Заранее спасибо
Июль 10, 2019 at 18:42
Maria BalobanovaСпасибо за вопрос! Да, можно. SQL запрос примерно такой: SELECT tbl1.[Name] FROM [Table] tbl1 where tbl1.[Name] like 'Абв%'
Июль 10, 2019 at 21:34
АнтонЗдравствуйте, хотелось бы узнать работает ли функция Date в Вашей программе?
Июнь 8, 2017 at 19:16
Maria BalobanovaАнтон, да, настройка "SQL Запросы" использует стантарт SQLite и поддерживает его операторы и функции, включая Date. Подробнее о синтаксисе здесь.
Июнь 13, 2017 at 14:07
DanielКакое максимальное количество строк может быть в исходной таблице?
Декабрь 2, 2016 at 04:29
Maria BalobanovaDaniel, надстройка XLTools SQL Запросы своих ограничений не устанавливает. Есть только ограничение Excel - размер листа лимитирован примерно в 1 млн строк (если точнее, 1 048 576 строк и 16 384 столбца). И, конечно, для обработки такого объема данных нужен достаточный размер оперативной памяти. Напишите, как получится - будет интересно узнать на рабочем примере!
Декабрь 2, 2016 at 17:37
Игорь70 тыс. строк не обрабатывает, пишет не достаточно памяти. Хотя установлено памяти (ОЗУ) 12 гб.
Февраль 9, 2017 at 10:26
Maria BalobanovaИгорь, чтобы разобраться, нам нужно больше информации об ошибке и типе запроса. Отправила Вам email.
Февраль 9, 2017 at 13:38
Айнуркак можно связать 3 таблицы, чтобы сделать запрос
Апрель 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]
Май 5, 2016 at 18:32
АллаДобрый вечер, Мария! Подскажите, пожалуйста, если я никогда не сталкивалась с SQL запросами, смогу ли я освоить работу с ними? Есть ли какие-нибудь видеоуроки для начинающих? Спасибо!
Март 18, 2016 at 21:26
Maria BalobanovaАлла, здравствуйте! Действительно, эта надстройка довольно продвинутая, а материалов у нас пока немного. Кроме общего описания выше, есть небольшое видео тренинга на английском языке. В будущем мы планируем создавать видеоуроки по применению надстройки. Пока могу посоветовать посмотреть в сети видео про основы основы языка SQL. Спасибо за интерес к XLTools!
Март 18, 2016 at 22:20
ЕвгенияЗдраствуйте, Мария. Скажите, как можно скачать надстройку? Она запрашивает пароль.
Март 5, 2016 at 14:33
Maria BalobanovaЕвгения, скачать надстройку XLTools для Вашей версии Excel можно по этой ссылке. Странно, что запрашивает пароль - возможно, речь об активации пробного периода? Посмотрите, пожалуйста, пошаговую инструкцию со скриншотами.
Март 9, 2016 at 14:56
ИгорьВозможно ли в будущем увидеть другие функции SQL, такие как UPDATE, INSERT, DELETE?
Февраль 4, 2016 at 09:50
Maria BalobanovaИгорь, пока надстройка SQL Запросы поддерживает операторы SELECT и UNION (синтаксис по стандарту SQLite). В будущем - да, рассматриваем возможность расширения функциональности, во многом по спросу пользователей - поэтому спасибо, что написали! Будем рады еще предложениям.
Февраль 4, 2016 at 15:39
Павелчто там с оператором Delete?добавили?
Июнь 8, 2016 at 18:38
Maria BalobanovaПавел, пока не добавили. Сейчас занимались функционалом Контроля Версий и установщиком для терминальных серверов. Дальше планируем взяться за SQL Запросы.
Июнь 9, 2016 at 15:02
НикитаДобрый день. Подскажите, можно ли выполнять запросы к таблицам в модели данных PowerPivot, которые отсутствуют на листах? Если нет, то планируется ли? Спасибо.
Ноябрь 18, 2015 at 22:04
Maria BalobanovaНикита, не совсем ясен вопрос, нужно уточнить. Ответила Вам по email.
Ноябрь 23, 2015 at 18:10
ВадимДобрый день! Во-первых, в марте сего года Peter Liapin в комментариях указал, что "в одной из следующих версий мы поддержим возможность выполнения запроса к книге, даже если она не открыта". Хотел бы уточнить, реализована эта возможность или нет в текущей версии надстройки. Дело в том, что у Вас на сайте нет никакой информации о версиях надстроек,новостей о их доработке и узнать об изменениях можно только посредством данного комментария. Во-вторых, в магазине приложений Microsoft цена на данную надстройку на сегодняшний момент установлена в 220 рублей, а у Вас на сайте 698 рублей. В чем разница?
Ноябрь 6, 2015 at 10:15
ВадимЗдравствуйте! Хочу возвратиться к моему вопросу от 06 ноября прошлого года. Тогда Вы ответили мне, что, предположительно в декабре в новой версии надстройки XLTools Вы добавите возможность выполнения SQL запроса к закрытым книгам. Хотелось бы узнать, когда всё-таки появится у Вас эта новая версия.
Февраль 11, 2016 at 12:24
Maria BalobanovaВадим, добрый день! Мы перенесли выпуск новой версии, поскольку планируем расширить функционал в т.ч. и других надстроек, помимо SQL Запросов. По срокам ориентировочно в конце февраля. Спасибо за интерес к XLTools!
Февраль 11, 2016 at 13:31
Maria BalobanovaВадим, добрый день! Спасибо, что связались с нами. Отвечая на вопросы: 1. Возможность выполнения SQL запроса к закрытым книгам действительно стоит в плане на разработку, вероятно, что будет уже в следующей версии, ориентировочно в конце декабря. На сайте также в скором времени появится раздел по обновлениям. Сейчас действует версия XLTools 3.5. 2. Надстройка “SQL Запросы” существует в двух вариантах – надстройка с установкой с нашего сайта (для Excel 2007 и позднее, бессрочная лицензия, плюс пакет бесплатных надстроек, сейчас стоит 698 руб.), или надстройка по подписке в Магазине Microsoft (для Excel 2013 и позднее или Excel Online, требуется постоянное подключение к Интернет, стоимость 220 руб./мес.). По функционалу разницы не будет, для создания запросов используется стандарт SQLite. Выбор за Вами. В любом случае перед покупкой рекомендуем взять бесплатную пробную версию.
Ноябрь 6, 2015 at 15:22
ВиталийПросто не мог не поблагодарить еще раз. Задача: есть более 30000 позиций и 15 складов, нужно выбрать только товары, которые присутствуют хотя бы на одном из складов. За минуту готовится таблица, минуту на запрос и обработка вместе с сортировкой 4 сек!!! и это в Excel ФАНТАСТИКА!!!
Сентябрь 30, 2015 at 12:50
ВиталийНадстройка из разряда "must have".Все запросы выполняются в считанные секунды. Любой сложный поиск, фильтрация, отбор данных теперь не проблема. Тех. поддержка на высоте. Удачи и процветания!
Сентябрь 21, 2015 at 17:44
Maria BalobanovaВиталий, спасибо! Рады, что оказались полезными.
Сентябрь 21, 2015 at 17:48
АлексейЗапросы к таблицам из разных книг возможно выполнять?
Март 4, 2015 at 22:11
Peter LiapinАлексей, да, можно. Чтобы выполнить такой запрос, все нужные книги должны быть открыты, в таком случае редактор SQL запроса покажет Вам все доступные таблицы из всех книг, и Вы сможете построить по ним запрос. Думаю, в одной из следующих версий мы поддержим возможность выполнения запроса к книге, даже если она не открыта, но в таком случае она должна быть расположена в той же папке, что и исходный документ.
Март 5, 2015 at 02:09
How can we help?