SQL запити в Microsoft Excel

SQL - популярна мова програмування, який застосовується при роботі з базами даних (БД). Хоча для операцій з базами даних в пакеті Microsoft Office є окремий додаток - Access, але програма Excel теж може працювати з БД, роблячи SQL запити. Давайте дізнаємося, як різними способами можна сформувати подібний запит.

Читайте також: Як створити базу даних в Ексель

Створення SQL запиту в Excel

Мова запитів SQL відрізняється від аналогів тим, що з ним працюють практично всі сучасні системи управління БД. Тому зовсім не дивно, що такий просунутий табличний процесор, як Ексель, що володіє багатьма додатковими функціями, теж вміє працювати з цією мовою. Користувачі, які володіють мовою SQL, використовуючи Excel, можуть впорядкувати безліч різних розрізнених табличних даних.

Спосіб 1: використання надбудови

Але для початку давайте розглянемо варіант, коли з Ексель можна створити SQL запит не за допомогою стандартного інструментарію, а скориставшись сторонньої надбудовою. Однією з кращих надбудов, що виконують цю задачу, є комплекс інструментів XLTools, який крім зазначеної можливості, надає масу інших функцій. Правда, слід зазначити, що безкоштовний період користування інструментом становить всього 14 днів, а потім доведеться купувати ліцензію.

Завантажити надбудову XLTools

1. Після того, як ви завантажили файл надбудови xltools.exe, слід приступити до його встановлення. Для запуску інсталятора потрібно зробити подвійне клацання лівої кнопки миші по файлу встановлення. Після цього запуститься вікно, в якому потрібно буде підтвердити згоду з ліцензійною угодою на використання продукції компанії Microsoft - NET Framework 4. Для цього всього лише потрібно клікнути на кнопку «Приймаю» внизу віконця.
2. Після цього установник робить завантаження обов'язкових файлів і починає процес їх установки.
3. Далі відкриється вікно, в якому ви повинні підтвердити свою згоду на установку цієї надбудови. Для цього потрібно клацнути по кнопці «Встановити».
4. Потім починається процедура установки безпосередньо самої надбудови.
5. Після її завершення відкриється вікно, в якому буде повідомлятися, що інсталяція успішно виконана. У зазначеному вікні досить натиснути на кнопку «Закрити».
6. Надбудова встановлена ​​і тепер можна запускати файл Excel, в якому потрібно організувати SQL запит. Разом з листом Ексель відкривається вікно для введення коду ліцензії XLTools. Якщо у вас є код, то потрібно ввести його в відповідне поле і натиснути на кнопку «OK». Якщо ви бажаєте використовувати безкоштовну версію на 14 днів, то слід просто натиснути на кнопку «Пробна ліцензія».
7. При виборі пробної ліцензії відкривається ще одне невелике віконце, де потрібно вказати своє ім'я та прізвище (можна псевдонім) і електронну пошту. Після цього тисніть на кнопку «Почати пробний період».
8. Далі ми повертаємося до вікна ліцензії. Як бачимо, введені вами значення вже відображаються. Тепер потрібно просто натиснути на кнопку «OK».
9. Після того, як ви зробите вищевказані маніпуляції, в вашому екземплярі Ексель з'явиться нова вкладка - «XLTools». Але не поспішаємо переходити в неї. Перш, ніж створювати запит, потрібно перетворити табличний масив, з яким ми будемо працювати, в так звану, «розумну» таблицю і присвоїти їй ім'я.
   Для цього виділяємо вказаний масив або будь-який його елемент. Перебуваючи у вкладці «Головна» клацаємо по значку «Форматувати як таблицю». Він розміщений на стрічці в блоці інструментів «Стилі». Після цього відкривається список вибору різних стилів. Вибираємо той стиль, який ви вважаєте за потрібне. На функціональність таблиці вказаний вибір ніяк не вплине, так що засновуйте свій вибір виключно на основі переваг візуального відображення.
10. Слідом за цим запускається невелике віконце. У ньому вказуються координати таблиці. Як правило, програма сама «підхоплює» повну адресу масиву, навіть якщо ви виділили тільки одну клітинку в ньому. Але про всяк випадок не заважає перевірити ту інформацію, яка знаходиться в полі «Вкажіть розташування даних таблиці». Також потрібно звернути увагу, щоб біля пункту «Таблиця із заголовками», стояла галочка, якщо заголовки в вашому масиві дійсно присутні. Потім тисніть на кнопку «OK».
11. Після цього весь зазначений діапазон буде відформатований, як таблиця, що вплине як на його властивості (наприклад, розтягування), так і на візуальне відображення. Зазначеній таблиці буде присвоєно ім'я. Щоб його дізнатися і за бажанням змінити, клацають по будь-якому елементу масиву. На стрічці з'являється додаткова група вкладок - «Робота з таблицями». Переміщаємося у вкладку «Конструктор», розміщену в ній. На стрічці в блоці інструментів «Властивості» в поле «Ім'я таблиці» буде вказано найменування масиву, яке йому присвоїла програма автоматично.
12. При бажанні це найменування користувач може змінити на більш інформативне, просто вписавши в поле з клавіатури бажаний варіант і натиснувши на клавішу Enter.
13. Після цього таблиця готова і можна переходити безпосередньо до організації запиту. Переміщаємося у вкладку «XLTools».
14. Після переходу на стрічці в блоці інструментів «SQL запити» клацаємо по значку «Виконати SQL».
15. Запускається вікно виконання SQL запиту. У лівій його області слід вказати лист документа і таблицю на дереві даних, до якої буде формуватися запит.

    У правій області вікна, яка займає його більшу частину, розташовується сам редактор SQL запитів. У ньому потрібно писати програмний код. Найменування стовпців обраної таблиці там вже будуть відображатися автоматично. Вибір стовпців для обробки проводиться за допомогою команди SELECT. Потрібно залишити в переліку тільки ті колонки, які ви бажаєте, щоб зазначена команда обробляла.

    Далі пишеться текст команди, яку ви хочете застосувати до вибраних об'єктів. Команди складаються за допомогою спеціальних операторів. Ось основні оператори SQL:

    • ORDER BY - сортування значень;
    • JOIN - об'єднання таблиць;
    • GROUP BY - угруповання значень;
    • SUM - підсумовування значень;
    • DISTINCT - видалення дублікатів.

    Крім того, в побудові запиту можна використовувати оператори MAX, MIN, AVG, COUNT, LEFT і ін.

    У нижній частині вікна слід вказати, куди саме буде виводитися результат обробки. Це може бути новий лист книги (за замовчуванням) або певний діапазон на поточному аркуші. В останньому випадку потрібно переставити перемикач в відповідну позицію і вказати координати цього діапазону.

    Після того, як запит складено і відповідні налаштування зроблені, тиснемо на кнопку «Виконати» в нижній частині вікна. Після цього введена операція буде проведена.

урок: «Розумні» таблиці в Ексель

Спосіб 2: використання вбудованих інструментів Excel

Існує також спосіб створити SQL запит до обраного джерела даних за допомогою вбудованих інструментів Ексель.

1. Запускаємо програму Excel. Після цього переміщаємося у вкладку «Дані».
2. У блоці інструментів «Отримання зовнішніх даних», який розташований на стрічці, тиснемо на значок «З інших джерел». Відкривається список подальших варіантів дій. Вибираємо в ньому пункт «З майстра підключення даних».
3. Запускається Майстер підключення даних. У переліку типів джерел даних вибираємо «ODBC DSN». Після цього клацаємо по кнопці «Далі».
4. Екрані з'явиться діалогове вікно підключення даних, в якому потрібно вибрати тип джерела. Вибираємо найменування «MS Access Database». Потім клацаємо по кнопці «Далі».
5. Відкривається невелике віконце навігації, в якому слід перейти в директорію розташування бази даних в форматі mdb або accdb і вибрати потрібний файл БД. Навігація між логічними дисками при цьому виробляється в спеціальному полі «Диски». Між каталогами проводиться перехід в центральній області вікна під назвою «Каталоги». На лівій панелі відображаються файли, розташовані в поточному каталозі, якщо вони мають розширення mdb або accdb. Саме в цій області потрібно вибрати найменування файлу, після чого клікнути на кнопку «OK».
6. Слідом за цим запускається вікно вибору таблиці у зазначеній базі даних. У центральній області слід вибрати найменування потрібної таблиці (якщо їх декілька), а потім натиснути на кнопку «Далі».
7. Після цього відкривається вікно збереження файлу підключення даних. Тут вказані основні відомості про підключення, яке ми налаштували. В даному вікні досить натиснути на кнопку «Готово».
8. На аркуші Excel запускається віконце імпорту даних. У ньому можна вказати, в якому саме вигляді ви хочете, щоб дані були представлені:
   • Таблиця;
   • Звіт зведеної таблиці;
   • Зведена діаграма.

   Вибираємо потрібний варіант. Трохи нижче потрібно вказати, куди саме слід помістити дані: на новий лист або на поточному аркуші. В останньому випадку надається також можливість вибору координат розміщення. За замовчуванням дані розміщуються на поточному аркуші. Лівий верхній кут імпортованого об'єкта розміщується в осередку A1.

   Після того, як всі налаштування імпорту вказані, тиснемо на кнопку «OK».

9. Як бачимо, таблиця з бази даних переміщена на лист. Потім переміщаємося у вкладку «Дані» і клацаємо по кнопці «Підключення», яка розміщена на стрічці в блоці інструментів з однойменною назвою.
10. Після цього запускається вікно підключення до книги. У ньому ми бачимо найменування вже підключалися нами бази даних. Якщо підключених БД кілька, то вибираємо потрібну і виділяємо її. Після цього клацаємо по кнопці «Властивості ...» у правій частині вікна.
11. Запускається вікно властивостей підключення. Переміщаємося в ньому у вкладку «Визначення». В поле «Текст команди», що знаходиться внизу поточного вікна, записуємо SQL команду відповідно до синтаксисом даного мови, про який ми коротко говорили при розгляді Способу 1. Потім тиснемо на кнопку «OK».
12. Після цього проводиться автоматичне повернення до вікна підключення до книги. Нам залишається тільки клікнути на кнопку «Оновити» в ньому. Відбувається звернення до бази даних із запитом, після чого БД повертає результати його обробки назад на лист Excel, в раніше перенесену нами таблицю.

Спосіб 3: підключення до сервера SQL Server

Крім того, за допомогою інструментів Excel існує можливість з'єднання з сервером SQL Server і посилу до нього запитів. Побудова запиту не відрізняється від попереднього варіанту, але перш за все, потрібно встановити саме підключення. Подивимося, як це зробити.

1. Запускаємо програму Excel і переходимо у вкладку «Дані». Після цього клацаємо по кнопці «З інших джерел», яка розміщується на стрічці в блоці інструментів «Отримання зовнішніх даних». На цей раз з розкрився списку вибираємо варіант «З сервера SQL Server».
2. Відбувається відкриття вікна підключення до сервера баз даних. В поле «Ім'я сервера» вказуємо найменування того сервера, до якого виконуємо підключення. У групі параметрів «Облікові відомості» потрібно визначитися, як саме буде відбуватися підключення: з використанням перевірки автентичності Windows або шляхом введення імені користувача і пароля. Виставляємо перемикач згідно з прийнятим рішенням. Якщо ви вибрали другий варіант, то крім того в відповідні поля доведеться ввести ім'я користувача і пароль. Після того, як всі налаштування проведені, тиснемо на кнопку «Далі». Після виконання цієї дії відбувається підключення до вказаного серверу. Подальші дії по організації запиту до бази даних аналогічні тим, які ми описували в попередньому способі.

Як бачимо, в Ексель SQL запит можна організувати, як вбудованими інструментами програми, так і за допомогою сторонніх надбудов. Кожен користувач може вибрати той варіант, який зручніше для нього і є більш відповідним для вирішення конкретно поставленої задачі. Хоча, можливості надбудови XLTools, в цілому, все-таки кілька більш просунуті, ніж у вбудованих інструментів Excel. Головний же недолік XLTools полягає в тому, що термін безкоштовного користування надбудовою обмежений всього двома календарними тижнями.

• ← Таблиця даних в Microsoft Excel
• Методи порівняння таблиць в Microsoft Excel →