Formule pour comparer des données dans Excel. Comment comparer deux colonnes dans Excel – Méthodes de comparaison de données Excel

Maison / Routeurs

Comparons deux tables avec presque la même structure. Les tableaux diffèrent par les valeurs des lignes individuelles ; certains noms de lignes apparaissent dans un tableau, mais peuvent ne pas l'être dans un autre.

Que ce soit sur les draps Janvier Et Février Il existe deux tableaux avec le chiffre d'affaires de la période pour les comptes correspondants.

Comme le montrent les figures, les tableaux diffèrent :

La présence (absence) de lignes (noms de compte). Par exemple, dans un tableau sur une feuille Janvier il n'y a pas de compte 26 (voir fichier exemple), et dans le tableau sur la feuille Février le compte 10 et ses sous-comptes sont manquants.
Différentes valeurs dans les lignes. Par exemple, selon le compte 57, le chiffre d'affaires de janvier et février ne correspond pas.

Si les structures des tables sont à peu près les mêmes (la plupart des noms de comptes (lignes) sont les mêmes, le nombre et les noms des colonnes sont les mêmes), alors vous pouvez comparer les deux tables. Faisons une comparaison de deux manières : l’une est plus simple à mettre en œuvre, l’autre est plus visuelle.

Une option simple pour comparer 2 tableaux

Tout d’abord, déterminons quelles lignes (noms de compte) sont présentes dans une table mais pas dans une autre. Ensuite, dans le tableau où il manque le moins de lignes (le tableau le plus complet), nous afficherons un rapport comparatif représentant l'écart par colonne (l'écart de chiffre d'affaires de janvier et février).

Le principal inconvénient de cette approche est que le rapport de comparaison des tableaux n'inclut pas les lignes manquantes dans le tableau le plus complet. Par exemple, dans le cas que nous considérons, le plus tableau complet est un tableau sur une feuille de calcul Janvier, dans lequel le compte 26 du tableau de février manque.

Pour déterminer lequel des deux tableaux est le plus complet, vous devez répondre à 2 questions : Quels comptes du tableau de février manquent dans le tableau de janvier ? et Quels comptes du tableau de janvier manquent dans le tableau de janvier ?

Cela peut être fait à l'aide de formules (voir colonne E) : = SI(END(RECHERCHEV(A7,janvier!$A$7:$A$81,1,0));"Non", "Oui") et = SI(END(RECHERCHEV(A7,février!$A$7:$A$77,1,0));"Non", "Oui")

Nous comparerons la rotation des comptes à l'aide des formules : = SI(END(RECHERCHEV($A7,février!$A$7:$C77,2,0)),0,RECHERCHEV($A7,février!$A$7:$C77,2,0))-B7 et = SI(END(RECHERCHEV($A7,février!$A$7:$C77,3,0)),0,RECHERCHEV($A7,février!$A$7:$C77,3,0))-C7

S'il n'y a pas de ligne correspondante, la fonction VLOOKUP() renvoie l'erreur #N/A, qui est traitée par une combinaison des fonctions END() et IF(), en remplaçant l'erreur par 0 (si la ligne est manquante) ou avec une valeur de la colonne correspondante.

Vous pouvez l'utiliser pour mettre en évidence les écarts (par exemple, en rouge).

Une option plus visuelle pour comparer 2 tableaux (mais plus complexe)

Par analogie avec le problème résolu dans l'article, vous pouvez générer une liste de noms de comptes, comprenant TOUS les noms de comptes des deux tables (sans répétitions). Affichez ensuite la différence par colonne.

Pour ce faire, vous avez besoin de :

En utilisant = SIERREUR(SIERREUR(INDEX(Janvier,MATCH(0,COUNTIF(A$4:$A4,Janvier),0)), INDEX(Février,MATCH(0,COUNTIF(A$4:$A4,Février),0))) ;"") créer une liste de comptes à partir des deux tableaux de la colonne A (sans répétitions) ;
En utilisant = SIERREUR(INDEX(Liste, MATCH(PETIT(COUNTIF(Liste, "<"&Список); СТРОКА()-СТРОКА($B$4)); СЧЁТЕСЛИ(Список; "<"&Список); 0));"") , où Liste est une liste de comptes des deux tables (colonne A), comptes obtenus à l'étape précédente ;
En utilisant la formule = SI(END(RECHERCHEV($B5,janvier!$A$7:$C$81,2,0)),0,RECHERCHEV($B5,janvier!$A$7:$C$81,2,0)) - SI( UND(RECHERCHEV($B5,février!$A$7:$C$77,2,0)),0,VLOOKUP($B5,février!$A$7:$C$77,2,0)) comparer la rotation des comptes ;
Utilisez la couleur pour mettre en évidence les écarts, ainsi que pour mettre en évidence les comptes trouvés uniquement dans un seul tableau (par exemple, dans la figure ci-dessus, les comptes contenus uniquement dans le tableau de janvier sont surlignés en bleu et les comptes uniquement du tableau de février sont surlignés en jaune).

La tâche consiste souvent à comparer deux listes d’éléments. Faire cela manuellement est trop fastidieux et la possibilité d'erreurs ne peut pas non plus être exclue. Excel facilite cette opération. Cette astuce décrit une méthode utilisant la mise en forme conditionnelle.

Sur la fig. La figure 164.1 montre un exemple de deux listes de noms à plusieurs colonnes. L’utilisation de la mise en forme conditionnelle peut rendre évidentes les différences dans les listes. Ces exemples de listes contiennent du texte, mais la méthode en question fonctionne également avec des données numériques.

La première liste est A2:B31, cette plage est appelée Ancienne liste. La deuxième liste est D2:E31, la plage s'appelle NouvelleListe. Les plages ont été nommées à l'aide de la commande Formules Noms définis Attribuer un nom. Il n'est pas nécessaire de nommer les plages, mais cela facilite leur utilisation.

Commençons par ajouter une mise en forme conditionnelle à l'ancienne liste.

Sélectionner des cellules dans une plage Ancienne liste.
Sélectionner.
Dans la fenêtre Créer une règle de formatage sélectionnez l'élément appelé Utiliser la formule
Entrez cette formule dans le champ de la fenêtre (Fig. 164.2) : =COUNTIF(NewList;A2)=0.
Cliquez sur le bouton Format et spécifiez le formatage qui sera appliqué lorsque la condition est vraie. Il est préférable de choisir différentes couleurs de remplissage.
Cliquez sur OK.

Cellules à portée NouvelleListe utilisez une formule de mise en forme conditionnelle similaire.

Sélectionner des cellules dans une plage NouvelleListe.
Sélectionner Accueil Mise en forme conditionnelle Créer une règle pour ouvrir une boîte de dialogue Créer une règle de formatage.
Dans la fenêtre Créer une règle formatage de l'élément de sélection Utiliser la formule pour définir les cellules à formater.
Entrez cette formule dans le champ de la fenêtre : =COUNTIF(OldList;D2)=0 .
Cliquez sur le bouton Format et définissez la mise en forme à appliquer lorsque la condition est vraie (couleur de remplissage différente).
Cliquez sur OK.

En conséquence, les noms qui figurent dans l'ancienne liste, mais pas dans la nouvelle, seront mis en évidence (Fig. 164.3). De plus, les noms de la nouvelle liste qui ne figurent pas dans l'ancienne liste sont également mis en évidence, mais dans une couleur différente. Les noms apparaissant dans les deux listes ne sont pas mis en surbrillance.

Les deux formules de mise en forme conditionnelle utilisent la fonction COUNTIF. Il calcule le nombre de fois qu'une certaine valeur apparaît dans une plage. Si la formule renvoie 0, cela signifie que l'élément n'est pas dans la plage. De cette façon, la mise en forme conditionnelle prend le relais et la couleur d’arrière-plan de la cellule change.

Très souvent, les utilisateurs d'Excel sont confrontés à la tâche de comparer deux tableaux ou listes pour identifier les différences ou les éléments manquants. Chaque utilisateur s'acquitte de cette tâche à sa manière, mais le plus souvent, un temps assez important est consacré à la résolution de ce problème, car toutes les approches de ce problème ne sont pas rationnelles. Dans le même temps, il existe plusieurs algorithmes d'action éprouvés qui vous permettront de comparer des listes ou des tableaux en un temps assez court avec un minimum d'effort. Examinons de plus près ces options.

Il existe de nombreuses façons de comparer les zones d'un tableau dans Excel, mais elles peuvent toutes être divisées en trois grands groupes :

comparaison de listes situées sur une même feuille ;

comparaison de tableaux situés sur différentes feuilles ;

comparaison des plages de tableaux dans différents fichiers.

C'est sur la base de cette classification que les méthodes de comparaison sont tout d'abord sélectionnées et que des actions et des algorithmes spécifiques pour accomplir la tâche sont déterminés. Par exemple, lorsque vous effectuez des comparaisons entre différents classeurs, vous devez ouvrir deux fichiers Excel en même temps.

De plus, il faut dire qu'il est logique de comparer les zones de table uniquement lorsqu'elles ont une structure similaire.

Méthode 1 : formule simple

Le moyen le plus simple de comparer les données de deux tableaux consiste à utiliser une simple formule d’égalité. Si les données correspondent, alors elles renvoient VRAI, et sinon, alors FAUX. Vous pouvez comparer des données numériques et textuelles. L'inconvénient de cette méthode est qu'elle ne peut être utilisée que si les données du tableau sont ordonnées ou triées de la même manière, synchronisées et comportent un nombre égal de lignes. Voyons comment utiliser cette méthode en pratique en utilisant l'exemple de deux tableaux placés sur une même feuille.

Nous avons donc deux tableaux simples avec des listes d'employés de l'entreprise et leurs salaires. Il est nécessaire de comparer les listes d'employés et d'identifier les incohérences entre les colonnes dans lesquelles se trouvent les noms.

Pour ce faire, nous avons besoin d'une colonne supplémentaire sur la feuille. Écrivez un signe là-bas «=» . Cliquez ensuite sur le prénom que vous souhaitez comparer dans la première liste. Nous remettons le symbole «=» depuis le clavier. Cliquez ensuite sur la première cellule de la colonne que nous comparons dans le deuxième tableau. Le résultat est une expression du type suivant :

Bien sûr, dans chaque cas spécifique, les coordonnées seront différentes, mais l'essence restera la même.
Cliquez sur la clé Entrer pour obtenir des résultats de comparaison. Comme vous pouvez le voir, en comparant les premières cellules des deux listes, le programme a indiqué l'indicateur "VRAI", ce qui signifie que les données correspondent.

Nous devons maintenant effectuer une opération similaire avec les cellules restantes des deux tableaux dans les colonnes que nous comparons. Mais vous pouvez simplement copier la formule, ce qui vous fera gagner beaucoup de temps. Ce facteur est particulièrement important lors de la comparaison de listes comportant un grand nombre de lignes.
La procédure de copie est plus facilement réalisée en utilisant le marqueur de remplissage. Nous déplaçons le curseur vers le coin inférieur droit de la cellule où nous avons reçu l'indicateur "VRAI". En même temps, elle devrait se transformer en une croix noire. C'est le marqueur de remplissage. Cliquez sur le bouton gauche de la souris et faites glisser le curseur vers le bas du nombre de lignes dans les tableaux comparés.

Comme vous pouvez le constater, la colonne supplémentaire affiche désormais tous les résultats de la comparaison des données dans deux colonnes de tableaux tabulaires. Dans notre cas, les données d’une seule ligne ne correspondaient pas. En les comparant, la formule a donné le résultat "MENSONGE". Pour toutes les autres lignes, comme on le voit, la formule de comparaison a donné l'indicateur "VRAI".

De plus, il est possible de calculer le nombre d'écarts à l'aide d'une formule spéciale. Pour cela, sélectionnez l'élément de la feuille où il sera affiché. Cliquez ensuite sur l'icône "Insérer une fonction".

Dans la fenêtre Assistants de fonctions dans le groupe des opérateurs "Mathématique" surligne le nom SOMME PRODUIT. Cliquez sur le bouton "D'ACCORD".

La fenêtre des arguments de la fonction est activée SOMME PRODUIT, dont la tâche principale est de calculer la somme des produits de la gamme sélectionnée. Mais cette fonction peut également être utilisée à nos fins. Sa syntaxe est assez simple :
SOMMEPRODUIT(tableau1,tableau2,…)

Au total, jusqu'à 255 adresses de tableaux peuvent être utilisées comme arguments. Mais dans notre cas, nous n'utiliserons d'ailleurs que deux tableaux comme un seul argument.

Placez le curseur dans le champ "Tableau1" et sélectionnez la plage de données à comparer dans la première zone de la feuille. Après cela, mettez un panneau sur le terrain "pas égal" (<> ) et sélectionnez la plage comparée de la deuxième zone. Ensuite, nous mettons l'expression résultante entre parenthèses, avant quoi nous mettons deux signes «-» . Dans notre cas, nous avons l'expression suivante :

--(A2:A7<>D2:D7)

Cliquez sur le bouton "D'ACCORD".

L'opérateur effectue le calcul et affiche le résultat. Comme vous pouvez le constater, dans notre cas le résultat est égal au nombre "1", c'est-à-dire que cela signifie qu'une incohérence a été trouvée dans les listes comparées. Si les listes étaient complètement identiques, alors le résultat serait égal au nombre «0» .

De la même manière, vous pouvez comparer les données de tableaux situés sur différentes feuilles. Mais dans ce cas, il est souhaitable que les lignes qu'ils contiennent soient numérotées. Sinon, la procédure de comparaison est presque exactement la même que celle décrite ci-dessus, à l'exception du fait que lors de la saisie de la formule, vous devrez basculer entre les feuilles. Dans notre cas, l'expression ressemblera à ceci :

B2=Feuil2!B2

C'est-à-dire, comme on le voit, avant que les coordonnées des données, qui se trouvent sur d'autres feuilles, différentes de celle où le résultat de la comparaison est affiché, soient indiquées le numéro de feuille et un point d'exclamation.

Méthode 2 : sélectionner des groupes de cellules

Des comparaisons peuvent être effectuées à l'aide de l'outil de sélection de groupe de cellules. Il peut également comparer uniquement des listes synchronisées et ordonnées. De plus, dans ce cas les listes doivent être situées les unes à côté des autres sur une même feuille.

Méthode 3 : mise en forme conditionnelle

Vous pouvez effectuer une comparaison en utilisant la méthode de formatage conditionnel. Comme dans la méthode précédente, les zones comparées doivent se trouver sur la même feuille de calcul Excel et être synchronisées entre elles.

Il existe une autre façon d'utiliser la mise en forme conditionnelle pour accomplir la tâche. Comme les options précédentes, elle nécessite la localisation des deux zones comparées sur la même feuille, mais contrairement aux méthodes décrites précédemment, la condition de synchronisation ou de tri des données ne sera pas obligatoire, ce qui distingue cette option de celles décrites précédemment.

Si vous le souhaitez, vous pouvez au contraire colorer les éléments qui ne correspondent pas, et laisser les indicateurs qui correspondent avec la même couleur de remplissage. Dans ce cas, l'algorithme des actions est presque le même, mais dans la fenêtre des paramètres, les valeurs répétitives sont mises en évidence dans le premier champ au lieu du paramètre. "Récurrent" vous devriez sélectionner l'option "Unique". Après cela, cliquez sur le bouton "D'ACCORD".

Ainsi, exactement les indicateurs qui ne correspondent pas seront mis en évidence.

Méthode 4 : formule complexe

Vous pouvez également comparer les données à l'aide d'une formule complexe basée sur la fonction COUNTIF. À l'aide de cet outil, vous pouvez calculer combien de fois chaque élément de la colonne sélectionnée du deuxième tableau est répété dans le premier.

Opérateur COUNTIF appartient au groupe statistique des fonctions. Sa tâche est de compter le nombre de cellules dont les valeurs satisfont à une condition donnée. La syntaxe de cet opérateur est la suivante :

COUNTIF(plage, critère)

Argument "Gamme" est l'adresse du tableau dans lequel les valeurs correspondantes sont comptées.

Argument "Critère" spécifie la condition de correspondance. Dans notre cas, il représentera les coordonnées de cellules spécifiques de la première zone du tableau.

Bien entendu, cette expression peut être utilisée sous sa forme existante pour comparer des indicateurs tabulaires, mais il est possible de l'améliorer.

Assurons-nous que les valeurs qui se trouvent dans le deuxième tableau, mais pas dans le premier, sont affichées dans une liste séparée.

Tout d’abord retravaillons un peu notre formule COUNTIF, à savoir faisons-en un des arguments de l’opérateur SI. Pour cela, sélectionnez la première cellule dans laquelle se trouve l'opérateur COUNTIF. Dans la barre de formule, ajoutez l'expression devant "SI" sans guillemets et ouvrez la parenthèse. Ensuite, pour nous faciliter le travail, mettez en surbrillance la valeur dans la barre de formule "SI" et cliquez sur l'icône "Insérer une fonction".

La fenêtre des arguments de la fonction s'ouvre SI. Comme vous pouvez le constater, le premier champ de la fenêtre est déjà rempli avec la valeur de l'opérateur COUNTIF. Mais nous devons ajouter autre chose à ce domaine. Placez-y le curseur et ajoutez à l'expression existante «=0» sans guillemets.
Après cela, nous passons au terrain "Valeur si vrai". Ici, nous utiliserons une autre fonction imbriquée - DOUBLER. Entrez le mot "DOUBLER" sans guillemets, puis ouvrez les parenthèses et indiquez les coordonnées de la première cellule avec le nom de famille dans le deuxième tableau, après quoi nous fermons les parenthèses. Plus précisément dans notre cas sur le terrain "Valeur si vrai" on obtient l'expression suivante :

LIGNE(D2)

Maintenant l'opérateur DOUBLER signalera les fonctions SI le numéro de la ligne dans laquelle se trouve un nom de famille spécifique, et dans le cas où la condition précisée dans le premier champ est remplie, la fonction SI affichera ce numéro dans la cellule. Cliquez sur le bouton "D'ACCORD".

Comme nous pouvons le voir, le premier résultat s'affiche sous la forme "MENSONGE". Cela signifie que la valeur ne satisfait pas aux conditions de l'opérateur SI. Autrement dit, le premier nom de famille est présent dans les deux listes.

À l'aide du marqueur de remplissage, nous copions l'expression de l'opérateur de la manière habituelle SI pour toute la colonne. Comme vous pouvez le constater, pour deux positions présentes dans le deuxième tableau mais pas dans le premier, la formule produit des numéros de ligne.

Nous nous éloignons de la zone du tableau à droite et remplissons la colonne avec des nombres dans l'ordre, en commençant par 1 . Le nombre de nombres doit correspondre au nombre de lignes du deuxième tableau comparé. Vous pouvez également utiliser un marqueur de remplissage pour accélérer le processus de numérotation.

Après cela, sélectionnez la première cellule à droite de la colonne avec les chiffres et cliquez sur l'icône "Insérer une fonction".

Ouvre Assistant de fonction. Aller à la catégorie "Statistique" et sélectionnez un nom "MOINS". Cliquez sur le bouton "D'ACCORD".

Fonction MOINS, dont la fenêtre d'argument a été ouverte, est destinée à afficher la plus petite valeur spécifiée par compte.
Dans le champ "Tableau" vous devez spécifier les coordonnées de la plage de la colonne supplémentaire "Nombre de matchs", que nous avons précédemment converti à l'aide de la fonction SI. Nous rendons tous les liens absolus.

Dans le champ "K" indique la plus petite valeur à sortir. Nous indiquons ici les coordonnées de la première cellule de la colonne numérotée que nous avons récemment ajoutée. Nous laissons l'adresse relative. Cliquez sur le bouton "D'ACCORD".

L'opérateur affiche le résultat - un nombre 3 . Il s'agit de la plus petite numérotation des lignes de tableaux qui ne correspondent pas. À l'aide du marqueur de remplissage, copiez la formule tout en bas.

Maintenant, connaissant les numéros de ligne des éléments qui ne correspondent pas, nous pouvons insérer leurs valeurs dans la cellule à l'aide de la fonction INDICE. Sélectionnez le premier élément de la feuille contenant la formule MOINS. Après cela, allez dans la barre de formule et avant le nom "MOINS" ajouter le nom "INDICE" sans guillemets, ouvrez immédiatement la parenthèse et mettez un point-virgule ( ; ). Sélectionnez ensuite le nom dans la barre de formule "INDICE" et cliquez sur l'icône "Insérer une fonction".

Après cela, une petite fenêtre s'ouvre dans laquelle vous devez déterminer quel type de référence la fonction doit avoir INDICE ou conçu pour fonctionner avec des tableaux. Nous avons besoin d'une deuxième option. Il est installé par défaut, donc dans cette fenêtre on clique simplement sur le bouton "D'ACCORD".

La fenêtre des arguments de la fonction s'ouvre INDICE. Cet opérateur est conçu pour afficher une valeur située dans un tableau spécifique sur une ligne spécifiée.
Comme on le voit, le terrain "Numéro de ligne" déjà rempli de valeurs de fonction MOINS. De la valeur qui y existe déjà, vous devez soustraire la différence entre la numérotation de la feuille Excel et la numérotation interne de la zone du tableau. Comme vous pouvez le voir, nous n'avons qu'un en-tête au-dessus des valeurs du tableau. Cela signifie que la différence est d'une ligne. On ajoute donc dans le champ "Numéro de ligne" signification "-1" sans guillemets.

Dans le champ "Tableau" indiquer l'adresse de la plage de valeurs du deuxième tableau. En même temps, nous rendons toutes les coordonnées absolues, c'est-à-dire que nous plaçons un signe dollar devant elles de la manière que nous avons décrite précédemment.

Cliquez sur le bouton "D'ACCORD".

Après avoir affiché le résultat à l'écran, utilisez le marqueur de remplissage pour faire glisser la fonction jusqu'à la fin de la colonne. Comme vous pouvez le constater, les deux noms de famille, présents dans le deuxième tableau mais pas dans le premier, sont affichés dans une plage distincte.

Méthode 5 : Comparaison de tableaux dans différents classeurs

Lorsque vous comparez des plages dans différents classeurs, vous pouvez utiliser les méthodes répertoriées ci-dessus, en excluant les options dans lesquelles vous devez placer les deux zones de tableau sur la même feuille. La condition principale pour effectuer la procédure de comparaison dans ce cas est d'ouvrir les fenêtres des deux fichiers en même temps. Pour les versions d'Excel 2013 et ultérieures, ainsi que pour les versions antérieures à Excel 2007, il n'y a aucun problème pour remplir cette condition. Mais dans Excel 2007 et Excel 2010, pour ouvrir les deux fenêtres en même temps, des manipulations supplémentaires sont nécessaires. Comment procéder est décrit dans une leçon séparée.

Comme vous pouvez le constater, il existe de nombreuses possibilités pour comparer les tableaux entre eux. L'option à utiliser dépend de l'emplacement exact des données tabulaires les unes par rapport aux autres (sur la même feuille, dans différents classeurs, sur différentes feuilles), ainsi que de la manière exacte dont l'utilisateur souhaite que cette comparaison soit affichée à l'écran.

Question d'un utilisateur

Bonjour!

J'ai une tâche, et depuis le troisième jour maintenant, je me creuse la tête - je ne sais pas comment la terminer. Il y a 2 tableaux (environ 500 à 600 lignes chacun), vous devez prendre la colonne avec le nom du produit d'une table et la comparer avec le nom du produit de l'autre, et si les produits correspondent, copier et collez la valeur du tableau 2 dans le tableau 1. Expliqué de manière confuse, mais je pense que vous comprendrez la tâche à partir de la photo ( env. : la photo a été découpée par la censure, c'est toujours une information personnelle).

Merci d'avance. Andreï, Moscou.

Bonne journée à tous !

Ce que vous avez décrit fait référence à des tâches assez courantes qui sont relativement faciles et rapides à résoudre à l'aide d'Excel. Tout ce que vous avez à faire est de mettre vos deux tables dans le programme et d'utiliser la fonction RECHERCHEV. Plus d’informations sur son travail ci-dessous…

Un exemple de travail avec la fonction RECHERCHEV

A titre d'exemple, j'ai pris deux petits panneaux, ils sont montrés dans la capture d'écran ci-dessous. Dans le premier tableau (colonnes A, B- produit et prix) aucune donnée sur la colonne B; dans la seconde, les deux colonnes (produit et prix) sont remplies. Vous devez maintenant vérifier les premières colonnes des deux tableaux et automatiquement, si une correspondance est trouvée, copier le prix dans le premier tableau. Cela semble être une tâche simple...

Comment faire...

Placez le pointeur de la souris dans une cellule B2- c'est-à-dire dans la première cellule de la colonne où nous n'avons aucune valeur et écrivons la formule :

=RECHERCHEV(A2,$E$1:$F$7,2,FALSE)

A2- la valeur de la première colonne du premier tableau (ce que l'on recherchera dans la première colonne du deuxième tableau) ;

1 $E$ : $F$7- un deuxième tableau complètement sélectionné (dans lequel on veut trouver et copier quelque chose). Faites attention au signe "$" - il est nécessaire pour que lors de la copie de la formule, les cellules du deuxième tableau sélectionné ne changent pas ;

2 - le numéro de la colonne à partir de laquelle nous copions la valeur (notez que notre deuxième table sélectionnée n'a que 2 colonnes. Si elle avait 3 colonnes, alors la valeur pourrait être copiée à partir de la 2ème ou de la 3ème colonne) ;

MENSONGE- nous recherchons une correspondance exacte (sinon le premier similaire sera substitué, ce qui ne nous convient évidemment pas).

En fait, vous pouvez ajuster la formule finie à vos besoins en la modifiant légèrement. Le résultat de la formule est présenté dans l'image ci-dessous : le prix a été trouvé dans le deuxième tableau et saisi en mode auto. Tout fonctionne !

Pour définir le prix d’autres noms de produits, étendez (copiez) simplement la formule à d’autres cellules. Exemple ci-dessous.

Après quoi, comme vous pouvez le constater, les premières colonnes des tableaux seront comparées : à partir des lignes où les valeurs des cellules coïncident, les données nécessaires seront copiées et remplacées. De manière générale, force est de constater que les tables peuvent être bien plus grandes !

Note: Je dois dire que la fonction RECHERCHEV est assez gourmande en ressources informatiques. Dans certains cas, avec un document trop volumineux, la comparaison des tableaux peut prendre beaucoup de temps. Dans ces cas, il convient d'envisager soit d'autres formules, soit des solutions complètement différentes (chaque cas est individuel).

C'est tout, bonne chance !

Supposons que vous souhaitiez comparer les versions d'un classeur, analyser un classeur à la recherche de problèmes ou d'incohérences, ou afficher les liens entre des classeurs ou des feuilles de calcul. Si Microsoft Office 365 ou Office Professionnel Plus 2013 est installé sur votre ordinateur, le complément Spreadsheet Inquire est disponible dans Excel.

Vous pouvez utiliser les commandes de l'onglet Demande de renseignements pour effectuer toutes ces tâches, et bien plus encore. L'onglet Demande de renseignements du ruban Excel comporte des boutons pour les commandes décrites ci-dessous.

Si vous ne voyez pas le Renseigner dans le ruban Excel, voir Activer le complément Spreadsheet Inquire .

Comparez deux classeurs

Le Comparer les fichiers La commande vous permet de voir les différences, cellule par cellule, entre deux classeurs. Vous devez avoir deux classeurs ouverts dans Excel pour exécuter cette commande.

Les résultats sont codés par couleur selon le type de contenu, tel que les valeurs saisies, les formules, les plages nommées et les formats. Il existe même une fenêtre qui peut afficher les modifications du code VBA ligne par ligne. Les différences entre les cellules sont affichées dans une disposition de grille facile à lire, comme ceci :

Le Comparer les fichiers La commande utilise Microsoft Spreadsheet Compare pour comparer les deux fichiers. Sous Windows 8, vous pouvez démarrer Spreadsheet Compare en dehors d'Excel en cliquant sur Comparaison de feuilles de calcul sur le Applicationsécran. Sous Windows 7, cliquez sur le bouton Windows Commencer bouton puis > Tous les programmes > Microsoft Office 2013 > Outils Office 2013 > Comparaison des feuilles de calcul 2013.

Pour en savoir plus sur la comparaison de feuilles de calcul et la comparaison de fichiers, lisez Comparer deux versions d’un classeur.

Analyser un classeur

Le Analyse du classeur La commande crée un rapport interactif affichant des informations détaillées sur le classeur et sa structure, ses formules, ses cellules, ses plages et ses avertissements. L'image ici montre un classeur très simple contenant deux formules et des connexions de données à une base de données Access et un fichier texte.

Afficher les liens du classeur

Les classeurs connectés à d’autres classeurs via des références de cellules peuvent prêter à confusion. Utilisez le pour créer une carte graphique interactive des dépendances de classeur créées par les connexions (liens) entre les fichiers. Les types de liens dans le diagramme peuvent inclure d'autres classeurs, bases de données Access, fichiers texte, pages HTML, bases de données SQL Server et autres sources de données. Dans le diagramme de relations, vous pouvez sélectionner des éléments et trouver plus d'informations à leur sujet, ainsi que faire glisser les lignes de connexion pour modifier la forme du diagramme.

Ce diagramme montre le classeur actuel sur la gauche et les connexions entre celui-ci et d'autres classeurs et sources de données. Il affiche également des niveaux supplémentaires de connexions au classeur, vous donnant une image des origines des données du classeur.

Afficher les liens vers les feuilles de calcul

Vous avez beaucoup de feuilles de travail qui dépendent les unes des autres ? Utilisez le pour créer une carte graphique interactive des connexions (liens) entre les feuilles de calcul du même classeur et d'autres classeurs. Cela vous donne une idée plus claire de la façon dont vos données peuvent dépendre de cellules situées à d’autres endroits.

Ce diagramme montre les relations entre les feuilles de calcul de quatre classeurs différents, avec des dépendances entre les feuilles de calcul du même classeur ainsi que des liens entre les feuilles de calcul de différents classeurs. Lorsque vous placez votre pointeur sur un nœud du diagramme, tel que la feuille de calcul nommée « Ouest » dans le diagramme, une bulle contenant des informations apparaît.

Afficher les relations entre les cellules

Pour obtenir un diagramme détaillé et interactif de tous les liens d'une cellule sélectionnée vers des cellules d'autres feuilles de calcul ou même d'autres classeurs, utilisez l'option Relation cellulaire outil. Ces relations avec d'autres cellules peuvent exister dans des formules ou des références à des plages nommées. Le diagramme peut traverser des feuilles de calcul et des classeurs.

Ce diagramme montre deux niveaux de relations entre les cellules pour la cellule A10 sur la feuille 5 dans Book1.xlsx. Cette cellule dépend de la cellule C6 de la feuille 1 d'un autre classeur, Book2.xlsx. Cette cellule constitue un précédent pour plusieurs cellules d'autres feuilles de calcul du même fichier.

Pour en savoir plus sur l’affichage des relations entre les cellules, lisez Voir les liens entre les cellules.

Nettoyer l'excès de formatage des cellules

Avez-vous déjà ouvert un classeur et constaté qu'il se charge lentement ou qu'il est devenu énorme ? Il se peut qu'une mise en forme soit appliquée à des lignes ou des colonnes dont vous n'êtes pas au courant. Nettoyer le formatage des cellules en excès commande pour supprimer le formatage excessif et réduire considérablement la taille du fichier. Cela vous aide à éviter le « gonflement des feuilles de calcul », ce qui améliore la vitesse d'Excel.

Gérer les mots de passe

Si vous utilisez les fonctionnalités d'Inquire pour analyser ou comparer des classeurs protégés par mot de passe, vous devrez ajouter le mot de passe du classeur à votre liste de mots de passe afin qu'Inquire puisse ouvrir la copie enregistrée de votre classeur. Utilisez le Mots de passe du classeur commande sur le Renseigner onglet pour ajouter des mots de passe, qui seront enregistrés sur votre ordinateur. Ces mots de passe sont cryptés et accessibles uniquement par vous.