Ungabala kanjani iphesenti lesamba sezinombolo ku-Excel

I-Excel ikuvumela ukuthi wenze imisebenzi ehlukahlukene ngamaphesenti: thola iphesenti lenombolo, wengeze ndawonye, ​​engeza iphesenti enombolweni, unqume ukuthi inani likhuphuke noma lehle ngamaphesenti amangaki, futhi wenze nenani elikhulu leminye imisebenzi. . Lawa makhono adingeka kakhulu empilweni. Kufanele uhlale ubhekana nazo, ngoba zonke izaphulelo, izikweletu, amadiphozithi zibalwa ngesisekelo sazo. Ake sihlolisise indlela yokwenza imisebenzi ehlukahlukene ngentshisekelo, kusukela kokulula kuya kokuyinkimbinkimbi kakhulu.

Liyini iphesenti

Cishe sonke siyaqonda ukuthi iyini inzalo nokuthi siyibala kanjani. Asiphinde le nto. Ake sicabange ukuthi amayunithi ayi-100 omkhiqizo othile alethwe endaweni yokugcina impahla. Lapha iyunithi eyodwa kulokhu ilingana nephesenti elilodwa. Uma amayunithi angama-200 ezimpahla angenisiwe, iphesenti elilodwa lingaba amayunithi amabili, njalonjalo. Ukuze uthole iphesenti elilodwa, udinga ukuhlukanisa isibalo sokuqala ngekhulu. Yilapho ongabalekela khona manje.

Ukubalwa kwephesenti lemali ku-Excel

Ngokuvamile, isibonelo esichazwe ngenhla sesivele siwukuboniswa okucacile kokuthola inani lephesenti kusuka kunani elikhulu (okungukuthi, isamba sabancane). Ukuze sisiqonde kangcono lesi sihloko, ake sithathe esinye isibonelo.

Uzothola ukuthi unganquma kanjani ngokushesha iphesenti lesamba samanani usebenzisa i-Excel.

Ake sithi itafula livuliwe kukhompuyutha yethu eliqukethe uhla olukhulu lwedatha futhi ulwazi lokugcina luqoshwa kuseli eyodwa. Ngokufanelekile, sidinga ukunquma ukuthi iyiphi ingxenye yendawo eyodwa ngokumelene nesizinda senani eliphelele. Eqinisweni, konke kufanele kwenziwe ngendlela efanayo nesigaba esandulele, kuphela isixhumanisi kuleli cala kufanele siguqulwe sibe ngokuphelele, hhayi isihlobo.

Isibonelo, uma amanani aboniswa kukholomu B, futhi umphumela utholakala kuseli B10, ifomula yethu izobukeka kanje.

=B2/$B$10

Ake sihlaziye le fomula ngokuningiliziwe. Iseli B2 kulesi sibonelo lizoshintsha uma ligcwaliswa ngokuzenzakalelayo. Ngakho-ke, ikheli layo kufanele libe nesihlobo. Kodwa ikheli le-cell B10 liphelele ngokuphelele. Lokhu kusho ukuthi kokubili ikheli lomugqa nekheli lekholomu akushintshi lapho uhudulela ifomula kwamanye amaseli.

Ukuze wenze isixhumanisi sibe esokuphelele, kufanele ucindezele okuthi F4 inombolo edingekayo yezikhathi noma ubeke uphawu lwedola kwesokunxele somugqa kanye/noma ikheli lekholomu. 

Esimweni sethu, kudingeka sibeke izimpawu ezimbili zamadola, njengoba kuboniswe esibonelweni esingenhla.

Nasi isithombe somphumela.

Ake sithathe isibonelo sesibili. Ake sicabange ukuthi sinethebula elifanayo njengasesibonelweni sangaphambilini, ulwazi kuphela olusabalele emigqeni eminingana. Kudingeka sinqume ukuthi iyiphi ingxenye yenani eliphelele elibalelwa kuma-oda omkhiqizo owodwa.

Indlela engcono kakhulu yokwenza lokhu ukusebenzisa umsebenzi I-SUMMESLI. Ngosizo lwayo, kuba nokwenzeka ukuhlanganisa kuphela lawo maseli awela ngaphansi kwesimo esithile. Esibonelweni sethu, lo ngumkhiqizo onikeziwe. Imiphumela etholiwe isetshenziselwa ukunquma isabelo sengqikithi. 

=SUMIF(ububanzi, imibandela, isamba_ububanzi)/isamba esiphelele

Lapha, ikholomu A iqukethe amagama ezimpahla ezakha ububanzi buhlangene. Ikholomu B ichaza ulwazi mayelana nebanga lokuhlanganisa, okuyisamba senani lezimpahla ezilethiwe. Isimo sibhalwe ku-E1, yigama lomkhiqizo, uhlelo olugxile kulo lapho kunqunywa iphesenti.

Ngokuvamile, ifomula izobukeka kanje (uma kunikezwe ukuthi inani eliphelele lizochazwa kuseli B10).

Kungenzeka futhi ukubhala igama ngqo kwifomula.

=СУММЕСЛИ(A2:A9;»cherries»;B2:B9)/$B$10

Uma ufuna ukubala iphesenti lemikhiqizo eminingana ehlukene kusukela enanini, lokhu kwenziwa ngezigaba ezimbili:

1. Into ngayinye ihlanganiswe nenye.
2. Khona-ke umphumela owumphumela uhlukaniswa ngenani eliphelele.

Ngakho-ke, ifomula enquma umphumela wama-cherry nama-apula izoba kanje:

=(СУММЕСЛИ(A2:A9;»cherries»;B2:B9)+СУММЕСЛИ(A2:A9;»apples»;B2:B9))/$B$10

Ungabala kanjani iphesenti lesamba samanani ethebula le-Excel

Masenze itafula elinjalo ngohlu lwabathengisi kanye nevolumu aphumelele ukuxoxisana ngayo. Phansi kwetafula kuneseli lokugcina, elirekhoda ukuthi yimalini bonke bendawonye bakwazi ukudayisa imikhiqizo. Ake sithi sithembise izihloko ezintathu, ingxenye yazo yemali engenayo ephezulu kakhulu, ibhonasi. Kodwa okokuqala udinga ukuqonda ukuthi mangaki amaphesenti emali engenayo iyonke ewela kumthengisi ngamunye.

Engeza ikholomu eyengeziwe kuthebula elikhona.

Kuseli C2, bhala ifomula elandelayo.

=B2/$B$9

Njengoba sesivele sazi, uphawu lwedola lwenza isixhumanisi siphelele. Okusho ukuthi, ayishintshi ngokuya ngokuthi ifomula ikopishwe kuphi noma ihudulwa kusetshenziswa isibambo sokuqedela ngokuzenzakalela. Ngaphandle kokusebenzisa ireferensi ephelele, akunakwenzeka ukwenza ifomula ezoqhathanisa inani elilodwa nelinye inani elithile, ngoba lapho yehliswa phansi, ifomula izoba yilena ngokuzenzakalelayo:

=B3/$B$10

Kudingeka siqinisekise ukuthi ikheli lokuqala liyanyakaza, kanti elesibili alinyakazi.

Ngemva kwalokho, sihudula amanani ngokuqondile kumaseli asele ekholomu sisebenzisa isibambo sokuqedela ngokuzenzakalela.

Ngemva kokusebenzisa ifomethi yephesenti, sithola lo mphumela.

Ungabala kanjani iphesenti lenombolo ku-Excel

Ukuze uthole ukuthi iyiphi ingxenye yenombolo ethile ku-Excel, kufanele uhlukanise inombolo encane ngenkulu bese uphindaphinda yonke into ngo-100.

Intshisekelo ku-Excel inefomethi yayo. Umehluko wayo omkhulu ukuthi iseli elinjalo liphindaphinda inani eliwumphumela ngokuzenzakalelayo ngo-100 futhi lengeze uphawu lwephesenti. Kanjalo, ifomula yokuthola iphesenti ku-Excel ilula nakakhulu: udinga nje ukuhlukanisa inombolo encane ngenkulu. Konke okunye uhlelo luzozibala ngokwalo.

Manje ake sichaze ukuthi isebenza kanjani esibonelweni sangempela.

Ake sithi udale ithebula elibonisa inombolo ethile yezinto ezi-odiwe kanye nenani elithile lemikhiqizo elethiwe. Ukuze uqonde ukuthi yimaphi amaphesenti aye-odwe, kuyadingeka (ifomula ibhalwe ngokusekelwe eqinisweni lokuthi inani eliphelele libhalwe kuseli B, futhi izimpahla ezilethiwe zikuseli C):

1. Hlukanisa inani lezimpahla ezilethiwe ngenani eliphelele. Ukuze wenze lokhu, vele ungene = C2 / B2 kubha yefomula.
2. Okulandelayo, lo msebenzi ukopishelwa enanini elidingekayo lemigqa kusetshenziswa umaka wokuqedela ngokuzenzakalela. Amaseli anikezwe ifomethi ethi “Amaphesenti”. Ukuze wenze lokhu, chofoza inkinobho ehambisanayo eqenjini elithi "Ekhaya".
3. Uma kunezinombolo eziningi kakhulu noma ezimbalwa kakhulu ngemva kwephoyinti ledesimali, ungakwazi ukulungisa lesi silungiselelo.

Ngemuva kwalokhu kukhohlisa okulula, sithola iphesenti kuseli. Esimweni sethu, ibhalwe kukholamu D.

Ngisho noma kusetshenziswa ifomula ehlukile, akukho lutho olushintshayo ezenzweni.

Inombolo oyifunayo kungenzeka ingabi kunoma yimaphi amaseli. Khona-ke kuyodingeka ifakwe kufomula mathupha. Kwanele nje ukubhala inombolo ehambisanayo esikhundleni sempikiswano edingekayo.

= 20/150

Ungabala kanjani iphesenti lamanani amaningi ukusuka esambeni sethebula

Esibonelweni sangaphambilini, kwakukhona uhlu lwamagama abathengisi, kanye nenani lemikhiqizo edayisiwe, abakwazi ukuyifinyelela. Besidinga ukunquma ukuthi umnikelo womuntu ngamunye ububaluleke kangakanani emalini yonke yenkampani.

Kodwa ake sicabange ngesimo esihlukile. Sinohlu lapho amanani afanayo echazwa kumaseli ahlukene. Ikholomu yesibili iwulwazi ngamavolumu okuthengiswayo. Kudingeka sibale isabelo somkhiqizo ngamunye enanini lemali engenayo, evezwe njengephesenti.

Ake sithi sidinga ukuthola ukuthi yimaphi amaphesenti esamba semali engenayo evela kutamatisi, asakazwa emigqeni eminingi ebangeni. Ukulandelana kwezenzo kanje:

1. Cacisa umkhiqizo kwesokudla.
   

   

2. Senza ukuthi ulwazi olukuseli E2 luboniswe njengephesenti. 
3. Faka isicelo I-SUMMESLI ukuhlanganisa utamatisi nokunquma iphesenti. 

Ifomula yokugcina izoba elandelayo.

=СУММЕСЛИ($A$2:$A$8;$E$1;$B$2:$B$8)/B9

Isebenza kanjani le fomula

Sisebenzise umsebenzi SUMMESLEY, ukwengeza amanani amaseli amabili, uma, njengomphumela wokuhlola ukuthobela kwawo isimo esithile, i-Excel ibuyisela inani TRUE.

I-syntax yalo msebenzi ilula kakhulu. Ububanzi bemibandela yokuhlola bubhalwe njengempikiswano yokuqala. Isimo sibhalwe endaweni yesibili, futhi ububanzi obuzofingqwa busendaweni yesithathu. 

Ukuphikisana ongakukhetha. Uma ungayicacisi, i-Excel izosebenzisa eyokuqala njengeyesithathu.

Ungawengeza kanjani amaphesenti enombolweni ku-Excel

Kwezinye izimo zempilo, ukwakheka okujwayelekile kwezindleko kungashintsha. Kungenzeka ukuthi ezinye izinguquko kuzodingeka zenziwe. 

Indlela yokwengeza iphesenti elithile enombolweni ilula kakhulu.

=Inani*(1+%)

Isibonelo, ngenkathi useholidini, ungase ufune ukwandisa ibhajethi yakho yokuzijabulisa ngo-20%. Kulokhu, le fomula izothatha ifomu elilandelayo.

=A1*(1-20%)

Umehluko phakathi kwezinombolo njengephesenti ku-Excel

Ifomula yokunquma umehluko phakathi kwamaseli noma izinombolo ngazinye njengephesenti ine-syntax elandelayo.

(BA)/A

Ukusebenzisa le fomula ekusebenzeni kwangempela, udinga ukuqonda ngokucacile ukuthi iyiphi inombolo okufanele uyifake.

Isibonelo esincane: ake sithi ulethwe ama-apula angu-80 esitolo izolo, kuyilapho namuhla alethe amaningi afinyelela kwangu-100.

Umbuzo: zingaki ezinye ezilethwe namuhla? Uma ubala ngokwale fomula, ukwanda kuzoba ngamaphesenti angama-25.

Ungawathola kanjani amaphesenti phakathi kwezinombolo ezimbili kumakholomu amabili ku-Excel

Ukunquma iphesenti phakathi kwezinombolo ezimbili kumakholomu amabili, kufanele usebenzise ifomula engenhla. Kodwa setha amanye njengamakheli eselula.

Ake sithi sinezintengo zomkhiqizo ofanayo. Ikholomu eyodwa iqukethe enkulu, futhi ikholomu yesibili iqukethe encane. Kudingeka siqonde izinga inani elishintshe ngalo uma kuqhathaniswa nesikhathi esidlule.

Ifomula ifana naleyo enikezwe esibonelweni sangaphambilini, nje ezindaweni ezidingekayo udinga ukufaka hhayi amaseli asemigqa ehlukene, kodwa kumakholomu ahlukene.

Ukuthi ifomula izobukeka kanjani esimweni sethu ibonakala ngokucacile kusithombe-skrini.

Kusele ukuthatha izinyathelo ezimbili ezilula:

1. Setha ifomethi yephesenti.
2. Hudula ifomula kuwo wonke amanye amaseli.

Ungaphindaphinda kanjani ngamaphesenti ku-excel

Kwesinye isikhathi ungadinga ukuphindaphinda okuqukethwe kwamaseli ngephesenti elithile ku-Excel. Ukuze wenze lokhu, udinga nje ukufaka ukusebenza kwe-arithmetic okujwayelekile ngendlela yenombolo yeseli noma inombolo, bese ubhala inkanyezi (*), bese ubhala iphesenti bese ufaka uphawu lwe-%.

Amaphesenti angaphinda aqukethwe kwenye iseli. Kulesi simo, udinga ukucacisa ikheli leseli eliqukethe amaphesenti njengesiphindaphindi sesibili.

Ungalithola kanjani iphesenti phakathi kwezinombolo ezimbili emigqeni emi-2 ku-Excel

Ifomula iyafana, kodwa esikhundleni senombolo encane, udinga ukunikeza isixhumanisi kuseli eliqukethe inombolo encane, futhi esikhundleni senombolo enkulu, ngokulandelana.

Ungabala kanjani inzalo yemalimboleko usebenzisa i-Excel

Ngaphambi kokuhlanganisa i-calculator yemalimboleko, udinga ukucabangela ukuthi zimbili izinhlobo zokuqongelela kwazo. Eyokuqala ibizwa ngokuthi i-annuity. Kusho ukuthi nyanga zonke inani lihlala lifana.

Okwesibili kuhlukaniswa, lapho izinkokhelo zenyanga zincishiswa.

Nansi indlela elula yokubala izinkokhelo ze-annuity ku-Excel.

1. Dala ithebula elinedatha yokuqala.
2. Dala ithebula lokukhokha. Kuze kube manje, ngeke kube khona ulwazi oluqondile kuyo.
3. Faka ifomula =ПЛТ($B$3/12; $B$4; $B$2) esitokisini sokuqala. Lapha sisebenzisa izinkomba eziphelele.
   

   

Ngendlela yokukhokha ehlukene, ulwazi lokuqala luhlala lufana. Bese udinga ukwakha ilebula yohlobo lwesibili.

Ngenyanga yokuqala, ibhalansi yesikweletu izolingana nenani lemali mboleko. Okulandelayo, ukuze uyibale, udinga ukusebenzisa ifomula =ЕСЛИ(D10>$B$4;0;E9-G9), ngokwepuleti lethu. 

Ukuze ubale inkokhelo yenzalo, udinga ukusebenzisa le fomula: =E9*($B$3/12).

Okulandelayo, lawa mafomula afakwa kumakholomu afanelekile, abese edluliselwa kuthebula lonke kusetshenziswa umaka wokuqedela ngokuzenzakalela.