Використання Microsoft Query для отримання зовнішніх даних

Увага! : Цю статтю перекладено за допомогою служби машинного перекладу; див. застереження. Версію цієї статті англійською мовою див. тут для отримання довідки.

За допомогою програми Microsoft Query для отримання даних із зовнішніх джерел. За допомогою програми Microsoft Query для отримання даних із корпоративні бази даних або файли, вам не потрібно повторно введіть дані, які потрібно проаналізувати в Excel. Також можна оновлювати звіти та зведення автоматично з до вихідної бази даних щоразу, коли база даних оновлюється з нової інформації.

У цій статті

Додаткові відомості про Microsoft Query

Підключення до джерела даних

Визначення запиту за допомогою майстра

Робота з даними у програмі Excel

Додаткові відомості про Microsoft Query

За допомогою Microsoft Query можна підключитися до зовнішніх джерел даних, вибрати дані з цих джерел, імпортувати їх на аркуш і за потреби оновити їх з метою синхронізації даних на аркуші з даними із зовнішніх джерел.

Типи баз даних, до яких можна отримати доступ     Дані можна отримати з кількох типів баз даних, наприклад, Microsoft Office Access, Microsoft SQL Server і служб Microsoft SQL Server OLAP Services. Дані також можна отримати з книг Excel і текстових файлів.

Microsoft Office містить драйвери, які можна використовувати для отримання даних з такими джерелами даних:

  • Microsoft SQL Server Analysis Services (постачальник OLAP)

  • Microsoft Office Access

  • dBASE

  • Microsoft FoxPro

  • Microsoft Office Excel

  • Oracle

  • Paradox

  • Бази даних у текстовому форматі

Можна також ODBC або дані джерела сторонніх виробників отримати дані з джерела даних, які не відображаються, включно з іншими типами баз даних OLAP. Відомості про інсталяцію драйвера ODBC або драйвер джерела даних, що немає у списку нижче зверніться до документації бази даних або зверніться до постачальника бази даних.

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

За допомогою Microsoft Query можна виділити потрібні стовпці даних та імпортувати тільки ці дані в Excel.

Оновлення аркуша за допомогою однієї операції     Якщо книга Excel містить зовнішні дані, щоразу під час змінення бази даних можна оновити дані для оновлення вашого аналізу (не потрібно повторно створювати звіти та діаграми). Наприклад, можна створити звіт місячних продажів та оновлювати його щомісяця після надходження нової статистики продажів.

Використання джерел даних Microsoft Query     Після настроювання джерела даних для окремої бази даних його можна використати щоразу, коли потрібно створити запит для вибору й отримання даних із цієї бази даних (не потрібно повторно вводити всі відомості про підключення). Джерело даних використовується в Microsoft Query для підключення до зовнішньої бази даних і відображення доступних даних. Після створення запиту й повернення даних в Excel Microsoft Query надає книзі Excel як відомості про запит, так і відомості про джерело даних, щоб користувач мав змогу повторно підключитися до бази даних, якщо виникне потреба оновити дані.

Схема використання джерел даних у Query

Використання Microsoft Query для імпортування даних     Щоб імпортувати зовнішні дані в Excel за допомогою Microsoft Query, виконайте основні дії, кожну з яких детально описано в наступних розділах.

  1. Підключення до джерела даних.

  2. Використання майстра для визначення запиту.

  3. Робота з даними у програмі Excel.

На початок сторінки

Підключення до джерела даних

Що таке джерело даних?     Джерело даних — це збережений набір відомостей, який дозволяє Excel і Microsoft Query підключитися до зовнішньої бази даних. Під час використання Microsoft Query для настроювання джерела даних джерелу даних присвоюється ім’я, яке потім вводиться користувачем разом із розташуванням бази даних або сервера, типом бази даних, даними входу до системи та паролем. Відомості також містять ім’я драйвера OBDC або драйвера джерела даних, який є програмою підключення до певного типу бази даних.

Щоб настроїти базу даних за допомогою Microsoft Query:

  1. На вкладці Дані у групі Отримати зовнішні дані виберіть варіант З інших джерел, а потім виберіть пункт З Microsoft Query.

  2. Виконайте одну з таких дій:

    • Щоб задати джерело даних для бази даних, текстового файлу або книги Excel, перейдіть на вкладку Бази даних.

    • Визначення джерела даних кубів OLAP, перейдіть на вкладку Кубів OLAP . Ця вкладка доступна, лише якщо ви запускали Microsoft Query із програми Excel.

  3. Двічі клацніть посилання <Створити джерело даних>.

    — або—

    Виберіть пункт <Створити джерело даних> і натисніть кнопку ОК.

    З’явиться діалогове вікно Створення джерела даних.

  4. У кроці 1 введіть ім’я джерела даних.

  5. У кроці 2 виберіть драйвер бази даних, використовуваної як джерело даних.

    Якщо потрібна зовнішня база даних не підтримується інстальованими за допомогою Microsoft Query драйверами ODBC, потрібно отримати та інсталювати сумісний із Microsoft Office драйвер ODBC від стороннього виробника, наприклад, виробника бази даних. Зверніться до виробника бази даних для отримання інструкцій з інсталяції.

    Примітка : Для баз даних OLAP драйвери ODBC не потрібні. Під час інсталяції Microsoft Query інсталюються драйвери для баз даних, які було створено за допомогою служб Microsoft SQL Server Analysis Services. Для підключення до інших баз даних OLAP потрібно інсталювати драйвер бази даних і клієнтське програмне забезпечення.

  6. Виберіть команду Підключитися до сервера та надайте необхідні для підключення до бази даних відомості. Для баз даних, книг Excel і текстових файлів такі відомості залежать від типу вибраного джерела даних. Може з’явитися запит про введення імені для входу, пароля, версії використовуваної бази даних, її розташування та інших відомостей, специфічних для цього типу бази даних.

    Використовуйте надійні паролі, у яких поєднуються букви верхнього й нижнього регістра, числа та символи. Якщо в паролі не поєднуються ці елементи, він ненадійний. Приклад надійного пароля: Y6dh!et5. Приклад ненадійного пароля: Dim27. Пароль має містити не менше 8 символів. Краще використовувати кодову фразу, яка містить не менше 14 символів.

    Дуже важливо, щоб ви пам'ятали пароль. Якщо ви забули пароль, корпорація Майкрософт нездатна відновити його. Записуйте паролі та зберігайте їх у надійному місці окремо від відомостей, якими вони захищаються.

  7. Після введення відомостей натисніть кнопку ОК або Готово для повернення до діалогового вікна Створення джерела даних.

  8. Якщо до бази даних є таблиці, і ви хочете певної таблиці автоматично відображати у майстер запитів, клацніть у полі крок 4 і натисніть кнопку таблицю, яку потрібно.

  9. Якщо не потрібно вводити ім’я для входу та пароль під час використання джерела даних, установіть прапорець Зберегти ідентифікатор користувача та пароль у визначенні джерела даних. Збережений пароль не шифрується. Якщо цей параметр недоступний, зверніться до адміністратора бази даних, щоб дізнатися, чи можна зробити цей параметр доступним.

    Примітка про безпеку : Не зберігайте відомості входу до системи, підключаючись до джерела даних. Можливо, ці відомості буде збережено у формі простого тексту, і тоді несумлінний користувач матиме змогу прочитати їх і порушити безпеку джерела даних.

Після виконання цих дій у діалоговому вікні Вибір джерела даних з’явиться ім’я джерела даних.

На початок сторінки

Використання майстра запитів для визначення запиту

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

Майстром можна також скористатися для сортування набору результатів і виконання простого фільтрування. В останньому кроці майстра можна повернути дані в Excel або продовжити визначення запиту в Microsoft Query. Створивши запит, його можна запустити в Excel або в Microsoft Query.

Для запуску майстра запитів виконайте такі дії.

  1. На вкладці Дані у групі Отримати зовнішні дані виберіть варіант З інших джерел, а потім виберіть пункт З Microsoft Query.

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

  3. Двічі клацніть потрібне джерело даних.

    — або—

    Виберіть потрібне джерело даних і натисніть кнопку ОК.

Робота безпосередньо в Microsoft Query з іншими типами запитів     Для створення складнішого запиту, ніж створені у майстрі запитів, можна працювати безпосередньо в Microsoft Query. Microsoft Query можна використати для переглядання та змінення запитів, створення яких розпочато в майстрі запитів. Нові запити можна створити і без використання майстра. Слід працювати безпосередньо в Microsoft Query, якщо потрібно створити запити, які виконують такі дії:

  • Вибір певних даних із поля     У великій базі даних часто потрібно вибрати деякі дані в полі та пропустити непотрібні. Наприклад, якщо потрібно отримати дані про два товари з поля, яке містить відомості про велику кількість товарів, можна використати умови, щоб вибрати дані лише про потрібні два товари.

  • Отримання даних на основі різних критеріїв під час кожного запуску запиту     Якщо потрібно створити той самий звіт або зведення Excel для кількох областей у одних і тих самих зовнішніх даних (наприклад, окремий звіт про продажі для кожного регіону), можна створити параметризований запит. Під час запуску параметризованого запиту потрібно ввести значення, яке використовуватиметься як критерій для відбору записів за запитом. Наприклад, у параметризованому запиті можна вводити певний регіон. Таким чином, цей запит можна повторно використати для створення окремих звітів щодо продажів для кожного регіону.

  • Об’єднання даних різними способами     Створювані майстром запитів внутрішні об’єднання є найбільш розповсюдженим типом використовуваного для створення запитів об’єднання. Однак, іноді потрібно використати об’єднання іншого типу. Наприклад, за наявності таблиці з відомостями про продаж товару й таблиці з даними про споживача внутрішнє об’єднання ( тип, створений майстром запитів) заборонятиме отримання записів про споживачів для споживачів, які нічого не придбали. За допомогою Microsoft Query ці таблиці можна об’єднати, щоб мати змогу отримувати всі записи про споживачів разом із даними продажу для споживачів, які придбали товар.

Для запуску Microsoft Query виконайте такі дії.

  1. На вкладці Дані у групі Отримати зовнішні дані виберіть варіант З інших джерел, а потім виберіть пункт З Microsoft Query.

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

  3. Двічі клацніть потрібне джерело даних.

    — або—

    Виберіть потрібне джерело даних і натисніть кнопку ОК.

Повторне та спільне використання запитів     Як у майстрі запитів, так і в Microsoft Query можна зберігати запити як файли .dqy, які можна змінювати та використовувати повторно або спільно. Файли .dqy можна відкривати безпосередньо в Excel, що дає змогу користувачам створювати додаткові діапазони зовнішніх даних з одного запиту.

Щоб відкрити збережений запит в Excel:

  1. На вкладці Дані у групі Зовнішні дані виберіть варіант З інших джерел, а потім виберіть пункт З Microsoft Query. З’явиться діалогове вікно Вибір джерела даних.

  2. У діалоговому вікні Вибір джерела даних перейдіть на вкладку Запити.

  3. Двічі клацніть збережений запит, який потрібно відкрити. Запит відобразиться в Microsoft Query.

Якщо потрібно відкрити збережений запит, а Microsoft Query вже відкрито, перейдіть до меню Файл у Microsoft Query і виберіть команду Відкрити.

Якщо двічі клацнути файл .dqy file, запит буде відкрито й виконано, а результати буде вставлено в новий аркуш.

Якщо потрібно спільно використати зведення або звіт Excel, який базується на зовнішніх даних, можна надати іншим користувачам книгу, що містить діапазон зовнішніх даних, або створити шаблон. Шаблон дозволяє зберегти зведення або звіт, не зберігаючи зовнішні дані (це дає змогу зменшити розмір файлу). Зовнішні дані отримуються під час відкриття користувачем шаблону звіту.

На початок сторінки

Робота з даними в Excel

Після створення запиту в майстрі запитів або в Microsoft Query дані можна повернути на аркуш Excel. Дані стають діапазон зовнішніх даних або звіт зведеної таблиці, які можна форматувати та оновлювати.

Форматування отриманих даних     В Excel можна скористатися різними засобами, такими як діаграми або автоматично обчислювані підсумки, для унаочнення та узагальнення отриманих за допомогою Microsoft Query даних. Дані також можна відформатувати, і форматування зберігається після оновлення зовнішніх даних. Можна використати власні підписи стовпців замість імен полів, а також автоматично додати номери рядків.

В Excel можна автоматично відформатувати нові дані, які введено в кінці діапазону, щоб їх форматування відповідало форматуванню попередніх рядків. Також можна автоматично копіювати формули, які повторюються в попередніх рядках, і розширити їх на додаткові рядки.

Примітка : Для розширення на нові рядки в діапазоні формати й формули мають застосовуватися принаймні у трьох із п’яти попередніх рядків.

Цей параметр можна увімкнути або вимкнути будь-коли, виконавши такі дії:

  1. Натисніть кнопку Microsoft Office  Зображення кнопки Office , натисніть кнопку Параметри Excel, а потім виберіть категорію Додатково.

  2. У розділі Редагування встановіть прапорець Розширювати формати та формули в діапазонах даних, щоб вимкнути цей параметр. Щоб знову вимкнути автоматичне форматування діапазону даних, зніміть цей прапорець.

Оновлення зовнішніх даних     Під час оновлення зовнішніх даних, час виконання запиту щоб отримати нові або змінені дані, яке відповідає вашому специфікації. Можна оновлювати запиту в Excel і Microsoft Query. Excel пропонує кілька варіантів для оновлення запити, у тому числі оновлення даних під час відкриття книги та автоматичне оновлення його через задані часові проміжки. Можна продовжувати працювати у програмі Excel під час оновлення даних, а також можна перевірити стан під час оновлення даних. Додаткові відомості про оновлення підключених даних (імпортовані).

На початок сторінки

Примітка : Застереження про машинний переклад: Цю статтю перекладено комп’ютерною системою без втручання людини. Корпорація Майкрософт пропонує таку послугу, щоб іншомовні користувачі могли дізнаватися про продукти, служби й технології Microsoft. Оскільки статтю перекладено за допомогою служби машинного перекладу, вона може містити смислові, синтаксичні або граматичні помилки.

Отримуйте нові функції раніше за інших
Приєднайтеся до оцінювачів Office

Ця інформація корисна?

Дякуємо за ваш відгук!

Дякуємо за відгук! Схоже, вам може стати в нагоді допомога одного з наших спеціалістів служби підтримки Office, з яким ми вас можемо з’єднати.

×