Hvordan telle rader hvis matchende n -tall i Excel

Anonim

I denne artikkelen vil vi lære å telle rader hvis de matcher minst n tall i Excel.

Scenario:

I enkle ord, mens du jobber med talldata. Vi må trekke ut antallet rader som har minst, høyst eller nøyaktig n verdier fra den bestemte raden som tilfredsstiller de nevnte kriteriene i Excel. For eksempel hvis du beregner antall studenter som presterer under gjennomsnitt i tester. Vi kan utføre slike tellende problemer i en lang liste ved hjelp av Excel -formelen forklart nedenfor.

Formelfunksjoner som brukes og formel Syntaks:

Følgende funksjon vil være i bruk. Så bli litt kjent med det før du bruker det.

  1. SUM -funksjon
  2. MMULT -funksjon
  3. TRANSPOSE -funksjon
  4. COLUMN -funksjon

Vi vil konstruere en formel av disse oppgitte funksjonene.

Bruk formelen:

{ = SUM ( - (MMULT ( - ((data) <krit), TRANSPOSE (COLUMN (data) 0)) = n)) }

Merk: For krøllete seler ( { } ) Bruk Ctrl + Shift + Enter når du arbeider med matriser eller områder i Excel. Dette vil generere Curly Braces på formelen som standard. IKKE prøv å hardt kode krøllete klammeparenteser.

data: haug med tall som data.

= n: eksakt antall forekomster (bruk <= for høyst eller >= for minst tilfeller)

crit: kriterier å matche med

Eksempel:

Alt dette kan være forvirrende å forstå. Så la oss teste denne formelen ved å kjøre den på eksemplet nedenfor. Her har vi salgsdata for den bestemte salgs -IDen på rad. Vi må finne antallet salgs -ID der selgeren ikke har fullført salget på 40 på nøyaktig 2 ganger. Her er kriteriene <40 og forekomsten er 2 (= 2 for nøyaktig forekomst)

Formel:

{ = SUMME ( - (MMULT ( - ((C4: H15) <40), TRANSPOSE (COLUMN (C4: H15) 0)) = 2))

Forklaring:

  • COLUMN -funksjon returnerer matrisen med kolonnenummer fra den første cellen i arrayet til den siste cellen i arrayet. {3; 4; 5; 7; 8}
  • ^0, denne operasjonen med alle matrisenummer returnerer matrisen 1s.
  • TRANSPOSE -funksjonen konverterer matrisearrayens rader til kolonner og kolonner til rader. Her blir rekken med 1s i rad konvertert til array med 6 (1s) i kolonner. { 1; 1 ; 1 ; 1 ; 1 ; 1 }
  • - ((C4: H15) <40), denne operasjonen returnerer verdiene som 1s som SANN-verdier (samsvarende kriterier) og 0s som Falske verdier.
  • MMULT -funksjonen tar matrisemultiplikasjon av de 2 matrisene og returnerer verdier som vist nedenfor.

= SUM ( - ({1; 0; 1; 0; 2; 1; 0; 0; 1; 2; 3; 3} = 2))

  • SUM -funksjonen teller verdiene i matrisematching 2 ettersom forekomsten er nødvendig.
  • Bruk Ctrl + Shift + Enter i stedet for for å få resultatet. Siden dette er en matriseformel.

Her er matrisene til funksjonen gitt som matrisereferanse. Bruk dobbel (-) manuelt Trykk Enter for å få resultatet.

Som du kan se på bildet ovenfor, er det 2 selgere som ikke har fullført 40 salg. Dette er tellingen til de tilsvarende radene i dataene der 2 tall er mindre enn 40. Du kan tilpasse antall forekomster og kriterier som skal kontrolleres i radene i tabellen. Nå er eksemplet nedenfor for å få greven med forskjellige kriterier og forskjellige forekomster.

Tell rader som matcher salg over 100 med 3 som eksakt forekomst:

Nå vil vi gjerne få hvor mange av selgerne som gjør det moderat godt i salget. For dette må vi få tellingen av selger som hadde solgt over 100 med nøyaktig 3 ganger.

Bruk formelen:

= SUM ( - (MMULT ( - ((C4: H15)> 100), TRANSPOSE (COLUMN (C4: H15) 0)) = 3))

> 100: tallet er større enn 100.

= 3: forekomst være lik 3

Her er matrisene til funksjonen gitt som matrisereferanse. Bruk dobbel (-) manuelt Trykk Enter for å få resultatet.

Som du kan se, er det 3 selgere som har det ganske bra. Dette er tellingen til de tilsvarende radene i dataene der 3 salg er større enn 100.

Her er alle observasjonsnotatene om bruk av formelen.

Merknader:

  1. Formelen fungerer bare med tall.
  2. MMULT- og TRANSPOSE -funksjonene er matrisefunksjonen, så inspiser før du bruker.
  3. Hvis formelen returnerer #VALUE -feil, må du kontrollere om de krøllete bukseselene er tilstede i formelen som vist i eksemplene i artikkelen.
  4. Negasjon (-) char endrer verdier, TRUEs eller 1s til FALSEs eller 0s og FALSEs eller 0s til TRUEs eller 1s.
  5. Operasjoner som er lik ( = ), mindre enn lik ( <= ), større enn ( > ) eller ikke er lik () kan utføres innenfor en anvendt formel, bare med tall.

Håper denne artikkelen om Hvordan telle rader hvis n samsvarende verdier i Excel er forklarende. Finn flere artikler om telleformler her. Hvis du likte bloggene våre, del den med fristarts 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

Finn den siste dataraden med tall i Excel : I en rekke tekstverdier finner du den siste med data i excel.

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 som er avstarter på andre celleverdier i Excel.

TELLER To kriterier samsvarer : Tell 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 -snarvei for å øke produktiviteten : Bli raskere på oppgaven din. Disse 50 snarveiene får deg til å jobbe enda raskere med Excel.

Slik bruker du tVLOOKUP -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 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.

Slik bruker du SUMIF -funksjonen i Excel : Dette er en annen viktig funksjon i instrumentbordet. Dette hjelper deg med å oppsummere verdier på spesifikke forhold.