01 av 02
Sumceller som faller mellom to verdier
Lifewire
SUMPRODUCT-funksjonen i Excel er en svært allsidig funksjon som vil gi ulike resultater avhengig av måten funksjonens argumenter legges inn på.
Vanligvis, som navnet antyder, multipliserer SUMPRODUCT elementene i en eller flere arrays for å få produktet deres, og legger deretter til eller summerer produktene sammen.
Ved å justere funksjonens syntaks kan den imidlertid brukes til å summere bare dataene i celler som oppfyller spesifikke kriterier.
Siden Excel 2007 har programmet inneholdt to funksjoner - SUMIF og SUMIFS - som vil summere data i celler som oppfyller ett eller flere angitte kriterier.
Til tider er SUMPRODUCT imidlertid enklere å jobbe med når det gjelder å finne flere forhold knyttet til samme rekkevidde som vist på bildet ovenfor.
SUMPRODUCT Funksjonssyntaks for å summere celler
Syntaksen som brukes for å få SUMPRODUCT til å summere data i celler som oppfyller spesifikke betingelser er:
=SUMPRODUKT([tilstand1][tilstand2][array])
condition1, condition2 - betingelsene som må oppfylles før funksjonen finner produktet av matrisen.
array – et sammenhengende celleområde
Eksempel: summering av data i celler som oppfyller flere betingelser
Eksemplet i bildet ovenfor legger til dataene i celler i området D1 til E6 som er mellom 25 og 75.
Åpne SUMPRODUCT-funksjonen
Fordi dette eksemplet bruker en uregelmessig form av SUMPRODUCT-funksjonen, kan ikke funksjonens dialogboks brukes til å angi funksjonen og dens argumenter. I stedet må funksjonen skrives inn manuelt i en regnearkcelle.
- Klikk på celle B7 i regnearket for å gjøre den til den aktive cellen;
- Skriv inn følgende formel i celle B7: =SUMPRODUCT(($A$2:$B$6>25)($A$2:$B$6<75)(A2:B6))
- Svaret 250 skal vises i celle B7
- Svaret ble oppnådd ved å legge til de fem tallene i området (40, 45, 50, 55 og 60) som er mellom 25 og 75. Summen av disse er 250
Breaking Down the SUMPRODUCT Formula
Når betingelser brukes for sine argumenter, evaluerer SUMPRODUCT hvert matriseelement mot betingelsen og returnerer en boolsk verdi (TRUE eller FALSE).
For beregningsformål tildeler Excel en verdi på 1 for de matriseelementene som er TRUE (oppfyller betingelsen) og en verdi på 0for matriseelementer som er FALSE (oppfyller ikke betingelsen).
For eksempel tallet 40:
- er TRUE for den første betingelsen, så en verdi på 1 er tilordnet i den første matrisen;
- er TRUE for den andre betingelsen, så en verdi på 1 er tilordnet i den andre matrisen.
Tallet 15:
- er FALSE for den første betingelsen, så en verdi på 0 er tilordnet i den første matrisen;
- er TRUE for den andre betingelsen, så en verdi på 1 er tilordnet i den andre matrisen.
De tilsvarende enerne og nullene i hver matrise multipliseres med hverandre:
- For tallet 40 - vi har 1 x 1 som returnerer en verdi på 1;
- For tallet 15 – vi har 0 x 1 som returnerer en verdi på 0.
Multiplikering av enere og nuller med rekkevidden
Disse enerne og nullene multipliseres deretter med tallene i området A2: B6
Dette er gjort for å gi oss tallene som vil summeres av funksjonen.
Dette fungerer fordi:
- 1 ganger et tall er lik det opprinnelige tallet
- 0 ganger et tall er lik 0
Så vi ender opp med:
-
140=40
015=0
022=0
145=45
150=50
155=55
025=0
075=0
160=600100=0
Opsummering av resultatene
SUMPRODUCT oppsummerer deretter resultatene ovenfor for å finne svaret.
40 + 0 + 0 + 45 + 50 + 55 + 0 + 0 + 60 + 0=250
