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 (-((((kriterier 1)+(kriterier 2)+… )>0) |
Kriterier 1: Dette er ethvert kriterium, der returnerer en matrix med SAND og FALSK.
Kriterier2: Dette er det næste kriterium, du vil kontrollere. På samme måde kan du have så mange kriterier, du vil.
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.
Eksempel: Tæl brugere, hvis forhandlerkode eller årTændstikker Brug 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 det tælles som 1.
Så her har vi 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. Disse rekorder tælles som 1. Og sådan får vi 7.
Hvordan virker det?
Så lad os se på, hvordan formlen løses trin for trin, så vil jeg diskutere, hvordan den fungerer.
| =SUMPRODUKT(-((((Kode = I3)+(År = K3))> 0)) |
| 1=>SUMPRODUKT(-(({TRUE; FALSE; TRUE; TRUE; TRUE;…}+{FALSE; FALSE; FALSE; TRUE;…})> 0)) |
| 2=>SUMPRODUKT(--(({1;0;1;2;2;1;1;1;0;0;0;0;0;0;0;0;0;0;0;0})>0)) |
| 3=>SUMPRODUKT(-({TRUE; FALSE; TRUE; TRUE; TRUE; TRUE; TRUE;…}) |
| 4=>SUMPRODUKT({1;0;1;1;1;1;1;1;0;0;0;0;0;0;0;0;0;0;0;0}) |
| 5=>7 |
I det første trin sammenlignes værdien af I3 ("INKA") med hver celle i kodeområdet. Dette returnerer en matrix med SAND og FALSK. SAND for hver kamp. For at spare plads har jeg ikke vist alle TRUE-FALSE. På samme måde matches værdien af K3 (2016) med hver celle i årsområdet.
I det næste trin tilføjer vi disse to arrays, der resulterer i en ny række numeriske værdier. Som du måske ved, behandles SAND som 1 og FALSK som 0 i Excel. Så når SAND og SAND tilføjes, får vi 2 og resten kan du forstå.
I det næste trin kontrollerer vi, hvilken værdi der er større end 0 i arrayet. Dette konverterer igen arrayet til et sandt falsk array. For hver 0 -værdi, vi får, konverteres False og rest som sandt. Nu er antallet af SANDE værdier i arrayet vores svar. Men hvordan tæller vi dem? Sådan gør du.
Dobbelt negative (-) tegn bruges til at konvertere boolske værdier til 1'er og 0'er. Så hver SAND værdi i arrayet konverteres til 1 og FALSK til 0.
I det sidste trin opsummerer SUMPRODUCT dette array, og vi får vores svar som 7.
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 Kode rækkevidde. 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ællingsindstillinger 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 træk.
Dette 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 er 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 opstod, da dette vil gøre 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.
Så ja kammerat, jeg håber jeg var klar nok, og det gav mening. Jeg håber, det tjener dit formål med at være her. Hvis denne formel ikke løste dit problem, skal du fortælle mig dine krav i kommentarfeltet herunder. Jeg vil mere end gerne hjælpe dig på nogen måde. Du kan nævne enhver tvivlsrelateret Excel/VBA. Indtil da fortsæt med at lære, bliv ved med at udmærke sig.
Sådan bruges funktionen SUMPRODUCT i Excel: Returnerer SUMMEN efter multiplikation af værdier i flere arrays i excel. Denne funktion kan bruges til at udføre flere opgaver. Dette er en af de mest alsidige funktioner.
COUNTIFS med Dynamic Criteria Range : For at tælle med dynamiske kriterier, bruger vi simpelthen INDIRECT -funktionen. Denne funktion kan
TÆLLER MED ELLER Til flere kriterier : Tæl celler, der har flere kriterier, der matcher ved hjælp af OR -funktionen. For at sætte en OR -logik i COUNTIFS -funktionen behøver du ikke bruge OR -funktionen.
Brug af IF med AND / OR -funktioner i Microsoft Excel : Disse logiske funktioner bruges til at udføre beregninger af flere kriterier. Med IF bruges OR- og AND -funktionerne til at inkludere eller ekskludere kampe.
Sådan bruges OR -funktionen i Microsoft Excel : Funktionen bruges til at inkludere alle de SANDE værdier i flere kriterier.
Sådan tælles celler, der indeholder dette eller det i Excel i Excel : Til celler, der indeholder dette eller hint, kan vi bruge SUMPRODUCT -funktionen. Sådan gør du disse beregninger.
Populære artikler:
50 Excel -genveje til at øge din produktivitet | Få hurtigere til din opgave. Disse 50 genveje får dig til at arbejde endnu hurtigere i Excel.
Sådan bruges Excel VLOOKUP -funktion| 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 Excel COUNTIF -funktion| 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.
Sådan bruges SUMIF -funktionen i Excel | Dette er endnu en vigtig instrumentbrætfunktion. Dette hjælper dig med at opsummere værdier på bestemte betingelser.
