Funcția SUMIF în Excel și suma după mai multe condiții

Excel este un program incredibil de funcțional. Chiar și setul de caracteristici încorporate este suficient pentru a finaliza aproape orice sarcină. Și pe lângă cele standard, cunoscute multora, mai sunt și cele despre care puțini oameni au auzit. Dar, în același timp, ele nu încetează să fie utile. Au o specializare mai restrânsă și nu întotdeauna este nevoie de ei. Dar dacă știi despre ele, atunci într-un moment critic pot fi foarte utile.

Astăzi vom vorbi despre una dintre astfel de funcții − SUMMESLIMN.

Dacă utilizatorul se confruntă cu sarcina de a însuma mai multe valori, concentrându-se pe anumite criterii, atunci este necesar să se utilizeze funcția SUMMESLIMN. Formula care folosește această funcție ia aceste condiții drept argumente, apoi însumează valorile care le îndeplinesc, iar apoi valoarea găsită este introdusă în celula în care este scrisă. 

Descriere detaliată a funcției SUMIFS

Înainte de a lua în considerare funcția SUMMESLIMN, trebuie mai întâi să înțelegeți ce este o versiune mai simplă a acesteia - SUMMESLI, deoarece pe acesta se bazează funcția pe care o luăm în considerare. Fiecare dintre noi este probabil deja familiarizat cu două funcții care sunt adesea folosite - USM (realizează însumarea valorilor) și DACĂ (testează o valoare față de o condiție specificată).

Dacă le combinați, obțineți o altă funcție - SUMMESLI, care verifică datele cu criteriile specificate de utilizator și însumează numai acele numere care îndeplinesc acele criterii. Dacă vorbim despre versiunea în limba engleză a Excel, atunci această funcție se numește SUMIF. Cu cuvinte simple, numele -language este o traducere directă a celui în limba engleză. Această funcție poate fi utilizată pentru o varietate de scopuri. În special, poate fi folosit ca alternativă VPR, adică notează

Principala diferență între funcție SUMMESLIMN  din functia obisnuita SUMMESLI este că se folosesc mai multe criterii. Sintaxa sa este destul de complexă la prima vedere, dar la o examinare mai atentă, se dovedește că logica acestei funcții este foarte simplă. Mai întâi trebuie să selectați intervalul în care vor fi verificate datele și apoi să setați condițiile de conformitate cu care va fi efectuată analiza. Și o astfel de operație poate fi efectuată pentru un număr destul de mare de condiții.

Sintaxa în sine este:

SUMIFS(interval_sumă, interval_condiție1, condiție1, [interval_condiție2, condiție2], …)

În locuri adecvate este necesar să se pună matrice de celule adecvate într-un caz particular. 

Să ne uităm la argumente mai detaliat:

1. Sum_range. Acest argument, precum și intervalul condiției 1 și condiției 1, este necesar. Este un set de celule care trebuie însumate.
2. Interval_condiție1. Acesta este intervalul în care va fi verificată condiția. Este asociat cu următorul argument – ​​Condiția1. Însumarea valorilor corespunzătoare criteriului se realizează în celulele specificate în argumentul anterior.
3. Condiția 1. Acest argument specifică criteriile cu care trebuie verificate. Poate fi setat, de exemplu, în acest fel: „> 32”.
4. Interval de condiții 2, Condiție 2... Aici, următoarele condiții sunt setate în același mod. Dacă trebuie specificate mai mult de câteva condiții, atunci se adaugă argumentele Interval de condiție 3 și Condiție 3. Sintaxa este aceeași pentru următoarele argumente.

Funcția permite procesarea maximă a până la 127 de perechi de condiții și intervale. 

Îl poți folosi în mai multe zone deodată (vom da doar câteva, lista este de fapt și mai lungă):

1. Contabilitate. De exemplu, este bine să folosiți funcția SUMMESLIMN pentru a crea rapoarte de sinteză, pe trimestru pentru cheltuirea peste o anumită sumă, de exemplu. Sau creați un raport pentru un produs dintr-o anumită categorie de preț.
2. Managementul vanzarilor. Și aici funcția poate fi foarte utilă. De exemplu, ne confruntăm cu sarcina de a însuma doar costul mărfurilor care au fost vândute unui anumit client la un anumit moment. Și într-o astfel de situație, funcția SUMMESLIMN poate fi foarte util.
3. Educaţie. Vom oferi astăzi mai multe exemple practice din acest domeniu. În special, îl puteți folosi pentru a obține un rezumat al notelor elevilor. Puteți alege pentru un singur subiect sau pentru note individuale. O persoană poate stabili imediat mai multe criterii după care va fi selectată evaluarea, ceea ce este foarte convenabil și poate economisi mult timp.

După cum puteți vedea, gama de aplicații pentru această funcție este foarte largă. Dar acesta nu este singurul său merit. Să aruncăm o privire la câteva beneficii suplimentare pe care le are această caracteristică:

1. Abilitatea de a stabili mai multe criterii. De ce este acesta un avantaj? Puteți utiliza funcția normală SUMMESLI! Și totul pentru că este convenabil. Nu este necesar să se efectueze calcule separate pentru fiecare dintre criterii. Toate acțiunile pot fi programate în prealabil, chiar înainte. cum va fi format tabelul de date. Acesta este un mare economisire de timp.
2. Automatizare. Epoca modernă este epoca automatizării. Doar persoana care știe să-și automatizeze în mod corespunzător munca poate câștiga mult. De aceea, capacitatea de a stăpâni Excel și funcția SUMMESLIMN în special, este atât de important pentru orice persoană care dorește să-și construiască o carieră. Cunoașterea unei singure funcții vă permite să efectuați mai multe acțiuni simultan, ca una singură. Și aici trecem la următorul beneficiu al acestei caracteristici.
3. Economie de timp. Doar datorită faptului că o funcție îndeplinește mai multe sarcini simultan.
4. Simplitate. În ciuda faptului că sintaxa este destul de grea la prima vedere datorită volumului său, de fapt, logica acestei funcții este foarte simplă. În primul rând, este selectat un interval de date, apoi un interval de valori, care va fi verificat pentru conformitatea cu o anumită condiție. Și, desigur, trebuie specificată și condiția în sine. Și așa de mai multe ori. De fapt, această funcție se bazează pe un singur construct logic, ceea ce o face mai simplă decât binecunoscutul VPR în ciuda faptului că poate fi folosit în aceleași scopuri, ținând cont și de un număr mai mare de criterii. 

Caracteristici de utilizare a funcției SUMIFS

Există mai multe caracteristici ale utilizării acestei funcții cărora trebuie să le acordați atenție. În primul rând, această funcție ignoră intervalele cu șiruri de text sau nule, deoarece aceste tipuri de date nu pot fi adăugate împreună într-un model aritmetic, ci doar concatenate ca șiruri. Această funcție nu poate face acest lucru. De asemenea, trebuie să acordați atenție următoarelor condiții:

1. Puteți utiliza aceste tipuri de valori ca condiții pentru selectarea celulelor pentru a adăuga în continuare valorile conținute în ele: valori numerice, expresii booleene, referințe de celule și așa mai departe. 
2. Dacă sunt verificate text, expresii logice sau semne matematice, atunci astfel de criterii sunt specificate prin ghilimele.
3. Nu se pot folosi termeni mai lungi de 255 de caractere.
4. Este posibil să se utilizeze criterii aproximative pentru selectarea valorilor folosind metacaracterele. Semnul întrebării este folosit pentru a înlocui un singur caracter, iar semnul de înmulțire (asteriscul) este necesar pentru a înlocui mai multe caractere. 
5. Valorile booleene care se află în intervalul de însumare sunt convertite automat în valori numerice în funcție de tipul lor. Astfel, valoarea „TRUE” se transformă în unu, iar „FALSE” – în zero. 
6. Dacă o #VALOARE! eroare apare într-o celulă, înseamnă că numărul de celule din intervalele de stare și de însumare este diferit. Trebuie să vă asigurați că dimensiunile acestor argumente sunt aceleași. 

Exemple de utilizare a funcției SUMIFS

Funcţie SUMMESLIMN nu atât de complicat pe cât pare la prima vedere, se pare. Dar pentru mai multă claritate, să ne uităm la câteva exemple practice despre cum puteți utiliza funcția SUMMESLIMN. Acest lucru va face mult mai ușor să aprofundați subiectul.

Interval dinamic de însumare a condițiilor

Deci, să începem cu primul exemplu. Să presupunem că avem un tabel care conține informații despre modul în care elevii fac față curriculum-ului într-o anumită materie. Există un set de note, performanța este evaluată pe o scară de 10 puncte. Sarcina este de a găsi nota pentru examenul acelor studenți al căror nume de familie începe cu litera A, iar punctajul minim al acestora este 5.

Masa arată așa.

Pentru a calcula scorul total pe baza criteriilor descrise mai sus, trebuie să aplicăm următoarea formulă.

Să descriem argumentele mai detaliat:

1. C3:C14 este intervalul nostru de însumare. În cazul nostru, aceasta coincide cu intervalul de condiții. Din el vor fi selectate punctele folosite pentru a calcula suma, dar numai cele care se încadrează în criteriile noastre.
2. „>5” este prima noastră condiție.
3. B3:B14 este al doilea interval de însumare care este procesat pentru a corespunde celui de-al doilea criteriu. Vedem că nu există nicio coincidență cu intervalul de însumare. Din aceasta concluzionăm că intervalul de însumare și domeniul de afecțiune pot fi sau nu identice. 
4. „A*” este al doilea interval, care specifică selecția de note numai pentru acei elevi al căror nume de familie începe cu A. În cazul nostru, un asterisc înseamnă orice număr de caractere. 

După calcule, obținem următorul tabel.

După cum puteți vedea, formula a însumat valorile pe baza intervalului dinamic și pe baza condițiilor specificate de utilizator.

Însumarea selectivă după condiție în Excel

Acum să presupunem că vrem să obținem informații despre ce mărfuri au fost expediate în ce țări în ultimul trimestru. După aceea, găsiți veniturile totale din expedieri pentru iulie și august.

Masa în sine arată așa. 

Pentru a determina rezultatul final, avem nevoie de o astfel de formulă.

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

În urma calculelor efectuate prin această formulă, obținem următorul rezultat.

Atenție! Această formulă pare destul de mare, deși am folosit doar două criterii. Dacă intervalul de date este același, atunci puteți reduce semnificativ lungimea formulei, așa cum se arată mai jos.

Funcția SUMIFS pentru a suma valorile în mai multe condiții

Acum să dăm un alt exemplu pentru a ilustra. În acest caz, tabelul rămâne același ca în cazul precedent. 

Folosim următoarea formulă (dar o scriem ca formulă matrice, adică o introducem prin combinația de taste CTRL + SHIFT + ENTER).

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

După funcție SUMMESLIMN va însuma matricea de valori pe baza criteriilor specificate în formulă (adică țările din China și Georgia), matricea rezultată este însumată de funcția obișnuită SUMĂ, care este scrisă ca o formulă matrice.

Dacă condițiile au fost transmise ca o constantă de matrice pentru mai mult de o pereche, atunci formula va da un rezultat incorect.

Acum să aruncăm o privire la tabelul care conține totalurile.

După cum puteți vedea, am reușit. Cu siguranță vei reuși și tu. Mare succes in acest domeniu. Aceasta este o funcție foarte simplă pe care o poate înțelege o persoană care tocmai a pus piciorul pe calea învățării Excel. Și știm deja că funcția SUMMESLIMN iti permite sa fii eficient in orice domeniu de activitate, de la contabilitate pana la educatie. Chiar dacă vă construiți o carieră în orice alt domeniu care nu a fost descris mai sus, această caracteristică vă va ajuta în continuare să câștigați bani. Acesta este motivul pentru care ea este valoroasă.

Cel mai important, vă permite să economisiți timp, care este, din păcate, o resursă limitată. S-ar părea că există câteva secunde pentru a aplica două funcții, dar atunci când trebuie să efectuați un număr mare de operații repetitive, atunci aceste secunde se adună la ore care ar putea fi cheltuite pentru altceva. Prin urmare, vă recomandăm să exersați utilizarea acestei funcții. În plus, este incredibil de simplu.