Kako koristiti Excel funkciju SUMPRODUCT u podatkovnim modelima

Funkcija ZBIRNI PROIZVOD zapravo je navedena u kategoriji matematike i trigonometrije Excelovih funkcija. Budući da je primarna svrha SUMPRODUCT-a izračunavanje zbrojnog proizvoda, većina ljudi ne zna da ga zapravo možete koristiti za traženje vrijednosti. Zapravo, ovu svestranu funkciju možete vrlo učinkovito koristiti u većini modela podataka.

Osnove SUMPRODUCT

Funkcija SUMPRODUCT dizajnirana je za množenje vrijednosti iz dva ili više raspona podataka, a zatim zbrajanje rezultata kako bi se vratio zbroj proizvoda. Pogledajte sljedeću sliku kako biste vidjeli tipičan scenarij u kojem je SUMPRODUCT koristan.

SUMPRODUCT, dobivanje ukupne prodaje uključuje množenje cijene” width=”458″/>

Bez ZBIRNOG PROIZVODA, dobivanje ukupne prodaje uključuje množenje cijene po jedinicama i zatim zbrajanje rezultata.

Vidite uobičajenu analizu u kojoj trebate ukupnu prodaju za godine 2011. i 2012. Kao što vidite, da biste dobili ukupnu prodaju za svaku godinu, prvo morate pomnožiti cijenu s brojem jedinica da biste dobili ukupnu prodaju za svaku godinu Regija. Zatim morate zbrojiti te rezultate da biste dobili ukupnu prodaju za svaku godinu.

Uz funkciju SUMPRODUCT, možete izvršiti analizu u dva koraka sa samo jednom formulom. Sljedeća slika prikazuje istu analizu s formulama ZBIRNI PROIZVOD. Umjesto da koristite 11 formula, možete izvršiti istu analizu sa samo 3!

Funkcija SUMPRODUCT omogućuje vam da izvršite istu analizu sa samo “širina=”535″/>

Funkcija SUMPRODUCT omogućuje vam da izvršite istu analizu sa samo 3 formule umjesto 11.

Sintaksa funkcije SUMPRODUCT prilično je jednostavna:

ZBIRNI PROIZVOD ( Niz1 , Niz2 , …)

Array : Array predstavlja raspon podataka. Možete koristiti bilo gdje od 2 do 255 nizova u formuli SUMPRODUCT. Nizovi se množe zajedno i zatim zbrajaju. Jedino tvrdo i brzo pravilo koje morate zapamtiti je da svi nizovi moraju imati isti broj vrijednosti. To jest, ne možete koristiti ZBIRNI PROIZVOD ako raspon X ima 10 vrijednosti, a raspon Y 11 vrijednosti. Inače, dobivate #VRIJEDNOST! pogreška.

Zaokret u funkciji SUMPRODUCT

Zanimljiva stvar u vezi s funkcijom SUMPRODUCT je da se može koristiti za filtriranje vrijednosti. Pogledajte sljedeću sliku:

Funkcija SUMPRODUCT može se koristiti za filtriranje podataka na temelju kriterija.” širina=”535″/>

Funkcija SUMPRODUCT može se koristiti za filtriranje podataka na temelju kriterija.

Formula u ćeliji E12 povlači zbroj ukupnih jedinica samo za regiju Sjever. U međuvremenu, ćelija E13 povlači jedinice evidentirane za regiju Sjever 2011. godine.

Da biste razumjeli kako to funkcionira, pogledajte formulu u ćeliji E12. Ta formula glasi ZUMPROIZVOD((C3:C10=“Sjever“)*(E3:E10)).

U Excelu se TRUE procjenjuje na 1, a FALSE na 0. Svaka vrijednost u stupcu C koja je jednaka sjeveru vrijedi TRUE ili 1. Ako vrijednost nije Sjever, ona se procjenjuje na FALSE ili 0. Dio formule koji glasi (C3 :C10=“Sjever“) nabraja svaku vrijednost u rasponu C3:C10, dodjeljuje 1 ili 0 svakoj vrijednosti. Zatim interno, formula SUMPRODUCT prevodi se u

(1*E3)+(0*E4)+(0*E5)+(0*E6)+(1*E7)+(0*E8)+(0*E9)+(0*E10).

Ovo vam daje odgovor iz 1628. jer

(1*751)+(0*483)+(0*789)+(0*932)+(1*877)+(0*162)+(0*258)+(0*517)

jednako 1628.

Primjena formula SUMPRODUCT u modelu podataka

Kao i uvijek u Excelu, ne morate strogo kodirati kriterije u svojim formulama. Umjesto da eksplicitno koristite "Sjever" u formuli SUMPRODUCT, možete referencirati ćeliju koja sadrži vrijednost filtra. Možete zamisliti da ćelija A3 sadrži riječ Sjever, u kojem slučaju možete koristiti (C3:C10=A3) umjesto (C3:C10=“Sjever“). Na taj način možete dinamički mijenjati kriterije filtriranja, a vaša formula se nastavlja.

Sljedeća slika pokazuje kako možete koristiti ovaj koncept za povlačenje podataka u scensku tablicu na temelju više kriterija. Imajte na umu da svaka od ovdje prikazanih formula SUMPRODUCT upućuje na ćelije B3 i C3 za filtriranje na računu i liniji proizvoda. Opet, možete dodati padajuće popise za provjeru valjanosti podataka u ćelije B3 i C3, što vam omogućuje jednostavnu promjenu kriterija.

Funkcija SUMPRODUCT može se koristiti za izvlačenje sažetih brojeva iz d” width=”535″/>

Funkcija SUMPRODUCT može se koristiti za povlačenje sažetih brojeva iz podatkovnog sloja u scenske tablice.