I denne artikkelen lærer vi hvordan du teller rader med kriterier SUMPRODUCT i Excel.
Scenario:
I enkle ord, mens vi arbeider med data, må vi noen ganger telle cellene der to områder oppfyller kriterier. Den betingede tellingen kan gjøres ved hjelp av COUNTIF -funksjonen, men dette er ikke den eneste funksjonen som kan gjøre det. SUMPRODUCT -funksjonen er en allsidig funksjon som kan brukes til å telle med kriterier, noen ganger enda bedre enn countif -funksjonen.
Hvordan løse problemet?
For denne artikkelen må vi bruke SUMPRODUCT -funksjonen. Nå skal vi lage en formel av disse funksjonene. Her får vi to områder data, og vi må telle verdiene der formelen tilfredsstiller kriterier.
Generisk formel:
= SUMPRODUCT ( - (område1 operatør område 2))
område 1 : 1. rekke
område 2 : 2. rekke
operatør: kriterier, betingelse gitt som operatør mellom de to områdene
Eksempel:
Alt dette kan være forvirrende å forstå. Så la oss teste denne formelen ved å kjøre den på eksemplet nedenfor.
Her har vi rekorden for de siste 7 store kampene i Premier League fanklubber.
Vi har rekorden for de siste 7 kampene med hjemmelag, mot lag, hjemmelaget og motstanderen scoret mål.
For det første må vi finne antallet kamper som er vunnet på hjemmebane av ethvert lag.
Nå vil vi bruke følgende formel for å få tellingen av kamper som vant på hjemmebane.
Bruk formelen:
= SUMPRODUCT ( - (E5: E11> F5: F11))
E5: E11 : 1. rekke, hjemme scoret mål
F5: F11 : 2. rekke, motstanderen scoret mål
>: kriterier, betingelse gitt som større enn operatøren
Forklaring:
- Betingelsen her som må være tilfredsstilt er at hjemmelaget mål må være større enn motstanderens scorede mål.
- E5: E11 (område1) må være større enn F5: F11 (område2).
- -- operatør som brukes til å konvertere SANN verdi til 1 og Falske verdi til 0.
- SUMPRODUCT -funksjonen får summen av 1s i det returnerte matrisen, som vil være antallet påkrevde gevinster.
Her er området gitt som cellereferanse. Trykk Enter for å få tellingen.
Som du kan se er de totale kampene der hjemmelaget hadde fordelen og vant 4 kamper.
Eksemplet ovenfor var enkelt. Så for å gjøre det interessant vil vi telle hvor mange kamper et lag vant i hele dataene.
For det første må vi finne antallet kamper som er vunnet på hjemmebane av ethvert lag.
Nå vil vi bruke følgende formel for å få tellingen av spill, Man. United vant.
Bruk formelen:
= SUMPRODUCT ( - (C5: C11 = G5) * (E5: E11> F5: F11) + (D5: D11 = G5) * (E5: E11 <F5: F11))
Forklaring:
- (C5: C11 = G5) * (E5: E11> F5: F11) sjekker hjemmelaget som mann. United og scoret flere mål enn motstanderlaget.
- (D5: D11 = G5) * (E5: E11 <F5: F11) sjekker motlaget som mann. United og scoret flere mål enn hjemmelaget.
- SUMPRODUCT -funksjonen får summen av 1s i det returnerte matrisen, som vil være antallet påkrevde gevinster av Man. Forent.
Her er området gitt som cellereferanse. Trykk Enter for å få tellingen.
Som du kan se totalt antall kamper der mannen. United -laget vant 2 kamper.
Her er noen observasjonsnotater vist nedenfor.
Merknader:
- Formelen fungerer bare med tall.
- Formelen fungerer bare når det ikke er dubletter på oppslagstabellen
- SUMPRODUCT-funksjonen anser ikke-numeriske verdier som 0s.
- SUMPRODUCT -funksjonen anser logisk verdi TRUE som 1 og Falsk som 0.
- Argumentmatrisen må ha samme lengde som funksjonen
Håper denne artikkelen om hvordan du teller tilbake hvis med SUMPRODUCT i Excel er forklarende. Finn flere artikler om SUMPRODUCT -funksjoner her. Vennligst del spørringen nedenfor i kommentarfeltet. Vi hjelper deg.
Slik bruker du SUMPRODUCT -funksjonen i Excel: Returnerer SUMMEN etter multiplikasjon av verdier i flere matriser i excel.
TELLER med Dynamic Criteria Range : Tell celler avhengig av andre celleverdier i Excel.
TELLER To kriterier: Telle celler som matcher to forskjellige kriterier på listen i excel.
TELLER MED ELLER For flere kriterier : Tell celler som har flere kriterier som samsvarer med OR -funksjonen.
COUNTIFS -funksjonen i Excel : Tell celler avhengig av andre celleverdier.
Hvordan bruke Countif i VBA i Microsoft Excel : Tell celler ved hjelp av Visual Basic for Applications -kode.
Hvordan bruke jokertegn i excel : Tell celler som matcher setninger ved hjelp av jokertegnene i excel
Populære artikler
50 Excel -snarveier for å øke produktiviteten
Rediger en rullegardinliste
Absolutt referanse i Excel
Hvis det er betinget formatering
Hvis det er jokertegn
Vlookup etter dato
Konverter tommer til fot og tommer i Excel 2016
Bli med for- og etternavn i excel
Telle celler som matcher enten A eller B.