Mukati
Mukuita, kazhinji pane zviitiko apo iwe neni tinoda kuwana kukosha kwepedyo museti (tafura) zvine chekuita nenhamba yakapihwa. Zvinogona kuva, semuenzaniso:
- Calculation of discount zvichienderana nehuwandu.
- Kuverengera kwehuwandu hwemabhonasi zvichienderana nekuitwa kwechirongwa.
- Kuverengerwa kwemitengo yekutumira zvinoenderana nedaro.
- Kusarudzwa kwemidziyo yakakodzera yezvinhu, nezvimwe.
Uyezve, kutenderedza kunogona kudiwa kumusoro nekudzika, zvichienderana nemamiriro ezvinhu.
Kune nzira dzakawanda - dziri pachena uye dzisiri pachena - kugadzirisa dambudziko rakadaro. Ngatitarisei kwavari zvakatevedzana.
Kutanga, ngatimbofungidzirai mutengesi anopa zvidzikiso paholesale, uye chikamu chekuderedzwa chinoenderana nehuwandu hwezvinhu zvakatengwa. Semuenzaniso, kana uchitenga zvidimbu zvinopfuura 5, 2% discount inopiwa, uye kana uchitenga kubva pazvikamu makumi maviri - kare 20%, nezvimwewo.
Nzira yekukurumidza uye nerunako kuverenga iyo yekuderedzwa muzana paunopinda muhuwandu hwezvinhu zvakatengwa?
Nzira 1: Nested IFs
Nzira kubva kune dzakatevedzana "chii chiripo kufunga - iwe unofanirwa kusvetuka!". Kushandisa nested mabasa IF (KANA) kutarisa sequentially kana kukosha kwesero kuchiwira mune imwe neimwe yenguva uye kuratidza kuderedzwa kweiyo inoenderana renji. Asi iyo formula mune iyi kesi inogona kuve yakaoma kwazvo:
Ini ndinofunga zviri pachena kuti kugadzirisa "chikara chidhori" chakadaro kana kuedza kuwedzera mamwe mamiriro matsva kwairi mushure menguva kunonakidza.
Mukuwedzera, Microsoft Excel ine muganho wekugara weIF basa - 7 nguva muzvinyorwa zvekare uye 64 nguva muzvinyorwa zvitsva. Ko kana uchida zvimwe?
Nzira 2. VLOOKUP ine maonero epakati
Iyi nzira inonyanya compact. Kuti uverenge chikamu chedhiresi, shandisa basa rengano VPR (VLOOKUP) mune fungidziro yekutsvaga maitiro:
apo
- B4 - kukosha kwehuwandu hwezvinhu mukutengeserana kwekutanga kwatiri kutsvaga kuderedzwa
- $G$4:$H$8 - chinongedzo kune tafura yekuderedzwa - pasina "musoro" uye nemakero akaiswa nechiratidzo chemadhora.
- 2 - iyo ordinal nhamba yekoramu mutafura yekuderedzwa kwatinoda kuwana mutengo wekuderedzwa
- TRUE - apa ndipo panovigwa "imbwa". Kana seyekupedzisira basa nharo VPR tsanangura KUNYEPA (NHEMA) kana 0, ipapo basa richatsvaga Mutambo wakaoma muhuwandu hwehuwandu (uye kwatiri ichapa #N / A kukanganisa, sezvo pasina kukosha 49 mutafura yekuderedza). Asi kana pane kudaro KUNYEPA nyora TRUE (CHOKWADI) kana 1, ipapo basa racho harizotarisi kune chaiyo, asi madiki ari pedyo kukosha uye ichatipa iyo muzana yekuderedzwa yatinoda.
Iyo yakaderera yeiyi nzira kudiwa kwekugadzirisa tafura yekuderedzwa mukukwira kurongeka nekoramu yekutanga. Kana pasina kurongeka kwakadaro (kana kunoitwa mukurongeka), saka fomula yedu haishande:
Saizvozvo, nzira iyi inogona chete kushandiswa kuwana iri pedyo diki kukosha. Kana iwe uchida kuwana iyo iri pedyo yakakura, saka iwe unofanirwa kushandisa imwe nzira.
Method 3. Kutsvaga iri pedyo nekukura uchishandisa INDEX uye MATCH mabasa
Zvino ngatitarisei dambudziko redu kubva kune rimwe divi. Ngatitii isu tinotengesa akati wandei mamodheru emapombi emaindasitiri emhando dzakasiyana siyana. Tafura yekutengesa kuruboshwe inoratidza simba rinodiwa nemutengi. Tinofanira kusarudza pombi yesimba riri pedyo kana kuti rakaenzana, asi kwete pasi pezvinodiwa neprojekti.
Basa reVLOOKUP haribatsire pano, saka uchafanirwa kushandisa analogue yayo - boka re INDEX mabasa. (INDEX) uye ZVAKAWANDA AKAFUMURWA (MATCH):
Pano, basa reMATCH rine nharo yekupedzisira -1 inoshanda nenzira yekuwana kukosha kukuru kwepedyo, uye INDEX basa rinobva ratora zita remuenzaniso watinoda kubva pamutsara wakatarisana.
Nzira 4. Basa idzva ONA (XLOOKUP)
Kana iwe uine vhezheni yeHofisi 365 ine zvese zvigadziriso zvakaiswa, ipapo panzvimbo yeVLOOKUP (VLOOKUP) unogona kushandisa analogue yayo - iyo VIEW basa (XLOOKUP), iyo yandatoongorora zvakadzama:
Pano:
- B4 - kukosha kwekutanga kwehuwandu hwechigadzirwa chatiri kutsvaga kuderedzwa
- $G$4:$G$8 - nzvimbo yatiri kutsvaga machisi
- $H$4:$H$8 - huwandu hwemhedzisiro kubva kwaunoda kudzorera kuderedzwa
- nharo yechina (-1) inosanganisira kutsvaga nhamba diki iri padyo yatinoda pachinzvimbo cheinofanana.
Izvo zvakanakira nzira iyi ndezvekuti hapana chikonzero chekugadzirisa tafura yekuderedzwa uye kugona kutsvaga, kana zvichidikanwa, kwete chete diki diki, asiwo iri pedyo yakakura kukosha. Nharo yekupedzisira munyaya iyi ichave 1.
Asi, zvinosuruvarisa, havasi vese vane chimiro ichi - varidzi vanofara veHofisi 365 chete.
Nzira 5. Power Query
Kana iwe usati wajairana neiyo ine simba uye yemahara Power Query yekuwedzera-muExcel, saka iwe uri pano. Kana iwe watojaira, saka ngatiedze kuishandisa kugadzirisa dambudziko redu.
Ngatitangei kuita rimwe basa rekugadzirira:
- Ngatishandure matafura edu ekutanga kuita ane simba (smart) tichishandisa nzira yekudimbudzira keyboard Ctrl+T kana timu Kumba – Fomati setafura (Kumba - Fomati seTafura).
- Kuti vajekese, ngativape mazita. Sales и Discounts Tab Constructor (Dhizaini).
- Rodha imwe neimwe yematafura uchichinjana kuita Power Query uchishandisa bhatani Kubva Tafura/Range Tab Data (Data - Kubva patafura / renji). Mushanduro dzichangoburwa dzeExcel, bhatani iri rakatumidzwa zita rekuti Nemashizha (Kubva pepa).
- Kana matafura aine mazita akasiyana emakoroni ane huwandu, semumuenzaniso wedu ("Huwandu hwezvinhu" uye "Huwandu kubva ..."), zvino vanofanirwa kupihwa zita rekuti Power Query vopihwa zvakafanana.
- Mushure meizvozvo, unogona kudzokera kuExcel nekusarudza murairo muPower Query edhita hwindo Imba - Vhara uye Rodha - Vhara uye Rodha mukati... (Kumba - Vhara & Rodha - Vhara & Rodha ku…) uyezve sarudzo Ingogadzira chinongedzo (Gadzira kubatana chete).
- Ipapo iyo inofadza zvikuru inotanga. Kana iwe uine ruzivo muSimba Query, saka ini ndinofungidzira kuti imwezve mutsara wepfungwa unofanirwa kunge uri munzira yekubatanidza aya matafura maviri nemubvunzo wekujoinha (kubatanidza) a la VLOOKUP, sezvazvaive zvakaita munzira yapfuura. Muchokwadi, isu tichada kusanganisa mune yekuwedzera modhi, izvo zvisiri pachena zvachose pekutanga kuona. Sarudza muExcel tab Dhata - Tora Dhata - Sanganisa Zvikumbiro - Wedzera (Data - Tora Dhata - Sanganisa mibvunzo - Wedzera) uyezve matafura edu Sales и Discounts muhwindo rinoonekwa:
- Mushure mekunge mavharidza OK matafura edu achanamirwa mune imwechete yakazara - pasi peumwe neumwe. Ndapota cherechedza kuti makoramu ane huwandu hwezvinhu mumatafura aya akawira pasi peumwe neumwe, nokuti. vane zita rimwe chete:
- Kana iyo yekutanga kutevedzana kwemitsara mutafura yekutengesa yakakosha kwauri, saka kuitira kuti mushure meshanduko dzese dzinotevera unogona kuzodzoreredza, wedzera nhamba ine nhamba patafura yedu uchishandisa murairo. Kuwedzera Column - Index Column (Wedzera chikamu - Index column). Kana kutevedzana kwemitsara kusina basa kwauri, saka unogona kusvetuka danho iri.
- Zvino, uchishandisa rondedzero yekudonhedza-pasi mumusoro wetafura, rongedza nekoramu uwandu Kukwira:
- Uye hunyengeri hukuru: tinya-kurudyi pane musoro wekoramu dhisikaundi sarudza timu Zadza – Pasi (Zadza - Pasi). Masero asina chinhu ane null inozadzwa otomatiki neyakaderedzwa mutengo wekare:
- Inoramba ichidzoreredza kutevedzana kwepakutanga kwemitsara nekuronga nekoramu indekisi (unogona kuidzima zvakachengeteka gare gare) uye bvisa mitsara isingakoshi nesefa null by column Transaction code:
- Kushandisa VLOOKUP basa kutsvaga uye kutsvaga data
- Kushandisa VLOOKUP (VLOOKUP) inyaya-inonzwa
- XNUMXD VLOOKUP (VLOOKUP)