Используйте возможности 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 2024, 2019, 2016, 2013, 2010, Microsoft 365.
Как превратить данные 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.
- Укажите, куда необходимо поместить результат запроса: на новый или существующий лист.
- Нажмите «Выполнить» Готово! Объединённая таблица появится в считанные секунды.
Помощи детальной нет, контекстной подсказки нет, привычные команды MS SQL не работают
Сложный SQL с несколькими выборками из одной и той же таблицы не работает
Предлагаемые функции лишь обьединяют таблицы
После нескольких экспериментов ексель завис и после перезапуска надстройка исчезла, перезапуск компа не помог, как ее запустить непонятно
Доработайте надстройку, создайте помощь и потом предлагайте людям
Зачем впаривать за 50 баксов сырое недоработанное г-но ????!!!
как с помощью этой надстройки и sql
написать фильт, который бы фильтровал столбец с "словосочетаниями" по условию "ключевых слов" в другом столбце, то есть,
в столбце А1 собраны "ключевые запросы"
в столбце А2 собраны "минус слова"
Как отфильтровать А1 по словам из А2?
Можно написать запрос с использованием оператора WHERE. Он как раз отфильтрует столбец A1 по какому-то минус-слову в столбце A2. Примерно так:
SELECT
tbl1.[Ключевые запросы],
tbl1.[Минус слова]
FROM [Таблица1] tbl1
WHERE tbl1.[Минус слова] ="aaa"
Добрый день
Можно ли использовать в вашей надстройке параметрические запросы с Like и как? Например я хочу вывести города которые начинаются на введенную букву
Заранее спасибо
SELECT
tbl1.[Name]
FROM [Table] tbl1 where tbl1.[Name] like 'Абв%'
Связать три таблицы можно через LEFT JOIN, если у них есть общее поле/столбец. Например:
SELECT
...
FROM [Table1] tbl1
LEFT JOIN [Table2] tbl2 ON tbl1.[общее_поле_1]=tbl2.[общее_поле_1]
LEFT JOIN [Table3] tbl3 ON tbl2.[общее_поле_2]=tbl3.[общее_поле_2]
Подскажите, можно ли выполнять запросы к таблицам в модели данных PowerPivot, которые отсутствуют на листах? Если нет, то планируется ли?
Спасибо.
Во-первых, в марте сего года Peter Liapin в комментариях указал, что "в одной из следующих версий мы поддержим возможность выполнения запроса к книге, даже если она не открыта". Хотел бы уточнить, реализована эта возможность или нет в текущей версии надстройки. Дело в том, что у Вас на сайте нет никакой информации о версиях надстроек,новостей о их доработке и узнать об изменениях можно только посредством данного комментария.
Во-вторых, в магазине приложений Microsoft цена на данную надстройку на сегодняшний момент установлена в 220 рублей, а у Вас на сайте 698 рублей. В чем разница?
Хочу возвратиться к моему вопросу от 06 ноября прошлого года. Тогда Вы ответили мне, что, предположительно в декабре в новой версии надстройки XLTools Вы добавите возможность выполнения SQL запроса к закрытым книгам. Хотелось бы узнать, когда всё-таки появится у Вас эта новая версия.
1. Возможность выполнения SQL запроса к закрытым книгам действительно стоит в плане на разработку, вероятно, что будет уже в следующей версии, ориентировочно в конце декабря. На сайте также в скором времени появится раздел по обновлениям. Сейчас действует версия XLTools 3.5.
2. Сейчас мы поддерживаем только desktop-версию XLTools для Windows с установкой с нашего сайта. Вариант через Магазин Microsoft больше не поддерживается. Для SQL запросов используйте инструмент SQL Запросы в составе XLTools, а начать можно с бесплатной пробной версии.
Выбор за Вами. В любом случае перед покупкой рекомендуем взять бесплатную пробную версию.
Думаю, в одной из следующих версий мы поддержим возможность выполнения запроса к книге, даже если она не открыта, но в таком случае она должна быть расположена в той же папке, что и исходный документ.