Funksioni SUMIF në Excel dhe shuma sipas kushteve të shumta

Excel është një program tepër funksional. Edhe grupi i veçorive të integruara është i mjaftueshëm për të përfunduar pothuajse çdo detyrë. Dhe përveç atyre standarde, të njohura për shumë, ka edhe nga ato për të cilat pak njerëz kanë dëgjuar. Por në të njëjtën kohë, ato nuk pushojnë së qeni të dobishme. Ata kanë një specializim më të ngushtë dhe nuk ka gjithmonë nevojë për to. Por nëse dini rreth tyre, atëherë në një moment kritik ato mund të jenë shumë të dobishme.

Sot do të flasim për një nga funksionet e tilla - SUMMESLIMN.

Nëse përdoruesi përballet me detyrën për të përmbledhur disa vlera, duke u fokusuar në disa kritere, atëherë është e nevojshme të përdoret funksioni SUMMESLIMN. Formula që përdor këtë funksion i merr këto kushte si argumente, më pas përmbledh vlerat që i plotësojnë ato dhe më pas vlera e gjetur futet në qelizën në të cilën është shkruar. 

Përshkrimi i detajuar i funksionit SUMIFS

Para se të shqyrtohet funksioni SUMMESLIMN, fillimisht duhet të kuptoni se cili është një version më i thjeshtë i tij - SUMMESLI, meqë mbi të bazohet funksioni që po shqyrtojmë. Secili prej nesh ndoshta tashmë është njohur me dy funksione që përdoren shpesh - SUM (kryen mbledhjen e vlerave) dhe NËSE (teston një vlerë kundrejt një kushti të caktuar).

Nëse i kombinoni ato, ju merrni një funksion tjetër - SUMMESLI, i cili kontrollon të dhënat kundrejt kritereve të përcaktuara nga përdoruesi dhe përmbledh vetëm ato numra që plotësojnë ato kritere. Nëse flasim për versionin anglisht të Excel, atëherë ky funksion quhet SUMIF. Me fjalë të thjeshta, emri -language është një përkthim i drejtpërdrejtë i atij në gjuhën angleze. Ky funksion mund të përdoret për qëllime të ndryshme. Në veçanti, mund të përdoret si një alternativë VPR, domethënë shkruani

Dallimi kryesor midis funksionit SUMMESLIMN  nga funksioni i zakonshëm SUMMESLI është se përdoren disa kritere. Sintaksa e tij është mjaft komplekse në shikim të parë, por pas shqyrtimit më të afërt, rezulton se logjika e këtij funksioni është shumë e thjeshtë. Së pari ju duhet të zgjidhni gamën ku do të kontrollohen të dhënat dhe më pas të vendosni kushtet për pajtueshmërinë me të cilat do të kryhet analiza. Dhe një operacion i tillë mund të kryhet për një numër mjaft të madh kushtesh.

Vetë sintaksa është:

SUMIFS(vargu_shumë, diapazoni_kushti1, kushti1, [vargu_kushti2, kushti2], …)

Në vende të përshtatshme është e nevojshme të vendosni grupe qelizash të përshtatshme në një rast të veçantë. 

Le t'i shohim argumentet në mënyrë më të detajuar:

1. Diapazoni i shumës. Ky argument, si dhe diapazoni i kushtit 1 dhe kushtit 1, kërkohet. Është një grup qelizash që duhet të përmblidhen.
2. Diapazoni_kushti1. Ky është diapazoni ku do të kontrollohet gjendja. Është çiftuar me argumentin tjetër - Kushti 1. Përmbledhja e vlerave që korrespondojnë me kriterin kryhet brenda qelizave të specifikuara në argumentin e mëparshëm.
3. Kushti 1. Ky argument specifikon kriteret për t'u kontrolluar. Mund të vendoset, për shembull, në këtë mënyrë: "> 32".
4. Gama e kushteve 2, gjendja 2... Këtu, kushtet e mëposhtme janë vendosur në të njëjtën mënyrë. Nëse duhet të specifikohen më shumë se disa kushte, atëherë shtohen argumentet e diapazonit të kushteve 3 dhe të kushteve 3. Sintaksa është e njëjtë për argumentet e mëposhtme.

Funksioni lejon përpunimin maksimal deri në 127 çifte kushtesh dhe diapazoni. 

Mund ta përdorni në disa fusha njëherësh (ne do të japim vetëm disa, lista është në fakt edhe më e gjatë):

1. Kontabiliteti. Për shembull, është mirë të përdoret funksioni SUMMESLIMN për të krijuar raporte përmbledhëse, për tremujor për shpenzime mbi një shumë të caktuar, për shembull. Ose krijoni një raport për një produkt nga një kategori e caktuar çmimesh.
2. Menaxher Shitjesh. Këtu gjithashtu funksioni mund të jetë shumë i dobishëm. Për shembull, ne përballemi me detyrën që të përmbledhim vetëm koston e mallrave që i janë shitur një klienti të caktuar në një kohë të caktuar. Dhe në një situatë të tillë, funksioni SUMMESLIMN mund të jetë shumë e dobishme.
3. Arsimi. Sot do të japim shembuj më praktikë nga kjo fushë. Në veçanti, mund ta përdorni për të marrë një përmbledhje të notave të studentëve. Ju mund të zgjidhni për një lëndë të vetme ose për nota individuale. Një person mund të vendosë menjëherë disa kritere me të cilat do të zgjidhet vlerësimi, gjë që është shumë e përshtatshme dhe mund të kursejë shumë kohë.

Siç mund ta shihni, gama e aplikacioneve për këtë funksion është shumë e gjerë. Por kjo nuk është merita e vetme e saj. Le të hedhim një vështrim në disa përfitime të tjera që ka kjo veçori:

1. Aftësia për të vendosur disa kritere. Pse është ky një avantazh? Mund të përdorni funksionin normal SUMMESLI! Dhe gjithçka sepse është i përshtatshëm. Nuk ka nevojë të kryhen llogaritje të veçanta për secilin nga kriteret. Të gjitha veprimet mund të programohen paraprakisht, madje edhe më parë. si do të formohet tabela e të dhënave. Ky është një kursim i shkëlqyeshëm i kohës.
2. Automatizimi. Epoka moderne është epoka e automatizimit. Vetëm personi që di të automatizojë siç duhet punën e tij mund të fitojë shumë. Kjo është arsyeja pse aftësia për të zotëruar Excel dhe funksionin SUMMESLIMN në veçanti, është kaq e rëndësishme për çdo person që dëshiron të ndërtojë një karrierë. Njohja e një funksioni ju lejon të kryeni disa veprime në të njëjtën kohë, si një. Dhe këtu kalojmë në përfitimin tjetër të kësaj veçorie.
3. Kursimi i kohës. Vetëm për faktin se një funksion kryen disa detyra në të njëjtën kohë.
4. Thjeshtësia. Përkundër faktit se sintaksa është mjaft e rëndë në shikim të parë për shkak të masës së saj, në fakt, logjika e këtij funksioni është shumë e thjeshtë. Fillimisht zgjidhet një varg të dhënash, më pas një varg vlerash, të cilat do të kontrollohen për pajtueshmërinë me një kusht të caktuar. Dhe sigurisht, vetë kushti duhet gjithashtu të specifikohet. Dhe kështu disa herë. Në fakt, ky funksion bazohet vetëm në një konstrukt logjik, gjë që e bën atë më të thjeshtë se i njohuri VPR pavarësisht se mund të përdoret për të njëjtat qëllime, duke marrë parasysh edhe një numër më të madh kriteresh. 

Karakteristikat e përdorimit të funksionit SUMIFS

Ka disa veçori të përdorimit të këtij funksioni që duhet t'u kushtoni vëmendje. Para së gjithash, ky funksion injoron vargjet me vargje teksti ose null, pasi këto lloje të dhënash nuk mund të shtohen së bashku në një model aritmetik, vetëm të lidhura si vargje. Ky funksion nuk mund ta bëjë këtë. Ju gjithashtu duhet t'i kushtoni vëmendje kushteve të mëposhtme:

1. Ju mund t'i përdorni këto lloje vlerash si kushte për zgjedhjen e qelizave për të shtuar më tej vlerat që përmbahen në to: vlerat numerike, shprehjet boolean, referencat e qelizave, etj. 
2. Nëse teksti, shprehjet logjike ose shenjat matematikore janë duke u kontrolluar, atëherë kriteret e tilla specifikohen përmes thonjëzave.
3. Nuk mund të përdoren terma më të gjatë se 255 karaktere.
4. Është e mundur të përdoren kritere të përafërta për zgjedhjen e vlerave duke përdorur shkronja të egra. Pikëpyetja përdoret për të zëvendësuar një karakter të vetëm, dhe shenja e shumëzimit (ylli) nevojitet për të zëvendësuar shumë karaktere. 
5. Vlerat Boolean që janë në intervalin e përmbledhjes konvertohen automatikisht në vlera numerike sipas llojit të tyre. Kështu, vlera "TRUE" kthehet në një, dhe "FALSE" - në zero. 
6. Nëse një #VLERË! gabimi shfaqet në një qelizë, kjo do të thotë se numri i qelizave në diapazonin e kushtit dhe përmbledhjes është i ndryshëm. Duhet të siguroheni që madhësitë e këtyre argumenteve të jenë të njëjta. 

Shembuj të përdorimit të funksionit SUMIFS

funksion SUMMESLIMN jo aq e komplikuar sa duket në shikim të parë, rezulton. Por për më shumë qartësi, le të shohim disa shembuj praktikë se si mund ta përdorni funksionin SUMMESLIMN. Kjo do ta bëjë shumë më të lehtë gërmimin në temë.

Gama dinamike e përmbledhjes së gjendjes

Pra, le të fillojmë me shembullin e parë. Le të themi se kemi një tabelë që përmban informacion se si studentët përballen me kurrikulën në një lëndë të caktuar. Ekziston një grup notash, performanca vlerësohet në një shkallë prej 10 pikësh. Detyra është të gjendet nota për provimin e atyre studentëve, mbiemri i të cilëve fillon me shkronjën A, dhe rezultati minimal i tyre është 5.

Tabela duket si kjo.

Në mënyrë që ne të llogarisim rezultatin total bazuar në kriteret e përshkruara më sipër, duhet të zbatojmë formulën e mëposhtme.

Le të përshkruajmë argumentet në mënyrë më të detajuar:

1. C3: C14 është diapazoni ynë i përmbledhjes. Në rastin tonë, ajo përkon me diapazonin e kushteve. Prej tij do të zgjidhen pikët e përdorura për llogaritjen e shumës, por vetëm ato që bien në kriteret tona.
2. "> 5" është kushti ynë i parë.
3. B3:B14 është diapazoni i dytë i përmbledhjes që përpunohet për t'u përshtatur me kriterin e dytë. Ne shohim se nuk ka rastësi me diapazonin e përmbledhjes. Nga kjo arrijmë në përfundimin se diapazoni i përmbledhjes dhe diapazoni i kushtit mund të jenë ose jo të njëjta. 
4. “A*” është diapazoni i dytë, i cili specifikon përzgjedhjen e notave vetëm për ata studentë, mbiemri i të cilëve fillon me A. Në rastin tonë, një yll nënkupton çdo numër karakteresh. 

Pas llogaritjeve, marrim tabelën e mëposhtme.

Siç mund ta shihni, formula përmblodhi vlerat bazuar në diapazonin dinamik dhe bazuar në kushtet e specifikuara nga përdoruesi.

Përmbledhja selektive sipas kushteve në Excel

Tani supozojmë se duam të marrim informacion se çfarë mallrash janë dërguar në cilat vende gjatë tremujorit të fundit. Pas kësaj, gjeni të ardhurat totale nga dërgesat për korrik dhe gusht.

Tabela në vetvete duket si kjo. 

Për të përcaktuar rezultatin përfundimtar, na duhet një formulë e tillë.

=(СУММЕСЛИМН(D2:D14;A2:A14;»=июнь»;B2:B14;»Товар_2″;C2:C14;»Казахстан»)+(СУММЕСЛИМН(D2:D14;A2:A14;»=август»;B2:B14;»Товар_2″;C2:C14;»Казахстан»)))

Si rezultat i llogaritjeve të kryera nga kjo formulë, marrim rezultatin e mëposhtëm.

Kujdes! Kjo formulë duket mjaft e madhe edhe pse kemi përdorur vetëm dy kritere. Nëse diapazoni i të dhënave është i njëjtë, atëherë mund të zvogëloni ndjeshëm gjatësinë e formulës, siç tregohet më poshtë.

Funksioni SUMIFS për të mbledhur vlerat në kushte të shumta

Tani le të japim një shembull tjetër për ta ilustruar. Në këtë rast, tabela mbetet e njëjtë si në rastin e mëparshëm. 

Ne përdorim formulën e mëposhtme (por e shkruajmë si formulë grupi, domethënë e futim përmes kombinimit të tastit CTRL + SHIFT + ENTER).

=СУММ(СУММЕСЛИМН(D2:D14;B2:B14;»Товар_1″;C2:C14;{«Китай»;»Грузия»}))

Pas funksionit SUMMESLIMN do të përmbledhë grupin e vlerave bazuar në kriteret e specifikuara në formulë (d.m.th., vendet e Kinës dhe Gjeorgjisë), grupi që rezulton përmblidhet me funksionin e zakonshëm SUM, e cila shkruhet si formulë vargu.

Nëse kushtet kalohen si një konstante grupi për më shumë se një çift, atëherë formula do të japë një rezultat të pasaktë.

Tani le të hedhim një vështrim në tabelën që përmban totalet.

Siç mund ta shihni, ne kemi pasur sukses. Edhe ju patjetër do të keni sukses. Sukses i madh në këtë fushë. Ky është një funksion shumë i thjeshtë që mund ta kuptojë një person që sapo ka vënë këmbë në rrugën e mësimit të Excel. Dhe ne tashmë e dimë se funksioni SUMMESLIMN ju lejon të jeni efektiv në çdo fushë të veprimtarisë, nga kontabiliteti deri tek arsimi. Edhe nëse po ndërtoni një karrierë në ndonjë fushë tjetër që nuk është përshkruar më sipër, kjo veçori do t'ju ndihmojë të fitoni para. Kjo është arsyeja pse ajo është e vlefshme.

Më e rëndësishmja, ju lejon të kurseni kohë, e cila, për fat të keq, është një burim i kufizuar. Duket se ka disa sekonda për të aplikuar dy funksione, por kur duhet të kryeni një numër të madh operacionesh të përsëritura, atëherë këto sekonda shtohen në orë që mund të shpenzohen për diçka tjetër. Prandaj, ju rekomandojmë që të praktikoni përdorimin e kësaj veçorie. Për më tepër, është tepër e thjeshtë.