Sakani mawu ofunika m'mawu

Kusaka mawu osakira m'mawu oyambira ndi imodzi mwantchito zofala kwambiri mukamagwira ntchito ndi data. Tiyeni tiwone yankho lake m'njira zingapo pogwiritsa ntchito chitsanzo chotsatirachi:

Tiyerekeze kuti inu ndi ine tili ndi mndandanda wa mawu osakira - mayina amtundu wamagalimoto - ndi tebulo lalikulu la mitundu yonse ya zida zosinthira, pomwe mafotokozedwe nthawi zina amatha kukhala ndi mtundu umodzi kapena zingapo nthawi imodzi, ngati gawo lake likukwanira kuposa imodzi. mtundu wagalimoto. Ntchito yathu ndikupeza ndikuwonetsa mawu osakira onse omwe apezeka m'maselo oyandikana nawo kudzera pagulu lopatukana (mwachitsanzo, koma).

Njira 1. Funso la Mphamvu

Inde, choyamba timatembenuza matebulo athu kukhala amphamvu ("anzeru") pogwiritsa ntchito njira yachidule ya kiyibodi Ctrl+T kapena malamulo Kunyumba - Pangani ngati tebulo (Kunyumba - Pangani Monga Table), apatseni mayina (mwachitsanzo Masampampuи Zida zobwezeretsera) ndikuyika chimodzi ndi chimodzi mu Power Query editor posankha pa tabu Deta - Kuchokera pa Table/Range (Deta - Kuchokera pa Table/Range). Ngati muli ndi mitundu yakale ya Excel 2010-2013, pomwe Power Query imayikidwa ngati chowonjezera chosiyana, ndiye kuti batani lomwe mukufuna lidzakhala pa tabu. Kufunsa Mphamvu. Ngati muli ndi mtundu watsopano wa Excel 365, ndiye dinani batani Kuchokera pa Table/Range kuitanidwa kumeneko tsopano Ndi masamba (Kuchokera pa Mapepala).

Pambuyo pokweza tebulo lililonse mu Power Query, timabwereranso ku Excel ndi lamulo Kunyumba — Tsekani ndikutsegula — Tsekani ndikutsegula ku… — Pangani kulumikizana kokha (Kunyumba — Tsekani & Kwezani — Tsekani & Kwezani ku… — Ingopangani kulumikizana).

Tsopano tiyeni tipange zobwereza Zida zobwezeretserapodina kumanja pa izo ndikusankha Pempho lobwereza (Funso lobwereza), kenako sinthaninso pempho lomwe mwapeza kuti Zotsatira ndipo tidzapitiriza kugwira naye ntchito.

Logic ya zochita ndi izi:

1. Pa Advanced tabu Kuwonjezera ndime sankhani gulu Mzati mwamakonda (Onjezani gawo - Mzere Wamakonda) ndipo lowetsani fomula = Mitundu. Pambuyo kuwonekera pa OK tidzalandira gawo latsopano, pomwe mu selo iliyonse mudzakhala tebulo lokhala ndi mndandanda wa mawu athu - automaker brand:

   

2. Gwiritsani ntchito batani lokhala ndi mivi iwiri pamutu wagawo lowonjezeredwa kuti mukulitse magome onse okhala. Nthawi yomweyo, mizere yokhala ndi mafotokozedwe a zida zosinthira idzachulukirachulukira ndi kuchuluka kwamitundu, ndipo titha kupeza mitundu yonse iwiri yophatikiza ya "spare part-brand":

   

3. Pa Advanced tabu Kuwonjezera ndime sankhani gulu Conditional column (Conditional column) ndikukhazikitsa mawonekedwe owonera kupezeka kwa mawu osakira (mtundu) m'mawu oyambira (mafotokozedwe agawo):

   

4. Kuti nkhani yosaka ikhale yosakhudzidwa, yonjezerani pamanja mkangano wachitatu mu bar ya formula Fananizani.OrdinalIgnoreCase ku ntchito yowunika zochitika Mawu.Muli (ngati fomula sikuwoneka, ndiye kuti ikhoza kuyatsidwa pa tabu Review):

   

5. Timasefa tebulo lomwe likubwera, ndikusiya gawo lomaliza, mwachitsanzo, ndikuchotsa gawo losafunika Zochitika.
6. Kuyika mafotokozedwe ofanana ndi lamulo Gulu ndi tsamba Transformation (Sinthani - Gulu ndi). Monga ntchito yophatikiza, sankhani Mizere yonse (Mizere yonse). Pazotulutsa, timapeza mzati wokhala ndi matebulo, omwe ali ndi tsatanetsatane wa gawo lililonse lopatula, kuphatikiza mtundu wa opanga makina omwe timafunikira:

   

7. Kuti muchotse magiredi pagawo lililonse, onjezani gawo lina lowerengeredwa pa tabu Kuwonjezera Mzere - Mzere Wamakonda (Onjezani gawo - Mzere Wamakonda) ndikugwiritsa ntchito chilinganizo chokhala ndi tebulo (zili patsamba lathu tsatanetsatane) ndi dzina lachigawo chochotsedwa:

   

8. Timadina batani ndi mivi iwiri pamutu wagawo lotsatira ndikusankha lamulo Chotsani mfundo (Chotsani mitengo)kuti mutulutse masitampu okhala ndi zilembo za delimiter zomwe mukufuna:

   

9. Kuchotsa gawo losafunika tsatanetsatane.
10. Kuti tiwonjezere patebulo lotsatira zigawo zomwe zidasowa kuchokera pamenepo, pomwe palibe zilembo zomwe zidapezeka muzofotokozera, timachita njira yophatikizira funsolo. chifukwa ndi pempho loyambirira Zida zobwezeretsera batani Gwirizanitsani tsamba Kunyumba (Kunyumba - Gwirizanitsani mafunso). Mtundu wamalumikizidwe - Kunja Kumanja Kumanja (Kulumikizana kunja kumanja):

    

11. Zomwe zatsala ndikuchotsa zipilala zowonjezera ndikusinthiranso-kusuntha zotsalazo - ndipo ntchito yathu yathetsedwa:

    
    

Njira 2. Zolemba

Ngati muli ndi mtundu wa Excel 2016 kapena mtsogolo, ndiye kuti vuto lathu litha kuthetsedwa mwadongosolo komanso lokongola kwambiri pogwiritsa ntchito ntchito yatsopanoyi. GWANIZANI (TEXTJOIN):

Lingaliro la formula iyi ndi losavuta:

• ntchito FUFUZANI (PEZANI) imayang'ana kupezeka kwa mtundu uliwonse motsatira zomwe zafotokozedwera panopo ndikubweretsanso nambala ya sikelo ya chizindikirocho, kuyambira pomwe mtunduwo unapezedwa, kapena cholakwika #VALUE! ngati chizindikirocho sichinafotokozedwe.
• Ndiye kugwiritsa ntchito IF (NGATI) и EOSHIBKA (ISERROR) timasintha zolakwikazo ndi chingwe chopanda kanthu "", ndi manambala a ordinal a zilembo ndi mayina amtunduwo.
• Maselo opanda kanthu omwe amabwera ndi mitundu yopezeka amasonkhanitsidwa mu chingwe chimodzi kudzera pagulu lolekanitsa lomwe laperekedwa pogwiritsa ntchito ntchitoyi. GWANIZANI (TEXTJOIN).

Kufananiza Magwiridwe ndi Mphamvu Yamafunso Kuthamangitsidwa kwa Speedup

Pakuyesa magwiridwe antchito, tiyeni titenge tebulo la mafotokozedwe a magawo 100 ngati data yoyamba. Pa izo timapeza zotsatirazi:

• Nthawi yowerengeranso ndi ma formula (Njira 2) - 9 sec. mukayamba kukopera fomula ku gawo lonse ndi 2 sec. mobwerezabwereza (kusokoneza kumakhudza, mwina).
  
• Nthawi yosinthika ya funso la Power Query (Njira 1) ndiyoipa kwambiri - masekondi 110.
  

Zachidziwikire, zambiri zimadalira zida za PC inayake ndi mtundu wokhazikitsidwa wa Office ndi zosintha, koma chithunzi chonse, ndikuganiza, chikuwonekera bwino.

Kuti mufulumizitse funso la Power Query, tiyeni tiyike pa tebulo loyang'ana Masampampu, chifukwa sichisintha poyankha mafunso ndipo sikofunikira kuti muwerengenso nthawi zonse (monga Power Query de facto imachitira). Kwa izi timagwiritsa ntchito Table.Buffer kuchokera ku chinenero cha Power Query chomangidwa mkati M.

Kuti muchite izi, tsegulani funso Zotsatira ndi pa tabu Review kanikizani batani Advanced Editor (Onani - Advanced Editor). Pawindo lomwe limatsegulidwa, onjezerani mzere wokhala ndi kusintha kwatsopano Marko 2, yomwe idzakhala mtundu wa buffer wa chikwatu chathu cha automaker, ndipo gwiritsani ntchito kusinthaku kwatsopano pambuyo pake pamafunso awa:

Pambuyo pa kukonzanso koteroko, kufulumira kwa pempho lathu kumawonjezeka pafupifupi nthawi 7 - mpaka masekondi 15. Chinthu chosiyana kwambiri 🙂

• Kusaka kwa mawu osamveka mu Power Query
• Kusintha mawu ambiri ndi mafomula
• Kusintha mawu ambiri mu Power Query ndi List.Accumulate ntchito