Ungayibhala kanjani ifomula e-Excel? Ukuziqeqesha. Amafomula adingeka kakhulu

Sawubona ntambama

Kwake kwenzeka isikhashana, ukubhala ifomula ngokwakho ku-Excel kwakuyinto emangalisayo kimi. Futhi yize ngivame ukusebenza kulolu hlelo, angizange ngigcwalise lutho ngaphandle kombhalo ...

Njengoba kwenzeka, amafomula amaningi awayona into eyinkimbinkimbi futhi ungasebenza kalula nawo, noma owasebenzisa ikhompyutha ye-novice. Ku-athikili, nje, ngithanda ukwembula amafomula adingeka kakhulu, okufanele ngisebenze ngawo kaningi ...

Ngakho-ke, ake siqale ...

1. Imisebenzi eyisisekelo nezisekelo. Funda izisekelo ze-Excel.

Zonke izenzo ezikulo mbhalo zizokhonjiswa ku-Excel version 2007.

Ngemuva kokuqala uhlelo lwe-Excel - iwindi livela namaseli amaningi - itafula lethu. Isici esiyinhloko sohlelo ukuthi ingakwazi ukufunda (njengekhalori) amafomula wakho owabhalayo. Ngendlela, ungangezela ifomula kuyo yonke ingqamuzana!

Ifomula kumele iqale ngesibonakaliso "=". Lesi yisidingo. Ngemuva kwalokho ubhala okudingayo ukubala: ukwenza isibonelo, "= 2 + 3" (ngaphandle kwezicaphuni) bese ucindezela inkinobho ka-Enter - ngenxa yalokho, uzobona ukuthi umphumela "5" uvela esitokisini. Bona isithombe-skrini ngezansi.

Kubalulekile! Ngaphandle kokuthi inombolo "5" ibhalwe esitokisini A1, ibalwa ifomula ("= 2 + 3"). Uma kuseli elilandelayo ubhala nje u- "5" embhalweni - lapho uzulazula kulesi seli kumhleli wefomula (umugqa ngenhla, Fx) - uzobona inombolo eyinhloko "5".

Manje ake ucabange ukuthi esitokisini awukwazi ukubhala nje inani 2 + 3, kodwa izinombolo zamaseli izindinganiso zawo okudingeka uwafake. Masithi "= B2 + C2".

Ngokwemvelo, kufanele kube nezinamba ezithile ku-B2 naku-C2, ngaphandle kwalokho i-Excel izosikhombisa esitokisini A1 umphumela ungu-0.

Futhi iphuzu elilodwa elibalulekile ...

Uma ukopisha iseli okukhona ifomula, ukwenza isibonelo u-A1 - bese uyinamathisela kwesinye iseli - akulona inani "5" elikopishiwe, kodwa ifomula uqobo!

Ngaphezu kwalokho, ifomula izoshintsha ngokulingana okuqondile: i.e. uma i-A1 ikopishelwa ku-A2, ifomula kuseli A2 izoba "= B3 + C3". I-Excel ishintsha ngokuzenzakalelayo ifomula yakho uqobo: uma i-A1 = B2 + C2, khona-ke kunengqondo ukuthi i-A2 = B3 + C3 (zonke izinombolo zanda ngo-1).

Umphumela, ngendlela, uku-A2 = 0, ngoba amangqamuzana u-B3 no-C3 awachazwanga, futhi ngenxa yalokho alingana no-0.

Ngakho-ke, ungabhala ifomula kanye, bese uyikopisha kuwo wonke amaseli ekholamu oyifunayo - futhi i-Excel izobala emugqeni ngamunye wethebula lakho!

Uma ungafuni ukuthi i-B2 ne-C2 bashintshe ngesikhathi sokukopisha futhi kuhlale kunamathiselwe kulawa maseli, mane nje ungeze isithonjana se- "$" kubo. Isibonelo ngezansi.

Ngale ndlela, nomaphi lapho ukopisha khona iseli A1, kuyohlala kubhekiswa kumaseli axhumekile.

2. Ukungezwa kwamanani emugqeni (amafomula we-SUMM ne-SUMMESLIMN)

Vele, ungangezela iseli ngalinye ngokwenza ifomula A1 + A2 + A3, njll. Kepha ukuze ungahlupheki, kukhona ifomula ekhethekile ku-Excel engeza wonke amanani asemokisini owakhethayo!

Thatha isibonelo esilula. Kunezinhlobo eziningi zezimpahla esitokweni, futhi siyazi ukuthi umkhiqizo ngamunye ungakanani ku-kg ngamunye. isesitoko. Masizame ukubala, kepha malini konke ku-kg. ukuthwala impahla esitokweni.

Ukuze wenze lokhu, iya esitokisini lapho umphumela uzokhonjiswa khona bese ubhala ifomula: "= SUM (C2: C5)". Bona isithombe-skrini ngezansi.

Njengomphumela, wonke amaseli ebangeni elikhethiwe azofingqwa, futhi uzobona umphumela.

2.1. Isengezo esimweni (nemibandela)

Manje ake sithi sinezimo ezithile, i.e. ungafaki wonke amanani asemaseli (uKg, esitokisini), kepha kuphela uqinisekile, ngenani (1 kg.) elingaphansi kwe-100.

Kunefomula enhle yalokhu. "SUMMESLIMN"... Ngokushesha isibonelo, bese kunencazelo yophawu ngalunye kwifomula.

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

C2: C5 - lelo kholamu (lawo maseli) azongezwa;

B2: B5 - ikholomu isimo esizohlolwa ngalo (isib. intengo, ngokwesibonelo, ngaphansi kuka-100);

"<100" - isimo uqobo lwaso, qaphela ukuthi isimo sibhalwe kumamaki wokucaphuna.

Akukho lutho oluyinkimbinkimbi kule formula, into esemqoka ukubheka ukulingana: C2: C5; B2: B5 - kwesokudla; C2: C6; B2: B5 - akulungile. Mina. ububanzi be-DRM nobubanzi bezimo kufanele kube ngokulingene, uma kungenjalo ifomula izobuyisa iphutha.

Kubalulekile! Kungaba nezimo eziningi zesamba, i.e. Ungahlola hhayi kukholamu yokuqala, kepha ngo-10 ngokushesha, ubeka izimo eziningi.

3. Kubalwa inani lemigqa elenza imibandela (ifomula engu-KAKHULU)

Kungumsebenzi ojwayelekile: ukubala hhayi inani lamanani asezitokisini, kodwa inani lamaseli anelise imibandela ethile. Kwesinye isikhathi, kunezimo eziningi.

Futhi ngakho ... ake siqale.

Esibonelweni esifanayo, ake sizame ukubala inani lezinto ngentengo engaphezu kuka-90 (uma ubheka, ungasho ukuthi kunemikhiqizo engu-2 enjalo: ama-tangerine namawolintshi).

Ukubala izimpahla esitokisini esikufunayo, sabhala le formula elandelayo (bheka ngenhla):

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

B2: B5 - uhla abazohlolwa ngalo, ngokuya ngesimo esibekwe yithi;

">90" - isimo ngokwaso sifakwe kumamaki wokucaphuna.

Manje ake sizame ukusilingisa kancane isibonelo sethu, bese sengeza i-akhawunti ngokuya ngesimo esisodwa ngaphezulu: ngentengo engaphezu kuka-90 + inani elikhona endaweni yokugcina izimpahla lingaphansi kwama-20 kg.

Ifomula ithatha ifomu:

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

Lapha konke kuhlala kufana, ngaphandle kwesimo esisodwa ngaphezulu (C2: C6; "<20") By the way, zingaba ziningi izimo ezinjalo!

Kuyacaca ukuthi akekho noyedwa ozobhala amafomula ethebula elincane kangako, kepha etafuleni lemigqa engamakhulu ambalwa, le yinto ehluke ngokuphelele. Isibonelo, leli thebula lingaphezu kokubonwa.

4. Ukucinga nokufakwa esikhundleni kwamanani asuka etafuleni ngalinye aya kwelinye (ifomula ye-VLOOKUP)

Cabanga ukuthi ithebula elisha selifikile kithi, linamathegi entengo entsha womkhiqizo. Yebo, uma izinto ziku-10-20, ungazibeka kabusha ngesandla. Futhi uma kungamakhulu ezinto ezinjalo? Kushesha kakhulu uma i-Excel ithola ngokuzimela kumagama afanayo ukusuka kwelinye ithebula kuya kwelinye, bese ikopisha amathegi entengo amasha etafuleni lethu lakudala.

Ngomsebenzi onjalo, ifomula isetshenziswa I-VPR. Ngesinye isikhathi, waye “hlakaniphile” ngesimo esinengqondo esithi “IF” aze ahlangana nale nto enhle!

Ngakho-ke, ake siqale ...

Nasi isibonelo sethu + itafula elisha elinamathegi entengo. Manje sidinga ukufaka ngokuzenzakalela amathegi entengo amasha kusuka etafuleni elisha kuya kwele elidala (amathegi entengo amasha abomvu).

Faka ikhesa kwiseli B2 - i.e. esitokisini sokuqala, lapho sidinga ukushintsha khona ithegi yamanani ngokuzenzakalelayo. Okulandelayo, sibhala ifomula, njengasesikrinini esingezansi (ngemuva kwesikrini kuzoba nencazelo enemininingwane yayo).

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

A2 - inani esizolikhangela ukuze uthathe ithegi entsha. Esimweni sethu, sifuna igama elithi "ama-apula" etafuleni elisha.

$ D $ 2: $ E $ 5 - khetha ngokuphelele itafula lethu elisha (D2: E5, ukukhetha kusuka ekhoneni eliphezulu kwesokunxele kuya ku-diagonal engezansi kwesokudla), i.e. lapho ukusesha kuzokwenziwa khona. Isiginesha i- "$" kule formula siyadingeka ukuze uma ukopisha leli fomula kwamanye amaseli - D2: E5 angashintshi!

Kubalulekile! Ukuseshwa kwegama elithi "ama-apula" kuzokwenziwa kuphela kukholamu lokuqala lethebula lakho elikhethiwe, kulesi sibonelo, "ama-apula" azoseshwa kukholomu D.

2 - Lapho igama elithi "apula" litholakala, umsebenzi kumele wazi ukuthi iyiphi ikholomu yetafula elikhethiwe (D2: E5) ukukopisha inani olifunayo. Ngokwesibonelo sethu, kopisha kusuka kwikholamu 2 (E), ngoba kukholamu yokuqala (D) esiseshile. Uma itafula lakho elikhethiwe losesho lizoba nezinsika eziyi-10, kuzobe kukholamu yokuqala, bese kusuka kumakholamu amabili kuya kwayi-10 - ungakhetha inombolo ozoyikopisha.

To ifomula = VLOOKUP (A2; $ D $ 2: $ E $ 5; 2) athatha amanye amanani amasha amanye amagama womkhiqizo - vele ukopishe kwamanye amaseli kukholamu namathegi wentengo yomkhiqizo (ngokwesibonelo, ikhophi kumaseli B3: B5). Ifomula izoseshela ngokuzenzakalelayo futhi ikopishe inani elisuka kwikholomu yetafula elisha olifunayo.

5. Isiphetho

Kulesi sihloko, sihlole izisekelo zokusebenza ne-Excel, ukuthi ungaqala kanjani ukubhala amafomula. Banikeze izibonelo zezindlela ezivame kakhulu abantu abaningi abasebenza e-Excel abavame ukusebenza nazo.

Ngiyethemba ukuthi lezi zibonelo ezihlangene zizoba usizo kumuntu futhi zizosiza ukusheshisa umsebenzi wakhe. Iba nesilingo esihle!

I-PS

Futhi usebenzisa muphi amafomula? Kungenzeka ukuthi ngandlela thize wenze lula amafomula anikezwe esihlokweni? Isibonelo, kumakhompiyutha abuthakathaka, lapho amanye amanani eshintsha kumatafula amakhulu lapho ukubalwa kwenziwa ngokuzenzakalelayo, ikhompyutha iyazingcwaba imizuzwana embalwa, ilandwe futhi ibonise imiphumela emisha ...