зміст
Якщо ви вже почали користуватися інструментами безкоштовної надбудови Power Query в Microsoft Excel, то зовсім скоро ви зіткнетеся з однією вузькоспеціалізованої, але дуже частою і неприємною проблемою, пов'язаною з постійним розривом посилань на вихідні дані. Суть проблеми полягає в тому, що якщо у вашому запиті ви посилаєтеся на зовнішні файли або папки, то Power Query жорстко кодує абсолютний шлях до них у тексті запиту. На вашому комп'ютері все працює нормально, але якщо ви вирішите відправити файл із запитом своїм колегам, то вони будуть розчаровані, т.к. вони мають інший шлях до вихідних даних на своєму комп’ютері, і наш запит не працюватиме.
Що робити в такій ситуації? Розглянемо цей випадок докладніше на наступному прикладі.
Постановка проблеми
Припустимо, у нас є в папці E:Звіти про продажі лежить файл 100 найкращих продуктів.xls, який є завантаженням з нашої корпоративної бази даних або ERP-системи (1С, SAP та ін.) Цей файл містить інформацію про найпопулярніші товарні позиції і виглядає всередині так:
Напевно, відразу зрозуміло, що працювати з ним в Excel в такому вигляді практично неможливо: заважатимуть порожні рядки до одного з даними, об'єднані комірки, зайві стовпці, багаторівневий заголовок і т.д.
Тому поруч із цим файлом у тій же папці створюємо ще один новий файл Handler.xlsx, у якому ми створимо запит Power Query, який завантажуватиме погані дані з вихідного файлу завантаження 100 найкращих продуктів.xls, і розставте їх по порядку:
Створення запиту до зовнішнього файлу
Відкриття файлу Handler.xlsx, виберіть на вкладці дані Command Отримати дані – з файлу – із книги Excel (Дані — Отримати дані — З файлу — З Excel), далі вказуємо місце розташування вихідного файлу і потрібного нам листа. Вибрані дані будуть завантажені в редактор Power Query:
Повернемо їх до нормального стану:
- Видалити порожні рядки за допомогою Головна — Видалити рядки — Видалити порожні рядки (Домашня сторінка — Видалити рядки — Видалити порожні рядки).
- Видаліть непотрібні 4 верхні рядки Головна — Видалити рядки — Видалити верхні рядки (Домашня сторінка — Видалити рядки — Видалити верхні рядки).
- Підніміть перший рядок до шапки таблиці кнопкою Використовуйте перший рядок як заголовки таб Головна (Домашня сторінка — використовувати перший рядок як заголовок).
- Відокремте п’ятизначний артикул від назви продукту у другому стовпці за допомогою команди розділити колонку таб Перетворення (Перетворення — розділити стовпець).
- Видаліть непотрібні стовпці та перейменуйте заголовки тих, що залишилися, для кращої видимості.
В результаті ми повинні отримати наступну, набагато більш приємну картинку:
Залишилося завантажити цю облагороджену таблицю назад на аркуш нашого файлу Handler.xlsx команда закрити і завантажити (Додому — Close&Load) таб Головна:
Пошук шляху до файлу в запиті
Тепер давайте подивимося, як наш запит виглядає «під капотом», на внутрішній мові, вбудованій у Power Query з короткою назвою «M». Для цього поверніться до нашого запиту, двічі клацнувши його на правій панелі Запити та підключення і на вкладці Розгляд вибирати Розширений редактор (Перегляд — розширений редактор):
У вікні, що відкриється, у другому рядку одразу відображається жорстко закодований шлях до нашого вихідного файлу завантаження. Якщо ми зможемо замінити цей текстовий рядок параметром, змінною або посиланням на комірку аркуша Excel, де цей шлях уже прописаний, то ми зможемо легко змінити його пізніше.
Додайте розумну таблицю з шляхом до файлу
Давайте зараз закриємо Power Query і повернемося до нашого файлу Handler.xlsx. Додамо новий порожній аркуш і створимо на ньому маленьку «розумну» таблицю, в єдиній комірці якої буде прописаний повний шлях до нашого вихідного файлу даних:
Щоб створити розумну таблицю зі звичайного діапазону, ви можете скористатися комбінацією клавіш Ctrl+T або кнопку Відформатувати як таблицю таб Головна (Домашня сторінка — форматувати як таблицю). Заголовок стовпця (комірка A1) може бути абсолютно будь-яким. Також зауважте, що для ясності я дав назву таблиці параметри таб Конструктор (Дизайн).
Скопіювати шлях з Провідника або навіть ввести його вручну, звичайно, не становить особливої складності, але краще мінімізувати людський фактор і визначати шлях, по можливості, автоматично. Це можна реалізувати за допомогою стандартної функції аркуша Excel КЛЕТКА (КЛІТИНКА), який може видати купу корисної інформації про комірку, вказану як аргумент, включаючи шлях до поточного файлу:
Якщо припустити, що вихідний файл даних завжди знаходиться в тій же папці, що і наш Процесор, то потрібний нам шлях можна сформувати за такою формулою:
=LEFT(CELL(“ім’я файлу”);FIND(“[“;CELL(“ім’я файлу”))-1)&”100 найкращих продуктів.xls”
або англійською мовою:
=LEFT(CELL(«назва файлу»);FIND(«[«;CELL(«назва файлу»))-1)&»Топ-100 товарів.xls»
… де знаходиться функція ЛЕВСІМВ (ВЛІВО) бере фрагмент тексту від повного посилання до початкової квадратної дужки (тобто шлях до поточної папки), а потім до нього приклеюється ім’я та розширення нашого вихідного файлу даних.
Параметризуйте шлях у запиті
Залишився останній і найважливіший штрих – прописати в запиті шлях до вихідного файлу 100 найкращих продуктів.xls, посилаючись на клітинку A2 нашої створеної «розумної» таблиці параметри.
Для цього повернемося до запиту Power Query і відкриємо його знову Розширений редактор таб Розгляд (Перегляд — розширений редактор). Замість тексту рядок-шлях у лапках “E:Sales reports Top 100 products.xlsx” Введемо таку структуру:
Excel.CurrentWorkbook(){[Name=”Налаштування”]}[Вміст]0 {}[Шлях до вихідних даних]
Давайте подивимося, з чого він складається:
- Excel.CurrentWorkbook() є функцією мови M для доступу до вмісту поточного файлу
- {[Name=”Налаштування”]}[Вміст] – це параметр уточнення попередньої функції, який вказує на те, що ми хочемо отримати вміст «розумної» таблиці параметри
- [Шлях до вихідних даних] це назва стовпця в таблиці параметрина які ми посилаємося
- 0 {} це номер рядка в таблиці параметриз якого ми хочемо взяти дані. Шапка не зараховується і нумерація починається з нуля, а не з одиниці.
Ось, власне, і все.
Залишилося натиснути на обробка і перевірте, як працює наш запит. Тепер при надсиланні всієї папки з обома файлами на інший ПК запит залишатиметься робочим і автоматично визначатиме шлях до даних.
- Що таке Power Query і навіщо він потрібен при роботі в Microsoft Excel
- Як імпортувати фрагмент плаваючого тексту в Power Query
- Перепроектування перехресної таблиці XNUMXD на плоску таблицю за допомогою Power Query