Ce wikiHow vous apprend à utiliser la mise en forme conditionnelle pour comparer toutes les cellules de deux listes distinctes dans une feuille de calcul Excel et marquer les cellules qui apparaissent sur les deux listes. Cette fonctionnalité n'est disponible que sur la version de bureau d'Excel et l'application mobile ne la prend pas en charge.
Pas
Méthode 1 sur 2: Utilisation de la formule CountIf
Étape 1. Ouvrez la feuille de calcul Excel que vous souhaitez modifier
Recherchez le fichier de feuille de calcul avec les listes que vous souhaitez comparer et double-cliquez sur le fichier pour l'ouvrir dans Microsoft Excel.
Étape 2. Sélectionnez votre première liste
Cliquez sur la première cellule de votre première liste et faites glisser votre souris jusqu'à la dernière cellule de la liste pour sélectionner toute la plage de données.
Étape 3. Cliquez sur l'onglet Formules dans le ruban de la barre d'outils
Vous pouvez trouver cet onglet au-dessus de la barre d'outils en haut de votre feuille de calcul. Il ouvrira vos outils de formule sur le ruban de la barre d'outils.
Étape 4. Cliquez sur Définir le nom dans la barre d'outils
Vous pouvez trouver cette option au milieu du ruban "Formules". Cela ouvrira une nouvelle fenêtre contextuelle et vous permettra de nommer votre liste.
Étape 5. Tapez List1 dans le champ Nom
Cliquez sur le champ de texte en haut de la fenêtre contextuelle et entrez un nom de liste ici.
- Vous pourrez ensuite utiliser ce nom pour insérer votre liste dans la formule de comparaison.
- Alternativement, vous pouvez donner à votre liste un nom différent ici. Par exemple, s'il s'agit d'une liste d'emplacements, vous pouvez la nommer « locations1 » ou « locationList ».
Étape 6. Cliquez sur OK dans la fenêtre contextuelle
Cela confirmera votre action et nommera votre liste.
Étape 7. Nommez votre deuxième liste List2
Suivez les mêmes étapes que la première liste et donnez un nom à votre deuxième liste. Cela vous permettra d'utiliser rapidement cette deuxième liste dans votre formule de comparaison plus tard.
Vous pouvez donner à la liste le nom que vous voulez. Assurez-vous de vous souvenir ou de noter le nom que vous donnez à chacune de vos listes ici
Étape 8. Sélectionnez votre première liste
Cliquez sur la première cellule de la première liste et faites glisser vers le bas pour sélectionner toute la plage de données.
Assurez-vous que votre première liste est sélectionnée avant de commencer à configurer votre mise en forme conditionnelle
Étape 9. Cliquez sur l'onglet Accueil de la barre d'outils
Il s'agit du premier onglet dans le coin supérieur gauche du ruban de la barre d'outils. Il ouvrira vos outils de tableur de base sur la barre d'outils.
Étape 10. Cliquez sur Mise en forme conditionnelle dans la barre d'outils
Cette option ressemble à une petite icône de feuille de calcul avec certaines cellules surlignées en rouge et bleu. Il ouvrira un menu déroulant de toutes vos options de formatage.
Étape 11. Cliquez sur la nouvelle règle dans le menu déroulant
Il ouvrira une nouvelle fenêtre contextuelle et vous permettra de configurer manuellement une nouvelle règle de formatage pour la plage sélectionnée.
Étape 12. Sélectionnez l'option "Utiliser une formule pour déterminer les cellules à formater"
Cette option vous permettra de saisir manuellement une formule de mise en forme pour comparer vos deux listes.
- Au les fenêtres, vous le trouverez en bas de la liste des règles dans la case "Sélectionnez un type de règle".
- Au Mac, sélectionnez Classique dans la liste déroulante "Style" en haut de la fenêtre contextuelle. Ensuite, recherchez cette option dans la deuxième liste déroulante sous le menu Style.
Étape 13. Cliquez sur le champ de formule dans la fenêtre contextuelle
Vous pouvez entrer n'importe quelle formule Excel valide ici pour configurer une règle de mise en forme conditionnelle.
Étape 14. Tapez =countif(List2, A1)=1 dans la barre de formule
Cette formule analysera vos deux listes et marquera toutes les cellules de votre première liste qui apparaissent également dans la deuxième liste.
- Remplacez A1 dans la formule par le numéro de la première cellule de votre première liste.
- Par exemple, si la première cellule de votre première liste est la cellule D5, votre formule ressemblera à =countif(List2, D5)=1.
- Si vous avez donné un nom différent à votre deuxième liste, assurez-vous de remplacer List2 dans la formule par le nom réel de votre propre liste.
- Vous pouvez également modifier la formule en =countif(List2, A1)=0 si vous souhaitez marquer les cellules qui ne pas apparaissent sur la deuxième liste.
Étape 15. Tapez =countif(List1, B1)=1 dans la barre de formule (facultatif)
Si vous voulez trouver et marquer les cellules de votre seconde liste qui apparaissent également sur la première liste, utilisez cette formule au lieu de la première.
Remplacez List1 par le nom de votre première liste et B1 par la première cellule de votre deuxième liste
Étape 16. Sélectionnez un format personnalisé pour marquer les cellules (facultatif)
Vous pouvez sélectionner une couleur de remplissage d'arrière-plan personnalisée et différents styles de police pour marquer les cellules trouvées par votre formule.
- Au les fenêtres, clique le Format en bas à droite de la fenêtre pop-up. Vous pouvez sélectionner une couleur d'arrière-plan dans l'onglet "Remplir" et des styles de police dans l'onglet "Police".
- Au Mac, sélectionnez un format prédéfini dans la liste déroulante "Format avec" en bas. Vous pouvez également sélectionner format personnalisé ici pour sélectionner manuellement un remplissage d'arrière-plan et des styles de police.
Étape 17. Cliquez sur OK dans la fenêtre contextuelle
Cela confirmera et appliquera votre formule de comparaison. Toutes les cellules de votre première liste qui apparaissent également sur la deuxième liste seront marquées avec la couleur et la police que vous avez sélectionnées.
- Par exemple, si vous sélectionnez un remplissage rouge clair avec du texte rouge foncé, toutes les cellules récurrentes prendront cette couleur sur votre première liste.
- Si vous utilisez la deuxième formule ci-dessus, la mise en forme conditionnelle marquera les cellules récurrentes sur votre deuxième liste au lieu de la première.
Méthode 2 sur 2: Utilisation de la formule VLookup
Étape 1. Ouvrez votre feuille de calcul Excel
Recherchez le fichier Excel avec les listes que vous souhaitez comparer et double-cliquez sur le nom ou l'icône du fichier pour ouvrir la feuille de calcul dans Microsoft Excel.
Étape 2. Cliquez sur la cellule vide à côté du premier élément de votre deuxième liste
Recherchez votre deuxième liste sur la feuille de calcul et cliquez sur la cellule vide à côté du premier élément de la liste en haut.
- Vous pouvez insérer votre formule VLookup ici.
- Vous pouvez également sélectionner n'importe quelle cellule vide de votre feuille de calcul. Cette cellule ne fera que rendre plus pratique pour vous de voir votre comparaison à côté de votre deuxième liste.
Étape 3. Tapez =vlookup(dans la cellule vide
La formule VLookup vous permettra de comparer tous les éléments de deux listes distinctes et de voir si une valeur est une valeur répétée ou nouvelle.
Ne fermez pas la parenthèse de formule tant que votre formule n'est pas terminée
Étape 4. Sélectionnez le premier élément de votre deuxième liste
Sans fermer la parenthèse de formule, cliquez sur le premier élément de votre deuxième liste. Cela insérera la première cellule de votre deuxième liste dans la formule.
Étape 5. Tapez une virgule dans la formule
Après avoir sélectionné la première cellule de votre deuxième liste, tapez une virgule dans la formule. Vous pourrez ensuite sélectionner votre plage de comparaison.
Étape 6. Maintenez enfoncé et sélectionnez toute votre première liste
Cela insérera la plage de cellules de votre première liste dans la deuxième partie de la formule VLookup.
Cela vous permettra de rechercher dans la première liste l'élément sélectionné dans votre deuxième liste (le premier élément en haut de la deuxième liste) et de revenir s'il s'agit d'une répétition ou d'une nouvelle valeur
Étape 7. Tapez une virgule dans la formule
Cela verrouillera la plage de comparaison dans votre formule.
Étape 8. Tapez 1 dans la formule après la virgule
Ce nombre représente votre numéro d'index de colonne. Il invitera la formule VLookup à rechercher la colonne de liste réelle au lieu d'une colonne différente à côté.
Si vous voulez que votre formule renvoie la valeur de la colonne juste à côté de votre première liste, tapez 2 ici
Étape 9. Tapez une virgule dans la formule
Cela verrouillera votre numéro d'index de colonne (1) dans la formule VLookup.
Étape 10. Tapez FALSE dans la formule
Cela recherchera dans la liste une correspondance exacte de l'élément de recherche sélectionné (le premier élément en haut de la deuxième liste) au lieu de correspondances approximatives.
- Au lieu de FALSE, vous pouvez utiliser 0, c'est exactement la même chose.
- Vous pouvez également saisir VRAI ou 1 si vous souhaitez rechercher une correspondance approximative.
Étape 11. Tapez) à la fin pour fermer la formule
Vous pouvez maintenant exécuter votre formule et voir si l'élément de recherche sélectionné sur votre deuxième liste est une valeur répétée ou nouvelle.
Par exemple, si votre deuxième liste commence à B1 et que votre première liste va des cellules A1 à A5, votre formule ressemblera à =vlookup(B1, $A$1:$A$5, 1, faux).
Étape 12. Appuyez sur ↵ Entrée ou Retournez sur votre clavier.
Cela exécutera la formule et recherchera dans votre première liste le premier élément de votre deuxième liste.
- S'il s'agit d'une valeur répétée, la même valeur s'affichera à nouveau dans la cellule de formule.
- S'il s'agit d'une nouvelle valeur, vous verrez " #N / A" imprimé ici.
- Par exemple, si vous recherchez « John » dans la première liste et que vous voyez maintenant « John » dans la cellule de formule, c'est une valeur de répétition qui apparaît sur les deux listes. Si vous voyez "#N/A", il s'agit d'une nouvelle valeur sur la deuxième liste.
Étape 13. Sélectionnez votre cellule de formule
Après avoir exécuté votre formule et vu vos résultats pour le premier élément de la liste, cliquez sur la cellule de formule pour la sélectionner.
Étape 14. Cliquez et faites glisser le point vert en bas à droite de la cellule
Cela développera votre cellule de formule le long de la liste et appliquera la formule à chaque élément de liste de votre deuxième liste.
- De cette façon, vous pouvez comparer chaque élément de votre deuxième liste à l'ensemble de votre première liste.
- Cela recherchera votre première liste pour chaque élément de votre deuxième liste individuellement et affichera le résultat à côté de chaque cellule séparément.
- Si vous souhaitez voir un marqueur différent pour les nouvelles valeurs au lieu de "#N/A", utilisez cette formule: =iferror(vlookup(B1, $A$1:$A$5, 1, false), "New Value"). Cela imprimera "Nouvelle valeur" pour les nouvelles valeurs au lieu de "#N/A".
