Маршрутизування даних у програмі Excel

Нижче наведено основні завдання, які можна робити, коли дані розташовано на станції потягів Excel.

• Імпорт    Ви можете імпортувати дані з багатьох різних зовнішніх джерел. Ці джерела даних можуть бути на вашому комп'ютері, у хмарі або по всьому світу. Докладні відомості див. в відео Імпорт даних із зовнішніх джерел.

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

• Безпека    Конфіденційність даних, облікові дані та автентифікація – це завжди занепокоєння. Докладні відомості див. в розділі Керування настройками та дозволами джерела даних і Установлення рівнів конфіденційності.

• Оновити    Щоб імпортувати дані, зазвичай потрібно виконати операцію оновлення, щоб внести зміни, як-от додавання, оновлення та видалення, до програми Excel. Докладні відомості див. в ційи: Оновлення зв'язків із зовнішніми даними в Excel.

• Connections/Properties    Кожне зовнішнє джерело даних має пов'язані з ним відомості про зв'язок і властивості, які іноді потребують змін залежно від обставин. Докладні відомості див. в статтяхКерування діапазонами зовнішніх даних і їхніми властивостями , Створення,редагування та керування підключеннями до зовнішніх даних і Властивості підключення.

• Застаріла попередня    Традиційні методи, як-от майстри імпорту застарілих варіантів і MSQuery, усе ще доступні для використання. Докладні відомості див. в статтях Параметри імпорту й аналізу даних і Отримання зовнішніх даних за допомогою Microsoft Query.

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

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

Нижче наведено відомості про найважливіші діалогові вікна, області, шляхи команд і відповідні розділи довідки.

Діалогове вікно або область Шляхи команд	Вкладки та тунелі	Основний розділ довідки
Останні джерела Data > Recent Sources	(Немає вкладок) Tunnels to Connect > Navigator dialog box	Керування настройками та дозволами джерела даних
Властивості підключення АБО Data Connection Wizard Data > Queries & Connections > Connections tab > (click a connection) > Properties	Вкладка "Визначення використання" на вкладці "Використовуються в"	Властивості підключення
Властивості запиту Дані> наявних підключень > (клацніть підключення правою кнопкою миші) > редагування властивостей підключення АБО Data> query & Connections | Вкладка Запити > (клацніть правою кнопкою миші підключення) > властивості АБО Query> Properties АБО Data> Refresh All> Connections (when positioned on a loaded query worksheet)	Вкладка "Визначення використання" на вкладці "Використовуються в"	Властивості підключення
Запити & підключення Запити > даних &'язки	Вкладка "Запити" на вкладці "Підключення"	Властивості підключення
Наявні підключення Дані > наявних підключень	Вкладка ''Підключення'' вкладка ''Таблиці''	Підключення до зовнішніх даних
Властивості зовнішніх даних АБО Властивості зовнішнього діапазону даних АБО Data> Properties (Disabled if not positioned on a query worksheet) (Вимкнуто, якщо не розташувати на аркуші запиту)	Використовується на вкладці (у діалоговому вікні Властивості підключення)   Кнопка "Оновити" праворуч на вкладці "Властивості запиту"	Керування зовнішніми діапазонами даних і їхніми властивостями
Вкладка "Властивості > "Визначення" > "Експортувати файл підключення" АБО Query> експортувати файл підключення	(Немає вкладок) Tunnels to File dialog box Data sources folder	Створення, редагування та керування підключеннями до зовнішніх даних

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

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

Відомості про підключення зберігаються в книзі та також можуть зберігатися у файлі зв'язку, наприклад у файлі зв'язку з даними Office (ODC) або файлі імені джерела даних (DSN).

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

Для багатьох джерел даних також потрібен драйвер ODBC або постачальник OLE DB, щоб координувати потік даних між Excel, файлом підключення та джерелом даних.

На схемі нижче наведено основні моменти зв'язків даних.

1. Існують різні джерела даних, до яких можна підключитися: служб аналізу Analysis Services, SQL Server, Microsoft Access, інших реляційних баз даних, електронних таблиць і текстових файлів.

2. Багато джерел даних мають пов'язаний драйвер ODBC або постачальник OLE DB.

3. Файл зв'язку визначає всі відомості, необхідні для доступу до даних і їх отримання з джерела даних.

4. Відомості про підключення копіюються з файлу підключення до книги, і відомості про підключення можна легко редагувати.

5. Дані копіюються до книги, тому їх можна використовувати так само, як дані, збережені безпосередньо в книзі.

Щоб знайти файли підключень, скористайтеся діалоговим вікном Наявні підключення. (Виберіть дані > наявних підключень.) У цьому діалоговому вікні доступні такі типи підключень:

• Підключення в книзі    

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

• Файли підключень на комп'ютері    

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

• Файли підключень у мережі    

  Цей список можна створити на основі набору папок у локальній мережі, розташування яких можна розгорнути в мережі як частина розгортання групових політик Microsoft Office або бібліотеки SharePoint. 

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

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

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

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

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

За допомогою діалогового вікна Підключення можна легко керувати цими підключеннями, зокрема створювати, редагувати та видаляти (вкладка Вибір запитів > Запити & & > вкладка > (клацніть правою кнопкою миші підключення) > Властивості). У цьому діалоговому вікні можна виконати такі дії: 

• Створювати, редагувати, оновлювати та видаляти зв'язки, які використовуються в книзі.

• Перевірте джерело зовнішніх даних. Це потрібно зробити на випадок, якщо інший користувач визначив підключення.

• Відображення місця використання кожного підключення в поточній книзі.

• Діагностування повідомлення про помилку підключення до зовнішніх даних.

• Переспрямування підключення до іншого сервера чи джерела даних або заміна файлу підключення для наявного підключення.

• Легко створюйте файли підключень і надайте до нього спільний доступ користувачам.

Файли підключення особливо корисні для узгодженого спільного доступу до підключень, що робить підключення більш видимими, що допомагає підвищити безпеку підключень і полегшити адміністрування джерел даних. Найкращий спосіб надати спільний доступ до файлів підключення – розмістити їх у безпечному та надійному розташуванні, наприклад у мережевій папці або бібліотеці SharePoint, де користувачі можуть читати файл, але лише призначені користувачі можуть змінювати файл. Докладні відомості див. в розділах Надання спільного доступу до даних у форматі ODC.

Використання файлів ODC

Файли зв'язку даних Office (ODC), підключаючись до зовнішніх даних у діалоговому вікні Вибір джерела даних або за допомогою майстра зв'язків даних для підключення до нових джерел даних. Для збереження відомостей про підключення у файлі ODC використовуються спеціальні html і XML-теги. Ви можете легко переглянути або відредагувати вміст файлу в Excel.

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

OdC-файли – це рекомендований спосіб підключення до даних і обміну даними. Ви можете легко перетворити інші традиційні файли зв'язків (DSN, UDL і файли запитів) у файл ODC. Для цього відкрийте файл підключення та на вкладці Визначення діалогового вікна Властивості підключення натисніть кнопку Експорт файлу підключення.

Використання файлів запитів

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

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

Використання файлів запитів OQY    Файли OQY можна зберегти, щоб підключитися до даних у базі даних OLAP на сервері або в автономний файл куба (CUB). Під час створення джерела даних для бази даних або куба OLAP за допомогою майстра багатовимірних підключень у Microsoft Query файл OQY створюється автоматично. Оскільки бази даних OLAP не впорядковано в записи або таблиці, для доступу до цих баз даних не можна створювати запити або файли DQY.

Використання файлів запитів RQY    Excel може відкривати файли запитів у форматі RQY, щоб підтримувати драйвери джерела даних OLE DB, які використовують цей формат. Докладні відомості див. в документації для драйвера.

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

Використання файлів веб-запитів IQY    Excel може відкривати файли веб-запитів iqy, щоб отримати дані з Інтернету. Докладні відомості див. в статтях Експорт до Excel із SharePoint.

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

Програма Excel автоматично назве діапазон зовнішніх даних таким чином:

• Діапазони зовнішніх даних із файлів зв'язку з даними Office (ODC) мають такі самі імена, що й ім'я файлу.

• Діапазони зовнішніх даних із баз даних мають назву запиту. За Query_from_ джереладаних – це ім'я джерела даних, яке використовувався для створення запиту.

• Діапазони зовнішніх даних із текстових файлів мають ім'я текстового файлу.

• Діапазони зовнішніх даних із веб-запитів мають ім'я веб-сторінки, з якої отримано дані.

Якщо аркуш містить кілька діапазонів зовнішніх даних із того самого джерела, діапазони нумеруються. Наприклад, MyText, MyText_1, MyText_2 тощо.

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

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

Підключені дані можна використовувати й оновлювати в службах Excel Services. Як і будь-яке зовнішнє джерело даних, може знадобитися автентифікація доступу. Докладні відомості див. в ційи: Оновлення зв'язків із зовнішніми даними в Excel. Fдокладні відомості про облікові дані див. в розділі Параметри автентифікації служб Excel Services.

У таблиці нижче наведено список джерел даних, які підтримуються для кожного об'єкта даних у програмі Excel.

Excel дані об'єкт	Створення Зовнішнє дані діапазон?		OLE DB	ODBC	Текст файл	HTML файл	XML файл	SharePoint список
Майстер імпорту текстових файлів	Так		Ні	Ні	Так	Ні	Ні	Ні
Звіт зведеної таблиці (не OLAP)	Ні		Так	Так	Так	Ні	Ні	Так
Звіт зведеної таблиці (OLAP)	Ні		Так	Ні	Ні	Ні	Ні	Ні
Таблиця Excel	Так		Так	Так	Ні	Ні	Так	Так
Карта XML	Так		Ні	Ні	Ні	Ні	Так	Ні
Веб-запит	Так		Ні	Ні	Ні	Так	Так	Ні
Майстер зв'язків даних	Так		Так	Так	Так	Так	Так	Так
Microsoft Query	Так		Ні	Так	Так	Ні	Ні	Ні

Спосіб вирішення для таблиць Excel і іменних діапазонів у службах Excel Services

Якщо потрібно відобразити книгу Excel у службах Excel Services, можна підключитися до даних і оновити їх, але потрібно використовувати звіт зведеної таблиці. Служби Excel Services не підтримують зовнішні діапазони даних. Це означає, що служби Excel Services не підтримують таблицю Excel, підключену до джерела даних, веб-запиту, карти XML або Microsoft Query.

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

Давайте віднесемо подорож до бази даних на смужці пам'яті.

Відомості про MDAC, базу даних OLE та OBC

По-перше, вибачення за всі акроніми. Компоненти доступу до даних Microsoft (MDAC) 2.8 входять до складу Microsoft Windows. За допомогою MDAC можна підключатися до даних із різноманітних реляційних і нереляційних джерел даних і використовувати їх. Можна підключитися до багатьох різних джерел даних, використовуючи драйвери ODBC або постачальники служб база даних OLE, які створили та відправили корпорація Майкрософт або розробили різні сторонні постачальники. Під час інсталяції Microsoft Office до комп'ютера додаються додаткові драйвери ODBC та постачальники OLE DB.

Щоб переглянути повний список постачальників баз даних OLE, інстальованих на комп'ютері, у файлі Зв'язок із даними відкрийте діалогове вікно Властивості зв'язку з даними, а потім перейдіть на вкладку Постачальник.

Щоб переглянути повний список інстальованих на комп'ютері постачальників ODBC, відкрийте діалогове вікно Адміністратор бази даних ODBC, а потім перейдіть на вкладку Драйвери.

Ви також можете використовувати драйвери ODBC та постачальники OLE DB від інших виробників, щоб отримувати інформацію з джерел, відмінних від джерел даних Microsoft, зокрема з інших типів баз даних ODBC та БАЗ ДАНИХ OLE. Щоб отримати відомості про інсталяцію цих драйверів ODBC або постачальників OLE DB, перегляньте документацію до бази даних або зверніться до постачальника бази даних.

Використання ODBC для підключення до джерел даних

В архітектурі ODBC програма (наприклад, Excel) підключається до диспетчера драйверів ODBC, який у свою чергу використовує певний драйвер ODBC (наприклад, драйвер Microsoft SQL ODBC), щоб підключитися до джерела даних (наприклад, бази даних Microsoft SQL Server).

Щоб підключитися до джерел даних ODBC, виконайте такі дії:

1. Переконайтеся, що на комп'ютері, який містить джерело даних, інстальовано відповідний драйвер ODBC.

2. Визначте ім'я джерела даних (DSN), використовуючи адміністратора джерел даних ODBC, щоб зберігати відомості про підключення в реєстрі чи файлі DSN, або рядок підключення в коді Microsoft Visual Basic, щоб передати відомості про підключення безпосередньо до диспетчера драйверів ODBC.

   Щоб визначити джерело даних, у Windows натисніть кнопку Пуск і виберіть пункт Панель керування. Послідовно виберіть елементи Система йобслуговування та Адміністрування. Клацніть Продуктивність і обслуговування, а потім – Адміністрування. а потім клацніть Джерела даних (ODBC). Щоб отримати докладні відомості про різні параметри, натисніть кнопку Довідка в кожному діалоговому вікні.

Джерела даних комп'ютера

Джерела даних комп'ютера зберігають відомості про підключення в реєстрі на певному комп'ютері з визначеним користувачем іменем. Джерела даних комп'ютера можна використовувати лише на тому комп'ютері, на якому їх визначено. Є два типи джерел даних комп'ютера: користувач і система. Користувацькі джерела даних можуть використовувати лише поточний користувач і бачити лише його. Системні джерела даних можуть використовувати всі користувачі на комп'ютері, і вони видимі для всіх користувачів комп'ютера.

Джерело даних комп'ютера особливо корисне, коли потрібно забезпечити додатковий захист, оскільки воно допомагає забезпечити перегляд джерела даних комп'ютера лише тим користувачам, які ввійшли в систему, а віддалений користувач не може скопіювати джерело даних комп'ютера на інший комп'ютер.

Файл джерела даних

Файлові джерела даних (також відомі як файли DSN) зберігають відомості про підключення в текстовому файлі, а не в реєстрі, і вони зазвичай гнучкіші у використанні, ніж джерела даних комп'ютера. Наприклад, ви можете скопіювати файлне джерело даних на будь-який комп'ютер із правильним драйвером ODBC, щоб програма використала узгоджені та точні відомості про підключення на всіх комп'ютерах, які вона використовує. Крім того, можна розмістити файлне джерело даних на одному сервері, надати до нього спільний доступ на багатьох комп'ютерах у мережі та легко зберігати відомості про підключення в одному розташуванні.

Крім того, для файлової джерела даних можна скасувати спільний доступ. Файлне джерело даних із спільним доступом міститься на одному комп'ютері та вказує на джерело даних комп'ютера. Щоб отримати доступ до наявних джерел даних комп'ютера із файлової джерела даних, можна використовувати файлні джерела даних із вимкнутою спільним доступом.

Використання бази даних OLE для підключення до джерел даних

В архітектурі баз даних OLE програма, яка шукає доступ до даних, називається споживачем даних (наприклад, Excel), і програма, яка надає власний доступ до даних, називається постачальником баз даних (наприклад, постачальником баз даних Microsoft OLE для SQL Server).

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

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

• Створіть пустий текстовий файл із розширенням імені файлу UDL, а потім змініть файл із діалоговим вікном Властивості зв'язку з даними.