Eno najpogostejših vprašanj na oglasni deski je, kako uporabljati funkcijo SumIf v dveh različnih pogojih. Žal je odgovor ta, da SumIf ne zmore dveh različnih pogojev.
Če želite narediti dva pogoja, morate uporabiti precej zapleteno formulo matrike. Dodatek čarovnika za pogojno vsoto vam omogoča enostavno vnašanje teh zapletenih formul.
Tu je Excelov delovni list s stolpci za izdelke, prodajnega predstavnika in prodajo. Podatki so v celicah A2: C29.
Če želite sešteti prodajo, bo delovala preprosta funkcija SUM (). =SUM(C2:C29).
Številni odličniki odkrijejo funkcijo SumIf. Z uporabo te funkcije je dokaj enostavno ugotoviti skupno prodajo izdelka ABC.=SUMIF(A2:A29,E2,C2:C29)
Prav tako je enostavno ugotoviti vsoto prodaje, ki jo je opravil prodajalec Joe =SUMIF(B2:B29,E2,C2:C29).
Nato bi domnevali, da je mogoče ugotoviti skupno prodajo izdelka ABC, ki ga je izdelal Joe. Vendar s funkcijo SumIf tega nikakor ne moremo storiti. Izkazalo se je, da morate uporabiti dokaj zapleteno matriko ali formulo iskalnika po meri.
Priznajmo si - formula Sum je Excel 101. Formula SumIf po zapletenosti ne zaostaja veliko. Vendar je formula CSE za izračun celotne prodaje ABC, ki jo je opravil Joe, dovolj, da se mi celo zavrti v glavi.
Dobra novica - Microsoft ponuja čarovnika za pogojno vsoto, ki celo začetniku omogoča vnos zapletenih pogojnih formul na podlagi 1, 2 ali več pogojev. Čarovnik za pogojno vsoto je dodatek. Če želite to funkcijo dodati v Excel, pojdite v meni Orodja in izberite Dodatki. V pogovornem oknu Add-Ins izberite potrditveno polje poleg Čarovnik za pogojno vsoto in izberite V redu. Možno je, da boste na tem mestu morda potrebovali namestitveni CD, ker Microsoft čarovnika ne vključi v privzeto namestitev.
Ko je dodatek uspešno vklopljen, bo na dnu menija Orodja prikazana možnost Conditonal Sum….
Izberite eno celico v naboru podatkov in izberite Orodja - pogojna vsota. Ob predpostavki, da so vaši podatki lepo oblikovani z eno vrstico naslovov, bo Excel pravilno uganil obseg vaših podatkov. Izberite Naprej.
V 2. koraku izberite stolpec za vsoto. V tem primeru je čarovnik že uganil, da želite sešteti prvi (in edini) številski stolpec - Prodaja. Na sredini pogovornega okna so trije spustni kontrolniki. Te so za prvi pogoj pravilne - izdelek je enak ABC, zato izberite gumb Dodaj pogoj.
Nato lahko dodate svoj drugi pogoj. V tem primeru želite določiti, da je prodajalec Joe. Izberite puščico za prvi spustni meni. Excel ponuja abecedni seznam razpoložljivih imen stolpcev. Izberite prodajnega predstavnika
Sredinski spustni seznam je pravilen, toda za popolnost tukaj lahko vidite, da bi lahko izbrali enako, manj kot, večje od, manj kot ali enako, večje ali enako ali ne enako.
V tretjem spustnem meniju izberite Joe.
Izberite gumb Dodaj pogoj.
Zdaj ste pripravljeni na 3. korak. Pritisnite gumb Naprej.
V 3. koraku imate dve možnosti. Pri prvi izbiri bo čarovnik v formulo vnesel eno samo formulo z vrednostmi "ABC" in "Joe". Dala vam bo odgovor, vendar ne bo možnosti za enostavno spreminjanje formule. Z drugo izbiro bo Excel postavil novo celico z vrednostjo "ABC" in novo celico z vrednostjo "Joe". Tretja celica bo vsebovala formulo, ki na podlagi teh dveh vrednosti naredi pogojno vsoto. S to možnostjo lahko v celice vtipkate nove vrednosti, da vidite celotne vrednosti XYZ, ki jih je prodal Adam.
Čarovnik vas bo nato vprašal, kje želite vrednost za ABC. Izberite celico in izberite Naprej. Ponovite, ko vas čarovnik prosi, da izberete celico za Joeja in formulo.
Ko v zadnjem koraku izberete Dokončaj, bo Excel ustvaril nekoliko drugačno (vendar veljavno) različico formule iskalnika po meri.
Ta formula izračuna, da je Joe prodal 33.338 ameriških dolarjev ABC.
Če spremenite vhodno celico izdelka iz ABC v DEF, bo formula znova izračunana, da bo Joe prodal 24.478 USD DEF.
Čarovnik za pogojno vsoto daje zapletene formule v doseg vsem lastnikom 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.
V načinu za urejanje (izberite celico in pritisnite F2 za urejanje) vnesite znak $ pred E. Vnesite znak za dolar pred 5 v F5. Ne pritisnite še Enter!
Ta formula je posebna vrsta formule. Če pritisnete Enter, boste dobili 0, kar ni pravilno.
Namesto da vtipkate Enter, medtem ko pritisnete Enter, držite tipki Ctrl in Shift. Ta čarobna kombinacija C trl + S hift + E nter je razlog, zakaj te formule imenujem CSE.
Pred kopiranjem formule v preostalo tabelo je še zadnji premislek. Vaš nagib je lahko kopiranje F6 in lepljenje v F6: G8. Če poskusite to, vam Excel prikaže zmedeno sporočilo »Dela polja ne moreš spremeniti«. Excel se pritožuje, da formule iskalnika po meri ne morete prilepiti v obseg, ki vsebuje prvotno formulo iskalnika po meri.
Tega je enostavno zaobiti. Kopija F6. Prilepite v F7: F8.
Kopija F6: F8. Prilepite v G6: G8. Imeli boste tabelo formul iskalnikov po meri, ki prikazuje skupne vrednosti na podlagi dveh pogojev.
