1. Introducere in agregarea Excel conditionata

Microsoft Excel ofera la o prima vedere o serie de functii redundante pentru efectuarea de calcule agregate (suma, medie, minimum, maximum, numarare, etc). Insa nu este asa, sa le luam pe rand:

Sa luam un tabel de referinta pentru a urmari mai usor:

Metode de a insuma conditionat (3 functii):

 SUMIF: adunare in functie de o singura conditie logica

Exemplul 1.1: Suma Stoc pentru Magazinele din „Bucuresti Sector 1”

=SUMIF(A2:A14, „Bucuresti Sect 1”, D2:D14)

Exemplul 1.2: Suma Stoc pentru toate Magazinele din Bucuresti (din toate sectoarele)

=SUMIF(A2:A14, „*Bucuresti*”, D2:D14)

SumIF nu poate calcula Suma Stoc pentru produsele „Vin” din „Bucuresti Sect 1”, pentru ca in acest caz avem doua conditii logice de indeplinit simultan (Se poate face usor cu SUMIFS)

Concluzia pentru SUMIF: O singura conditie logica, cu sau fara wildcards

SUMIFS: aduna in functie de mai multe conditii logice (ce trebuiesc indeplinite simultan)

Exemplul 1.3: Suma Stoc pentru produsele  „Vin” din „Bucuresti Sect 1”

=SUMIFS(D2:D14, A2:A14, „Bucuresti Sect 1”, B2:B14, „Vin”)

Exemplul 1.4: Suma Stoc pentru Berea din magazinele „Bucuresti Sect 1” plus „Bucuresti Sect 2”

=SUMIFS(D2:D14, A2:A14, „Bucuresti Sect 1”, B2:B14, „Bere”) + SUMIFS(D2:D14, A2:A14, „Bucuresti Sect 2”, B2:B14, „Bere”)

Concluzia pentru SUMIFS: Stie sa gestioneaza mai multe conditii logice de indeplinit SIMULTAN (AND logic), insa in cazul in care dorim OR logic (reuniunea) trebuie scrise mai multe formule SUMIFS si apoi adunate.

DSUM: aduna in functie de oricate conditii logice (ce trebuie SAU nu indeplinite simultan)

Aceasta functie, mai putin cunoscuta de catre utilizatorii obisnuiti, stie sa adune in functie de oricate conditii logice (legate cu orice conjunctie booleana: AND, OR, NOT).

Pentru celelelate functii agregate se aplica aceleasi reguli ca mai sus, astfel:

Numarari (count) conditionate (3 functii):

COUNTIF: numara in functie de o singura conditie logica

COUNTIFS: numara in functie de mai multe conditii logice (ce trebuiesc indeplinite simultan)

DCOUNT: numara in functie de oricate conditii logice (ce trebuiesc SAU nu indeplinite simultan)

Medii conditionate (3 functii):

AVERAGEIF: face media in functie de o singura conditie logica

AVERAGEIFS: face media in functie de mai multe conditii logice (ce trebuiesc indeplinite simultan)

DAVERAGE: face media in functie de oricate conditii logice (ce trebuiesc SAU nu indeplinite simultan)

Minimum conditionat (2 functii):

MINIFS: afla valoarea cea mai mica in functie de mai multe conditii logice (ce trebuiesc indeplinite simultan)

DMIN: afla valoarea cea mai mica in functie de oricate conditii logice (ce trebuiesc SAU nu indeplinite simultan)

 Maximum conditionat (2 functii):

MAXIFS: afla valoarea cea mai mare in functie de mai multe conditii logice (ce trebuiesc indeplinite simultan)

DMAX: afla valoarea cea mai mare in functie de oricate conditii logice (ce trebuiesc SAU nu indeplinite simultan)

Dupa cum observati, exista nu mai putin de 13 functii agregate conditionate uzuale (mai exista si altele, mai rar folosite), insa astazi vom vorbi in peste 20 de pagini (si 3000 de cuvinte) doar despre SUMELE AGREGATE

Partea buna este ca daca prindeti „smecheria” cu SUMIFS, toate celelalte se folosesc (cam) la fel.

2. Functia SUMIF – sintaxa si utilizare

Daca nu ati parcurs articolul precedent legat de folosirea functiei SUMIF, acum este momentul sa o faceti. Aici voi puncta doar diferentele fata de SUMIFS

=SUMIF (celulele_unde_caut,  criteriu_de_cautare,  [celulele_de_unde_adun])

celulele_unde_caut – celulele care trebuie evaluate dupa criteriile dvs., de exemplu A1: A10.

criteriu_de_cautare – conditia care trebuie indeplinita. De exemplu, puteti introduce criterii precum „23”,  „seminte”, „02/13/2020”,  „<7” sau direct B20 (adresa celulei ce contine valoarea de cautat) etc.

celulele_de_unde_adun – celulele pentru care se doreste insumarea daca criteriu_de_cautare este indeplinit. Acest argument este optional si trebuie sa-l utilizati numai daca doriti sa insumati alte celule decat cele definite in argumentul celulele_unde_caut.

Exemplul 2.1: Cum folosesc SUMIF, un exemplu simplu:

In tabelul de mai jos dorim sa calculam suma Stoc pentru Vin (de la toate Orasele):

=SUMIF(B2:B14,G12,D2:D14)

 

Exemplul 2.2: Cum adun conditionat folosind functia SUMIF si cautari aproximative cu wildcards (metacaractere)

Vrem suma Stoc pentru Piper (indiferent de tip)

=SUMIF(B2:B14,”*piper*”,D2:D14)

3. Functia SUMIFS – sintaxa si utilizare

Utilizati SUMIFS in Excel pentru a gasi o suma conditionata a valorilor pe baza a mai multor criterii.

Functia SUMIFS a fost introdusa in Excel 2007, astfel incat sa o poti folosi in toate versiunile moderne de Excel: Office 365, 2019, 2016, 2013, 2010 si 2007.

Fata de SUMIF, sintaxa SUMIFS este putin mai lunga:

SUMIFS (sum_range, criterii_range1, criterii1, [criterii_range2, criterii2], …)

Primele 3 argumente sunt obligatorii, intervalele suplimentare si criteriile asociate acestora sunt optionale.

sum_range – este obligatorie si reprezinta celulele de unde in final va aduna. Doar celulele cu numere sunt insumate; valori goale si text sunt ignorate.

criterii_range1 – este necesar si reprezinta celulele peste care aplicam prima conditie logica.

criterii1 – prima conditie care trebuie indeplinita, obligatorie. Puteti furniza criteriile sub forma unui numar, expresie logica, referinta a celulelor, text sau o alta functie Excel. De exemplu, puteti utiliza criterii precum: 23, „> = 35”, D2, „gutui” sau TODAY ().

criterii_range2, criterii2, … – acestea sunt intervale suplimentare si criterii asociate acestora, optional. Puteti utiliza pana la 127 de perechi de intervale / criterii in formulele SUMIFS.

Observatie: Functia SUMIFS functioneaza cu logica AND, ceea ce inseamna ca o celula din intervalul sumei este insumata numai daca indeplineste toate criteriile specificate (daca toate criteriile sunt adevarate pentru acea celula).

Exemplul 3.1: Dorim sa calculam suma Stocurilor de la Iasi pentru caracatita?

=SUMIFS(D2:D12, A2:A12, „Iasi”, B2:B12, „Caracatita”)

In acest exemplu:

sum_range este D2:D12, adica coloana de unde vom aduna (atentie ca este exact invers ca la SUMIF). La SUMIFS se incepe cu coloana de agregat

criterii_range1 este prima coloana unde o sa punem prima conditie logica, in cazul nostru coloana Magazin (A2:A12)

criterii1 este prima conditie logica de indeplinit (pentru coloana criterii_range1). Cautam „Iasi” pe coloana A2:A12

criterii_range2 este a doua coloana unde o sa punem prima conditie logica, in cazul nostru coloana cu Produs (B2:B12)

criterii2 este a doua conditie logica de indeplinit (pentru coloana criterii_range2). Cautam „Caracatita” pe coloana B2:B12

Atentie, se evalueaza ambele criterii de indeplinit SIMULTAN. Observati ca in tabel mai exista o valoare Iasi care este ignorata (nu detine caracatita)

Exemplul 3.2: Folosirea operatorilor de comparatie de catre SUMIFS

Dorim Suma Stoc pentru toate produsele mai putin pentru Vin si Bere

=SUMIFS(D2:D14, B2:B14,”<>Vin”, B2:B14, „<>Bere”)

Observati ca operatorii de comparatie se folosesc in interiorul ghilimelelor la argumentele de criterii

Exemplul 3.3: Folosirea SUMIFS cu datele calendaristice

In tabelul de mai jos dorim sa calculam Suma Valorilor de la toate facturile scadente intre datele: 1 Februarie 2020 si 1 Martie 2020

Si mai elegant este sa punem cele doua date calendaristice in doua celule separate si apoi sa le concatenam dinamic in formula :

=SUMIFS(E2:E12, B2:B12, „>=” & G5, B2:B12, „<=” & G6)

Exemplul 3.4: Cum lucreaza SUMIFS cu valorile BLANKS si EMPTY („”)

Avem urmatorul tabel, in care am pus atat celule blank cat si una empty (cu „”)

Intrebarea ar fi de ce ar scrie cineva intr-o celula =”” ?

Nu a scris nimeni explicit, pur si simplu e posibil ca valoarea empty sa fie intoarsa de o alta formula

Adunam Valorile facturilor care au Data necompletata:

=SUMIFS(E2:E10, B2:B10, „=”)

Adunam Valorile facturilor care au Data necompletata sau EMPTY

=SUMIFS(E2:E10, B2:B10, „”)

Daca am scadea cele doua sume de mai sus, am gasi suma valorilor de la toate celulele empty 😊:

=SUMIFS(E2:E10, B2:B10, „”) – SUMIFS(E2:E10, B2:B10, „=”)

Urmariti tabelul de mai jos, pentru ca am explicat in el toate variante de cautari (NON) BLANK SI/SAU (NON) EMPTY:

Exemplul 3.5: Suma Stocuri pentru toate produsele de tip Piper din Iasi

Formula presupune folosirea wildcards de cautare aproximativa:

=SUMIFS(D2:D14, A2:A14, „Iasi”, B2:B14, „*piper*”)

4. SUMIFS cu mai multe criterii OR

Daca sunt mai multe conditii logice de indeplinit separat si nu simultan (adica OR logic, nu AND logic), avem trei metode pentru a lucra cu SUMIFS in continuare”

Metoda 1: + + …

Metoda 2: SUM impreuna cu un parametru de tip vector returnat de SUMIFS

Metoda 3: Folosind SUMPRODUCT impreuna cu functia MATCH

Sa le luam pe rand:

Metoda 1: <SUMIFS1…> + <SUMIFS2…>

Exemplul 4.1: Dorim sa calculam in tabelul de mai jos: Suma Valorilor pentru facturile cu produsele: Aspen, Carlota si Sunset

                     =SUMIFS(D2:D12, C2:C12, „Aspen”, D2:D12, „>100”) +

                                       SUMIFS(D2:D12, C2:C12, „Carlota”, D2:D12, „>100”) +

                                                      SUMIFS(D2:D12, C2:C12, „Sunset”, D2:D12, „>100”)

Metoda 2: SUM impreuna cu un parametru de tip vector returnat de SUMIFS

Exemplul 4.2: Declaram vectorul manual in interiorul formulei

=SUM(SUMIFS(D2:D12, C2:C12, {„Aspen”,”Carlota”,”Sunset”}, D2:D12, „>100”))

Metoda 3: Folosind SUMPRODUCT impreuna cu MATCH

Exemplul 4.3: Punem lista de valori cautate intr-un panou de control

Aceasta metoda este favorita mea, deoarece putem pune valorile cautate cu OR in propriul panou de control 😊 si automatiza astfel procesul de calculare.

=SUMPRODUCT(–(D2:D12>=K8), –(ISNUMBER(MATCH(C2:C12, K4:K6,0))), D2:D12)

Explicatii:

Primul vector:  D2:D12>=K8 intoarce o succesiune de TRUE si FALSE (in functie de indeplinirea conditiei: Valoarea>100)

{FALSE;FALSE;TRUE;FALSE;TRUE;TRUE;FALSE;TRUE;TRUE;FALSE;FALSE}

Am pus – in fata pentru a-l oblica sa transforme valorile booleene in cifre (TRUE=1, FALSE=0)

{0;0;1;0;1;1;0;1;1;0;0}

MATCH(C2:C12, K4:K6,0), intoarce un vector plin cu TRUE si FALSE, in functie daca valorile de pe coloana C2:C13 se gasesc in lista de produse (K4:K6), apoi – transforma vectorul in 1 si 0:

{1;1;#N/A;#N/A;2;3;2;#N/A;1;#N/A;#N/A} (observam ca apare eroarea #N/A daca MATCH nu gaseste, de aceea am folosit functia isNumber)

Ultimul vector este cel cu valorile de insumat (D2:D12):

{12.12;30.29;176.89;84.14;138.73;178.2;19.82;253.12;112.7;31.53;54.62}

La final SUMPRODUCT inmulteste valorile corespondente din fiecare dintre cei 3 vectori:

0*1*12.12 + 0*1* 30.29 +… ati prins ideea, va face sumele reale doar la acele valori diferite de 0 din primul vector 😊

5. SUMIF cu mai multe criterii de cautare AND/OR

Probabil ca folosesti o versiunea mai veche de Excel 2007, numai asa se explica de ce nu folosesti functiile de insumare dupa mai multe conditii (SUMIFS, DSUM)

Dupa cum ati vazut, sintaxa functiei SUMIF permite o singura conditie logica de cautare.

Si totusi, SUMIF poate fi utilizata pentru a aduna valori in functie de mai multe criterii de cautare.

Iata cum, in trei metode:

-Prin adaugarea unor coloane calculate (daca conditiile sunt de indeplinit simultan; AND logic)

-Prin adunarea rezultatelor mai multor functii SUMIF (OR logic)

-Prin utilizarea functiilor SUM si IF (luate separat) cu criterii de adunari vectoriale

Metoda 1: SUMIF cu coloana „helper” (ajutatoare) pentru conditii multiple (AND/OR logic)

Exemplul 5.1: Suma Stoc pentru produsele „Vin” din „Bucuresti Sect 1”

Adaugam o coloana noua la tabel, in care calculam daca linia respectiva indeplineste ambele conditii in acelasi timp (folosim functia AND)

=AND(A2=”Bucuresti Sect 1″, B2=”Vin”)

Cu functia SUMIF cautam si insumam Stocurile pentru care gasim valoarea True pe coloana noua:

=SUMIF(E2:E14, TRUE, D2:D14)

Personal nu-mi place aceasta metoda care presupune adaugarea de coloane noi calculate.

In cazul in care avem conditii logice multiple cu AND logic, prefer metoda rapida cu SUMIFS:

=SUMIFS(D2:D14, A2:A14, „Bucuresti Sect 1”, B2:B14, „Vin”)

 (insa aceasta functie a aparut prima oara la Excel 2007)

 Metoda 2: Prin adunarea rezultatelor mai multor functii SUMIF (OR logic)

Exemplul 5.2 : Sa se insumeze stocurile de la categoriile de produse: Cereale si Legume

In acest caz (OR logic, adica reuniunea celor doua multimi Cereale si Legume) la fel ca la SUMIFS, trebuie sa scriu doua formule SUMIF si sa le adun:

=SUMIF(C2:C14, „Cereale”, D2:D14) + =SUMIF(C2:C14, „Legume”, D2:D14)

Metoda 3: Prin utilizarea formulelor SUM si IF (luate separat) cu criterii de adunari vectoriale

Putem aduna agregat conditionat cu SUMIF si conditii multiple si fara coloane ajutatoare, insa tehnica este mai complexa.

Recomand aceasta metoda doar celor care folosesc versiuni de Excel 2003 sau mai vechi (pentru ca nu au de ales, ei neavand acces la functia SUMIFS)

Exemplul 5.3: Suma Stoc pentru produsele „Vin” din „Bucuresti Sect 1”, de data asta sa scriem o singura formula si fara coloana ajutatoare:

=SUM((B2:B14=”Vin”) * (A2:A14=”Bucuresti Sect 1″) * ( D2:D14))

– rezultatul este 920

Daca va apucati deja sa scrieti formula, nu uitati sa apasati la final CTRL + SHIFT + ENTER (pentru ca este o formula vectoriala)

Expresia (B2:B14=”Vin”) compara o coloana cu un numar, asta inseamna ca testul se va realiza pentru fiecare rand intors de coloana:

{TRUE;FALSE;FALSE;FALSE;TRUE;FALSE;FALSE;FALSE;TRUE;FALSE;TRUE;FALSE;FALSE} – vector output

Pentru ca apoi apare semnul * (inmultire), vectorul de output este transformat din valori booleene (true, false) in valori numerice. Toata lumea stie ca True este 1 si False 0

{1;0;0;0;1;0;0;0;1;0;1;0;0} – in cazul nostru 1 apare acolo unde produsul este Vin

Asemanator facem si cu al doilea vector (A2:A14=”Bucuresti Sect 1″), rezulta:

{0;1;0;0;1;0;0;0;1;0;0;0;0}

Ce insemna ca inmultim cei doi vectori:

{1;0;0;0;1;0;0;0;1;0;1;0;0} * {0;1;0;0;1;0;0;0;1;0;0;0;0}

Rezultatul o sa fie un vector de aceeasi dimensiune cu cei doi, ce va contine 1 doar daca gaseste 1 in ambii vectori pe aceeasi pozitie:

{0;0;0;0;1;0;0;0;1;0;0;0;0} – adica doar doua pozitii cu ambele conditii indeplinite

Inmultim vectorul rezultat cu ultimul vector (coloana cu Stocurile) si rezulta:

{0;0;0;0;60;0;0;0;860;0;0;0;0}

La final SUM aduna toate elementele din vector, rezultand suma agregata conditionata multiplu (cu AND)

Dar daca doream reuniunea? Adica suma stocuri „Vin” (indiferent de Oras) + „Bucuresti Sect 1” (indiferent de produs)?

Pornim de la:

=SUM(((B2:B14=”Vin”) + (A2:A14=”Bucuresti Sect 1″)) * (D2:D14))

Observati semnul + (adunare)

Daca adunam valorile logice dupa testare, observam ca va aparea numarul 2 in dreptul celor care indeplinesc simultan ambele conditii

{1;1;0;0;2;0;0;0;2;0;1;0;0}

Putem sa impartim la 2 apoi sa rotunjim la primul nr intreg, rezulta:

{1;1;0;0;1;0;0;0;1;0;1;0;0}

Rezulta formula finala:

{=SUM(ROUND(((B2:B14=”Vin”)+(A2:A14=”Bucuresti Sect 1″))/2,0) *(D2:D14))}

Nu uitati de CTRL + SHIFT + ENTER 😊

Nu scrieti acoladele manual ca nu functioneaza, ele se pun automat dupa ce faceti combinatia de dinainte.