27432 09.12.2018 Скачать пример

Если вы уже начали использовать в работе инструменты бесплатной надстройки Power Query в Microsoft Excel, то очень скоро столкнётесь с одной узкоспециальной, но весьма частой и надоедливой проблемой, связанной с постоянно ломающимися ссылками на исходные данные. Суть проблемы в том, что если в своём запросе вы ссылаетесь на внешние файлы или папки, то Power Query жёстко прописывает абсолютный путь к ним в тексте запроса. У вас на компьютере всё работает прекрасно, но если вы решите отправить файл с запросом своим коллегам, то их ждёт разочарование, т.к. у них на компьютере путь к исходным данным уже другой, и наш запрос работать не будет.

Что же сделать в такой ситуации? Давайте рассмотрим этот случай подробнее на следующем примере.

Постановка задачи

Предположим, что у нас в папке E:\Отчеты по продажам лежит файл Топ-100 товаров.xls, представляющий собой выгрузку из нашей корпоративной базы данных или ERP-системы (1С, SAP и т.п.) Этот файл содержит информацию о наиболее популярных товарных позициях и выглядит внутри примерно так:

С ходу, наверное, понятно, что работать с ним в Excel в таком виде практически невозможно: будут мешать пустые строки через одну с данными, объединенные ячейки, лишние столбцы, многоуровневая шапка и т.д.

Поэтому рядом с этим файлом в той же папке мы создаём ещё один новый файл Обработчик.xlsx, в котором создадим запрос Power Query, который будет загружать страшненькие данные из исходного файла-выгрузки Топ-100 товаров.xls, и приводить их в порядок:

Создаем запрос к внешнему файлу

Открыв файл Обработчик.xlsx, выберем на вкладке Данные команду Получить данные - Из файла - Из книги Excel (Data - Get Data - From file - From Excel), затем укажем местоположение исходного файла и нужный нам лист. Выбранные данные загрузятся в редактор Power Query:

Приведём их в нормальный вид:

1. Удалим пустые строки через Главная - Удалить строки - Удалить пустые строки (Home - Remove Rows - Remove Empty Rows).
2. Удалим ненужные 4 верхних строки через Главная - Удалить строки - Удалить верхние строки (Home - Remove Rows - Remove Top Rows).
3. Поднимем первую строку в шапку таблицы кнопкой Использовать первую строку в качестве заголовков на вкладке Главная (Home - Use first row as header).
4. Отделим пятизначный артикул от названия товара во втором столбце, используя команду Разделить столбец на вкладке Преобразование (Transform - Split Column).
5. Удалим ненужные столбцы и переименуем заголовки оставшихся для лучшей наглядности.

В итоге у нас должна получиться следующая, гораздо более приятная, картина:

Осталось эту облагороженную таблицу выгрузить обратно на лист в наш файл Обработчик.xlsx командой Закрыть и загрузить (Home - Close&Load) на вкладке Главная:

Находим путь к файлу в запросе

Теперь давайте посмотрим как выглядит наш запрос "под капотом", на встроенном в Power Query внутреннем языке с лаконичным названием "М". Для этого вернемся в наш запрос двойным щелчком по нему в правой панели Запросы и подключения и на вкладке Просмотр выберем Расширенный редактор (View - Advanced Editor):

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

Добавляем умную таблицу с путём к файлу

Закроем пока Power Query и вернёмся в наш файл Обработчик.xlsx. Добавим новый пустой лист и сделаем на нём маленькую "умную" таблицу, в единственной ячейке которой будет записан полный путь к нашему файлу исходных данных:

Для создания "умной" таблицы из обычного диапазона можно использовать сочетание клавиш Ctrl+T или кнопку Форматировать как таблицу на вкладке Главная (Home - Format as Table). Заголовок столбца (ячейка А1) может быть совершенно любым. Также обратите внимание, что для понятности я дал таблице имя Параметры на вкладке Конструктор (Design).

Скопировать из Проводника путь или даже ввести его вручную не представляет, конечно, особой сложности, но лучше всего минимизировать человеческий фактор и определять путь, по возможности, автоматически. Это можно реализовать с помощью стандартной функции рабочего листа Excel ЯЧЕЙКА (CELL), которая умеет выдавать кучу полезной информации об указанной в качестве аргумента ячейке - в том числе и путь к текущему файлу:

Если предположить, что файл с исходными данными всегда лежит в той же папке, что и наш Обработчик, то путь, который нам нужен можно сформировать следующей формулой:

=ЛЕВСИМВ(ЯЧЕЙКА("имяфайла");НАЙТИ("[";ЯЧЕЙКА("имяфайла"))-1)&"Топ-100 товаров.xls"

или в английской версии:

=LEFT(CELL("filename");FIND("[";CELL("filename"))-1)&"Топ-100 товаров.xls"

... где функция ЛЕВСИМВ (LEFT) берёт из полной ссылки кусок текста до открывающей квадратной скобки (т.е. путь к текущей папке), а затем к нему приклеивается имя и расширение нашего исходного файла с данными.

Параметризуем путь в запросе

Остался последний и самый главный штрих - прописать в запросе путь к исходному файлу Топ-100 товаров.xls, сославшись на ячейку А2 нашей созданной "умной" таблицы Параметры.

Для этого вернемся в запрос Power Query и ещё раз откроем Расширенный редактор на вкладке Просмотр (View - Advanced Editor). Вместо текстовой строки-пути в кавычках "E:\Отчеты по продажам\Топ-100 товаров.xlsx" введём туда вот такую конструкцию:

Excel.CurrentWorkbook(){[Name="Параметры"]}[Content]{0}[Путь к исходным данным]

Давайте разберемся из чего она состоит:

• Excel.CurrentWorkbook() - это функция языка М для обращения к содержимому текущего файла
• {[Name="Параметры"]}[Content] - это уточняющий параметр к предыдущей функции, указывающий, что мы хотим получить содержимое "умной" таблицы Параметры
• [Путь к исходным данным] - это имя столбца в таблице Параметры, к которому мы обращаемся
• {0} - это номер строки в таблице Параметры, из которой мы хотим взять данные. Шапка - не в счет и нумерация начинается от нуля, а не от единицы.

Вот и всё, собственно.

Осталось нажать на Готово и проверить как работает наш запрос. Теперь при пересылке всей папки с обоими файлами внутри на другой ПК запрос будет сохранять работоспособность и определять путь к данным автоматически.