Obsah
Ak ste už začali používať nástroje bezplatného doplnku Power Query v Microsoft Exceli, tak veľmi skoro narazíte na jeden vysoko špecializovaný, no veľmi častý a nepríjemný problém spojený s neustálym lámaním odkazov na zdrojové dáta. Podstata problému spočíva v tom, že ak vo svojom dotaze odkazujete na externé súbory alebo priečinky, Power Query napevno zakóduje absolútnu cestu k nim v texte dotazu. Na vašom počítači všetko funguje dobre, ale ak sa rozhodnete poslať súbor so žiadosťou svojim kolegom, budú sklamaní, pretože. majú na svojom počítači inú cestu k zdrojovým údajom a náš dotaz nebude fungovať.
Čo robiť v takejto situácii? Pozrime sa na tento prípad podrobnejšie s nasledujúcim príkladom.
Formulácia problému
Predpokladajme, že máme v priečinku E:Správy o predaji leží súbor 100 najlepších produktov.xls, čo je nahranie z našej firemnej databázy alebo ERP systému (1C, SAP atď.) Tento súbor obsahuje informácie o najobľúbenejších komoditných položkách a vo vnútri vyzerá takto:
Asi je hneď jasné, že v Exceli sa s ním v takejto podobe takmer nedá pracovať: prekážajú prázdne riadky cez jeden s údajmi, zlúčené bunky, stĺpce navyše, viacúrovňová hlavička atď.
Preto vedľa tohto súboru v rovnakom priečinku vytvoríme ďalší nový súbor Handler.xlsx, v ktorom vytvoríme dotaz Power Query, ktorý načíta škaredé údaje zo zdrojového nahrávaného súboru 100 najlepších produktov.xlsa usporiadajte ich:
Zadanie požiadavky na externý súbor
Otváranie súboru Handler.xlsx, vyberte na karte dátum povel Získať údaje – zo súboru – z excelového zošita (Údaje — Získať údaje — Zo súboru — Z Excelu), potom zadajte umiestnenie zdrojového súboru a hárok, ktorý potrebujeme. Vybraté údaje sa načítajú do editora Power Query:
Vráťme ich do normálu:
- Odstráňte prázdne riadky pomocou Domov — Vymazať riadky — Vymazať prázdne riadky (Domov — Odstrániť riadky — Odstrániť prázdne riadky).
- Odstráňte nepotrebné horné 4 riadky Domov — Odstrániť riadky — Odstrániť prvé riadky (Domov — Odstrániť riadky — Odstrániť vrchné riadky).
- Zdvihnite prvý riadok do hlavičky tabuľky pomocou tlačidla Použite prvý riadok ako hlavičky pútko Domov (Domov — Použite prvý riadok ako hlavičku).
- Pomocou príkazu oddeľte päťmiestny článok od názvu produktu v druhom stĺpci rozdelený stĺpec pútko Premena (Transformácia – rozdelený stĺpec).
- Odstráňte nepotrebné stĺpce a premenujte nadpisy zostávajúcich pre lepšiu viditeľnosť.
V dôsledku toho by sme mali získať nasledujúci, oveľa príjemnejší obraz:
Zostáva nahrať túto zušľachtenú tabuľku späť do hárku v našom súbore Handler.xlsx tím zavrieť a stiahnuť (Domov – Zavrieť a načítať) pútko Domov:
Nájdenie cesty k súboru v žiadosti
Teraz sa pozrime, ako náš dotaz vyzerá „pod kapotou“ v internom jazyku zabudovanom do Power Query s výstižným názvom „M“. Ak to chcete urobiť, vráťte sa na náš dopyt dvojitým kliknutím naň v pravej časti okna Žiadosti a spojenia a na karte preskúmanie vybrať Pokročilý editor (Zobraziť — Pokročilý editor):
V okne, ktoré sa otvorí, druhý riadok okamžite odhalí pevne zakódovanú cestu k nášmu pôvodnému nahrávanému súboru. Ak dokážeme nahradiť tento textový reťazec parametrom, premennou alebo odkazom na bunku hárka Excelu, kde je táto cesta vopred napísaná, môžeme to neskôr ľahko zmeniť.
Pridajte inteligentnú tabuľku s cestou k súboru
Zatvorme Power Query a vráťme sa k nášmu súboru Handler.xlsx. Pridajme nový prázdny hárok a urobme na ňom malú „inteligentnú“ tabuľku, v ktorej jedinej bunke bude napísaná úplná cesta k nášmu zdrojovému súboru údajov:
Na vytvorenie inteligentnej tabuľky z bežného rozsahu môžete použiť klávesovú skratku ctrl+T alebo tlačidlo Formátovať ako tabuľku pútko Domov (Domov – Formátovať ako tabuľku). Záhlavie stĺpca (bunka A1) môže byť úplne čokoľvek. Všimnite si tiež, že kvôli prehľadnosti som dal tabuľke názov parametre pútko staviteľ (Dizajn).
Skopírovanie cesty z Prieskumníka alebo dokonca jej manuálne zadanie samozrejme nie je nijak zvlášť náročné, no najlepšie je minimalizovať ľudský faktor a určiť cestu, pokiaľ je to možné, automaticky. Toto je možné implementovať pomocou štandardnej funkcie pracovného hárka programu Excel BUNKA (BUNKA), ktorý môže poskytnúť množstvo užitočných informácií o bunke zadanej ako argument – vrátane cesty k aktuálnemu súboru:
Ak predpokladáme, že zdrojový dátový súbor sa vždy nachádza v rovnakom priečinku ako náš procesor, potom cestu, ktorú potrebujeme, môžeme vytvoriť podľa nasledujúceho vzorca:
=LEFT(BUŇKA(“názov súboru”);NÁJSŤ(“[“;BUNKA(“názov súboru”))-1)&”100 najlepších produktov.xls”
alebo v anglickej verzii:
=LEFT(CELL(«názov súboru»);FIND(«[«;CELL(«názov súboru»))-1)&»Топ-100 товаров.xls»
... kde je funkcia LEVSIMV (VĽAVO) prevezme kúsok textu od úplného odkazu až po úvodnú hranatú zátvorku (tj cestu k aktuálnemu priečinku) a potom sa k nemu prilepí názov a prípona nášho zdrojového dátového súboru.
Parametrizujte cestu v dotaze
Zostáva posledný a najdôležitejší dotyk – napísať do požiadavky cestu k zdrojovému súboru 100 najlepších produktov.xlss odkazom na bunku A2 našej vytvorenej „inteligentnej“ tabuľky parametre.
Ak to chcete urobiť, vráťte sa k dotazu Power Query a znova ho otvorte Pokročilý editor pútko preskúmanie (Zobraziť — Pokročilý editor). Namiesto textového reťazca-cesta v úvodzovkách „E: Prehľady predajaNajlepších 100 produktov.xlsx“ Predstavme si nasledujúcu štruktúru:
Excel.CurrentWorkbook(){[Name=”Nastavenia”]}[Obsah]0 {}[Cesta k zdrojovým údajom]
Pozrime sa, z čoho pozostáva:
- Excel.CurrentWorkbook() je funkcia jazyka M na prístup k obsahu aktuálneho súboru
- {[Name=”Nastavenia”]}[Obsah] – toto je spresňujúci parameter predchádzajúcej funkcie, ktorý naznačuje, že chceme získať obsah „inteligentnej“ tabuľky parametre
- [Cesta k zdrojovým údajom] je názov stĺpca v tabuľke parametrena ktorý sa odvolávame
- 0 {} je číslo riadku v tabuľke parametrez ktorých chceme brať údaje. Čiapočka sa nepočíta a číslovanie začína od nuly, nie od jednotky.
To je vlastne všetko.
Zostáva kliknúť ďalej úprava a skontrolujte, ako naša požiadavka funguje. Teraz, keď odosielate celý priečinok s oboma súbormi vo vnútri do iného počítača, požiadavka zostane funkčná a automaticky určí cestu k údajom.
- Čo je Power Query a prečo je potrebný pri práci v programe Microsoft Excel
- Ako importovať útržok plávajúceho textu do Power Query
- Prepracovanie krížovej tabuľky XNUMXD na plochú tabuľku pomocou Power Query