Excel: Beregn betingede summer korrekt
I Excel kan du beregne summer, der ikke tager højde for alle elementer i det valgte område - såkaldte betingede summer. Vi viser dig, hvordan du foretager den mest fornuftige beregning.
Tre forskellige funktioner til betingede summer i Excel
Excel tilbyder forskellige muligheder og funktioner til betingede totaler. Det rigtige valg afhænger af typen betingelser:
- Hvis en fast betingelse gælder for hver værdi, skal du bruge SUMIF-funktionen.
- Hvis der skal tages flere forhold i betragtning, er der også SUMIFS-funktionen fra Excel 2007.
- Under variable betingelser har du dog brug for en matrixformel eller funktionen SUM PRODUCT.
- I de følgende afsnit forklarer vi de tre funktioner ved hjælp af eksempler.
SUMIF: Betingede summer med faste betingelser
- Antag, at dataene er i cellerne A1 til A10 og forskellige værdier i cellerne B1 til C10, der bruges til betingelser.
- Den første opgave med en fast betingelse betyder derefter for eksempel at opsummere alle værdierne i kolonne A, for hvilke den respektive værdi i kolonne B er omtrent større end 10.
- For at gøre dette skal du bruge formlen »= SUMIF (B1: B10;"> 10 "; A1: A10)«.
- Det første argument »B1: B10« definerer det område, der skal evalueres, efterfulgt af betingelsen »"> 10 "«. Bemærk, at forholdene med symboler eller operatører skal være i anførselstegn.
- Endelig definerer »A1: A10« det område, der skal summeres.
- Områderne for betingelser og datoer kan også være identiske, så formlen »= SUMIF (A1: A10;"> 10 ")« er tilstrækkelig.
SOM IF: Betingede summer med flere betingelser
Fra Excel 2007 udvider SUMIFS-funktionen dette princip til flere til maksimalt 127 betingelser:
- Bemærk den anden rækkefølge af argumenterne. Det område, der skal summeres, er her først. Dette efterfølges af et kriterieområde og den tilhørende tilstand, hvor områdene altid skal specificeres separat.
- For eksempel summerer formlen »= SUMIFS (A1: A10; B1: B10;"> 10 "; C1: C10;" <100 ")« alle værdier i kolonne A, for hvilke de tilsvarende værdier i kolonne B er større end 10 og de tilsvarende værdier i kolonne C er mindre end 100.
SUMPRODUKT: Betinget sum med variable betingelser
Variable kriterier som "hvis værdien i kolonne B er større end den i kolonne C" kræver en matrixformel til evaluering:
- De værdier, der skal tages i betragtning, vælges ved at multiplicere med en logisk værdi.
- For at gøre dette skal du indtaste omkring "= SUM (A1: A10 * (B1: B10> C1: C10))" og lukke altid posten, selv efter en ændring med tastkombinationen [Ctrl] + [Shift] + [Enter], så formlen er i seler.
- I dette tilfælde kan du opnå det samme resultat med funktionen "= SUMPRODUKT (A1: A10; 1 * (B1: B10> C1: C10))".
- Du skal dog bemærke, at du først skal multiplicere det andet argument med 1 for at konvertere sammenligningens logiske værdi til et tal.
Bemærk: I praktisk talt alle tilfælde kan du tydeligt oprette summen med en hjælpekolonne. Dette tilrådes også under udviklingen af en beregning for at kontrollere resultaterne af de komplekse formler. Tip: Vi vil forklare, hvordan du tilføjer mange celler i Excel her.