Som jeg har nevnt i mange av bloggene mine at et SUMPRODUCT er en veldig allsidig funksjon og kan brukes til flere formål. I denne artikkelen vil vi se hvordan vi kan bruke denne funksjonen til å telle verdier med flere eller kriterier.
Generisk SUMPRODUCT Formula å telle med flere eller kriterier
| = SUMPRODUCT (-(((kriterium1)+(kriterier2)+…)> 0) |
Kriterier 1: Dette er alle kriterier som returnerer en rekke SANN og FALSK.
Kriterier 2: Dette er det neste kriteriet du vil kontrollere. På samme måte kan du ha så mange kriterier du vil.
Den generiske formelen ovenfor er ofte modifisert for å passe kravene for å telle med flere ELLER -kriterier. Men grunnformelen er denne. Først skal vi se hvordan dette fungerer gjennom et eksempel, og etter det vil vi diskutere andre scenarier der du må endre denne formelen litt.
Problem:
Prisen i en celle bør deles nøyaktig i 5 lignende betalinger. Når vi bare deler prisen $ 88,04 med 5 og avrunder resultatet til to desimaler, får vi individuelle betalinger på $ 17,61. Dette vil imidlertid utgjøre en total betaling på $ 88,05, som ikke er det eksakte beløpet vi begynner med. Derfor ønsker vi å beregne 5 lignende tall som summerer seg til det totale beløpet.
Løsning:
Beregn først den første betalingen ved å dele prisen med 5 og avrunde resultatet.
Bruk RUND -funksjonen i følgende formel:
= RUND (B1/5,2)
Steg 2:
Beregn hver gjenværende betaling.
Bruk funksjonene RUND, SUMMER og RAD i følgende formel:
= RUND (($ B $ 1-SUM ($ B $ 2: B2))/(5-RAD ()+RAD ($ B $ 2)), 2)
Eksempel:
Tell brukere hvis forhandlerkoden samsvarer eller år ved bruk av SUMPRODUCT
Så her har vi et datasett med selgere. Dataene inneholder mange kolonner. Det vi må gjøre er å telle antall brukere som har koden "INKA" eller året er "2016". Sørg for at hvis noen har begge (koden som "inka" og år 2016) skal regnes som 1.
Så hør vi har to kriterier. Vi bruker ovennevnte SUMPRODUCT -formel:
| = SUMPRODUCT (-(((Kode = I3)+(År = K3))> 0)) |
Her er kode og år navngitt områder. Dette gir 7.
I dataene har vi 5 poster med INKA -kode og 4 poster fra 2016. Men 2 poster har henholdsvis "INKA" og 2016 som kode og år. Og disse postene regnes som 1. Og slik får vi 7.
La oss se hvordan det fungerer?
Så la oss se på hvordan formelen løses trinnvis, så vil jeg diskutere hvordan den fungerer.
-
- = SUMPRODUCT(-((((Kode = I3)+(År = K3))> 0))
- SUMPRODUKT(-(({TRUE; FALSE; TRUE; TRUE; TRUE;…}+{FALSE; FALSE; FALSE; TRUE;…})> 0))
- SUMPRODUKT(--(({1;0;1;2;2;1;1;1;0;0;0;0;0;0;0;0;0;0;0;0})>0))
- SUMPRODUKT(-({TRUE; FALSE; TRUE; TRUE; TRUE; TRUE; TRUE;…})
- SUMPRODUKT({1;0;1;1;1;1;1;1;0;0;0;0;0;0;0;0;0;0;0;0})
7
De doble negative (-) tegnene brukes til å konvertere boolske verdier til 1s og 0s. Så hver SANN verdi i matrisen blir konvertert til 1 og FALSK til 0.
Legge til flere eller kriterier for å telle ved hjelp av SUMPRODUCT, så hvis du trenger å legge til flere eller kriterier å telle, kan du bare legge til kriterier ved å bruke + -tegnet til funksjonen.
For eksempel, hvis du vil legge til et annet kriterium i formelen ovenfor, slik at det legger til antall ansatte som har solgt mer enn 5 produkter. SUMPRODUCT -formelen vil ganske enkelt se slik ut:
| = SUMPRODUCT (-(((Kode = I3)+(År = K3)+(Salg> 5))> 0)) |
Enkel! ikke sant?
Men la oss si at du vil ha to kriterier fra kodeområdet. La oss si at du vil telle "INKB". Så hvordan gjør du dette? En metode er å bruke teknikken ovenfor, men det vil være repeterende. La oss si at jeg vil legge til 10 flere kriterier fra samme område. I slike tilfeller er denne teknikken ikke så smart å telle med SUMPRODUCT.
La oss si at vi har ordnet data slik.
Kriteriene er i en rad I2: J2. Ordningen av data er viktig her. SUMPRODUCT -formelen for innstilling av 3 ELLER kriterier vil være:
| = SUMPRODUCT (-((((Kode = I2: J2)+(År = I3: J3))> 0)) |
Dette er SUMPRODUCT -formelen for å telle med flere kriterier når flere kriterier fra ett område skrives på rad.
Formelen ovenfor returnerer det riktige svaret som er 10.
Hvis du skriver et år i J3, vil formelen også legge til det tallet.
Dette brukes når kriteriene er på en rad. Vil det fungere når kriteriene i en kolonne for samme område? Nei. Det vil ikke.
I dette eksemplet har vi flere koder å telle, men disse typekodene er skrevet i en kolonne. Når vi bruker ovenstående SUMPRODUCT -formel, får vi en #N/A -feil. Vi vil ikke komme inn på hvordan denne feilen kommer da dette gjør denne artikkelen for lang. La oss se hvordan vi kan få dette til å fungere.
For å få denne formelen til å fungere, må du pakke inn kodekriteriene i TRANSPOSE -funksjonen. Dette vil få formelen til å fungere.
| = SUMPRODUCT (-((((Code = TRANSPOSE (H3: H4))+(Year = TRANSPOSE (I3: I4)))> 0)) |
Dette er formelen for å telle med flere eller betingelser i samme område når kriterier er oppført i en kolonne.
Her er alle observasjonsnotatene om bruk av formelen.
Merknader:
- Formelen fungerer både for tekst og tall.
- Oppslagsverdiene kan gis som cellereferanse eller direkte ved hjelp av sitatsymbol (") i formelen som argumenter.
Håper du forsto hvordan du deler et beløp i like betalinger mens du unngår divisjon / avrundingsfeil i Excel. Utforsk flere artikler om Excel -oppslagsverdi og Excel 2019 -funksjoner her. Hvis du likte bloggene våre, del den med vennene dine på Facebook. Og du kan også følge oss på Twitter og Facebook. Vi vil gjerne høre fra deg, gi oss beskjed om hvordan vi kan forbedre, utfylle eller innovere arbeidet vårt og gjøre det bedre for deg. Skriv til oss på e -post.
Bruk INDEX og MATCH til å slå opp verdi : INDEX & MATCH -funksjon for å slå opp verdien etter behov.
SUM -område med INDEX i Excel : Bruk INDEX -funksjonen for å finne SUMMEN av verdiene etter behov.
Slik bruker du INDEX -funksjonen i Excel : Finn INDEX for en matrise ved å bruke INDEX -funksjonen forklart med et eksempel.
Slik bruker du MATCH -funksjonen i Excel : Finn MATCH i matrisen ved å bruke INDEX -verdien inne i MATCH -funksjonen forklart med eksempel.
Hvordan bruke LOOKUP -funksjonen i Excel : Finn oppslagsverdien i matrisen ved å bruke LOOKUP -funksjonen forklart med et eksempel.
Populære artikler:
Slik bruker du IF -funksjonen i Excel : IF -setningen i Excel sjekker tilstanden og returnerer en bestemt verdi hvis betingelsen er SANN eller returnerer en annen spesifikk verdi hvis FALSE.
Slik bruker du VLOOKUP -funksjonen i Excel : Dette er en av de mest brukte og populære funksjonene i excel som brukes til å slå opp verdi fra forskjellige områder og ark.
Slik bruker du SUMIF -funksjonen i Excel : Dette er en annen viktig funksjon i instrumentbordet. Dette hjelper deg med å oppsummere verdier på spesifikke forhold.
Slik bruker du COUNTIF -funksjonen i Excel : Tell verdier med betingelser ved hjelp av denne fantastiske funksjonen. Du trenger ikke å filtrere dataene dine for å telle spesifikke verdier. Countif -funksjonen er avgjørende for å forberede dashbordet.