Compter les non correspondances par recherche Excel

Pour partager cette vidéo sur les réseaux sociaux ou sur un site, voici son url :


Sujets que vous pourriez aussi aimer :


Compter les différences

Cette astuce Excel est une exploitation dérivée de la précédente qui avait démontré comment repérer simplement toutes les concordances strictes dans un tableau.



Dans l'exemple illustré par la capture, des critères de recherche sont émis en deuxième colonne. Ces termes sont à chercher dans la première colonne. En haut de la troisième rangée, il doit en résulter le décompte de toutes les cellules non concordantes, soit celles ne portant aucune des mentions stipulées en critères. Et ces conditions peuvent évoluer en nombre, comme l'indiquent les mentions Vide dans la deuxième rangée du tableau.

Source et présentation
Pour mener à bien cette démonstration, nous proposons de récupérer tout d'abord ce tableau.

• Télécharger le classeur compter-differences.xlsx en cliquant sur ce lien,
• Double cliquer sur le fichier réceptionné pour l'ouvrir dans Excel,
• Puis, cliquer sur le bouton Activer la modification du bandeau de sécurité,
• En haut à gauche de la feuille Excel, déployer la liste déroulante de la zone Nom,

Vous remarquez que les deux premières colonnes du tableau sont reconnues par les intitulés respectifs Autos et Criteres.



Fort naturellement, nous exploiterons ces noms pour simplifier la construction de la formule matricielle. De plus, vous remarquez que des résultats numériques sont déjà en place. Le premier est inscrit en cellule F7. Il est le fruit de l'astuce précédente : =SOMMEPROD((NB.SI(Autos; Criteres))*1). Dans un raisonnement matriciel, il permet de compter toutes les cellules qui correspondent avec les critères émis en deuxième colonne et ce, quel que soit leur nombre. Le second est inscrit en cellule F10: =NBVAL(Autos). Il est classique. Il totalise simplement le nombre d'entrées, soit le nombre de véhicules. En F4, nous pourrions donc nous contenter de réaliser la soustraction de ces deux valeurs pour aboutir au résultat, celui sur le nombre de différences. Mais nous allons partir du principe que ces deux résultats intermédiaires n'existent pas. L'idée est d'obtenir le décompte de ces différences en une formule, avec bien sûr, un raisonnement matriciel une fois encore.

Dénombrer les non correspondances
L'astuce consiste donc à réaliser une recherche matricielle de la zone des critères dans la colonne des automobiles. Pour cela, nous allons exploiter la fonction Excel Equiv. Comme vous le savez, lorsqu'une fonction de recherche ne trouve pas, elle retourne des erreurs. Il s'agit donc de repérer et de totaliser ces erreurs.

• Cliquer sur la cellule F4 pour la sélectionner,
• Taper le symbole égal (=) pour démarrer la construction de la formule,
• Inscrire le nom de la fonction matricielle suivi d'une parenthèse, soit : SommeProd(,
• Ouvrir une nouvelle parenthèse pour accueillir la matrice de recherche,
• Taper la fonction logique suivante suivie d'une parenthèse : EstNa(,

Lorsqu'une recherche est infructueuse, la fonction Equiv répond en effet par le message d'erreur : #N/A. Nous devons cumuler toutes ces erreurs pour les totaliser et livrer le décompte des différences observées.

• Inscrire la fonction de recherche des positions, suivie d'une parenthèse, soit : Equiv(,
• Désigner la plage des valeurs cherchées par son nom, soit : Criteres,
• Taper un point-virgule (;) pour passer dans l'argument de la plage de recherche,
• Désigner la matrice des véhicules par son nom, soit : Autos,
• Taper un nouveau point-virgule suivi du chiffre zéro : ;0, pour une recherche exacte,
• Fermer la parenthèse de la fonction Equiv,
• Fermer la parenthèse de la fonction EstNa,
• Puis, fermer la parenthèse de la matrice conditionnelle,

A ce stade, le calcul matriciel répond par des indicateurs booléens (Vrai ou Faux) repérant les positions des non correspondances. Nous devons les convertir en chiffres (1 ou 0) pour qu'ils puissent être additionnés par la fonction SommeProd afin de livrer le décompte des différences. Nous connaissons l'astuce, elle consiste à multiplier cette matrice conditionnelle par le chiffre 1 afin de forcer la conversion.

• Taper le symbole de l'astérisque suivi du chiffre 1, soit : *1, pour forcer la conversion,
• Fermer enfin la parenthèse de la fonction Excel SommeProd,
• Puis, valider la formule matricielle à l'aide de la touche Entrée du clavier,

Le résultat tombe et comme vous le constatez, il est parfaitement cohérent. Il s'agit bien de la différence entre les deux autres résultats numériques de décompte. La syntaxe de la formule que nous avons bâtie reste relativement simple : =SOMMEPROD((ESTNA(EQUIV(Criteres; Autos; 0)))*1). Bien sûr, si vous ajoutez un critère à la suite dans la colonne E, le compteur des correspondances s'incrémente tandis que celui des non concordances se décrémente aussitôt.