Початок роботи з набором функцій "Завантажити та перетворити" в Excel 2016

Початок роботи з набором функцій "Завантажити та перетворити" в Excel 2016

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

Поширені дії з функціями Power Query

Зазвичай послідовність дій така:

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

  • Перетворення даних відповідно до своїх потреб (вихідні дані не змінюються).

  • Поєднання даних із багатьох джерел і отримання унікального подання даних завдяки створенню моделі даних.

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

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

За допомогою набору функцій Завантажити та перетворити ви можете створювати прості або складні запити відповідно до власних потреб. Коли ви додаєте дії до запиту, Редактор запитів автоматично створює набір окремих інструкцій для виконання ваших команд. Інструкції створюються мовою М. Якщо вам подобаються потужні та гнучкі можливості сценаріїв даних, ви можете вручну створювати (або налаштовувати) запити мовою M, використовуючи Розширений редактор. Широкі можливості автоматичної настройки та створення сценаріїв уручну – це невід’ємна складова набору функцій Завантажити та перетворити в програмі Excel 2016. Докладніші відомості про Редактор запитів і Розширений редактор наведено нижче в цій статті.

Щоб створити запит в Excel 2016, на стрічці в розділі Завантажити та перетворити натисніть кнопку Новий запит.

Кнопка "Новий запит" у програмі Excel 2016

Примітка : Ця технологія також доступна в попередніх версіях програми Excel із надбудовою Power Query (яку можна завантажити) і в службі Power BI. Щоб дізнатися, як працювати з надбудовою Power Query в попередніх версіях програми Excel, див. статтю Початок роботи з надбудовою Power Query.

Підключення

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

Якщо натиснути кнопку Новий запит у розділі Завантажити та перетворити на вкладці Дані, у меню з’являться доступні джерела даних. Існує чимало джерел даних, які можна використовувати в запитах. Це можуть бути файли, наприклад книги Excel або файли CSV, бази даних, як-от Access, SQL Server, Oracle і MySQL, служби Azure, зокрема HDInsight чи Сховище BLOB-об’єктів, а також інші різноманітні джерела: Інтернет, списки SharePoint, файли Hadoop, Facebook, Salesforce тощо.

Примітка : Деякі джерела даних доступні лише за ліцензіями на випуск "Професійний" або "Професійний плюс".

Джерела даних для запиту

Під час підключення до джерела даних набір функцій Завантажити та перетворити відображає вікно Навігатор, у якому можна редагувати дані з джерела. Якщо у вікні Навігатор натиснути кнопку Редагувати, набір функцій Завантажити та перетворити запускає Редактор запитів – спеціальне вікно, яке спрощує підключення до даних. У цьому вікні відображаються зв’язки з даними й застосовані перетворення. Докладні відомості про Редактор запитів наведено в розділі Перетворення нижче.

Перетворення

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

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

Навігатор запитів

Якщо натиснути кнопку Завантажити, джерело даних буде перенесено безпосередньо в Excel. У цьому випадку ми натиснули кнопку Редагувати, щоб запустити Редактор запитів.

Редактор запитів

Редактор запитів стежить за всіма діями, які ви виконуєте з даними. Редактор запитів записує й позначає всі кроки під час перетворення даних. Редактор запитів також відстежує всі операції перетворення: підключення до даних (джерела даних), видалення стовпця, об’єднання та змінення типу даних – у розділі ЗАСТОСОВАНІ КРОКИ в області Параметри запиту.

Підключившись до даних і перетворюючи їх, ви формуєте запит.

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

Дані можна перетворювати найрізноманітнішими способами. Зокрема, це можна робити за допомогою мови M (якою Редактор запитів записує кроки у фоновому режимі). Для цього використовуйте Розширений редактор, який можна відкрити в Редакторі запитів на вкладці стрічки Подання. У ньому ви зможете змінювати кроки, пов’язані з наявним запитом, використовуючи мову M, а також створювати нові запити.

Розширений редактор для запиту

Створивши запит, на вкладці Головна натисніть кнопку Закрити й завантажити. Результати запиту буде завантажено в Excel, і вони з’являться на новій вкладці книги.

Додаткові відомості про перетворення:

Надання спільного доступу

Коли ви зберігаєте книгу Excel, яка містить запит, він зберігається автоматично. Щоб переглянути всі запити в книзі Excel, на вкладці Дані в розділі Завантажити та перетворити виберіть команду Відобразити запити.

Відобразити запити

В області Запити книги відображаються всі запити в книзі.

Запити книги в програмі Excel 2016

Але навіщо на цьому зупинятися? За допомогою каталогу даних Power BI ви можете надати спільний доступ до запитів будь-кому у своїй організації. Крім того, можна створити запит, який часто використовуватиметься, щоб застосовувати його в кількох книгах, заощаджуючи свій час. Замість того, щоб зберігати книги Excel і надсилати їх електронною поштою (а потім намагатися з’ясувати, яка з версій вихідна, що змінено й чи не застарілі ці дані), через що в папці "Вхідні" накопичуватиметься безліч версій книги, які не відстежуються, збережіть запит у каталозі даних. Просто клацніть правою кнопкою миші запит в області Запити книги, щоб відкрити меню з численними параметрами й командами, зокрема Надіслати до каталогу даних.

Надіслати до каталогу даних

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

Крім того, запити можна об’єднувати та додавати, що робить їх стандартними блоками для багаторазового використання.

Ви також можете опублікувати книгу в Power BI і створювати веб-звіти, а потім надавати доступ до них групі, уточнювати й автоматично оновлювати їх. Щоб опублікувати книгу в Power BI, виберіть елементи Файл > Опублікувати > Опублікувати в Power BI. Опублікувати в Power BI

Примітка : Для публікації в Power BI книгу необхідно зберегти в службі OneDrive для бізнесу.

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

Докладніше про надання спільного доступу:

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

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

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

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

×