Višeuslovna agregacija u Excel izveštajima

U prethodnom tekstu mogli ste da saznate šta su to funkcije za uslovnu agregaciju, a opisali smo kako rade SUMIF, AVERAGEIF i COUNTIF. Postoji i klasa funkcija, malo novijeg datuma, koja dozvoljava da iskoristite i više od jednog kriterijuma kako biste izračunali sumu, prosek, prebrojali vrednosti… Nakon ovog teksta verujemo da ćete ih često koristiti, te da će vam one postati nezamenjivi alati u svakodnevnom radu.

Prva funkcija o kojoj će biti reč je funkcija SUMIFS. Njena sintaksa je:

SUMIFS (<opseg za sumiranje>;<opseg₁>; <kriterijum₁>;…;<opseg_n>; <kriterijum_n>)

Prvi argument fukcije je opseg nad kojim se vrši agregacija, odnosno kolona na osnovu koje sabiramo vrednosti. Opseg može biti skup ćelija, kolona, imenovani niz… Zatim dodajemo parove opsega i kriterijuma. Minimalno bi trebalo navesti bar jedan par, a funkcija podržava do 127 parova opsega i kriterijuma. Da biste lakše razumeli kako ona radi, hajde da to pogledamo na jednom jednostavnom primeru.

Kompanija koja se bavi distribucijom bezalkoholnih pića angažuje terenske komercijaliste. Oni obilaze maloprodajne lokacije (supermarkete, prodavnice, trafike itd.) i evidentiraju porudžbine kupaca. U izvornoj tabeli možete da vidite ime i prezime komercijaliste, naziv artikla i poručene količine. Kako izračunati porudžbine artikala u odnosu na komercijaliste? Napisaćemo formulu:

=SUMIFS(D2:D1002;C2:C1002;F4;B2:B1002;G3)

Formula kaže da treba da saberemo vrednosti u koloni sa količinama, a kao uslov zadajemo kolonu sa nazivima artikala te je poredimo sa nazivom u ćeliji F4, kao i kolonu sa imenima komercijalista te je poredimo sa kolonom G3. Ova formula će vratiti rezultat koliko je Aleksandar Stošić uzeo porudžbina za artikal Coca-Cola 0.5l.

Ipak, ne bi bilo praktično da za svaki par artikal-komercijalista pišemo formulu ispočetka. Sve kolone koje se pretražuju ćemo fiksirati, dodavanjem znaka $ ispred oznake kolone i reda. Pošto se podaci o artiklima nalaze u koloni F, treći argument treba da bude $F4, a pošto se imena komercijalista nalaze u trećem redu poslednji argument označićemo kao G$3. Kopiranjem ovakve formule u preostale ćelije u tabeli dobićemo vrednosti poručivanja svakog artikla za svakog komercijalistu. Zgodno, zar ne?

Postoji još nekoliko funkcija za vešeuslovnu agregaciju, a njihova sintaksa je:

AVERAGEIFS (<opseg za sumiranje>;<opseg₁>; <kriterijum₁>;…;<opseg_n>; <kriterijum

MAXIFS (<opseg za sumiranje>;<opseg₁>; <kriterijum₁>;…;<opseg_n>; <kriterijum

MINIFS (<opseg za sumiranje>;<opseg₁>; <kriterijum₁>;…;<opseg_n>; <kriterijum

COUNTIFS (<opseg₁>; <kriterijum^₁>;…;<opseg_n>; <kriterijum_n>)

Prva funkcija, po istom principu, računa prosek zadatih vrednosti. Druga računa maksimum, treća minimum, a poslednja se koristi za prebrojavanje vrednosti u tabeli.