Trikovi u Excelu 41. deo – Postanite majstor za zadavanje uslova za brojanje.

Funkcije SUMIFS, COUNTIFS i AVERAGEIFS: Možete da vršite izračunavanja na osnovu višestrukih uslova

PROBLEM KORISNIKA:

Funkcije COUNTIF i SUMIF postoje još od Excela 97. Kad god čovek nauči kako da koristi te funkcije, neizbežno se susreće sa situacijom u kojoj treba da broji ili zbraja na osnovu više od jednog uslova.

REŠENJE:

Pre Excela 2007 morali ste da koristite funkciju SUMPRODUCT ili neku komplikovanu formulu niza. Počev od Excela 2007 možete da koristite funkcije SUMIFS, COUNTIFS ili AVERAGEIFS.

Razumete? SUMIFS je množina SUMIF. Ta funkcija može da prihvati do 127 različitih kriterijuma.

PAŽNJA:

Mada SUMIF i SUMIFS zvuče isto, Microsoft je morao da promeni redosled argumenata da bi funkcija SUMIFS radila. Argument Sum_Range koji je bio treći u funkciji SUMIF premešten je na prvo mesto u funkciji SUMIFS.

Da biste napravili funkciju SUMIFS ili AVERAGEIFS, upotrebite ove argumente:

• Sum_Range: Prvo se određuje opseg brojeva koje treba sabrati.

   

   

• Criteria_Range1: Opseg vrednosti koje treba pretražiti.

   

   

• Criteria1: Vrednost koja se traži u opsegu Criteria_Range1.

   

Potom možete da ponovite parove Criteria_Range i Criteria za svaki dodatni uslov.

Slika 1.

Recimo da hoćete da izračunate prosečnu zaradu po odeljenju i starosnoj grupi. Za to su potrebna tri skupa kriterijuma. Mora da se poklapa odeljenje. Pošto hoćete da napravite izveštaj o starosnim grupama po dekadama, treba da potražite godine starosti >=30 i <40 (Slika 1).

 NAPOMENA:

Podaci čiji se prosek utvrđuje slični su podacima iz prethodnih nekoliko tema. Na gornjoj slici ne prikazujem kolone A do F jer bi bila premala. Kolone u skupu podataka pogledajte na Slici 1. Naslovi su u redu br. 1 a podaci su u redovima br. 2 do 57.

 Prvi argument je opseg s vrednostima čiji prosek želite da dobijete. To je F2:F57.

 Sledeći parovi argumenata određuju da Excel treba da pretražuje opseg D2:D57 za godine starosti koje su veće od nule. Obratite pažnju na znak dolara ispred 1 u I$1. To Vam omogućava da iskopirate formulu. Taj argument će uvek ukazivati na kriterijum u redu br. 1, ali se referenca može promeniti u kolonu J, K, L i M.

 Sledeći par argumenata kaže da se pretraže godine starosti u opsegu ćelija D2:D57 i da se pronađu godine starosti ispod 30. I ovde je kriterijum naveden u I$2.

 Poslednji par argumenata kaže da se pretraže odeljenja u opsegu ćelija E2:E57 i da se pronađu podaci koji se odnose na odeljenje računovodstva, što je navedeno u $H3.

PAŽNJA:

Poruka #DIV/0! u ćeliji M6 javlja se zato što odeljenje prodaje nema zaposlene iznad 59 godina starosti (verovatno zato što su se komercijalisti povukli na svoje privatno ostrvo posle mnogo godina zarađivanja ogromnih provizija). Kada pravite prosek opsega u kojem nema numeričkih ćelija, rezultat će biti deljenje s nulom.