Як запитувати зовнішні дані в Excel 2016

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

Excel 2016 також дозволяє легко отримувати дані з різноманітних джерел даних, включаючи файли бази даних Microsoft Access, веб-сторінки в Інтернеті, текстові файли та інші джерела даних, наприклад таблиці бази даних на серверах SQL і Analysis Services, файли даних XML. , а також таблиці даних з онлайн-з'єднань до Microsoft Windows Azure DataMarket і каналів даних OData.

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

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

Під час роботи з пов’язаними таблицями даних слід пам’ятати лише про інше, а саме про тип зв’язку, який існує між двома таблицями. У моделі даних Excel підтримуються два типи зв’язків:

• Зв’язок «один до одного», де записи в полях первинного та зовнішнього ключа є абсолютно унікальними, наприклад зв’язок між списком даних клієнтів і списком даних про знижки, де поле Ідентифікатор клієнта зустрічається лише один раз у кожній таблиці (оскільки кожен клієнт має лише призначено один відсоток знижки)

• Відношення "один до багатьох", де повторювані записи в полі зовнішнього ключа дозволені і навіть очікуються, як у зв'язку між списком даних клієнтів і списком даних замовлень, де поле "Ідентифікатор клієнта" може зустрічатися кілька разів (оскільки клієнт здійснює кілька покупок)

У більшості випадків Excel 2016 може з’ясувати зв’язок між таблицями даних, які ви імпортуєте. Однак, якщо Excel коли-небудь помилиться або ваші таблиці містять більше одного загального поля, яке може служити ключем, ви можете вручну визначити правильний зв’язок. Просто натисніть кнопку «Зв’язки» в групі «Інструменти даних» на вкладці «Дані» стрічки (Alt+AA), щоб відкрити діалогове вікно «Керування зв’язками». Там ви клацніть «Створити», щоб відкрити діалогове вікно «Створення зв’язку», де ви визначаєте загальне поле в кожній із двох пов’язаних таблиць даних. Після створення цього зв’язку ви можете використовувати будь-які поля в одній із двох пов’язаних таблиць у звітах, які ви готуєте, або зведених таблицях, які ви створюєте.

Щоб імпортувати зовнішні дані, виберіть командну кнопку «Отримати зовнішні дані» на вкладці «Дані» стрічки (Alt+AZX). Коли ви це робите, Excel відображає меню з такими параметрами:

• З Access для імпорту таблиць бази даних, збережених у Microsoft Access

• З Інтернету для виконання запиту веб-сторінки для імпорту списків даних із веб-сторінок в Інтернеті

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

• З інших джерел, щоб відкрити спадне меню, яке пропонує різноманітні варіанти: із SQL Server, із служб Analysis Services, із Windows Azure Marketplace, із каналу даних OData, із імпорту даних XML, із майстра підключення даних та з Microsoft Query

• Існуючі з’єднання для повторного використання підключення до служби даних або каналу даних (за допомогою одного з параметрів імпорту, особливо в розділі «З інших джерел»), які ви вже встановили, щоб отримати більше даних або оновити раніше імпортовані дані