SQL запросы: возможности SQL для создания запросов в Excel и напрямую к таблицам Excel

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

Надстройка XLTools «SQL запросы» расширит Excel возможностями языка структурированных запросов:

  • Создание запросов SQL в интерфейсе Excel и напрямую к Excel таблицам
  • Автогенерация запросов SELECT и JOIN
  • Доступны JOIN, ORDER BY, DISTINCT, GROUP BY, SUM и другие операторы SQLite
  • Создание запросов в интуитивном редакторе с подстветкой синтаксиса
  • Обращение к любым таблицам Excel из дерева данных

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

 

Добавить «SQL запросы» в Excel 2016, 2013, 2010, 2007

 

Подходит для: Microsoft Excel 2016 – 2007, desktop Office 365 (32-бит и 64-бит).

Скачать надстройку XLTools

Кнопка SQL зпросов в Excel

 

Как работать с надстройкой:

 

Как превратить данные Excel в реляционную базу данных и подготовить их к работе с SQL запросами

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

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

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

 

Как создать и выполнить запрос SQL SELECT к таблицам Excel

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

  1. Нажмите кнопку «Выполнить SQL» на вкладке XLTools > Откроется окно редактора.
  2. В левой части окна находится дерево данных со всеми доступными таблицами Excel.
    Нажатием на узлы открываются/сворачиваются поля таблицы (столбцы).
  3. Выберите целые талицы или конкретные поля.
    По мере выбора полей, в правой части редактора автоматически генерируется запрос SELECT.
    Обратите внимание: редактор запросов SQL автоматически подсвечивает систаксис.
  4. Укажите, куда необходимо поместить результат запроса: на новый или существующий лист.
  5. Нажмите кнопку «Выполнить» > Готово!

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

 

Операторы 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 в одну, если у них есть общее ключевое поле. Предположим, вам нужно объединить несколько таблиц по общему столбцу «КодТовара»:

  1. Нажмите «Выполнить SQL» на вкладке XLTools > Выберите поля, которые нужно включить в объединённую таблицу.
    По мере выбора полей, автоматически генерируется запрос SELECT и LEFT JOIN.
  2. Укажите, куда необходимо поместить результат запроса: на новый или существующий лист.
  3. Нажмите «Выполнить» > Готово! Объединённая таблица появится в считанные секунды.

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

 

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

 

Any questions or suggestions? Please leave your comment below.

27 Responses to SQL запросы: возможности SQL для создания запросов в Excel и напрямую к таблицам Excel

  1. Антон says:

    Здравствуйте, хотелось бы узнать работает ли функция Date в Вашей программе?

  2. Daniel says:

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

    • Maria Balobanova says:

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

      • Игорь says:

        70 тыс. строк не обрабатывает, пишет не достаточно памяти. Хотя установлено памяти (ОЗУ) 12 гб.

        • Maria Balobanova says:

          Игорь, чтобы разобраться, нам нужно больше информации об ошибке и типе запроса. Отправила Вам email.

  3. Айнур 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]

  4. Алла says:

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

    • Maria Balobanova says:

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

  5. Евгения says:

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

  6. Игорь says:

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

    • Maria Balobanova says:

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

      • Павел says:

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

        • Maria Balobanova says:

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

  7. Никита says:

    Добрый день.

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

    Спасибо.

  8. Вадим 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!

  9. Виталий says:

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

  10. Виталий says:

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

  11. Алексей says:

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

    • Peter Liapin says:

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

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

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

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