Formule Excel: liste les nombres les plus fréquents -

Table des matières

Formule générique

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

Sommaire

Pour répertorier les nombres les plus fréquents dans une colonne (c'est-à-dire le plus courant, le deuxième plus courant, le troisième le plus courant, etc.), vous pouvez utiliser une formule matricielle basée sur quatre fonctions Excel: IF, MODE, MATCH et ISNUMBER. Dans l'exemple illustré, la formule en D5 est:

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

où "données" est la plage nommée B5: B16. La formule est ensuite copiée dans les lignes sous D5 pour afficher la liste souhaitée des nombres les plus fréquents.

Remarque: il s'agit d'une formule matricielle et doit être saisie avec Ctrl + Maj + Entrée.

Explication

Le cœur de cette formule est la fonction MODE, qui renvoie le nombre le plus fréquent dans une plage ou un tableau. Le reste de la formule construit simplement un tableau filtré pour MODE à utiliser dans chaque ligne. La plage en expansion $ D $ 4: D4 fonctionne pour exclure les nombres déjà générés dans $ D $ 4: D4.

Travailler de l'intérieur vers l'extérieur:

  1. La fonction MATCH est utilisée pour vérifier d'abord tous les nombres dans la plage nommée "data" par rapport aux nombres existants dans la plage en expansion $ D $ 4: D4
  2. ISNUMBER convertit les valeurs correspondantes en TRUE et les valeurs non correspondantes en FALSE
  3. 1-NUMBER inverse le tableau et l'opération mathématique génère des uns et des zéros
  4. IF utilise la sortie de tableau de # 3 ci-dessus pour filtrer la liste de valeurs d'origine, à l'exclusion des nombres déjà dans $ D $ 4: D4
  5. La fonction MODE renvoie le nombre le plus fréquent dans la sortie du tableau à l'étape 4

Dans la cellule D5, aucun filtrage ne se produit et la sortie de chaque étape ci-dessus ressemble à ceci:

(#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

Dans la cellule D6, avec 93 déjà dans D5, la sortie ressemble à ceci:

(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

Gestion des erreurs

La fonction MODE renverra l'erreur # N / A lorsqu'il n'y a pas de mode. Lorsque vous copiez la formule dans les lignes suivantes, vous rencontrerez probablement l'erreur # N / A. Pour intercepter cette erreur et renvoyer une chaîne vide ("") à la place, vous pouvez utiliser IFERROR comme ceci:

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

Articles intéressants...