01 od 02
Zbroj ćelija koje padaju između dvije vrijednosti
Lifewire
Funkcija SUMPRODUCT u Excelu vrlo je svestrana funkcija koja će dati različite rezultate ovisno o načinu na koji se unose argumenti funkcije.
Normalno, kao što mu ime sugerira, SUMPRODUCT množi elemente jednog ili više nizova kako bi dobio njihov umnožak, a zatim zbraja ili zbraja umnoške.
Međutim, prilagođavanjem sintakse funkcije, ona se može koristiti za zbrajanje samo podataka u ćelijama koje zadovoljavaju određene kriterije.
Od Excela 2007, program sadrži dvije funkcije - SUMIF i SUMIFS - koje će zbrajati podatke u ćelijama koje zadovoljavaju jedan ili više postavljenih kriterija.
Međutim, povremeno je lakše raditi sa SUMPRODUCT kada treba pronaći više uvjeta koji se odnose na isti raspon kao što je prikazano na gornjoj slici.
Sintaksa funkcije SUMPRODUCT za zbrajanje ćelija
Sintaksa koja se koristi za dobivanje SUMPRODUCT za zbrajanje podataka u ćelijama koje ispunjavaju određene uvjete je:
=SUMPRODUCT([uvjet1][uvjet2][niz])
uvjet1, uvjet2 - uvjeti koji moraju biti ispunjeni prije nego što funkcija pronađe umnožak niza.
niz - kontinuirani raspon ćelija
Primjer: zbrajanje podataka u ćelijama koje ispunjavaju više uvjeta
Primjer na gornjoj slici dodaje podatke u ćelije u rasponu od D1 do E6 koje su između 25 i 75.
Ulazak u funkciju SUMPRODUCT
Budući da ovaj primjer koristi nepravilan oblik funkcije SUMPRODUCT, dijaloški okvir funkcije ne može se koristiti za unos funkcije i njezinih argumenata. Umjesto toga, funkcija se mora ručno upisati u ćeliju radnog lista.
- Kliknite ćeliju B7 na radnom listu kako biste je učinili aktivnom ćelijom;
- Unesite sljedeću formulu u ćeliju B7: =SUMPROIZVOD(($A$2:$B$6>25)($A$2:$B$6<75)(A2:B6))
- Odgovor 250 trebao bi se pojaviti u ćeliji B7
- Do odgovora se došlo zbrajanjem pet brojeva u rasponu (40, 45, 50, 55 i 60) koji su između 25 i 75. Ukupno je 250
Razlaganje formule SUMPRODUCT
Kada se uvjeti koriste za svoje argumente, SUMPRODUCT procjenjuje svaki element niza prema uvjetu i vraća Booleovu vrijednost (TRUE ili FALSE).
Za potrebe izračuna, Excel dodjeljuje vrijednost 1 za one elemente niza koji su TRUE (zadovoljavaju uvjet) i vrijednost 0za elemente niza koji su FALSE (ne ispunjavaju uvjet).
Na primjer, broj 40:
- je TRUE za prvi uvjet tako da je vrijednost 1 dodijeljena u prvom nizu;
- je TRUE za drugi uvjet tako da je vrijednost 1 dodijeljena u drugom nizu.
Broj 15:
- je FALSE za prvi uvjet tako da je vrijednost 0 dodijeljena u prvom nizu;
- je TRUE za drugi uvjet tako da je vrijednost 1 dodijeljena u drugom nizu.
Odgovarajuće jedinice i nule u svakom nizu se međusobno množe:
- Za broj 40 - imamo 1 x 1 koji vraća vrijednost 1;
- Za broj 15 - imamo 0 x 1 koja vraća vrijednost 0.
Množenje jedinica i nula s rasponom
Ove jedinice i nule se zatim množe s brojevima u rasponu A2: B6
Ovo se radi kako bismo dobili brojeve koje će funkcija zbrojiti.
Ovo radi jer:
- 1 puta bilo koji broj jednak je originalnom broju
- 0 puta bilo koji broj jednako je 0
Završavamo s:
-
140=40
015=0
022=0
145=45
150=50
155=55
025=0
075=0
160=600100=0
Zbrajanje rezultata
SUMPRODUCT zatim sažima gore navedene rezultate kako bi pronašao odgovor.
40 + 0 + 0 + 45 + 50 + 55 + 0 + 0 + 60 + 0=250
