Imisebenzi yezibalo ye-Excel: Isifundo esinezibonelo zocwaningo, ingxenye yesine

I-Excel ihlinzeka ngezinhlobonhlobo zemisebenzi yezibalo eyenza izibalo kusukela kuncazelo eyisisekelo, i-median, nemodi kuya emisebenzini yokubheka.

Kulesi sihloko sizocubungula ngokujulile imisebenzi yosesho.

Sicela uqaphele ukuthi eminye imisebenzi yezibalo yethulwe ezinguqulweni zakamuva ze-Excel ngakho-ke ayitholakali kuzinguqulo ezindala.

Isikhathi sokufunda esilinganiselwe: 18 imizuzu

Sesha imisebenzi

MAX

Umsebenzi MAX ye-Excel isohlwini lwesigaba se-Microsoft Excel Statistical Functions. Ibuyisela inani elikhulu ohlwini lwamanani. MAX imele ubuningi futhi uma ucacisa uhlu lwamanani lubheka inani eliphezulu kakhulu futhi ibuyisela lelo nani kumphumela.

-syntax

= MAX(number1, [number2], …)

izifundo

• number1:  inombolo, iseli eliqukethe inombolo, noma ububanzi bamaseli aqukethe izinombolo ofuna ukuthola kuwo inombolo enkulu kunazo zonke.
• [number2]:  inombolo iseli eliqukethe inombolo noma ububanzi bamaseli aqukethe izinombolo ofuna ukuthola kuwo inombolo enkulu kunazo zonke.

Ngokwesibonelo

Ukwenza kahle umsebenzi we-MAX sidinga ukuwuzama ngesibonelo futhi ngezansi kukhona ongazama:

Esibonelweni esilandelayo, sifake izinombolo ngqo emsebenzini ngokuzihlukanisa ngokhefana.

Nota: ungaphinda ufake inombolo usebenzisa izingcaphuno ezikabili.

Esibonelweni esilandelayo, sibhekise ububanzi futhi umphumela wabuyisela u-1861 njengenani elikhulu kunawo wonke. Ungaphinda ubhekisele kumalungu afanayo.

Esibonelweni esilandelayo, sihlangabezane nenani lephutha futhi umsebenzi ubuyisele inani lephutha kumphumela.

MAXA

Umsebenzi we-Excel Maxa ifana kakhulu ne Umsebenzi we-Excel Max.

Umehluko kuphela phakathi kwale misebenzi emibili uvela lapho ukuphikisana kunikezwa emsebenzini njengereferensi yeseli noma uchungechunge lwamaseli.

Umsebenzi Max kuziba amanani anengqondo kanye nombhalo ngenkathi umsebenzi Maxa inani elinengqondo liyabala TRUE njengo-1, inani elinengqondo FALSE njengo-0 kanye namanani ombhalo njengo-0.

Umsebenzi MAXA I-Excel ibuyisela inani elikhulu kunawo wonke ukusuka kusethi yamanani ezinombolo, ibala umbhalo kanye nevelu enengqondo FALSE njengenani lika-0 nokubala inani elinengqondo TRUE njengenani elingu-1.

-syntax

= MAXA(number1, [number2], …)

izifundo

• number1:  inombolo (noma amalungu afanayo amanani ezinombolo), iseli eliqukethe inombolo, noma ububanzi bamaseli aqukethe izinombolo ofuna ukuthola kuwo inombolo enkulu kunazo zonke.
• [number2]:  inombolo iyiseli eliqukethe inombolo (noma amalungu afanayo amanani ezinombolo) noma ububanzi bamaseli aqukethe izinombolo ofuna ukuthola kuwo inombolo enkulu kunazo zonke.

Ezinguqulweni zamanje ze-Excel (i-Excel 2007 nakamuva), ungakwazi ukunikeza ama-agumenti ezinombolo angafika kwangu-255 kumsebenzi we-Maxa, kodwa ku-Excel 2003 umsebenzi ungamukela kuphela ama-agumenti ezinombolo angu-30.

esempi

Isiqephu 1

Iseli B1 kuspredishithi esilandelayo sibonisa umsebenzi Excel Maxa, esetshenziselwa ukubuyisa inani elikhulu kunawo wonke kusethi yamanani kumaseli A1-A5.

Isiqephu 2

Iseli B1 kuspredishithi esilandelayo sibonisa umsebenzi Excel Maxa, esetshenziselwa ukubuyisa inani elikhulu kunawo wonke kusethi yamanani kumaseli A1-A3.

Qaphela ukuthi inani TRUE kuseli A1 yesipredishithi sithathwa njengenani lenombolo 1 ngomsebenzi Maxa. Ngakho-ke, leli inani elikhulu kunawo wonke ebangeni A1-A3.

Izibonelo ezengeziwe zomsebenzi Excel Maxa ahlinzekwa ku Iwebhusayithi ye-Microsoft Office .

Iphutha lomsebenzi MAXA

Uma uthola iphutha kumsebenzi Maxa ku-Excel, leli kungenzeka iphutha #VALORE!: Kuvela uma amanani anikezwe ngokuqondile kumsebenzi Maxa azizona izinombolo.

MAXIFS

Umsebenzi we-Excel Maxifs kuwumsebenzi wosesho obuyisela inani eliphezulu ukusuka kusethi encane yamanani ashiwo ngokusekelwe kumbandela wokunquma owodwa noma ngaphezulu.

-syntax

= MAXIFS( max_range, criteria_range1, criteria1, [criteria_range2, criteria2], ... )

izifundo

• max_range:  Uhlu lwamanani ezinombolo (noma ububanzi bamaseli aqukethe amanani ezinombolo), ofuna ukubuyisela kuwo inani eliphakeme uma imibandela ifinyelelwa.
• criteria_range1:  amanani afanayo (noma ububanzi bamaseli aqukethe amanani) okufanele kuhlolwe ngawo criteria1 .(Lawa malungu afanayo kufanele wonke afane nobude obufanayo nobubanzi_obukhulu ).
• criteria1: Isimo sokuhlolwa ngokuhlonipha amanani aku- criteria_range1.
• [criteria_range2, criteria2], [criteria_range3, criteria3], ...: Amalungu afanayo engeziwe ongawakhetha wamanani azohlolwa kanye nezimo ezifanele zokuhlolwa.

Umsebenzi Maxifs ingaphatha amapheya ezihloko ezingafika ku-126 criteria_range e criteria.

Indlela ngayinye enikeziwe ingaba:

• inani lenombolo (elingaba inombolo ephelele, idesimali, idethi, isikhathi noma inani elinengqondo) (isb. 10, 01/01/2017, TRUE)

noma

• iyunithi yezinhlamvu yombhalo (isb. “Igama”, “MercoleKwe)

noma

• isisho (isibonelo “>1”, “<>0”).

Kungani criteria okuhlobene nombhalo ungasebenzisa ama-wildcards:

• ? ukufanisa nanoma yimuphi uhlamvu olulodwa
• * ukufanisa noma yikuphi ukulandelana kwezinhlamvu.

Uma i- criteria iyunithi yezinhlamvu yombhalo noma isisho, lokhu kufanele kunikezwe kumsebenzi Maxifs ezicashuniwe.

Umsebenzi Maxifs Ayizwela kakhulu. Ngakho-ke, ngokwesibonelo, lapho uqhathanisa amanani ku criteria_range nami criteria, izintambo zombhalo “TEXT"E"text” kuzobhekwa njengokulinganayo.

Umsebenzi Maxifs yethulwa okokuqala ku-Excel 2019 ngakho-ke ayitholakali kuzinguqulo zangaphambili ze-Excel.

esempi

Isipredishithi esingezansi sibonisa idatha yokuthengisa yekota yabamele abadayisi abathathu.

Umsebenzi Maxifs ingasetshenziswa ukuthola inani eliphezulu lokuthengisa lanoma iyiphi ikota, indawo, noma i-rep rep (noma iyiphi inhlanganisela yekota, indawo, kanye ne-sales rep).

Ake sibheke izibonelo ezilandelayo.

Isiqephu 1

Ukuze uthole inani eliphezulu lokuthengisa ngekota yokuqala:

=MAXIFS( D2:D13, A2:A13, 1 )

okunikeza umphumela $ 456.000.

Kulesi sibonelo, i-Excel Maxifs ihlonza imigqa lapho inani elikukholomu A lilingana no-1 futhi ibuyisela inani eliphakeme ukusuka kumanani ahambisanayo kukholomu D.

Okusho ukuthi, umsebenzi uthola inani eliphezulu lamanani angu-$223.000, $125.000 kanye nama-$456.000 (kusuka kumaseli D2, D3 kanye no-D4).

Isiqephu 2

Futhi, sisebenzisa isipredishithi sedatha esingenhla, singasebenzisa nomsebenzi we-Maxifs ukuze sithole inani eliphezulu lokuthengisa lika-“Jeff”, phakathi kwekota 3 nelesi-4:

=MAXIFS( D2:D13, A2:A13, ">2", C2:C13, "Jeff" )

Le fomula ibuyisela umphumela $ 310.000 .

Kulesi sibonelo, i-Excel Maxifs ikhomba imigqa lapho:

• Inani elikukholomu A likhulu kuno-2

E

• Okufakiwe kukholamu C kulingana nokuthi “Jeff”

futhi ibuyisela inani eliphezulu lamanani ahambisanayo kukholomu D.

Okusho ukuthi, le fomula ithola inani eliphakeme lama- $ 310.000 nama- $ 261.000 (kusuka kumaseli D8 no-D11).

Xhumana ne Iwebhusayithi ye-Microsoft Office ukuze uthole imininingwane eyengeziwe ngezibonelo zomsebenzi we-Excel Maxifs.

Iphutha lomsebenzi MAXIFS

Uma uthola iphutha ku-Excel function Maxifs, kungenzeka kube okukodwa kokulandelayo:

#VALUE!: Ihlola uma ama-array max_range e criteria_range enikeziwe ayinabo bonke ubude obufanayo.

@NAME?: Kuvela uma usebenzisa inguqulo endala ye-Excel (pre-2019), engasisekeli isici Maxifs.

MIN

Umsebenzi MIN kuwumsebenzi wosesho obuyisela inani eliphansi kakhulu ohlwini lwamanani. MIN imele ubuncane futhi uma ucacisa uhlu lwamanani isesha inani eliphansi kakhulu kulo futhi ibuyisela lelo nani kumphumela.

-syntax

= MIN(number1, [number2], …)

izifundo

• number1:  inombolo, iseli eliqukethe inombolo, noma uhla lwamaseli aqukethe izinombolo ofuna ukuthola kuzo inombolo encane kakhulu.
• [number2]:  inombolo, iseli eliqukethe inombolo, noma uhla lwamaseli aqukethe izinombolo ofuna ukuthola kuzo inombolo encane kakhulu.

Ngokwesibonelo

Esibonelweni esilandelayo, sifake izinombolo ngqo emsebenzini ngokuzihlukanisa ngokhefana.

Ungaphinda ufake inombolo usebenzisa izingcaphuno ezimbili. Manje, esibonelweni esilandelayo, sibhekise ububanzi futhi umphumela obuyisiwe ngu-1070.

Esibonelweni esilandelayo, sihlangabezane nenani lephutha futhi umsebenzi ubuyisele inani lephutha kumphumela.

MINA

Umsebenzi we-Excel MINA ifana kakhulu ne Umsebenzi we-Excel MIN.

Umehluko kuphela phakathi kwale misebenzi emibili uvela lapho ukuphikisana kunikezwa emsebenzini njengereferensi yeseli noma uchungechunge lwamaseli.

Kulokhu umsebenzi MIN kuziba amanani anengqondo kanye nombhalo ngenkathi umsebenzi MINA inani elinengqondo liyabala TRUE njengo-1, inani elinengqondo FALSE njengo-0 kanye namanani ombhalo njengo-0.

Umsebenzi MINA I-Excel ibuyisela inani elincane ukusuka kusethi yamanani ezinombolo, ibala umbhalo kanye nevelu enengqondo FALSE njengenani lika-0 nokubala inani elinengqondo TRUE njengenani elingu-1.

-syntax

= MINA( number1, [number2], ... )

izifundo

• number1:  inombolo, iseli eliqukethe inombolo, noma uhla lwamaseli (noma amalungu afanayo amanani ezinombolo) aqukethe izinombolo ofuna ukuthola kuzo inombolo encane kakhulu.
• [number2]:  inombolo, iseli eliqukethe inombolo, noma uhla lwamaseli (noma amalungu afanayo amanani ezinombolo) aqukethe izinombolo ofuna ukuthola kuzo inombolo encane kakhulu.

Ezinguqulweni zamanje ze-Excel (i-Excel 2007 nakamuva), unganikezela ngama-agumenti ezinombolo angama-255 emsebenzini. MINA, kodwa ku-Excel 2003 umsebenzi ungamukela kuphela ama-agumenti ezinombolo angama-30.

esempi

Isiqephu 1

Iseli B1 kuspredishithi esilandelayo sibonisa umsebenzi we-Excel MINA, osetshenziselwa ukubuyisa inani elincane kusethi yamanani kumaseli A1-A5.

Isiqephu 2

Iseli B1 kuspredishithi esilandelayo sibonisa umsebenzi we-Excel MINA, esetshenziselwa ukubuyisa inani elincane kusethi yamanani kumaseli A1-A3.

Khumbula ukuthi inani TRUE esitokisini A1 yesipredishithi sithathwa njengenani lenombolo 1 ngomsebenzi MINA. Ngakho-ke, leli inani elincane kakhulu kububanzi A1-A3.

Izibonelo ezengeziwe zomsebenzi we-Excel MINA ahlinzekwa ku Iwebhusayithi ye-Microsoft Office .

Iphutha lomsebenzi MINA

Uma uthola iphutha kumsebenzi MINA ku-Excel, leli kungenzeka iphutha #VALORE!. Kuvela uma amanani anikezwe umsebenzi we-MINA engezona izinombolo.

MINIFS

Umsebenzi we-Excel MINIFS kuwumsebenzi wosesho obuyisela inani eliphansi ukusuka kusethi encane yamanani ashiwo ngokusekelwe kumbandela wokunquma owodwa noma ngaphezulu.

-syntax

= MINIFS( min_range, criteria_range1, criteria1, [criteria_range2, criteria2], ... )

izifundo

• min_range:  Uhlu lwamanani ezinombolo (noma ububanzi bamaseli aqukethe amanani ezinombolo), ofuna ukubuyisela kuwo inani eliphakeme uma imibandela ifinyelelwa.
• criteria_range1:  amanani afanayo (noma ububanzi bamaseli aqukethe amanani) okufanele kuhlolwe ngawo criteria1 .(Lolu hlu kufanele lulingane nobude min_range ).
• criteria1: Isimo sokuhlolwa ngokuhlonipha amanani aku- criteria_range1.
• [criteria_range2, criteria2], [criteria_range3, criteria3], ...: Amalungu afanayo engeziwe ongawakhetha wamanani azohlolwa kanye nezimo ezifanele zokuhlolwa.

Umsebenzi Minifs ingaphatha amapheya ezihloko ezingafika ku-126 criteria_range e criteria.

Indlela ngayinye enikeziwe ingaba:

• inani lenombolo (elingaba inombolo ephelele, idesimali, idethi, isikhathi noma inani elinengqondo) (isb. 10, 01/01/2017, TRUE)

noma

• iyunithi yezinhlamvu yombhalo (isb. “Igama”, “MercoleKwe)

noma

• isisho (isibonelo “>1”, “<>0”).

Kungani criteria okuhlobene nombhalo ungasebenzisa ama-wildcards:

• ? ukufanisa nanoma yimuphi uhlamvu olulodwa
• * ukufanisa noma yikuphi ukulandelana kwezinhlamvu.

Uma i- criteria iyunithi yezinhlamvu yombhalo noma isisho, lokhu kufanele kunikezwe kumsebenzi Minifs ezicashuniwe.

Umsebenzi Minifs Ayizwela kakhulu. Ngakho-ke, ngokwesibonelo, lapho uqhathanisa amanani ku criteria_range nami criteria, izintambo zombhalo “TEXT” kanye “nombhalo” kuzobhekwa njengento efanayo.

Umsebenzi Minifs yethulwa okokuqala ku-Excel 2019 ngakho-ke ayitholakali kuzinguqulo zangaphambili ze-Excel.

esempi

Isipredishithi esingezansi sibonisa idatha yokuthengisa yekota yabathengisi abangu-3.

Umsebenzi Minifs ingasetshenziswa ukuthola inani elincane lokuthengisa lanoma iyiphi ikota, isifunda noma omele ukuthengisa.

Lokhu kuboniswa ezibonelweni ezilandelayo.

Isiqephu 1

Ukuze uthole inani elincane lokuthengisa phakathi nekota yokuqala:

=MINIFS( D2:D13, A2:A13, 1 )

okunikeza umphumela $ 125.000 .

Kulesi sibonelo, i-Excel Minifs ikhomba imigqa lapho inani elikukholomu A lilingana no-1 futhi ibuyisela inani eliphansi ukusuka kumanani ahambisanayo kukholomu D.

Okusho ukuthi, umsebenzi uthola ubuncane bamanani angu-$223.000, $125.000, kanye nama-$456.000 (kusuka kumaseli D2, D3, kanye no-D4).

Isiqephu 2

Futhi, sisebenzisa isipredishithi sedatha esingenhla, singaphinda sisebenzise umsebenzi Minifs ukuthola inani elincane lokuthengisa lika-“Jeff” ngesikhathi sekota 3 nelesi-4:

=MINIFS( D2:D13, A2:A13, ">2", C2:C13, "Jeff" )

Le fomula ibuyisela umphumela $261.000 .

Kulesi sibonelo, i-Excel Minifs ikhomba imigqa lapho:

• Inani elikukholomu A likhulu kuno-2

E

• Okufakiwe kukholamu C kulingana nokuthi “Jeff”

futhi ibuyisela ubuncane bamanani ahambisanayo kukholamu D.

Okusho ukuthi, le fomula ithola ubuncane bamanani angu-$310.000 no-$261.000 (kusuka kumaseli D8 no-D11).

Ukuze uthole izibonelo ezengeziwe zomsebenzi we-Excel Minifs, bonisana ne Iwebhusayithi ye-Microsoft Office .

Iphutha lomsebenzi MINIFS

Uma uthola iphutha kumsebenzi we-Excel Minifs, kungenzeka kube ngesinye salezi zizathu ezilandelayo:

• #VALORE! -Ihlola uma ama-arrays min_range e criteria_range enikeziwe ayinabo bonke ubude obufanayo.
• #NOME? - Kuvela uma usebenzisa inguqulo endala ye-Excel (ngaphambi kuka-2019), engasisekeli isici Minifs.

LARGE

Umsebenzi we-Excel Large kuwumsebenzi wosesho obuyisela inani elikhulu lika-k'th ohlwini lwamanani ezinombolo.

-syntax

= LARGE( array, k )

izifundo

• amalungu afanayo - Uhlu lwamanani ezinombolo ukuze useshe inani elikhulu kakhulu le-k'th.
• K - Inkomba, okungukuthi umsebenzi ubuyisela inani elikhulu kakhulu le-kth ukusukaarray enikeziwe.

I-agumenti yamalungu afanayo inganikezwa emsebenzini ngokuqondile noma njengereferensi yobubanzi bamaseli aqukethe amanani ezinombolo. Uma amanani ebangeni leseli elinikeziwe engamavelu ombhalo, lawa manani ayazitshwa.

Ngokwesibonelo

Ispredishithi esilandelayo sibonisa umsebenzi we-Excel Large, esetshenziselwe ukubuyisa amanani amakhulu oku-1, 2, 3, 4, kanye ne-5 kusukela kusethi yamanani kumaseli A1-A5.

Eminye imicabango kuspredishithi esingenhla:

• Esitokisini B1, lapho u-k esethelwe ku-1, umsebenzi Large yenza isenzo esifanayo ne Umsebenzi we-Excel Max ;
• Esitokisini B5, uma u-k esethelwe ku-5 (inani lamanani ohlwini olunikeziwe), umsebenzi Omkhulu wenza isenzo esifanayo nese Umsebenzi we-Excel Min .

Eminye imininingwane nezibonelo ze-Excel Large function ingatholakala ku- Iwebhusayithi ye-Microsoft Office .

Iphutha lomsebenzi LARGE

Uma i-Excel Large ibuyisela iphutha, kungenzeka kube okukodwa kokulandelayo:

• #NUM! - Kwenzeka uma:
  • Inani elinikeziwe lika-k lingaphansi kuka-1 noma likhulu kunenani lamanani ohlwini olunikeziwe
    noma
    Thearray enikeziwe ayinalutho.
• #VALUE! - Kuvela uma i-k enikeziwe ingeyona inombolo.

Nokho, amaphutha angenzeka ekubalweni komsebenzi OMKHULU ngisho noma inani elinikeziwe lika-k liphakathi kuka-1 kanye nenani lamanani ohlwini olunikeziwe. Isizathu esingaba isizathu kungaba ukuthi amanani ombhalo, okuhlanganisa nokumelwa kombhalo kwezinombolo ngaphakathi kwamalungu afanayo anikeziwe, azitshwa umsebenzi Omkhulu. Ngakho-ke, le nkinga ingenzeka uma amanani ohlwini olunikeziwe emelela izinombolo kunokuba kube amanani angempela ezinombolo.

Isixazululo singatholakala ngokuguqula wonke amanani we-array abe amanani ezinombolo. 

SMALL

Umsebenzi Omncane we-Excel uwumsebenzi wokubheka obuyisela inani elincane le-kth kusuka kumanani amaningi ezinombolo.

-syntax

= SMALL( array, k )

izifundo

• array - Uhlu lwamanani ezinombolo ukucinga inani elikhulu kakhulu le-k'th.
• K - Inkomba, okungukuthi umsebenzi ubuyisela inani elikhulu kakhulu le-kth ukusukaarray enikeziwe.

I-agumenti yamalungu afanayo inganikezwa emsebenzini ngokuqondile noma njengereferensi yobubanzi bamaseli aqukethe amanani ezinombolo. Uma amanani ebangeni leseli elinikeziwe engamavelu ombhalo, lawa manani ayazitshwa.

Ngokwesibonelo

Ispredishithi esilandelayo sibonisa umsebenzi we-Excel Small, esetshenziselwe ukubuyisa amanani amancane oku-1, 2, 3, 4, kanye ne-5 kusukela kusethi yamanani kumaseli A1-A5.

Esibonelweni kuyadingeka ukucabangela ukuthi::

• Kuseli B1, lapho u-k esethelwe ku-1, umsebenzi Small yenza isenzo esifanayo ne Umsebenzi we-Excel Min ;
• Kuseli B5, lapho u-k usethelwe ku-5 (inani lamanani ku-array enikeziwe), umsebenzi Small yenza isenzo esifanayo ne Umsebenzi we-Excel Max .

Imininingwane eyengeziwe nezibonelo zomsebenzi we-Excel Small ahlinzekwa ku Iwebhusayithi ye-Microsoft Office .

Iphutha lomsebenzi SMALL

Uma i-Excel SMALL ibuyisela iphutha, kungenzeka kube okukodwa kokulandelayo:

• #NUM! - Kwenzeka uma:
  • Inani elinikeziwe lika-k lingaphansi kuka-1 noma likhulu kunenani lamanani ohlwini olunikeziwe
    noma
    Uhlu olunikeziwe alunalutho.
• #VALUE! - Kuvela uma i-k enikeziwe ingeyona inombolo.

Nokho, amaphutha angenzeka ekubalweni komsebenzi LARGE noma ngabe inani elinikeziwe lika-k liphakathi kuka-1 kanye nenani lamanani ku-array enikeziwe. Isizathu okungenzeka kube ukuthi amanani ombhalo, okuhlanganisa nokumelwa kombhalo kwezinombolo ngaphakathi kwearray uma nje, azinakwa umsebenzi Omkhulu. Ngakho-ke, le nkinga ingenzeka uma amanani ku-array okunikezwayo kukhona izethulo zombhalo zezinombolo kunamanani ezinombolo zangempela.

Isixazululo singafinyelelwa ngokuguqula wonke amanani wearray ngamanani amanani. 

Ukufundwa Okuhlobene

• Imisebenzi yezibalo ye-Excel: Isifundo esinezibonelo, ingxenye yokuqala
• Amashadi e-Excel, ayini, indlela yokudala ishadi nokuthi ungakhetha kanjani ishadi elifanele
• Isicelo se-JSP, zonke izindlela zesicelo esibekiwe
• Ukuphathwa Kwephrojekthi ekuqeqesheni ulwazi
• Amathebula e-Pivot: ukuthi ayini, angakha kanjani ku-Excel naku-Google. Isifundo esinezibonelo

Ercole Palmeri