Используйте возможности 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 запросами
По умолчанию 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?
AleksandrНе нужны никакие надстройки для того чтобы писать даже сложные вложенные запросы и подзапросы к Excel как к базе данных. Изучите и используйте объекты ADODB.Connection и ADODB.Recordset и их основные свойства и методы.
Maria BalobanovaAleksandr, спасибо. Вы правы, это тоже вариант. Excel очень гибкий, при умении можно выполнить много всего и разными способами. С надстройкой проще и быстрее ;)
НазарПОдскажите как с помощью этой надстройки и sql написать фильт, который бы фильтровал столбец с "словосочетаниями" по условию "ключевых слов" в другом столбце, то есть, в столбце А1 собраны "ключевые запросы" в столбце А2 собраны "минус слова" Как отфильтровать А1 по словам из А2?
Maria BalobanovaНазар, добрый день! Можно написать запрос с использованием оператора WHERE. Он как раз отфильтрует столбец A1 по какому-то минус-слову в столбце A2. Примерно так: SELECT tbl1.[Ключевые запросы], tbl1.[Минус слова] FROM [Таблица1] tbl1 WHERE tbl1.[Минус слова] ="aaa"
ВикторияСкажите,как написать запрос? таблицы расположены на разных листах. Их нужно объединить по одному из полей.
Maria BalobanovaВиктория, добрый день! Для начала необходимо отформатировать таблицы на этих листах в именованный диапазон с применением стиля таблицы. Далее открыть надстройку SQL Запросы - и в редакторе таблиц вы увидите все доступные таблицы. Выделяя нужные строки, сформируйте запрос с объединением по общему полю (LEFT JOIN). Выше есть описание похожего примера.
Bjorn MelgaardДобрый день Можно ли использовать в вашей надстройке параметрические запросы с Like и как? Например я хочу вывести города которые начинаются на введенную букву Заранее спасибо
Maria BalobanovaСпасибо за вопрос! Да, можно. SQL запрос примерно такой: SELECT tbl1.[Name] FROM [Table] tbl1 where tbl1.[Name] like 'Абв%'
АнтонЗдравствуйте, хотелось бы узнать работает ли функция Date в Вашей программе?
Maria BalobanovaАнтон, да, настройка "SQL Запросы" использует стантарт SQLite и поддерживает его операторы и функции, включая Date. Подробнеео синтаксисе здесь.
DanielКакое максимальное количество строк может быть в исходной таблице?
Maria BalobanovaDaniel, надстройка XLTools SQL Запросы своих ограничений не устанавливает. Есть только ограничение Excel - размер листа лимитирован примерно в 1 млн строк (если точнее, 1 048 576 строк и 16 384 столбца). И, конечно, для обработки такого объема данных нужен достаточный размер оперативной памяти. Напишите, как получится - будет интересно узнать на рабочем примере!
Игорь70 тыс. строк не обрабатывает, пишет не достаточно памяти. Хотя установлено памяти (ОЗУ) 12 гб.
Maria BalobanovaИгорь, чтобы разобраться, нам нужно больше информации об ошибке и типе запроса. Отправила Вам email.
Айнуркак можно связать 3 таблицы, чтобы сделать запрос
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]
АллаДобрый вечер, Мария! Подскажите, пожалуйста, если я никогда не сталкивалась с SQL запросами, смогу ли я освоить работу с ними? Есть ли какие-нибудь видеоуроки для начинающих? Спасибо!
Maria BalobanovaАлла, здравствуйте! Действительно, эта надстройка довольно продвинутая, а материалов у нас пока немного. Кроме общего описания выше, есть небольшоевидео тренингана английском языке. В будущем мы планируем создавать видеоуроки по применению надстройки. Пока могу посоветовать посмотреть в сети видео про основы основы языка SQL. Спасибо за интерес к XLTools!
ЕвгенияЗдраствуйте, Мария. Скажите, как можно скачать надстройку? Она запрашивает пароль.
Maria BalobanovaЕвгения, скачать надстройку XLTools для Вашей версии Excel можнопо этой ссылке. Странно, что запрашивает пароль - возможно, речь об активации пробного периода? Посмотрите, пожалуйста,пошаговую инструкцию со скриншотами.
ИгорьВозможно ли в будущем увидеть другие функции SQL, такие как UPDATE, INSERT, DELETE?
Maria BalobanovaИгорь, пока надстройка SQL Запросы поддерживает операторы SELECT и UNION (синтаксис по стандарту SQLite). В будущем - да, рассматриваем возможность расширения функциональности, во многом по спросу пользователей - поэтому спасибо, что написали! Будем рады еще предложениям.
Павелчто там с оператором Delete?добавили?
Maria BalobanovaПавел, пока не добавили. Сейчас занимались функционалом Контроля Версий и установщиком для терминальных серверов. Дальше планируем взяться за SQL Запросы.
НикитаДобрый день. Подскажите, можно ли выполнять запросы к таблицам в модели данных PowerPivot, которые отсутствуют на листах? Если нет, то планируется ли? Спасибо.
Maria BalobanovaНикита, не совсем ясен вопрос, нужно уточнить. Ответила Вам по email.
ВадимДобрый день! Во-первых, в марте сего года Peter Liapin в комментариях указал, что "в одной из следующих версий мы поддержим возможность выполнения запроса к книге, даже если она не открыта". Хотел бы уточнить, реализована эта возможность или нет в текущей версии надстройки. Дело в том, что у Вас на сайте нет никакой информации о версиях надстроек,новостей о их доработке и узнать об изменениях можно только посредством данного комментария. Во-вторых, в магазине приложений Microsoft цена на данную надстройку на сегодняшний момент установлена в 220 рублей, а у Вас на сайте 698 рублей. В чем разница?
ВадимЗдравствуйте! Хочу возвратиться к моему вопросу от 06 ноября прошлого года. Тогда Вы ответили мне, что, предположительно в декабре в новой версии надстройки XLTools Вы добавите возможность выполнения SQL запроса к закрытым книгам. Хотелось бы узнать, когда всё-таки появится у Вас эта новая версия.
Maria BalobanovaВадим, добрый день! Мы перенесли выпуск новой версии, поскольку планируем расширить функционал в т.ч. и других надстроек, помимо SQL Запросов. По срокам ориентировочно в конце февраля. Спасибо за интерес к XLTools!
Maria BalobanovaВадим, добрый день! Спасибо, что связались с нами. Отвечая на вопросы: 1. Возможность выполнения SQL запроса к закрытым книгам действительно стоит в плане на разработку, вероятно, что будет уже в следующей версии, ориентировочно в конце декабря. На сайте также в скором времени появится раздел по обновлениям. Сейчас действуетверсия XLTools 3.5. 2. Надстройка “SQL Запросы” существует в двух вариантах –надстройка с установкой с нашего сайта(для Excel 2007 и позднее, бессрочная лицензия, плюс пакет бесплатных надстроек, сейчас стоит 698 руб.), илинадстройка по подписке в Магазине Microsoft(для Excel 2013 и позднее или Excel Online, требуется постоянное подключение к Интернет, стоимость 220 руб./мес.). По функционалу разницы не будет, для создания запросов используется стандарт SQLite. Выбор за Вами. В любом случае перед покупкой рекомендуем взять бесплатную пробную версию.
ВиталийПросто не мог не поблагодарить еще раз. Задача: есть более 30000 позиций и 15 складов, нужно выбрать только товары, которые присутствуют хотя бы на одном из складов. За минуту готовится таблица, минуту на запрос и обработка вместе с сортировкой 4 сек!!! и это в Excel ФАНТАСТИКА!!!
ВиталийНадстройка из разряда "must have".Все запросы выполняются в считанные секунды. Любой сложный поиск, фильтрация, отбор данных теперь не проблема. Тех. поддержка на высоте. Удачи и процветания!
Maria BalobanovaВиталий, спасибо! Рады, что оказались полезными.
АлексейЗапросы к таблицам из разных книг возможно выполнять?
Peter LiapinАлексей, да, можно. Чтобы выполнить такой запрос, все нужные книги должны быть открыты, в таком случае редактор SQL запроса покажет Вам все доступные таблицы из всех книг, и Вы сможете построить по ним запрос. Думаю, в одной из следующих версий мы поддержим возможность выполнения запроса к книге, даже если она не открыта, но в таком случае она должна быть расположена в той же папке, что и исходный документ.
