За допомогою Power Query (відомої як Перетворення & Excel) можна імпортувати або підключатися до зовнішніх даних, а потім формувати ці дані, наприклад видаляти стовпець, змінювати тип даних або об'єднувати таблиці відповідно до своїх потреб. Потім можна завантажити запит до Excel, щоб створити діаграми та звіти. Періодично можна оновлювати дані, щоб вони були актуальні. Power Query доступна в трьох програмах Excel, Excel для Windows, Excel для Mac і Excel для Інтернету. Зведення всіх Power Query розділів довідки див. в статті Power Query довідки Excel.

Примітка.: Power Query в Excel для Windows використовує .NET framework, але для цього потрібна версія 4.7.2 або пізніша. Ви можете завантажити найновішу .NET Framework звідси. Виберіть рекомендовану версію та завантажте середовище виконання.

Існує чотири етапи використання Power Query.

Power Query common steps

  1. Підключення    Підключення до даних у хмарі, службі або локально

  2. Перетворити    Формування даних відповідно до своїх потреб, а вихідне джерело залишається незмінним

  3. Об'єднати    Інтеграція даних із кількох джерел для отримання унікального подання в даних

  4. Навантаження   Виконуйте запит і завантажуйте його на аркуш або модель даних і періодично оновлюйте.

У наведених нижче розділах докладно описано кожен етап. 

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

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

Команди імпорту в інтерфейсі користувача

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

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

Power Query використовує виділене вікно під назвою Редактор Power Query для полегшення та відображення перетворень даних. Щоб відкрити Редактор Power Query, натисніть кнопку Запустити Редактор Power Query з команди Отримати дані в групі Отримати & Перетворення даних, але вона також відкривається, коли ви підключаєтеся до джерела даних, створюєте новий запит або завантажуєте запит.

Інтерфейс користувача параметрів запиту

Редактор Power Query відстежує всі ваші дії з даними, записуючи та позначаючи кожне перетворення або крок, застосований до даних. Незалежно від того, чи це перетворення – це зв'язок даних, видалення стовпця, злиття або змінення типу даних, можна переглянути та змінити кожне перетворення в розділі ЗАСТОСОВАНІ КРОКИ області Настройки Запит.

Є багато перетворень, які можна зробити з інтерфейсу користувача. Кожне перетворення записується як крок у фоновому режимі. Ви навіть можете змінити та написати власні кроки, використовуючи мову Power Query M в Розширений редактор.

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

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

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

  • Проміжне додавання    Створює новий запит для кожної операції додавання.

  • Вбудоване додавання    Додайте дані до наявного запиту, доки не досягнете остаточного результату.

Концепція запитів на додавання

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

Концепція об'єднання запитів

Є два основні способи завантаження запитів до книги:

  • У Редактор Power Query на вкладці Основне в групі Закрити можна скористатися командами Закрити та Завантажити.

  • В області запити книги Excel (вибір запитів & підключення) можна клацнути правою кнопкою миші запит і вибрати команду Завантажити до.

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

Більше десяти років Power Query підтримується в Excel для Windows. Тепер Excel розширює підтримку Power Query на Excel для Mac і додає підтримку Excel для Інтернету. Це означає, що ми робимо Power Query доступними на трьох основних платформах і демонструємо популярність і функціональність Power Query серед Excel клієнтів. Слідкуйте за майбутніми оголошеннями про дорожню карту Microsoft 365та Нові можливості Excel для Microsoft 365.

Інтеграція Get & Transform Data (тепер називається Power Query) у Excel зазнала низки змін за ці роки. 

Excel 2010 і 2013 Windows

У Excel 2010 р. для Windows ми вперше представили Power Query і вона була доступна як безкоштовна надбудова, яку можна завантажити тут: Завантажте надбудову Power Query. Після ввімкнення Power Query функціональність була доступна на вкладці Power Query стрічки.

Стрічка Power Query Excel 2013

Microsoft 365

Ми оновили Power Query як основну роботу в Excel для імпорту та очищення даних. Отримати доступ до майстрів та засобів імпорту даних Power Query можна в групі Отримати & перетворення даних на вкладці Дані на стрічці Excel.

стрічка Excel 2016 Power Query

Ця функція включала розширені функції імпорту даних, перевпорядковані команди на вкладці Дані , нову область "Запити & підключення ", а також можливість ефективного формування даних за допомогою сортування, змінення типів даних, розділення стовпців, агрегації даних тощо.

Ця нова версія також замінила старі старі старі майстри імпорту даних під командою Дані в групі Отримати зовнішні дані . Однак доступ до них усе одно можна отримати в діалоговому вікні Параметри Excel (вибір параметрів > файлу > > Показати майстри імпорту застарілих даних).

Excel 2016 та Windows 2019 р.

Ми додали ту саму технологію Перетворення даних get & на основі технології Power Query, що й Microsoft 365.

Excel для Microsoft 365 для Mac

У 2019 році ми почали підтримку Power Query в Excel для Mac. Відтоді ми додали можливість оновлювати Power Query запити з файлів TXT, CSV, XLSX, JSON і XML. Також додано можливість оновлювати дані із сервера SQL та таблиць & діапазонів у поточній книзі.

У жовтні 2019 р. додано можливість оновлювати наявні Power Query запити та використовувати VBA для створення та редагування нових запитів.

У січні 2021 р. додано підтримку для оновлення запитів Power Query з OData та джерел SharePoint.

Докладні відомості див. в статті Використання Power Query в Excel для Mac.

Примітка    У Excel 2016 та Excel 2019 для Mac немає підтримки Power Query.

Data Catalog вилучення

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

  • 1 серпня 2018 року ми припинили приєднання нових клієнтів до Data Catalog.

  • 3 грудня 2018 року користувачам не вдалося надати спільний доступ до нових або оновлених запитів у Data Catalog.

  • 4 березня 2019 року Data Catalog припинила роботу. Після цієї дати ми рекомендували завантажити спільні запити, щоб продовжити використовувати їх поза Data Catalog, скориставшись параметром Відкрити в області завдань Мої запити Data Catalog.

Power Query вилучення надбудов

На початку літа 2019 року ми офіційно вилучили надбудову Power Query, необхідну для Excel 2010 і 2013 років для Windows. Як люб'язно надано, ви все одно можете використовувати надбудову, але вона може змінитися пізніше.

З'єднувач даних Facebook закрито

Імпорт і оновлення даних із Facebook у Excel припинили роботу в квітні 2020 року. Будь-які підключення Facebook, створені до цієї дати, більше не працюють. Радимо переглянути або видалити наявні запити Power Query, які використовують з'єднувач Facebook якомога швидше, щоб уникнути неочікуваних результатів.

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

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

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

Наскільки ви задоволені якістю мови?
Що вплинуло на ваші враження?

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

×