Дізнайтеся, як використовувати Power Query та Power Pivot в Excel

Завдяки Power Query (перетворенню Get & в Excel) та Power Pivot в Excel ви будете витрачати менше часу на керування даними та більше часу на вплив на продуктивність. Для ознайомлення перегляньте це 2-хвилинне відео.

Спробуйте самі

Будь то невеликі набори даних або великі набори даних з мільйонами рядків, ви можете швидше підключатися, очищати, аналізувати та спільно використовувати дані за допомогою Power Query (перетворення get & Excel) і Power Pivot.

Запитання та відповіді

З Microsoft 365 для підприємств Excel на Windows для комп'ютерів пропонує повні функції Power Query і Power Pivot, які покращують можливості аналітики та моделювання, використовують розширені можливості підключення до даних і ефективно спільно використовують дані в Організації. Щоб отримати додаткові відомості про доступність Power Query та Power Pivot, див. у нижченаведених відомостях і дізнайтеся у ІТ-відділу, чи підтримує ваш поточний план Office ці функції.

Основні функції бізнес-аналітики

Базові функції аналітики доступні в будь-якій Microsoft 365 підписці або Office 2016. До таких функцій належать зведені таблиці, зрізи, діаграми та можливості моделі даних. З Power Query ви можете імпортувати, формувати та об'єднувати дані з файлів, баз даних та веб-сайтів. Підтримка базової моделі даних дозволяє завантажувати дані в модель даних, автоматично виявляти зв'язки, додавати групування часу та створювати заходи DAX.

Додаткові відомості про отримання найкращої продуктивності див. у розділі "Вибір між 64-розрядною або 32-розрядною версією Office.

Розширені функції бізнес-аналітики

Функції розширеної аналітики доступні в Microsoft 365 для підприємств, Office 2019 Professional, Office 2019 Professional Plus і Excel 2019. Крім базових функцій аналітики, ви можете використовувати Power Query для імпорту, формування та об'єднання даних з корпоративних, великих даних та хмарних джерел даних, а також для спільного використання запитів. Підтримка розширених моделей даних Power Pivot включає спеціальний редактор моделювання даних, представлення даних, обчислювані стовпці DAX, ключові показники ефективності та ієрархії.

Для отримання додаткових відомостей див. Power Query джерелахданих в Excel версіях і де знаходиться Power Pivot?

Excel 2016 для Mac та Excel 2019 для Mac мають безліч основних функцій аналізу, перерахованих вище: зведені таблиці, зрізи, діаграми та основні можливості імпорту. Вони ще не мають Power Query Power Pivot, але ми постійно покращуємо Excel mac. Excel для Microsoft 365 для Mac пропонує обмежену підтримку Power Query.

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

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

• Підключення Імпортуйте дані та встановіть підключення до даних у хмарі, службі або локально.
• Перетворити Формування даних відповідно до своїх потреб, у той час як вихідне джерело залишається незмінним.
• Об'єднати Подальше формування даних шляхом їх інтеграції з кількох джерел для отримання унікального представлення даних.
• Навантаження Виконайте запит і збережіть його на аркуші або моделі даних.

Для отримання додаткових відомостей див. Power Query Excel Power Query довідці Excel довідці.

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

Power BI — це набір інструментів бізнес-аналітики для надання детальної інформації про всю вашу організацію. За допомогою Power BI Desktop ви можете підключатися до сотень джерел даних, спрощувати підготовку даних та виконувати спеціальний аналіз. Ви також можете створювати звіти та публікувати їх в організації для використання в Інтернеті та на мобільних пристроях. Усі користувачі можуть створювати персональні інформаційні панелі з використанням всебічного представлення організації. Є вбудовані засоби управління та забезпечення безпеки.

Для отримання додаткових відомостей див. у Power BI.

Excel 2016 та Power BI Desktop надають унікальний для галузі набір інструментів. Разом вони дозволяють бізнес-аналітикам легко збирати, формувати, аналізувати та наочно вивчати дані. Power BI — це набір інструментів бізнес-аналітики для надання детальної інформації про всю вашу організацію.Він допомагає всім співробітникам організації об'єднувати дані з різних джерел: книг Excel, локальних корпоративних даних, служб Azure та інших популярних хмарних рішень, таких як Salesforce.com або Marketo, щоб переглядати та спільно використовувати інформаційні панелі та інтерактивні звіти.

Для отримання додаткових відомостей див. у розділі "Аналіз Excel для Power BI".

Для Microsoft 365 передплатників та Excel 2019 додано покращення функцій та функцій, які ви вже використовували.

Для отримання додаткових відомостей див. у наступних статтях:

Що таке Power Query?

Power Query – це модуль перетворення даних та підсистеми підготовки даних. Power Query поставляється з графічним інтерфейсом для отримання даних із джерел та Редактор Power Query для застосування перетворень. Оскільки підсистема доступна в багатьох продуктах та службах, місце зберігання даних залежить від того, де використовувався Power Query. За допомогою Power Query можна обробляти дані вилучення, перетворення та завантаження (ETL).

Схема із символічними джерелами даних зліва, передаючи Power Query для перетворення в центрі, а потім перейшовши в чотири призначення праворуч: Microsoft Azure Data Lake служба сховища, Microsoft Dataverse, Microsoft Excel та Microsoft Power BI.

Як Power Query допомагає з отриманням даних

Бізнес-користувачі витрачають до 80 відсотків часу на підготовку даних, що затримує роботу аналізу та прийняття рішень. Деякі проблеми сприяють цій ситуації, і Power Query допомагає вирішити багато з них.

Інтерфейси Power Query

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

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

При створенні нового кроку перетворення, взаємодіючи з компонентами інтерфейсу Power Query, Power Query автоматично створює код M, необхідний перетворення, щоб не потрібно писати код.

В даний час доступні два інтерфейси Power Query:

• Power Query Online — він знаходиться в інтеграції, таких як потоки даних Power BI, потоки даних Microsoft Power Platform, Фабрика даних Azure, переупорядкування потоків даних та багато іншого, що надає можливості через веб-сторінку.
• Power Query for Desktop — він знайдений в інтеграції, наприклад, Power Query для Excel і Power BI Desktop.

Хоча два інтерфейси Power Query існують, обидва вони забезпечують практично той самий інтерфейс користувача в кожному сценарії.

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

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

Всі ці перетворення стають можливими шляхом вибору параметра перетворення в меню, а потім застосування параметрів, необхідних для цього перетворення. На наступному малюнку показано деякі перетворення, доступні Редактор Power Query.

Потоки даних

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

Мова формул Power Query M

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

Мова M – це мова перетворення даних Power Query. Все, що відбувається в запиті, зрештою записується в M.Якщо ви хочете виконати розширені перетворення за допомогою підсистеми Power Query, ви можете використовувати Розширений редактор для доступу до скрипту запиту та зміни його за необхідності. Якщо ви виявите, що функції та перетворення інтерфейсу користувача не будуть виконувати точні зміни, використовуйте Розширений редактор і мову M для точного налаштування функцій і перетворень.

let Source = Exchange.Contents("[email protected]"), Mail1 = Source[Data], #"Expanded Sender" = Table.ExpandRecordColumn(Mail1, "Sender", , ), #"Filtered Rows" = Table. SelectRows(#"Expanded Sender", each ([HasAttachments] = true)), #"Filtered Rows1" = Table.SelectRows(#"Filtered Rows", each ([Subject] = "sample files for email PQ test") and ([Folder Path] = "\Inbox\")), #"Removed Other Columns" = Table.SelectColumns(#"Filtered Rows1",), #"Expanded Attachments" = Table.ExpandTableColumn(#"Removed Other Columns", "Attachments", , ), #"Filtered Hidden Files1" = Table.SelectRows(#"Expanded Attachments", each [Attributes]?[Hidden]? <> true), # "Invoke Custom Function1" = Table.AddColumn(#"Filtered Hidden Files1", "Transform File from Mail", each # "Transform File from Mail"([AttachmentContent])), #"Removed Other Columns1" = Table.SelectColumns(#"Invoke Custom Function1", ), #"Expanded Table Column1" = Table.ExpandTableColumn(#"Removed Other Columns1", "Transform File from Mail", Table.ColumnNames(#"Transform File from Mail"(#"Sample File"))), #"Changed Type" = Table.TransformColumnTypes(#"Expanded Table Column1",, , , , , , , , , >) in #"Changed Type"

Де можна використовувати Power Query?

У наступній таблиці перелічені продукти та служби Microsoft, де можна знайти Power Query.

Створення формул Power Query в Excel

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

Редактор Power Query надає для Excel можливість запиту та формування даних, яку можна використовуватиме зміни даних з багатьох джерел даних. Щоб відобразити вікно Редактор Power Query, імпортуйте дані із зовнішніх джерел даних на аркуші Excel, виділіть комірку даних, а потім виберіть Запит > Змінити. Нижче наведено зведення компонентів main.

1. Стрічка Редактор Power Query, що використовується для формування даних
2. Область "Запити", яка використовується для пошуку джерел даних та таблиць
3. Контекстні меню, зручні комбінації клавіш для команд на стрічці
4. Попередній перегляд даних, в якому відображаються результати кроків, які застосовуються до даних
5. Панель "Параметри запиту", в якій перелічені властивості та кожен крок запиту

У фоновому режимі кожен крок запиту ґрунтується на формулі, яка відображається у рядку формул.

Іноді потрібно змінити чи створити формулу. Формули використовують мову формул Power Query, яку можна використовувати для створення простих та складних виразів. Додаткові відомості про синтаксис, аргументи, примітки, функції та приклади див. у розділі мова формул Power Query M.

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

Послідовність дій

1. Щоб імпортувати дані, виберіть Дані >з Інтернету, введіть "http://en.wikipedia.org/wiki/UEFA_European_Football_Championship" у полі URL-адреса та натисніть кнопку ОК.
2. У діалоговому вікні Навігатор виберіть таблицю Результати [Змінити] ліворуч, а потім виберіть Перетворити дані унизу. Відкриється редактор Power Query.
3. Щоб змінити назву стандартного запиту, в області Параметри запиту у розділі Властивості видаліть "Результати [Змінити]" та введіть "UEFA champs".
4. Щоб видалити непотрібні стовпці, виділіть перший, четвертий та п'ятий стовпці, а потім виберіть Головна >Видалити стовпець >Видалити інші стовпці.
5. Щоб видалити небажані значення, виберіть Стовпець1, виберіть Головна >Замінити значення, введіть "details" у полі Значення для пошуку, а потім натисніть кнопку ОК.
6. Щоб видалити рядки зі словом "Рік", натисніть стрілку фільтра в стовпці Column1, очистіть поле перевірки поруч із полем "Рік", а потім натисніть кнопку ОК.
7. Щоб перейменувати заголовки стовпців, двічі клацніть кожен з них, а потім змініть "Column1" на "Year", "Column4" на "Winner" та "Column5" на "Final Score".
8. Щоб зберегти запит, виберіть Головна >Закрити & завантажити.

Result (Результат)

У наступній таблиці наведено зведення по кожному застосованому кроку і відповідна формула.

Крок та завдання запиту

Підключення до веб-джерела даних

Зміна типів даних (що Power Query виконується автоматично)

Вилучено інші стовпці

Видалення непотрібних стовпців

Замінене значення

Заміна значень для очищення значень у вибраному стовпці

= Table.ReplaceValue(#"Removed Other Columns","Details","",Replacer.ReplaceText,)

Відфільтровані рядки

Фільтрування значень у стовпці

= Table.SelectRows(#"Replaced Value", each ([Column1] <> "Year"))

Перейменовані стовпці

Змінено заголовки стовпців, щоб вони були значущими

Важливо Уважно змінюйте кроки Джерело, Навігація і Змінений тип, оскільки вони створюються Power Query для визначення та налаштування джерела даних.

Відображення або приховування рядка формул

Рядок формул відображається за замовчуванням, але якщо її не видно, її можна переграти.

Edit формула в рядку формул

1. Щоб відкрити запит, знайдіть раніше завантажений з Редактор Power Query, виберіть комірку даних, а потім виберіть Запит >Змінити. Щоб отримати додаткові відомості, див. Створення, завантаження та зміна запиту в Excel.
2. В області Параметри запиту у розділі Застосовані кроки виберіть крок, який потрібно змінити.
3. У рядку формул знайдіть та змініть значення параметрів, а потім клацніть значок ВВЕДЕННЯ

або натисніть клавішу ENTER. Наприклад, змініть цю формулу, щоб зберегти стовпець2:

Створення формули у рядку формул

Для простого прикладу формули давайте перетворимо текстове значення на правильний регістр за допомогою функції Text.Proper.

1. Щоб відкрити порожній запит, в Excel виберіть Дані >Отримати дані >з інших джерел >порожній запит. Для отримання додаткових відомостей див. у статті Створення, завантаження та зміна запиту в Excel.
2. У рядку формул введіть =Text.Proper("text value") , а потім натисніть значок ВВЕДЕННЯ

або натисніть клавішу ENTER.

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

Важливо Уважно змінюйте кроки Джерело, Навігація і Змінений тип, оскільки вони створюються Power Query для визначення та налаштування джерела даних.

Зміна формули за допомогою діалогового вікна

Цей метод використовує діалогові вікна, які залежить від кроку. Вам не потрібно знати синтаксису формули.

1. Щоб відкрити запит, знайдіть раніше завантажений з Редактор Power Query, виберіть комірку даних, а потім виберіть Запит >Змінити. Для отримання додаткових відомостей див. у статті Створення, завантаження та зміна запиту в Excel.
2. В області Параметри запиту у розділі Застосовані кроки виберіть піктограму Змінити параметри

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

1. В області Параметри запиту у розділі Застосовані кроки виберіть крок, який потрібно відразу перед новим кроком та відповідною формулою.
2. Клацніть піктограму Додати крок

ліворуч від рядка формул. Крім того, клацніть крок правою кнопкою миші та виберіть команду Вставити крок після. Створюється нова формула у форматі:

Наприклад, припустимо, що у вас є таблиця зі стовпцем Gender і ви хочете додати стовпець зі значенням "Ms". або "Mr.", Залежно від статі людини. Формула буде:

Зміна порядку кроку

• В області Параметри запитів у розділі Застосовані кроки клацніть правою кнопкою миші крок і виберіть Перемістити вгору або вниз.

Клацніть піктограму Видалити

ліворуч від кроку або клацніть правою кнопкою миші, а потім виберіть Видалити або Видалити до кінця. Значок Видалити

У цьому прикладі давайте перетворимо текст у стовпці на правильний регістр, використовуючи поєднання формул в Розширений редактор.

Наприклад, у вас є таблиця Excel з іменем Orders зі стовпцем ProductName, який необхідно перетворити на правильний регістр.

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

let
Source = Text.Proper("hello world")
in
Source

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

Етап 1. Відкриття Розширений редактор

1. В Excel виберіть Дані >Отримати дані >інші джерела >порожній запитДодаткові відомості див. у статті Створення, завантаження та зміна запиту в Excel.
2. У Редактор Power Query виберіть Головна >Розширений редактор який відкриється з шаблоном виразу let.

Етап 2. Визначення джерела даних

Створіть вираз let за допомогою функції Excel.CurrentWorkbook наступним чином:

let
Source = Excel.CurrentWorkbook()[Content] #x2

Етап 3. Підвищення рівня першого рядка до заголовків

1. Щоб відкрити запит, на аркуші виберіть комірку даних, а потім виберіть Запит >ЗмінитиДодаткові відомості див. у статті Створення, завантаження та зміна запиту в Excel (Power Query).
2. У Редактор Power Query виберіть Головна >Розширений редактор який відкриється з інструкцією, створеною в розділ Етап 2. Визначення джерела даних.
3. У виразі let додайте #"Перший рядок як заголовок" і функцію Table.PromoteHeaders наступним чином:

Етап 4. Зміна кожного значення у стовпці у правильному регістрі

1. Щоб відкрити запит, на аркуші виберіть комірку даних, а потім виберіть Запит >ЗмінитиДодаткові відомості див. у статті Створення, завантаження та зміна запиту в Excel.
2. У Редактор Power Query виберіть Головна >Розширений редактор який відкриється з інструкцією, створеною на етап 3. Підвищення рівня першого рядка до заголовків.
3. У виразі let перетворіть кожне значення стовпця ProductName у правильний текст за допомогою функції Table.TransformColumns, посилаючись на попередній крок формули запиту "Перший рядок як заголовок", додавши #"Великі літери Кожен Word" в джерело даних, а потім призначивши результату # "Великі букви кожен Word".