Зачастую таблицы Excel разрастаются настолько, что с ними становится неудобно работать. При большом документообороте немало времени уходит на что, чтобы вручную выбрать и сгруппировать нужные данные, переключаясь между множеством книг и листов. И всё равно удаление дубликатов, группировка, сложносоставная фильтрация, комплексная сортировка, объединение нескольких таблиц в одну, т.д. превращаются в трудоёмкие задачи. Теоретически эти задачи можно легко решить с помощью языка запросов SQL.
Теперь пользователи, знакомые с языком запросов SQL, могут выполнять запросы напрямую к таблицам Excel. Благодаря удобному редактору SQL запросов с подсветкой синтаксиса и возможностью автогенерации запросов SELECT и JOIN, надстройка проста в использовании и существенно упрощает обработку больших таблиц.
Как работать с надстройкой «SQL запросы»:
1. Прежде чем создать запрос, преобразуйте нужные данные в таблицу (именованный диапазон). Это необходимо, поскольку SQL применим к реляционным базам данных, а Excel по умолчанию воспринимает все данные как простой диапазон ячеек.
- Выделите диапазон ячеек и откройте вкладку «Главная». Нажмите «Форматировать как таблицу» и выберете любой стиль таблицы.
- Выделите таблицу, откройте вкладку «Конструктор» и присвойте таблице имя, например, «КодТовара».
- Повторите эти действия для всех диапазонов данных, которые вы планируете обрабатывать, например, «КодТовара», «ЦенаРозн», «ОбъемПродаж», т.д. Теперь данные подготовлены для выполнения SQL запросов.
2. Напишите и выполните запрос к таблицам Excel:
- XLTools использует стандарт SQLite – узнать больше (внешняя ссылка). Нажмите кнопку «Выполнить SQL» на панели инструментов XLTools. В появившемся окне слева находится дерево данных Excel, а справа – редактор SQL запросов. Дерево данных наглядно отображает все листы и таблицы рабочей книги Excel.
- Создайте команду SELECT и выберете данные для обработки. Выборка может включать целые таблицы (кликнуть по узлу таблицы на дереве данных) или выборочные столбцы (кликнуть по узлу поля таблицы). Убедитесь, как редактор SQL прост в использовании – он подсвечивает синтаксис и автоматически генерирует SELECT предложения.
Обратите внимание: SQL запросы можно задавать к разным книгам и листам Excel. Для этого убедитесь, что эти книги открыты, а нужные диапазоны ячеек отформатированы как именованные таблицы.
- Введите необходимые команды: JOIN, ORDER BY, DISTINCT или другие. Имя таблицы или столбцы можно добавить в написанный запрос простым перетаскиванием её узла из дерева данных в область редактора SQL.
- Укажите, выгрузить ли результат запроса на существующий или на новый лист Excel.
- Нажмите «Выполнить», и результат запроса появится всего через секунду!
3. Пользователи, владеющие языком SQL, могут выполнять практически любые SQL запросы с помощью этой надстройки XLTools:
- JOIN – объединить несколько таблиц в одну
- ORDER BY – сортировать данные
- DISTINCT – удалить дубликаты из результата запроса
- GROUP BY – группировать данные
- SUM, COUNT, MIN, MAX, AVG и другие команды
Например, вы можете объединить несколько таблиц в одну по ключевому полю. Создайте запрос SELECT к таблицам «КодТовара», «ЦенаРозн», «ОбъемПродаж». Далее – запрос JOIN ON для слияния таблиц по столбцу «Код товара». Готово! «SQL запросы» от XLTools сгенерирует результат в считанные секунды.
Появились вопросы или предложения? Оставьте комментарий ниже.
Здравствуйте, хотелось бы узнать работает ли функция 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?добавили?
Павел, пока не добавили. Сейчас занимались функционалом Контроля Версий и установщиком MSI для терминальных серверов. Дальше планируем взяться за 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 запроса покажет Вам все доступные таблицы из всех книг, и Вы сможете построить по ним запрос.
Думаю, в одной из следующих версий мы поддержим возможность выполнения запроса к книге, даже если она не открыта, но в таком случае она должна быть расположена в той же папке, что и исходный документ.