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

Створення формул 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. Скористайтеся заявою, що призначає вихідну формулу = Excel. "Currenentкнизі" (). Це використовуватиметься як джерело даних у таблиці Excel. Докладні відомості про формулу Excel. Currenpкнига () наведено в статті Excel. currenкниги.

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

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

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

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

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

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

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

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

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

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

  1. Додайте формулу # "перший рядок як заголовок" = Table. Prototeheads () до кроків формули запиту та посилання на джерело як джерело даних. Докладні відомості про таблицю. Prototeheaders () наведено в статті таблиця. prototeзаголовки.

  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. Додавання формули # "капіталізовані кожне слово" = Table. TransformColumns () до кроків формули запиту та посилання на елемент # "перший рядок як заголовок" як джерело даних. Докладні відомості про формулу. TransformColumns () наведено в статті таблиця. 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

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

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

×