Comparer et consolider deux tableaux Excel

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


Sujets que vous pourriez aussi aimer :


Comparer et consolider deux tableaux Excel

Dans cette formation, nous proposons de regrouper des connaissances acquises au travers de différents supports afin de résoudre un cas d'entreprise spécifique mais courant. La société gère un parc automobile conséquent. Chaque véhicule est fort logiquement identifié par son immatriculation, soit un identifiant unique.



Les services et prestataires sont nombreux expliquant les dissonances qui existent entre certaines bases de données réalisant le suivi des véhicules.

Sources de données et problématique
Les manipulations et techniques que nous proposons pourront facilement s'appliquer à n'importe quel type de données, pour toute entreprise donc. Nous devons commencer par récupérer ces données.

• Télécharger l'archive comparer-consolider-excel.rar en cliquant sur son lien,
• La décompresser dans le dossier de votre choix,

La décompression conduit à deux fichiers. Le classeur Excel comparer-consolider-excel.xlsm est l'outil de suivi et de gestion de l'entreprise, dans lequel figure la source de données officielle. Le fichier externe.txt quant à lui, résulte d'une extraction réalisée depuis une base de données utilisée par les prestataires. Bien entendu, ces deux sources devront être réunies dans le même classeur pour que l'étude comparative puisse être réalisée.

• Double cliquer sur le fichier comparer-consolider-excel.xlsm pour l'ouvrir dans Excel,
• Si nécessaire, cliquer sur les boutons Activer la modification et Activer le contenu des rubans de sécurité,

L'extension de ce fichier (xlsm) permet de gérer les macros et le code Visual Basic qui leur est associé. Ce classeur est constitué de deux feuilles. La feuille Interne propose un tableau recensant les véhicules du parc automobiles. Il s'agit de la source de données officielle qui a volontairement été restreinte. Comme l'illustre la capture ci-dessus, trois champs permettent de décrire ces véhicules. Le dernier champ (Corr.) est conçu pour initier les travaux de comparaison. La feuille externe est vide quant à elle. Comme son nom l'indique, elle est prévue pour accueillir les données issues des gestionnaires de bases externes.

Nous proposons donc d'importer la source de données externe à partir de laquelle il s'agira de comparer et si possible de consolider les données. Excel propose des fonctionnalités permettant de regrouper les enregistrements afin de combler les références manquantes. Mais ici, l'objectif est d'automatiser ces manipulations à l'issue, afin de faciliter tous les futurs traitements.

Importer des données CSV
Le format csv retranscrit les informations de champs séquencées par des séparateurs de listes, comme le point-virgule, pour tous les enregistrements. Il permet une communication universelle entre tous les logiciels manipulant des bases de données.

• Cliquer sur l'onglet externe en bas de la fenêtre Excel pour activer sa feuille,
• Sélectionner la cellule A1 pour désigner le point de départ de l'importation,
• Cliquer sur l'onglet données en haut de la fenêtre Excel pour activer son ruban,
• Tout à fait à gauche du ruban, cliquer sur le bouton Données externes,
• Dans la liste, choisir Fichier texte,
• Double cliquer sur le fichier externe.txt précédemment téléchargé,
• Dans la boîte de dialogue qui suit, conserver cochée la case Délimité et cliquer sur Suivant,
• Dans l'étape qui suit, décocher la case Tabulation puis cocher la case Point-virgule,

Nous indiquons ainsi comment Excel doit reconstruire les colonnes depuis cette source. Vous remarquez d'ailleurs que l'aperçu en bas de la boîte de dialogue se met instantanément à jour. Désormais, les informations de champs semblent explicitement identifiées et séparées.

• Cliquer de nouveau sur Suivant puis sur Terminer,
• Dans la dernière étape, confirmer la cellule A1 pour l'importation en validant par Ok,

Comme vous le constatez, les données sont parfaitement importées et reconstituées dans trois colonnes correspondant aux trois champs identiques à ceux de la source d'origine. Néanmoins, les noms des champs sont manquants en entête du tableau. Nous proposons donc de les ajouter et de réaliser une rapide mise en forme.

• Activer la feuille Interne,
• Sélectionner les trois premières cellules de titre, soit la plage A1:C1,
• Les copier à l'aide du raccourci clavier CTRL + C par exemple,
• Revenir sur la feuille Externe et cliquer avec le bouton droit sur l'étiquette de ligne 1,
• Dans le menu contextuel, choisir Insérer les cellules copiées,
• Dans la boîte de dialogue qui suit, cocher la case Décaler les cellules vers le bas,
• Puis, valider par Ok,

Nous récupérons ainsi les titres du tableau source pour y voir plus clair.

• Cliquer dans l'une des cellules de titre pour désigner le tableau, par exemple B1,
• Dans la section Styles du ruban Accueil, cliquer sur le bouton Mettre sous forme de tableau,
• Dans les visuels proposés, choisir un style moyen orangé par exemple,
• Dans la boîte de dialogue qui suit, valider la sélection proposée en cliquant sur Ok,
• Puis, confirmer le message relatif aux données externes en cliquant sur Oui,

Les données sont plus joliment présentées et de fait semblent mieux structurées. Cette mise en forme automatique est très pratique pour mettre en valeur des tableaux de bases de données. En revanche, les références des cellules sont transformées en plages nommées. Or, pour les besoins des calculs de comparaison, nous préférons manipuler ces plages avec leurs références d'origine. Pour ce faire, nous pouvons exploiter les options du ruban Création Contextuel qui est visible lorsque l'une des cellules du tableau est active.

• Sélectionner l'une des cellules du tableau, par exemple B2,
• Cliquer sur l'onglet Création en haut de la fenêtre Excel pour activer son ruban,
• Dans la section Outils, cliquer sur le bouton Convertir en plages et valider le message qui suit,

Nous retrouvons un tableau normal tout en conservant les attributs de mise en forme automatique qui ont été répliqués. Vous remarquez que les flèches des filtres automatiques ont disparu.

Nous obtenons donc deux tableaux de structures identiques mais d'origines différentes. Celui de la feuille interne représente le tableau officiel. Celui de la feuille externe représente le tableau des données à compléter.

Comparaison de tableaux - Afficher les non correspondances
La formation Excel sur la facturation automatisée nous avait appris à exploiter une fonction de recherche très puissante, la fonction RechercheV. Elle permet d'extraire des données d'un tableau en fonction d'un élément recherché. Sa syntaxe est la suivante :

=RechercheV(element_cherché ; tableau_de_recherche ; colonne_de_retour ; Faux)

L'élément de recherche est l'immatriculation du véhicule. Le tableau de recherche est celui de la feuille externe. La colonne de retour est le numéro de la rangée par rapport à la sélection, dans laquelle se trouve l'élément correspondant à extraire. Comme nous souhaitons simplement confirmer que le véhicule existe, nous nous contenterons de récupérer l'immatriculation elle-même, soit la colonne 1. Le booléen Faux en dernier paramètre force la fonction à réaliser une recherche selon une correspondance exacte.

Depuis la colonne Corr. (D) de la feuille interne donc, nous allons récupérer l'immatriculation recherchée si elle existe dans le tableau de la feuille interne et afficher un message textuel le cas échéant. Lorsque la fonction RechercheV ne trouve aucune correspondance, elle retourne un message d'erreur. Pour gérer cette erreur et la remplacer par un texte, nous allons l'imbriquer dans une fonction SiErreur.

• Cliquer sur l'onglet Interne en bas de la fenêtre Excel pour activer sa feuille,
• Sélectionner la cellule D2 et taper le symbole = pour débuter le calcul,
• Saisir le nom de la fonction d'erreur suivi d'une parenthèse, soit SiErreur(,
• Saisir le nom de la fonction de recherche suivi d'une parenthèse, soit RechercheV(,
• Cliquer la cellule A2 pour désigner l'élément cherché, soit l'immatriculation,
• Taper un point-virgule (;) pour passer à l'argument suivant de la fonction,
• Cliquer sur l'onglet Externe en bas de la fenêtre Excel pour activer sa feuille,
• Supprimer le préfixe Externe! inscrit automatiquement par Excel dans la formule,

En effet, pour que la recherche des non correspondances puisse s'adapter aux futures sources de données, nous ne pouvons pas nous contenter de désigner un tableau aux bornes fixes. Nous allons exploiter la fonction Excel Decaler comme nous l'avait appris la formation Excel pour créer des listes déroulantes dynamiques et évolutives. Cette dernière associée à la fonction NbVal pour comptabiliser les cellules non vides, permettra de décaler la borne inférieure du tableau, jusqu'à intégrer la dernière saisie, quelle que soit la taille du tableau importé.

• Saisir le nom de la fonction suivi d'une parenthèse, soit Decaler(,
• Cliquer dans la cellule de référence pour la colonne de recherche, soit A1,
• Enfoncer la touche F4 du clavier pour figer cette cellule,

Notez qu'Excel replace aussitôt le préfixe de la feuille devant la référence figée ($A$1) de la cellule désignée. Les deux arguments suivants pour la fonction Decaler, permettent de définir les décalages de départ en ligne et en colonne. Nous n'en voulons pas, puisqu'il s'agit de considérer la recherche dès la première immatriculation.

• En conséquence, saisir ;0;0; à la suite,
• Puis taper le nom de la fonction de dénombrement suivi d'une parenthèse, soit NbVal(,
• Cliquer sur l'étiquette de la colonne A pour la désigner, ce qui donne Externe!A:A,
• Fermer la parenthèse de la fonction NbVal,
• Fermer la parenthèse de la fonction Decaler pour revenir dans la fonction RechercheV,
• Taper un point-virgule (;) pour passer à l'argument du numéro de colonne,
• Saisir le chiffre 1 suivi d'un point-virgule (;) pour passer au dernier argument de la fonction RechercheV,
• Taper le texte Faux pour une correspondance exacte,
• Fermer la parenthèse de la rechercheV pour revenir dans la fonction SiErreur,
• Taper un point-virgule et saisir le texte nok entre guillemets, soit 'nok',

Donc si une erreur est générée du fait que la fonction RechercheV ne trouve aucune correspondance, nous indiquons à la fonction SiErreur d'inscrire l'information nok en lieu et place dans la cellule du résultat.

• Fermer la parenthèse de la fonction SiErreur,
• Puis, valider le calcul par le raccourci CTRL + Entrée pour conserver la cellule active,
• Double cliquer sur la poignée de la cellule pour répliquer le calcul sur la hauteur du tableau,

La formule complète que nous venons de bâtir est la suivante :

=SIERREUR(RECHERCHEV(A2; DECALER(Externe!$A$1; 0; 0; NBVAL(Externe!A:A)); 1; FAUX); 'nok')



Comme vous le constatez, la densité des messages nok en retour est importante. Elle traduit les nombreuses discordances entre les deux sources de données et notamment le manque de fiabilité de la source externe.

Mettre en évidence les non correspondances
Le résultat offert par l'extraction de la rechercheV est efficace. Cependant, il reste assez complexe de réaliser une synthèse rapide dans la mesure où les données qui nous intéressent, sont éparpillées sur toute la hauteur du tableau. Nous souhaitons appliquer un format conditionnel sur toutes les immatriculations qui ne sont pas trouvées dans la première source de données. Ainsi, elles ressortiront en couleur et ces couleurs s'adapteront aux données des futures extractions.

• Sélectionner la plage de cellules A2:C89,
• Dans le ruban Accueil, cliquer sur le bouton Mise en forme conditionnelle,
• Dans la liste, choisir Nouvelle règle,
• Dans les types de la boîte de dialogue qui suit, sélectionner le dernier, soit Utiliser une formule pour déterminer...,
• Cliquer dans la zone de saisie située juste en dessous pour l'activer,
• Cliquer la première cellule du calcul précédent pour la désigner, soit D2,
• Enfoncer deux fois la touche F4 du clavier pour la défiger en ligne,
• Taper le symbole = suivi du terme nok entre guillemets, soit ='nok',

Concrètement, nous sommes en train de définir la condition selon laquelle la ligne toute entière doit être mise en valeur. C'est pourquoi nous devons personnaliser les paramètres de mise en forme dans la foulée.

• Cliquer sur le bouton Format situé juste en dessous,
• Dans la boîte de dialogue qui suit, activer l'onglet Remplissage,
• Choisir un violet clair pour le fond des cellules correspondant au critère,
• Valider par Ok pour revenir aux réglages de la mise en forme conditionnelle,
• Valider de nouveau par Ok pour confirmer la règle dynamique,
• De retour sur la feuille, cliquer droit sur l'étiquette de la colonne D,
• Dans le menu contextuel qui apparaît, choisir Masquer (ou Cacher selon la version),

Regrouper les données non correspondantes
Grâce aux réglages de la mise en forme conditionnelle, la proportion des données discordantes est plus flagrante. Les couleurs attirent l'oeil instantanément et rendent compte de l'importance des données à mettre à jour. Néanmoins, elles sont toujours éparpillées sur la hauteur du tableau. Nous proposons d'offrir à l'utilisateur la possibilité de les regrouper par simple clic. Comme nous l'avait appris la formation Excel sur les équations à plusieurs inconnues, il est possible d'exclure facilement de la sélection, toutes les données ne correspondant pas au code couleur du format dynamique.

• Sélectionner l'une des cellules du tableau, B2 par exemple,
• Tout à fait à droite dans le ruban Accueil, cliquer sur le bouton Trier et filtrer,
• Dans la liste, choisir Filtrer,

Cette action a pour effet de faire apparaître les flèches de filtres automatiques sur la ligne d'entête du tableau.

• Cliquer sur la flèche de la colonne Immat. (Colonne A),
• Dans la liste, pointer sur Filtrer par couleur puis cliquer sur l'icône de la couleur,

Instantanément, toutes les données correspondant à la mise en valeur dynamique du format conditionnel sont regroupées. En d'autres termes, tous les véhicules dont l'immatriculation du tableau interne n'est pas trouvée dans le tableau externe, sont réunies. Il est ainsi plus simple de traiter les non correspondances issues de la comparaison des deux tableaux. D'ailleurs la barre d'état en bas de la fenêtre Excel renseigne sur la proportion, puisqu'elle indique que 33 enregistrements non correspondants ont été extraits.

Justement, associé à ce résultat visuel, il pourrait être précieux de combiner un résultat de synthèse, dans une cellule située en dessous du tableau. A chaque filtre automatique, son objectif est d'afficher clairement le nombre d'enregistrements et donc de véhicules correspondant aux critères. Souvenez, la formation Excel des calculs sur les données filtrées nous avait appris à exploiter la fonction Sous.Total. Cette fonction a la capacité de s'adapter au contexte en excluant du calcul les lignes filtrées, donc masquées.

• Cliquer de nouveau sur la flèche du filtre de la colonne Immat.,
• Dans la liste, choisir Effacer le filtre de Immat.,

Ainsi, tous les enregistrements réapparaissent.

• Réaliser le raccourci clavier CTRL + Fin pour atteindre la dernière cellule active de la feuille,
• Sélectionner la cellule C91 et taper le symbole = pour débuter le calcul,
• Taper le nom de la fonction de synthèse suivi d'une parenthèse, soit sous.total(,
• Saisir le chiffre 3 pour demander le décompte des cellules non vides,
• Taper un point-virgule (;) pour passer à l'argument de la plage de cellules,
• Sélectionner la plage de cellules C2:C89, soit toute la colonne du tableau,
• Fermer la parenthèse de la fonction Sous.Total et valider le calcul,

La fonction retourne le nombre 88 pour désigner les 88 véhicules recensés par défaut dans cette petite base de données, volontairement compactée.

• Réaliser le raccourci clavier CTRL + Home (Flèche orientée Nord-Ouest) pour revenir tout en haut de la feuille,
• Cliquer de nouveau sur la flèche de filtre de la colonne Immat.,
• Dans la liste, pointer sur Filtrer par couleur puis cliquer sur l'icône de couleur,
• Et réaliser le raccourci CTRL + Fin pour atteindre la synthèse,

La fonction Sous.Total s'est en effet adaptée aux lignes masquées par le filtre et corrobore l'information de la barre d'état. Pour éviter de naviguer entre les extrémités de la feuille, nous aurions aussi pu choisir de placer ce résultat de synthèse sur les lignes du dessus, dans des colonnes situées à droite du tableau.

Extraire et consolider les non correspondances
Comme nous l'avait appris la formation Excel pour filtrer les bases de données, les filtres avancés, tout en offrant des critères plus puissants, proposent d'extraire les données correspondantes plutôt que de les filtrer sur place. Pour les exploiter, nous devons prévoir une zone de critères ainsi qu'une zone d'extraction répliquant les mêmes noms de champs que la source de données. Et pour ce faire, nous devons commencer par couper les filtres automatiques.

• Cliquer sur l'onglet Données en haut de la fenêtre Excel pour activer son ruban,
• Cliquer sur le bouton Filtrer pour les désactiver,

Les flèches de filtres automatiques disparaissent en effet, et si ce n'était pas déjà le cas, toutes les données du tableau réapparaissent.



Nous devons maintenant créer une zone de critère en colonne H ainsi qu'une zone d'extraction entre les colonnes I et K, comme l'illustre la capture ci-dessus. Ces noms de champs doivent être strictement identiques à ceux de la source de données. Pour éviter toute erreur, il est préférable de les copier.

• Copier (CTRL + V) la cellule D1 en H1 (CTRL + V),
• Saisir l'information : nok en D2,
• Copier la plage de cellules A1:C1 en I1,
• Cliquer dans le tableau source, par exemple B2, pour le désigner,
• Dans la section Trier et filtrer du ruban Données, cliquer sur le bouton Avancé,
• Dans la boîte de dialogue qui apparaît, conserver la zone Plages telle qu'elle est proposée,
• Cliquer dans la zone de critères et remplacer son contenu en sélectionnant la plage H1:H2,
• Cocher la case Copier vers un autre emplacement,
• Puis, remplacer le contenu de la zone Copier dans en sélectionnant la plage I1:K1,
• Valider par Ok pour extraire les résultats correspondant au critère,

Comme vous pouvez le voir, tous les véhicules manquants dans la source externe sont parfaitement extraits sous les champs de la zone d'extraction.

Automatiser la consolidation
Puisque la mise en forme conditionnelle et le calcul de non correspondance sont en place, nous pourrions proposer un bouton de macro à l'utilisateur. Au clic, nous produirions ainsi l'extraction des données non correspondantes ainsi que leur consolidation dans le tableau de la feuille externe. Mais pour que les actions de macros puissent s'adapter quelle que soit la longueur du tableau, nous allons devoir les simuler à l'aide de raccourcis claviers, notamment pour les positionnements. Si le ruban Développeur n'est pas présent dans vos onglets, vous pouvez vous référer à la formation pour débuter la programmation en Visual Basic Excel.

• Cliquer sur l'onglet Développeur en haut de la fenêtre Excel, pour activer son ruban,
• Dans la section Code, à gauche du ruban, cliquer sur le bouton Enregistrer une macro,
• Dans la zone Nom de la macro de la boîte de dialogue qui suit, taper : extraire,
• Cliquer sur le bouton Ok pour débuter l'enregistrement,
• Sélectionner l'une des cellules du tableau, par exemple B4,
• Dans le ruban Données, cliquer sur le bouton Avancé,
• Conserver la plage proposée par défaut pour la détection des bornes du tableau,
• Dans la zone de critères, spécifier la plage de cellules H1:H2,
• Cocher la case Copier vers un autre emplacement,
• Dans la zone Copier dans, spécifier de nouveau la plage I1:K1,
• Cliquer sur Ok pour réaliser l'extraction,
• Sélectionner la première cellule des données extraites, soit I2,
• Réaliser le raccourci clavier CTRL + MAJ + Fin pour englober toutes les données extraites,
• Les copier (CTRL + C) puis cliquer sur l'onglet Externe en bas de la fenêtre pour activer sa feuille,
• Réaliser le raccourci CTRL + Fin pour atteindre la toute dernière cellule de cette feuille,
• Descendre d'une ligne avec la flèche orientée vers le bas du pavé directionnel,
• Enfoncer la touche Home (Flèche orientée Nord-Ouest) pour activer la première cellule de cette dernière ligne,
• Réaliser le raccourci CTRL + V pour coller les données extraites,
• Utiliser éventuellement la balise active pour coller sans mise en forme,
• Dans le ruban Développeur, cliquer sur le bouton Arrêter l'enregistrement,
• Supprimer les données consolidées par la simulation de la macro,
• Revenir sur la feuille Interne,
• Dans la section Contrôles du ruban Développeur, cliquer sur le bouton Insérer,
• Le tracer entre le tableau source et la zone d'extraction, entre les lignes 2 et 3,
• Dans la boîte de dialogue qui apparaît, sélectionner la macro extraire pour faire le lien,
• Puis cliquer sur ce bouton de macros,

Comme vous le remarquez, les actions s'enchainent automatiquement. Les données non correspondantes sont parfaitement extraites dans un premier temps. Ensuite, elles sont copiées tout en bas de la feuille externe afin de consolider cette seconde source de données. Désormais donc, à chaque importation d'une nouvelle source de données, l'utilisateur n'a plu qu'à cliquer sur le bouton pour la mettre à jour automatiquement. A l'issue il ne restera qu'à l'extraire pour la livrer.