змест
Калі вы ўжо пачалі карыстацца інструментамі бясплатнай надбудовы Power Query ў Microsoft Excel, то вельмі хутка вы сутыкнецеся з адной вузкаспецыялізаванай, але вельмі частай і непрыемнай праблемай, звязанай з пастаянна разрывам спасылак на зыходныя дадзеныя. Сутнасць праблемы ў тым, што калі ў вашым запыце вы спасылаецеся на знешнія файлы або тэчкі, то Power Query жорстка кадуе абсалютны шлях да іх у тэксце запыту. На вашым кампутары ўсё выдатна працуе, але калі вы вырашыце адправіць файл з запытам сваім калегам, то яны будуць расчараваныя, т.к. яны маюць іншы шлях да зыходных дадзеных на сваім кампутары, і наш запыт не будзе працаваць.
Што рабіць у такой сітуацыі? Разгледзім гэты выпадак падрабязней на наступным прыкладзе.
Пастаноўка задачы
Дапусцім, у нас ёсць у тэчцы E:Справаздачы аб продажах ляжыць файл 100 лепшых прадуктаў.xls, які з'яўляецца запампоўкай з нашай карпаратыўнай базы дадзеных або ERP-сістэмы (1С, SAP і г.д.). Гэты файл змяшчае інфармацыю аб самых папулярных таварных пазіцыях і выглядае так:
Напэўна, адразу зразумела, што працаваць з ім у Excel ў такім выглядзе практычна немагчыма: будуць замінаць пустыя радкі праз адну з дадзенымі, аб'яднаныя вочкі, лішнія слупкі, шматузроўневы загаловак і г.д.
Такім чынам, побач з гэтым файлам у той жа тэчцы мы ствараем яшчэ адзін новы файл Апрацоўшчык.xlsx, у якім мы створым запыт Power Query, які будзе загружаць непрыгожыя даныя з зыходнага файла загрузкі 100 лепшых прадуктаў.xls, і пастаўце іх у парадак:
Стварэнне запыту да вонкавага файла
Адкрыццё файла Апрацоўшчык.xlsx, абярыце на ўкладцы Дата Каманда Атрымаць даныя – з файла – з кнігі Excel (Дадзеныя — Атрымаць даныя — З файла — З Excel), затым паказваем месцазнаходжанне зыходнага файла і патрэбнага нам ліста. Выбраныя даныя будуць загружаны ў рэдактар Power Query:
Давайце вернем іх да нармальнага жыцця:
- Выдаліць пустыя радкі з дапамогай Галоўная — Выдаліць радкі — Выдаліць пустыя радкі (Галоўная старонка — Выдаліць радкі — Выдаліць пустыя радкі).
- Выдаліце непатрэбныя 4 верхнія радкі Галоўная — Выдаліць радкі — Выдаліць верхнія радкі (Галоўная старонка — Выдаліць радкі — Выдаліць верхнія радкі).
- Кнопкай падніміце першы радок да загалоўка табліцы Выкарыстоўвайце першы радок у якасці загалоўкаў таб Галоўная (Галоўная старонка — выкарыстоўваць першы радок як загаловак).
- Аддзяліце пяцізначны артыкул ад назвы прадукту ў другім слупку з дапамогай каманды раздзелены слупок таб Пераўтварэнне (Пераўтварэнне — Разбіць слупок).
- Выдаліце непатрэбныя слупкі і перайменуйце загалоўкі астатніх для лепшай бачнасці.
У выніку ў нас павінна атрымацца наступная, значна больш прыемная карціна:
Засталося загрузіць гэтую акультураную табліцу назад на ліст у наш файл Апрацоўшчык.xlsx Каманда зачыніць і спампаваць (Галоўная — Зачыніць і загрузіць) таб Галоўная:
Пошук шляху да файла ў запыце
Зараз давайце паглядзім, як наш запыт выглядае «пад капотам», на ўнутранай мове, убудаванай у Power Query з лаканічнай назвай «M». Для гэтага вярніцеся да нашага запыту, двойчы клікнуўшы па ім у правай панэлі Запыты і сувязі і на ўкладцы агляд выбіраць Пашыраны рэдактар (Прагляд — пашыраны рэдактар):
У акне, якое адкрыецца, другі радок адразу паказвае жорстка закадзіраваны шлях да нашага зыходнага файла для загрузкі. Калі мы можам замяніць гэты тэкставы радок параметрам, зменнай або спасылкай на ячэйку ліста Excel, дзе гэты шлях загадзя прапісаны, то мы можам лёгка змяніць яго пазней.
Дадайце разумную табліцу з шляхам да файла
Давайце пакуль закрыем Power Query і вернемся да нашага файла Апрацоўшчык.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:Справаздачы аб продажах 100 лепшых прадуктаў.xlsx» Увядзем наступную структуру:
Excel.CurrentWorkbook(){[Name=”Настройкі”]}[Змест]0 {}[Шлях да зыходных дадзеных]
Давайце паглядзім, з чаго ён складаецца:
- Excel.CurrentWorkbook() гэта функцыя мовы M для доступу да змесціва бягучага файла
- {[Name=”Настройкі”]}[Змест] – гэта параметр удакладнення папярэдняй функцыі, які паказвае, што мы хочам атрымаць змесціва «разумнай» табліцы параметры
- [Шлях да зыходных дадзеных] гэта назва слупка ў табліцы параметрыда якога мы спасылаемся
- 0 {} гэта нумар радка ў табліцы параметрыз якога мы хочам атрымаць дадзеныя. Шапка не лічыцца і нумарацыя пачынаецца з нуля, а не з адзінкі.
Вось, уласна, і ўсё.
Засталося націснуць Заканчэнне і праверце, як працуе наш запыт. Цяпер пры адпраўцы ўсёй тэчкі з абодвума файламі на іншы ПК запыт будзе заставацца ў працоўным стане і аўтаматычна вызначаць шлях да дадзеных.
- Што такое Power Query і для чаго ён патрэбны пры працы ў Microsoft Excel
- Як імпартаваць фрагмент плаваючага тэксту ў Power Query
- Рэдызайн XNUMXD крыжаванай табліцы ў плоскую табліцу з дапамогай Power Query