Ukushintshwa kombhalo ngobuningi kumbuzo wamandla ngohlu.Buthelela umsebenzi

Ungawushintsha kanjani umbhalo ngokushesha nangenqwaba ngokuhambisana nohlu lwereferensi ngamafomula - sesiwuhlungile kakade. Manje ake sizame ukukwenza kokuthi Umbuzo Wamandla.

Njengoba kuvame ukwenzeka yenza lo msebenzi ulula kakhulu kunokuchaza kungani iyasebenza, kodwa ake sizame ukukwenza kokubili 🙂

Ngakho-ke, sinamathebula amabili aguqukayo "ahlakaniphile" adalwe kusuka kububanzi obujwayelekile ngesinqamuleli sekhibhodi Ctrl+T noma iqembu Ikhaya – Fometha njengetafula (Ekhaya — Fometha Njengethebula):

Ngabiza itafula lokuqala Idatha, ithebula lesibili - Directorykusetshenziswa insimu Igama lethebula (Igama lethebula) ithebhu umakhi (Umklamo).

Umsebenzi: shintsha amakheli kuthebula Idatha zonke izenzakalo ezivela kukholamu Ukuthola Handbook kozakwabo abafanele abasuka kukholamu I-Substitute. Wonke umbhalo kumaseli kufanele uhlale ungathintwa.

Isinyathelo 1. Layisha uhla lwemibhalo ku-Power Query bese uluguqula lube uhlu

Ngemva kokusetha iseli esebenzayo kunoma iyiphi indawo kuthebula lesithenjwa, chofoza ithebhu Idatha (Usuku)noma kuthebhu Umbuzo Wamandla (uma unenguqulo endala ye-Excel futhi ufake Umbuzo Wamandla njengesengezo kuthebhu ehlukile) enkinobho Kusukela kuthebula/ububanzi (Kusuka kuThebula/Ububanzi).

Ithebula lesithenjwa lizolayishwa kusihleli sombuzo wamandla:

Ukuze ungaphazamisi, isinyathelo esengeziwe ngokuzenzakalelayo uhlobo olushintshiwe (Uhlobo Olushintshiwe) kuphaneli elungile, izinyathelo ezisetshenzisiwe zingasuswa ngokuphepha, kushiye isinyathelo kuphela Umthombo (Umthombo):

Manje, ukuze senze izinguquko ezengeziwe nokushintshanisa, sidinga ukushintsha leli thebula libe uhlu (uhlu).

Ukwehliswa okusemthethweni

• Ithebula iyinhlangano enezinhlangothi ezimbili ehlanganisa imigqa namakholomu amaningana.
• Rekhoda (Rekhoda) - Iyunithi yezinhlamvu ezinohlangothi olulodwa, ehlanganisa izinkambu eziningana-izakhi ezinamagama, isibonelo [Igama = “Masha”, Ubulili = “f”, Ubudala = 25]
  
• Uhlu – ikholomu enohlangothi olulodwa, ehlanganisa izakhi ezimbalwa, isibonelo {1, 2, 3, 10, 42} or {“Ukholo Lwethemba Uthando”}
  

Ukuxazulula inkinga yethu, sizoba nentshisekelo ngokuyinhloko ohlotsheni Uhlu.

Iqhinga lapha ukuthi izinto zohlu Ezikumbuzo Wamandla azikwazi ukuba izinombolo ezivinjelwe kuphela noma umbhalo, kodwa futhi nezinye izinhlu noma amarekhodi. Kusohlwini olunjalo olukhohlisayo (uhlu), oluhlanganisa amarekhodi (amarekhodi) lapho sidinga khona ukuvula umkhombandlela wethu. Ku-Power Query syntactic notation (okufakiwe kubakaki abayisikwele, izinhlu kubakaki abagoqekile) lokhu kungabukeka kanje:

{

    [ Thola = “ISt. Petersburg”, Faka esikhundleni = “ISt. Petersburg”] ,

    [ Thola = “ISt. Petersburg”, Faka esikhundleni = “ISt. Petersburg”] ,

    [ Thola = “Peter”, Faka esikhundleni = “St. Petersburg”] ,

njll

}

Uguquko olunjalo lwenziwa kusetshenziswa umsebenzi okhethekile wolimi lwe-M olwakhelwe ku-Power Query - Ithebula.ToRecords. Ukuwusebenzisa ngqo kubha yefomula, engeza lo msebenzi kukhodi yesinyathelo lapho Umthombo.

Bekuyi:

Ngemuva kwalokho:

Ngemva kokwengeza umsebenzi we-Table.ToRecords, ukubukeka kwetafula lethu kuzoshintsha - lizoshintsha libe uhlu lwamarekhodi. Okuqukethwe kwamarekhodi angawodwana kungabonwa ngezansi kwefasitelana lokubuka ngokuchofoza ingemuva leseli eduze kwanoma yiliphi igama. Qopha (kodwa hhayi ngegama elilodwa!)

Ukwengeza kulokhu okungenhla, kunengqondo ukwengeza enye i-stroke - ku-cache (buffer) uhlu lwethu oludaliwe. Lokhu kuzophoqa Umbuzo Wamandla ukuthi ulayishe uhlu lwethu lokubheka kanye ekhanda futhi singaphinde silubale futhi uma kamuva silufinyelela ukuze silumiselele. Ukuze wenze lokhu, bopha ifomula yethu komunye umsebenzi - Uhlu.Ibhafa:

Ukugcinwa kwesikhashana okunjalo kuzonikeza ukwanda okuphawulekayo kwejubane (izikhathi ezimbalwa!) Ngenani elikhulu ledatha yokuqala okufanele isulwe.

Lokhu kuqeda ukulungiswa kwebhukwana.

Kusele ukuchofoza Ikhaya - Vala futhi Ulayishe - Vala futhi Ulayishe ku... (Ekhaya — Vala&Layisha — Vala&Layisha ku..), khetha inketho Vele udale uxhumano (Dala uxhumano kuphela) bese ubuyela ku-Excel.

Isinyathelo 2. Ilayisha ithebula ledatha

Konke kuhlanzekile lapha. Njengangaphambili ngebhuku lereferensi, sisukuma siye kunoma iyiphi indawo etafuleni, chofoza ithebhu Idatha inkinobho Kusukela kuThebula/Ububanzi kanye netafula lethu Idatha ingena ku-Power Query. Isinyathelo esingezwe ngokuzenzakalelayo uhlobo olushintshiwe (Uhlobo Olushintshiwe) ungasusa futhi:

Azikho izenzo ezikhethekile zokulungiselela ezidingekayo ukuze zenziwe ngayo, futhi siqhubekela phambili entweni ebaluleke kakhulu.

Isinyathelo sesi-3. Yenza okushintshiwe usebenzisa Uhlu.Buthelela umsebenzi

Ake sengeze ikholomu ebaliwe etafuleni lethu ledatha sisebenzisa umyalo Ukwengeza Ikholomu – Ikholomu Ngokwezifiso (Engeza ikholomu — Ikholomu yangokwezifiso): bese ufaka igama lekholomu eyengeziwe efasiteleni elivulekayo (ngokwesibonelo, ikheli elilungisiwe) kanye nomsebenzi wethu womlingo Uhlu.Buthelela:

Kusele ukuchofoza OK - futhi sithola ikholomu enokushintshwa okwenziwe:

Qaphela ukuthi:

• Njengoba Umbuzo Wamandla uzwela kakhulu, akukho ukushintshwa emugqeni owandulela owedlule, ngoba ohlwini lwemibhalo sine-“SPb”, hhayi “SPb”.
• Uma kukhona uchungechunge oluncane oluncane oluzoshintshwa ngesikhathi esisodwa kudatha yomthombo (isibonelo, kulayini wesi-7 udinga ukushintsha kokubili okuthi “S-Pb” kanye “ne-Prospectus”), lapho-ke lokhu akudali izinkinga (ngokungafani nokufaka amafomula asuka indlela yangaphambili).
• Uma kungekho okushintshwayo kumbhalo womthombo (umugqa wesi-9), awekho amaphutha avelayo (ngokungafani, futhi, ekushintshweni kwamafomula).

Ijubane lesicelo esinjalo lihle kakhulu. Isibonelo, kuthebula ledatha yokuqala enosayizi wemigqa engu-5000, lo mbuzo ubuyekezwe esikhathini esingaphansi kwesekhondi (ngaphandle kokubhafa, noma kunjalo, cishe imizuzwana emi-3!)

Usebenza kanjani umsebenzi we-List.Accumulate

Empeleni, lokhu kungaba ukuphela (ukuba ngibhale, futhi wena ufunde) lesi sihloko. Uma ungafuni ukukwazi kuphela, kodwa futhi uqonde ukuthi isebenza kanjani “ngaphansi kwe-hood”, kuzodingeka ukuthi ungene ujule kancane emgodini onogwaja futhi ubhekane noHlu.Buthelela umsebenzi, owenze konke ukushintshwa kwenqwaba. sisebenzele.

I-syntax yalo msebenzi ithi:

=Uhlu.Buthelela(uhlu, imbewu, isixhumi)

lapho

• uhlu wuhlu esiphindaphinda izingxenye zalo. 
• imbewu - isimo sokuqala
• isixhumi – umsebenzi owenza umsebenzi othile (wezibalo, umbhalo, njll.) engxenyeni elandelayo yohlu futhi iqoqe umphumela wokucubungula ngokuguquguquka okukhethekile.
  

Ngokuvamile, i-syntax yokubhala imisebenzi ku-Power Query ibukeka kanjena:

(impikiswano1, ingxabano2, … ingxabanoN) => ezinye izenzo ezinezimpikiswano

Isibonelo, umsebenzi wokuhlanganisa ungamelwa kanje:

(a, b) => a + b

Ukuze Uhlu.Buthelela , lesi senzo sesiqoqiwe sinezimpikiswano ezimbili ezidingekayo (zingaqanjwa noma yini, kodwa amagama ajwayelekile isimo и zamanje, njengosizo olusemthethweni lwalo msebenzi, lapho:

• isimo - okuguquguqukayo lapho umphumela unqwabelana khona (inani layo lokuqala yilelo elishiwo ngenhla imbewu)
  
• zamanje – inani eliphindiwe elilandelayo ohlwini uhlu
  

Isibonelo, ake sibheke izinyathelo ze-logic yokwakhiwa okulandelayo:

=Uhlu.Buthelela({3, 2, 5}, 10, (isimo, samanje) => isimo + samanje)

Lokhu okwakamuva okuqoqiwe isimo inani Uhlu lwethu.Buthelela umsebenzi kanye nemiphumela njengomphumela:

Uma uphupha kancane, bese usebenzisa Uhlu.Buthelela umsebenzi, ungakwazi ukulingisa, isibonelo, umsebenzi we-Excel CONCATENATE (kumbuzo wamandla, i-analogue yawo ibizwa ngokuthi. Umbhalo.Hlanganisa) usebenzisa isisho:

Noma useshe inani eliphakeme kakhulu (ukulingiswa komsebenzi we-Excel's MAX, okubizwa ngokuthi Umbuzo Wamandla Uhlu.Ubukhulu):

Nokho, isici esiyinhloko soHlu.Buthelela ikhono lokucubungula hhayi kuphela umbhalo olula noma izinhlu zezinombolo njengezimpikiswano, kodwa izinto eziyinkimbinkimbi kakhulu - isibonelo, uhlu-olusuka-ezinhlwini noma izinhlu-ezisuka kumarekhodi (sawubona, Uhla lwemibhalo!)

Ake sibheke futhi ukwakhiwa okwenze ukushintshwa kwenkinga yethu:

Uhlu.Buthelela(Directory, [Ikheli], (isimo, samanje) => Umbhalo.Faka esikhundleni(isimo, samanje[Thola], okwamanje[Buyisela]) )

Kwenzekani ngempela lapha?

1. Njengevelu yokuqala (imbewu) sithatha umbhalo wokuqala ongacacile kukholamu [Ikheli] itafula lethu: 199034, eSt. Petersburg, eSt. Beringa, d. 1
2. Bese Uhlu.Buthelela iphindaphinda phezu kwezakhi zohlu ngayinye ngayinye - Handbook. Ingxenye ngayinye yalolu hlu irekhodi eliqukethe ipheya yezinkambu “Ongakuthola - Ongakushintshanisa ngakho” noma, ngamanye amazwi, umugqa olandelayo ohlwini lwemibhalo.
3. Umsebenzi we-accumulator ufaka okuguquguqukayo isimo inani lokuqala (ikheli lokuqala 199034, eSt. Petersburg, eSt. Beringa, d. 1) futhi yenza umsebenzi we-accumulator kuyo - umsebenzi wokushintsha kusetshenziswa umsebenzi ojwayelekile we-M Umbhalo.Faka esikhundleni (okufana nomsebenzi we-Excel we-SUBSTITUTE). I-syntax yayo ithi:

   Umbhalo.Buyisela(umbhalo wokuqala, esikufunayo, esifaka esikhundleni salokho)

   futhi lapha sinakho:

• isimo ikheli lethu elingcolile, elingaphakathi isimo (esuka lapho imbewu)
  
• okwamanje[Sesha] – inani lenkundla Ukuthola kusukela ekufakweni okuphindaphindiwe okulandelayo kohlu Directory, elele kokuguquguqukayo zamanje
• okwamanje[Faka esikhundleni] – inani lenkundla I-Substitute kusukela ekufakweni okuphindaphindiwe okulandelayo kohlu Directoryelele phakathi zamanje

Ngakho, ekhelini ngalinye, umjikelezo ogcwele wokubalwa kwayo yonke imigqa esohlwini lwemibhalo uyasetshenziswa isikhathi ngasinye, esikhundleni sombhalo ophuma kunkambu ethi [Thola] ngenani elivela kunkambu ethi [Faka esikhundleni].

Ngethemba ukuthi uwutholile umbono 🙂

• Inqwaba ishintsha umbhalo ohlwini kusetshenziswa amafomula
  
• Izinkulumo Ezivamile (RegExp) Embuzweni Wamandla