Una de les moltes funcions de Microsoft Excel és la possibilitat de comparar dues llistes de dades, identificar coincidències entre les llistes i identificar quins elements es troben en una sola llista. Això és útil per comparar registres financers o per comprovar si hi ha un nom concret a una base de dades. Podeu utilitzar la funció MATCH per identificar i marcar registres coincidents o no coincidents, o bé podeu utilitzar el format de condicionament amb la funció COUNTIF. Els passos següents indiquen com utilitzar-los per fer coincidir les vostres dades.
Passos
Mètode 1 de 2: identificació de registres amb la funció MATCH
Pas 1. Copieu les llistes de dades en un sol full de càlcul
Excel pot funcionar amb diversos fulls de treball dins d’un mateix llibre o amb diversos llibres, però trobareu més fàcil comparar les llistes si copieu la seva informació en un sol full de càlcul.
Pas 2. Doneu a cada element de la llista un identificador únic
Si les vostres dues llistes no comparteixen una manera comuna d’identificar-les, és possible que hàgiu d’afegir una columna addicional a cada llista de dades que identifiqui aquest element a Excel perquè pugui veure si un element d’una llista determinada està relacionat amb un element. a l’altra llista. La naturalesa d’aquest identificador dependrà del tipus de dades que intenteu fer coincidir. Necessitareu un identificador per a cada llista de columnes.
- Per a les dades financeres associades a un període determinat, com ara els registres fiscals, pot ser la descripció d’un actiu, la data en què es va adquirir l’actiu o totes dues coses. En alguns casos, una entrada es pot identificar amb un número de codi; tanmateix, si no s'utilitza el mateix sistema per a les dues llistes, aquest identificador pot crear coincidències on no n'hi hagi cap o ignorar les coincidències que s'haurien de fer.
- En alguns casos, podeu agafar articles d'una llista i combinar-los amb elements d'una altra llista per crear un identificador, com ara una descripció física del recurs i l'any en què es va comprar. Per crear aquest identificador, heu de concatenar (afegir, combinar) dades de dues o més cel·les mitjançant el signe amperes (&). Per combinar una descripció de l'element a la cel·la F3 amb una data a la cel·la G3, separada per un espai, introduïu la fórmula "= F3 &" "& G3" en una altra cel·la d'aquesta fila, com ara E3. Si voleu incloure només l'any a l'identificador (perquè una llista utilitza dates completes i l'altra només fa anys), inclouríeu la funció YEAR introduint "= F3 &" "& YEAR (G3)" a la cel·la E3. (No inclogueu les cometes simples; només hi són per indicar l'exemple.)
- Un cop hàgiu creat la fórmula, podeu copiar-la a la resta de cel·les de la columna identificadora seleccionant la cel·la amb la fórmula i arrossegant el mànec de farciment sobre les altres cel·les de la columna on vulgueu copiar la fórmula. Quan deixeu anar el botó del ratolí, cada cel·la que heu arrossegat s'emplenarà amb la fórmula, amb les referències de cel·la ajustades a les cel·les adequades de la mateixa fila.
Pas 3. Estandarditzeu les dades quan sigui possible
Mentre que la ment reconeix que "Inc." i "Incorporated" vol dir el mateix, Excel no ho fa tret que el torni a formatar una paraula o l'altra. De la mateixa manera, podeu considerar valors com ara $ 11, 950 i $ 11, 999,95 com a prou propers per coincidir, però Excel no ho farà tret que ho digueu.
- Podeu fer servir algunes abreviatures, com ara "Co" per a "Company" i "Inc" per a "Incorporated mitjançant la funció de cadena LEFT per truncar els caràcters addicionals. És possible que altres abreviatures, com ara" Assn "per a" Association ", siguin tractats establint una guia d'estil d'entrada de dades i escrivint un programa per buscar i corregir formats inadequats.
- Per a cadenes de números, com ara codis postals on algunes entrades inclouen el sufix ZIP + 4 i d'altres no, podeu tornar a utilitzar la funció de cadena LEFT per reconèixer i fer coincidir només els codis postals principals. Per tal que Excel reconegui valors numèrics propers però no iguals, podeu utilitzar la funció RODONAR per arrodonir els valors de tancament al mateix nombre i fer-los coincidir.
- Els espais addicionals, com escriure dos espais entre paraules en lloc d’un, es poden eliminar mitjançant la funció TRIM.
Pas 4. Creeu columnes per a la fórmula de comparació
De la mateixa manera que heu de crear columnes per als identificadors de llista, haureu de crear columnes per a la fórmula que us faci la comparació. Necessitareu una columna per a cada llista.
Voleu etiquetar aquestes columnes amb alguna cosa com "Falta?"
Pas 5. Introduïu la fórmula de comparació a cada cel·la
Per a la fórmula de comparació, utilitzeu la funció MATCH imbricada dins d’una altra funció Excel, ISNA.
- La fórmula pren la forma de "= ISNA (MATCH (G3, $ L $ 3: $ L $ 14, FALSE))", on es compara una cel·la de la columna identificadora de la primera llista amb cadascun dels identificadors de la segona llista amb veure si coincideix amb un d’ells. Si no coincideix, falta un registre i es mostrarà la paraula "TRUE" a la cel·la. Si coincideix, el registre està present i es mostrarà la paraula "FALS". (Quan introduïu la fórmula, no incloeu les cometes adjuntes.)
- Podeu copiar la fórmula a les cel·les restants de la columna de la mateixa manera que vau copiar la fórmula de l’identificador de cel·la. En aquest cas, només canvia la referència de cel·la per a la cel·la identificadora, ja que posar els signes de dòlar davant de les referències de fila i columna per a la primera i l'última cel·la de la llista dels segons identificadors de cel·la els converteix en referències absolutes.
- Podeu copiar la fórmula de comparació de la primera llista a la primera cel·la de la columna de la segona llista. A continuació, haureu d'editar les referències de cel·la de manera que "G3" se substitueixi per la referència de la primera cel·la identificadora de la segona llista i "$ L $ 3: $ L $ 14" se substitueixi per la primera i l'última cel·la identificadora de la segona llista. (Deixeu els signes de dòlar i els dos punts en pau). A continuació, podeu copiar aquesta fórmula editada a les cel·les restants de la fila de comparació de la segona llista.
Pas 6. Ordeneu les llistes per veure els valors que no coincideixen amb més facilitat, si cal
Si les vostres llistes són grans, és possible que hàgiu d'ordenar-les per reunir tots els valors que no coincideixin. Les instruccions dels subpases següents convertiran les fórmules en valors per evitar errors de recàlcul i, si les vostres llistes són grans, s’evitarà un temps de recàlcul llarg.
- Arrossegueu el ratolí per sobre de totes les cel·les d’una llista per seleccionar-lo.
- Seleccioneu Copia al menú Edita a Excel 2003 o al grup Portapapers de la cinta Inici a Excel 2007 o 2010.
- Seleccioneu Enganxa especial al menú Edita d'Excel 2003 o al botó desplegable Enganxa del grup Portapapers de la cinta d'inici d'Excel 2007 o 2010.
- Seleccioneu "Valors" a la llista Enganxa com al quadre de diàleg Enganxa especial. Feu clic a D'acord per tancar el diàleg.
- Seleccioneu Ordena al menú Dades a Excel 2003 o el grup Ordena i filtre de la cinta de dades a Excel 2007 o 2010.
- Seleccioneu "Fila de capçalera" a la llista "El meu rang de dades té" al quadre de diàleg Ordena per, seleccioneu "Falta?" (o el nom que realment vau donar a la capçalera de la columna de comparació) i feu clic a D'acord.
- Repetiu aquests passos per a l’altra llista.
Pas 7. Compareu visualment els elements que no coincideixen per veure per què no coincideixen
Com es va assenyalar anteriorment, Excel està dissenyat per buscar coincidències de dades exactes tret que el configureu per cercar-ne de aproximades. La vostra no coincidència podria ser tan senzilla com una transposició accidental de lletres o dígits. També pot ser una cosa que requereixi una verificació independent, com ara comprovar si cal que es notifiquin en primer lloc els recursos llistats.
Mètode 2 de 2: format condicional amb COUNTIF
Pas 1. Copieu les llistes de dades en un sol full de càlcul
Pas 2. Decidiu en quina llista voleu ressaltar els registres coincidents o no coincidents
Si voleu ressaltar els registres en una sola llista, probablement voldreu ressaltar els registres exclusius d’aquesta llista; és a dir, registres que no coincideixen amb els registres de l’altra llista. Si voleu ressaltar els registres de les dues llistes, voldreu ressaltar els registres que coincideixin. Als efectes d’aquest exemple, suposarem que la primera llista ocupa les cel·les G3 a G14 i la segona llista ocupa les cel·les L3 a L14.
Pas 3. Seleccioneu els elements de la llista en què vulgueu ressaltar els elements únics o coincidents
Si voleu ressaltar els elements coincidents a les dues llistes, haureu de seleccionar-les una per una i aplicar la fórmula de comparació (descrita al pas següent) a cada llista.
Pas 4. Apliqueu la fórmula de comparació adequada
Per fer-ho, haureu d’accedir al diàleg de format condicional de la vostra versió d’Excel. A Excel 2003, ho feu seleccionant Format condicional al menú Format, mentre que a Excel 2007 i 2010 feu clic al botó Format condicional al grup Estils de la cinta Inici. Seleccioneu el tipus de regla com a "Fórmula" i introduïu la fórmula al camp Edita la descripció de la regla.
- Si voleu ressaltar els registres exclusius de la primera llista, la fórmula seria "= COUNTIF ($ L $ 3: $ L $ 14, G3 = 0)", amb l'interval de cel·les de la segona llista representat com a valors absoluts i la referència a la primera cel·la de la primera llista com a valor relatiu. (No introduïu les cometes tancades.)
- Si voleu ressaltar els registres exclusius de la segona llista, la fórmula seria "= COUNTIF ($ G $ 3: $ G $ 14, L3 = 0)", amb l'interval de cel·les de la primera llista representat com a valors absoluts i la referència a la primera cel·la de la segona llista com a valor relatiu. (No introduïu les cometes tancades.)
- Si voleu ressaltar els registres de cada llista que es troben a l’altra llista, necessitareu dues fórmules, una per a la primera llista i una per a la segona. La fórmula de la primera llista és "= COUNTIF ($ L $ 3: $ L $ 14, G3> 0)", mentre que la fórmula de la segona llista és COUNTIF ($ G $ 3: $ G $ 14, L3> 0) ". Com assenyalat anteriorment, seleccioneu la primera llista per aplicar la seva fórmula i, a continuació, seleccioneu la segona llista per aplicar-ne la fórmula.
- Apliqueu el format que vulgueu per ressaltar els registres que es marquen. Feu clic a D'acord per tancar el diàleg.
Vídeo: mitjançant aquest servei, es pot compartir informació amb YouTube
Consells
- En lloc d’utilitzar una referència de cel·la amb el mètode de format condicional COUNTIF, podeu introduir un valor que cal cercar i marcar una o més llistes per a instàncies d’aquest valor.
- Per simplificar els formularis de comparació, podeu crear noms per a la vostra llista, com ara "Llista1" i "Llista2". Aleshores, quan s’escriuen les fórmules, aquests noms de llista poden substituir els intervals de cel·les absoluts que s’utilitzen en els exemples anteriors.
