Переміщення даних в Excel

Якщо дані завжди під рукою, то програма Excel схожа на Центральний вокзал. Уявіть, що дані – це потяг із пасажирами, які регулярно заходять у програму Excel, вносять зміни, а потім вирушають. Є десятки способів увійти в програму Excel, яка імпортує дані всіх типів, і цей список постійно збільшується. Коли дані будуть збережені в програмі Excel, фігуру можна буде змінювати так, як вам потрібно, використовуючи Power Query. Дані, як і всі ми, також потребують «догляду та годування», щоб все працювало безперебійно. Ось тут на допомогу приходять підключення, запит і властивості даних. Нарешті, дані залишають залізничний вокзал Excel багатьма способами: імпортуються з інших джерел даних, надані у вигляді звітів, діаграм і зведених таблиць, а також експортуються до Power BI та Power Apps.  

Основні дії, які можна робити з даними на залізничній станції Excel

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

• Імпорт Дані можна імпортувати з багатьох зовнішніх джерел. Ці джерела даних можуть бути на комп'ютері, у хмарі або в іншому куточку світу. в статті Імпорт даних із зовнішніх джерел);
• Power Query За допомогою Power Query (попередня назва – "Отримати & перетворення") можна створювати запити, щоб формувати, перетворювати та об'єднувати дані різними способами. Ви можете експортувати свою роботу як шаблон Power Query, щоб визначити операцію потоку даних у Power Apps. Щоб доповнити зв'язані типи даних, можна навіть створити новий тип даних. Докладні відомості див. в Power Query довідці Excel.
• Безпека Конфіденційність даних, облікові дані та автентифікація завжди є предметом постійної проблеми. Докладні відомості див. в статтях "Керування настройками та дозволами джерела даних " і "Установлення рівнів конфіденційності".
• Оновити Щоб внести до програми Excel зміни, наприклад додані, оновлені та видалені, імпортовані дані зазвичай потребують операції оновлення. Докладні відомості див. в статті "Оновлення зв'язків із зовнішніми даними в програмі Excel".
• Підключення/Властивості З кожним зовнішнім джерелом даних пов'язано різноманітні підключення та відомості про властивості, які іноді потрібно змінювати залежно від обставин. Докладні відомості див. в статтях "Керування зовнішніми діапазонами даних і їхніми властивостями", "Створення, редагування та керування зв'язками із зовнішніми даними" та "Властивості підключення".
• Спадщина Традиційні методи, такі як застарілі майстри імпорту та MSQuery, все ще доступні для використання. Докладні відомості див. в статтях " Параметри імпорту й аналізу даних " і "Використання Microsoft Query для отримання зовнішніх даних".

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

Зведення зв'язків і властивостей

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

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

Діалогове вікно або область Шляхи команд	Вкладки й тунелі	Головна розділ довідки
Останні джерела Data (Дані)>Останні джерела	(Без вкладок) Tunnels to Connect>Navigator dialog box	Керування настройками та дозволами джерел даних
Властивості підключення АБО Майстер зв’язків даних Data (Дані)>Запити & підключення>Вкладка >"Підключення" (клацніть підключення правою кнопкою миші) >Властивості	Вкладка "Використання " Вкладка "Визначення " Вкладка "Використовується в"	Властивості підключення
Властивості запиту Data (Дані)>Наявні> підключення (клацніть підключення правою кнопкою миші) >Редагування властивостей підключення АБО Data (Дані)>Запити & підключення| Вкладка >"Запити" (клацніть підключення правою кнопкою миші) >Властивості АБО Запит>Властивості АБО Data (Дані)>Оновити все>Підключення (коли їх розміщено на аркуші завантаженого запиту)	Вкладка "Використання " Вкладка "Визначення " Вкладка "Використовується в"	Властивості підключення
Запити & підключення Data (Дані)>Запити & підключення	Вкладка "Запити " Вкладка "Підключення"	Властивості підключення
Наявні підключення Data (Дані)>Наявні підключення	Вкладка "Підключення" Вкладка «Таблиці»	Підключення до зовнішніх даних
Властивості зовнішніх даних АБО Властивості діапазону зовнішніх даних АБО Data (Дані)>Властивості (вимкнуто, якщо їх не розміщено на аркуші запиту)	Використовується на вкладці (у діалоговому вікні "Властивості підключення ") Refresh button on the right tunnels to Query Properties	Керування зовнішніми діапазонами даних і їхніми властивостями
Властивості> підключенняВкладка> "Визначення" Експорт файлу підключення АБО Запит>Експорт файлу підключення	(Без вкладок) Тунелі до Діалогове вікно "Файл " Data sources folder	Створення, редагування та керування підключеннями до зовнішніх даних

Основи зв'язків із даними

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

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

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

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

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

 

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

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

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

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

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

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

Пошук зв'язків

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

• Підключення в книзі 
  У цьому списку відображаються всі поточні підключення в книзі. Список створюється з уже визначених зв'язків, діалогового вікна " Вибір джерела даних " майстра зв'язків даних або зв'язків, попередньо вибраних у цьому діалоговому вікні як підключення.
• Файли підключень на комп'ютері 
  Цей список створюється в папці "Мої джерела даних ", яка зазвичай зберігається в папці "Документи ".
• Файли підключень у мережі 
  Цей список можна створити з набору папок у локальній мережі, розташування яких може бути розгорнуто в усій мережі в рамках розгортання групових політик Microsoft Office або бібліотеки SharePoint. 

Редагування властивостей підключення

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

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

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

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

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

Керування підключеннями

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

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

Спільне використання зв'язків ODC і запитів у файлах

Файли підключень особливо корисні, щоб регулярно обмінюватися підключеннями, робити їх помітнішими, підвищувати їх безпеку та полегшуючи адміністрування джерел даних. Найкращий спосіб надати спільний доступ до файлів підключень – розмістити їх у безпечному та надійному розташуванні, наприклад у мережній папці або бібліотеці 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_source – це ім'я джерела даних, з якого ви створили запит.
• Зовнішні діапазони даних текстових файлів називаються іменем текстового файлу.
• Зовнішні дані з веб-запитів називаються іменем веб-сторінки, з якої отримано дані.

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

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

Data source support in служби Excel Services services служби Excel Services

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

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

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

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

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

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

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

Компоненти доступу до даних ODBC і OLE DB

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

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

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

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

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

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

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

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

Додаткові відомості

Power QueryДовідка з надбудови Power Query для програми Excel