vsebina
Če ste že začeli uporabljati orodja brezplačnega dodatka Power Query v Microsoft Excelu, boste zelo kmalu naleteli na eno visoko specializirano, a zelo pogosto in nadležno težavo, povezano z nenehnim prekinjevanjem povezav do izvornih podatkov. Bistvo težave je v tem, da če se v svoji poizvedbi sklicujete na zunanje datoteke ali mape, potem Power Query v besedilu poizvedbe trdo kodira absolutno pot do njih. Na vašem računalniku vse deluje dobro, a če se odločite poslati datoteko z zahtevo svojim sodelavcem, bodo razočarani, ker. imajo drugačno pot do izvornih podatkov v svojem računalniku in naša poizvedba ne bo delovala.
Kaj storiti v takšni situaciji? Oglejmo si ta primer podrobneje z naslednjim primerom.
Formulacija problema
Recimo, da imamo v mapi E:Poročila o prodaji leži datoteka 100 najboljših izdelkov.xls, ki je naložen iz naše poslovne baze podatkov ali sistema ERP (1C, SAP itd.) Ta datoteka vsebuje informacije o najbolj priljubljenih blagovnih artiklih in je znotraj videti takole:
Verjetno je takoj jasno, da je skoraj nemogoče delati z njim v Excelu v tej obliki: prazne vrstice do enega s podatki, združene celice, dodatni stolpci, večnivojska glava itd. Bodo motile.
Zato poleg te datoteke v isti mapi ustvarimo še eno novo datoteko Handler.xlsx, v katerem bomo ustvarili poizvedbo Power Query, ki bo naložila grde podatke iz izvorne datoteke za nalaganje 100 najboljših izdelkov.xlsin jih razporedite po vrsti:
Izdelava zahteve za zunanjo datoteko
Odpiranje datoteke Handler.xlsx, izberite na zavihku datum Ukaz Pridobite podatke – iz datoteke – iz Excelovega delovnega zvezka (Podatki — Pridobi podatke — Iz datoteke — Iz Excela), nato določite lokacijo izvorne datoteke in list, ki ga potrebujemo. Izbrani podatki bodo naloženi v urejevalnik Power Query:
Spravimo jih nazaj v normalno stanje:
- Izbrišite prazne vrstice z Domov — Izbriši vrstice — Izbriši prazne vrstice (Domov — Odstrani vrstice — Odstrani prazne vrstice).
- Izbrišite nepotrebne zgornje 4 vrstice Domov — Izbriši vrstice — Izbriši zgornje vrstice (Domov — Odstrani vrstice — Odstrani zgornje vrstice).
- Z gumbom dvignite prvo vrstico do glave tabele Prvo vrstico uporabite kot glave tab Domov (Domov — Uporabi prvo vrstico kot glavo).
- Z ukazom ločite petmestni artikel od imena izdelka v drugem stolpcu razdeljen stolpec tab Preoblikovanje (Pretvorba — razdeli stolpec).
- Izbrišite nepotrebne stolpce in preimenujte naslove preostalih za boljšo preglednost.
Kot rezultat bi morali dobiti naslednjo, veliko bolj prijetno sliko:
Ostaja še naložiti to oplemeniteno tabelo nazaj na list v naši datoteki Handler.xlsx ekipa zaprite in prenesite (Domov — Zapri&Naloži) tab Domov:
Iskanje poti do datoteke v zahtevi
Zdaj pa poglejmo, kako je naša poizvedba videti "pod pokrovom", v internem jeziku, vgrajenem v Power Query z jedrnatim imenom "M". Če želite to narediti, se vrnite na našo poizvedbo tako, da dvokliknete nanjo v desnem podoknu Prošnje in povezave in na zavihku pregled izberite Napredni urejevalnik (Pogled — napredni urejevalnik):
V oknu, ki se odpre, druga vrstica takoj razkrije trdo kodirano pot do izvirne datoteke za nalaganje. Če lahko ta besedilni niz nadomestimo s parametrom, spremenljivko ali povezavo do celice Excelovega lista, kjer je ta pot vnaprej zapisana, jo lahko pozneje preprosto spremenimo.
Dodajte pametno tabelo s potjo datoteke
Za zdaj zaprimo Power Query in se vrnimo k naši datoteki Handler.xlsx. Dodajmo nov prazen list in na njem naredimo majhno »pametno« tabelo, v edini celici katere bo zapisana celotna pot do naše izvorne podatkovne datoteke:
Če želite ustvariti pametno tabelo iz običajnega obsega, lahko uporabite bližnjico na tipkovnici Ctrl+T ali gumb Oblikuj kot tabelo tab Domov (Domov — Oblikuj kot tabelo). Naslov stolpca (celica A1) je lahko karkoli. Upoštevajte tudi, da sem zaradi jasnosti dal tabeli ime parametri tab Konstruktor (Oblikovanje).
Kopiranje poti iz Raziskovalca ali celo ročni vnos seveda ni posebno težko, vendar je najbolje zmanjšati človeški dejavnik in pot določiti, če je le mogoče, samodejno. To je mogoče izvesti s standardno funkcijo Excelovega delovnega lista CELICA (CELICA), ki lahko poda kup uporabnih informacij o celici, navedeni kot argument – vključno s potjo do trenutne datoteke:
Če predpostavimo, da je izvorna podatkovna datoteka vedno v isti mapi kot naš procesor, potem lahko pot, ki jo potrebujemo, oblikujemo z naslednjo formulo:
=LEFT(CELL(“ime datoteke”);FIND(“[“;CELL(“ime datoteke”))-1)&”100 najboljših izdelkov.xls”
ali v angleški različici:
=LEFT(CELL(«ime datoteke»);FIND(«[«;CELL(«ime datoteke»))-1)&»Top-100 izdelkov.xls»
… kje je funkcija LEVSIMV (LEVO) vzame del besedila od celotne povezave do začetnega oglatega oklepaja (tj. poti do trenutne mape), nato pa se nanj prilepi ime in končnica naše izvorne podatkovne datoteke.
Parametrirajte pot v poizvedbi
Ostaja še zadnji in najpomembnejši dotik – v zahtevo zapisati pot do izvorne datoteke 100 najboljših izdelkov.xls, ki se nanaša na celico A2 naše ustvarjene »pametne« tabele parametri.
Če želite to narediti, se vrnimo k poizvedbi Power Query in jo znova odprimo Napredni urejevalnik tab pregled (Pogled — napredni urejevalnik). Namesto besedilnega niza-pot v narekovajih “E:Poročila o prodaji 100 najboljših izdelkov.xlsx” Predstavimo naslednjo strukturo:
Excel.CurrentWorkbook(){[Name=”Nastavitve”]}[Vsebina]0 {}[Pot do izvornih podatkov]
Poglejmo, iz česa je sestavljen:
- Excel.CurrentWorkbook() je funkcija jezika M za dostop do vsebine trenutne datoteke
- {[Name=”Nastavitve”]}[Vsebina] – to je parameter za izboljšanje prejšnje funkcije, ki nakazuje, da želimo pridobiti vsebino »pametne« tabele parametri
- [Pot do izvornih podatkov] je ime stolpca v tabeli parametrina katerega se sklicujemo
- 0 {} je številka vrstice v tabeli parametriiz katerega želimo vzeti podatke. Zgornja meja se ne šteje in številčenje se začne od nič, ne od ena.
To je pravzaprav vse.
Ostaja še klik Konec in preverite, kako deluje naša zahteva. Zdaj, ko pošljete celotno mapo z obema datotekama v drugem računalniku, bo zahteva ostala operativna in samodejno določila pot do podatkov.
- Kaj je Power Query in zakaj je potreben pri delu v Microsoft Excelu
- Kako uvoziti delček lebdečega besedila v Power Query
- Preoblikovanje XNUMXD navzkrižne tabele v ravno tabelo s funkcijo Power Query