Som jeg har nævnt i mange af mine blogs, at et SUMPRODUCT er en meget alsidig funktion og kan bruges til flere formål. I denne artikel vil vi se, hvordan vi kan bruge denne funktion til at tælle værdier med flere eller kriterier.
Generisk SUMPRODUCT -formel, der skal tælles med flere eller kriterier
| = SUMPRODUCT (-(((kriterium1)+(kriterium2)+…)> 0) |
Kriterier 1: Dette er ethvert kriterium, der returnerer en matrix med SAND og FALSK.
Kriterier 2: Dette er det næste kriterium, du vil kontrollere. På samme måde kan du have så mange kriterier, du ønsker.
Ovenstående generiske formel ændres ofte for at passe til kravene til at tælle med flere OR -kriterier. Men grundformlen er denne. Først vil vi se, hvordan dette fungerer gennem et eksempel, og derefter vil vi diskutere andre scenarier, hvor du bliver nødt til at ændre denne formel lidt.
Problem:
Prisen i en celle skal opdeles præcist i 5 lignende betalinger. Når vi blot deler prisen $ 88,04 med 5 og afrunder resultatet til to decimaler, får vi individuelle betalinger på $ 17,61. Dette ville dog udgøre en samlet betaling på $ 88,05, hvilket ikke er det nøjagtige beløb, vi begynder med. Derfor vil vi beregne 5 lignende tal, der summerer til det samlede beløb.
Løsning:
Beregn først den første betaling ved at dividere prisen med 5 og afrunde resultatet.
Brug funktionen RUND i følgende formel:
= RUND (B1/5,2)
Trin 2:
Beregn hver resterende betaling.
Brug funktionerne ROUND, SUM og ROW i følgende formel:
= ROUND (($ B $ 1-SUM ($ B $ 2: B2))/(5-ROW ()+ROW ($ B $ 2)), 2)
Eksempel:
Tæl brugere, hvis forhandlerkoden matcher eller år ved hjælp af SUMPRODUCT
Så her har vi et datasæt af sælgere. Dataene indeholder mange kolonner. Hvad vi skal gøre er at tælle antallet af brugere, der har koden "INKA" eller året er "2016". Sørg for, at hvis nogen har begge (kode som "inka" og år 2016) skal tælles som 1.
Så hør, vi har to kriterier. Vi bruger ovennævnte SUMPRODUCT -formel:
| = SUMPRODUCT (-((((Kode = I3)+(År = K3))> 0)) |
Her er kode og år navngivet intervaller. Dette returnerer 7.
I dataene har vi 5 registreringer af INKA -kode og 4 registreringer af år 2016. Men 2 poster har henholdsvis "INKA" og 2016 som kode og år. Og disse poster tælles som 1. Og sådan får vi 7.
Lad os se, hvordan det fungerer?
Så lad os se på, hvordan formlen løses trin for trin, 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 dobbelte negative (-) tegn bruges til at konvertere boolske værdier til 1s og 0s. Så hver SAND værdi i arrayet konverteres til 1 og FALSK til 0.
Tilføjelse af flere eller kriterier for at tælle ved hjælp af SUMPRODUCT, så hvis du har brug for at tilføje flere eller kriterier for at tælle, kan du bare tilføje kriterier ved hjælp af + tegn til funktionen.
For eksempel, hvis du vil tilføje et andet kriterium til ovenstående formel, så det tilføjer antallet af medarbejdere, der har solgt mere end 5 produkter. SUMPRODUCT -formlen vil simpelthen se sådan ud:
| = SUMPRODUCT (-(((Kode = I3)+(År = K3)+(Salg> 5))> 0)) |
Enkel! er det ikke?
Men lad os sige, at du vil have to kriterier fra kodeområdet. Lad os sige, at du vil tælle "INKB". Så hvordan gør du dette? En metode bruger ovenstående teknik, men det ville være gentaget. Lad os sige, at jeg vil tilføje 10 flere kriterier fra samme område. I sådanne tilfælde er denne teknik ikke så smart til at tælle med SUMPRODUCT.
Lad os sige, at vi har data arrangeret sådan.
Kriterierne er i en række I2: J2. Arrangementet af data er vigtigt her. SUMPRODUCT -formlen for 3 ELLER kriterietælling vil være:
| = SUMPRODUCT (-((((Kode = I2: J2)+(År = I3: J3))> 0)) |
Dette er SUMPRODUCT -formlen til at tælle med flere kriterier, når flere kriterier fra et område skrives i række.
Ovenstående formel returnerer det korrekte svar, som er 10.
Hvis du skriver et hvilket som helst år i J3, tilføjer formlen også dette tal.
Dette bruges, når kriterierne er i en række. Vil det fungere, når kriterierne i en kolonne for det samme område? Nej. Det vil ikke.
I dette eksempel har vi flere koder at tælle, men disse typekoder er skrevet i en kolonne. Når vi bruger ovenstående SUMPRODUCT -formel, får vi ans #N/A -fejl. Vi vil ikke komme ind på, hvordan denne fejl kommer, da dette gør denne artikel for lang. Lad os se, hvordan vi kan få dette til at fungere.
For at få denne formel til at fungere, skal du pakke kodekriterierne ind i TRANSPOSE -funktionen. Dette får formlen til at fungere.
| = SUMPRODUCT (-((((Code = TRANSPOSE (H3: H4))+(Year = TRANSPOSE (I3: I4)))> 0)) |
Dette er formlen til at tælle med flere eller betingelser i det samme område, når kriterier er angivet i en kolonne.
Her er alle observationsnotater vedrørende brug af formlen.
Bemærkninger:
- Formlen fungerer både for tekst og tal.
- Opslagsværdierne kan angives som cellereference eller direkte ved hjælp af citatsymbol (") i formlen som argumenter.
Håber du forstod, hvordan du opdeler et beløb i lige store betalinger, mens du undgår division / afrundingsfejl i Excel. Udforsk flere artikler om Excel -opslagsværdi og Excel 2019 -funktioner her. Hvis du kunne lide vores blogs, kan du dele den med dine venner på Facebook. Og også du kan følge os på Twitter og Facebook. Vi vil meget gerne høre fra dig, lad os vide, hvordan vi kan forbedre, supplere eller innovere vores arbejde og gøre det bedre for dig. Skriv til os på e -mail -stedet.
Brug INDEX og MATCH til at slå op på værdi : INDEX & MATCH -funktion for at slå værdien op efter behov.
SUM -område med INDEX i Excel : Brug INDEX -funktionen til at finde SUMMEN af værdierne efter behov.
Sådan bruges INDEX -funktionen i Excel : Find INDEX for en matrix ved hjælp af INDEX -funktionen forklaret med et eksempel.
Sådan bruges MATCH -funktionen i Excel : Find MATCH i arrayet ved hjælp af INDEX -værdien inde i MATCH -funktionen forklaret med eksempel.
Sådan bruges LOOKUP -funktion i Excel : Find opslagsværdien i arrayet ved hjælp af LOOKUP -funktionen forklaret med et eksempel.
Populære artikler:
Sådan bruges IF -funktionen i Excel : IF -sætningen i Excel kontrollerer betingelsen og returnerer en bestemt værdi, hvis betingelsen er SAND, eller returnerer en anden specifik værdi, hvis FALSK.
Sådan bruges VLOOKUP -funktionen i Excel : Dette er en af de mest anvendte og populære funktioner i excel, der bruges til at slå værdi op fra forskellige områder og ark.
Sådan bruges SUMIF -funktionen i Excel : Dette er en anden vigtig instrumentbrætfunktion. Dette hjælper dig med at opsummere værdier på bestemte betingelser.
Sådan bruges COUNTIF -funktionen i Excel : Tæl værdier med betingelser ved hjælp af denne fantastiske funktion. Du behøver ikke at filtrere dine data for at tælle bestemte værdier. Countif -funktion er afgørende for at forberede dit dashboard.