Kutsiviwa kwemavara akawanda muKubvunza Kwesimba neRondedzero.Unganisa basa

Maitiro ekukurumidza uye muhuwandu kutsiva zvinyorwa zvinoenderana nereferensi runyorwa nemafomula - isu takatozvirongedza. Zvino ngatiedze kuzviita muMasimba Query.

Sezvinowanzoitika ita basa iri riri nyore kupfuura kutsanangura sei inoshanda, asi ngatiedzei kuita ese ari maviri 🙂

Saka, isu tine matafura maviri e "smart" ane simba akagadzirwa kubva kune akajairwa marenji ane keyboard yekudimbudzira Ctrl+T kana timu Kumba – Fomati setafura (Kumba - Fomati seTafura):

Ndakadaidza tafura yekutanga Data, tafura yechipiri - dhairetoriuchishandisa munda Tafura zita (Zita repatafura) Tab Constructor (Dhizaini).

Basa: tsiva mumakero ari patafura Data zvese zvinoitika kubva pachikamu Kuwana Handbook kune avo anoenderana nawo anoenderana kubva pakoramu Substitute. Zvimwe zvinyorwa mumaseru zvinofanira kuramba zvisina kubatwa.

Danho 1. Rodha dhairekitori mukati Power Query uye worishandura kuita runyorwa

Mushure mekuseta iyo inoshanda sero kune chero nzvimbo mutafura yereferensi, tinya pane iyo tab Data (Zuva)kana kuti pane tab Simba Query (kana iwe uine yekare vhezheni yeExcel uye iwe wakaisa Power Query seyekuwedzera pane imwe tebhu) pane bhatani. Kubva patafura/renji (Kubva Tafura/Range).

Tafura yereferensi ichaiswa muPower Query query editor:

Kuti urege kupindira, danho rakawedzerwa otomatiki modified type (Changed Type) mupaneru yekurudyi, matanho akaiswa anogona kubviswa zvakachengeteka, achisiya chete nhanho mabviro (Chinhu):

Zvino, kuti tiite dzimwe shanduko uye kutsiva, isu tinofanirwa kushandura tafura iyi kuita runyorwa (rondedzero).

Lyrical digression

• tafura imbiri-dimensional array ine mitsetse yakawanda uye makoramu.
• Rekodha (Rekodha) - one-dimensional array-tambo, ine akati wandei minda-zvinhu zvine mazita, semuenzaniso. [Zita = “Masha”, Gender = “f”, Zera = 25]
  
• List - imwe-dimensional array-column, inosanganisira zvinhu zvakawanda, semuenzaniso {1, 2, 3, 10, 42} or {“Kutenda Tariro Rudo”}
  

Kugadzirisa dambudziko redu, isu tichanyanya kufarira mhando List.

Manomano apa ndeekuti rondedzero zvinhu muSimba Query hazvigone kungova banal manhamba kana zvinyorwa, asiwo mamwe rondedzero kana marekodhi. Iri mune rondedzero inonyepa kudaro (rondedzero), ine marekodhi (marekodhi) yatinofanira kushandura dhairekitori redu. MuPower Query syntactic notation (manyorero mumabhuraketi akaenzana, rondedzero mumabhuraketi akamonana) izvi zvinoita se:

{

    [ Tsvaga = “St. Petersburg”, Tsiva = “St. Petersburg”] ,

    [ Tsvaga = “St. Petersburg”, Tsiva = “St. Petersburg”] ,

    [ Tsvaga = “Petro”, Tsiva = “St. Petersburg”] ,

etc.

}

Shanduko yakadai inoitwa uchishandisa basa rakakosha reM mutauro wakavakirwa muSimba Query - Table.ToRecords. Kuti uishandise zvakananga mubhari yeformula, wedzera basa iri kune nhanho kodhi ipapo mabviro.

Zvanga zviri:

Mushure:

Mushure mekuwedzera Table.ToRecords basa, kutaridzika kwetafura yedu kuchachinja - inoshanduka kuva runyoro rwezvinyorwa. Zviri mukati marekodhi ega zvinogona kuoneka pazasi pepane rekuona nekudzvanya musero sero padhuze nechero izwi. rekodhi (asi kwete neshoko rimwe chete!)

Pamusoro pezviri pamusoro, zvine musoro kuwedzera imwe sitiroko - kune cache (buffer) yedu yakagadzirwa runyorwa. Izvi zvinomanikidza Power Query kurodha yedu yekutarisa runyorwa kamwe mundangariro uye kwete kuiverenga zvakare kana isu tichazoiwana kuti tiitsive. Kuti uite izvi, peta fomula yedu mune rimwe basa - List.Buffer:

Kuchengetedzwa kwakadaro kuchapa kuwedzera kunooneka kwekukurumidza (nekakati wandei!) Nehuwandu hukuru hwekutanga data kucheneswa.

Izvi zvinopedzisa kugadzirira kwebhuku rekushandisa.

Rinosara kudzvanya Imba - Vhara uye Rodha - Vhara uye Rodha ku... (Kumba - Vhara & Rodha - Vhara & Rodha ku..), sarudza imwe sarudzo Ingogadzira chinongedzo (Gadzira kubatana chete) uye dzokera kuExcel.

Nhanho 2. Kuisa tafura yedata

Zvese zvakapusa pano. Sekare nebhuku rereferenzi, isu tinosimuka kune chero nzvimbo mutafura, tinya pane tab Data bhatani Kubva Tafura/Range netafura yedu Data inopinda mukati Power Query. Yakawedzerwa nhanho modified type (Changed Type) unogonawo kubvisa:

Hapana zviito zvakakosha zvekugadzirira zvinodiwa kuti zviitwe nazvo, uye tinoenderera mberi kune chinhu chinonyanya kukosha.

Danho 3. Ita zvinotsiva uchishandisa List.Accumulate function

Ngatiwedzerei koramu yakaverengerwa kutafura yedu yedata tichishandisa murairo Kuwedzera Column - Custom Column (Wedzera koramu - Tsika mbiru): uye isa zita rekoramu yakawedzerwa pahwindo rinovhurwa (semuenzaniso, kero yakagadziriswa) uye basa redu remashiripiti List.Unganidza:

Rinosara kudzvanya OK - uye isu tinowana koramu ine zvinotsiva zvakaitwa:

Cherechedza kuti:

• Sezvo Power Query iri nyaya inonzwa, pakanga pasina kutsiva mumutsara wekupedzisira, nekuti mudhairekitori tine "SPb", kwete "SPb".
• Kana paine akati wandei ma substrings ekutsiva kamwechete mune sosi data (semuenzaniso, mumutsara wechinomwe unofanirwa kutsiva ese "S-Pb" uye "Prospectus"), saka izvi hazvigadziri chero matambudziko (kusiyana nekutsiva nemafomula kubva nzira yapfuura).
• Kana pasina chekutsiva mune chinyorwa chinyorwa (9th mutsara), saka hapana zvikanganiso zvinoitika (zvakasiyana, zvakare, kubva pakutsiviwa nemafomati).

Kumhanya kwechikumbiro chakadaro kwakanyanya, kwakanaka kwazvo. Semuenzaniso, kune tafura yedata yekutanga ine saizi ye5000 mitsara, mubvunzo uyu wakagadziridzwa mukati isingasviki sekondi (pasina buffering, nenzira, anenge masekonzi matatu!)

Mashandiro anoita List.Accumulate function

Muchidimbu, iyi inogona kunge iri magumo (kuti ini ndinyore, uye kuti iwe uverenge) chinyorwa ichi. Kana iwe uchida kwete kungokwanisa chete, asiwo kunzwisisa kuti inoshanda sei "pasi pehodhi", ipapo iwe uchafanirwa kunyura zvishoma mukati megomba retsuro uye kubata neRondedzero.Kuunganidza basa, iro rakaita zvose zvakawanda zvinotsiva. tishandire.

Syntax yebasa iri ndeiyi:

=Rondedzerwa.Unganidza(Pamazita, mbeu, kuunganidza)

apo

• Pamazita ndiyo rondedzero ine zvinhu zvatiri kudzokorora pamusoro. 
• mbeu - mamiriro ekutanga
• kuunganidza - basa rinoita kumwe kushanda (masvomhu, zvinyorwa, nezvimwewo) pane chimwe chinhu chinotevera chechinyorwa uye chinounganidza mugumisiro wekugadzirisa mune zvakasiyana-siyana.
  

Kazhinji, iyo syntax yekunyora mabasa muPower Query inoita seizvi:

(nharo1, nharo2, … nharoN) => zvimwe zviito zvine nharo

Semuenzaniso, iyo summary function inogona kumiririrwa se:

(a, b) => a + b

Pamusoro peChirongwa.Kuunganidzira , iyi accumulator basa ine nharo mbiri dzinodiwa (dzinogona kutumidzwa chero chinhu, asi mazita anowanzo mamiriro и mafambiro, semubatsiro wepamutemo webasa iri, uko:

• mamiriro - shanduko apo mhedzisiro inounganidzwa (yayo yekutanga kukosha ndiyo yataurwa pamusoro mbeu)
  
• mafambiro - iyo inotevera iterated kukosha kubva pane iyo rondedzero Pamazita
  

Semuenzaniso, ngatitarisei matanho eiyo logic yeinotevera kuvaka:

=Rondedzerwa.Unganidza({3, 2, 5}, 10, (nyika, ikozvino) => nyika + ikozvino)

Iyi ndiyo yazvino yakaunganidzwa mamiriro kukosha ndeyedu List.Accumulate basa uye zvabuda semugumisiro:

Kana iwe uchifungidzira zvishoma, zvino uchishandisa iyo List.Kuunganidza basa, unogona kutevedzera, semuenzaniso, basa reExcel CONCATENATE (mune Power Query, analogue yayo inonzi. Text.Combine) vachishandisa izwi rokuti:

Kana kutotsvaga iyo yakanyanya kukosha (kutevedzera yeExcel's MAX basa, iro muPower Query rinonzi List.Max):

Zvisineyi, chinhu chikuru cheRondedzero.Kuunganidzira kugona kugadzirisa kwete zvinyorwa zvakapfava chete kana zvinyorwa zvenhamba senharo, asi zvinhu zvakanyanya kuomarara - semuenzaniso, zvinyorwa-kubva pamazita kana zvinyorwa-kubva-marekodhi (mhoro, Dhairekitori!)

Ngatitarisei zvakare pakuvakwa kwakaita kutsiva mudambudziko redu:

List.Accumulate(dhairetori, [Kero], (nyika, yazvino) => Chinyorwa.Dzorera(nyika, yazvino[Tsvaga], yazvino[Tsiva]) )

Chii chaizvo chiri kuitika pano?

1. Sezvo kukosha kwekutanga (mbeu) tinotora rugwaro rwekutanga rusinganzwisisike kubva mukoramu [Kero] tafura yedu: 199034, St. Petersburg, St. Beringa, d. 1
2. Wobva wanyora.Kuunganidzira iterates pamusoro pezvinhu zvechinyorwa chimwe nechimwe - Handbook. Chinhu chega chega cheiyi chinyorwa chinyorwa chine mbiri yeminda "Zvekuwana - Zvekutsiva nazvo" kana, nemamwe mazwi, mutsara unotevera mudhairekitori.
3. Iyo accumulator basa inoisa mune inoshanduka mamiriro kukosha kwekutanga (kero yekutanga 199034, St. Petersburg, St. Beringa, d. 1) uye inoita accumulator basa pairi - kutsiva kushanda uchishandisa yakajairwa M-basa Text.Replace (inofanana neExcel's SUBSTITUTE basa). Syntax yayo inoti:

   Text.Replace(mavara ekutanga, zvatiri kutsvaga, zvatiri kutsiva nazvo)

   uye pano tine:

• mamiriro ndiyo kero yedu yakasviba, iyo iri mukati mamiriro (kubva ipapo mbeu)
  
• zvazvino[Tsvaga] - kukosha kwemunda Kuwana kubva pane inotevera inodzokororwa inodzokororwa dhairetori, iyo iri mukusiyana mafambiro
• zvazvino[Siva] - kukosha kwemunda Substitute kubva pane inotevera inodzokororwa inodzokororwa dhairetoriakarara mukati mafambiro

Saka, pakero yega yega, kutenderera kuzere kwekuverengwa kwemitsara yese mudhairekitori kunoitwa nguva yega yega, kutsiva zvinyorwa kubva kumunda we [Tsvaga] neukoshi kubva pa[Tsinza] ndima.

Ndinovimba wawana zano 🙂

• Kuwanda kunotsiva mavara murunyoro uchishandisa mafomula
  
• Nguva Dzose Matauriro (RegExp) mune Simba Query