Уніфіковане завантажити та перетворити досвід

В Excel 2016 додано потужний набір засобів, який називається "Завантажити та перетворити". Ці засоби створені на основі технології Power Query. Вони дають змогу легко зв’язувати, об’єднувати та формувати дані з різних джерел. У цій статті описано основні принципи роботи із засобами "Завантажити та перетворити".

Ми з радістю повідомляємо вам про наступне значне оновлення можливостей імпорту й формування даних в Excel, у рамках якого запроваджуються уніфікований набір засобів "Завантажити та перетворити". В оновленні Office 365 за березень 2017 року ми розширили можливості імпорту даних Excel, а також змінили порядок кнопок на вкладці "Дані", тож тепер ви можете без зусиль і максимально ефективно використовувати набір засобів "Завантажити та перетворити".

Параметри завантаження та перетворення на вкладці "Дані"

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

Зверніть увагу, що новий набір засобів "Завантажити та перетворити" замінив застарілі майстри імпорту даних, які містилися на вкладці Дані в групі Отримання зовнішніх даних. Але за потреби ви можете отримати до них доступ. Докладні відомості див. в розділі Відновлення застарілого набору функцій "Отримання зовнішніх даних".

Примітка.: Ця функція Excel доступна, тільки якщо ви передплатили Office 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 і не міститиме жодні кроки перетворення, які міг би містити вихідний запит. Якщо вам потрібно відновити застарілі з’єднувачі "З бази даних Microsoft Access", "З бази даних SQL Server", "З інформаційного каналу OData", див. розділ Відновлення застарілого набору функцій "Отримання зовнішніх даних".

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

Разом із набором засобів "Завантажити та перетворити" ми запровадили в Excel 2016 два методи імпорту даних. Перший – це новий метод на основі набору засобів Завантажити та перетворити, для якого потрібно використовувати запити (а також внутрішні підключення книги, які дають змогу оновлювати елементи та завантаження їх до неї). Другий – це застарілий метод, для якого використовувалися підключення книги. Коли він застосовувався, керування запитами та підключеннями книги також здійснювалось окремо. Щоб керувати запитами книги, використовувалася спеціальна область "Запити". Для підключень це здійснювалося в діалоговому вікні "Підключення робочої книги". Це часто плутало користувачів.

У цьому оновленні ми вирішили цю проблему. Тепер керувати наявними запитами та підключеннями книги, а також отримувати до них доступ можна з виділеної області Запити та підключення. Щоб відкрити цю бічну область, виберіть Дані > Запити та підключення.

Області "Завантажити та перетворити" й "Запити та зв’язки"

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

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

На вкладці Підключення у верхній частині бічної області можна переглядати доступні застарілі підключення книги, пов’язаної з одним із застарілих майстрів, зокрема З тексту, Зі служб Analysis Services або Microsoft Query, а також керувати ними. Клацніть підключення правою кнопкою миші, щоб відкрити для нього контекстне меню. Тут можна виконувати додаткові дії, зокрема оновлювати дані підключення з джерела, видаляти підключення або керувати властивостями підключення в добре знайомому діалоговому вікні Властивості підключення.

Відновлення застарілого набору функцій "Отримання зовнішніх даних"

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

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

Виберіть Файл > Параметри > Дані > Показати майстрів імпорту даних старих версій, а потім виберіть необхідний застарілий майстер:

Зображення параметрів завантажити та перетворити застарілі майстер з файлу > Параметри > даних.

Відкрийте розкривне меню (Дані > Отримати дані) і запустіть один із попередньо вибраних застарілих майстрів імпорту даних, вибравши його в спеціальній категорії "Застарілі майстри":

Параметри застарілого меню завантаження та перетворення

Додаткові відомості про набір засобів "Завантажити та перетворити"

Щоб дізнатися більше про можливості набору засобів Завантажити та перетворити, радимо спочатку переглянути довідку з надбудови Microsoft Power Query для Excel.

Потрібна додаткова довідка?

Ви завжди можете поставити запитання експерту в спільноті Tech (у розділі Excel), отримати підтримку в спільноті, що допомагає знайти відповіді на запитання, або запропонувати нову функцію чи вдосконалення на форумі Excel User Voice.

Удосконалення навичок роботи з Office
Ознайомтеся з навчальними матеріалами
Отримуйте нові функції раніше за інших
Приєднайтеся до оцінювачів Office

Ця інформація корисна?

Дякуємо за ваш відгук!

Дякуємо за відгук! Схоже, вам може стати в нагоді допомога одного з наших спеціалістів служби підтримки Office, з яким ми вас можемо з’єднати.

×