Kuainisha Njia za Data katika Hoja ya Nguvu

Ikiwa tayari umeanza kutumia zana za nyongeza ya Swali la Nguvu ya bure katika Microsoft Excel, basi hivi karibuni utakutana na shida moja maalum, lakini ya mara kwa mara na ya kukasirisha inayohusishwa na kuvunja viungo kila mara kwa data ya chanzo. Kiini cha tatizo ni kwamba ikiwa katika swali lako unarejelea faili au folda za nje, basi Power Query huweka misimbo ya njia kamili kwao katika maandishi ya hoja. Kila kitu kinafanya kazi vizuri kwenye kompyuta yako, lakini ukiamua kutuma faili na ombi kwa wenzako, basi watakuwa na tamaa, kwa sababu. wana njia tofauti ya data chanzo kwenye kompyuta zao, na hoja yetu haitafanya kazi.

Nini cha kufanya katika hali kama hiyo? Wacha tuangalie kesi hii kwa undani zaidi na mfano ufuatao.

Uundaji wa shida

Tuseme tunayo kwenye folda E:Ripoti za mauzo uongo faili Bidhaa 100 bora.xls, ambayo ni upakiaji kutoka kwa hifadhidata yetu ya shirika au mfumo wa ERP (1C, SAP, n.k.) Faili hii ina maelezo kuhusu bidhaa maarufu zaidi na inaonekana kama hii ndani:

Pengine ni wazi moja kwa moja kwamba karibu haiwezekani kufanya kazi nayo katika Excel katika fomu hii: safu tupu kupitia moja iliyo na data, seli zilizounganishwa, safu wima za ziada, kichwa cha ngazi nyingi, n.k. zitaingilia kati.

Kwa hiyo, karibu na faili hii kwenye folda sawa, tunaunda faili nyingine mpya Handler.xlsx, ambamo tutaunda hoja ya Power Query ambayo itapakia data mbaya kutoka kwa faili ya upakiaji chanzo Bidhaa 100 bora.xls, na uziweke kwa mpangilio:

Kufanya ombi kwa faili ya nje

Kufungua faili Handler.xlsx, chagua kwenye kichupo Data Amri Pata Data - Kutoka kwa Faili - Kutoka kwa Kitabu cha Kazi cha Excel (Data - Pata Data - Kutoka kwa faili - Kutoka Excel), kisha taja eneo la faili ya chanzo na karatasi tunayohitaji. Data iliyochaguliwa itapakiwa kwenye kihariri cha Hoja ya Nguvu:

Wacha tuwarudishe kwa kawaida:

1. Futa mistari tupu na Nyumbani - Futa mistari - Futa mistari tupu (Nyumbani - Ondoa Safu - Ondoa Safu Tupu).
2. Futa mistari 4 ya juu isiyo ya lazima kupitia Nyumbani - Futa Safu - Futa Safu za Juu (Nyumbani - Ondoa Safu - Ondoa Safu Mlalo za Juu).
3. Inua safu mlalo ya kwanza kwenye kichwa cha jedwali na kitufe Tumia mstari wa kwanza kama vichwa tab Nyumbani (Nyumbani - Tumia safu mlalo ya kwanza kama kichwa).
4. Tenganisha nakala ya nambari tano kutoka kwa jina la bidhaa kwenye safu wima ya pili kwa kutumia amri safu mgawanyiko tab Mabadiliko (Badilisha - Gawanya Safu).
5. Futa safu wima zisizo za lazima na ubadilishe jina la vichwa vya zilizobaki kwa mwonekano bora.

Kama matokeo, tunapaswa kupata picha ifuatayo, ya kupendeza zaidi:

Inabakia kupakia jedwali hili lililoboreshwa kwenye laha katika faili yetu Handler.xlsx timu funga na upakue (Nyumbani - Funga&Pakia) tab Nyumbani:

Kutafuta njia ya faili katika ombi

Sasa hebu tuone jinsi swali letu linavyoonekana "chini ya kifuniko", katika lugha ya ndani iliyojengwa katika Hoja ya Nguvu kwa jina la kifupi "M". Ili kufanya hivyo, rudi kwenye hoja yetu kwa kubofya mara mbili kwenye kidirisha cha kulia Maombi na miunganisho na kwenye kichupo Tathmini kuchagua Mhariri wa hali ya juu (Angalia - Mhariri wa hali ya juu):

Katika dirisha linalofungua, mstari wa pili unaonyesha mara moja njia ya msimbo ngumu kwa faili yetu asili ya upakiaji. Ikiwa tunaweza kubadilisha mfuatano huu wa maandishi kwa kigezo, kigezo, au kiungo cha seli ya laha ya Excel ambapo njia hii imeandikwa awali, basi tunaweza kuibadilisha kwa urahisi baadaye.

Ongeza jedwali mahiri na njia ya faili

Hebu tufunge Hoja ya Nguvu kwa sasa na turudi kwenye faili yetu Handler.xlsx. Wacha tuongeze karatasi mpya tupu na tufanye jedwali ndogo "smart" juu yake, kwenye seli pekee ambayo njia kamili ya faili yetu ya data itaandikwa:

Ili kuunda jedwali mahiri kutoka kwa safu ya kawaida, unaweza kutumia njia ya mkato ya kibodi Ctrl+T au kifungo Fomati kama jedwali tab Nyumbani (Nyumbani - Umbizo kama Jedwali). Kichwa cha safu (kiini A1) kinaweza kuwa chochote kabisa. Pia kumbuka kuwa kwa uwazi nimeipa jedwali jina vigezo tab kuujenga (Ubunifu).

Kuiga njia kutoka kwa Explorer au hata kuingia kwa manually ni, bila shaka, si vigumu sana, lakini ni bora kupunguza sababu ya kibinadamu na kuamua njia, ikiwa inawezekana, moja kwa moja. Hii inaweza kutekelezwa kwa kutumia kazi ya kawaida ya lahakazi ya Excel CLE ( KIINI), ambayo inaweza kutoa rundo la habari muhimu kuhusu kisanduku kilichobainishwa kama hoja - ikijumuisha njia ya faili ya sasa:

Ikiwa tunadhania kuwa faili ya data ya chanzo daima iko kwenye folda sawa na Kichakataji chetu, basi njia tunayohitaji inaweza kuundwa kwa fomula ifuatayo:

=KUSHOTO(CELL("jina la faili");TAFUTA("[";CELL("jina la faili"))-1)&"Bidhaa 100 bora.xls"

au katika toleo la Kiingereza:

=KUSHOTO(KIINI(«jina la faili»); TAFUTA(«[«; KIINI(«jina la faili»))-1)&»Топ-100 товаров.xls»

... kazi iko wapi LEVSIMV (KUSHOTO) inachukua kipande cha maandishi kutoka kwa kiungo kamili hadi kwenye mabano ya mraba ya ufunguzi (yaani njia ya folda ya sasa), na kisha jina na upanuzi wa faili yetu ya data ya chanzo huunganishwa kwayo.

Parameterize njia katika swala

Mguso wa mwisho na muhimu zaidi unabaki - kuandika njia ya faili ya chanzo katika ombi Bidhaa 100 bora.xls, ikimaanisha kisanduku A2 cha jedwali letu la "smart" iliyoundwa vigezo.

Ili kufanya hivyo, hebu turudi kwenye hoja ya Hoja ya Nishati na tuifungue tena Mhariri wa hali ya juu tab Tathmini (Angalia - Mhariri wa hali ya juu). Badala ya mfuatano wa maandishi katika nukuu "E:Ripoti za mauzoJuu ya bidhaa 100.xlsx" Wacha tuanzishe muundo ufuatao:

Excel.CurrentWorkbook(){[Jina="Mipangilio"][Maudhui]0 {}[Njia ya data chanzo]

Wacha tuone inajumuisha nini:

• Excel.CurrentWorkbook() ni kazi ya lugha ya M ya kufikia yaliyomo kwenye faili ya sasa
• {[Jina="Mipangilio"][Maudhui] - hii ni parameta ya uboreshaji kwa kazi ya awali, inayoonyesha kwamba tunataka kupata yaliyomo kwenye jedwali la "smart" vigezo
• [Njia ya data chanzo] ni jina la safu katika jedwali vigezoambayo tunarejelea
  
• 0 {} ni nambari ya safu kwenye jedwali vigezoambayo tunataka kuchukua data. Kofia haihesabu na hesabu huanza kutoka sifuri, sio kutoka kwa moja.

Hiyo ndiyo yote, kwa kweli.

Inabakia kubofya Kumaliza na uangalie jinsi ombi letu linavyofanya kazi. Sasa, wakati wa kutuma folda nzima na faili zote mbili ndani ya PC nyingine, ombi litaendelea kufanya kazi na kuamua njia ya data moja kwa moja.

• Swali la Nguvu ni nini na kwa nini inahitajika wakati wa kufanya kazi katika Microsoft Excel
• Jinsi ya kuingiza kijisehemu cha maandishi kinachoelea kwenye Hoja ya Nguvu
• Kusanifu upya XNUMXD Crosstab kwa Jedwali la Gorofa lenye Hoja ya Nguvu