Перейти до основного
Підтримка
Вхід

Надбудова Power Query в програмі Excel

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

Надбудова Power Query містить чотири етапи.

Power Query common steps

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

  2. Перетворення    Shape data to meet your needs, while the original source remains unchanged

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

Завантажити запити до книги можна двома основними способами:

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

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

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

Інтеграція програми 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.

Стрічка надбудови Power Query в програмі Excel 2016

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

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

Excel 2016 і 2019 для Windows

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

Excel для Microsoft 365 для Mac

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

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

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

Докладні відомості див. в цій програмі.

Примітка    Надбудова Power Query не підтримується в програмах Excel 2016 і Excel 2019 для Mac.

Вилучення каталогу даних

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

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

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

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

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

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

Виключення з'єднатора даних Facebook

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

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

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

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

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

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

×