Compter les valeurs manquantes
Supposons que vous disposiez de deux listes. Pour compter le nombre total de valeurs présentes dans l’une des listes mais absentes de l’autre, vous pouvez utiliser une formule SOMMEPROD combinée aux fonctions EQUIV et ESTNA, ou encore la fonction NB.SI.
Compter les valeurs manquantes avec SOMMEPROD, EQUIV et ESTNA
Compter les valeurs manquantes avec SOMMEPROD et NB.SI
Compter les valeurs manquantes avec SOMMEPROD, EQUIV et ESTNA
Pour compter le nombre total de valeurs de la liste B absentes de la liste A, comme illustré ci-dessus, commencez par utiliser la fonction EQUIV pour obtenir un tableau indiquant les positions relatives des valeurs de la liste B dans la liste A. Si une valeur n’est pas présente dans la liste A, la fonction renvoie une erreur #N/A. La fonction ESTNA détecte ensuite ces erreurs #N/A, et SOMMEPROD calcule leur nombre total.
Syntaxe générique
=SUMPRODUCT(--ISNA(MATCH()))range_to_count,lookup_range,0)))
- plage_à_compter : La plage à partir de laquelle compter les valeurs manquantes. Il s’agit ici de la liste B.
- plage_recherche : La plage à comparer avec la plage_à_compter. Il s’agit ici de la liste A.
- 0 : Le type_correspondance 0 oblige la fonction EQUIV à effectuer une correspondance exacte.
Pour compter le nombre total de valeurs de la liste B absentes de la liste A, copiez ou saisissez la formule ci-dessous dans la cellule H6, puis appuyez sur Entréepour obtenir le résultat :
=SOMMEPROD(--ESTNA(EQUIV()))F6:F8;B6:B10;0)))
Explication de la formule
=SUMPRODUCT(--)ISNA()MATCH(F6:F8,B6:B10,0)))
- EQUIV(F6:F8;B6:B10;0): Le type_correspondance 0 oblige la fonction EQUIV à renvoyer des valeurs numériques indiquant les positions relatives des valeurs situées dans les cellules F6 à F8 dans la plage B6:B10. Si une valeur n’existe pas dans la liste A, une erreur #N/A est renvoyée. Les résultats apparaissent donc sous forme de tableau comme suit : {2;3;#N/A}.
- ESTNA()EQUIV(F6:F8;B6:B10;0))=ESTNA(){2;3;#N/A}):La fonction ESTNA permet de déterminer si une valeur correspond à l’erreur « #N/A ». Si c’est le cas, elle renvoie VRAI ; sinon, elle renvoie FAUX. Ainsi, la formule ESTNA renverra {FAUX;FAUX;VRAI}.
- SOMMEPROD(--)ESTNA()EQUIV(F6:F8;B6:B10;0))) = SOMMEPROD(--{FAUX;FAUX;VRAI}): Le double signe négatif convertit les VRAI en 1 et les FAUX en 0 : {0;0;1}. La fonction SOMMEPROD renvoie ensuite la somme : 1.
Compter les valeurs manquantes avec SOMMEPROD et NB.SI
Pour compter le nombre total de valeurs de la liste B absentes de la liste A, vous pouvez également utiliser la fonction NB.SI pour vérifier si chaque valeur figure dans la liste A, en appliquant la condition « =0 ». En effet, cette fonction renvoie 0 lorsqu’une valeur est manquante. La fonction SOMMEPROD additionne ensuite tous ces zéros pour vous donner le nombre total de valeurs absentes.
Syntaxe générique
=SUMPRODUCT(--(COUNTIF()))lookup_range,range_to_count)=0))
- plage_recherche : La plage à comparer avec la plage_à_compter. Il s’agit ici de la liste A.
- plage_à_compter : La plage à partir de laquelle compter les valeurs manquantes. Ici, il s’agit de la liste B.
- 0 : Le type_correspondance 0 oblige la fonction EQUIV à effectuer une correspondance exacte.
Pour compter le nombre total de valeurs de la liste B absentes de la liste A, copiez ou saisissez la formule ci-dessous dans la cellule H6, puis appuyez sur Entréepour obtenir le résultat :
=SOMMEPROD(--(NB.SI()))B6:B10;F6:F8)=0))
Explication de la formule
=SUMPRODUCT()--()COUNTIF(B6:B10,F6:F8)=0))
- NB.SI(B6:B10;F6:F8):La fonction NB.SI compte le nombre d’occurrences des valeurs situées entre F6 et F8 dans la plage B6:B10. Le résultat s’affiche sous forme de tableau comme suit : {1;1;0}.
- --()NB.SI(B6:B10;F6:F8)=0)=--(){1;1;0}=0):Le fragment {1;1;0}=0 génère un tableau de VRAI et de FAUX : {FAUX;FAUX;VRAI}. Le double signe négatif convertit ensuite les VRAI en 1 et les FAUX en 0, ce qui donne le tableau final suivant : {0;0;1}.
- SOMMEPROD()--()NB.SI(B6:B10;F6:F8)=0)) = SOMMEPROD({0;0;1}) : La fonction SOMMEPROD renvoie alors la somme : 1.
Fonctions associées
Dans Excel, la fonction SOMMEPROD multiplie entre eux deux colonnes ou tableaux (voire davantage), puis renvoie la somme des produits obtenus. En réalité, SOMMEPROD est une fonction particulièrement polyvalente : elle permet non seulement de réaliser des calculs avancés, mais aussi de compter ou d’additionner des valeurs selon plusieurs critères, à l’instar des fonctions NB.SI.ENS ou SOMME.SI.ENS. Cet article présente sa syntaxe ainsi que quelques exemples concrets d’utilisation.
La fonction EQUIV d’Excel recherche une valeur précise dans une plage de cellules et renvoie sa position relative.
La fonction NB.SI est une fonction statistique d’Excel conçue pour compter les cellules qui répondent à un critère spécifique. Elle prend en charge les opérateurs logiques (comme > et <) ainsi que les caractères génériques (? et *) pour effectuer des recherches partielles.
Formules associées
Rechercher les valeurs manquantes
Il vous arrive parfois de devoir comparer deux listes sous Excel pour vérifier si une valeur de la liste A figure également dans la liste B. Par exemple, vous avez une liste de produits et souhaitez savoir si ces derniers sont présents dans la liste fournie par votre fournisseur. Pour réaliser cette opération, nous vous proposons ci-dessous trois méthodes différentes : choisissez celle qui vous convient le mieux.
Cet article met l’accent sur les formules Excel permettant de compter les cellules strictement égales à une chaîne de texte spécifiée ou partiellement correspondantes à une chaîne donnée, comme le montrent les captures d’écran ci-dessous. Il présente d’abord la syntaxe et les arguments de ces formules, puis illustre leur utilisation à l’aide d’exemples concrets pour une meilleure compréhension.
Compter le nombre de cellules non comprises entre deux nombres donnés
Compter le nombre de cellules comprises entre deux nombres est une opération courante dans Excel. Cependant, il arrive parfois que vous ayez besoin de compter les cellules qui **ne se situent pas** entre deux valeurs données. Par exemple, j’ai une liste de produits avec leurs ventes du lundi au dimanche, et je dois déterminer combien de cellules se trouvent **en dehors** d’une fourchette définie par un seuil bas et un seuil haut, comme le montre la capture d’écran ci-dessous. Dans cet article, je vous présente plusieurs formules efficaces pour résoudre ce type de problème sous Excel.
Les meilleurs outils de productivité Office
Kutools pour Excel – Vous permet de vous démarquer de la foule
Kutools pour Excel propose plus de 300 fonctionnalités,garantissant que ce dont vous avez besoin est toujours à portée de clic...
Office Tab – Active la lecture et l’édition par onglets dans Microsoft Office (y compris Excel)
- Passez d’un document à l’autre en une seconde, même avec des dizaines de documents ouverts !
- Réduisez de centaines vos clics de souris chaque jour et dites adieu au syndrome de la main de la souris.
- Augmentez votre productivité de 50 % en consultant et en modifiant simultanément plusieurs documents.
- Apporte à Office (y compris Excel) une efficacité comparable à celle des navigateurs tels que Chrome, Edge et Firefox.