Comparer des données avec les tableaux croisés dynamiques

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


Sujets que vous pourriez aussi aimer :


Comparer grâce aux tableaux croisés dynamiques

Avec cette nouvelle astuce, nous allons apprendre à confronter plusieurs listes Excel. L'objectif est de déceler les données présentes dans certaines listes et absentes dans d'autres.



Dans l'exemple finalisé illustré par la capture, nous avons consolidé trois listes Excel avec un tableau croisé dynamique. Des indicateurs numériques montrent explicitement les présences et manquements de certaines données, en fonction du tableau d'origine.

Source et procédure
Pour démontrer l'intérêt de cette astuce, nous proposons tout d'abord de récupérer ces trois listes.

• Télécharger le classeur comparer-par-tableaux-croises.xlsx en cliquant sur ce lien,
• Double cliquer sur le fichier téléchargé pour l'ouvrir dans Excel,
• Puis, cliquer sur le bouton Activer la modification du bandeau de sécurité,

Nous trouvons une feuille sur laquelle sont inscrites trois listes. Elles sont toutes de la même taille mais pourraient très bien offrir des dimensions différentes. Cela ne remet pas en cause le procédé que nous allons démontrer. De plus, chacune de ces listes n'est constituée que de deux colonnes. Là aussi, nous pourrions très bien agir sur des tableaux de données denses, offrant de nombreuses colonnes. Les comparaisons doivent s'observer dans la première rangée de ces listes. Généralement, il s'agit des identifiants des enregistrements. Ici, ce sont les noms des personnes qui sont renseignées. Et nous devons savoir quelles sont celles qui sont présentes dans les trois listes et quelles sont celles qui sont absentes dans l'une ou l'autre. La deuxième colonne de chaque liste identifie le tableau par une lettre. C'est cette lettre qui servira dans la consolidation à observer les différences. Pour que ces informations puissent être croisées, nous devons commencer par les réunir, les unes en dessous des autres.

• Sélectionner le premier nom du deuxième tableau en cliquant sur sa cellule E4,
• Avec la touche MAJ (Shift) enfoncée, cliquer sur la dernière case du tableau, soit la cellule F21,

Cette technique permet d'englober dans la sélection, toutes les cellules comprises entre la première et la dernière.

• Copier la sélection avec le raccourci clavier CTRL + C,
• Sélectionner la première case vide après le premier tableau, soit la cellule B22,
• Coller la sélection avec le raccourci clavier CTRL + V,

Désormais, les deux premiers tableaux sont réunis. Nous devons ajouter les informations de la troisième liste.

• Sélectionner toutes les données du troisième tableau, soit la plage de cellules H4:I21,
• Les copier avec le raccourci clavier CTRL + C,
• Sélectionner la première case vide après le premier tableau, soit la cellule B40,
• Coller cette sélection avec le raccourci clavier CTRL + V,

Désormais, les trois listes sont parfaitement regroupées. Ensuite, pour construire un tableau croisé dynamique, la source de données doit d'abord être désignée explicitement.

• Sélectionner l'une des cellules du tableau, par exemple B4,
• En haut de la fenêtre Excel, cliquer sur l'onglet Insertion pour activer son ruban,
• Tout à fait à gauche du ruban, cliquer sur le bouton Tableau croisé dynamique,

Une boîte de dialogue surgit. Dans la zone Tableau/Plage, vous constatez qu'Excel a parfaitement borné le tableau à consolider, grâce à la présélection de l'une de ses cellules. Nous souhaitons construire cette analyse comparative sur la feuille en cours.

• En bas de la boîte de dialogue, cocher la case Feuille de calcul existante,
• Puis, sur la feuille, cliquer sur la cellule K3 pour la désigner,
• Cliquer alors sur le bouton Ok pour générer la construction du tableau croisé dynamique,

Comme vous le savez, la finalité d'un TCD (Tableau croisé dynamique) est de recouper des données pour offrir une vue synthétisée. C'est exactement ce à quoi nous souhaitons aboutir sur les noms listés afin de connaître les répartitions. Les noms des personnes doivent donc apparaître en ligne. Les noms des listes doivent apparaître en colonne pour identifier les recoupements sur la provenance. De fait, une opération de décompte doit être engagée sur ce croisement. Nous saurons ainsi, dans une vue très explicite, les personnes présentes dans les trois listes et celles absentes dans l'une ou l'autre.

Le tableau croisé dynamique est en cours de construction. Il attend les instructions de l'utilisateur pour placer les informations à recouper. C'est la raison pour laquelle un volet apparaît sur la droite de la fenêtre. Il est nommé Champs de tableau croisé dynamique.

• Dans ce volet, cocher la case Nom,

Nous ordonnons ainsi le placement des noms en ligne. Nous devons recouper ces données avec l'information sur le tableau d'origine.

• Cliquer et glisser le champ Table dans la section Colonnes,

Nous définissons ainsi l'organisation des données à recouper. Il reste à définir l'opération de synthèse à enclencher sur ce croisement.

• Glisser de nouveau le champ Table mais cette fois dans la section Valeurs,

Dans le tableau croisé dynamique ainsi construit, vous voyez des chiffres apparaître au croisement des noms et des tables. Ils indiquent précisément dans quelles sources de données les informations recoupées sont recensées. Lorsqu'un ou plusieurs chiffres sont manquants, nous savons instantanément qu'il s'agit d'une personne qui n'est pas recensée dans toutes les tables. Et le moins que l'on puisse dire est que rares sont les personnes communes aux trois listes de données. Instantanément, nous nous rendons compte de l'utilité des TCD pour recouper les données et dans ce contexte précis, pour les comparer.

Certes, il convient d'améliorer la mise en forme de ce tableau de synthèse.



Les présentations brutes des TCD laissent souvent à désirer.

• Cliquer sur la croix du volet pour fermer ce dernier,
• Cliquer droit sur le champ de synthèse (Total général) en dernière colonne,
• Dans la liste, choisir Supprimer le total général,

Ainsi, seules demeurent les informations de synthèse sur les recoupements opérés entre les trois sources. Ensuite, il convient éventuellement d'ajuster les dimensions des colonnes et d'adapter les couleurs de fond et de texte.



La synthèse livrée est judicieuse et évidente. Dès lors qu'un chiffre est manquant, nous savons que l'information n'est pas présente dans la source mentionnée en rappel par sa colonne. Lorsque les trois chiffres sont rappelés, nous savons que la personne est bien recensée dans les trois tableaux.

Désormais et pour parachever la solution, une mise en forme conditionnelle peut paraître opportune. Il peut par exemple s'agir de faire ressortir de façon explicitement différente toutes les personnes archivées dans les trois sources. Pour cela, nous devons bâtir une règle. Cette règle doit premièrement rechercher le nom de la personne dans le tableau croisé dynamique. Dans le même temps, elle doit s'assurer que le chiffre 1 est inscrit dans chacune des colonnes. Le plus simple consiste encore à effectuer la somme de ces chiffres pour la recherche honorée. Si cette somme conduit à un total de 3, nous saurons qu'il s'agit bien d'un enregistrement recoupé par les trois sources. Pour un tel raisonnement, la fonction SommeProd est dédiée. Nous devons donc insuffler des matrices conditionnelles à cette fonction SommeProd.

• Sélectionner la plage de cellules B4:H21,

Ainsi, nous englobons dans la sélection toutes les colonnes de noms à rechercher dans la synthèse fournie par le tableau croisé dynamique.

• Dans la section Styles du ruban Accueil, cliquer sur le bouton Mise en forme conditionnelle,
• En bas de la liste des propositions, opter pour la commande Nouvelle règle,
• Dans la boîte de dialogue qui suit, sélectionner le type : Utiliser une formule pour ...,
• Puis, cliquer dans la zone de saisie du dessous pour l'activer,
• Taper le symbole égal (=) pour initier la syntaxe de la règle,
• Inscrire le nom de la fonction suivi d'une parenthèse, soit : SommeProd(,
• Ouvrir une première parenthèse pour accueillir la première matrice conditionnelle,
• Sélectionner tous les noms du TCD, soit la plage de cellules $K$5:$K$39,
• Taper le symbole égal (=) pour annoncer la condition à honorer,
• Désigner le premier nom de la sélection en cliquant sur sa cellule B4, soit : $B$4,
• Enfoncer trois fois la touche F4 du clavier pour libérer totalement la cellule, soit : B4,

En effet et vous le savez, l'analyse d'une mise en forme conditionnelle est chronologique. Toutes les cellules de la sélection doivent être passées en revue pour être cherchées dans le tableau de synthèse. C'est donc la raison pour laquelle nous la libérons afin de la laisser suivre le mouvement de l'analyse de la règle.

• Fermer la parenthèse de cette première matrice conditionnelle,
• Taper le symbole de l'étoile (*) pour annoncer la condition à recouper,
• Ouvrir une nouvelle parenthèse pour accueillir la nouvelle matrice conditionnelle,
• Désigner la première source synthétisée en sélectionnant sa plage de cellules $L$5:$L$39,
• Taper le symbole plus (+) pour la consolidation à suivre,
• Désigner la deuxième source synthétisée en sélectionnant sa plage de cellules $M$5:$M$39,
• Taper le symbole plus (+) pour la dernière consolidation,
• Désigner la troisième source synthétisée en sélectionnant sa plage de cellules $N$5:$N$39,

Dans ce raisonnement matriciel, lorsqu'un nom est trouvé, la fonction SommeProd répond par le booléen Vrai équivalent au chiffre 1. Recoupé avec le critère cumulé sur les indices en regard, elle livrera la somme des chiffres inscrits pour la personne trouvée, par exemple 1*(1 + 1 + 1). Si le résultat vaut 3, nous savons que la personne en question appartient bien aux trois listes. Nous devons vérifier ce critère.

• Fermer la parenthèse de la seconde matrice conditionnelle,
• Puis, fermer la parenthèse de la fonction SommeProd,
• Ensuite, taper l'égalité suivante: =3, pour la condition à honorer,

La syntaxe que nous venons de construire est donc la suivante :

=SommeProd(($K$5:$K$39=B4)*($L$5:$L$39 + $M$5:$M$39 + $N$5:$N$39))=3

Lorsque cette multiple condition est honorée, nous devons faire ressortir la cellule concernée dans des attributs de format explicitement différents.

• En bas de la boîte de dialogue, cliquer sur le bouton Format,
• Dans la boîte de dialogue qui suit, activer l'onglet Remplissage,
• Dans la palette de couleurs, choisir un bleu assez soutenu pour le fond de la cellule,
• Activer alors l'onglet Police de la boîte de dialogue,
• Avec la liste déroulante, choisir un gris clair pour le texte,
• Puis, valider ces attributs par le bouton Ok,

Nous sommes de retour sur la première boîte de dialogue. Elle résume parfaitement le contexte. Tout élément commun aux trois tableaux doit surgir dans les régalages de mise en forme ainsi définis.

• Valider la création de cette règle en cliquant sur le bouton Ok de la boîte de dialogue,

De retour sur la feuille, vous notez que toutes les personnes effectivement recensées dans les trois sources sont dynamiquement surlignées. Et c'est bien sûr grâce au recoupement réalisé par le tableau croisé dynamique que nous avons pu comparer aussi simplement ces trois sources de données. Et d'ailleurs, la technique aurait été aussi évidente quel que soit le nombre de tableaux à comparer.

Si nous avions voulu mettre en évidence les personnes présentes seulement dans l'une des sources ou dans deux d'entre elles, il aurait suffi d'adapter le critère d'égalité :

=SommeProd(($K$5:$K$39=B4)*($L$5:$L$39 + $M$5:$M$39 + $N$5:$N$39))=2
=SommeProd(($K$5:$K$39=B4)*($L$5:$L$39 + $M$5:$M$39 + $N$5:$N$39))=1