Створення формул Power Query в програмі Excel

Примітка.:  Надбудова Power Query відома як набір функцій Завантажити та перетворити в програмі Excel 2016. Викладена тут інформація стосується обох компонентів. Докладні відомості див. в статті Функція "Завантажити та перетворити" в Excel 2016.

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

Створімо спочатку просту формулу, а потім – розширену.

Створення простої формули

Для прикладу простої формули перетворімо текстове значення на текст, у якому всі слова починаються з великої букви, використовуючи формулу Text.Proper() .

  1. Виберіть на стрічці на вкладку POWER QUERY, натисніть кнопку З інших джерел і виберіть пункт Пустий запит.

    Стрічка надбудови Power Query
  2. У рядку формул редактора запитів введіть = Text.Proper("text value") і натисніть клавішу Enter або виберіть піктограму "Ввести". Довгий формат дати

  3. У Power Query в області результатів формул відобразяться результати.

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

На аркуші результат матиме такий вигляд:

Вибір анімації, яку потрібно ініціювати

У редакторі запитів можна також створювати розширені формули запитів.

Створення розширеної формули

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

Примітка.: У цьому розділі наведено основні відомості про розширені формули Power Query. Докладні відомості про формули Power Query див. в статті Відомості про формули Power Query.

Припустімо, наприклад, що таблицю Excel із назвами продуктів потрібно перетворити на таблицю, у якій усі слова починаються з великої букви.

Вихідна таблиця має такий вигляд:

Блок-схема зі сполучними лініями з червоними кінцевими точками.

Потрібно, щоб вона мала такий вигляд:

Після

Створімо кроки-формули запиту, щоб змінити значення в стовпці ProductName вихідної таблиці на значення, у яких усі слова починаються з великої букви.

Приклад створення розширеного запиту за допомогою розширеного редактора

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

  • Створити послідовність кроків-формул запиту, які починаються з інструкції let. Зверніть увагу, що в мові формул Power Query враховується регістр.

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

  • Вивести результат кроку-формули запиту, використовуючи інструкцію in. Зазвичай результат виконання останнього кроку запиту використовується як остаточний набір даних.

Крок 1. Відкриття розширеного редактора

  1. Виберіть на стрічці на вкладку POWER QUERY, натисніть кнопку З інших джерел і виберіть пункт Пустий запит.

  2. У редакторі запитів натисніть кнопку Розширений редактор.

    Розширений редактор

  3. Відкриється розширений редактор.

    Звіт про працівників у режимі попереднього перегляду

Крок 2. Визначення вихідного джерела

У розширеному редакторі:

  1. Використайте інструкцію let, щоб призначити формулу Source = Excel.CurrentWorkbook(). Тепер як джерело даних використовуватиметься таблиця Excel. Докладні відомості про формулу Excel.CurrentWorkbook() див. в статті Excel.CurrentWorkbook.

  2. Призначте Source результату інструкції in.

    let Source =
    Excel.CurrentWorkbook(){[Name="Orders"]}[Content]
    in Source
  3. Розширений запит у розширеному редакторі виглядатиме так:

    Розширений редактор 3
  4. Щоб переглянути результати на аркуші:

    1. Натисніть кнопку Готово.

    2. На стрічці редактора запитів натисніть кнопку Закрити й завантажити.

Діалогове вікно «Масштаб»

Результат на аркуші виглядатиме так:

Математичний символ

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

Щоб перетворити значення в стовпці ProductName на текст, у якому всі слова починаються з великої букви, спочатку потрібно зробити перший рядок заголовками стовпців. Для цього потрібно скористатися розширеним редактором:

  1. Додайте формулу #"First Row as Header" = Table.PromoteHeaders() до кроків-формул у запиті, використавши Source як посилання на джерело даних. Докладні відомості про формулу Table.PromoteHeaders() див. в статті Table.PromoteHeaders.

  2. Призначте #"First Row as Header" результату інструкції in.

    let
        Source = Excel.CurrentWorkbook(){[Name="Orders"]}[Content],
        #"First Row as Header" = Table.PromoteHeaders(Source)
    in
        #"First Row as Header"

Результат на аркуші виглядатиме так:

Крок 3 – результат

Крок 4. Перетворення всіх слів у кожному значенні на слова з великої букви

Щоб перетворити кожне значення в стовпці ProductName на текст, у якому всі слова починаються з великої букви, використайте функцію Table.TransformColumns() і пошліться на крок-формулу запиту "First Row as Header". Для цього потрібно скористатися розширеним редактором:

  1. Додайте формулу #"Capitalized Each Word" = Table.TransformColumns() до кроків-формул запиту, використавши #"First Row as Header" як посилання на джерело даних. Докладні відомості про формулу Table.TransformColumns() див. в статті Table.TransformColumns.

  2. Призначте #"Capitalized Each Word" результату інструкції in.

let
    Source = Excel.CurrentWorkbook(){[Name="Orders"]}[Content],
    #"First Row as Header" = Table.PromoteHeaders(Source),
    #"Capitalized Each Word" = Table.TransformColumns(#"First Row as Header",{{"ProductName", Text.Proper}})
in
    #"Capitalized Each Word"

У результаті кожне значення в стовпці ProductName буде змінено так, щоб усі слова в ньому починалися з великої букви. На аркуші це виглядатиме так:

Крок 4 – результат

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

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

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

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

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

×