За допомогою Microsoft Query можна отримати дані із зовнішніх джерел. Отримуючи дані з корпоративних баз даних і файлів за допомогою Microsoft Query, немає необхідності повторно вводити дані, які потрібно проаналізувати в програмі Excel. Крім того, ви можете автоматично оновлювати звіти та зведення Excel із вихідної вихідної бази даних щоразу, коли база даних оновлюється новими відомостями.
Докладні відомості про Microsoft Query
За допомогою Microsoft Query можна підключитися до зовнішніх джерел даних, вибрати з них дані, імпортувати їх до аркуша та за потреби оновити, щоб дані аркуша синхронізувалися з даними зовнішніх джерел.
Типи баз даних, до яких можна отримати доступ Дані можна отримати з кількох типів баз даних, зокрема Microsoft Office Access, Microsoft SQL ServerSQL Server і Microsoft SQL ServerSQL Server OLAP Services. Дані можна також отримувати з книг Excel і текстових файлів.
Microsoft Office надає драйвери, які можна використовувати, щоб отримувати дані з таких джерел:
- Microsoft SQL Server Analysis Services (постачальник OLAP)
- Microsoft Office Access
- dBASE
- Microsoft FoxPro
- Microsoft Office Excel
- Oracle
- Парадокс
- Бази даних текстових файлів
Крім того, за допомогою драйверів ODBC або драйверів джерел даних від інших виробників можна отримувати інформацію з джерел даних, не зазначених у цьому списку, зокрема інших типів баз даних OLAP. Щоб отримати відомості про інсталяцію драйвера ODBC або драйвера джерела даних, якого немає в цьому списку, перегляньте документацію до бази даних або зверніться до постачальника бази даних.
Selecting data from a database Ви отримуєте дані з бази даних, створюючи запит, який ставиться про дані, які зберігаються в зовнішній базі даних. Наприклад, якщо дані зберігаються в базі даних Access, може знадобитися дізнатися показники збуту певного продукту в розрізі регіонів. Ви можете отримати частину даних, вибравши лише дані для продукту та регіону, які потрібно проаналізувати.
За допомогою Microsoft Query можна вибрати стовпці з потрібними даними та імпортувати лише ці дані до програми Excel.
Оновлення аркуша однією дією Якщо у вас є зовнішні дані в книзі Excel, у разі внесення змін до бази даних ви можете оновлювати ці дані, щоб оновити аналіз, не створюючи повторно підсумкові звіти та діаграми. Наприклад, можна створити щомісячний підсумок продажів і оновлювати його щомісяця, коли надходитимуть нові дані про продажі.
Використання джерел даних у Microsoft Query Настроївши джерело даних для певної бази даних, ви можете використовувати його щоразу, коли знадобиться створити запит, щоб вибрати й отримати дані з цієї бази даних, не вводячи повторно всі відомості про підключення. Засіб Microsoft Query використовує джерело даних, щоб підключатися до зовнішньої бази даних і відображати доступні дані. Коли ви створите запит і повернете дані до програми Excel, функція Microsoft Query надасть до книги Excel інформацію про запит і джерело даних, щоб можна було повторно підключитися до бази даних, коли знадобиться оновити дані.
Використання Microsoft Query для імпорту даних Щоб імпортувати зовнішні дані в програму Excel за допомогою Microsoft Query, виконайте ці основні кроки, кожен із яких докладніше описано в наступних розділах.
Підключення до джерела даних
Що таке джерело даних? Джерело даних – це набір відомостей, що зберігаються, завдяки чому програми Excel і Microsoft Query підключаються до зовнішньої бази даних. Під час настроювання джерела даних за допомогою Microsoft Query слід надати йому ім'я, вказати ім'я та розташування бази даних або сервера, тип бази даних, а також ім'я для входу й пароль. Ці відомості також містять ім'я драйвера OBDC або драйвера джерела даних, тобто програми, яка створює підключення до певного типу бази даних.
Щоб настроїти джерело даних за допомогою Microsoft Query:
На вкладці " Дані " в групі "Отримання зовнішніх даних " виберіть пункт "З інших джерел", а потім – "З Microsoft Query".
Примітка.
В Excel 365 Microsoft Query перемістився до групи меню " Застарілі версії майстрів ". За замовчуванням це меню не відображається. Для цього перейдіть до розділу "Файл", " Параметри", " Дані" та ввімкніть цей параметр у розділі "Показати майстрів імпорту даних застарілих версій".
Виконайте одну з таких дій:
- Щоб указати джерело даних для бази даних, текстового файлу або книги Excel, перейдіть на вкладку " Бази даних ".
- Щоб визначити джерело даних куба OLAP, відкрийте вкладку "Куби OLAP ". Ця вкладка доступна, лише якщо Microsoft Query виконувався з програми Excel.
Двічі клацніть команду <"Нове джерело> даних".
-або-
Виберіть команду< "Нове джерело> даних" і натисніть кнопку "OK".
Відкриється діалогове вікно " Створення джерела даних ".На кроці 1 введіть ім'я, щоб визначити джерело даних.
На кроці 2 виберіть драйвер для типу бази даних, яка використовується як джерело даних.
Примітка.
- Якщо драйвери ODBC, інстальовані з Microsoft Query, не підтримують зовнішню базу даних, до якої потрібно отримати доступ, потрібно отримати й інсталювати сумісний із Microsoft Office драйвер ODBC від стороннього постачальника, наприклад виробника бази даних. Зверніться до постачальника бази даних, щоб отримати вказівки з інсталяції.
- Щоб працювати з базами даних OLAP, драйвери ODBC не потрібні. Під час інсталяції програми Microsoft Query виконується інсталяція драйверів для баз даних, створених за допомогою служб аналізу Microsoft SQL Server Analysis Services Служби аналізу SQL Server Analysis Services. Щоб підключитися до інших баз даних OLAP, потрібно інсталювати драйвер джерела даних і клієнтське програмне забезпечення.
Натисніть кнопку "Підключитися" та введіть відомості, потрібні для підключення до джерела даних. Для баз даних, книг Excel і текстових файлів надані відомості залежать від типу вибраного джерела даних. Вас можуть попросити вказати ім'я для входу, пароль, версію бази даних, яка використовується, розташування бази даних або інші відомості, що стосуються типу бази даних.
Важливо
- Використовуйте надійні паролі, у яких поєднуються букви верхнього й нижнього регістра, числа та символи. Якщо в паролі не поєднуються ці елементи, він ненадійний. Приклад надійного пароля: Y6dh!et5. Приклад ненадійного пароля: Dim27. Пароль має містити не менше 8 символів. Краще використовувати кодову фразу, яка містить не менше 14 символів.
- Пам’ятати свій пароль дуже важливо. Якщо ви забудете пароль, корпорація Майкрософт не зможе його відновити. Зберігайте записані паролі в безпечному місці подалі від інформації, яку вони захищають.
Ввівши необхідні відомості, натисніть кнопку OK або кнопку Готово , щоб повернутися до діалогового вікна "Створення джерела даних ".
Якщо база даних складається з таблиць і потрібно, щоб певна таблиця автоматично відображалася в майстрі запитів, клацніть поле для кроку 4, а потім виберіть потрібну таблицю.
Якщо не потрібно вводити ім'я користувача та пароль під час використання джерела даних, установіть прапорець Зберегти мій ідентифікатор користувача та пароль у визначенні джерела даних . Збережений пароль не зашифровано. Якщо прапорець недоступний, з'ясуйте, чи можна активувати цей параметр, в адміністратора бази даних.
Примітка.
Радимо не зберігати відомості про вхід до системи під час підключення до джерел даних. Ці відомості можуть зберігатися як звичайний текст, а зловмисний користувач може отримати доступ до інформації, що поставить під загрозу безпеку джерела даних.
Після виконання цих дій ім'я джерела даних відобразиться в діалоговому вікні " Вибір джерела даних ".
Визначення запиту за допомогою майстра запитів
Для більшості запитів використовується майстер запитів Майстер запитів дає змогу легко вибирати й об'єднувати дані з різних таблиць і полів у базі даних. За допомогою майстра запитів можна вибрати таблиці та поля, які потрібно додати. Внутрішнє об'єднання (операція запиту, яка визначає, що рядки з двох таблиць об'єднуються на основі однакових значень полів) створюється автоматично, коли майстер розпізнає поле первинного ключа в одній таблиці та поле з таким самим іменем в іншій таблиці.
Майстер також дає змогу сортувати набір результатів і фільтрувати дані. На останньому кроці майстра можна повернутися до даних до Excel або додатково уточнити запит у Microsoft Query. Створений запит можна виконати в програмі Excel або Microsoft Query.
Щоб запустити майстер запитів, зробіть ось що:
- На вкладці " Дані " в групі "Отримання зовнішніх даних " виберіть пункт "З інших джерел", а потім – "З Microsoft Query".
- У діалоговому вікні " Вибір джерела даних " переконайтеся, що встановлено прапорець "Створювати й редагувати запити за допомогою майстра запитів ".
- Двічі клацніть джерело даних, яке потрібно використовувати.
-або-
Виберіть потрібне джерело даних і натисніть кнопку ОК.
Робота із запитами інших типів безпосередньо в Microsoft Query Якщо потрібно створити складніший запит, ніж дозволяє майстер запитів, можна працювати безпосередньо в Microsoft Query. За допомогою Microsoft Query можна переглядати та змінювати запити, розпочаті в майстрі запитів, або створювати нові запити, не використовуючи майстер. Працюйте безпосередньо в Microsoft Query, якщо потрібно створити запити, які виконують такі дії:
- Виділення певних даних із поля Якщо використовується велика база даних, може знадобитися вибрати деякі дані в полі й опустити непотрібні. Наприклад, якщо вам потрібні дані для двох продуктів у полі, яке містить відомості про багато товарів, ви можете використовувати умови, щоб вибрати дані лише для двох потрібних продуктів.
- Отримання даних на основі різних умов кожного разу під час виконання запиту Якщо потрібно створити однаковий звіт Excel або зведення для кількох областей з однаковими зовнішніми даними (наприклад, окремий звіт про збут для кожного регіону), можна створити параметризований запит. Під час виконання параметризованого запиту відображається запит на використання значення як умови під час вибору записів запиту. Наприклад, параметризований запит може запропонувати ввести конкретний регіон, і ви можете повторно використати цей запит для кожного звіту про регіональні продажі.
- Різні способи об'єднання даних Внутрішні об'єднання, створені майстром запитів, є найпоширенішим типом об'єднання при створенні запитів. Однак іноді потрібно використовувати інший тип об'єднання. Наприклад, якщо у вас є таблиця відомостей про продажі товару та таблиця відомостей про клієнтів, внутрішнє об'єднання (тип, створений майстром запитів) перешкоджатиме отриманню записів клієнтів клієнтам, які не здійснили покупку. Використовуючи Microsoft Query, можна об'єднати ці таблиці, щоб отримати всі записи клієнтів, а також дані про продажі тих клієнтів, які здійснили покупки.
Щоб запустити Microsoft Query, виконайте наведені нижче кроки.
- На вкладці " Дані " в групі "Отримання зовнішніх даних " виберіть пункт "З інших джерел", а потім – "З Microsoft Query".
- У діалоговому вікні " Вибір джерела даних " переконайтеся, що знято прапорець "Створювати й редагувати запити за допомогою майстра запитів ".
- Двічі клацніть джерело даних, яке потрібно використовувати.
-або-
Виберіть потрібне джерело даних і натисніть кнопку ОК.
Повторне використання запитів і надання спільного доступу до них Як у майстрі запитів, так і в Microsoft Query запити можна зберігати у вигляді файлу DQY, який можна змінювати, повторно використовувати та надавати до нього спільний доступ. Excel може відкривати файли DQY безпосередньо, що дає змогу створити додаткові діапазони зовнішніх даних на основі одного запиту.
Щоб відкрити збережений запит у програмі Excel:
- На вкладці " Дані " в групі "Отримання зовнішніх даних " виберіть пункт "З інших джерел", а потім – "З Microsoft Query". Відкриється діалогове вікно " Вибір джерела даних ".
- У діалоговому вікні " Вибір джерела даних " перейдіть на вкладку "Запити ".
- Двічі клацніть збережений запит, який потрібно відкрити. Запит відобразиться в Microsoft Query.
Якщо потрібно відкрити збережений запит у презентації Microsoft Query, клацніть меню " Файл Microsoft Query " та виберіть команду "Відкрити".
Якщо двічі клацнути файл DQY, Excel відкриється, виконає запит і вставить результати на новий аркуш.
Якщо потрібно надати спільний доступ до зведення або звіту Excel на основі зовнішніх даних, можна надати іншим користувачам книгу, яка містить діапазон зовнішніх даних, або створити шаблон. Шаблон дає змогу зберегти підсумок або звіт без збереження зовнішніх даних, тому файл менший. Зовнішні дані отримуються, коли користувач відкриває шаблон звіту.
Робота з даними в програмі Excel
Створивши запит у майстрі запитів або Microsoft Query, можна повернути дані до аркуша Excel. Потім дані стають зовнішнім діапазоном даних або звітом зведеної таблиці, який можна форматувати й оновлювати.
Форматування отриманих даних У програмі Excel можна використовувати знаряддя, наприклад діаграми або автоматичні проміжні підсумки, для представлення та зведення даних, отриманих за допомогою Microsoft Query. Ви можете відформатувати дані, але це форматування залишиться в разі оновлення зовнішніх даних. Ви можете використовувати власні підписи стовпців замість імен полів і автоматично додавати номери рядків.
Нові дані, введені в кінці діапазону, можуть автоматично форматуватися відповідно до попередніх рядків. Програма Excel також може автоматично копіювати формули, які повторювалися в попередніх рядках, і поширювати їх на додаткові рядки.
Примітка.
Щоб поширити формат на нові рядки в діапазоні, формати й формули мають міститися принаймні в трьох із п'яти попередніх рядків.
Цей параметр можна ввімкнути (або вимкнути) у будь-який час.
- Виберіть елемент"Додатковіпараметри>файлу>".
- У розділі "Параметри редагування " встановіть прапорець "Розширювати діапазон даних формати та формули ". Щоб знову вимкнути автоматичне форматування діапазону даних, зніміть цей прапорець.
Оновлення зовнішніх даних Коли оновлюються зовнішні дані, запит виконується для отримання нових і змінених даних, які відповідають специфікаціям. Оновити запит можна в Microsoft Query та Excel. В Excel є кілька параметрів оновлення запитів, зокрема оновлення даних щоразу, коли відкривається книга, і автоматичне оновлення через задані проміжки часу. Під час оновлення даних можна продовжувати роботу в Excel. Також можна перевірити стан під час оновлення даних. Докладні відомості див. в статті "Оновлення зв'язків із зовнішніми даними в програмі Excel".