Уявімо собі ситуацію, коли у нас є таблиця з інформацією про заборгованість клієнтів у національній валюті. Нашим завданням буде перевести суму цієї заборгованості в іноземну валюту (долар США) за курсом на поточний день, зберігши дані за попередні дні.

Вихідні дані до цього завдання надані у вигляді файлу, що містить динамічну «розумну» таблицю зі списком боржників підприємства та сум їх боргу, яку ми заздалегідь назвали «Заборгованість»

Оскільки нам потрібно переводити заборгованість у долари за курсом на поточну дату, створімо підключення до сайту НБУ. У новому файлі 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: поглиблені інструменти"