Zamkatimu
Imodzi mwamavidiyo omwe amawonedwa kwambiri pa njira yanga ya YouTube ndi kanema wokhudza Flash Fill mu Microsoft Excel. Chofunika kwambiri cha chida ichi ndi chakuti ngati mukufuna kusintha deta yanu yochokera, muyenera kungoyamba kulemba zotsatira zomwe mukufuna kuti mufike pamzere woyandikana nawo. Pambuyo pamaselo angapo otayidwa pamanja (nthawi zambiri 2-3 ndi yokwanira), Excel "imvetsetsa" zosintha zomwe mukufuna ndikupitilira zomwe mwalemba, ndikumaliza ntchito yonse yovuta kwa inu:
The quintessence wa dzuwa. Batani lamatsenga "kuchita bwino" lomwe tonse timakonda kwambiri, sichoncho?
Ndipotu, pali analogue ya chida choterocho mu Power Query - kumeneko imatchedwa Mzere wochokera ku zitsanzo (Mndandanda wa Zitsanzo). Ndipotu, iyi ndi nzeru yaing'ono yokumba yomangidwa mu Power Query yomwe ingaphunzire mwamsanga kuchokera ku deta yanu ndikusintha. Tiyeni tiwone mwatsatanetsatane kuthekera kwake muzochitika zingapo zothandiza kuti timvetsetse komwe zingakhale zothandiza kwa ife muzochita zenizeni.
Chitsanzo 1. Kumata/kudula mawu
Tiyerekeze kuti tili ndi tebulo "lanzeru" ku Excel lomwe lili ndi data ya ogwira ntchito:
Kwezani mu Power Query m'njira yokhazikika - ndi batani Kuchokera pa Table/Range tsamba Deta (Deta - Kuchokera pa Table/Range).
Tiyerekeze kuti tifunika kuwonjezera ndime yokhala ndi mayina omaliza ndi oyambira kwa wogwira ntchito aliyense (Ivanov SV kwa wogwira ntchito woyamba, ndi zina). Kuti muthane ndi vutoli, mutha kugwiritsa ntchito imodzi mwa njira ziwiri:
- dinani kumanja pa mzati mutu ndi gwero deta ndi kusankha lamulo Onjezani gawo kuchokera ku zitsanzo (Onjezani gawo lazitsanzo);
- sankhani mizati imodzi kapena zingapo zokhala ndi data komanso pa tabu Kuwonjezera ndime sankhani gulu Mzere wochokera ku zitsanzo. Pano, pamndandanda wotsikira pansi, mutha kufotokoza ngati zigawo zonse kapena zosankhidwa zokha ziyenera kufufuzidwa.
Ndiye chirichonse chiri chophweka - mu ndime yomwe ikuwonekera kumanja, timayamba kuyika zitsanzo za zotsatira zomwe tikufuna, ndipo nzeru zopangira zomangidwa mu Power Query zimayesa kumvetsetsa malingaliro athu a kusintha ndikupitirizabe paokha:
Mwa njira, mutha kuyika zosankha zolondola m'maselo aliwonse agawoli, mwachitsanzo, osati pamwamba-pansi komanso mzere. Komanso, mutha kuwonjezera kapena kuchotsa zipilala zowunikira pambuyo pake pogwiritsa ntchito mabokosi omwe ali pamutu.
Samalani ndondomeko yomwe ili pamwamba pawindo - izi ndi zomwe Smart Power Query imapanga kuti tipeze zotsatira zomwe tikufuna. Izi, mwa njira, ndiye kusiyana kwakukulu pakati pa chida ichi ndi Kudzaza pompopompo mu Excel. Kudzaza pompopompo kumagwira ntchito ngati "bokosi lakuda" - samatiwonetsa malingaliro akusintha, koma amangopereka zotsatira zomwe zakonzedwa ndipo timazitenga mopepuka. Apa zonse zikuwonekera ndipo mutha kumvetsetsa bwino zomwe zikuchitika ndi deta.
Ngati muwona kuti Power Query "idagwira lingaliro", ndiye kuti mutha kukanikiza batani OK kapena njira yachidule ya kiyibodi Ctrl+Lowani - ndime yokhazikika yokhala ndi fomula yopangidwa ndi Power Query idzapangidwa. Mwa njira, imatha kusinthidwa mosavuta ngati gawo lopangidwa mwadongosolo (ndi lamulo Kuwonjezera Mzere - Mzere Wamakonda) podina chizindikiro cha gear kumanja kwa sitepeyo:
Chitsanzo 2: Mlandu ngati m’ziganizo
Ngati mudina kumanja pamutu womwe uli ndi mawu ndikusankha lamulo Transformation (Sinthani), ndiye mutha kuwona malamulo atatu omwe ali ndi udindo wosintha kaundula:
Zosavuta komanso zoziziritsa kukhosi, koma pamndandandawu, mwachitsanzo, ine ndekha ndakhala ndikusowa njira inanso - monga mu ziganizo, pamene capitalization (likulu) sikhala chilembo choyamba m'mawu aliwonse, koma chilembo choyamba mu cell, ndipo ena onse pamene Izi zikuwonetsedwa mu zilembo zazing'ono (zochepa).
Chosowachi ndi chosavuta kugwiritsa ntchito ndi luntha lochita kupanga Mizati yochokera ku zitsanzo - ingolowetsani zosankha zingapo kuti Power Query ipitirire mu mzimu womwewo:
Monga chilinganizo apa, Power Query imagwiritsa ntchito mulu wa ntchito Mawu.Pamwamba и Mawu.Pansi, kutembenuza malemba kukhala zilembo zazikulu ndi zazing'ono, motsatira, ndi ntchito Lembani.Yambani и Text.Mid - ma analogue a ntchito za Excel LEFT ndi PSTR, amatha kutulutsa kachigawo kakang'ono pamawu kuchokera kumanzere ndi pakati.
Chitsanzo 3. Kuloledwa kwa mawu
Nthawi zina, pokonza zomwe mwalandira, zimakhala zofunikira kusinthanso mawu m'maselo motsatizana. Zachidziwikire, mutha kugawa gawolo m'magawo osiyana a mawu ndi olekanitsa ndikumangiriranso mu dongosolo lomwe mwatchulidwa (musaiwale kuwonjezera mipata), koma mothandizidwa ndi chida. Mzere wochokera ku zitsanzo zonse zikhala zosavuta:
Chitsanzo 4: Manambala okha
Ntchito ina yofunika kwambiri ndikutulutsa manambala (manambala) kuchokera mu cell. Monga kale, mutatsitsa deta mu Power Query, pitani ku tabu Kuwonjezera ndime - Mzere wochokera ku zitsanzo ndikudzaza ma cell angapo pamanja kuti pulogalamuyo imvetsetse zomwe tikufuna kupeza:
Bingo!
Apanso, ndikofunikira kuyang'ana pamwamba pazenera kuti muwonetsetse kuti Query adapanga formula molondola - apa ili ndi ntchito. Mawu. Sankhani, yomwe, monga momwe mungaganizire, imachotsa zilembo zomwe zaperekedwa kuchokera kutsamba loyambira malinga ndi mndandanda. Pambuyo pake, mndandandawu, ndithudi, ukhoza kusinthidwa mosavuta mu bar ya formula ngati kuli kofunikira.
Chitsanzo 5: Malemba okha
Mofananamo ndi chitsanzo chapitachi, mukhoza kutulutsa ndi mosemphanitsa - malemba okha, kuchotsa manambala onse, zizindikiro zopumira, ndi zina zotero.
Pankhaniyi, ntchito yomwe ili yotsutsana kale ndi tanthauzo imagwiritsidwa ntchito - Text.Chotsani, yomwe imachotsa zilembo kuchokera ku chingwe choyambirira malinga ndi mndandanda woperekedwa.
Chitsanzo 6: Kuchotsa deta muphala la zilembo ndi nambala
Kufunsa kwa Mphamvu kungathandizenso pazovuta kwambiri, pamene mukufuna kuchotsa chidziwitso chothandiza kuchokera ku phala la alphanumeric mu selo, mwachitsanzo, pezani nambala ya akaunti kuchokera kukufotokozera cholinga cha malipiro pa banki:
Zindikirani kuti Power Query yopangidwa yotembenuza fomula ikhoza kukhala yovuta kwambiri:
Kuti muwerenge komanso kumvetsetsa bwino, itha kusinthidwa kukhala mawonekedwe anzeru kwambiri pogwiritsa ntchito ntchito yaulere yapaintaneti. Power Query Formatter:
Chinthu chothandiza kwambiri - kulemekeza kwa omwe adapanga!
Chitsanzo 7: Kutembenuza madeti
chida Mzere wochokera ku zitsanzo ingagwiritsidwenso ntchito pazipilala zamasiku kapena nthawi. Mukalowetsa manambala oyamba atsiku, Power Query iwonetsa mndandanda wazonse zomwe mungasinthire:
Chifukwa chake mutha kusintha tsiku loyambirira kukhala mtundu uliwonse wachilendo, monga "tsiku la mwezi wachaka":
Chitsanzo 8: Magulu
Ngati tigwiritsa ntchito Mzere wochokera ku zitsanzo pagawo lokhala ndi manambala, zimagwira ntchito mosiyana. Tiyerekeze kuti tili ndi zotsatira zoyezetsa antchito zomwe zalowetsedwa mu Power Query (ziwerengero zoyambira pa 0-100) ndipo timagwiritsa ntchito makwerero otsatirawa:
- Masters - omwe adagoletsa kuposa 90
- Akatswiri - adapeza 70 mpaka 90
- Ogwiritsa ntchito - kuyambira 30 mpaka 70
- Oyamba - omwe adagoletsa zosakwana 30
Ngati tiwonjeza ndime kuchokera pazitsanzo pamndandanda ndikuyamba kukonza magawowa pamanja, ndiye posachedwa Power Query itenga lingaliro lathu ndikuwonjezera ndime yokhala ndi chilinganizo, pomwe ogwiritsa ntchito amalumikizana wina ndi mnzake. if logic idzakhazikitsidwa, yofanana kwambiri ndi yomwe tikufuna:
Apanso, simungathe kukanikiza zinthu mpaka kumapeto, koma dinani OK ndiyeno konzani zikhalidwe zomwe zili kale mu formula - zimathamanga motere:
Mawuwo
Ndithu chida Mzere wochokera ku zitsanzo si "mapiritsi amatsenga" ndipo, posakhalitsa, padzakhala zinthu zomwe sizili zoyenera kapena makamaka zonyalanyazidwa za "famu yogwirizana" mu deta, pamene Power Query idzalephera ndipo sitingathe kuchita zomwe tikufuna. molondola kwa ife. Komabe, ngati chida chothandizira, ndichabwino kwambiri. Kuphatikiza apo, powerenga mafomu omwe adapanga, mutha kukulitsa chidziwitso chanu cha ntchito za chilankhulo cha M, chomwe chidzakhala chothandiza mtsogolo.
- Kuyika Mawu Okhala ndi Mawu Okhazikika (RegExp) mu Power Query
- Kusaka kwa mawu osamveka mu Power Query
- Kudzaza kwa Flash mu Microsoft Excel