Съдържание
Ако вече сте започнали да използвате инструментите на безплатната добавка Power Query в Microsoft Excel, тогава много скоро ще се сблъскате с един високоспециализиран, но много често срещан и досаден проблем, свързан с постоянно прекъсване на връзките към изходните данни. Същността на проблема е, че ако във вашата заявка се позовавате на външни файлове или папки, тогава Power Query твърдо кодира абсолютния път до тях в текста на заявката. Всичко работи добре на вашия компютър, но ако решите да изпратите файл с молба до колегите си, те ще бъдат разочаровани, защото. те имат различен път до изходните данни на своя компютър и нашата заявка няма да работи.
Какво да направите в такава ситуация? Нека разгледаме този случай по-подробно със следния пример.
Формулиране на проблема
Да предположим, че имаме в папката E:Отчети за продажбите лежи файлът Топ 100 продукти.xls, който е качване от нашата корпоративна база данни или ERP система (1C, SAP и др.) Този файл съдържа информация за най-популярните стокови позиции и изглежда така вътре:
Вероятно е ясно още от самото начало, че е почти невъзможно да се работи с него в Excel в тази форма: празни редове до един с данни, обединени клетки, допълнителни колони, многостепенна заглавка и т.н. ще пречат.
Следователно до този файл в същата папка създаваме друг нов файл Handler.xlsx, в който ще създадем Power Query заявка, която ще зареди грозни данни от изходния файл за качване Топ 100 продукти.xlsи ги подредете:
Извършване на заявка към външен файл
Отваряне на файла Handler.xlsx, изберете в раздела Дата Команда Вземете данни – от файл – от работна книга на Excel (Данни — Получаване на данни — От файл — От Excel), след което посочете местоположението на изходния файл и листа, от който се нуждаем. Избраните данни ще бъдат заредени в редактора на Power Query:
Нека ги върнем към нормалното:
- Изтрийте празните редове с Начало — Изтриване на редове — Изтриване на празни редове (Начало — Премахване на редове — Премахване на празни редове).
- Изтрийте ненужните горни 4 реда Начало — Изтриване на редове — Изтриване на горни редове (Начало — Премахване на редове — Премахване на горни редове).
- Повдигнете първия ред до заглавката на таблицата с бутона Използвайте първия ред като заглавки етикет Начало (Начало — Използвайте първия ред като заглавка).
- Отделете петцифрения артикул от името на продукта във втората колона с помощта на командата разделена колона етикет Трансформация (Трансформация — Разделяне на колона).
- Изтрийте ненужните колони и преименувайте заглавията на останалите за по-добра видимост.
В резултат на това трябва да получим следната, много по-приятна картина:
Остава да качим тази облагородена таблица обратно на листа в нашия файл Handler.xlsx екипът затворете и изтеглете (Начало — Затвори и зареди) етикет Начало:
Намиране на пътя до файл в заявка
Сега нека видим как нашата заявка изглежда „под капака“, на вътрешния език, вграден в Power Query с краткото име „M“. За да направите това, върнете се към нашата заявка, като щракнете двукратно върху нея в десния панел Заявки и връзки и на раздела Оценки на посетители изберете Разширен редактор (Преглед — Разширен редактор):
В прозореца, който се отваря, вторият ред веднага разкрива твърдо кодиран път към оригиналния файл за качване. Ако можем да заменим този текстов низ с параметър, променлива или връзка към клетка от лист на Excel, където този път е предварително написан, тогава можем лесно да го променим по-късно.
Добавете интелигентна таблица с път към файла
Нека затворим Power Query за сега и се върнем към нашия файл Handler.xlsx. Нека добавим нов празен лист и да направим малка „умна“ таблица върху него, в единствената клетка на която ще бъде записан пълният път до нашия файл с изходни данни:
За да създадете интелигентна таблица от обикновен диапазон, можете да използвате клавишната комбинация Ctrl+T или бутон Форматирайте като таблица етикет Начало (Начало — Форматиране като таблица). Заглавието на колоната (клетка A1) може да бъде абсолютно всичко. Също така имайте предвид, че за по-голяма яснота дадох име на таблицата параметри етикет конструктор (Дизайн).
Копирането на път от Explorer или дори въвеждането му ръчно, разбира се, не е особено трудно, но най-добре е да сведете до минимум човешкия фактор и да определите пътя, ако е възможно, автоматично. Това може да се реализира с помощта на стандартната функция за работен лист на Excel CELL (КЛЕТКА), който може да даде куп полезна информация за клетката, посочена като аргумент – включително пътя до текущия файл:
Ако приемем, че изходният файл с данни винаги се намира в същата папка като нашия процесор, тогава пътят, от който се нуждаем, може да се формира по следната формула:
=LEFT(CELL(“име на файл”);FIND(“[“;CELL(“име на файл”))-1)&”Топ 100 продукта.xls”
или в английска версия:
=LEFT(CELL(«име на файл»);FIND(«[«;CELL(«име на файл»))-1)&»Топ-100 товара.xls»
… къде е функцията ЛЕВСИМВ (НАЛЯВО) взема част от текста от пълната връзка до отварящата квадратна скоба (т.е. пътя до текущата папка), след което името и разширението на нашия файл с изходни данни се залепват към него.
Параметризирайте пътя в заявката
Остава последното и най-важно докосване – да напишем пътя до изходния файл в заявката Топ 100 продукти.xls, препращайки към клетка A2 от нашата създадена „умна“ таблица параметри.
За да направите това, нека се върнем към заявката на Power Query и я отворим отново Разширен редактор етикет Оценки на посетители (Преглед — Разширен редактор). Вместо текстов низ-път в кавички „E:Отчети за продажби Топ 100 продукта.xlsx“ Нека представим следната структура:
Excel.CurrentWorkbook(){[Name=”Настройки”]}[Съдържание]0 {}[Път до изходните данни]
Да видим от какво се състои:
- Excel.CurrentWorkbook() е функция на езика M за достъп до съдържанието на текущия файл
- {[Name=”Настройки”]}[Съдържание] – това е параметър за уточняване на предишната функция, показващ, че искаме да получим съдържанието на „умната“ таблица параметри
- [Път до изходните данни] е името на колоната в таблицата параметрикъм които се позоваваме
- 0 {} е номерът на реда в таблицата параметриот който искаме да вземем данни. Капачката не се брои и номерацията започва от нула, а не от единица.
Това е всичко, всъщност.
Остава да кликнете върху завършеност и проверете как работи нашата заявка. Сега, когато изпратите цялата папка с двата файла вътре на друг компютър, заявката ще остане работеща и автоматично ще определи пътя до данните.
- Какво е Power Query и защо е необходим при работа в Microsoft Excel
- Как да импортирате фрагмент с плаващ текст в Power Query
- Препроектиране на XNUMXD кръстосана таблица до плоска таблица с Power Query