Imisebenzi ye-MINIFS ne-MAXIFS ku-Excel: ukusetshenziswa, amaqhinga, nezibonelo

Ukwenza ukuhlaziya idatha nge-Excel kungelinye lamakhono abaluleke kakhulu kulabo abaphatha ulwazi kunoma iyiphi inkampani noma iphrojekthi. Uma kuziwa ekuhlungeni nasekuqhathaniseni inani elikhulu lolwazi, imisebenzi ye-Excel MINIFS y MAXIFS Baba ngabangane bakho abakhulu ukusheshisa izibalo futhi bakhiphe ulwazi lwamasu ngaphandle kokulahleka kumafomula ayinkimbinkimbi.

Eminyakeni yamuva nje, i-Excel ifake imisebenzi ethuthukisiwe ukuze kutholwe amanani amancane naphezulu ngaphansi kwezimo ezithile, into ebidingeka ngaphambilini. tricks kanye nezinhlanganisela ezilandelanayo. Uma ufuna ukusebenzisa lawa mathuluzi njengochwepheshe futhi uyeke ukuchitha isikhathi ngezinqubo ezenziwa ngesandla, hlala ubukele: nali inkomba iphelele futhi igcwele izibonelo ezingokoqobo ukubusa MINIFS y MAXIFS, xazulula ubuhlungu bekhanda obujwayelekile futhi uthole okuningi kumaspredishithi akho.

Ihlinzeka ngani imisebenzi ye-MINIFS kanye ne-MAXIFS?

Lapho kufika I-Excel 2016, le misebenzi yengezwe kubasebenzisi abadinga ukuhlunga idatha futhi bathole ubuncane noma ubukhulu bobubanzi ngokusekelwe kumbandela owodwa noma ngaphezulu. Lokhu kuwusizo ikakhulukazi ekusebenzeni nezinkulu yolwazi, ingxenye ngezinsuku, iminyango, imikhiqizo, izifunda nanoma iyiphi ikholomu okudingeka uyisebenzise njengesihlungi.

MINIFS y MAXIFS Bahlaziya idatha belandela imibandela ethile obanikeza yona, ngaphandle kokuthi baphendukele kumafomula e-matrix anzima noma izihlungi ezenziwa ngesandla eziyinkimbinkimbi. Ukuguquguquka kwayo: vumela kufikela ku-126 imibandela/ ububanzi ngamapheya, ukuzivumelanisa cishe nanoma yimuphi umbuzo odinga ukuwenza.

Izicelo kanye nezinzuzo ekuhlaziyeni idatha

Ukusebenziseka kwale misebenzi kudlulela ngalé kokuthola inombolo ephezulu noma ephansi kakhulu. Ingabe usebenza kwezezimali, ukuthengisa, izinsiza zabantu, ukumaketha, noma imfundo? Akunandaba umkhakha, ngoba MINIFS y MAXIFS lula imisebenzi efana nale:

• Thola izinyanga ngemali engenayo engcono kakhulu nembi kakhulu ngokusekelwe emikhankasweni noma izikhathi.
• Hlaziya ubuncane kanye nemiphumela ephezulu yezemfundo ebekwe yizindawo, amakilasi noma izivivinyo.
• Lawula amazinga esitoko aphansi kakhulu omkhiqizo esifundazweni esithile kuphela.
• Landelela umfutho wegazi ophansi wesiguli phakathi kokuvakasha noma umphumela omuhle kakhulu womtholampilo eqenjini.
• Khomba isisebenzi esithola amaphuzu aphansi kakhulu emsebenzini, kodwa kuphela phakathi kwalabo abaseshifu ethile.

Kuzo zonke izimo, ukunemba nesivinini sokuhlaziya sithuthuka kakhulu.Amaphutha okwenziwa ngesandla ayasuswa, ukukhiqizwa kwempendulo kuyazenzakalela, futhi amadeshibhodi angadalwa abuyekezwa ngokuzenzakalelayo lapho idatha entsha yengezwa.

Okhiye bokulungiselela kahle nokuhlela idatha

Umlingo we MINIFS y MAXIFS Iyasebenza, kodwa inqobo nje uma idatha ihlanzekile futhi yakhiwe kahle. Nazi izinyathelo ezibalulekile Ukuze usebenzise ngokugcwele lezi zici:

• Ukuhlanzwa kwedatha: Susa izimpinda, lungisa amaphutha, futhi uqinisekise ukuthi ayikho imigqa engenalutho noma idatha engahambisani kumakholomu ahilelekile.
• Ifomethi yethebula: Okuguquguqukayo ngakunye kufanele kube kukholomu ehlukile, futhi umugqa ngamunye kufanele ube umbono ohlukile. Sebenzisa izihloko ezicacile.
• Indlela eqondanisiwe: Bonke ububanzi obusetshenzisiwe (idatha nemibandela) kufanele babe nosayizi ofanayo.
• Amazinga aqanjiwe: Uma usebenza ngedatha efanayo njalo, qamba ububanzi bakho ukuze ugweme amaphutha futhi uthuthukise ukufundeka kwamafomula akho.

Khumbula, ukwakheka okunobudlabha kuvame ukuba umthombo wamaphutha emiphumeleni noma ukuphahlazeka kusipredishithi.

I-syntax enemininingwane nendlela yokubhala MINIFS/MAXIFS

I-syntax ilula, kodwa kunemininingwane ebalulekile yokugwema amaphutha:

• MINIFS(ububanzi_bobuncane, imibandela_ububanzi1, umbandela1, , …)
• MAXIFS(ububanzi_ubukhulu, imibandela_ububanzi1, imibandela1, , …)

Kuyo yomibili imisebenzi, i-agumenti yokuqala ububanzi bamanani ofuna ukuthola ubuncane noma ubukhulu bawo. Bese, wengeza amapheya ububanzi bemibandela y izimo. Isibonelo:

• =MINIFS(A2:A100, B2:B100, «>=20»): Ithola inani eliphansi ku-A2:A100, kuphela phakathi kwaleyo migqa lapho i-B2:B100 inkulu noma ilingana no-20.
• =MAXIFS(D2:D100, E2:E100, «Ukuthengisa», F2:F100, «2023»): Ibuyisela inani elikhulu ku-D2:D100 kuphela kumarowu lapho u-E2:E100 ethi “Ukuthengisa” futhi okuthi F2:F100 kuthi “2023”.

Umbandela ungakhiwa kusetshenziswa ama-opharetha afana no-“>”, “<“, “>=”, “<=”, umbhalo oqondile, noma izinkomba zeseli. Kubalulekile ukukhumbula ukuthi imibandela yombhalo kufanele ifakwe kuzingcaphuno uma ingezona izinkomba zamaseli anamanani ezinombolo.

Izibonelo ezingokoqobo: I-MINIFS kanye ne-MAXIFS esenzweni sansuku zonke

Lezi zibonelo zibonisa indlela yokusebenzisa le misebenzi emisebenzini yansuku zonke:

• Ukuthengiswa okuncane komkhiqizo endaweni:
  =MINIFS(Ukuthengisa, Umkhiqizo, "Amateki", Isifunda, "eNyakatho")
• Ibanga eliphakeme kakhulu lomfundi wesifazane kuMathematika:
  =MAXIFS(Amabanga, Ubulili, "Owesifazane", Isihloko, "Izibalo")
• Amazinga okushisa aphansi ngesikhathi sokushisa ngo-Agasti:
  =MINIFS(Izinga lokushisa, Inyanga, «August», Umcimbi, «Igagasi Lokushisa»)
• Inani eliphezulu elitholakalayo lemikhiqizo esebenzayo:
  =MAXIFS(Isitoko, Isimo, "Iyasebenza")

Ingabe ufuna okuthile okuthuthuke kakhulu? Ungahlanganisa imibandela, isibonelo:
=MAXIFS(Ukuthengisa, Umkhiqizo, "A", Isifunda, "Ningizimu", Inyanga, ">=6", Inyanga, "<=8")
Lesi sibonelo sihlunga ukuthengiswa okuphezulu komkhiqizo A esifundeni esiseNingizimu kuphela kusukela ngoJuni kuya ku-Agasti, kuhlanganisa.

Ukuzalanisa Nokuhlanganisa: Lapho Okuyisisekelo Kufushane

Enye yezinzuzo ezinkulu ze-Excel yikhono lokwenza imisebenzi yokuzalanisa, okungukuthi, ukubeka eyodwa ngaphakathi kwelinye ukuxazulula amacala ayinkimbinkimbi kakhulu:

• Ukuhlaziya okunemibandela:
  =MAXIFS(A2:A10, B2:B10, ">=100", C2:C10, "<=200")
  Ibuyisela inani elikhulu ku-A2:A10 lapho i-B2:B10 okungenani ingu-100 futhi i-C2:C10 okungenani ingu-200.
• Nge-IFERROR yokuphatha amaphutha:
  =IFERROR(MINIFS(A2:A10, B2:B10, ">100"), "Ayikho idatha")
• Kusetshenziswa i-INDIRECT kububanzi obuguqukayo:
  =AMA-MAXIFS(INDIRECT("A"&F2&":A"&F3), B2:B10, "Iyasebenza")
• Ukuhlanganiswa kwe-SUMIFS kanye ne-MAXIFS:
  =MAXIFS(SUMIFS(D2:D10, A2:A10, «ProductX»), C2:C10, «>5000»)

Lezi zinhlanganisela zivumela ulwazi olunembayo oluhambisana nezidingo zemibiko eguquguqukayo namadeshibhodi.

Amaphutha ajwayelekile nendlela yokuwalungisa

Ngisho nongoti abanolwazi olunzulu babhekana nobunzima. Nazi izinkinga ezivame kakhulu nezixazululo zabo:

• Ukungalingani kusayizi wobubanzi: Uma ububanzi bedatha nemibandela ingafani, i-Excel izoveza a #KUFANELE!Njalo hlola ukuthi wonke umuntu unenombolo efanayo yemigqa noma amakholomu.
• Idatha okungeyona yezinombolo kububanzi bokubala: Ukuba khona kombhalo ongenalutho noma amaseli kungadala amaphutha #NUM!. Hlanza idatha noma uyiguqule ibe izinombolo nge =INANI().
• I-syntax engalungile kumibandela: Sebenzisa izingcaphuno zombhalo nama-opharetha, isibonelo, ">=10".
• Amalungu afanayo akhiwe kabi: Uma usebenzisa imibandela eminingi kumalungu afanayo, kufanele ahlelwe kanje: {«>=10», «<=20»}.
• Idatha efihliwe: Khumbula lokho MINIFS y MAXIFS Abazibi imigqa noma amashidi afihliwe. Uma ungafuni ukuzicabangela, zihlunge kuqala.
• Amaphutha angalindelekile: Usa IFERROR ukuphatha amaphutha nokubonisa imilayezo ecacile uma idatha ingekho.
• Izinkinga zokusebenza ezinamavolumu aphezulu: Khawulela ububanzi kudatha edingekayo futhi usebenzise amathebula e-Excel ukuze ulungiselele izibalo.

Imikhuba emihle uma usebenza ngedatha eningi

Ngamafayela anezinkulungwane zemigqa noma amashidi amaningi, cabangela lawa macebiso:

• Guqula ububanzi bube amathebula (Ctrl+T) ukuze uthole izinkomba ezivikelekile nezizenzakalelayo.
• Gwema amafomula aguqukayo afana ne-OFFSET noma i-INDIRECT, ngaphandle uma kudingekile, ngoba anciphisa ifayela.
• Sebenzisa amafomula e-matrix kancane: Incamela i-MINIFS eqondile kanye ne-MAXIFS futhi igcine amafomula e-matrix ezimweni ezidingeka kakhulu.
• Qinisekisa okufakiwe kwedatha: Ngale ndlela uvimbela amaphutha esikhathi esizayo.
• Sebenzisa hardware okwanele ukuphatha inqwaba yolwazi.
• Sabalalisa idatha kumashidi amaningi ukugcina ukuphatha kulula.
• Gcina i-Excel isesikhathini samanje: Izinguqulo zakamuva zithuthukisa ukusebenza futhi zinikeza izici ezengeziwe.

Uyihlanganisa kanjani i-MINIFS ne-MAXIFS emibikweni yakho namadeshibhodi

Le misebenzi ezenzakalelayo yokubala ubuncane nobukhulu obunemibandela evumelana nezimo ibalulekile ekuthengisweni, ezinsizeni zabasebenzi, namadeshibhodi okuhlaziya uhlu lwempahla. Avumela amadeshibhodi ukuthi abonise imiphumela ngesikhathi sangempela, ngaphandle kokushintsha amafomula ngesibuyekezo ngasinye sedatha.

Ingabe ufuna ukuhlukanisa amakhasimende ngokuthenga ileveli? Qhathanisa izifunda, izinkathi zonyaka, noma imikhiqizo? Dizayina amathebula namashadi axhunywe kuwo MINIFS y MAXIFS futhi uzothuthukisa ukusebenza kahle nokunemba kokuhlaziya kwakho.

Ithebula lezibonelo ezicacile zokusetshenziswa

Amafomula nemiphumela:

• =MINIFS(A2:A5, B2:B5, «>25», C2:C5, «A»): Ibuyisela okungu-102, okuyi-ID ephansi kakhulu enamaphuzu angaphezu kuka-25 esigabeni A.
• =MINIFS(A2:A5, B2:B5, «>25», C2:C5, «B»): Ibuyisela i-123.
• =MINIFS(A2:A5, B2:B5, «<35», C2:C5, «B»): Ibuyisela i-123.
• =MINIFS(A2:A5, B2:B5, «>35», C2:C5, «B»): Ibuyisela okungu-0, okubonisa ukuthi ayikho idatha ehlangabezana nayo.

Amanothi engeziwe nezincomo

• Uma ingekho idatha ehlangabezana nemibandela, i-MINIFS ne-MAXIFS ibuyisela u-0 ngokuzenzakalelayo.
• Kubalulekile ukuthi ububanzi bedatha nemibandela ilingana nosayizi; ngaphandle kwalokho, i-Excel izoveza iphutha.
• Hlanganisa le misebenzi neminye efana AMAFOMU KANYE IMISEBENZI ku-Excel ukudala imibiko ebanzi.

Ngokusebenzisa le mibono, ukuphathwa kwedatha nokuhlaziya ku-Excel kuba yimpumelelo kakhulu futhi kuthembekile. Kusukela emahhovisi amancane kuya ezinkampanini ezinkulu, sisebenzisana imibiko ezenzakalelayo, enembile, futhi engenziwa ngendlela oyifisayo Ikuvumela ukuthi unciphise amahora okusebenza futhi unikeze isikhathi esengeziwe sokuhumusha imiphumela namasu.

I-athikili ehlobene:

Amaphutha ajwayelekile kakhulu kumafomula e-Excel nendlela yokuwalungisa

Isaka

Umbhali oshisekayo ngomhlaba wamabhayithi nobuchwepheshe ngokujwayelekile. Ngiyathanda ukwabelana ngolwazi lwami ngokubhala, futhi yilokho engizokwenza kule bhulogi, ngikubonise zonke izinto ezithakazelisayo kakhulu ngamagajethi, isofthiwe, ihadiwe, izitayela zobuchwepheshe, nokuningi. Inhloso yami ukukusiza ukuthi uzulazule emhlabeni wedijithali ngendlela elula nejabulisayo.