Aquest wikiHow us ensenya a utilitzar el format condicional per comparar totes les cel·les de dues llistes separades en un full de càlcul d'Excel i marcar les cel·les que apareixen a les dues llistes. Aquesta característica només està disponible a la versió d'escriptori d'Excel i l'aplicació mòbil no l'admet.
Passos
Mètode 1 de 2: utilitzar la fórmula CountIf
Pas 1. Obriu el full de càlcul d'Excel que vulgueu editar
Cerqueu el fitxer de full de càlcul amb les llistes que voleu comparar i feu doble clic al fitxer per obrir-lo a Microsoft Excel.
Pas 2. Seleccioneu la vostra primera llista
Feu clic a la primera cel·la de la vostra primera llista i arrossegueu el ratolí fins a l'última cel·la de la llista per seleccionar tot l'interval de dades.
Pas 3. Feu clic a la pestanya Fórmules a la cinta de la barra d'eines
Podeu trobar aquesta pestanya a sobre de la barra d’eines a la part superior del full de càlcul. Obrirà les eines de fórmules a la cinta de la barra d'eines.
Pas 4. Feu clic a Defineix el nom a la barra d'eines
Podeu trobar aquesta opció al mig de la cinta "Fórmules". S'obrirà una nova finestra emergent i us permetrà anomenar la llista.
Pas 5. Escriviu Llista1 al camp Nom
Feu clic al camp de text a la part superior de la finestra emergent i introduïu un nom de llista aquí.
- Més endavant, podeu utilitzar aquest nom per inserir la vostra llista a la fórmula de comparació.
- També podeu donar un nom diferent a la vostra llista. Per exemple, si es tracta d'una llista d'ubicacions, podeu anomenar-la "locations1" o "locationList".
Pas 6. Feu clic a D'acord a la finestra emergent
Això confirmarà la vostra acció i anomenarà la vostra llista.
Pas 7. Anomeneu la vostra segona llista com a Llista2
Seguiu els mateixos passos que la primera llista i assigneu un nom a la vostra segona llista. Això us permetrà utilitzar aquesta segona llista ràpidament a la fórmula de comparació més endavant.
Podeu donar a la llista el nom que vulgueu. Recordeu o anoteu el nom que assigneu a cadascuna de les vostres llistes aquí
Pas 8. Seleccioneu la vostra primera llista
Feu clic a la primera cel·la de la primera llista i arrossegueu cap avall per seleccionar tot l'interval de dades.
Assegureu-vos que la vostra primera llista estigui seleccionada abans de començar a configurar el format condicional
Pas 9. Feu clic a la pestanya Inici de la barra d'eines
Aquesta és la primera pestanya de l'extrem superior esquerre de la cinta de la barra d'eines. Obrirà les eines bàsiques del full de càlcul a la barra d'eines.
Pas 10. Feu clic a Format condicional a la barra d'eines
Aquesta opció sembla una petita icona de full de càlcul amb algunes cel·les ressaltades en vermell i blau. Obrirà un menú desplegable amb totes les opcions de format.
Pas 11. Feu clic a Regla nova al menú desplegable
S'obrirà una nova finestra emergent i us permetrà configurar manualment una nova regla de format per a l'interval seleccionat.
Pas 12. Seleccioneu l'opció "Utilitza una fórmula per determinar quines cel·les cal formatar"
Aquesta opció us permetrà escriure manualment una fórmula de format per comparar les vostres dues llistes.
- Encès Windows, el trobareu a la part inferior de la llista de regles al quadre "Selecciona un tipus de regla".
- Encès Mac, seleccioneu Clàssic al menú desplegable "Estil" a la part superior de la finestra emergent. A continuació, trobeu aquesta opció al segon menú desplegable que hi ha a sota del menú Estil.
Pas 13. Feu clic al camp de la fórmula a la finestra emergent
Podeu introduir qualsevol fórmula Excel vàlida aquí per configurar una regla de format condicional.
Pas 14. Escriviu = countif (List2, A1) = 1 a la barra de fórmules
Aquesta fórmula explorarà les vostres dues llistes i marcarà totes les cel·les de la vostra primera llista que també apareixeran a la segona llista.
- Substituïu A1 a la fórmula pel número de la primera cel·la de la vostra primera llista.
- Per exemple, si la primera cel·la de la vostra primera llista és la cel·la D5, la fórmula tindrà l'aspecte de = countif (List2, D5) = 1.
- Si heu assignat un nom diferent a la vostra segona llista, assegureu-vos de substituir la llista 2 de la fórmula pel nom real de la vostra pròpia llista.
- Com a alternativa, canvieu la fórmula a = countif (Llista2, A1) = 0 si voleu marcar les cel·les que no apareixerà a la segona llista.
Pas 15. Escriviu = countif (List1, B1) = 1 a la barra de fórmules (opcional)
Si voleu trobar i marcar les cel·les del vostre fitxer segon llista que també apareix a la primera llista, utilitzeu aquesta fórmula en lloc de la primera.
Substituïu Llista1 pel nom de la vostra primera llista i B1 per la primera cel·la de la vostra segona llista
Pas 16. Seleccioneu un format personalitzat per marcar les cel·les (opcional)
Podeu seleccionar un color de farciment de fons personalitzat i diferents estils de tipus de lletra per marcar les cel·les que trobi la fórmula.
- Encès Windows, feu clic a Format a la part inferior dreta de la finestra emergent. Podeu seleccionar un color de fons a la pestanya "Omplir" i estils de tipus de lletra a la pestanya "Tipus de lletra".
- Encès Mac, seleccioneu un format predefinit al menú desplegable "Format amb" a la part inferior. També podeu seleccionar format personalitzat aquí per seleccionar manualment un estil de farciment i de farciment.
Pas 17. Feu clic a D'acord a la finestra emergent
Això confirmarà i aplicarà la fórmula de comparació. Totes les cel·les de la vostra primera llista que també apareixen a la segona llista es marcaran amb el color i el tipus de lletra seleccionats.
- Per exemple, si seleccioneu un farcit de color vermell clar amb text de color vermell fosc, totes les cel·les recurrents passaran a aquest color a la vostra primera llista.
- Si utilitzeu la segona fórmula anterior, el format condicional marcarà les cel·les recurrents de la vostra segona llista en lloc de la primera.
Mètode 2 de 2: utilitzar la fórmula VLookup
Pas 1. Obriu el full de càlcul d'Excel
Cerqueu el fitxer Excel amb les llistes que voleu comparar i feu doble clic sobre el nom o la icona del fitxer per obrir el full de càlcul a Microsoft Excel.
Pas 2. Feu clic a la cel·la buida que hi ha al costat del primer element de la vostra segona llista
Cerqueu la vostra segona llista al full de càlcul i feu clic a la cel·la buida que hi ha al costat del primer element de la llista a la part superior.
- Podeu inserir la fórmula VLookup aquí.
- Com a alternativa, podeu seleccionar qualsevol cel·la buida del full de càlcul. Aquesta cel·la només us farà més convenient veure la vostra comparació al costat de la vostra segona llista.
Pas 3. Escriviu = vlookup (a la cel·la buida
La fórmula VLookup us permetrà comparar tots els elements de dues llistes separades i veure si un valor és un valor repetit o nou.
No tanqueu el parèntesi de la fórmula fins que la fórmula no estigui completa
Pas 4. Seleccioneu el primer element de la vostra segona llista
Sense tancar el parèntesi de la fórmula, feu clic al primer element de la vostra segona llista. Això inserirà la primera cel·la de la vostra segona llista a la fórmula.
Pas 5. Escriviu a, coma a la fórmula
Després de seleccionar la primera cel·la de la vostra segona llista, escriviu una coma a la fórmula. A continuació, podreu seleccionar el vostre interval de comparació.
Pas 6. Mantingueu premut i seleccioneu tota la primera llista
Això inserirà l'interval de cel·les de la vostra primera llista a la segona part de la fórmula VLookup.
Això us permetrà cercar a la primera llista l'element seleccionat de la vostra segona llista (el primer a la part superior de la segona llista) i tornar si es tracta d'un valor nou o repetit
Pas 7. Escriviu a, coma a la fórmula
Això bloquejarà l'interval de comparació a la fórmula.
Pas 8. Escriviu 1 a la fórmula després de la coma
Aquest número representa el número d'índex de la vostra columna. Sol·licitarà que la fórmula VLookup cerqui la columna de la llista real en lloc d'una columna diferent al costat.
Si voleu que la fórmula torni el valor de la columna que hi ha just al costat de la vostra primera llista, escriviu 2 aquí
Pas 9. Escriviu a, coma a la fórmula
Això bloquejarà el número d'índex de columna (1) a la fórmula VLookup.
Pas 10. Escriviu FALS a la fórmula
Això cercarà a la llista una coincidència exacta de l'element de cerca seleccionat (el primer element a la part superior de la segona llista) en lloc de coincidències aproximades.
- En lloc de FALS, podeu utilitzar 0, és exactament el mateix.
- Com a alternativa, podeu escriure TRUE o 1 si voleu cercar una coincidència aproximada.
Pas 11. Escriviu) al final per tancar la fórmula
Ara podeu executar la fórmula i veure si l’element de cerca seleccionat a la vostra segona llista és un valor nou o repetit.
Per exemple, si la vostra segona llista comença per B1 i la vostra primera llista va de les cel·les A1 a A5, la vostra fórmula serà = vlookup (B1, $ A $ 1: $ A $ 5, 1, fals).
Pas 12. Premeu ↵ Retorn o bé ⏎ Torneu al teclat.
Això executarà la fórmula i cercarà el primer element de la segona llista a la primera llista.
- Si es tracta d'un valor de repetició, veureu el mateix valor imprès de nou a la cel·la de la fórmula.
- Si es tracta d'un valor nou, veureu " # N / A"imprès aquí.
- Per exemple, si cerqueu "John" a la primera llista i ara veieu "John" a la cel·la de la fórmula, es tracta d'un valor de repetició que apareix a les dues llistes. Si veieu "# N / A", apareixerà un valor nou a la segona llista.
Pas 13. Seleccioneu la vostra cel·la de fórmula
Després d'executar la fórmula i veure els resultats del primer element de llista, feu clic a la cel·la de la fórmula per seleccionar-la.
Pas 14. Feu clic i arrossegueu cap avall el punt verd situat a la part inferior dreta de la cel·la
Això ampliarà la cel·la de la fórmula al llarg de la llista i aplicarà la fórmula a tots els elements de la llista de la segona llista.
- D'aquesta manera, podeu comparar tots els elements de la vostra segona llista amb tota la primera llista.
- Això cercarà individualment la vostra primera llista per a cada element de la vostra segona llista i mostrarà el resultat al costat de cada cel·la per separat.
- Si voleu veure un marcador diferent per a valors nous en lloc de "# N / A", utilitzeu aquesta fórmula: = iferror (vlookup (B1, $ A $ 1: $ A $ 5, 1, false), "New Value"). S'imprimirà "Valor nou" per als valors nous en lloc de "# N / A".
