Funcția SUMMESLULUI, precum și SUMMERELE prin două criterii

1. Căutați după etichete

Imaginați-vă un tabel în care numele departamentelor (sau conturile sau altceva) sunt enumerate în rânduri într-un rând.

Scrieți celulele după criterii
Este necesar să se calculeze suma totală pentru fiecare departament. Mulți fac acest lucru cu un filtru și scriu cu pixuri în celule.
Deși se poate face ușor și pur și simplu cu o singură funcție - SUMMESLI .
SUMMESLES (SUMIF) -Sumulează celule care satisfac o anumită condiție (poate fi specificată o singură condiție). Această funcție poate fi utilizată și în cazul în care tabelul este împărțit în coloane pe perioade (lunar, în fiecare lună, trei coloane - Venit | Cheltuieli | Diferență) și trebuie să calculați suma totală pentru toate perioadele numai prin Venit, Cheltuieli și Diferență.

Există trei argumente în total pentru SUMMESLI: Range , Criterion , Range_Summing .
= SUMMESUL (A1: A20000; A1; B1: B20000)
= SUMIF (A1: A20000, A1, B1: B20000)

• Domeniu (A1: A20000) - indică intervalul cu criteriile. Ie Coloana în care se caută valoarea indicată de argumentul Criterion .
• Criteriul (A1) este valoarea (text sau numeric, precum și data) care trebuie găsită în interval . Poate să conțină caracterele "*" și "?". Ie specificând criteriul "* mass *" pentru a rezuma valorile în care apare cuvântul "masă". În același timp, cuvântul "masă" poate apărea oriunde în text sau poate exista un singur cuvânt într-o celulă. Și specificând "masa *", toate valorile care încep cu "masa" vor fi rezumate. "?" - înlocuiește un singur caracter, adică specificând "masă", puteți rezuma liniile cu valoarea "masă" și valoarea "mască" etc.
  Dacă criteriul este scris într-o celulă și trebuie totuși să utilizați caractere tip "wildcard", atunci puteți face o legătură cu această celulă adăugând cea necesară. Să presupunem că trebuie să rezumați valorile care conțin cuvântul "total". Cuvântul "total" este scris în celula A1, în timp ce în coloana A pot exista diferite valori de ortografie care conțin cuvântul "total": "totaluri pentru iunie", "totaluri pentru iulie" și "totaluri pentru martie". Formula ar trebui să arate astfel:
  = SUMMERS (A1: A20000; "*" & A1 & "*"; B1: B20000)
  "*" & A1 & "*" - semnul (ampersand) combină mai multe valori într-una. Ie rezultatul va fi "* rezultat *".
  Pentru a înțelege mai bine principiul funcționării formulelor, este mai bine să utilizați instrumentul Calculate Formula : Cum se vizualizează pașii pentru calcularea formulelor
  Toate criteriile și criteriile textuale cu semne logice și matematice trebuie incluse în citate dublă (= SUMMESLI (A1: A20000; "total"; B1: B20000)). Dacă criteriul este un număr, citatele nu sunt necesare. Dacă doriți să găsiți un semn de întrebare sau un asterisc în mod direct, trebuie să puneți un tilde (~) în fața acestuia.
  Despre tilde și caracteristicile sale pot fi găsite în acest articol: Cum se înlocuiește / se elimină / se găsește asteriscul?
• Sum_Range (B1: B20000) (argument opțional) - specifică intervalul sumelor sau valorilor numerice care urmează să fie însumate.

Cum funcționează: funcția caută intervalul pentru valoarea specificată de argumentul Criterion și atunci când se găsește o potrivire, sumăază datele indicate de argumentul Range_Amount. Ie dacă avem un nume de departament în coloana A și o sumă în coloana B, atunci specificând departamentul de dezvoltare ca fiind un criteriu care va rezulta în suma tuturor valorilor din coloana B, la care se găsește Departamentul de dezvoltare în coloana A. De fapt, SumArrangementul poate să nu aibă aceeași dimensiune ca argumentul Range și acest lucru nu va cauza o eroare a funcției în sine. Cu toate acestea, atunci când se definesc celulele pentru sumare, celula stânga sus a argumentului Range_Amount va fi folosită ca celula de pornire pentru sumare, iar apoi celulele corespunzătoare mărimii și formei la argumentul Range vor fi însumate.

Unele caracteristici
Ultimul argument al funcției (Sum_And_Band: B1: B20000) este opțional. Aceasta înseamnă că nu poate fi specificat. Dacă nu o specificați, funcția va adăuga valorile specificate de argumentul Range . Pentru ce este. De exemplu, trebuie să obțineți suma numai a numerelor care sunt mai mari decât zero. În coloana A a sumei. Apoi funcția va arăta astfel:
= VARĂ (A1: A20000; "> 0")

Ce ar trebui să fie luate în considerare: suma_limitului și intervalul ar trebui să fie egale în numărul de linii. În caz contrar, puteți obține rezultate greșite. În mod optim, dacă se va arăta în formulele pe care le-am dat: intervalul și intervalul sumarelor încep de la o linie și au același număr de linii: A1: A20000; B1: B20000

Rezumatul privind două sau mai multe criterii
Dar ce să facem atunci când criteriile pentru sumare 2 și mai mult? Să presupunem că trebuie să rezuma doar sumele care aparțin unui departament și numai pentru o anumită dată. Proprietarii fericiți ai versiunilor de birou 2007 și de mai sus pot utiliza funcția SUMMESLIMN:
= SUMMESLIMN ($ C $ 2: $ C $ 50; $ A $ 2: $ A $ 50; $ I $ 3; $ B $ 2: $ B $ 50; $ H8)
$ C $ 2: $ C $ 50 - range_summing. Primul argument specifică gama de celule care conțin cantitățile care vor fi colectate într-una.
$ A $ 2: $ A $ 50, $ B $ 2: $ B $ 50 --Criteria Range. Specifică intervalul de celule în care doriți să căutați un criteriu de potrivire.
$ I $ 3, $ H8 - criteriu. Aici, ca și în SUMMESLI, sunt acceptate caracterele wildcard * și ? și lucrează la fel.

Specificații de specificare a argumentelor: în primul rând, este specificat intervalul criteriilor (sunt numerotate), apoi valoarea (criteriul) este indicată direct în punct și virgulă, care trebuie găsite în acest interval - $ A $ 2: $ A $ 50; $ I $ 3. Și nimic altceva. Nu trebuie să încercați să specificați mai întâi toate gamele și apoi criteriile pentru ele - funcția va da fie o eroare, fie nu va rezuma ceea ce este necesar.

Toate condițiile sunt comparate în conformitate cu principiul I. Aceasta înseamnă că, dacă toate condițiile enumerate sunt îndeplinite. Dacă nu este îndeplinită cel puțin o condiție, funcția omite linia și nu adaugă nimic.
În ceea ce privește SUMMERII, intervalele de sumare și criterii trebuie să fie egale în numărul de rânduri.

pentru că SUMMESLIMN a apărut numai în versiunile Excel, începând cu 2007, apoi cum pot fi utilizatorii nefericiți ai versiunilor anterioare în astfel de cazuri? Foarte simplu: utilizați o altă funcție - SUMPRODUCT. Nu voi picta argumentele, pentru că Sunt multe dintre ele și toate sunt o serie de valori. Această funcție multiplică matricele indicate de argumente. Voi încerca să descriu principiul general al utilizării acestei funcții pentru a rezuma datele în mai multe condiții.
Pentru a rezolva problema sumării prin mai multe criterii, funcția va arăta astfel:
= SUMPRODUCT (($ A $ 2: $ A $ 50 = $ I $ 3) * ($ B $ 2: $ B $ 50 = H5); $ C $ 2: $ C $ 50)
$ A $ 2: $ A $ 50 - interval de date. $ I $ 3 este data criteriului pentru care este necesar să se sintetizeze datele.
$ B $ 2: $ B $ 50 - numele departamentelor. H5 - numele departamentului, datele care trebuie însumate.
$ C $ 2: $ C $ 50 - variază cu sumele.

Analizăm logica, pentru că pentru mulți, va fi complet neclar doar prin examinarea acestei funcții. Dacă numai pentru că în această aplicație această aplicație nu este descrisă. Pentru o mai mare lizibilitate, reduceți dimensiunea intervalelor:
= SUMPRODUCT (($ A $ 2: $ A $ 5 = $ I $ 3) * ($ B $ 2: $ B $ 5 = H5); $ C $ 2: $ C $ 5)
Astfel, expresia ($ A $ 2: $ A $ 5 = $ I $ 3) și ($ B $ 2: $ B $ 5 = H5) sunt matrice logice și returnate de FALSE și TRUE. TRUE dacă celula din intervalul $ A $ 2: $ A $ 5 este egală cu valoarea celulei $ I $ 3, iar celula din intervalul $ B $ 2: $ B $ 5 este egală cu valoarea celulei H5. Ie avem urmatoarele:
= SUMPRODUCT ({FALSE; TRUE; TRUE; FALSE} * {FALSE; FALSE; TRUE; FALSE}; $ C $ 2: $ C $ 50)
După cum puteți vedea, în prima matrice există două meciuri pentru condiție, iar în al doilea. Mai mult, aceste două matrice sunt multiplicate (semnul de multiplicare (*) este responsabil pentru acest lucru). Când are loc înmulțirea, apare conversia implicită a matricelor FALSE și TRUE la constantele numerice 0 și respectiv 1 ({0; 1; 1; 0} * {0; 0; 1; 0}). După cum știți, când se înmulțește cu zero, ajungem la zero. Și rezultatul este o singură matrice:
= SUMPRODUCT ({0; 0; 1; 0}; $ C $ 2: $ C $ 50)
Apoi, matricea {0; 0; 1; 0} este înmulțită cu o serie de numere din intervalul $ C $ 2: $ C $ 50:
= SUMPRODUCT ({0; 0; 1; 0}; {10; 20; 30; 40});
Și, ca rezultat, ajungem la 30. Ceea ce ne-am dorit - ajungem doar la suma care îndeplinește criteriul. Dacă există mai mult de o sumă care satisface criteriul, atunci acestea vor fi rezumate.

Avantajul SUMMYROIZV
Dacă argumentele au semnul plus în locul semnului de înmulțire:
($ A $ 2: $ A $ 5 = $ I $ 3) + ($ B $ 2: $ B $ 5 = H5)
atunci condițiile vor fi comparate în conformitate cu principiul OR: adică sumele totale vor fi însumate dacă este îndeplinită cel puțin o condiție: fie $ A $ 2: $ A $ 5 este egală cu valoarea celulei $ I $ 3, fie cu celula din intervalul $ B $ 2: $ B $ 5 este egală cu valoarea celulei H5.
Acesta este avantajul SUMMPRODUCT peste SUMMESLIMN. SUMMESLIMN nu poate sintetiza valori conform principiului OR, numai în conformitate cu principiul AND (toate condițiile trebuie îndeplinite).

deficiențe
SUMPRODUCT nu poate utiliza metacaractere * și?. Este posibil să se folosească mai precis, dar ele nu vor fi percepute ca niște caractere speciale, ci ca un asterisc și un semn de întrebare. Cred că acesta este un dezavantaj important. Și, deși acest lucru poate fi bypassed, am folosi și alte funcții în SUMPRODUCT - ar fi minunat dacă funcția ar putea folosi cumva metacaractere.

În exemplul de mai jos veți găsi câteva exemple de funcții pentru o mai bună înțelegere a ceea ce este scris mai sus.

Descărcați un exemplu

Sumă după mai multe criterii (41,5 KiB, 10,477 descărcări)

Vezi și:
Sumare celule după culoarea de umplere
Sumarea celulelor în funcție de culoarea fontului
Sumează celulele după formatul celular
Calculați cantitatea de celule prin culoarea de umplere
Calculați cantitatea de celule după culoarea fontului
Cum să însumați date din mai multe coli, inclusiv de condiție

{"Bottom bar": "textstyle": "static", "textpositionstatic": "bottom", "textautohide": true, "textpositionmarginstatic" textpositionmarginright ": 24," textpositionmarginbottom ": 24," texteffect ":" slide "," texteffectasing ":" easeOutCubic "," texteffectduration ": 600," texteffectsididedirection " : 30, "texteffectdelay": 500, "texteffectseparate": false, "texteffect1": "slide", "texteffectslidedirection1": "right", "texteffectslidedistance1": 120, "texteffectasing1": "easeOutCubic", "texteffectduration1" , texteffectdelay1 ": 1000," texteffect2 ":" slide "," texteffectslidedirection2 ":" right "," texteffectslidedistance2 ": 120" texteffectasing2 ":" easeOutCubic "," texteffectduration2 " textcss ":" afișare: bloc; poziție: absolută; top: 0px; stânga: 0px; lățimea: 100%; înălțimea: 100% ; culoare-fundal: # 333333; opacitate: 0,6; filtru: a lpha (opacitate = 60); "," titlecss ":" display: block; poziția: relativă; font: bold 14px \ "Lucida Sans Unicode \", \ "Lucida Grande \", sans-serif, Arial; culoare: #fff; "," descriptioncss ":" afișare: bloc; poziția: relativă; font: 12px \ "Lucida Sans Unicode \", \ "Lucida Grande \", sans-serif, Arial; culoare: #fff; margin-top: 8px; "," buttoncss ":" afișare: bloc; poziția: relativă; margin-top: 8px; "," texteffectresponsive ": true," texteffectresponsivesize ": 640," titlecssresponsive ":" font-size: 12px; "", "addgooglefonts": false, "googlefonts": "", "textleftrightpercentforstatic": 40}}

Căutați după etichete