Compter les valeurs manquantes
Supposons que vous ayez deux listes, pour compter le nombre total de valeurs dans une liste qui n'existent pas dans une autre liste, vous pouvez utiliser un SUMPRODUCT formule à l'aide de MATCH et les fonctions ISNA, ou les COUNTIF la fonction.
Compter les valeurs manquantes avec SUMPRODUCT, MATCH et ISNA
Compter les valeurs manquantes avec SUMPRODUCT et COUNTIF
Compter les valeurs manquantes avec SUMPRODUCT, MATCH et ISNA
Pour compter les nombre total de valeurs de la liste B manquantes dans la liste A comme indiqué ci-dessus, vous pouvez d'abord utiliser la fonction MATCH pour renvoyer un tableau de la position relative des valeurs de la liste B dans la liste A. Si une valeur n'existe pas dans la liste A, une erreur #N/A sera renvoyée. La fonction ISNA identifiera alors les erreurs #N/A et SUMPRODUCT comptera le nombre total d'erreurs.
Syntaxe générique
=SUMPRODUCT(--ISNA(MATCH(range_to_count,lookup_range,0)))
- range_to_count : La plage à partir de laquelle compter les valeurs manquantes. Se réfère ici à la liste B.
- plage_recherche : La plage à comparer avec la range_to_count. Se réfère ici à la liste A.
- 0: La match_type 0 force MATCH à effectuer une correspondance exacte.
Pour compter les nombre total de valeurs de la liste B manquantes dans la liste A, veuillez copier ou saisir la formule ci-dessous dans la cellule H6, et appuyez sur Entrer pour obtenir le résultat :
=SOMMEPROD(--ESTNA(CONCORDANCE(F6: F8,B6: B10, 0)))
Explication de la formule
=SUMPRODUCT(--ISNA(MATCH(F6:F8,B6:B10,0)))
- CORRESPONDANCE(F6:F8,B6:B10,0): La match_type 0 force la fonction MATCH à renvoyer des valeurs numériques qui indiquent les positions relatives des valeurs dans les cellules F6 à F8 dans le périmètre B6: B10. Si une valeur n'existe pas dans la liste A, une erreur #N/A sera renvoyée. Ainsi, les résultats seront dans un tableau comme celui-ci : {2;3;#N/A}.
- ISNA(CORRESPONDANCE(F6:F8,B6:B10,0)) = ISNA({2;3;#N/A}): ISNA fonctionne pour savoir si une valeur est une erreur "#N/A" ou non. Si oui, la fonction renverra un TURE ; Sinon, il renverra un FALSE. Ainsi, la formule ISNA renverra {FAUX;FAUX;VRAI}.
- SOMMEPROD(--ISNA(CORRESPONDANCE(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; 1; 0}. Ensuite, la fonction SOMMEPROD renvoie la somme : 1.
Compter les valeurs manquantes avec SUMPRODUCT et COUNTIF
Pour compter les nombre total de valeurs de la liste B manquantes dans la liste A, vous pouvez également utiliser la fonction NB.SI pour savoir si une valeur existe dans la liste A ou non avec la condition "=0", car un 0 sera généré si une valeur est manquante. Le SOMMEPROD compte alors le nombre total de valeurs manquantes.
Syntaxe générique
=SUMPRODUCT(--(COUNTIF(lookup_range,range_to_count)=0))
- plage_recherche : La plage à comparer avec la range_to_count. Se réfère ici à la liste A.
- range_to_count : La plage à partir de laquelle compter les valeurs manquantes. Se réfère ici à la liste B.
- 0: La match_type 0 force MATCH à effectuer une correspondance exacte.
Pour compter les nombre total de valeurs de la liste B manquantes dans la liste A, veuillez copier ou saisir la formule ci-dessous dans la cellule H6, et appuyez sur Entrer pour 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 cellules jusqu'à F6 à F8 dans le périmètre B6: B10. Le résultat sera dans un tableau comme celui-ci : {1; 1; 0}.
- - (NB.SI(B6:B10,F6:F8)= 0) = - ({1; 1; 0}= 0): L'extrait {1;1;0}=0 donne un tableau VRAI et FAUX {FAUX;FAUX;VRAI}. Le double signe négatif transforme alors VRAI en 1 et FAUX en 0. Le tableau final ressemble à ceci : {0; 0; 1}.
- SOMMEPROD(- (NB.SI(B6:B10,F6:F8)= 0)) = SOMMEPROD({0; 0; 1}): Le SOMMEPROD renvoie alors la somme : 1.
Fonctions connexes
Dans Excel, la fonction SUMPRODUCT peut être utilisée pour multiplier deux ou plusieurs colonnes ou tableaux ensemble, puis obtenir la somme des produits. En fait, le SUMPRODUCT est une fonction utile qui peut aider à compter ou à additionner les valeurs de cellule avec plusieurs critères comme la fonction COUNTIFS ou SUMIFS. Cet article présentera la syntaxe de la fonction et quelques exemples pour cette fonction SUMPRODUCT.
La fonction Excel MATCH recherche une valeur spécifique dans une plage de cellules et renvoie la position relative de la valeur.
La fonction COUNTIF est une fonction statistique dans Excel qui est utilisée pour compter le nombre de cellules qui répondent à un critère. Il prend en charge les opérateurs logiques (<>, =, > et <) et les caractères génériques (? et *) pour la correspondance partielle.
Formules associées
Trouver les valeurs manquantes
Il existe des cas où vous devez comparer deux listes pour vérifier si une valeur de la liste A existe dans la liste B dans Excel. Par exemple, vous avez une liste de produits, et vous souhaitez vérifier si les produits de votre liste existent dans la liste de produits fournie par votre fournisseur. Pour accomplir cette tâche, nous avons répertorié ici trois manières ci-dessous, n'hésitez pas à choisir celle que vous aimez.
Cet article se concentrera sur les formules Excel pour compter les cellules qui sont exactement égales à une chaîne de texte que vous spécifiez ou partiellement égale à la chaîne de texte donnée comme ci-dessous les captures d'écran. Tout d'abord, il expliquera la syntaxe et l'argument de la formule, le fournit des exemples pour que vous puissiez mieux comprendre.
Compter le nombre de cellules non comprises entre deux nombres donnés
Compter le nombre de cellules entre deux nombres est une tâche courante pour nous dans Excel, mais, dans certains cas, vous souhaiterez peut-être compter les cellules non comprises entre deux nombres donnés. Par exemple, j'ai une liste de produits avec les ventes du lundi au dimanche, maintenant, j'ai besoin d'obtenir le nombre de cellules qui ne se situe pas entre les nombres bas et élevés spécifiques comme illustré ci-dessous. Cet article, je vais présenter quelques formules pour traiter cette tâche dans Excel.
Les meilleurs outils de productivité de bureau
Kutools for Excel - vous aide à vous démarquer de la foule
Souhaitez-vous terminer votre travail quotidien rapidement et parfaitement? Kutools pour Excel apporte
- Conçu pour
1500scénarios de travail, vous aide à résoudre80 %Problèmes Excel.
- Réduisez les milliers de clics de clavier et de souris chaque jour, soulagez vos yeux et vos mains fatigués.
- Devenez un expert Excel en 3 minutes. Plus besoin de se souvenir des formules douloureuses et des codes VBA.
-
30-jour d'essai gratuit illimité. Garantie de remboursement de 60 jours. Mise à jour gratuite et assistance pendant 2 ans.
Onglet Office - Activer la lecture et la modification par onglets dans Microsoft Office (y compris Excel)
- Une seconde pour basculer entre des dizaines de documents ouverts!
- Réduisez chaque jour des centaines de clics de souris, dites adieu à la souris.
- Augmente votre productivité en
50 %lors de la visualisation et de la modification de plusieurs documents.
- Apporte des onglets efficaces à Office (y compris Excel), tout comme Chrome, Firefox et le nouvel Internet Explorer.
