Примечание: Эта статья выполнила свою задачу, и в скором времени ее использование будет прекращено. Чтобы избежать появления ошибок "Страница не найдена", мы удаляем известные нам ссылки. Если вы создали ссылки на эту страницу, удалите их, и вместе мы обеспечим согласованность данных в Интернете.
Примечание: Надстройка Power Query предлагается в Excel 2016 в виде группы команд Скачать и преобразовать. Информация в этой статье относится и к Power Query, и к новой группе. Дополнительные сведения см. в статье Функция "Скачать и преобразовать" в Excel 2016.
Для создания формул Power Query в Excel можно использовать строку формул редактора запросов или расширенный редактор. Редактор запросов — это средство, включенное в Power Query, в котором можно создавать запросы к данным и формулы. Для создания этих формул используется язык формул Power Query. Существует множество формул Power Query, с помощью которых можно обнаруживать, объединять и уточнять данные. Подробные сведения обо всех видах формул Power Query см. в статье Категории формул Power Query.
Давайте создадим сначала простую формулу, а затем сложную.
Создание простой формулы
В этом примере мы с помощью формулы Text.Proper() преобразуем текстовое значение, сделав первые буквы слов прописными.
-
На вкладке ленты POWER QUERY нажмите кнопку Из других источников и выберите пункт Пустой запрос.
-
В строке формул редактора запросов введите = Text.Proper("text value"), а затем нажмите клавишу ВВОД или щелкните значок "Ввод".
-
Power Query отобразит результаты в области результатов формул.
-
Чтобы увидеть результат на листе Excel, нажмите кнопку Закрыть и загрузить.
Результат будет выглядеть на листе вот так:
В редакторе запросов можно также создавать сложные формулы.
Создание сложной формулы
В этом примере мы с помощью комбинации формул преобразуем текст в столбце, сделав первые буквы слов прописными. Можно воспользоваться языком формул Power Query, чтобы объединить несколько формул в шаги запроса для получения результирующего набора данных. Результат можно импортировать на лист Excel.
Примечание: В этой статье приведены базовые сведения о сложных формулах Power Query. Подробные сведения о формулах Power Query см. в статье Формулы Power Query.
Предположим, что есть таблица Excel с наименованиями товаров, в которых нужно сделать первые буквы слов прописными.
Исходная таблица выглядит так:
Результирующая таблица должна иметь такой вид:
Рассмотрим шаги формулы запроса, с помощью которых можно изменить исходную таблицу и сделать первые буквы слов в столбце ProductName прописными.
Пример составления сложного запроса с помощью расширенного редактора
Чтобы очистить исходную таблицу, создайте шаги формулы запроса с помощью расширенного редактора. Создадим каждый шаг формулы запроса, чтобы показать, как можно построить сложный запрос. Все шаги формулы запроса перечислены ниже. При создании сложного запроса сделайте следующее:
-
Создайте последовательность шагов формулы запроса, которая начинается с оператора let. Обратите внимание: в языке формул Power Query учитывается регистр.
-
Каждый шаг формулы запроса строится на основе одного из предыдущих шагов (ссылается на него по имени).
-
Результат шага формулы запроса выводится с помощью оператора in. Обычно последний шаг запроса используется как окончательный результат оператора in для набора данных.
Шаг 1. Открытие расширенного редактора
-
На вкладке ленты POWER QUERY нажмите кнопку Из других источников и выберите пункт Пустой запрос.
-
В редакторе запросов выберите команду Расширенный редактор.
-
Откроется окно Расширенный редактор.
Шаг 2. Определение исходного источника
В расширенном редакторе сделайте следующее:
-
Используйте оператор let, определяющий формулу Источник = Excel.CurrentWorkbook(). В результате источником данных станет таблица Excel. Дополнительные сведения о формуле Excel.CurrentWorkbook() см. в статье Excel.CurrentWorkbook.
-
Назначьте имя шага Источник результату оператора in.
let Source = Excel.CurrentWorkbook(){[Name="Orders"]}[Content] in Source
-
Сложный запрос будет выглядеть в расширенном редакторе следующим образом:
-
Вот как можно просмотреть результаты на листе:
-
Нажмите кнопку Готово.
-
На ленте редактора запросов нажмите кнопку Закрыть и загрузить.
-
На листе результат будет выглядеть вот так:
Шаг 3. Преобразование первой строки в заголовки
Чтобы преобразовать значения в столбце ProductName в текст, где первые буквы слов прописные, сначала нужно преобразовать первую строку, сделав ее значения заголовками столбцов. Для этого воспользуйтесь расширенным редактором.
-
Добавьте к шагам формулы запроса формулу #"Использовать первую строку в качестве заголовков" = Table.PromoteHeaders() и сошлитесь на "Источник" как на источник данных. Дополнительные сведения о формуле Table.PromoteHeaders() см. в статье Table.PromoteHeaders.
-
Назначьте имя шага #"Использовать первую строку в качестве заголовков" результату оператора in.
let Source = Excel.CurrentWorkbook(){[Name="Orders"]}[Content], #"First Row as Header" = Table.PromoteHeaders(Source) in #"First Row as Header"
На листе результат будет выглядеть вот так:
Шаг 4. Изменение каждого значения в столбце (замена первых букв в словах прописными)
Чтобы преобразовать каждое значение столбца ProductName, сделав первые буквы слов прописными, воспользуйтесь формулой Table.TransformColumns() и сошлитесь на шаг формулы запроса "Использовать первую строку в качестве заголовков". Для этого воспользуйтесь расширенным редактором.
-
Добавьте к шагам формулы запроса формулу #"Выполнена капитализация каждого слова" = Table.TransformColumns() и сошлитесь на шаг #"Использовать первую строку в качестве заголовков" как на источник данных. Дополнительные сведения о формуле Table.TransformColumns() см. в статье Table.TransformColumns.
-
Назначьте имя шага #"Выполнена капитализация каждого слова" результату оператора 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 будет изменено (первые буквы слов станут прописными). На листе это будет выглядеть так:
С помощью языка формул Power Query можно создавать простые и сложные запросы для обнаружения, объединения и уточнения данных. Дополнительные сведения о Power Query см. в статье Справка по Microsoft Power Query для Excel.