Comment utiliser la fonction RechercheV pour comparer deux listes dans des feuilles de calcul séparées ?

RechercheV pour comparer deux listes dans des feuilles de calcul distinctes avec des formules

Une approche pratique et directe pour comparer des listes situées dans différentes feuilles de calcul Excel consiste à utiliser la fonction RECHERCHEV. Cette méthode vous aide à extraire ou marquer efficacement tous les noms trouvés dans Noms-1 et Noms-2 :

1. Dans la feuille Noms-1, choisissez une cellule adjacente à vos données (par exemple, cellule B2) et entrez la formule suivante :

=VLOOKUP(A2,'Names-2'!$A$2:$A$19,1,FALSE)

Appuyez ensuite sur Entrée. Si le nom dans la ligne actuelle existe dans Noms-2, la formule renvoie le nom ; sinon, une erreur #N/A sera affichée. Voir l'exemple ci-dessous :

2. Copiez la formule vers le bas en faisant glisser la poignée de remplissage pour comparer chaque nom dans Noms-1 avec tous les noms dans Noms-2. Les entrées correspondantes afficheront le nom, tandis que celles non trouvées afficheront une valeur d'erreur :

Remarques :

1. Pour plus de clarté, vous pourriez utiliser cette formule alternative pour retourner des indicateurs "Oui" ou "Non" pour les correspondances :

=IF(ISNA(VLOOKUP(A2,'Names-2'!$A$2:$A$19,1,FALSE)), "No", "Yes")

Cette formule affiche "Oui" pour les noms présents dans les deux feuilles et "Non" pour ceux uniquement trouvés dans Noms-1 :

2. Lorsque vous utilisez ces formules, remplacez A2 par la première cellule de votre liste, Noms-2 par le nom de la feuille de référence, et ajustez $A$2:$A$19 pour correspondre à la plage de données réelle dans votre feuille de calcul. N'oubliez pas que les plages doivent commencer et se terminer avec les bons numéros de lignes pour inclure toutes vos données.

3. Conseils d'utilisation : Si vous rencontrez des erreurs #N/A là où il devrait y avoir des correspondances, vérifiez attentivement les problèmes potentiels causés par des espaces supplémentaires, des différences de formatage des données (texte vs nombre), ou des fautes de frappe dans vos listes. Utilisez TRIM ou CLEAN dans une colonne d'aide pour nettoyer les données si nécessaire.

4. Pour éviter des écrasements accidentels, pensez à sauvegarder vos données avant d'appliquer des formules en bloc. De plus, après la comparaison, vous pouvez utiliser un filtre sur votre colonne de résultats de formule pour afficher rapidement toutes les correspondances ou éléments uniques.

RechercheV pour comparer deux listes dans des feuilles de calcul distinctes avec Kutools pour Excel

Si vous avez Kutools pour Excel, sa fonction Sélectionner les mêmes et différentes cellules peut vous aider à comparer rapidement deux listes provenant de différentes feuilles de calcul et sélectionner ou mettre en surbrillance les noms communs entre ces deux feuilles - sans entrer de formules complexes. Cette méthode est particulièrement efficace lorsque vous travaillez avec de grandes quantités de données ou souhaitez un résultat visuel codé par couleur facile à interpréter en un coup d'œil.

Après avoir installé Kutools pour Excel, suivez ces étapes pour comparer facilement vos listes :

1. Allez dans l'onglet Kutools, puis cliquez sur Sélectionner > Sélectionner les mêmes et différentes cellules comme indiqué ci-dessous :

2. Dans la boîte de dialogue Sélectionner les mêmes et différentes cellules qui s'ouvre :

(1.) Sous Rechercher les valeurs dans, sélectionnez la plage depuis Noms-1 que vous devez comparer ;

(2.) Sous Selon, sélectionnez la plage depuis Noms-2 pour la comparaison ;

(3.) Dans la section Basé sur, choisissez Par ligne pour comparer respectivement les lignes ;

(4.) Depuis la section Rechercher, sélectionnez Valeurs identiques pour identifier et mettre en surbrillance les noms correspondants ;

(5.) Optionnellement, vous pouvez définir une couleur d'arrière-plan ou de police pour mettre en surbrillance les résultats et faire ressortir visuellement les correspondances.

3. Cliquez sur Ok, et vous verrez une boîte de dialogue indiquant combien de cellules correspondantes ont été trouvées et mises en surbrillance. Tous les noms présents dans les deux listes seront sélectionnés et visuellement mis en évidence, simplifiant ainsi toute revue ou modification ultérieure :

Cliquez pour télécharger et essayer gratuitement Kutools pour Excel Maintenant !

Conseils pratiques : Si vos feuilles de calcul contiennent de grands ensembles de données, envisagez d'utiliser la fonction de filtrage après mise en surbrillance pour revoir rapidement uniquement les correspondances. De plus, avant d'exécuter la comparaison, vérifiez que vos sélections de plage sont correctement alignées et n'incluent pas les lignes d'en-tête, sauf intention, car les inadéquations peuvent affecter les résultats.

Dans de rares cas, si la fonction ne retourne pas les résultats attendus, vérifiez si les deux listes sont formatées de la même manière (par exemple, toutes deux en tant que texte, sans espaces cachés au début/à la fin), car les écarts de formatage peuvent causer des correspondances manquées.

Mise en forme conditionnelle avec formule entre feuilles

Si vous préférez ne pas écrire de formules dans des colonnes ou utiliser des add-ins, vous pouvez utiliser la mise en forme conditionnelle avec une formule personnalisée pour identifier visuellement les noms correspondants dans une feuille sur la base des données d'une autre feuille. Cette méthode est simple et ne nécessite pas de VBA, mais ne retourne pas une liste de résultats distincte – elle met simplement en forme les correspondances pour une revue rapide à vue.

Scénarios applicables : Cette solution convient parfaitement aux utilisateurs souhaitant un indicateur visuel non intrusif des valeurs correspondantes sans modifier la structure des feuilles de calcul. La limitation est que les règles de mise en forme conditionnelle ne peuvent pas référencer directement un autre classeur, et le référencement de formules entre feuilles fonctionne uniquement dans le même fichier.

Étapes :

1. Dans Noms-1, sélectionnez la plage à laquelle vous souhaitez appliquer la mise en surbrillance (par exemple, A2:A19).

2. Allez dans Accueil > Mise en forme conditionnelle > Nouvelle règle > Utiliser une formule pour déterminer quelles cellules mettre en forme.

3. Dans la zone de formule, entrez la formule suivante :

=COUNTIF('Names-2'!$A$2:$A$19,A2)>0

Cela vérifie si la valeur dans A2 de Noms-1 existe quelque part dans Noms-2 !A2:A19.

4. Cliquez sur Format pour choisir une couleur de mise en surbrillance, puis cliquez sur OK pour appliquer la règle. Toutes les correspondances seront automatiquement mises en surbrillance dans votre plage sélectionnée.

Conseils pratiques : Vous pouvez ajuster les plages en fonction de vos données réelles, et l'étape NB.SI peut être combinée avec un filtrage pour se concentrer uniquement sur les cellules mises en surbrillance. Assurez-vous que les deux feuilles de calcul sont dans le même classeur lors de la configuration des références croisées entre feuilles, car Excel ne prend pas en charge les règles de mise en forme conditionnelle faisant référence à des fichiers externes.

Rappels d'erreur : Si les mises en surbrillance n'apparaissent pas comme prévu, vérifiez vos sélections de plage et les références croisées entre feuilles pour détecter des erreurs. Assurez-vous qu'il n'y ait pas d'espaces au début/à la fin ou d'incohérences de format causant des correspondances manquées. Si nécessaire, utilisez TRIM dans une colonne d'aide pour nettoyer les listes afin d'obtenir une comparaison précise.

Code VBA - Comparer automatiquement des listes et mettre en surbrillance ou extraire les correspondances

Pour les utilisateurs à l'aise avec les macros, l'utilisation de code VBA offre une méthode hautement flexible et automatisée pour comparer deux listes dans des feuilles de calcul distinctes. Cette approche vous permet de mettre en surbrillance les noms correspondants ou d'extraire les valeurs correspondantes à un nouvel emplacement, ce qui peut être particulièrement utile pour manipuler de grandes quantités de données ou obtenir des mises à jour rapides lorsque vos listes changent.

Scénarios applicables : Cette solution est particulièrement efficace lorsque vous souhaitez exécuter des comparaisons de manière répétée, manipuler de très grands jeux de données, automatiser les rapports, ou personnaliser davantage le traitement ou la présentation des correspondances. Bien que des connaissances en VBA soient nécessaires, vous bénéficiez d'une automatisation et d'un contrôle complets. Un inconvénient est que les macros doivent être activées dans le classeur, ce qui peut ne pas être autorisé dans tous les environnements en raison des paramètres de sécurité.

Comment exécuter la macro pour mettre en surbrillance les correspondances dans Noms-1 si elles sont présentes dans Noms-2 :

1. Cliquez sur Outils de développement > Visual Basic pour lancer la fenêtre Microsoft Visual Basic pour Applications. Dans la fenêtre, cliquez sur Insérer > Module et collez le code suivant dans le nouveau module :

Sub HighlightMatchingNames()
    Dim ws1 As Worksheet
    Dim ws2 As Worksheet
    Dim rng1 As Range
    Dim cell As Range
    Dim matchFound As Range
    
    On Error Resume Next
    xTitleId = "KutoolsforExcel"
    
    Set ws1 = Worksheets("Names-1")
    Set ws2 = Worksheets("Names-2")
    
    Set rng1 = ws1.Range("A2", ws1.Cells(ws1.Rows.Count, "A").End(xlUp))
    
    ws1.Range("A2:A" & ws1.Cells(ws1.Rows.Count, "A").End(xlUp).Row).Interior.ColorIndex = xlNone
    
    For Each cell In rng1
        Set matchFound = ws2.Range("A2:A" & ws2.Cells(ws2.Rows.Count, "A").End(xlUp).Row).Find( _
            What:=cell.Value, LookIn:=xlValues, LookAt:=xlWhole)
        
        If Not matchFound Is Nothing And cell.Value <> "" Then
            cell.Interior.Color = vbYellow
        End If
    Next cell
End Sub

2. Dans l'éditeur VBA, cliquez sur le bouton pour exécuter le code. Cette macro parcourra les noms dans la colonne A de la feuille de calcul "Noms-1", et si un nom apparaît également dans la colonne A de la feuille de calcul "Noms-2", elle mettra en surbrillance cette cellule dans "Noms-1" avec une couleur de remplissage jaune. Toutes les mises en surbrillance précédentes dans la plage seront effacées avant la nouvelle comparaison.

Dépannage : Si aucune cellule n'est mise en surbrillance, vérifiez que les deux feuilles de calcul sont exactement nommées "Noms-1" et "Noms-2", et que vos plages de données commencent à partir de A2. Assurez-vous que les macros sont activées, et qu'aucune feuille de calcul n'est protégée ou filtrée. Cette approche peut être facilement personnalisée ; par exemple, vous pouvez changer la couleur de mise en surbrillance, ou adapter le code pour copier les résultats correspondants vers une autre feuille ou colonne.

Résumé et suggestions : En fonction de vos besoins et de votre niveau de confort technique, vous pouvez choisir parmi des solutions de formules intégrées, l'automatisation par macro, des add-ins intelligents comme Kutools, ou une visualisation simple avec la mise en forme conditionnelle. Lors de l'utilisation de formules ou de VBA, passez toujours en revue vos données pour détecter les espaces supplémentaires ou les incohérences de formatage, qui sont des sources courantes d'erreurs. Sauvegardez vos données avant d'apporter des modifications en bloc, surtout lors de l'utilisation de macros ou d'add-ins pour la première fois. Si vous rencontrez des problèmes tels que des formules qui ne se mettent pas à jour ou des correspondances incorrectes, vérifiez les erreurs de plage relative/absolue et vérifiez les noms des feuilles de calcul. En choisissant la méthode qui correspond à votre flux de travail, vous pouvez comparer efficacement et efficacement des listes à travers différentes feuilles dans Excel.