Давайте представим себе ситуацию, когда у нас есть таблица с информацией о задолженности клиентов в национальной валюте. Нашей задачей будет перевести суму этой задолженности в иностранную валюту (доллар США) по курсу на текущий день, сохранив при этом данные за предыдущие дни.

Исходные данные к этому заданию предоставлены в виде файла, что содержит в себе динамическую «умную» таблицу со списком должников предприятия и сумм их долга, которую мы заранее назвали «Задолженность»

Так как нам нужно переводить задолженность в доллары по курсу на текущую дату, давайте, создадим подключение к сайту НБУ. В новом файле Microsoft Excel (назовем его «Результат») на вкладке Данные выбираем Получить данные - Из других источников - Из Интернета.

В открывшемся окне указываем ссылку на справочное значение курса гривны к доллару США по состоянию на 12:00 нужной вам даты на сайте НБУ (https://bank.gov.ua/files/Kurs_dovid.xlsx) и нажимаем кнопку «ОК».

Дальше, в окне навигатора, выбираем нужный нам год и нажимаем кнопку «Преобразовать данные»

После нажатия на кнопку «Преобразовать данные» открывается редактор Power Query, в котором нам нужно будет немного отформатировать полученные данные:

1. Удаляем верхнюю строку (Главная - Сократить строки - Удалить строки - Удаление верхних строк – Количество строк «1» - ОК);
2. Задаем строку заголовков (Главная – в блоке «Преобразование» выбираем «Использовать первою строку в качестве заголовком»);
3. Заменяем значение «-» (такое значение указывает на то, что просматриваемая дата – выходной) на курс за предыдущий день. Для этого, для начала, нам нужно изменить тип данных во втором столбце на «Десятичное число». В строках, где вместо курса доллара указано «-» теперь будет отображаться ошибка - «error».

Создаем новый столбец с помощью команды Добавление столбца – Настраиваемый столбец – в открывшемся окне задаем название нового столбца (в нашем случае «Курс») и в поле формул напишем конструкцию try [выбираем столбец, которые содержит в себе курс валют] otherwise null. С помощью такой формулы все значение, которые раньше были «-» заменятся зарезервированным в языке М словом null, которое дальше мы заменим на курс за предыдущий день.

Дальше, предварительно выделив только что созданный столбец, на вкладке Преобразование – в блоке Любой столбец нажимаем на кнопку Заполнить – Вниз.

4. Удаляем ненужные столбцы и меняем название запроса (например, Курс_НБУ).

5. На вкладке Главная – выбираем «Закрыть и загрузить» - Закрыть и загрузить в… - Только создать подключение.

Теперь давайте, подключимся к нашему исходному файлу, который содержит в себе сведения о задолженности клиентов (Данные – Получить данные – Из файла – Из книги – и указываем путь к нашему файлу.

В открывшемся окне выбираем нашу «умную» таблицу – «Задолженность» и нажимаем на клавишу – «Преобразовать данные». Теперь в редакторе Power Query нам нужно будет добавить столбец, в котором будет отображаться текущая дата. Для этого в главном меню выбираем Добавление столбца – Настраиваемый столбец. В качестве названия столбца укажем «Дата», а в поле формул воспользуемся функцией DateTime.LocalNow, которая выводит значение текущей даты.

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

Теперь мы можем к нашему запросу «Задолженность» добавить курс доллара на текущую дату.  Чтобы объединить два запросы воспользуемся пунктом главного меню Главная – Объединить – Объединить запросы. В открывшемся окне указываем какой запрос будет добавлен к текущему (в нашем случае, таким запросом будет «Курс_НБУ). Дальше обозначаем общие для двух запросов столбцы (мы выбираем один столбец «Дата»). Так как запрос «Курс_НБУ» ссылается на внешний источник, а именно – сайт, у нас появиться окно «Уровни безопасности» - отмечаем поле «Пропустить проверку…» - Сохранить – и жмем ОК.

После выполнения вышеуказанных действий нам нужно выбрать, какой именно столбец из запроса «Курс_НБУ» нужно развернуть. Для этого жмем на кнопку «Развернуть» в правом углу названия появившегося столбца – выбираем нужный нам столбец – забираем отметку возле пункта «Использовать исходное имя столбца как префикс» - ОК.

Теперь можем разделить «Задолженость клиента, грн» на «Курс» и получить новый столбец - «Задолженость клиента, usd» и выгрузить полученную таблицу на лист Microsoft Excel (Главная – «Закрыть и загрузить» - Закрыть и загрузить в… - Имеющийся лист – ОК).

Дальше нам нужно создать ещё один запрос, который будет выводить на лист информацию, что была актуальной до обновления запроса (в нашем случае, такой информацией будут данные за предыдущие дни). Выделяем любую ячейку полученной нами таблицы после выгрузки запроса на лист Microsoft Excel и на вкладке Данные нажимаем на кнопку Из таблицы/диапазона (в новых версиях Microsoft Excel - С листа). В полученном запросе ничего не редактируем, только меняем название на «Пред_день» и сохраняем эго (Главная - Закрыть и загрузить - Закрыть и загрузить в... - Только создать подключение).

Теперь давайте, объединим оба запросы. Открываем запрос «Задолженность» на вкладке Главная выбираем Объединить – Добавить запросы и в открывшемся окне выбираем запрос «Пред_день» - ОК

Теперь можем возвращаться на лист Microsoft Excel (Главная - Закрыть и загрузить) и на следующий день попробовать обновить нашу таблицу (Данные – Обновить всё) проверив, подтянется ли новый курс доллара и получилось ли у нас сохранить данные за предыдущий период.

Больше о Power Query вы сможете узнать на нашем курсе "EXCEL ADVANCED: углубленные инструменты"

Узнайте больше про полную программу "EXCEL ADVANCED: углубленные инструменты"