Ungabhala kanjani ifomula ku-Excel? Ukuqeqeshwa Amafomula adingekayo kakhulu

Kuhle ntambama

Ngesinye isikhathi, ukubhalela ifomula ku-Excel ngokwakho kwakuyinto enganginakwenzeka kimi. Futhi naphezu kokuthi ngangivame ukusebenza kuloluhlelo, angizange ngidle lutho ngaphandle kombhalo ...

Njengoba kwenzeka, iningi lamafomula akuyona into eyinkimbinkimbi futhi kulula ukusebenza nabo, ngisho nomsebenzisi wekhompyutha yomshayeli. Kulesi sihloko, nje, ngingathanda ukudalula amafomula amaningi adingekayo, okuyinto ngokuvamile okumele isebenze ...

Futhi-ke, ake siqale ...

1. Imisebenzi eyisisekelo nezisekelo. Ukuqeqeshwa kwe-Excel.

Zonke izenzo kulesi sihloko zizoboniswa ku-Excel version 2007.

Ngemuva kokuqala uhlelo lwe-Excel - iwindi livela ngamaningi amangqamuzana - ithebula lethu. Isici esiyinhloko salolu hlelo ukuthi singakwazi ukufunda (njengendlela yokubala) amafomula akho obhalayo. Ngendlela, ungangezela ifomu kumakhalekhukhwini ngamunye!

Ifomula kumele iqale ngesibonakaliso "=". Lokhu kuyadingeka. Okulandelayo, ubhala lokho okudingayo ukubala: isibonelo, "= 2 + 3" (ngaphandle kwamacaphuno) bese ucindezela u-Enter - ngenxa yalokho uzobona ukuthi umphumela ubonakale esitokisini "5". Bona umfanekiso wesithombe ngezansi.

Kubalulekile! Naphezu kokuthi inombolo "5" ibhalwe esitokisini A1, ibalwa ngefomula ("= = 2 + 3"). Uma esitokisini esilandelayo ubhala "5" ngombhalo - ke uma uhambisa isikhombisi kule seli - kumhleli wefomula (umugqa ngenhla, Fx) - uzobona inombolo yokuqala "5".

Manje ake ucabange ukuthi esitokisini awukwazi ukubhala okungenani ukubaluleka kokungu-2 + 3, kodwa izinombolo zamaseli anamagugu ofuna ukuwafaka. Ake sithi "= B2 + C2".

Ngokwemvelo, kufanele kube nezinombolo ezithile ku-B2 no-C2, ngaphandle kwalokho i-Excel izosikhombisa esitokisini A1 umphumela olingana no-0.

Futhi enye inothi ebalulekile ...

Uma ukopisha iseli lapho kunefomula, isibonelo, i-A1 - bese uyifaka kwelinye iseli, hhayi inani "5" likopishiwe, kodwa ifomu ngokwayo!

Ngaphezu kwalokho, ifomula izoshintsha ngokuqondile: uma i-A1 ikopishwa ku-A2 - khona-ke ifomula e-cell A2 izolingana ne- "= B3 + C3". I-Excel ishintsha ngokuzenzakalelayo ifomula yakho ngokwayo: uma i-A1 = B2 + C2, kungengqondo ukuthi i-A2 = B3 + C3 (zonke izinombolo zanda ngo-1).

Umphumela, ngendlela, ngu-A2 = 0, kusukela amaseli B3 no-C3 awasethiwe, ngakho-ke alingana no-0.

Ngale ndlela, ungabhala ifomula kanye, bese uyikopisha kuwo wonke amaseli ekholomu oyifunayo - ne-Excel ngokwayo iyobala emgqeni ngamunye wetafula lakho!

Uma ungafuni i-B2 ne-C2 ukuthi ishintshe uma ikopisha njalo ihlanganiswe kulawa maseli, vele ufake isithonjana "$" kubo. Isibonelo ngezansi.

Ngakho-ke, nomaphi lapho ukopisha khona iseli A1, lizohlala libhekisela kumaseli axhunyiwe.

2. Ukwengezwa kwamanani ezinkambo (ifomula SUM kanye ne-SUMMESLIMN)

Yebo, ungeza iseli ngalinye, wenze ifomula A1 + A2 + A3, njll. Kodwa ukuze ungahlupheki kakhulu, ku-Excel kunomfutho okhethekile ozokwengeza wonke amanani kumaseli owakhethayo!

Thatha isibonelo esilula. Kukhona izinto eziningana esitokisini, futhi siyazi ukuthi into ngayinye ingakanani ku-kg. isesitokisini. Ake sizame ukubala ukuthi kungakanani kg. i-cargo in isitokwe.

Ukuze wenze lokhu, iya esitokisini lapho umphumela uzoboniswa bese ubhala ifomula: "= SUM (C2: C5)". Bona umfanekiso wesithombe ngezansi.

Ngenxa yalokho, wonke amaseli ebangeni elikhethiwe azofingqwa, futhi uzobona umphumela.

2.1. Ukwengeza ngesimo (ngezimo)

Manje cabanga ukuthi sinezimo ezithile, i.e. akudingekile ukwengeza wonke amanani kumaseli (Kg, esitokisini), kodwa kuphela labo echazwe, bathi, ngentengo (1 kg.) ngaphansi kuka-100.

Kulokhu kukhona umuthi omangalisayo "SUMMESLIMN"Ngokushesha isibonelo, bese kuchazwa uphawu ngalunye kwifomula.

= SUMMESLIMN (C2: C5; B2: B5; "<100")kuphi:

C2: C5 - Lekholomu (lawo maseli), okuzofakwa;

B2: B5 - ikholomu lapho isimo sizohlolwa khona (ie intengo, isibonelo, ngaphansi kuka-100);

"<100" - isimo ngokwalo, phawula ukuthi isimo sibhaliwe kumacaphuno.

Akukho lutho oluyinkimbinkimbi kule fomula, into esemqoka ukugcina ukulingana: C2: C5; B2: B5 ilungile; C2: C6; B2: B5 ayiphutha. Yebo ibanga lokufingqa kanye nobubanzi besimo kufanele kube ngokulinganayo, ngaphandle kwalokho ifomula izobuyisela iphutha.

Kubalulekile! Kungase kube khona izimo eziningi ze-mali, isb. Ungahlola ngekholomu yokuqala, kodwa ngo-10 ngesikhathi esisodwa, ngokucacisa isethi yemibandela.

3. Ukubala inani lemigqa eyanelisa izimo (ifomu COUNTIFSLIMN)

Umsebenzi ovame ukwenza njalo ukubala hhayi inani lamagugu amangqamuzana, kodwa inani lalawo maseli anelisa izimo ezithile. Ngezinye izikhathi, izimo eziningi.

Futhi ngakho ... ake siqale.

Esikhathini esifanayo, sizozama ukubala inani lomkhiqizo ngamanani angaphezulu kuka 90 (uma ubheka, ungasho ukuthi kunemikhiqizo enjalo emibili: ama-tangerines nama-oranges).

Ukuze ubale izimpahla esitokisini esifuna, sibhale ifomula elandelayo (bheka ngenhla):

= IKHAYA (B2: B5; "> 90")kuphi:

B2: B5 - ububanzi obuzohlolwa ngalo ngokuvumelana nesimo esizibekiwe;

">90" - isimo ngokwawo sisezingcaphuno.

Manje sizozama ukuphoqelela isibonelo sethu kancane, bese sengeza ibhilidi ngokusho kwesinye isimo: ngentengo engaphezu kuka-90 + ubungakanani esitokisini ingaphansi kuka-20 kg.

Ifomula ithatha ifomu:

= COUNTIFS (B2: B6; "> 90"; C2: C6; "<20")

Lapha konke kuqhubeka okufanayo, ngaphandle kwesinye isimo esisodwa (I-C2: C6; "<20"). Ngendlela, kungaba nemibandela eminingi enjalo!

Kuyacaca ukuthi etafuleni elincane elinjalo, akekho ozobhala imibono enjalo, kodwa etafuleni lemigqa engamakhulu ambalwa - lokhu kungenye indaba. Isibonelo, leli tafula lingaphezu kokucacile.

4. Ukusesha nokuguqulwa kwamagugu kusuka kwelinye itafula kuya kwenye (ifomu le-CDF)

Cabanga ukuthi ithebula elisha lifikile kithi, ngamathegi amanani entengo yezimpahla. Awu, uma amagama ka-10-20 - futhi ungakwazi "ukuwakhohlwa" konke. Futhi uma kunezinkulungwane zamagama anjalo? Ngokushesha kakhulu uma i-Excel itholakala ngokuzenzakalelayo amagama afanayo kusuka kwelinye itafula kuya kwelinye, bese ikopisha amathegi amanani entsha etafuleni lethu elidala.

Kulo msebenzi, ifomula isetshenziswa I-Vpr. Ngesinye isikhathi, yena ngokwakhe "ngokuhlakanipha" ngamafomu enengqondo ethi "i-IF" ayengakayitholi le nto emangalisayo!

Futhi-ke, ake siqale ...

Nasi isibonelo sethu + itafula elisha ngamathegi wamanani. Manje sidinga ukufaka ngokuzenzekelayo amathegi amanani amasha kusukela kuthebula elisha kuya kudala (amathegi amanani amasha abomvu).

Beka isikhombisi esitokisini B2 - isb. esitokisini sokuqala lapho sidinga ukushintsha ithegi yentengo ngokuzenzakalelayo. Okulandelayo, sibhala ifomula njengemifanekiso engezansi ngezansi (emva kwesithombe esiphezulu sizoba nencazelo eningiliziwe).

= CDF (A2; $ D $ 2: $ E $ 5; 2)kuphi

A2 - inani esizolifuna ukuze uthole ithegi entsha yamanani. Esimweni sethu, sibheke igama elithi "ama-apula" etafuleni elisha.

$ D $ 2: $ E $ 5 - sikhetha ithebula lethu elisha ngokuphelele (D2: E5, ukhetho luvela phezulu ngakwesokunxele kuya kwesokudla ngezansi diagonally), isb. lapho ukusesha kuzokwenziwa khona. I-"$" yokungena kule fomula kuyadingeka ukuze uma ukukopisha leli fomu kwamanye amaseli - D2: E5 ayishintshi!

Kubalulekile! Ukusesha igama elithi "ama-apula" kuzokwenziwa kuphela kukholomu yetafula lakho elikhethiwe; kulesi sibonelo, "ama-apula" azoseshwa kukholomu D.

2 - Uma igama elithi "apula" litholakala, umsebenzi kufanele wazi ukuthi iyiphi ikholomu yekhebula ekhethiwe (D2: E5) ukukopisha inani elifunayo. Esikhathini sethu, kopisha kusuka kukholomu 2 (E), kusukela kukholomu yokuqala (D) esiyifunayo. Uma ithebula lakho elikhethiwe lokusesha lizoqukatha amakholomu angu-10, ikholomu yokuqala izosesha, futhi kusuka kumakholomu amabili kuya kwangu-10 - ungakhetha inombolo ezokopishwa.

Kuya ifomula = CDF (A2; $ D $ 2: $ E $ 5; 2) amanani amasha ashintshiwe kwamanye amagama womkhiqizo - mane nje uwakopishe kwamanye amaseli ekholomu ngamathegi wamanani omkhiqizo (kusibonelo sethu, ikopi kumaseli B3: B5). Ifomula izosesha ngokuzenzekelayo bese ikopiza inani kusukela kukholomu yetafula elisha oyidingayo.

5. Isiphetho

Kulesi sihloko, sibheke izisekelo zokusebenzisana ne-Excel kusuka kokuqala ukubhala amafomula. Banikeza izibonelo zefomula ezivame kakhulu ezisebenza kaningi nalabo abasebenza ku-Excel.

Ngithemba ukuthi izibonelo ezihloliwe zizosiza umuntu futhi zizosiza ukusheshisa umsebenzi wakhe. Ukuhlolwa okuphumelelayo!

PS

Futhi yiziphi izindlela ozisebenzisayo, kungenzeka ukuthi ngandlela-thile ululaze amafomula anikezwe kulesi sihloko? Isibonelo, kumakhompuyutha abuthakathaka, lapho amanye amanani ashintsha amatafula amakhulu, lapho izibalo zenziwa ngokuzenzekelayo, ikhompyutha ikhululeka imizuzwana embalwa, ivuselela futhi ibonise imiphumela emisha ...