Compter les données manquantes avec Excel

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


Sujets que vous pourriez aussi aimer :


Trouver les données absentes

Cette nouvelle astuce Excel propose de montrer comment calculer les données manquantes à partir d'une liste par rapport aux valeurs recensées dans un tableau.



Dans l'exemple finalisé illustré par la capture, l'utilisateur liste des personnes à l'étude dans une colonne située sur la droite. Instantanément, un calcul doit indiquer combien de personnes issues du tableau central sont ignorées. Et dans le même temps, ces dernières doivent être repérées en couleur orange dans ce tableau d'origine.

Classeur source
Pour développer cette astuce, nous suggérons d'appuyer nos démonstrations sur un classeur existant.

• Télécharger le classeur trouver-donnees-absentes.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é,

Des listes déroulantes sont prévues dans chaque cellule quadrillée de la colonne G. L'utilisateur peut ainsi facilement désigner une nouvelle personne à placer à l'étude. A ce titre, nous aurions pu construire des listes déroulantes amenuisées comme l'avait démontré une formation. De cette manière, chaque personne déjà appelée ne serait plus proposée. Mais ce n'est pas la vocation de cette astuce.

Comme nous avons déjà pu le constater, des résultats de synthèse sont attendus en colonne B dans les cases colorées. Les titres sont suffisamment parlants. Pour simplifier l'étude, des noms ont été attribués à deux plages de cellules. Vous pouvez le constater en déployant la zone Nom en haut à gauche de la feuille Excel. La plage Liste fait référence à l'intégralité des cellules qu'il est possible de renseigner en colonne G. La plage Noms fait référence à la colonne des noms du tableau. En l'occurrence ici, il s'agit des cellules de la colonne D. Les plages Liste et Noms sont donc à comparer. Et ces intitulés vont permettre de simplifier les syntaxes des formules à construire.

Compter les cellules
Nous proposons de débuter par les calculs les plus simples. En cellule B7, nous attendons le nombre de personnes désignées par l'utilisateur. En cellule B10, nous souhaitons connaître le nombre total de personnes recensées dans le tableau d'origine. Dans l'un et l'autre cas, il suffit d'appliquer la fonction Excel NbVal sur la plage adaptée. Comme vous le savez, cette fonction permet de compter les cellules non vides.

• En cellule B10, construire et valider la formule suivante : =NBVAL(Noms),
• En cellule B7, construire et valider la formule suivante : =NBVAL(Liste),

Le tableau recense donc onze personnes. Et dans la configuration de départ, seules deux sont désignées dans la liste annexe. Mais si vous ajoutez de nouvelles personnes, ce deuxième résultat s'actualise instantanément.

Compter les personnes manquantes
Certes, grâce aux deux précédents résultats, il est très simple de déduire les personnes n'ayant pas été désignées, par simple soustraction. Mais nous souhaitons nous placer dans le contexte où nous devons aboutir au résultat, donc sans calculs intermédiaire d'aide. Pour cela, nous devons comparer les deux listes. Et pour comparer l'intégralité des données d'un tableau avec l'intégralité des données d'un autre, un raisonnement matriciel est nécessaire. Et la formule à bâtir est très simple. L'astuce consiste à exploiter la fonction Excel SommeProd en lui passant une matrice conditionnelle. Cette matrice conditionnelle doit être capable de dénombrer tous les éléments d'une liste qui ne se trouvent pas dans l'autre. Et la fonction de dénombrement dédiée est la fonction Excel Nb.Si.

• Sélectionner le résultat à trouver en cliquant sur sa cellule B4,
• Taper le symbole égal (=) pour initier la syntaxe de la formule matricielle,
• Inscrire la fonction matricielle suivie d'une parenthèse, soit : SommeProd(,
• Ouvrir une nouvelle parenthèse pour accueillir la matrice conditionnelle,
• Inscrire la fonction de dénombrement conditionnel, suivie d'une parenthèse, soit : Nb.Si(,
• Désigner la plage des noms à analyser par son intitulé, soit : Noms,
• Taper un point-virgule (;) pour passer dans l'argument du critère de la fonction Nb.Si,

Dans ce raisonnement matriciel, il ne s'agit pas de lui passer un critère à isoler. Ce sont tous les éléments des deux plages qui doivent être comparés ensemble.

• Désigner la plage des personnes ajoutées par son nom, soit : Liste,
• Puis, fermer la parenthèse de la fonction Nb.Si,
• Dès lors, taper le critère suivant : =0,

Nous proposons de résumer la syntaxe à ce stade. La matrice conditionnelle est confectionnée avec la fonction Nb.Si. Ce n'est classiquement pas une cellule qui est cherchée dans une plage. C'est une matrice qui est comparée à une autre. Dans un raisonnement matriciel, toutes les cellules de la seconde liste sont comparées chronologiquement à chaque cellule de la première. Le critère est sans appel. Pour chaque dénombrement nul, nous en concluons que la personne en cours d'analyse est manquante.

Pour l'instant, la formule matricielle répond par des indicateurs booléens renseignant sur les positions concordantes (Vrai). Nous devons les forcer en chiffres par simple multiplication. Et puisque c'est sa nature, la fonction SommeProd doit additionner tous ces chiffres 1 à l'issue. Il doit en résulter le décompte des personnes manquantes.

• Fermer la parenthèse de la matrice conditionnelle,
• Taper le symbole de l'étoile suivi du chiffre 1 : *1, pour forcer la conversion en chiffres,
• Fermer la parenthèse de la fonction SommeProd,
• Puis, valider la formule matricielle par la touche Entrée du clavier,

Le résultat tombe et il est parfaitement cohérent. Il s'agit bien de la différence entre le nombre total de personnes inscrites et le nombre total de personnes à l'étude. Et bien évidemment, si vous ajoutez de nouvelles personnes, les deux premiers calculs s'ajustent dynamiquement en cohérence. La syntaxe finale de la formule que nous avons construite pour comparer deux plages de cellules, est très simple :

=SOMMEPROD((NB.SI(Noms; Liste)=0)*1)

Repérer les données manquantes
Pour plus de clarté, nous proposons d'identifier visuellement les personnes du tableau non mentionnées dans la liste. Pour faire ressortir ces éléments en couleur dynamique, nous devons créer une règle de mise en forme conditionnelle. Et cette règle doit être bâtie sur toutes les données d'origine.

• Sélectionner toutes les informations du tableau, soit la plage de cellules D4:E14,
• Dans la section Styles du ruban Accueil, cliquer sur le bouton Mise en forme conditionnelle,
• En bas de la liste des propositions, choisir l'option Nouvelle règle,
• Dans la boîte de dialogue qui suit, sélectionner le type : Utiliser une formule pour ...,
• Puis, cliquer dans la zone du dessous pour l'activer,
• Dès lors, taper le symbole égal (=) pour initier la syntaxe de la règle de mise en forme,
• Inscrire la fonction de dénombrement conditionnel suivie d'une parenthèse, soit : Nb.Si(,
• Désigner la plage des personnes ajoutées par son nom, soit : Liste,
• Taper un point-virgule (;) pour passer dans l'argument de la donnée à compter,
• Sélectionner le premier nom du tableau en cliquant sur sa cellule D4, soit : $D$4,
• Puis, enfoncer deux fois la touche F4 du clavier pour la libérer en ligne, soit : $D4,

En effet, nous ne sommes plus dans un raisonnement matriciel. L'analyse d'une mise en forme conditionnelle est chronologique. C'est pourquoi nous débutons l'étude à partir du premier nom. Et pour qu'ils soient tous passés en revue afin d'être cherchés dans la liste annexe, nous libérons la cellule en ligne. C'est la raison pour laquelle le dollar ne subsiste plus que devant l'indice de colonne.

• Fermer la parenthèse de la fonction Nb.Si,
• Puis, ajouter le critère suivant :=0,

Si le dénombrement conduit à une valeur nulle, cela signifie que le nom en cours d'étude n'a pas été trouvé dans la liste annexe. Dans ces conditions, nous devons faire ressortir sa ligne dans une couleur explicitement différente.

• Cliquer sur le bouton Format en bas de la boîte de dialogue,
• Dans la boîte de dialogue qui suit, activer l'onglet Police,
• Avec la seconde liste déroulante, choisir un orange assez vif pour la couleur du texte,
• Valider ces réglages de format avec le bouton Ok,
• De retour sur la première boîte de dialogue, cliquer sur Ok pour créer la règle,

Une fois revenu sur la feuille, vous constatez que la couleur orange domine. Mais au fur et à mesure que vous ajoutez des personnes dans la liste annexe, en même temps que les calculs de dénombrement s'ajustent, les couleurs se dissipent. De plus en plus de données sont en effet trouvées d'une liste à une autre.