contents
Si ou te deja kòmanse sèvi ak zouti yo nan gratis Power Query ajoute nan Microsoft Excel, Lè sa a, trè byento ou pral rankontre yon pwoblèm trè espesyalize, men trè souvan ak anmèdan ki asosye ak toujou ap kraze lyen ki mennen nan done sous. Sans nan pwoblèm nan se ke si nan rechèch ou an ou al gade nan dosye ekstèn oswa dosye, Lè sa a, Power Query hardcodes chemen an absoli nan yo nan tèks la rechèch. Tout bagay ap travay byen sou òdinatè w lan, men si w deside voye yon dosye ak yon demann bay kòlèg ou yo, Lè sa a, yo pral wont, paske. yo gen yon chemen diferan nan done sous yo sou òdinatè yo, ak rechèch nou an pa pral travay.
Kisa pou fè nan yon sitiyasyon konsa? Ann gade nan ka sa a an plis detay ak egzanp sa a.
Fòmasyon pwoblèm nan
Sipoze nou gen nan katab la E:Komèsyal rapò bay manti dosye a Top 100 pwodwi yo.xls, ki se yon upload soti nan baz done antrepriz nou an oswa sistèm ERP (1C, SAP, elatriye) Fichye sa a gen enfòmasyon sou atik ki pi popilè komodite yo ak sanble sa a andedan:
Li pwobableman klè nan baton an ke li prèske enposib travay avèk li nan Excel nan fòm sa a: ranje vid nan yon sèl ak done, selil fizyone, kolòn siplemantè, yon header milti-nivo, elatriye pral entèfere.
Se poutèt sa, akote fichye sa a nan katab la menm, nou kreye yon lòt nouvo dosye Handler.xlsx, nan ki nou pral kreye yon demann Power Query ki pral chaje done lèd ki soti nan dosye a telechaje sous Top 100 pwodwi yo.xls, epi mete yo nan lòd:
Fè yon demann nan yon dosye ekstèn
Louvri dosye a Handler.xlsx, chwazi sou tab la Done Kòmandman Jwenn done - Soti nan dosye - Soti nan liv travay Excel (Done — Jwenn Done — Soti nan fichye — Soti nan Excel), Lè sa a, presize kote dosye sous la ak fèy nou bezwen an. Done yo chwazi yo pral chaje nan editè Power Query:
Ann fè yo tounen nòmal:
- Efase liy vid ak Kay — Efase liy — Efase liy vid (Kay — Retire ranje — Retire ranje vid).
- Efase 4 liy ki pa nesesè yo Kay — Efase Ranje — Efase Top Ranje (Kay — Retire ranje — Retire ranje tèt).
- Leve premye ranje a nan tèt tab la ak bouton an Sèvi ak premye liy kòm headers tab Akèy (Kay — Sèvi ak premye ranje kòm header).
- Separe atik senk chif la ak non pwodwi a nan dezyèm kolòn nan lè l sèvi avèk kòmandman an fann kolòn tab Transfòmasyon (Transfòme — Divize Kolòn).
- Efase kolòn ki pa nesesè yo epi chanje non tit yo nan sa ki rete yo pou pi bon vizibilite.
Kòm yon rezilta, nou ta dwe jwenn foto sa yo, pi bèl:
Li rete pou telechaje tab annoble sa a tounen nan fèy nan dosye nou an Handler.xlsx ekip la fèmen epi telechaje (Kay — Fèmen&Chajman) tab Akèy:
Jwenn chemen an nan yon dosye nan yon demann
Koulye a, ann wè ki jan rechèch nou an sanble "anba kapo a", nan lang entèn ki bati nan Power Query ak non an kout "M". Pou fè sa, tounen nan rechèch nou an pa double klike sou li nan fenèt la dwat Demann ak koneksyon ak sou tab la Revizyon chwazi Editè avanse (View — Editè avanse):
Nan fenèt la ki ouvè, dezyèm liy lan imedyatman revele yon chemen difisil-kode nan dosye orijinal nou an. Si nou ka ranplase fisèl tèks sa a ak yon paramèt, varyab, oswa yon lyen nan yon selil fèy Excel kote chemen sa a pre-ekri, Lè sa a, nou ka fasilman chanje li pita.
Ajoute yon tab entelijan ak yon chemen dosye
Ann fèmen Power Query pou kounye a epi retounen nan dosye nou an Handler.xlsx. Ann ajoute yon nouvo fèy vid epi fè yon ti tab "entelijan" sou li, nan sèl selil la ki pral ekri chemen konplè nan dosye done sous nou an:
Pou kreye yon tab entelijan nan yon seri regilye, ou ka itilize chemen kout klavye a Ctrl+T oswa bouton Fòma kòm yon tab tab Akèy (Kay — Fòma kòm Tablo). Tit kolòn (selil A1) kapab absoliman anyen. Epitou sonje ke pou klè mwen te bay tab la yon non paramèt tab Constructor (Design).
Kopi yon chemen soti nan Explorer oswa menm antre nan li manyèlman se, nan kou, pa patikilyèman difisil, men li pi bon pou minimize faktè imen an epi detèmine chemen an, si sa posib, otomatikman. Sa a ka aplike lè l sèvi avèk fonksyon estanda Excel fèy travay la SELILÈ (SELIL), ki ka bay yon pakèt enfòmasyon itil sou selil ki espesifye kòm yon agiman - ki gen ladan chemen an nan dosye aktyèl la:
Si nou sipoze ke fichye done sous la toujou chita nan katab la menm ak Processeur nou an, Lè sa a, chemen nou bezwen an ka fòme pa fòmil sa a:
=GÒCH(SELIL ("non fichye");JWENN ("[";SELIL ("non fichye"))-1)&"Top 100 pwodwi yo.xls"
oswa nan vèsyon angle:
=GÒCH(SELIL(«non fichye»);FIND(«[«;SELIL(«non fichye»))-1)&»Топ-100 товаров.xls»
… kote fonksyon an ye LEVSIMV (GFTCH) pran yon moso tèks soti nan lyen konplè a jiska bracket kare ouvèti a (sa vle di chemen ki mennen nan katab aktyèl la), ak Lè sa a, non an ak ekstansyon nan dosye done sous nou an kole sou li.
Paramètrize chemen an nan rechèch la
Dènye ak pi enpòtan manyen rete - ekri chemen an nan dosye sous la nan demann lan Top 100 pwodwi yo.xls, refere li a selil A2 nan tablo "entelijan" nou kreye paramèt.
Pou fè sa, ann tounen nan rechèch la Power Query epi louvri li ankò Editè avanse tab Revizyon (View — Editè avanse). Olye de yon tèks chaîne-chemen nan guillemets "E: Rapò Komèsyal Top 100 pwodwi yo.xlsx" Ann prezante estrikti sa a:
Excel.CurrentWorkbook(){[Name =”Anviwònman”] [Contenu]0 {}[Chemen pou done sous]
Ann wè ki sa li konsiste de:
- Excel.CurrentWorkbook() se yon fonksyon lang M pou jwenn aksè nan sa ki nan dosye aktyèl la
- {[Name =”Anviwònman”] [Contenu] - sa a se yon paramèt rafineman nan fonksyon anvan an, ki endike ke nou vle jwenn sa ki nan tab la "entelijan" paramèt
- [Chemen pou done sous] se non kolòn nan tablo a paramètki nou refere
- 0 {} se nimewo ranje ki nan tablo a paramètkote nou vle pran done. Bouchon an pa konte epi nimero a kòmanse soti nan zewo, pa soti nan yon sèl.
Sa a tout, an reyalite.
Li rete pou klike sou Fini epi tcheke kijan demann nou an ap fonksyone. Koulye a, lè w ap voye katab la tout antye ak tou de dosye andedan nan yon lòt PC, demann lan ap rete operasyonèl epi detèmine chemen an nan done yo otomatikman.
- Ki sa ki se Power Query ak poukisa li nesesè lè w ap travay nan Microsoft Excel
- Ki jan yo enpòte yon fragment tèks k ap flote nan Power Query
- Redesigning yon XNUMXD Crosstab nan yon tab plat ak Power Query