I denne artikel vil vi lære at tælle rækker, hvis de matcher mindst n tal i Excel.
Scenarie:
I enkle ord, mens du arbejder med taldata. Vi skal udtrække antallet af rækker med mindst, højst eller nøjagtigt n værdier fra den bestemte række, der opfylder de nævnte kriterier i Excel. For eksempel hvis man beregner antallet af elever, der klarer sig under gennemsnittet i test. Vi kan udføre denne slags tælleproblemer på en lang liste ved hjælp af Excel -formlen forklaret nedenfor.
Brugte formelfunktioner og formel Syntaks:
Følgende funktion vil være i brug. Så kend lidt til, før du bruger den.
- SUM -funktion
- MMULT funktion
- TRANSPOSE -funktion
- COLUMN funktion
Vi vil konstruere en formel ud af disse angivne funktioner.
Brug formlen:
| { = SUM ( - (MMULT ( - ((data) <crit), TRANSPOSE (COLUMN (data) 0)) = n)) } |
Bemærk: Til krøllede seler ( { } ) Brug Ctrl + Shift + Enter når du arbejder med arrays eller intervaller i Excel. Dette genererer som standard Curly Braces på formlen. Forsøg IKKE at hardt kode krøllede seler tegn.
data: flok tal som data.
= n: nøjagtigt antal forekomster (brug <= for højst eller >= i hvert fald forekomster)
crit: kriterier at matche med
Eksempel:
Alt dette kan være forvirrende at forstå. Så lad os teste denne formel ved at køre den på eksemplet nedenfor. Her har vi salgsdata for det særlige salgs -id i træk. Vi skal finde antallet af salgs -id, hvor sælger ikke har gennemført salget på 40 nøjagtigt 2 gange. Her er kriterierne <40 og forekomsten er 2 (= 2 for nøjagtig forekomst)
Formel:
| { = SUM ( - (MMULT ( - ((C4: H15) <40), TRANSPOSE (COLUMN (C4: H15) 0)) = 2)) |
Forklaring:
- COLUMN -funktion returnerer array af kolonnenummer fra den første celle i array til den sidste celle i array. {3; 4; 5; 7; 8}
- ^0, denne operation med alle matrixnumre returnerer array på 1s.
- TRANSPOSE -funktionen konverterer matrixmatrixens rækker til kolonner og kolonner til rækker. Her konverteres arrayet med 1'er i rækken til array 6 (1s) i kolonner. {1; 1; 1; 1; 1; 1}
- - ((C4: H15) <40), denne handling returnerer værdierne som 1s som SANDE værdier (matchede kriterier) og 0'er som Falske værdier.
- MMULT -funktionen tager matrixmultiplikation af de 2 arrays og returnerer værdier som vist nedenfor.
= SUM ( - ({1; 0; 1; 0; 2; 1; 0; 0; 1; 2; 3; 3} = 2))
- SUM -funktion tæller værdierne i matrixmatchning 2, da forekomsten er påkrævet.
- Brug Ctrl + Shift + Enter i stedet for for at få resultatet. Da dette er en matrixformel.
Her er arrays til funktionen givet som arrayreference. Brug dobbelt (-) manuelt Tryk på Enter for at få resultatet.
Som du kan se på billedet ovenfor, er der 2 sælger, der ikke har gennemført selv 40 salg. Dette er tællingen til de tilsvarende rækker i dataene, hvor 2 tal er mindre end 40. Du kan tilpasse tællingens forekomst og kriterier, der skal kontrolleres i rækker i tabellen. Nu er eksemplet herunder for at få greven med forskellige kriterier og forskellige forekomster.
Tæl rækker, der matcher salg over 100 med 3 som nøjagtig forekomst:
Nu vil vi gerne få, hvor mange af sælgere, der klarer sig moderat godt i salget. Til dette har vi brug for at få optællingen af sælger, der havde solgt over 100 med præcis 3 gange.
Brug formlen:
| = SUM ( - (MMULT ( - ((C4: H15)> 100), TRANSPOSE (COLUMN (C4: H15) 0)) = 3)) |
> 100: antallet er større end 100.
= 3: forekomst være lig med 3
Her er arrays til funktionen givet som arrayreference. Brug dobbelt (-) manuelt Tryk på Enter for at få resultatet.
Som du kan se, er der 3 sælgere, der klarer sig moderat fint. Dette er tællingen til de tilsvarende rækker i dataene, hvor 3 salg er større end 100.
Her er alle observationsnotater vedrørende brug af formlen.
Bemærkninger:
- Formlen fungerer kun med tal.
- MMULT- og TRANSPOSE -funktioner er matrixfunktionen, så inspicér før brug.
- Hvis formlen returnerer #VÆRDI -fejl, skal du kontrollere, om de krøllede seler skal være til stede i formlen som vist i eksemplerne i artiklen.
- Negation (-) char ændrer værdier, TRUEs eller 1s til FALSEs eller 0s og FALSEs eller 0s til TRUEs eller 1s.
- Handlinger som er lig med ( = ), mindre end lig med ( <= ), bedre end ( > ) eller ikke er lig med () kan udføres inden for en anvendt formel, kun med tal.
Håber denne artikel om hvordan man tæller rækker, hvis n matchende værdier i Excel er forklarende. Find flere artikler om tælleformler her. Hvis du kunne lide vores blogs, kan du dele den med dine fristarts 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
Find den sidste række data med tal i Excel : I en række tekstværdier finder du den sidste data i excel.
Sådan bruges funktionen SUMPRODUCT i Excel: Returnerer SUMMEN efter multiplikation af værdier i flere arrays i excel.
COUNTIFS med Dynamic Criteria Range : Tæl celler depstartent på andre celleværdier i Excel.
TÆLLER To kriterier matcher : Tæl celler, der matcher to forskellige kriterier på listen i excel.
TÆLLER MED ELLER Til flere kriterier : Tæl celler, der har flere kriterier, der matcher ved hjælp af OR -funktionen.
COUNTIFS -funktionen i Excel : Tæl celler afhængigt af andre celleværdier.
Sådan bruges Countif i VBA i Microsoft Excel : Tæl celler ved hjælp af Visual Basic for Applications -kode.
Sådan bruges jokertegn i excel : Tæl celler, der matcher sætninger ved hjælp af jokertegnene i excel
Populære artikler:
50 Excel -genvej til at øge din produktivitet : Bliv hurtigere til din opgave. Disse 50 genveje får dig til at arbejde endnu hurtigere i Excel.
Sådan bruges than VLOOKUP -funktion 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 funktionen COUNTIF 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.
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.