În acest articol, vom învăța cum să numărăm rândurile dacă se potrivesc cel puțin n numere în Excel.
Scenariu:
Cu cuvinte simple, în timp ce lucrați cu date numerice. Trebuie să extragem numărul de rânduri care au cel puțin, cel mult sau exact n valori din rândul respectiv care îndeplinește criteriile menționate în Excel. De exemplu, dacă se calculează numărul de studenți care au performanțe sub medie la teste. Putem efectua acest tip de probleme de numărare într-o listă lungă folosind formula Excel explicată mai jos.
Funcții de formulă utilizate și sintaxă de formulă:
Următoarea funcție va fi utilizată. Așadar, cunoașteți puțin înainte de a-l utiliza.
- Funcția SUM
- Funcția MMULT
- Funcția TRANSPOSE
- Funcția COLUMN
Vom construi o formulă din aceste funcții declarate.
Folosiți formula:
| { = SUM (- (MMULT (- ((date) <crit), TRANSPOSE (COLUMN (data) 0)) = n)) } |
Notă: Pentru acolade ( { } ) Utilizare Ctrl + Shift + Enter când lucrați cu tablouri sau intervale în Excel. Aceasta va genera în mod implicit aparate dentare pe formulă. NU încercați să codificați caractere dentare cu coduri dure.
date: grămadă de numere ca date.
= n: numărul exact de apariții (utilizați <= pentru cel mult sau >= pentru cel mai mic eveniment)
crit: criterii pentru a se potrivi cu
Exemplu:
Toate acestea ar putea fi confuze pentru a înțelege. Deci, să testăm această formulă executând-o pe exemplul prezentat mai jos. Aici avem date de vânzări pentru ID-ul de vânzări consecutiv. Trebuie să găsim numărul de cod de vânzări în cazul în care agentul de vânzări nu a finalizat vânzările de 40 de exact 2 ori. Aici criteriile sunt <40 și apariția este 2 (= 2 pentru apariția exactă)
Formulă:
| { = SUM (- (MMULT (- ((C4: H15) <40), TRANSPOZĂ (CULONĂ (C4: H15) 0)) = 2)) |
Explicaţie:
- Funcția COLUMN returnează matricea numărului de coloană de la prima celulă din matrice la ultima celulă din matrice. {3; 4; 5; 7; 8}
- 0, această operațiune cu orice număr de matrice returnează matrice de 1s.
- Funcția TRANSPOSE convertește rândurile matricei matrice în coloane și coloane în rânduri. Aici matricea de 1s în rând este convertită în matrice de 6 (1s) în coloane. {1; 1; 1; 1; 1; 1}
- - ((C4: H15) <40), această operațiune returnează valorile ca 1s ca valori TRUE (criterii potrivite) și 0s ca valori false.
- Funcția MMULT ia multiplicarea matricială a celor 2 tablouri și returnează valorile așa cum se arată mai jos.
= SUM (- ({1; 0; 1; 0; 2; 1; 0; 0; 1; 2; 3; 3} = 2))
- Funcția SUM numără valorile din matricea potrivită 2 pe măsură ce este necesară apariția.
- Folosiți Ctrl + Shift + Enter în locul pentru a obține rezultatul. Deoarece aceasta este o formulă matrice.
Aici matricile funcției sunt date ca referință de matrice. Folosiți manual dublu (-) manual Apăsați Enter pentru a obține rezultatul.
După cum puteți vedea în imaginea de mai sus, există 2 persoane care nu au realizat nici măcar 40 de vânzări. Acesta este Numărul la rândurile corespunzătoare din date unde 2 numere sunt mai mici de 40. Puteți personaliza apariția numărului și criteriile care trebuie verificate în rândurile tabelului. Acum, mai jos este exemplul pentru a obține numărul cu criterii diferite și apariții diferite.
Numărați rândurile care corespund vânzărilor peste 100 cu 3 ca apariție exactă:
Acum ne-ar plăcea să obținem câți dintre vânzători au performanțe moderat bune în vânzări. Pentru aceasta, trebuie să obținem numărul vânzătorului care a realizat vânzări peste 100 de exact 3 ori.
Folosiți formula:
| = SUM (- (MMULT (- ((C4: H15)> 100), TRANSPOZĂ (CULONĂ (C4: H15) 0)) = 3)) |
> 100: numărul să fie mai mare de 100.
= 3: apariția trebuie să fie egală cu 3
Aici matricile funcției sunt date ca referință de matrice. Utilizați manual dublu (-) Apăsați Enter pentru a obține rezultatul.
După cum puteți vedea, există 3 agenți de vânzări care se descurcă moderat. Acesta este Numărul la rândurile corespunzătoare din date în care 3 vânzări sunt mai mari de 100.
Iată toate notele observaționale privind utilizarea formulei.
Note:
- Formula funcționează numai cu numere.
- Funcțiile MMULT și TRANSPOSE sunt funcția matrice, deci inspectați înainte de utilizare.
- Dacă formula returnează eroarea #VALUE, verificați dacă aparatele dentare trebuie să fie prezente în formulă așa cum se arată în exemplele din articol.
- Negation (-) char schimbă valorile, TRUEs sau 1s în FALSE sau 0s și FALSEs sau 0s în TRUEs sau 1s.
- Operații precum este egal cu ( = ), mai mic decât egal cu ( <= ), mai mare ca ( > ) sau nu egal cu () poate fi efectuat în cadrul unei formule aplicate, numai cu numere.
Sper că acest articol despre Cum se numără rândurile dacă n valori potrivite în Excel este explicativ. Găsiți mai multe articole despre formulele de numărare aici. Dacă ți-au plăcut blogurile noastre, împărtășește-le cu prietenii tăi de pe Facebook. Și, de asemenea, ne puteți urmări pe Twitter și Facebook. Ne-ar plăcea să aflăm de la dvs., să ne anunțați cum putem îmbunătăți, completa sau inova munca noastră și să o îmbunătățim. Scrieți-ne pe site-ul de e-mail
Găsiți ultimul rând de date cu numere în Excel : Într-o gamă de valori text găsiți ultimele date în Excel.
Cum se utilizează funcția SUMPRODUCT în Excel: Returnează SUMA după multiplicarea valorilor în mai multe tablouri în excel.
COUNTIFS cu gamă de criterii dinamice : Numărați celulele dependente de alte valori ale celulei din Excel.
COUNTIFS Două criterii potrivite : Numărați celulele care se potrivesc cu două criterii diferite pe listă în Excel.
COUNTIFI CU SAU Pentru criterii multiple : Numărați celulele care au mai multe criterii potrivite utilizând funcția SAU.
Funcția COUNTIFS în Excel : Numărați celulele dependente de alte valori ale celulei.
Cum se folosește Countif în VBA în Microsoft Excel : Numărați celulele folosind codul Visual Basic pentru aplicații.
Cum se utilizează metacaracterele în Excel : Numărați celule care se potrivesc cu fraze folosind metacaracterele în excel
Articole populare:
50 Comandă rapidă Excel pentru a vă crește productivitatea : Deveniți mai rapid la sarcina dvs. Aceste 50 de comenzi rapide vă vor face să lucrați și mai rapid pe Excel.
Cum se folosește tFuncția VLOOKUP în Excel : Aceasta este una dintre cele mai utilizate și populare funcții ale Excel, care este utilizată pentru a căuta valoarea din diferite intervale și foi.
Cum se utilizează funcția COUNTIF în Excel : Numărați valorile cu condiții folosind această funcție uimitoare. Nu trebuie să vă filtrați datele pentru a număra anumite valori. Funcția Countif este esențială pentru a vă pregăti tabloul de bord.
Cum se utilizează funcția SUMIF în Excel : Aceasta este o altă funcție esențială a tabloului de bord. Acest lucru vă ajută să rezumați valori în condiții specifice.