Et av de vanligste spørsmålene på Meldingstavlen er hvordan du bruker SumIf-funksjonen med to forskjellige forhold. Dessverre er svaret at SumIf ikke kan takle to forskjellige forhold.
For å gjøre to forhold, må du bruke en ganske komplisert matriseformel. Tillegget for veiviseren for betinget sum lar deg enkelt skrive inn disse kompliserte formlene.
Her er et Excel-regneark med kolonner for produkt, selger og salg. Dataene er i cellene A2: C29.
Hvis du vil totalsalg, vil en enkel SUM () -funksjon fungere. =SUM(C2:C29).
Mange Excellers oppdager SumIf-funksjonen. Ved å bruke denne funksjonen er det ganske enkelt å finne ut omsetningen for produktet ABC.=SUMIF(A2:A29,E2,C2:C29)
Det er også enkelt å finne ut hvor mye salg som selger Joe har gjort =SUMIF(B2:B29,E2,C2:C29).
Du antar da at det er mulig å finne ut det totale salget av produktet ABC laget av Joe. Det er imidlertid ingen måte å gjøre dette med SumIf-funksjonen. Det viser seg at du må bruke en ganske kompleks matrise eller CSE-formel.
La oss innse det - Sum-formelen er Excel 101. SumIf-formelen er ikke langt etter i kompleksitet. Imidlertid er CSE-formelen for å beregne totalt ABC-salg laget av Joe nok til å få hodet mitt til å snurre.
Den gode nyheten - Microsoft tilbyr veiviseren for betinget sum som lar selv en nybegynner skrive inn komplekse betingede formler basert på 1, 2 eller flere forhold. Veiviseren for betinget sum er et tillegg. For å legge til denne funksjonaliteten i Excel, gå til Verktøy-menyen og velg Tillegg. Velg avmerkingsboksen ved siden av Veiviser for betinget sum i dialogboksen Tillegg, og velg OK. Det er mulig at du kanskje trenger installasjons-CDen din på dette tidspunktet, fordi Microsoft ikke inkluderer veiviseren i standardinstallasjonen.
Når tillegget er slått på vellykket, vil det være et betinget sum … valg nederst i Verktøy-menyen.
Velg en enkelt celle i datasettet og velg Verktøy - Betinget sum. Forutsatt at dataene dine er pent formatert med en enkelt rad med overskrifter, vil Excel gjette riktig på dataområdet. Velg Neste.
I trinn 2 velger du kolonnen som skal summeres. I dette tilfellet gjettet veiviseren allerede at du vil oppsummere den første (og eneste) numeriske kolonnen - Salg. Midt i dialogboksen er det tre rullegardinkontroller. Disse er tilfeldigvis korrekte for den første tilstanden - Produktet er lik ABC, så velg knappen Legg til betingelse.
Deretter kan du legge til den andre tilstanden din. I dette tilfellet vil du spesifisere at salgsrepresentanten er Joe. Velg pilen for den første rullegardinmenyen. Excel tilbyr en alfabetisk liste over tilgjengelige kolonnenavn. Velg salgsrepresentant.
Midtmenyen er riktig, men for fullstendighet her kan du se at du kunne ha valgt lik, mindre enn, større enn, mindre enn eller lik, større enn eller lik, eller ikke lik.
Velg Joe fra den tredje rullegardinmenyen.
Velg knappen Legg til betingelse.
Du er nå klar til å gå til trinn 3. Trykk på Neste-knappen.
I trinn 3 har du to valg. Ved førstevalget vil veiviseren legge inn en enkelt formel med verdiene "ABC" og "Joe" hardkodet i formelen. Det vil gi deg svaret, men det vil ikke være noen mulighet til å enkelt endre formelen. Med andrevalget vil Excel sette opp en ny celle med verdien "ABC" og en ny celle med verdien "Joe". En tredje celle inneholder formelen som gjør en betinget sum basert på disse to verdiene. Med dette alternativet kan du skrive inn nye verdier i cellene for å se de totale XYZ-ene som Adam selger.
Veiviseren vil da spørre hvor du vil ha verdien for ABC. Velg en celle og velg Neste. Gjenta når veiviseren ber deg velge en celle for Joe og formelen.
Når du velger Fullfør i det siste trinnet, oppretter Excel en litt annen (men gyldig) versjon av CSE-formelen.
Denne formelen beregner at Joe solgte $ 33,338 av ABC.
Hvis du endrer produktinndatacellen fra ABC til DEF, beregnes formelen på nytt for å vise at Joe solgte $ 24.478 i DEF.
Veiviseren for betinget sum setter komplekse formler godt innen rekkevidde for alle Excel-eiere.
Tilleggsinformasjon:Hvis du vil lage en tabell som viser salg av hvert produkt av hver salgsrepresentant, er det noen spesielle "pleie og fôring" som du trenger å vite om disse formlene. Skriv hver salgsrepresentant øverst i området. Skriv hvert produkt ned i venstre kolonne i området. Rediger formelen fra veiviseren. I bildet nedenfor peker formelen på produktet i celle E6. Denne referansen må virkelig være $ E6. Hvis du lar referansen være E6 og kopierer formelen til kolonne G, vil formelen se på F6 i stedet for E6, og dette vil være feil. Å legge til et dollartegn før E i E6 vil sørge for at formelen alltid ser på produktet i kolonne E. Formelen peker også på en selger i celle F5. Denne referansen må virkelig være F $ 5. Hvis du la referansen være F5 og kopierte ned til rad 7,F5-referansen vil endres til F6, og dette er ikke riktig. Hvis du legger til et dollartegn før radnummeret, låses radnummeret, og referansen vil alltid peke på rad 5.
I redigeringsmodus (velg cellen og trykk F2 for å redigere), skriv inn $ før E. Skriv inn et dollartegn før 5 i F5. Ikke trykk Enter ennå!
Denne formelen er en spesiell formel. Hvis du trykker Enter, får du et 0, som ikke er riktig.
I stedet for å skrive Enter, hold nede Ctrl og Shift-tastene mens du trykker Enter. Denne magiske kombinasjonen av C trl + S hift + E nter er grunnen til at jeg kaller disse CSE-formlene.
Det er en siste vurdering før du kopierer formelen til resten av tabellen. Din tilbøyelighet kan være å kopiere F6 og lime inn på F6: G8. Hvis du prøver dette, vil Excel gi deg den forvirrende meldingen "Du kan ikke endre en del av en matrise". Excel klager over at du ikke kan lime inn en CSE-formel i et område som inneholder den opprinnelige CSE-formelen.
Det er lett å omgå dette. Kopier F6. Lim inn til F7: F8.
Kopier F6: F8. Lim inn til G6: G8. Du vil ha en tabell med CSE-formler som viser totaler basert på to forhold.
