Jedno od čestih pitanja na oglasnoj ploči je kako koristiti funkciju SumIf s dva različita uvjeta. Nažalost, odgovor je da SumIf ne može podnijeti dva različita uvjeta.
Da biste postigli dva uvjeta, morate koristiti prilično složenu formulu niza. Dodatak čarobnjaka za uvjetni zbroj omogućuje vam da s lakoćom unesete ove složene formule.
Ovdje je Excel radni list sa stupcima za proizvode, predstavnike prodaje i prodaju. Podaci su u ćelijama A2: C29.
Ako želite zbrajati prodaju, funkcionirat će jednostavna funkcija SUM (). =SUM(C2:C29).
Mnogi izvrsnici otkrivaju funkciju SumIf. Korištenjem ove funkcije prilično je lako dokučiti ukupnu prodaju proizvoda ABC.=SUMIF(A2:A29,E2,C2:C29)
Također je lako dokučiti ukupan promet prodavača s kojim je Joe radio =SUMIF(B2:B29,E2,C2:C29).
Tada biste pretpostavili da je moguće dokučiti ukupnu prodaju proizvoda ABC koju je proizveo Joe. Međutim, to nikako ne možete učiniti s funkcijom SumIf. Ispada da morate koristiti prilično složen niz ili CSE formulu.
Priznajmo - formula Sum je Excel 101. Formula SumIf ne zaostaje mnogo u složenosti. Međutim, CSE formula za izračun ukupne prodaje ABC-a koju je napravio Joe dovoljna je da mi se čak i u glavi zavrti.
Dobra vijest - Microsoft nudi čarobnjaka za uvjetni zbroj koji čak i početniku omogućuje unos složenih uvjetnih formula na temelju 1, 2 ili više uvjeta. Čarobnjak za uvjetni zbroj je dodatak. Da biste ovu funkciju dodali u Excel, idite na izbornik Alati i odaberite Dodaci. U dijaloškom okviru Dodaci označite potvrdni okvir pored Čarobnjak za uvjetnu sumu i odaberite U redu. Moguće je da će vam u ovom trenutku trebati vaš instalacijski CD jer Microsoft ne uključuje čarobnjaka u zadanu instalaciju.
Nakon što se dodatak uspješno uključi, pri dnu izbornika Alati pojavit će se izbor Conditonal Sum …
Odaberite jednu ćeliju u skupu podataka i odaberite Alati - uvjetni zbroj. Pod pretpostavkom da su vaši podaci lijepo oblikovani s jednim redom naslova, Excel će pravilno pogoditi raspon vaših podataka. Odaberite Dalje.
U koraku 2 odaberite stupac koji želite zbrojiti. U ovom je slučaju čarobnjak već pretpostavio da želite zbrojiti prvi (i jedini) numerički stupac - Prodaja. U sredini dijaloškog okvira nalaze se tri padajuće kontrole. Slučajno su točne za prvi uvjet - proizvod je jednak ABC, pa odaberite gumb Dodaj uvjet.
Tada možete dodati svoj drugi uvjet. U ovom slučaju želite navesti da je prodajni predstavnik Joe. Odaberite strelicu za prvi padajući izbornik. Excel nudi abecedni popis dostupnih imena stupaca. Odaberite prodajnog predstavnika
Padajući centar je točan, ali za cjelovitost ovdje možete vidjeti da ste mogli odabrati jednako, manje od, veće od, manje ili jednako, veće ili jednako, ili ne jednako.
S trećeg padajućeg izbornika odaberite Joe.
Odaberite gumb Dodaj uvjet.
Sada ste spremni za prelazak na korak 3. Pritisnite gumb Dalje.
U koraku 3 imate dva izbora. Pri prvom odabiru, čarobnjak će unijeti jednu formulu s vrijedno kodiranim vrijednostima "ABC" i "Joe". Dati će vam odgovor, ali neće biti mogućnosti da lako promijenite formulu. S drugim izborom, Excel će postaviti novu ćeliju s vrijednošću "ABC" i novu ćeliju s vrijednošću "Joe". Treća ćelija sadržavat će formulu koja daje uvjetni zbroj na temelju te dvije vrijednosti. Pomoću ove opcije možete unijeti nove vrijednosti u ćelije kako biste vidjeli ukupne XYZ-ove koje je Adam prodao.
Čarobnjak će zatim pitati gdje želite vrijednost za ABC. Odaberite ćeliju i odaberite Dalje. Ponovite dok čarobnjak traži da odaberete ćeliju za Joea i formulu.
Kad u zadnjem koraku odaberete Finish, Excel će stvoriti malo drugačiju (ali valjanu) verziju CSE formule.
Ova formula izračunava da je Joe prodao ABC 33 338 dolara.
Ako promijenite ćeliju za unos proizvoda iz ABC u DEF, formula će se ponovno izračunati kako bi pokazala da je Joe prodao 24.478 USD DEF-a.
Čarobnjak za uvjetni zbroj stavlja složene formule na dohvat ruke svim vlasnicima Excela.
Dodatne informacije: If you want to build a table that will show sales of each product by each sales rep, there is some special "care and feeding" that you will need to know about these formulas. Type each sales rep across the top of the range. Type each product down the left column of the range. Edit the formula provided by the wizard. In the image below, the formula is pointing a the product in cell E6. This reference really needs to be $E6. If you leave the reference as E6 and copy the formula to column G, the formula would look at F6 instead of E6 and this would be wrong. Adding a dollar sign before the E in E6 will make sure that the formula always looks at the product in column E. The formula is also pointing to a sales rep in cell F5. This reference really needs to be F$5. If you left the reference as F5 and copy down to row 7, the F5 reference will change to F6 and this is not right. Adding a dollar sign before the row number will lock the row number and the reference will always point to row 5.
U načinu Uredi (odaberite ćeliju i pritisnite F2 za uređivanje), upišite $ prije E. Unesite znak dolara prije 5 u F5. Još ne pritisnite Enter!
Ova je formula posebna vrsta formule. Ako pritisnete Enter, dobit ćete 0, što nije točno.
Umjesto da upišete Enter, pritisnite i držite tipke Ctrl i Shift dok pritiskate Enter. Ova čarobna kombinacija C trl + S hift + E nter je razlog zašto ove CSE formule nazivam.
Postoji posljednje razmatranje prije kopiranja formule u ostatak tablice. Vaša bi sklonost mogla biti kopiranje F6 i lijepljenje u F6: G8. Ako ovo pokušate, Excel će vam dati zagonetnu poruku "Ne možete promijeniti dio niza". Excel se žali da CSE formulu ne možete zalijepiti u raspon koji sadrži izvornu formulu CSE.
Lako je to zaobići. Kopija F6. Zalijepite u F7: F8.
Kopija F6: F8. Zalijepite u G6: G8. Imat ćete tablicu CSE formula koje pokazuju ukupne vrijednosti na temelju dva uvjeta.
