SQL запросы к таблицам Excel

Зачастую таблицы Excel разрастаются настолько, что с ними становится неудобно работать. При большом документообороте немало времени уходит на что, чтобы вручную выбрать и сгруппировать нужные данные, переключаясь между множеством книг и листов. И всё равно удаление дубликатов, группировка, сложносоставная фильтрация, комплексная сортировка, объединение нескольких таблиц в одну, т.д. превращаются в трудоёмкие задачи. Теоретически эти задачи можно легко решить с помощью языка запросов SQL.

Теперь пользователи, знакомые с языком запросов SQL, могут выполнять запросы напрямую к таблицам Excel. Благодаря удобному редактору SQL запросов с подсветкой синтаксиса и возможностью автогенерации запросов SELECT и JOIN, надстройка проста в использовании и существенно упрощает обработку больших таблиц.

Как работать с надстройкой «SQL запросы»:

1. Прежде чем создать запрос, преобразуйте нужные данные в таблицу (именованный диапазон). Это необходимо, поскольку SQL применим к реляционным базам данных, а Excel по умолчанию воспринимает все данные как простой диапазон ячеек.

  • Выделите диапазон ячеек и откройте вкладку «Главная». Нажмите «Форматировать как таблицу» и выберете любой стиль таблицы.
  • Выделите таблицу, откройте вкладку «Конструктор» и присвойте таблице имя, например, «КодТовара».
  • Повторите эти действия для всех диапазонов данных, которые вы планируете обрабатывать, например, «КодТовара», «ЦенаРозн», «ОбъемПродаж», т.д. Теперь данные подготовлены для выполнения SQL запросов.

XLTools SQL Запросы: подготовка данных и формат таблиц

2. Напишите и выполните запрос к таблицам Excel:

  • XLTools использует стандарт SQLite – узнать больше (внешняя ссылка). Нажмите кнопку «Выполнить SQL» на панели инструментов XLTools. В появившемся окне слева находится дерево данных Excel, а справа – редактор SQL запросов. Дерево данных наглядно отображает все листы и таблицы рабочей книги Excel.
  • Создайте команду SELECT и выберете данные для обработки. Выборка может включать целые таблицы (кликнуть по узлу таблицы на дереве данных) или выборочные столбцы (кликнуть по узлу поля таблицы). Убедитесь, как редактор SQL прост в использовании – он подсвечивает синтаксис и автоматически генерирует SELECT предложения.

Обратите внимание: SQL запросы можно задавать к разным книгам и листам Excel. Для этого убедитесь, что эти книги открыты, а нужные диапазоны ячеек отформатированы как именованные таблицы.

  • Введите необходимые команды: JOIN, ORDER BY, DISTINCT или другие. Имя таблицы или столбцы можно добавить в написанный запрос простым перетаскиванием её узла из дерева данных в область редактора SQL.
  • Укажите, выгрузить ли результат запроса на существующий или на новый лист Excel.
  • Нажмите «Выполнить», и результат запроса появится всего через секунду!

XLTools SQL Запросы: SELECT к таблице Excel

3. Пользователи, владеющие языком SQL, могут выполнять практически любые SQL запросы с помощью этой надстройки XLTools:

  • JOIN – объединить несколько таблиц в одну
  • ORDER BY – сортировать данные
  • DISTINCT – удалить дубликаты из результата запроса
  • GROUP BY – группировать данные
  • SUM, COUNT, MIN, MAX, AVG и другие команды

Например, вы можете объединить несколько таблиц в одну по ключевому полю. Создайте запрос SELECT к таблицам «КодТовара», «ЦенаРозн», «ОбъемПродаж». Далее – запрос JOIN ON для слияния таблиц по столбцу «Код товара». Готово! «SQL запросы» от XLTools сгенерирует результат в считанные секунды.

XLTools SQL Запросы: JOIN к таблицам Excel

 

Появились вопросы или комментарии? Свяжитесь с нами или оставьте свой отзыв ниже.

 

23 Responses to SQL запросы к таблицам Excel

  1. Daniel says:

    Какое максимальное количество строк может быть в исходной таблице?

    • Maria Balobanova says:

      Daniel, надстройка XLTools SQL Запросы своих ограничений не устанавливает. Есть только ограничение Excel — размер листа лимитирован примерно в 1 млн строк (если точнее, 1 048 576 строк и 16 384 столбца). И, конечно, для обработки такого объема данных нужен достаточный размер оперативной памяти. Напишите, как получится — будет интересно узнать на рабочем примере!

  2. Айнур says:

    как можно связать 3 таблицы, чтобы сделать запрос

    • Maria Balobanova says:

      Айнур, спасибо за вопрос!
      Связать три таблицы можно через LEFT JOIN, если у них есть общее поле/столбец. Например:

      SELECT

      FROM [Table1] tbl1
      LEFT JOIN [Table2] tbl2 ON tbl1.[общее_поле_1]=tbl2.[общее_поле_1]
      LEFT JOIN [Table3] tbl3 ON tbl2.[общее_поле_2]=tbl3.[общее_поле_2]

  3. Алла says:

    Добрый вечер, Мария! Подскажите, пожалуйста, если я никогда не сталкивалась с SQL запросами, смогу ли я освоить работу с ними? Есть ли какие-нибудь видеоуроки для начинающих? Спасибо!

    • Maria Balobanova says:

      Алла, здравствуйте! Действительно, эта надстройка довольно продвинутая, а материалов у нас пока немного. Кроме общего описания выше, есть небольшое видео тренинга на английском языке. В будущем мы планируем создавать видеоуроки по применению надстройки. Пока могу посоветовать посмотреть в сети видео про основы основы языка SQL. Спасибо за интерес к XLTools!

  4. Евгения says:

    Здраствуйте, Мария. Скажите, как можно скачать надстройку? Она запрашивает пароль.

  5. Игорь says:

    Возможно ли в будущем увидеть другие функции SQL, такие как UPDATE, INSERT, DELETE?

    • Maria Balobanova says:

      Игорь, пока надстройка SQL Запросы поддерживает операторы SELECT и UNION (синтаксис по стандарту SQLite). В будущем — да, рассматриваем возможность расширения функциональности, во многом по спросу пользователей — поэтому спасибо, что написали! Будем рады еще предложениям.

      • Павел says:

        что там с оператором Delete?добавили?

        • Maria Balobanova says:

          Павел, пока не добавили. Сейчас занимались функционалом Контроля Версий и установщиком MSI для терминальных серверов. Дальше планируем взяться за SQL Запросы.

  6. Никита says:

    Добрый день.

    Подскажите, можно ли выполнять запросы к таблицам в модели данных PowerPivot, которые отсутствуют на листах? Если нет, то планируется ли?

    Спасибо.

  7. Вадим says:

    Добрый день!
    Во-первых, в марте сего года Peter Liapin в комментариях указал, что «в одной из следующих версий мы поддержим возможность выполнения запроса к книге, даже если она не открыта». Хотел бы уточнить, реализована эта возможность или нет в текущей версии надстройки. Дело в том, что у Вас на сайте нет никакой информации о версиях надстроек,новостей о их доработке и узнать об изменениях можно только посредством данного комментария.
    Во-вторых, в магазине приложений Microsoft цена на данную надстройку на сегодняшний момент установлена в 220 рублей, а у Вас на сайте 698 рублей. В чем разница?

    • Maria Balobanova says:

      Вадим, добрый день! Спасибо, что связались с нами. Отвечая на вопросы:
      1. Возможность выполнения SQL запроса к закрытым книгам действительно стоит в плане на разработку, вероятно, что будет уже в следующей версии, ориентировочно в конце декабря. На сайте также в скором времени появится раздел по обновлениям. Сейчас действует версия XLTools 3.5.
      2. Надстройка “SQL Запросы” существует в двух вариантах – надстройка с установкой с нашего сайта (для Excel 2007 и позднее, бессрочная лицензия, плюс пакет бесплатных надстроек, сейчас стоит 698 руб.), или надстройка по подписке в Магазине Microsoft (для Excel 2013 и позднее или Excel Online, требуется постоянное подключение к Интернет, стоимость 220 руб./мес.). По функционалу разницы не будет, для создания запросов используется стандарт SQLite.
      Выбор за Вами. В любом случае перед покупкой рекомендуем взять бесплатную пробную версию.

    • Вадим says:

      Здравствуйте!
      Хочу возвратиться к моему вопросу от 06 ноября прошлого года. Тогда Вы ответили мне, что, предположительно в декабре в новой версии надстройки XLTools Вы добавите возможность выполнения SQL запроса к закрытым книгам. Хотелось бы узнать, когда всё-таки появится у Вас эта новая версия.

      • Maria Balobanova says:

        Вадим, добрый день! Мы перенесли выпуск новой версии, поскольку планируем расширить функционал в т.ч. и других надстроек, помимо SQL Запросов. По срокам ориентировочно в конце февраля. Спасибо за интерес к XLTools!

  8. Виталий says:

    Просто не мог не поблагодарить еще раз. Задача: есть более 30000 позиций и 15 складов, нужно выбрать только товары, которые присутствуют хотя бы на одном из складов. За минуту готовится таблица, минуту на запрос и обработка вместе с сортировкой 4 сек!!! и это в Excel ФАНТАСТИКА!!!

  9. Виталий says:

    Надстройка из разряда «must have».Все запросы выполняются в считанные секунды. Любой сложный поиск, фильтрация, отбор данных теперь не проблема. Тех. поддержка на высоте. Удачи и процветания!

  10. Алексей says:

    Запросы к таблицам из разных книг возможно выполнять?

    • Peter Liapin says:

      Алексей, да, можно. Чтобы выполнить такой запрос, все нужные книги должны быть открыты, в таком случае редактор SQL запроса покажет Вам все доступные таблицы из всех книг, и Вы сможете построить по ним запрос.

      Думаю, в одной из следующих версий мы поддержим возможность выполнения запроса к книге, даже если она не открыта, но в таком случае она должна быть расположена в той же папке, что и исходный документ.

Добавить комментарий

Ваш e-mail не будет опубликован. Обязательные поля помечены *