Amafomula angu-15 aphezulu ku-Excel

I-Excel ingenye yezinhlelo ezibaluleke kakhulu. Kwenze izimpilo zabasebenzisi abaningi zaba lula. I-Excel ikuvumela ukuthi wenze ngokuzenzakalelayo izibalo eziyinkimbinkimbi kakhulu, futhi lokhu kuyinzuzo enkulu yalolu hlelo.

Njengomthetho, umsebenzisi ojwayelekile usebenzisa isethi elinganiselwe yemisebenzi, kuyilapho kunamafomula amaningi avumela ukuthi usebenzise imisebenzi efanayo, kodwa ngokushesha okukhulu.

Lokhu kungaba usizo uma kufanele wenze njalo izenzo eziningi zohlobo olufanayo ezidinga inani elikhulu lemisebenzi.

Ube nentshisekelo? Bese wamukelekile ekubuyekezweni kwamafomula ayi-15 e-Excel awusizo kakhulu.

Amanye amatemu

Ngaphambi kokuba uqale ukubuyekeza imisebenzi ngokuqondile, udinga ukuqonda ukuthi iyini. Lo mqondo usho ifomula ebekwe abathuthukisi, ngokuya ngokuthi yiziphi izibalo ezenziwayo futhi umphumela othile utholakala ekuphumeni. 

Wonke umsebenzi unezingxenye ezimbili eziyinhloko: igama nokuphikisana. Ifomula ingaba nomsebenzi owodwa noma eminingana. Ukuze uqale ukuyibhala, udinga ukuchofoza kabili kuseli edingekayo bese ubhala uphawu olulinganayo.

Ingxenye elandelayo yomsebenzi igama. Empeleni, igama lefomula, elizosiza i-Excel ukuthi iqonde ukuthi umsebenzisi ufunani. Kulandelwa ukuphikisana kubakaki. Lawa amapharamitha omsebenzi acatshangelwa ukwenza imisebenzi ethile. Kunezinhlobo eziningana zezimpikiswano: izinombolo, umbhalo, ezinengqondo. Futhi, esikhundleni sazo, izinkomba zamaseli noma ububanzi obuthile zivame ukusetshenziswa. I-agumenti ngayinye ihlukaniswa kwenye nge-semicolon.

I-syntax ingenye yezindlela eziyinhloko ezibonisa umsebenzi. Leli gama lisho isifanekiso sokufaka amanani athile ukuze kwenziwe umsebenzi usebenze.

Futhi manje ake sihlole konke lokhu ngokusebenza.

Ifomula 1: VLOOKUP

Lo msebenzi wenza kube nokwenzeka ukuthola ulwazi oludingekayo kuthebula, futhi ubonise umphumela obuyisiwe kuseli ethile. Isifinyezo segama lomsebenzi simele “Ukubuka Okuqondile”.

syntax

Lena ifomula eyinkimbinkimbi enezimpikiswano ezingu-4, futhi ukusetshenziswa kwayo kunezici eziningi.

I-syntax yile:

=I-VLOOKUP(inani_lokubheka, ithebula, inombolo_yekholomu, [ububanzi_ukubheka])

Ake sihlolisise zonke izingxabano:

1. Inani lokubheka phezulu.
2. Ithebula. Kudingeka ukuthi kube khona inani lokubheka kukholamu yokuqala, kanye nenani elibuyiselwayo. Lesi sakamuva sitholakala noma yikuphi. Umsebenzisi anganquma ngokuzimela ukuthi uzowufaka kuphi umphumela wefomula. 
3. Inombolo yekholomu.
4. Ukubuka isikhawu. Uma lokhu kungenasidingo, ungashiya inani lale mpikiswano. Kuyisisho se-boolean esibonisa izinga lokunemba lokufanayo okumele kutholwe umsebenzi. Uma ipharamitha ethi “Iqiniso” icacisiwe, i-Excel izobheka inani eliseduze kunalelo elishiwo njengenani lokusesha. Uma ipharamitha ethi “Amanga” icacisiwe, umsebenzi uzosesha kuphela lawo manani akukholomu yokuqala.

Kulesi sithombe-skrini, sizama ukuthola ukuthi bangaki ukubuka okukhiqizwa embuzweni othi "thenga ithebhulethi" kusetshenziswa ifomula.

Ifomula 2: Uma

Lo msebenzi uyadingeka uma umsebenzisi efuna ukusetha isimo esithile lapho inani elithile kufanele libalwe noma likhishwe ngaphansi kwaso. Kungathatha izinketho ezimbili: iqiniso kanye namanga.

syntax

Ifomula yalo msebenzi inezimpikiswano ezintathu eziyinhloko, futhi ibonakala kanje:

=IF(inkulumo_enengqondo, “inani_uma_iqiniso”, “inani_uma_amanga”).

Lapha, isisho esiphusile sisho ifomula echaza ngokuqondile umbandela. Ngosizo lwayo, idatha izohlolelwa ukuhambisana nesimo esithile. Ngokufanelekile, impikiswano "inani uma ingamanga" ihloselwe umsebenzi ofanayo, umehluko kuphela ukuthi iyisibuko esiphambene nencazelo. Ngamazwi alula, uma isimo singaqinisekiswanga, uhlelo lwenza izenzo ezithile.

Kukhona enye indlela yokusebenzisa umsebenzi IF – imisebenzi esidleke. Kungaba nezimo eziningi ezengeziwe lapha, kufika ku-64. Isibonelo sokucabanga esihambisana nefomula enikezwe kusithombe-skrini simi kanje. Uma iseli A2 lilingana nokubili, kuzomele ubonise inani elithi “Yebo”. Uma inenani elihlukile, kuzomele uhlole ukuthi iseli D2 lilingana nokubili. Uma uthi yebo, kuzomele ubuyisele inani elithi “cha”, uma lapha isimo siphenduka singamanga, ifomula kufanele ibuyisele inani elithi “mhlawumbe”.

Akunconywa ukusebenzisa imisebenzi efakwe isidleke kaningi, ngoba kunzima ukuyisebenzisa, amaphutha ayenzeka. Futhi kuzothatha isikhathi eside ukuzilungisa. 

Umsebenzi IF ingasetshenziswa futhi ukunquma ukuthi iseli elithile alinalutho. Ukufeza lo mgomo, kufanele kusetshenziswe omunye umsebenzi − ISBLANK.

Nansi i-syntax:

=IF(ISBLANK(inombolo yeselula),"Akunalutho","Akunalutho").

Ngaphezu kwalokho, kungenzeka ukusebenzisa esikhundleni umsebenzi ISBLANK sebenzisa ifomula evamile, kodwa cacisa ukuthi kucatshangwa ukuthi awekho amanani kuseli.

IF - lokhu kungomunye wemisebenzi evamile okulula kakhulu ukuyisebenzisa futhi ikuvumela ukuthi uqonde ukuthi amanani athile ayiqiniso kangakanani, uthole imiphumela yemibandela ehlukahlukene, futhi unqume ukuthi iseli elithile alinalutho.

Lo msebenzi uyisisekelo samanye amafomula. Manje sizohlaziya ezinye zazo ngokuningiliziwe.

Ifomula 3: SUMIF

Umsebenzi I-SUMMESLI ikuvumela ukuthi ufingqe idatha, ngokuya ngokuhambisana nemibandela ethile.

syntax

Lo msebenzi, njengalowo odlule, unezimpikiswano ezintathu. Ukuyisebenzisa, udinga ukubhala ifomula enjalo, esikhundleni samanani adingekayo ezindaweni ezifanele.

=SUMIF(ububanzi, isimo, [isamba_ububanzi])

Ake siqonde ngokuningiliziwe ukuthi iyini impikiswano ngayinye:

1. Isimo. Le mpikiswano ikuvumela ukuthi udlulisele amaseli kumsebenzi, angaphansi kokuhlanganiswa.
2. Ibanga lesifinyezo. Le mpikiswano iyinketho futhi ikuvumela ukuthi ucacise amaseli ozohlanganisa uma isimo singamanga.

Ngakho-ke, kulesi simo, i-Excel ifingqa idatha kuleyo mibuzo lapho inani lezinguquko lidlula i-100000.

Ifomula 4: SUMMESLIMN

Uma kunezimo eziningana, khona-ke umsebenzi ohlobene uyasetshenziswa I-SUMMESLIMN.

syntax

Ifomula yalo msebenzi ibukeka kanje:

=I-SUMIFS(isifinyezo_ububanzi, condition_range1, condition1, [condition_range2, condition2], …)

Kudingeka izimpikiswano zesibili nezesithathu, okungukuthi “Uhla lwesimo 1” kanye “nobubanzi besimo 1”.

Ifomula 5: COUNTIF kanye ne-COUNTIFS

Lo msebenzi uzama ukunquma inani lamaseli angangenalutho afana nezimo ezinikeziwe ngaphakathi kobubanzi obufakwe umsebenzisi.

syntax

Ukufaka lo msebenzi, kufanele ucacise ifomula elandelayo:

= COUNTIF (ububanzi, inqubo)

Zisho ukuthini izimpikiswano ezinikeziwe?

1. Ububanzi isethi yamaseli okufanele ukubalwa phakathi kwawo.
2. Umbandela - isimo esicatshangelwa lapho kukhethwa amaseli.

Isibonelo, kulesi sibonelo, uhlelo lubala inani lemibuzo ebalulekile, lapho inani lokuchofoza ezinjinini zokusesha lidlula izinkulungwane eziyikhulu. Ngenxa yalokho, ifomula ibuyisele inombolo 3, okusho ukuthi kunamagama angukhiye amathathu.

Ukukhuluma ngomsebenzi ohlobene COUNTIFS, khona-ke, ngokufanayo nesibonelo sangaphambilini, inikeza ikhono lokusebenzisa imibandela embalwa ngesikhathi esisodwa. Ifomula yayo imi kanje:

=COUNTIFS(condition_range1, condition1, [condition_range2, condition2],…)

Futhi ngokufanayo ngodaba lwangaphambilini, “Ububanzi Besimo 1” kanye “Nesimo 1” kuyizimpikiswano ezidingekayo, kuyilapho ezinye zingashiywa uma singekho isidingo esinjalo. Umsebenzi omkhulu unikeza ikhono lokusebenzisa kufika kububanzi obuyi-127 kanye nezimo.

Ifomula 6: IFERROR

Lo msebenzi ubuyisela inani elishiwo umsebenzisi uma kuhlangatshezwana nephutha ngenkathi kuhlolwa ifomula. Uma inani eliwumphumela lilungile, uyalishiya.

syntax

Lo msebenzi unama-agumenti amabili. I-syntax yile elandelayo:

=IFERROR(inani;inani_uma_iphutha)

Incazelo yezimpikiswano:

1. Inani liyifomula ngokwayo, ihlole iziphazamisi.
2. Inani uma iphutha liwumphumela ovela ngemva kokutholwa kwephutha.

Uma sikhuluma ngezibonelo, le fomula izobonisa umbhalo othi "Iphutha ekubaleni" uma ukuhlukaniswa kungenakwenzeka.

Ifomula 7: KWESOKUNXELE

Lo msebenzi wenza kube nokwenzeka ukukhetha inombolo edingekayo yezinhlamvu kwesokunxele sochungechunge.

I-syntax yayo yile elandelayo:

=KUSELE(umbhalo,[inombolo_yezinhlamvu])

Izimpikiswano ezingenzeka:

1. Umbhalo – iyunithi yezinhlamvu ofuna ukuthola kuyo ucezu oluthile.
2. Inombolo yezinhlamvu yinombolo ngqo yezinhlamvu ezizokhishwa.

Ngakho-ke, kulesi sibonelo, ungabona ukuthi lo msebenzi usetshenziswa kanjani ukuze ubone ukuthi izihloko zamakhasi esayithi zizobukeka kanjani. Okusho ukuthi, noma ngabe iyunithi yezinhlamvu izongena enanini elithile lezinhlamvu noma cha.

Ifomula 8: PSTR

Lo msebenzi wenza kube nokwenzeka ukuthola inombolo edingekayo yezinhlamvu embhalweni, kuqala ngohlamvu oluthile ku-akhawunti.

I-syntax yayo yile elandelayo:

=MID(umbhalo,indawo_yokuqala,inombolo_yezinhlamvu).

Ukunwetshwa kwengxabano:

1. Umbhalo uwuchungechunge oluqukethe idatha edingekayo.
2. Indawo yokuqala iyindawo ngqo yalowo mlingisi, osebenza njengesiqalo sokukhipha umbhalo.
3. Inombolo yezinhlamvu - inombolo yezinhlamvu okufanele ifomula ikhiphe embhalweni.

Empeleni, lo msebenzi ungasetshenziswa, isibonelo, ukwenza lula amagama ezihloko ngokususa amagama asekuqaleni kwawo.

Ifomula 9: PROPISN

Lo msebenzi ubhala ngosonhlamvukazi zonke izinhlamvu eziqukethwe ochungechungeni oluthile. I-syntax yayo yile elandelayo:

=KUDINGEKA(umbhalo)

Kukhona ingxabano eyodwa kuphela - umbhalo ngokwawo, ozocutshungulwa. Ungasebenzisa ireferensi yeseli.

Ifomula 10: PHANSI

Ngokuyinhloko umsebenzi ophambene ohlaza ohlamvu abancane bonke bombhalo onikeziwe noma iseli.

I-syntax yayo iyafana, kune-agumenti eyodwa kuphela equkethe umbhalo noma ikheli leseli.

Ifomula 11: SESHA

Lo msebenzi wenza kube nokwenzeka ukuthola into edingekayo phakathi kwebanga lamaseli futhi unikeze indawo yayo.

Isifanekiso sale fomula sithi:

=MATCH(inani_lokubheka, uhlu_lokubheka, uhlobo_lokufanisa)

Kudingeka izimpikiswano ezimbili zokuqala, ezokugcina uzikhethela.

Kunezindlela ezintathu zokuhlanganisa:

1. Ngaphansi noma kulingana no-1.
2. Ngokuqondile - 0.
3. Inani elincane kakhulu, elilingana noma likhulu kuno- -1.

Kulesi sibonelo, sizama ukunquma ukuthi yimaphi amagama angukhiye alandelwa ukuchofoza okungafika kokungu-900, okuhlanganisiwe.

Ifomula 12: DLSTR

Lo msebenzi wenza kube nokwenzeka ukunquma ubude beyunithi yezinhlamvu enikeziwe.

I-syntax yayo iyafana neyangaphambilini:

=DLSTR(umbhalo)

Ngakho-ke, ingasetshenziswa ukunquma ubude bencazelo ye-athikili lapho i-SEO-promotion yesayithi.

Kuhle futhi ukuyihlanganisa nomsebenzi IF.

Ifomula 13: Xhuma

Lo msebenzi wenza kube nokwenzeka ukwenza imigqa eminingana ukusuka kowodwa. Ngaphezu kwalokho, kuvunyelwe ukucacisa kuma-agumenti kokubili amakheli eseli kanye nenani ngokwalo. Ifomula yenza kube lula ukubhala izakhi ezingafika ku-255 ezinobude obungekho ngaphezu kwezinhlamvu ze-8192, okwanele ukuzijwayeza.

I-syntax yile:

=CONCATENATE(umbhalo1,umbhalo2,umbhalo3);

Ifomula 14: PROPNACH

Lo msebenzi ushintshanisa izinhlamvu ezingosonhlamvukazi nabancane.

I-syntax ilula kakhulu:

=PROPLAN(umbhalo)

Ifomula 15: PRINT

Le fomula yenza kube nokwenzeka ukususa zonke izinhlamvu ezingabonakali (isibonelo, ukunqanyulwa komugqa) ku-athikili.

I-syntax yayo yile elandelayo:

=PRINT(umbhalo)

Njengokuphikisana, ungacacisa ikheli leseli.

iziphetho

Yiqiniso, lena akuyona yonke imisebenzi esetshenziswa ku-Excel. Besifuna ukuletha okunye umsebenzisi we-spreadsheet angakaze akuzwe noma angavamisile ukukusebenzisa. Ngokwezibalo, imisebenzi esetshenziswa kakhulu eyokubala nokuthola inani elimaphakathi. Kodwa i-Excel ingaphezu nje kohlelo lwesipredishithi. Kuyo, ungakwazi ukushintsha ngokuphelele noma yimuphi umsebenzi. 

Ngethemba ngempela ukuthi kuphumelele, futhi uzifundele izinto eziningi eziwusizo.