Создание формул Power Query в Excel

Примечание :  Надстройка Power Query предлагается в Excel 2016 в виде группы команд Скачать и преобразовать. Информация в этой статье относится и к Power Query, и к новой группе. Дополнительные сведения см. в статье Функция "Скачать и преобразовать" в Excel 2016.

Для создания формул Power Query в Excel можно использовать строку формул редактора запросов или расширенный редактор. Редактор запросов — это средство, включенное в Power Query, в котором можно создавать запросы к данным и формулы. Для создания этих формул используется язык формул Power Query. Существует множество формул Power Query, с помощью которых можно обнаруживать, объединять и уточнять данные. Подробные сведения обо всех видах формул Power Query см. в статье Категории формул Power Query.

Давайте создадим сначала простую формулу, а затем сложную.

Создание простой формулы

В этом примере мы с помощью формулы Text.Proper() преобразуем текстовое значение, сделав первые буквы слов прописными.

  1. На вкладке ленты POWER QUERY нажмите кнопку Из других источников и выберите пункт Пустой запрос.

    Лента Power Query
  2. В строке формул редактора запросов введите = Text.Proper("text value"), а затем нажмите клавишу ВВОД или щелкните значок "Ввод". Редактор формул

  3. Power Query отобразит результаты в области результатов формул.

  4. Чтобы увидеть результат на листе Excel, нажмите кнопку Закрыть и загрузить.

Результат будет выглядеть на листе вот так:

Текстовое значение

В редакторе запросов можно также создавать сложные формулы.

Создание сложной формулы

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

Примечание : В этой статье приведены базовые сведения о сложных формулах Power Query. Подробные сведения о формулах Power Query см. в статье Формулы Power Query.

Предположим, что есть таблица Excel с наименованиями товаров, в которых нужно сделать первые буквы слов прописными.

Исходная таблица выглядит так:

Before

Результирующая таблица должна иметь такой вид:

After

Рассмотрим шаги формулы запроса, с помощью которых можно изменить исходную таблицу и сделать первые буквы слов в столбце ProductName прописными.

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

Чтобы очистить исходную таблицу, создайте шаги формулы запроса с помощью расширенного редактора. Создадим каждый шаг формулы запроса, чтобы показать, как можно построить сложный запрос. Все шаги формулы запроса перечислены ниже. При создании сложного запроса сделайте следующее:

  • Создайте последовательность шагов формулы запроса, которая начинается с оператора let. Обратите внимание: в языке формул Power Query учитывается регистр.

  • Каждый шаг формулы запроса строится на основе одного из предыдущих шагов (ссылается на него по имени).

  • Результат шага формулы запроса выводится с помощью оператора in. Обычно последний шаг запроса используется как окончательный результат оператора in для набора данных.

Шаг 1. Открытие расширенного редактора

  1. На вкладке ленты POWER QUERY нажмите кнопку Из других источников и выберите пункт Пустой запрос.

  2. В редакторе запросов выберите команду Расширенный редактор.

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

  3. Откроется окно Расширенный редактор.

    Расширенный редактор 2

Шаг 2. Определение исходного источника

В расширенном редакторе сделайте следующее:

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

  2. Назначьте имя шага Источник результату оператора in.

    let Source =
    Excel.CurrentWorkbook(){[Name="Orders"]}[Content]
    in Source
  3. Сложный запрос будет выглядеть в расширенном редакторе следующим образом:

    Расширенный редактор 3
  4. Вот как можно просмотреть результаты на листе:

    1. Нажмите кнопку Готово.

    2. На ленте редактора запросов нажмите кнопку Закрыть и загрузить.

Шаг 1. Расширенный редактор

На листе результат будет выглядеть вот так:

Шаг 1. Результат

Шаг 3. Преобразование первой строки в заголовки

Чтобы преобразовать значения в столбце ProductName в текст, где первые буквы слов прописные, сначала нужно преобразовать первую строку, сделав ее значения заголовками столбцов. Для этого воспользуйтесь расширенным редактором.

  1. Добавьте к шагам формулы запроса формулу #"Использовать первую строку в качестве заголовков" = Table.PromoteHeaders() и сошлитесь на "Источник" как на источник данных. Дополнительные сведения о формуле Table.PromoteHeaders() см. в статье Table.PromoteHeaders.

  2. Назначьте имя шага #"Использовать первую строку в качестве заголовков" результату оператора 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() и сошлитесь на шаг формулы запроса "Использовать первую строку в качестве заголовков". Для этого воспользуйтесь расширенным редактором.

  1. Добавьте к шагам формулы запроса формулу #"Выполнена капитализация каждого слова" = Table.TransformColumns() и сошлитесь на шаг #"Использовать первую строку в качестве заголовков" как на источник данных. Дополнительные сведения о формуле Table.TransformColumns() см. в статье Table.TransformColumns.

  2. Назначьте имя шага #"Выполнена капитализация каждого слова" результату оператора 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.

Примечания : 

  • Отказ от ответственности относительно машинного перевода. Данная статья была переведена с помощью компьютерной системы без участия человека. Microsoft предлагает эти машинные переводы, чтобы помочь пользователям, которые не знают английского языка, ознакомиться с материалами о продуктах, услугах и технологиях Microsoft. Поскольку статья была переведена с использованием машинного перевода, она может содержать лексические,синтаксические и грамматические ошибки.

  • Используйте английский вариант этой статьи, который находится здесь, в качестве справочного материала. Вместе с другими участниками сообщества Майкрософт вы можете улучшить эту статью с помощью Community Translation Framework (CTF). Просто наведите указатель мыши на предложение в статье и выберите команду "УЛУЧШИТЬ ПЕРЕВОД" в мини-приложении CTF. Для получения дополнительных сведений о CTF щелкните здесь. Используя CTF, вы соглашаетесь с нашими условиями предоставления услуг.

Совершенствование навыков
Перейти к обучению
Первоочередный доступ к новым возможностям
Присоединиться к программе предварительной оценки Office

Были ли сведения полезными?

Спасибо за ваш отзыв!

Благодарим за отзыв! Возможно, будет полезно связать вас с одним из наших специалистов службы поддержки Office.

×