Applies ToExcel для Microsoft 365 Excel 2024 Excel 2021 Excel 2019 Excel 2016

Примечание: Эта статья выполнила свою задачу, и в скором времени ее использование будет прекращено. Чтобы избежать появления ошибок "Страница не найдена", мы удаляем известные нам ссылки. Если вы создали ссылки на эту страницу, удалите их, и вместе мы обеспечим согласованность данных в Интернете.

Примечание:  Надстройка 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 с наименованиями товаров, в которых нужно сделать первые буквы слов прописными.

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

Перед

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

После

Рассмотрим шаги формулы запроса, с помощью которых можно изменить исходную таблицу и сделать первые буквы слов в столбце 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.

Нужна дополнительная помощь?

Нужны дополнительные параметры?

Изучите преимущества подписки, просмотрите учебные курсы, узнайте, как защитить свое устройство и т. д.

В сообществах можно задавать вопросы и отвечать на них, отправлять отзывы и консультироваться с экспертами разных профилей.