Formula Excel: enumerați cel mai frecvent numerele care apar -

Formula generică

(=MODE(IF(1-ISNUMBER(MATCH(data,exp_rng,0)),data)))

rezumat

Pentru a enumera cele mai frecvente numere dintr-o coloană (adică cele mai frecvente, al doilea cel mai frecvent, al treilea cel mai frecvent, etc), puteți formula o matrice bazată pe patru funcții Excel: IF, MODE, MATCH și ISNUMBER. În exemplul prezentat, formula din D5 este:

(=MODE(IF(1-ISNUMBER(MATCH(data,$D$4:D4,0)),data)))

unde „date” este intervalul numit B5: B16. Formula este apoi copiată pe rândurile de sub D5 pentru a afișa lista dorită a celor mai frecvente numere.

Notă: aceasta este o formulă matrice și trebuie introdusă cu control + shift + enter.

Explicaţie

Nucleul acestei formule este funcția MODE, care returnează cel mai frecvent număr dintr-un interval sau matrice. Restul formulei construiește doar o matrice filtrată pe care MODE o poate folosi în fiecare rând. Intervalul extins $ D $ 4: D4 funcționează pentru a exclude numerele deja afișate în $ D $ 4: D4.

Lucrând din interior spre exterior:

  1. Funcția MATCH este utilizată mai întâi verificați toate numerele din intervalul numit „date” față de numerele existente în intervalul în expansiune $ D $ 4: D4
  2. ISNUMBER convertește valorile potrivite în TRUE și valorile nepotrivite în FALSE
  3. 1-NUMBER inversează matricea, iar operația matematică scoate unele și zerouri
  4. IF folosește ieșirea matricei de # 3 de mai sus pentru a filtra lista originală de valori, excluzând numerele deja în $ D $ 4: D4
  5. Funcția MODE returnează cel mai frecvent număr din ieșirea matricei la pasul # 4

În celula D5, nu are loc nici o filtrare și ieșirea fiecărui pas de mai sus arată astfel:

(#N/A;#N/A;#N/A;#N/A;#N/A;#N/A;#N/A;#N/A;#N/A;#N/A;#N/A;#N/A) (FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE) (1;1;1;1;1;1;1;1;1;1;1;1) (93;92;93;94;95;96;97;98;99;93;97;95) 93

În celula D6, cu 93 deja în D5, ieșirea arată astfel:

(2;#N/A;2;#N/A;#N/A;#N/A;#N/A;#N/A;#N/A;2;#N/A;#N/A) (TRUE;FALSE;TRUE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;TRUE;FALSE;FALSE) (0;1;0;1;1;1;1;1;1;0;1;1) (FALSE;92;FALSE;94;95;96;97;98;99;FALSE;97;95) 95

Gestionarea erorilor

Funcția MODE va ​​returna eroarea # N / A atunci când nu există un mod. Pe măsură ce copiați formula în rândurile următoare, probabil veți întâlni eroarea # N / A. Pentru a prinde această eroare și a returna un șir gol (""), puteți folosi IFERROR astfel:

=IFERROR(MODE(IF(1-ISNUMBER(MATCH(data,$D$4:D4,0)),data)),"")

Articole interesante...