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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

• Connections у книзі    

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

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

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

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

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

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

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

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

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

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

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

• створювати, редагувати, оновлювати й видаляти зв’язки, які використовуються в книзі;

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

• показувати, де використовується кожне підключення в поточній книзі;

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

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

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

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

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

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

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

Файли 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_source – це ім'я джерела даних, за допомогою якого ви створювали запит.

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

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

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

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

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

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

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

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

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

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

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

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

Відомості про MDAC, OLE DB та OBC

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

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

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

Також можна використовувати драйвери ODBC та постачальників OLE DB від інших виробників, щоб отримувати інформацію з джерел, відмінних від джерел даних Microsoft, зокрема з баз даних ODBC та OLE DB інших типів. Відомості про те, як інсталювати такі драйвери 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 DB програма, яка використовується для доступу до даних, називається споживачем даних (наприклад, Excel), а програма, яка надає власний доступ до даних, називається постачальником бази даних (наприклад, постачальником баз даних OLE для SQL Server).

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

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

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