Перейти до основного
Office

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

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

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

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

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

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

  • Служби аналізу Microsoft SQL Server (_ z0z_)

  • Microsoft Office Access

  • dBASE

  • Microsoft FoxPro

  • Microsoft Office Excel

  • Oracle

  • Paradox

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

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

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

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

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

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

Діаграма використання джерел даних у Microsoft Query

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

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

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

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

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

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

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

  3. Двічі клацніть елемент _ Lt_new Data Source>.

    -або-

    Натисніть кнопку _ Lt_new Data Source>і натисніть кнопку OK.

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

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

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

    Примітки.: 

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

    • Бази даних OLAP не потребують драйверів ODBC. Під час інсталяції Microsoft Query драйвери інсталюються для баз даних, створених за допомогою служб аналізу Microsoft SQL Server. Щоб підключитися до інших баз даних 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, коли потрібно створити запити, які виконують наведені нижче дії.

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

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

  • Об'єднання даних по-різному     Внутрішні об'єднання, які створює майстер запитів, – це найпоширеніший тип об'єднання, який використовується для створення запитів. Але іноді потрібно використовувати інший тип об'єднання. Наприклад, якщо у вас є таблиця відомостей про збут продуктів і таблиця відомостей про клієнтів, внутрішнє об'єднання (тип, створений майстром запитів), не дає змоги отримати звіти клієнтів для клієнтів, які не зробили покупку. За допомогою 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 або звіту на основі зовнішніх даних, можна надати іншим користувачам книгу, що містить зовнішній діапазон даних, або створити _ z0z_. Шаблон дає змогу зберегти зведення або звіт без збереження зовнішніх даних, щоб файл було меншим. Зовнішні дані витягаються, коли користувач відкриває шаблон звіту.

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

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

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

Примітка.: Щоб розширення до нових рядків в діапазоні, формати та формули мають відображатися принаймні в трьох із п'яти попередніх рядків.

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

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

    У _ z0z_: натисніть кнопку Microsoft Office,_ Z1z_, натисніть кнопку Параметри Excel, а потім виберіть категорію Додатково .

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

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

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

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

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

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

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

×