Порой таблицы Excel постепенно разрастаются настолько, что с ними становится неудобно работать. Поиск дубликатов, группировка, сложная сортировка, объединение нескольких таблиц в одну, т.д. — превращаются в действительно трудоёмкие задачи. Теоретически эти задачи можно легко решить с помощью языка запросов SQL… если бы только можно было составлять запросы напрямую к данным Excel.
Надстройка XLTools «SQL запросы» расширит Excel возможностями языка структурированных запросов:
- Создание запросов SQL в интерфейсе Excel и напрямую к Excel таблицам
- Автогенерация запросов SELECT и JOIN
- Доступны JOIN, ORDER BY, DISTINCT, GROUP BY, SUM и другие операторы SQLite
- Создание запросов в интуитивном редакторе с подстветкой синтаксиса
- Обращение к любым таблицам Excel из дерева данных
Добавить «SQL запросы» в Excel 2019, 2016, 2013, 2010
Подходит для: Microsoft Excel 2019 – 2010, 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 в таблицу (именованный диапазон с применением стиля таблицы):
- Выделите диапазон данных > На вкладке «Главная» нажмите «Форматировать как таблицу» > Примените стиль таблицы.
-
Выберите эту таблицу > Откройте вкладку «Конструктор» > Напечатайте имя таблицы.
Напр., «КодТовара». -
Повторите эти шаги для каждого диапазона, который планируете использовать в запросах.
«КодТовара», «ЦенаРозн», «ОбъемПродаж», т.д. - Готово, теперь эти таблицы будут служить реляционной базой данных и готовы к 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. - Укажите, куда необходимо поместить результат запроса: на новый или существующий лист.
- Нажмите «Выполнить» > Готово! Объединённая таблица появится в считанные секунды.
Не нужны никакие надстройки для того чтобы писать даже сложные вложенные запросы и подзапросы к Excel как к базе данных. Изучите и используйте объекты ADODB.Connection и ADODB.Recordset и их основные свойства и методы.
Aleksandr, спасибо. Вы правы, это тоже вариант. Excel очень гибкий, при умении можно выполнить много всего и разными способами. С надстройкой проще и быстрее 😉
ПОдскажите
как с помощью этой надстройки и sql
написать фильт, который бы фильтровал столбец с «словосочетаниями» по условию «ключевых слов» в другом столбце, то есть,
в столбце А1 собраны «ключевые запросы»
в столбце А2 собраны «минус слова»
Как отфильтровать А1 по словам из А2?
Назар, добрый день!
Можно написать запрос с использованием оператора WHERE. Он как раз отфильтрует столбец A1 по какому-то минус-слову в столбце A2. Примерно так:
SELECT
tbl1.[Ключевые запросы],
tbl1.[Минус слова]
FROM [Таблица1] tbl1
WHERE tbl1.[Минус слова] =»aaa»
Скажите,как написать запрос? таблицы расположены на разных листах. Их нужно объединить по одному из полей.
Виктория, добрый день! Для начала необходимо отформатировать таблицы на этих листах в именованный диапазон с применением стиля таблицы. Далее открыть надстройку SQL Запросы — и в редакторе таблиц вы увидите все доступные таблицы. Выделяя нужные строки, сформируйте запрос с объединением по общему полю (LEFT JOIN). Выше есть описание похожего примера.
Добрый день
Можно ли использовать в вашей надстройке параметрические запросы с Like и как? Например я хочу вывести города которые начинаются на введенную букву
Заранее спасибо
Спасибо за вопрос! Да, можно. SQL запрос примерно такой:
SELECT
tbl1.[Name]
FROM [Table] tbl1 where tbl1.[Name] like ‘Абв%’
Здравствуйте, хотелось бы узнать работает ли функция Date в Вашей программе?
Антон, да, настройка «SQL Запросы» использует стантарт SQLite и поддерживает его операторы и функции, включая Date. Подробнее о синтаксисе здесь.
Какое максимальное количество строк может быть в исходной таблице?
Daniel, надстройка XLTools SQL Запросы своих ограничений не устанавливает. Есть только ограничение Excel — размер листа лимитирован примерно в 1 млн строк (если точнее, 1 048 576 строк и 16 384 столбца). И, конечно, для обработки такого объема данных нужен достаточный размер оперативной памяти. Напишите, как получится — будет интересно узнать на рабочем примере!
70 тыс. строк не обрабатывает, пишет не достаточно памяти. Хотя установлено памяти (ОЗУ) 12 гб.
Игорь, чтобы разобраться, нам нужно больше информации об ошибке и типе запроса. Отправила Вам email.
как можно связать 3 таблицы, чтобы сделать запрос
Айнур, спасибо за вопрос!
Связать три таблицы можно через LEFT JOIN, если у них есть общее поле/столбец. Например:
SELECT
…
FROM [Table1] tbl1
LEFT JOIN [Table2] tbl2 ON tbl1.[общее_поле_1]=tbl2.[общее_поле_1]
LEFT JOIN [Table3] tbl3 ON tbl2.[общее_поле_2]=tbl3.[общее_поле_2]
Добрый вечер, Мария! Подскажите, пожалуйста, если я никогда не сталкивалась с SQL запросами, смогу ли я освоить работу с ними? Есть ли какие-нибудь видеоуроки для начинающих? Спасибо!
Алла, здравствуйте! Действительно, эта надстройка довольно продвинутая, а материалов у нас пока немного. Кроме общего описания выше, есть небольшое видео тренинга на английском языке. В будущем мы планируем создавать видеоуроки по применению надстройки. Пока могу посоветовать посмотреть в сети видео про основы основы языка SQL. Спасибо за интерес к XLTools!
Здраствуйте, Мария. Скажите, как можно скачать надстройку? Она запрашивает пароль.
Евгения, скачать надстройку XLTools для Вашей версии Excel можно по этой ссылке. Странно, что запрашивает пароль — возможно, речь об активации пробного периода? Посмотрите, пожалуйста, пошаговую инструкцию со скриншотами.
Возможно ли в будущем увидеть другие функции SQL, такие как UPDATE, INSERT, DELETE?
Игорь, пока надстройка SQL Запросы поддерживает операторы SELECT и UNION (синтаксис по стандарту SQLite). В будущем — да, рассматриваем возможность расширения функциональности, во многом по спросу пользователей — поэтому спасибо, что написали! Будем рады еще предложениям.
что там с оператором Delete?добавили?
Павел, пока не добавили. Сейчас занимались функционалом Контроля Версий и установщиком для терминальных серверов. Дальше планируем взяться за SQL Запросы.
Добрый день.
Подскажите, можно ли выполнять запросы к таблицам в модели данных PowerPivot, которые отсутствуют на листах? Если нет, то планируется ли?
Спасибо.
Никита, не совсем ясен вопрос, нужно уточнить. Ответила Вам по email.
Добрый день!
Во-первых, в марте сего года Peter Liapin в комментариях указал, что «в одной из следующих версий мы поддержим возможность выполнения запроса к книге, даже если она не открыта». Хотел бы уточнить, реализована эта возможность или нет в текущей версии надстройки. Дело в том, что у Вас на сайте нет никакой информации о версиях надстроек,новостей о их доработке и узнать об изменениях можно только посредством данного комментария.
Во-вторых, в магазине приложений Microsoft цена на данную надстройку на сегодняшний момент установлена в 220 рублей, а у Вас на сайте 698 рублей. В чем разница?
Вадим, добрый день! Спасибо, что связались с нами. Отвечая на вопросы:
1. Возможность выполнения SQL запроса к закрытым книгам действительно стоит в плане на разработку, вероятно, что будет уже в следующей версии, ориентировочно в конце декабря. На сайте также в скором времени появится раздел по обновлениям. Сейчас действует версия XLTools 3.5.
2. Надстройка “SQL Запросы” существует в двух вариантах – надстройка с установкой с нашего сайта (для Excel 2007 и позднее, бессрочная лицензия, плюс пакет бесплатных надстроек, сейчас стоит 698 руб.), или надстройка по подписке в Магазине Microsoft (для Excel 2013 и позднее или Excel Online, требуется постоянное подключение к Интернет, стоимость 220 руб./мес.). По функционалу разницы не будет, для создания запросов используется стандарт SQLite.
Выбор за Вами. В любом случае перед покупкой рекомендуем взять бесплатную пробную версию.
Здравствуйте!
Хочу возвратиться к моему вопросу от 06 ноября прошлого года. Тогда Вы ответили мне, что, предположительно в декабре в новой версии надстройки XLTools Вы добавите возможность выполнения SQL запроса к закрытым книгам. Хотелось бы узнать, когда всё-таки появится у Вас эта новая версия.
Вадим, добрый день! Мы перенесли выпуск новой версии, поскольку планируем расширить функционал в т.ч. и других надстроек, помимо SQL Запросов. По срокам ориентировочно в конце февраля. Спасибо за интерес к XLTools!
Просто не мог не поблагодарить еще раз. Задача: есть более 30000 позиций и 15 складов, нужно выбрать только товары, которые присутствуют хотя бы на одном из складов. За минуту готовится таблица, минуту на запрос и обработка вместе с сортировкой 4 сек!!! и это в Excel ФАНТАСТИКА!!!
Надстройка из разряда «must have».Все запросы выполняются в считанные секунды. Любой сложный поиск, фильтрация, отбор данных теперь не проблема. Тех. поддержка на высоте. Удачи и процветания!
Виталий, спасибо! Рады, что оказались полезными.
Запросы к таблицам из разных книг возможно выполнять?
Алексей, да, можно. Чтобы выполнить такой запрос, все нужные книги должны быть открыты, в таком случае редактор SQL запроса покажет Вам все доступные таблицы из всех книг, и Вы сможете построить по ним запрос.
Думаю, в одной из следующих версий мы поддержим возможность выполнения запроса к книге, даже если она не открыта, но в таком случае она должна быть расположена в той же папке, что и исходный документ.