Excel: Beregn betingede summer korrekt
I Excel kan du beregne summer som ikke tar hensyn til alle elementene i det valgte området - såkalte betingede summer. Vi viser deg hvordan du gjør den mest fornuftige beregningen.
Tre forskjellige funksjoner for betingede summer i Excel
Excel tilbyr forskjellige alternativer og funksjoner for betingede totaler. Riktig valg avhenger av type forhold:
- Hvis en fast betingelse gjelder for hver verdi det gjelder, bruk SUMIF-funksjonen.
- Hvis det må tas hensyn til flere forhold, er det også SUMIFS-funksjonen fra Excel 2007.
- Under variable forhold trenger du imidlertid en matriksformel eller funksjonen SUM PRODUCT.
- I de følgende avsnitt forklarer vi de tre funksjonene ved å bruke eksempler.
SUMIF: Betingede summer med faste betingelser
- Anta at dataene er i cellene A1 til A10 og forskjellige verdier i cellene B1 til C10 som brukes til forhold.
- Den første oppgaven med en fast tilstand betyr da for eksempel å oppsummere alle verdiene i kolonne A som den respektive verdien i kolonne B er omtrent større enn 10.
- For å gjøre dette, bruk formelen »= SUMIF (B1: B10;"> 10 "; A1: A10)«.
- Det første argumentet »B1: B10« definerer området som skal evalueres, etterfulgt av betingelsen »"> 10 "«. Merk at forholdene med symboler eller operatører må være i anførselstegn.
- Til slutt definerer »A1: A10« området som skal summeres.
- Områdene for forhold og datoer kan også være identiske, slik at formelen »= SUMIF (A1: A10;"> 10 ")« er tilstrekkelig.
SUM IF: Betingede summer med flere forhold
Fra Excel 2007 utvider SUMIFS-funksjonen dette prinsippet til flere til maksimalt 127 forhold:
- Legg merke til den andre rekkefølgen på argumentene. Området som skal summeres er her først. Dette blir fulgt av et kriterieområde og tilhørende tilstand, der områdene alltid må spesifiseres separat.
- For eksempel oppsummerer formelen "= SUMIFS (A1: A10; B1: B10;"> 10 "; C1: C10;" <100 ")« alle verdier i kolonne A som de tilsvarende verdiene i kolonne B er større enn 10 og de tilsvarende verdiene i kolonne C er mindre enn 100.
SUMPRODUKT: Betinget sum med variable forhold
Variable kriterier som "hvis verdien i kolonne B er større enn den i kolonne C" krever en matriksformel for evaluering:
- Verdiene som skal tas i betraktning velges ved å multiplisere med en logisk verdi.
- For å gjøre dette, skriv om "= SUM (A1: A10 * (B1: B10> C1: C10))" og lukk alltid oppføringen, selv etter en endring med tastekombinasjonen [Ctrl] + [Shift] + [Enter], slik at formelen er i seler.
- I dette tilfellet kan du oppnå det samme resultatet med funksjonen "= SUMPRODUKT (A1: A10; 1 * (B1: B10> C1: C10))".
- Du må imidlertid merke deg at du først må multiplisere det andre argumentet med 1 for å konvertere sammenligningens logiske verdi til et tall.
Merk: I praktisk talt alle tilfeller kan du tydelig opprette totalen med en hjelpekolonne. Dette anbefales også under utviklingen av en beregning for å sjekke resultatene av de komplekse formlene. Tips: Vi vil forklare hvordan du legger til mange celler i Excel her.