Okuqukethwe
Empeleni, kaningi kuba nezimo lapho mina nawe sidinga ukuthola inani eliseduze kakhulu kusethi (ithebula) maqondana nenombolo enikeziwe. Kungaba, isibonelo:
- Ukubalwa kwesaphulelo kuye ngevolumu.
- Ukubalwa kwenani lamabhonasi kuye ngokuqaliswa kohlelo.
- Ukubalwa kwamazinga okuthumela kuye ngebanga.
- Ukukhethwa kweziqukathi ezifanele zezimpahla, njll.
Ngaphezu kwalokho, ukuzungezisa kungadingeka kokubili phezulu naphansi, kuye ngesimo.
Kunezindlela eziningana - ezisobala futhi ezingabonakali kangako - zokuxazulula inkinga enjalo. Ake sizibheke ngokulandelana.
Okokuqala, ake sicabange ngomphakeli onikeza izaphulelo kuwholesale, futhi iphesenti lesaphulelo lincike enanini lezimpahla ezithengiwe. Isibonelo, lapho uthenga izingcezu ezingaphezu kuka-5, isaphulelo esingu-2% sinikezwa, futhi lapho uthenga kusuka ezicucu ezingu-20 - kakade 6%, njll.
Ungabala kanjani ngokushesha futhi kahle iphesenti lesaphulelo lapho ufaka inani lezimpahla ezithengiwe?
Indlela 1: Ama-IF Akhiwe
Indlela evela ochungechungeni "yini okumele ucabange - udinga ukugxuma!". Ukusebenzisa imisebenzi ehlanganisiwe IF (UMA) ukuhlola ngokulandelana ukuthi inani leseli liwela yini kuzikhawu ngayinye bese ubonisa isaphulelo sobubanzi obuhambisanayo. Kodwa ifomula kuleli cala ingaba nzima kakhulu:
Ngicabanga ukuthi kusobala ukuthi ukususa iphutha “kunodoli oyinunu” noma ukuzama ukungeza izimo ezimbalwa ezintsha kuwo ngemuva kwesikhathi esithile kumnandi.
Ngaphezu kwalokho, i-Microsoft Excel inomkhawulo wokuzalela umsebenzi we-IF - izikhathi ezingu-7 ezinguqulweni ezindala kanye nezikhathi ezingu-64 ezinguqulweni ezintsha. Kuthiwani uma udinga okwengeziwe?
Indlela 2. I-VLOOKUP enokubuka kwesikhawu
Le ndlela ihlangene kakhulu. Ukuze ubale iphesenti lesaphulelo, sebenzisa umsebenzi oyinganekwane VPR (VLOOKUP) kumodi yokusesha cishe:
lapho
- B4 – inani lenani lezimpahla ekwenziweni kokuqala esifuna isaphulelo
- $G$4:H$8 - isixhumanisi setafula lesaphulelo - ngaphandle "kwesihloko" kanye namakheli ahlanganiswe nophawu lwe-$.
- 2 — inombolo ye-ordinal yekholomu kuthebula lesaphulelo esifuna ukuthola kulo inani lesaphulelo
- TRUE - kulapho "inja" ingcwatshwa khona. Uma njengokungqubuzana komsebenzi wokugcina VPR ucacise AMANGA (AMANGA) Noma i-0, khona-ke umsebenzi uzobheka ukufana okuqinile kukholamu yobuningi (futhi esimweni sethu izonikeza iphutha elithi #N/A, njengoba lingekho inani elingu-49 kuthebula lesaphulelo). Kodwa uma esikhundleni salokho AMANGA bhala TRUE (IQINISO) Noma i-1, khona-ke umsebenzi ngeke ubheke okuqondile, kodwa encane eseduze value futhi izosinika iphesenti lesaphulelo esisidingayo.
Uhlangothi olubi lwale ndlela yisidingo sokuhlunga ithebula lesaphulelo ngendlela ekhuphukayo ngekholomu yokuqala. Uma kungekho ukuhlunga okunjalo (noma kwenziwa ngokulandelana), ifomula yethu ngeke isebenze:
Ngakho, le ndlela ingasetshenziswa kuphela ukuthola inani elincane eliseduze. Uma udinga ukuthola eliseduzane elikhulu kunawo wonke, kuzomele usebenzise indlela ehlukile.
Indlela yesi-3. Ukuthola okuseduze kakhulu usebenzisa imisebenzi ye-INDEX kanye ne-MATCH
Manje ake sibheke inkinga yethu ngakolunye uhlangothi. Ake sithi sithengisa amamodeli amaningana amaphampu ezimboni amakhono ahlukahlukene. Ithebula lokuthengisa elingakwesokunxele libonisa amandla adingwa yikhasimende. Kudingeka sikhethe iphampu yamandla amakhulu aseduze noma alinganayo, kodwa hhayi ngaphansi kwalokho kudingwa iphrojekthi.
Umsebenzi we-VLOOKUP ngeke usize lapha, ngakho-ke kuzodingeka usebenzise i-analogue yayo - inqwaba yemisebenzi ye-INDEX. (INDEX) futhi OKUNINGI OKUDALIWE (IMESHI):
Lapha, umsebenzi we-MATCH one-agumenti yokugcina -1 usebenza kumodi yokuthola inani elikhulu eliseduze, futhi umsebenzi we-INDEX ube usukhipha igama lemodeli esilidingayo kukholamu eseduze.
Indlela 4. Umsebenzi omusha VIEW (XLOOKUP)
Uma unenguqulo ye-Office 365 enazo zonke izibuyekezo ezifakiwe, esikhundleni se-VLOOKUP (VLOOKUP) ungasebenzisa i-analogue yayo - umsebenzi wokubuka (XLOOKUP), esengiyihlaziyile ngokuningiliziwe:
Lapha:
- B4 – inani lokuqala lenani lomkhiqizo esiwubhekela isaphulelo
- I-$G$4:$G$8 - ububanzi lapho sifuna khona umentshisi
- $H$4:H$8 - ububanzi bemiphumela ofuna ukubuyisela kuyo isaphulelo
- impikiswano yesine (-1) kuhlanganisa ukusesha inombolo encane eseduze esiyifunayo esikhundleni sokufana nse.
Izinzuzo zale ndlela yukuthi asikho isidingo sokuhlunga ithebula lesaphulelo kanye nekhono lokusesha, uma kunesidingo, hhayi kuphela okuncane kakhulu, kodwa futhi nenani elikhulu eliseduze. Ingxabano yokugcina kuleli cala izoba ngu-1.
Kodwa, ngeshwa, akuwona wonke umuntu onalesi sici okwamanje - abanikazi abajabule kuphela be-Office 365.
Indlela 5. Umbuzo Wamandla
Uma ungakajwayelani nesengezo Sombuzo Wamandla esinamandla futhi samahhala se-Excel, khona-ke ulapha. Uma usuyazi kakade, ake sizame ukuyisebenzisa ukuxazulula inkinga yethu.
Asenze umsebenzi wokulungiselela kuqala:
- Masiguqule amathebula ethu omthombo abe yi-dynamic (smart) sisebenzisa isinqamuleli sekhibhodi Ctrl+T noma iqembu Ikhaya – Fometha njengetafula (Ekhaya — Fometha Njengethebula).
- Ukuze kucace, ake sibanikeze amagama. Sales и izaphulelo ithebhu umakhi (Umklamo).
- Layisha ithebula ngalinye ngokushintshana kokuthi Umbuzo Wamandla usebenzisa inkinobho Kusukela kuThebula/Ububanzi ithebhu Idatha (Idatha - Kusuka kuthebula/ububanzi). Ezinguqulweni zakamuva ze-Excel, le nkinobho iqanjwe kabusha yaba Ngamaqabunga (Kusuka eshidini).
- Uma amathebula anamagama ekholomu ahlukene anamanani, njengasesibonelweni sethu (“Ubuningi bezimpahla” kanye “Nobuningi obusuka …”), kufanele aqanjwe kabusha kokuthi Umbuzo Wamandla futhi aqanjwe okufanayo.
- Ngemva kwalokho, ungakwazi ukubuyela emuva ku-Excel ngokukhetha umyalo efasiteleni lomhleli Wombuzo Wamandla Ikhaya - Vala futhi Ulayishe - Vala futhi Ulayishe... (Ekhaya — Vala&Layisha — Vala&Layisha ku…) bese-ke inketho Vele udale uxhumano (Dala uxhumano kuphela).
- Bese kuqala okuthakazelisa kakhulu. Uma unolwazi Kumbuzo Wamandla, ngicabanga ukuthi umugqa owengeziwe womcabango kufanele ube ohlangothini lokuhlanganisa lawa mathebula amabili nombuzo wokuhlanganisa (hlanganisa) i-la VLOOKUP, njengoba kwakunjalo endleleni yangaphambilini. Eqinisweni, sizodinga ukuhlanganisa kumodi yokwengeza, engabonakali nhlobo ekuqaleni. Khetha kuthebhu ye-Excel Idatha - Thola Idatha - Hlanganisa Izicelo - Engeza (Idatha - Thola Idatha - Hlanganisa imibuzo - Faka) bese kuba amatafula ethu Sales и izaphulelo efasiteleni elivelayo:
- Ngemuva kokuchofoza OK amatafula ethu azonamathiselwa abe yingxenye eyodwa - ngaphansi komunye nomunye. Sicela uqaphele ukuthi amakholomu anenani lezimpahla kulawa matafula awela ngaphansi komunye nomunye, ngoba. banegama elifanayo:
- Uma ukulandelana kwangempela kwemigqa kuthebula lokuthengisa kubalulekile kuwe, ngakho-ke ukuze emva kwazo zonke izinguquko ezilandelayo ungakwazi ukukubuyisela, engeza ikholomu enenombolo etafuleni lethu usebenzisa umyalo. Ukwengeza Ikholomu – Ikholomu Yenkomba (Engeza ikholomu — Ikholomu yenkomba). Uma ukulandelana kwemigqa kungenandaba nawe, ungakwazi ukweqa lesi sinyathelo.
- Manje, usebenzisa uhlu lokudonsela phansi kunhlokweni yethebula, lihlele ngekholomu Ubuningi Iyakhuphuka:
- Futhi iqhinga eliyinhloko: chofoza kwesokudla kunhlokweni yekholomu Discount khetha iqembu Gcwalisa - Phansi (Gcwalisa - Phansi). Amaseli angenalutho nge null igcwaliswe ngokuzenzakalelayo ngamavelu esaphulelo adlule:
- Kusele ukubuyisela ukulandelana kwangempela kwemigqa ngokuhlunga ngekholomu Index (ungayisusa ngokuphepha kamuva) futhi ulahle imigqa engadingekile ngesihlungi null ngekholomu Ikhodi yokwenziwe:
- Ukusebenzisa umsebenzi we-VLOOKUP ukusesha nokubheka idatha
- Ukusebenzisa i-VLOOKUP (VLOOKUP) kuyazwela
- I-XNUMXD VLOOKUP (VLOOKUP)