Comparer deux tables

Nous avons deux tableaux (par exemple, l'ancienne et la nouvelle version de la liste de prix), que nous devons comparer et trouver rapidement les différences :

Il est immédiatement clair que quelque chose a été ajouté à la nouvelle liste de prix (dattes, ail…), quelque chose a disparu (mûres, framboises…), les prix ont changé pour certains produits (figues, melons…). Vous devez trouver et afficher rapidement tous ces changements.

Pour toute tâche dans Excel, il existe presque toujours plusieurs solutions (généralement 4-5). Pour notre problème, de nombreuses approches différentes peuvent être utilisées :

• fonction VPR (VLOOKUP) - recherchez les noms de produits de la nouvelle liste de prix dans l'ancienne et affichez l'ancien prix à côté du nouveau, puis saisissez les différences
• fusionner deux listes en une seule, puis créer un tableau croisé dynamique basé sur celle-ci, où les différences seront clairement visibles
• utiliser le complément Power Query pour Excel

Prenons-les tous dans l'ordre.

Méthode 1. Comparer des tables avec la fonction VLOOKUP

Si vous n'êtes pas du tout familier avec cette merveilleuse fonctionnalité, regardez d'abord ici et lisez ou regardez un didacticiel vidéo à ce sujet - économisez quelques années de vie.

En règle générale, cette fonction est utilisée pour extraire des données d'une table à une autre en faisant correspondre un paramètre commun. Dans ce cas, nous l'utiliserons pour pousser les anciens prix dans le nouveau prix :

Ces produits, pour lesquels l'erreur #N/A s'est avérée, ne figurent pas dans l'ancienne liste, c'est-à-dire qu'ils ont été ajoutés. Les changements de prix sont également clairement visibles.

Avantages cette méthode : simple et claire, « classique du genre », comme on dit. Fonctionne dans n'importe quelle version d'Excel.

Inconvénients est également là. Pour rechercher des produits ajoutés à la nouvelle liste de prix, vous devrez effectuer la même procédure dans le sens inverse, c'est-à-dire remonter les nouveaux prix à l'ancien prix à l'aide de VLOOKUP. Si les tailles des tables changent demain, alors les formules devront être ajustées. Eh bien, et sur de très grandes tables (> 100 XNUMX rangées), tout ce bonheur ralentira décemment.

Méthode 2 : Comparer des tableaux à l'aide d'un pivot

Copions nos tableaux les uns sous les autres, en ajoutant une colonne avec le nom de la liste de prix, afin que plus tard vous puissiez comprendre à partir de quelle liste quelle ligne :

Maintenant, sur la base du tableau créé, nous allons créer un résumé via Insérer – Tableau croisé dynamique (Insérer - Tableau croisé dynamique). Lançons un champ Produit au domaine des lignes, champ Prix à la zone de colonne et au champ Цena dans la gamme :

Comme vous pouvez le voir, le tableau croisé dynamique générera automatiquement une liste générale de tous les produits des anciennes et nouvelles listes de prix (pas de répétitions !) et triera les produits par ordre alphabétique. Vous pouvez clairement voir les produits ajoutés (ils n'ont pas l'ancien prix), les produits supprimés (ils n'ont pas le nouveau prix) et les changements de prix, le cas échéant.

Les totaux généraux dans un tel tableau n'ont pas de sens, et ils peuvent être désactivés sur l'onglet Constructeur - Totaux généraux - Désactiver pour les lignes et les colonnes (Conception — Totaux généraux).

Si les prix changent (mais pas la quantité de marchandises !), il suffit alors de simplement mettre à jour le récapitulatif créé en faisant un clic droit dessus – Refresh.

Avantages: Cette approche est un ordre de grandeur plus rapide avec de grandes tables que VLOOKUP. 

Inconvénients: vous devez copier manuellement les données les unes sous les autres et ajouter une colonne avec le nom de la liste de prix. Si la taille des tables change, vous devez tout recommencer.

Méthode 3 : Comparer des tables avec Power Query

Power Query est un complément gratuit pour Microsoft Excel qui vous permet de charger des données dans Excel à partir de presque toutes les sources, puis de transformer ces données de la manière souhaitée. Dans Excel 2016, ce complément est déjà intégré par défaut dans l'onglet Données (Données), et pour Excel 2010-2013, vous devez le télécharger séparément du site Web de Microsoft et l'installer - obtenez un nouvel onglet Requête d'alimentation.

Avant de charger nos listes de prix dans Power Query, elles doivent d'abord être converties en tables intelligentes. Pour ce faire, sélectionnez la plage avec des données et appuyez sur la combinaison du clavier Ctrl+T ou sélectionnez l'onglet sur le ruban Accueil – Format sous forme de tableau (Accueil — Format sous forme de tableau). Les noms des tables créées peuvent être corrigés dans l'onglet Constructeur (je laisserai la norme Tableau 1 и Tableau 2, qui sont obtenus par défaut).

Charger l'ancien prix dans Power Query à l'aide du bouton À partir du tableau/plage (À partir du tableau/plage) de l'onglet Données (Date) ou depuis l'onglet Requête d'alimentation (selon la version d'Excel). Après le chargement, nous reviendrons à Excel depuis Power Query avec la commande Fermer et charger – Fermer et charger en… (Fermer et charger — Fermer et charger dans…):

… et dans la fenêtre qui apparaît alors sélectionnez Créez simplement une connexion (Connexion uniquement).

Répétez la même chose avec la nouvelle liste de prix. 

Créons maintenant une troisième requête qui combinera et comparera les données des deux précédentes. Pour cela, sélectionnez dans Excel sur l'onglet Données – Obtenir des données – Combiner des demandes – Combiner (Données — Obtenir des données — Fusionner des requêtes — Fusionner) ou appuyez sur le bouton Combiner (Fusionner) languette Requête d'alimentation.

Dans la fenêtre de jointure, sélectionnez nos tables dans les listes déroulantes, sélectionnez les colonnes contenant les noms des marchandises et, en bas, définissez la méthode de jointure - Externe complet (Extérieur complet):

Après avoir cliqué sur OK un tableau de trois colonnes devrait apparaître, où dans la troisième colonne, vous devez développer le contenu des tableaux imbriqués à l'aide de la double flèche dans l'en-tête :

En conséquence, nous obtenons la fusion des données des deux tables :

Il est préférable, bien sûr, de renommer les noms de colonnes dans l'en-tête en double-cliquant sur les plus compréhensibles :

Et maintenant le plus intéressant. Aller à l'onglet Ajouter une colonne (Ajouter une colonne) et cliquez sur le bouton Colonne conditionnelle (Colonne conditionnelle). Et puis dans la fenêtre qui s'ouvre, entrez plusieurs conditions de test avec leurs valeurs de sortie correspondantes :

Il reste à cliquer sur OK et téléchargez le rapport résultant vers Excel en utilisant le même bouton fermer et télécharger (Fermer et charger) languette Accueil (Accueil):

Beauté.

De plus, si des changements surviennent dans les listes de prix à l'avenir (des lignes sont ajoutées ou supprimées, les prix changent, etc.), il suffira alors de mettre à jour nos demandes avec un raccourci clavier Ctrl+autre+F5 ou par bouton Rafraîchir tout (Rafraîchir tout) languette Données (Date).

Avantages: Peut-être le moyen le plus beau et le plus pratique de tous. Fonctionne intelligemment avec de grandes tables. Ne nécessite pas de modifications manuelles lors du redimensionnement des tableaux.

Inconvénients: Nécessite l'installation du complément Power Query (dans Excel 2010-2013) ou Excel 2016. Les noms de colonne dans les données source ne doivent pas être modifiés, sinon nous obtiendrons l'erreur "La colonne telle et telle n'a pas été trouvée!" lors de la tentative de mise à jour de la requête.

• Comment collecter des données à partir de tous les fichiers Excel dans un dossier donné à l'aide de Power Query
• Comment trouver des correspondances entre deux listes dans Excel
• Fusionner deux listes sans doublons