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

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

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

Microsoft Office надає драйвери, за допомогою яких можна отримувати дані з таких джерел даних:

• Служби аналізу Microsoft SQL Server Analysis Services (постачальник OLAP )

• Microsoft Office Access

• dBASE

• Microsoft FoxPro

• Microsoft Office Excel

• Oracle

• Парадокс

• Бази даних текстових файлів

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

Вибір даних із бази даних     Ви маєте отримати дані з бази даних, створивши запит ( запитання про дані, які зберігаються в зовнішній базі даних). Наприклад, якщо дані зберігаються в базі даних 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:

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

   Примітка.: Програма Excel 365 перемістила Microsoft Query до групи меню застарілих майстрів.  Це меню не відображається за замовчуванням.  Щоб увімкнути цей параметр, перейдіть до розділу Файл, Параметри, Дані та ввімкніть їх у розділі Показати майстри імпорту застарілих даних .

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

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

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

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

   -або-

   Натисніть кнопку<Створити джерело даних>, а потім натисніть кнопку OK.

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

4. На кроці 1 введіть ім'я для визначення джерела даних.

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

   Примітки.: 

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

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

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

   Увага!: 

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

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

7. Ввівши потрібні відомості, натисніть кнопку OK або Готово , щоб повернутися до діалогового вікна Створення джерела даних .

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

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

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

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

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

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

Щоб запустити майстер запитів, виконайте наведені нижче дії.

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

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

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

   -або-

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

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

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

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

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

Щоб запустити Microsoft Query, виконайте наведені нижче дії.

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

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

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

   -або-

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

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

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

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

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

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

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

Якщо двічі клацнути файл DQY, програма Excel відкриє, виконає запит, а потім вставить результати на новий аркуш.

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

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

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

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

Цей параметр (або знову) можна ввімкнути в будь-який час.

1. Послідовно виберіть елементи Файл > Параметри > Додатково.

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

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

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