Mukati
Yese yemhando yepamusoro yekutsvaga uye mhando yekutsiva mabasa VPR (VLOOKUP), GPR (HLOOKUP), ZVAKAWANDA AKAFUMURWA (MATCH) uye avo vakaita saivo vane chinhu chimwe chakakosha - ivo vanotsvaga kubva pakutanga kusvika kumagumo, kureva kuruboshwe-kurudyi kana kumusoro-kusvika-pasi mune dhata. Pakangowanikwa mutambo wekutanga wekufananidza, kutsvaga kunomira uye chete chiitiko chekutanga chechinhu chatinoda chinowanikwa.
Chii chatinofanira kuita kana tisingadi kuwana kwete yekutanga, asi yekupedzisira kuitika? Semuenzaniso, kutengeserana kwekupedzisira kwemutengi, kubhadhara kwekupedzisira, kurongeka kwazvino, nezvimwewo?
Method 1: Kutsvaga Yekupedzisira Row ine Array Formula
Kana iyo tafura yepakutanga isina koramu ine zuva kana serial nhamba yemutsara (kurongeka, kubhadhara ...), saka basa redu nderekutsvaga mutsara wekupedzisira unogutsa mamiriro akapihwa. Izvi zvinogona kuitwa neinotevera array formula:
Pano:
- basa IF (KANA) inotarisa maseru ese muchikamu chimwe nechimwe kasitoma uye inoratidza nhamba yemutsara kana iine zita ratinoda. Nhamba yemutsara papepa inopiwa kwatiri nebasa racho LINE (MUTSARA), asi sezvo tichida nhamba yemutsara mutafura, tinofanirawo kubvisa 1, nokuti tine musoro mutafura.
- Zvadaro basa MAX (MAX) inosarudza huwandu hunokosha kubva pane yakaumbwa yenhamba dzemutsara, kureva nhamba yemutsara wekupedzisira wemutengi.
- basa INDEX (INDEX) inodzorera zviri mukati mesero ine nhamba yekupedzisira yakawanikwa kubva kune chero imwe inodiwa tafura tafura (Kodhi yekodhi).
Zvose izvi zvinofanira kuiswa se rondedzero fomula, kureva:
- MuHofisi 365 ine zvigadziriso zvazvino zvakaiswa uye tsigiro yeane simba arrays, unogona kungodzvanya pinda.
- Mune mamwe mavhezheni ese, mushure mekupinda fomula, iwe uchafanirwa kudzvanya iyo keyboard yekudimbudzira Ctrl+kusuduruka+pinda, iyo inozongowedzera curly braces kwairi mune formula bar.
Method 2: Reverse lookup neiyo itsva LOOKUP basa
Ndakatonyora chinyorwa chakareba nevhidhiyo nezvechinhu chitsva ONA (XLOOKUP), iyo yakabuda mushanduro dzichangoburwa dzeHofisi kutsiva yekare VLOOKUP (VLOOKUP). Nerubatsiro rweBROWSE, basa redu rinogadziriswa zvekutanga, nekuti. yebasa iri (kusiyana neVLOOKUP), unogona kunyatso kuseta nzira yekutsvaga: kumusoro-pasi kana kuzasi-kumusoro - nharo yayo yekupedzisira (-1) ine chekuita neizvi:
Nzira 3. Tsvaga tambo ine zuva razvino
Kana mune iyo sosi data isu tine koramu ine serial nhamba kana zuva rinoita basa rakafanana, saka basa rinogadziriswa - isu hatifanirwe kuwana kwete yekupedzisira (yakaderera) mutsara ine mechi, asi mutsara neazvino ( maximum) date.
Ini ndakatokurukura zvakadzama maitiro ekuita izvi uchishandisa classic mabasa, uye ikozvino ngatiedze kushandisa simba reiyo dynamic array mabasa. Kuti runako rwakakura uye ruve nyore, isu tinoshandurawo tafura yepakutanga kuita "smart" tafura tichishandisa nzira yekudimbudzira keyboard Ctrl+T kana mirairo Kumba – Fomati setafura (Kumba - Fomati seTafura).
Nerubatsiro rwavo, iyi "mhondi yakaroora" inogadzirisa dambudziko redu nenyasha:
Pano:
- Basa kutanga FILTER (SEFA) anosarudza chete mitsara kubva patafura yedu apo mukoromo kasitoma – zita ratinoda.
- Zvadaro basa giredha (SORT) anoronga mitsara yakasarudzwa nezuva muhurongwa hwekudzika, nedhiri ichangoburwa pamusoro.
- basa INDEX (INDEX) inobvisa mutsara wekutanga, kureva kuti inodzorera kutengesa kwekupedzisira kwatinoda.
- Uye, pakupedzisira, iyo yekunze FILTER basa inobvisa yakawedzera 1st uye 3rd makoramu kubva pane zvabuda (Kodhi yekodhi и kasitoma) uye inosiya chete zuva nemari. Nokuda kweizvi, mutsara wezvinyorwa zvinoshandiswa. {0;1;0;1}, tichitsanangura kuti ndeapi makoramu atinoda (1) kana kuti hatidi (0) kuratidza.
Nzira 4: Kutsvaga Yekupedzisira Match mune Simba Mubvunzo
Zvakanaka, nekuda kwekukwana, ngatitarisei mhinduro kudambudziko redu rekutsvaga reverse tichishandisa Power Query add-in. Nekubatsira kwake, zvinhu zvose zvinogadziriswa nokukurumidza uye zvakanaka.
1. Ngatishandure tafura yedu yepakutanga kuita "smart" tichishandisa nzira yekudimbudzira keyboard Ctrl+T kana mirairo Kumba – Fomati setafura (Kumba - Fomati seTafura).
2. Irodze muPower Query nebhatani Kubva Tafura/Range Tab Data (Data - Kubva Tafura/Range).
3. Isu tinoronga (kuburikidza neinodonhedza-pasi runyoro rwesefa mumusoro) tafura yedu mukudzika kurongeka kwezuva, kuitira kuti zvichangobva kuitika kutengeserana zviri pamusoro.
4… Mu tab Kuchinja sarudza timu Gadzirira ne (Shandura - Boka Ne) uye isa boka nevatengi, uye sebasa rekuunganidza, sarudza sarudzo Mitsetse yose (Mitsara yese). Iwe unogona kutumidza iyo nyowani nyowani chero chaunoda - semuenzaniso Details.
Mushure mekuita mapoka, tichawana runyorwa rwemazita akasiyana evatengi vedu uye muchikamu Details - matafura ane zvese zvekutengeserana kweumwe neumwe wavo, uko mutsara wekutanga uchave wazvino kutengeserana, ndizvo zvatinoda:
5. Wedzera koramu itsva yakaverengerwa nebhatani Custom column Tab Wedzera ikholamu (Wedzera koramu - Wedzera tsika koramu)uye isa iyo inotevera fomula:
pano Details - iyi ndiyo mbiru yatinotora matafura nevatengi, uye 0, XNUMX, XNUMX {} ndiyo nhamba yemutsara watinoda kutora (mutsara wenhamba muSimba Query inotanga kubva pazero). Tinowana koramu ine zvinyorwa (rekodhi), uko kunopinda kwega kwega kuri mutsara wekutanga kubva patafura yega yega:
Inosara yekuwedzera zviri mukati meese marekodhi nebhatani rine miseve miviri mumusoro wekoromo Last deal kusarudza makoramu aunoda:
... wobva wadzima iyo column iyo isingachadiwi Details nekudzvanya-kurudyi pane zita rayo - Bvisa mbiru (Bvisa makoramu).
Mushure mekuisa zvawanikwa papepa kuburikidza Musha - Vhara uye takura - Vhara uye urodze mukati (Kumba - Vhara & Rodha - Vhara & Rodha ku…) tichawana tafura yakanaka kudaro ine runyorwa rwezvichangobva kuitika, sezvataida:
Paunoshandura iyo sosi data, haufanirwe kukanganwa kugadzirisa zvawanikwa nekudzvanya-kurudyi pazviri - iwo murairo Gadziridza & Chengeta (Refresh) kana nzira yekudimbudzira keyboard Ctrl+alt+F5.
- Basa reLOOKUP dzinza reVLOOKUP
- Maitiro ekushandisa iyo itsva dynamic array mabasa SORT, FILTER, uye UNIC
- Kutsvaga yekupedzisira isiri-isina blank sero mumutsara kana koramu ine basa reLOOKUP