Formule générique
SUMPRODUCT(--(A:A=A1))
Sommaire
Préface
C'est une introduction énervante, mais le contexte est important, désolé!
Si vous essayez de compter des nombres très longs (16 chiffres et plus) dans une plage avec COUNTIF, vous pouvez voir des résultats incorrects, en raison d'un bogue dans la façon dont certaines fonctions gèrent les nombres longs, même lorsque ces nombres sont stockés sous forme de texte. Considérez l'écran ci-dessous. Tous les nombres de la colonne D sont incorrects - bien que chaque nombre de la colonne B soit unique, le nombre retourné par COUNTIF suggère que ces nombres sont des doublons.
=COUNTIF(data,B5)
Ce problème est lié à la façon dont Excel gère les nombres. Excel ne peut gérer que 15 chiffres significatifs, et si vous entrez un nombre avec plus de 15 chiffres dans Excel, vous verrez les chiffres de fin convertis silencieusement en zéro. Le problème de comptage mentionné ci-dessus découle de cette limite.
Normalement, vous pouvez éviter cette limite en entrant des nombres longs sous forme de texte, soit en commençant le nombre par un guillemet simple ('999999999999999999) soit en formatant la ou les cellules en tant que texte avant de les saisir. Tant que vous n'avez pas besoin d'effectuer des opérations mathématiques sur un nombre, c'est une bonne solution, et cela vous permet de saisir des nombres très longs pour des choses comme les numéros de carte de crédit et les numéros de série sans perdre aucun numéro.
Cependant, si vous essayez d'utiliser COUNTIF pour compter un nombre avec plus de 15 chiffres (même lorsqu'il est stocké sous forme de texte), vous pouvez voir des résultats peu fiables. Cela se produit parce que COUNTIF convertit en interne la valeur longue en un nombre à un moment donné pendant le traitement, déclenchant la limite de 15 chiffres décrite ci-dessus. Sans tous les chiffres présents, certains nombres peuvent être comptés comme des doublons lorsqu'ils sont comptés avec COUNTIF.
Solution
Une solution consiste à remplacer la formule COUNTIF par une formule qui utilise SUM ou SUMPRODUCT. Dans l'exemple illustré, la formule en E5 ressemble à ceci:
=SUMPRODUCT(--(data=B5))
La formule utilise la plage nommée "données" (B5: B9) et génère le compte correct pour chaque nombre avec SUMPRODUCT.
Explication
Tout d'abord, l'expression à l'intérieur de SUMPRODUCT compare toutes les valeurs de la plage nommée «données» avec la valeur de la colonne B de la ligne actuelle. Il en résulte un tableau de résultats TRUE / FALSE.
=SUMPRODUCT(--(data=B5)) =SUMPRODUCT(--((TRUE;FALSE;FALSE;FALSE;FALSE)))
Ensuite, le double négatif contraint les valeurs TRUE / FALSE à des valeurs 1/0.
=SUMPRODUCT((1;0;0;0;0))
Enfin, SUMPRODUCT additionne simplement les éléments du tableau et renvoie le résultat.
Variante de formule de tableau
Vous pouvez également utiliser la fonction SOMME au lieu de SUMPRODUCT, mais il s'agit d'une formule matricielle et doit être saisie avec Ctrl + Maj + Entrée:
(=SUM(--(B:B=B5)))
Autres fonctions avec ce problème
Je ne l'ai pas vérifié moi-même, mais il semble que plusieurs fonctions aient le même problème, notamment SUMIF, SUMIFS, COUNTIF, COUNTIFS, AVERAGEIF et AVERAGEIFS.
