Cum se utilizează COUNTIFS, SUMIFS, AVERAGEIFS în Excel

Cele trei formule cele mai frecvent utilizate în Excel care efectuează calcule matematice simple sunt COUNT , SUM și AVERAGE . Indiferent dacă gestionați un buget financiar în Excel sau pur și simplu urmăriți următoarea vacanță, probabil că ați mai folosit una dintre aceste funcții.

În acest articol, vom parcurge elementele de bază ale acestor trei funcții și omologii lor relevante și utile: COUNTIFS, SUMIFS și AVERAGEIFS.

Să presupunem că începem o nouă afacere online de vânzare de telefoane mobile și avem o fișă care enumeră vânzările pe care le-am făcut în primele două luni. Descărcați exemplu de foaie de calcul Excel aici .

Excel COUNT, SUM și AVERAGE

Pentru a ști câte telefoane mobile am vândut, putem folosi rapid formula COUNT , după cum se arată mai jos:

=NUMĂRĂ(E2:E16)

Pe de altă parte, pentru a obține valoarea totală a vânzărilor pe care le-am realizat, putem folosi formula SUMA așa cum se arată mai jos:

=SUMA(E2:E16)

În cele din urmă, pentru a afla vânzările medii pe care le-am făcut pentru toate telefoanele, putem folosi formula MEDIE după cum urmează:

=MEDIE(E2:E16)

Rezultatul ar trebui să fie ca mai jos:

Formulele COUNT, SUM și AVERAGE vor funcționa numai pentru înregistrările în care valoarea celulei este în format numeric. Orice înregistrare din intervalul de formule (adică E2:E16 în acest exemplu) care nu este în formatul numeric va fi ignorată.

Prin urmare, asigurați-vă că toate celulele din formula COUNT, SUM și AVERAGE sunt formatate ca Number , nu Text . Încercați să utilizați aceeași formulă, dar cu E:E ca interval în loc de E2:E16 . Va returna același rezultat ca înainte, deoarece ignoră antetul (adică Prețul de vânzare ), care este în format text.

Acum, ce se întâmplă dacă vrem să știm numărul de vânzări, valoarea totală a vânzărilor și valoarea medie a vânzărilor pe telefon, doar pentru cele vândute în SUA? Aici este locul în care COUNTIFS, SUMIFS și AVERAGEIFS joacă un rol important. Respectați formula de mai jos:

COUNTIFS

Defalcarea formulei:

1. =COUNTIFS( – „=” indică începutul unei formule în celulă și COUNTIFS este prima parte a funcției Excel pe care o folosim.
2. D2:D16 – Se referă la intervalul de date pentru a verifica dacă îndeplinește criteriile pentru a fi incluse în formula de numărare.
3. „SUA” – Criterii de căutat în intervalul de date specificat ( D2:D16 )
4. ) – Paranteză de închidere care indică sfârșitul formulei.

Formula returnează 6, care este numărul de vânzări pentru produsele expediate din depozitul SUA.

SUMIFS

Defalcarea formulei:

1. =SUMIFS( – „=” indică din nou începutul formulei.
2. E2:E16 – Se referă la intervalul de date pe care am dori să le totalăm, adică prețul de vânzare în exemplul nostru.
3. D2:D16 – Se referă la intervalul de date pentru a verifica dacă îndeplinește criteriile pentru a fi incluse în suma totală.
4. „SUA” – Criterii de căutat în intervalul de date specificat ( D2:D16 )
5. ) – Paranteză de închidere care indică sfârșitul formulei.

Formula arată vânzări totale de 6.050 USD care au fost realizate pentru produsele expediate din depozitul SUA.

MEDIEIFS

Defalcarea formulei:

1. =AVERAGEIFS( – „=” indică începutul formulei.
2. E2:E16 – Se referă la intervalul de date pe care am dori să o mediem. În acest exemplu, dorim să obținem valoarea medie a vânzărilor pentru toate telefoanele vândute în SUA.
3. D2:D16 – Se referă la intervalul de date pentru a verifica dacă îndeplinește criteriile pentru a fi incluse în formula medie.
4. „SUA” – Criterii de căutat în intervalul de date specificat
5. ) – Paranteză de închidere care indică capetele formulei.

Formula arată că am vândut produsul pentru aproximativ 1.008 USD per telefon în SUA.

Toate cele trei formule pot lua mai mult de un criteriu. De exemplu, dacă vrem să cunoaștem aceleași cifre (adică COUNT , SUM și AVERAGE ) pentru produsele vândute în SUA , dar în mod specific doar pentru  marca Samsung , trebuie doar să adăugăm intervalul de date care trebuie verificat urmat de criteriile acestuia.

Vă rugăm să vedeți exemplul de mai jos în care un al doilea criteriu este adăugat la verificările inițiale ale criteriilor. (Textul albastru indică primul criteriu și roșu indică al doilea criteriu)

=NUMĂRĂȚI(D2:D16, „SUA”, B2:B16, „Samsung”) =SUMIFS(E2:E16,D2:D16, „SUA”, B2:B16, „Samsung”) =MEDIAJEF(E2:E16,D2 :D16,"SUA", B2:B16,"Samsung")

Veți observa că Excel are și  formule COUNTIF , SUMIF și AVERAGEIF fără sufixul „S” . Acestea sunt folosite similar cu COUNTIFS , SUMIFS și AVERAGEIFS . Cu toate acestea, cei fără sufixul „S” în formulă au limitarea de a permite doar un criteriu per formulă.

Deoarece sintaxa este ușor diferită, aș recomanda să utilizați  COUNTIFS , SUMIFS și AVERAGEIFS numai deoarece poate fi folosit fie pentru un criteriu, fie pentru mai multe, dacă este necesar. Bucurați-vă!