Hur man räknar rader om matchande n -nummer i Excel

Anonim

I den här artikeln kommer vi att lära oss hur man räknar rader om matchar minst n -tal i Excel.

Scenario:

I enkla ord, medan du arbetar med siffradata. Vi måste extrahera antalet rader som har minst, högst eller exakt n -värden från den specifika raden som uppfyller de nämnda kriterierna i Excel. Till exempel om man beräknar antalet elever som presterar under genomsnittet i tester. Vi kan utföra den här typen av räkneproblem i en lång lista med hjälp av Excel -formeln som förklaras nedan.

Formelfunktioner som används och formel Syntax:

Följande funktion kommer att användas. Så lär dig lite om innan du använder den.

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

Vi kommer att konstruera en formel av dessa angivna funktioner.

Använd formeln:

{ = SUMMA ( - (MMULT ( - ((data) <crit), TRANSPOSE (COLUMN (data) 0)) = n)) }

Obs: För lockiga hängslen ( { } ) Använda sig av Ctrl + Skift + Enter när du arbetar med matriser eller intervall i Excel. Detta genererar som standard Curly Braces på formeln. Försök INTE att koda lockiga tandställningstecken.

data: massa siffror som data.

= n: exakt antal förekomster (använd <= för högst eller >= för åtminstone händelser)

crit: kriterier att matcha med

Exempel:

Allt detta kan vara förvirrande att förstå. Så, låt oss testa denna formel genom att köra den på exemplet som visas nedan. Här har vi försäljningsdata för det specifika försäljnings -ID: t i rad. Vi måste hitta antalet försäljnings -ID där säljaren inte har slutfört försäljningen på 40 på exakt 2 gånger. Här är kriterierna <40 och förekomsten är 2 (= 2 för exakt förekomst)

Formel:

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

Förklaring:

  • COLUMN -funktionen returnerar array med kolumnnummer från den första cellen i arrayen till den sista cellen i arrayen. {3; 4; 5; 7; 8}
  • ^0, den här operationen med alla matrisnummer returnerar matrisen 1s.
  • TRANSPOSE -funktionen konverterar matrismatrisens rader till kolumner och kolumner till rader. Här konverteras matrisen med 1: or i rad till matris med 6 (1: or) i kolumner. {1; 1; 1; 1; 1; 1}
  • - ((C4: H15) <40), den här åtgärden returnerar värdena som 1s som SANT-värden (matchade kriterier) och 0s som falska värden.
  • MMULT -funktionen tar matrismultiplikation av de 2 matriserna och returnerar värden enligt nedan.

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

  • SUM -funktionen räknar värdena i matrismatchning 2 eftersom förekomsten krävs.
  • Använd Ctrl + Skift + Enter istället för för att få resultatet. Eftersom detta är en matrisformel.

Här anges matriserna till funktionen som matrisreferens. Använd dubbel (-) manuellt Tryck på Enter för att få resultatet.

Som du kan se i bilden ovan är det 2 säljare som inte har slutfört ens 40 försäljningar. Detta är räkningen till motsvarande rader i data där 2 nummer är färre än 40. Du kan anpassa antalet förekomster och kriterier som ska kontrolleras i raderna i tabellen. Nu nedan är exemplet för att få greven med olika kriterier och olika förekomster.

Räkna rader som matchar försäljning över 100 med 3 som exakt förekomst:

Nu skulle vi vilja få hur många av säljare som presterar måttligt bra i försäljning. För detta måste vi få räkningen av säljare som hade gjort försäljning över 100 med exakt 3 gånger.

Använd formeln:

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

> 100: antalet är större än 100.

= 3: förekomst vara lika med 3

Här anges matriserna till funktionen som matrisreferens. Använd dubbel (-) manuellt Tryck på Enter för att få resultatet.

Som ni ser är det tre säljare som mår måttligt bra. Detta är räkningen till motsvarande rader i data där 3 försäljningar är större än 100.

Här är alla observationsanteckningar om användning av formeln.

Anmärkningar:

  1. Formeln fungerar bara med siffror.
  2. MMULT- och TRANSPOSE -funktionerna är matrisfunktionen, så kontrollera innan du använder den.
  3. Om formeln returnerar #VÄRDE -fel, kontrollera om de lockiga hängslen måste finnas i formeln som visas i exemplen i artikeln.
  4. Negation (-) char ändrar värden, TRUE eller 1s till FALSE eller 0s och FALSE eller 0s till TRUE eller 1s.
  5. Operationer som är lika med ( = ), mindre än lika med ( <= ), större än ( > ) eller inte är lika med () kan utföras inom en tillämpad formel, endast med siffror.

Hoppas den här artikeln om hur man räknar rader om n matchande värden i Excel är förklarande. Hitta fler artiklar om räknar formler här. Om du gillade våra bloggar, dela den med dina fristarts på Facebook. Och du kan också följa oss på Twitter och Facebook. Vi vill gärna höra från dig, låt oss veta hur vi kan förbättra, komplettera eller förnya vårt arbete och göra det bättre för dig. Skriv till oss på e -postwebbplatsen

Hitta den sista raden med data med siffror i Excel : I en rad textvärden hittar du den sista med data i excel.

Så här använder du SUMPRODUCT -funktionen i Excel: Returnerar SUMMEN efter multiplikation av värden i flera matriser i excel.

RÄKNINGAR med Dynamic Criteria Range : Räkna celler depstartent på andra cellvärden i Excel.

RÄKNINGAR Matchning två kriterier : Räkna celler som matchar två olika kriterier på listan i excel.

RÄKNINGAR MED ELLER För flera kriterier : Räkna celler med flera kriterier som matchar med hjälp av OR -funktionen.

RÄTTA -funktionen i Excel : Räkna celler beroende på andra cellvärden.

Hur man använder Countif i VBA i Microsoft Excel : Räkna celler med Visual Basic for Applications -kod.

Hur man använder jokertecken i excel : Räkna celler som matchar fraser med jokertecken i excel

Populära artiklar:

50 Excel -genväg för att öka din produktivitet : Bli snabbare på din uppgift. Dessa 50 genvägar får dig att arbeta ännu snabbare med Excel.

Hur man använder than VLOOKUP -funktion i Excel : Detta är en av de mest använda och populära Excel -funktionerna som används för att leta upp värde från olika intervall och ark.

Så här använder du funktionen COUNTIF i Excel : Räkna värden med villkor med denna fantastiska funktion. Du behöver inte filtrera dina data för att räkna specifika värden. Countif -funktionen är avgörande för att förbereda din instrumentpanel.

Hur man använder SUMIF -funktionen i Excel : Detta är en annan viktig instrumentpanel. Detta hjälper dig att summera värden på specifika förhållanden.