Примітка.: Ця стаття зробила свою роботу та скоро буде видалена. Щоб не з’являлися повідомлення "Сторінку не знайдено", ми видаляємо посилання, про які знаємо. Якщо ви створили посилання на цю сторінку, видаліть їх. Так ми разом зробимо Інтернет кращим.
В Excel 2016 з'явилось потужний набір інструментів, який називається "Завантажити & перетворення". Ці засоби, основані на технології Power Query, дають змогу легко підключати, об'єднувати та формувати дані, що надходитить із різних джерел. Нижче наведено посібник & "Перетворення", який допоможе вам навчитися основам роботи.
Ми з радістю повідомляємо вам про наступне значне оновлення можливостей імпорту й формування даних в Excel, у рамках якого запроваджуються уніфікований набір засобів "Завантажити та перетворити". В оновленнях за березень 2017 Microsoft 365 року ми розширили можливості імпорту даних Excel і змінили порядок кнопок на вкладці Дані, щоб отримати повну перевагу функції "Завантажити & Перетворити".
Уніфікований набір засобів "Завантажити та перетворити" використовує добре інтегрований сучасний набір можливостей імпорту та формування даних. Ви можете легко підключати дані та отримувати їх із різних популярних джерел, наприклад файлів, баз даних, служб Azure, онлайнових служб тощо. Крім того, ви можете формувати ці дані різними способами, зокрема сортувати їх і об’єднувати, змінювати їхні типи та розділяти стовпці тощо. Оскільки постійно з’являються нові з’єднувачі, а дані можна перетворювати без зайвих зусиль, набір засобів "Завантажити та перетворити" стане для вас незамінним у повсякденній роботі й допоможе заощадити час.
Зверніть увагу, що нова версія замінює старіші майстри імпорту даних, які були доступні в розділі Дані > Отримати зовнішні дані. Проте за потреби доступ до них можна буде отримати. Докладні відомості див. в статті Відновлення застарілої можливості "Отримання зовнішніх даних"
Примітка.: Ця функція доступна в Excel, лише якщо у вас є передплата на Microsoft 365. Якщо ви абонентMicrosoft 365, переконайтеся, що маєте найновішу версію Office.
Отримання даних із популярних джерел, зокрема CSV-файлів та Інтернету
Ми знаємо, що користувачі, які імпортують дані, хотіли б тримати найпопулярніші з’єднувачі під рукою на стрічці. У цьому оновлені ми спробували задовольнити цю потребу, а також подбали про сумісність з іншими засобами. Тепер на вкладці "Дані" в розділі "Завантажити та перетворити" доступні такі три найпопулярніші з’єднувачі: З тексту/CSV, З Інтернету та З таблиці або діапазону.
З тексту/CSV
За допомогою цього з’єднувача можна легко імпортувати дані з текстових файлів (TXT), файлів даних із роздільниками-комами (CSV) або файлів форматованого тексту (PRN). Цей сучасний з’єднувач, створений на основі технології Power Query, замінює застарілу версію майстра імпорту даних із тексту.
Щоб імпортувати дані за допомогою цього з’єднувача, виберіть Дані > З тексту/CSV, виберіть вихідний файл, а потім натисніть кнопку OK. Цей новий і вдосконалений з’єднувач З тексту/CSV проаналізує структуру вхідного файлу й автоматично застосує відповідні параметри імпорту. Він може розділяти стовпці за роздільником, перетворювати перший рядок на заголовки стовпців тощо.
Якщо вас не влаштовують результати попереднього перегляду, ви можете в тому ж вікні вручну налаштувати основні параметри імпорту, зокрема "Походження файлу", "Роздільник" або "Виявлення типу даних". Результати в області попереднього перегляду автоматично змінюються відповідно до вибраних параметрів.
Потім ви можете натиснути кнопку Завантажити, щоб завантажити дані безпосередньо на аркуш, або клацнути стрілку поруч із кнопкою Завантажити, щоб завантажити дані у свою модель даних. Щоб застосувати до даних додаткові перетворення в редакторі запитів, натисніть кнопку Редагувати.
Інтернет-розташування
За допомогою цього з’єднувача можна збирати дані з HTML-сторінок. Це ще один сучасний з’єднувач на основі технології Power Query, що замінює застарілу версію майстра імпорту з Інтернету.
Щоб використати його, виберіть Дані > З Інтернету, укажіть URL-адресу цільової сторінки, а потім натисніть кнопку OK. У діалоговому вікні "Навігатор" з’явиться список усіх таблиць на сторінці. З веб-сторінкою можна взаємодіяти в табличному поданні або веб-поданні. У табличному поданні спочатку клацніть ім’я таблиці ліворуч, і дані з цієї таблиці з’являться праворуч. Якщо вибрати веб-подання, таблицю можна вибрати в списку "Навігатор" або в області попереднього перегляду "Веб-подання". У будь-якому поданні можна вибрати кілька таблиць.
З таблиці або діапазону
За допомогою цього з’єднувача ви можете створити запит, зв’язаний із таблицею або іменованим діапазоном на аркуші Excel. Потім можна уточнити дані та застосувати до них додаткові перетворення у вікні редактора запитів.
Отримання даних із додаткових джерел, наприклад XML-файлу або сервера Microsoft SQL Server
Ви можете створити запит на імпорт даних з одного джерела даних, наприклад бази даних Microsoft Access або інформаційного каналу OData, або одночасно імпортувати дані з кількох текстових файлів, що містяться в одній папці.
На вкладці Дані в розкривному меню Отримати дані можна переглянути всі доступні джерела даних. Варіанти джерел даних упорядковані за категоріями, наприклад книги Excel, XML-файли або вся папка, бази даних (наприклад, SQL Server або Oracle), служби Azure (наприклад, HDInsight або сховище BLOB-об’єктів), онлайнові служби (наприклад, список SharePoint Online або Salesforce), а також ряд інших джерел, як-от OData або ODBC.
Список доступних з’єднувачів постійно збільшується.
У цьому оновленні ми також вилучили такі застарілі версії майстрів, які раніше містилися на вкладці "Дані" в розділі "Отримання зовнішніх даних": "З Access", "З Інтернету", "З тексту", "З бази даних SQL Server", "З інформаційного каналу OData", "З імпортованих даних XML" і "З майстра зв’язків даних". Якщо з якоїсь причини вам знадобиться один із вилучених майстрів, ознайомтеся з розділом Відновлення застарілого набору функцій "Отримання зовнішніх даних".
У новому наборі засобів ми залишили два популярних і доволі унікальних майстри, які входили до застарілого набору функцій "Отримання зовнішніх даних": Зі служб Analysis Services і З Microsoft Query. Щоб знайти їх у новому наборі засобів, виберіть Дані > З бази даних або Дані > З інших джерел.
Вилучення й формування даних за допомогою набору засобів "Завантажити та перетворити"
Засоби Завантажити та перетворити дають змогу очищати та формувати дані в процесі аналізу даних. Ви можете вилучати стовпці, фільтрувати рядки, змінювати типи даних стовпців, заміняти значення тощо. Це можна зробити в спеціальному вікні Редактор запитів, яке спрощує перетворення даних і дає змогу переглянути його результати.
Перейдіть на стрічці на вкладку Дані, клацніть Отримати дані, підключіться до потрібного джерела даних (наприклад, файлу або бази даних) за допомогою одного з доступних з’єднувачів і перегляньте дані у вікні Навігатор.
Після цього виберіть таблиці, які потрібно імпортувати, і натисніть кнопку Редагувати, щоб відкрити вікно Редактор запитів.
На стрічці у вікні Редактор запитів доступний широкий набір команд. Ви можете застосувати до даних широкий спектр перетворень, зокрема сортувати їх і фільтрувати, змінювати їхні типи, розділяти або об’єднувати стовпці, об’єднувати дані тощо.
Усі кроки перетворення, застосовані до даних, записуються та зберігаються як частина запиту. Записані перетворення можна відстежувати та керувати ними за допомогою розділу ЗАСТОСОВАНІ КРОКИ у вікні редактора запитів, і їх буде повторно застосовано до даних під час кожного оновлення запиту.
Отримання даних з останніх джерел, файлів підключення до баз даних Office (ODC), інших книг або таблиць.
Один із найпоширеніших способів імпорту даних – підключити останні джерела та отримати з них дані. Користувачі часто визначають набір джерел, з яких вони часто імпортують дані.
Щоб підключитися до одного з останніх джерел і створити для нього запит, виберіть Дані > Останні джерела. Перегляньте джерела, до яких ви нещодавно підключались, виберіть потрібне, а потім натисніть кнопку Підключитися, щоб продовжити імпорт.
Інший популярний сценарій – імпорт даних із наявних підключень або таблиць у книзі. Наприклад, можна створити нову зведену таблицю на основі попередньо створеного підключення книги. Крім того, можна зв'язати та імпортувати дані із джерела даних за допомогою файлу зв'язку з базами даних Office, визначеного користувачем у вашій організації.
У цьому оновленні ви зможете експортувати будь-які визначення запитів до файлу ODC, а потім надати до нього спільний доступ у книгах або поділитися ним зі співробітниками. Щоб експортувати визначення запиту до файлу ODC, клацніть правою кнопкою миші запит у бічній області Запити та підключення, а потім виберіть Експортувати файл підключення. Створений файл ODC міститиме визначення запиту з усіма залежностями, зокрема інструкції з підключення до джерела даних і кроки перетворення, застосовані до даних. Ви також можете експортувати визначення запиту за допомогою діалогового вікна Властивості запиту, яке можна відкрити, клацнувши Властивості в контекстному меню запиту.
Отримавши файл ODC, ви зможете використовувати його, а також імпортувати визначення запиту до книги. Щоб імпортувати дані на основі механізму ODC, виберіть Дані > Наявні підключення. Коли з’явиться діалогове вікно Наявні підключення, ви зможете імпортувати дані з доступних файлів ODC, наявних підключень книги або таблиць.
Якщо потрібно створити файл ODC, який використовуватиме інша програма, як-от Visio або Excel 2013 або попередніх версій, це можна зробити за допомогою таких з'єдначів: З бази даних SQL Server, З бази даних Microsoft Access, З каналу OData, Зі служб Analysis Services, Із microsoft Query або застарілого майстра зв'язків даних. Важливо знати, що під час використання ODC-файлів в іншій програмі ця програма використовуватиме лише повторно вказівки з підключення до ODC і не міститиме кроків перетворення, які могло містити вихідний запит. Якщо потрібно відновити старіші версії Програми Access, Sql Server, з'єдначів каналу даних OData, див. статті Відновлення застарілого на основі застарілої версії "Отримання зовнішніх даних"?
Керування наявними запитами та підключеннями книги
Уперше введені функції "Завантажити & Transform" в Excel 2016 ми ввели два способи імпорту даних: першою була нова можливість завантажити надбудову "Завантажити & Перетворити", яка використовує запити (а також використовує зв'язки в книзі, які лежать під час оновлення та завантаження до елементів книги). Другим був старіший застарілий досвід, у якому використовуються підключення книги. Керування запитами й підключеннями книги також здійснюється окремо. Запитами книги керували через виділену область запитів, а підключеннями керувалися через діалогове вікно Підключення робочої книги. Це призводило до плутанини з нашими користувачами.
У цьому оновленні ми вирішили цю проблему. Тепер керувати наявними запитами та підключеннями книги, а також отримувати до них доступ можна з виділеної області Запити та підключення. Щоб відкрити цю бічну область, виберіть Дані > Запити та підключення.
На вкладці Запити у верхній частині цієї області можна переглянути доступні запити в книзі та керувати ними. Клацніть запит правою кнопкою миші, щоб відкрити контекстне меню запиту, у якому можна виконати додаткові дії. Наприклад, тут можна оновити дані запиту з основного джерела, скопіювати або вставити запит, змінити кроки перетворення запиту за допомогою редактора запитів тощо.
Ми також спростили керування запитом і його базовим підключенням у діалоговому вікні Властивості запиту. Тепер, коли ви створюєте запит у книзі, автоматично створюється сутність базового підключення, що пов’язана з цим запитом. Запит відповідає за підключення до джерела даних і застосування в ньому потрібних перетворень, а базове підключення визначає місце завантаження даних (наприклад, аркуш або модель даних), а також параметри оновлення даних запиту, наприклад частоту. В оновленому діалоговому вікні Властивості запиту ви можете легко налаштувати всі властивості запиту, наприклад його ім’я або інструкції з перетворення, а також властивості базового підключення, наприклад параметри оновлення, з одного централізованого розташування. Щоб відкрити нове діалогове вікно Властивості запиту, клацніть команду Властивості в контекстному меню запиту в бічній області Запити та підключення.
На вкладці Підключення у верхній частині бічної області можна переглядати доступні застарілі підключення книги, пов’язаної з одним із застарілих майстрів, зокрема З тексту, Зі служб Analysis Services або Microsoft Query, а також керувати ними. Клацніть підключення правою кнопкою миші, щоб відкрити для нього контекстне меню. Тут можна виконувати додаткові дії, зокрема оновлювати дані підключення з джерела, видаляти підключення або керувати властивостями підключення в добре знайомому діалоговому вікні Властивості підключення.
Відновлення застарілого набору функцій "Отримання зовнішніх даних"
Порівняно із застарілими майстрами новий набір засобів "Завантажити та перетворити" надає сучасний набір з’єднувачів і розширені можливості перетворення. Проте є випадки, коли вам все ще може знадобитись один із застарілих майстрів імпорту даних. Наприклад, якщо потрібно зберегти облікові дані для входу в джерело даних у складі книги. (Зверніть увагу, що цей метод не відповідає сучасним стандартам безпеки, а тому не підтримується в новій версії набору засобів "Завантажити та перетворити".)
Щоб отримати доступ до застарілих з’єднувачів, можна налаштувати параметри стрічки або вибрати їх безпосередньо в розкривному меню (Дані > Отримати дані), попередньо додавши їх за допомогою нових і зручних параметрів конфігурації в діалоговому вікні "Параметри Excel".
Виберіть Файл > Параметри > Дані > Показати майстрів імпорту даних старих версій, а потім виберіть необхідний застарілий майстер:
Відкрийте розкривне меню (Дані > Отримати дані) і запустіть один із попередньо вибраних застарілих майстрів імпорту даних, вибравши його в спеціальній категорії "Застарілі майстри":
Додаткові відомості про набір засобів "Завантажити та перетворити"
Щоб дізнатися більше про можливості функції "& перетворення", слід почати з довідки з Microsoft Power Query для Excel.
Потрібна додаткова довідка?
Ви завжди можете поставити запитання експерту в спільноті Tech (у розділі Excel), отримати підтримку в спільноті, що допомагає знайти відповіді на запитання, або запропонувати нову функцію чи вдосконалення на форумі Excel User Voice.
