contingut
Aquest article us portarà uns 10 minuts a llegir-lo. En els propers 5 minuts, podeu comparar fàcilment dues columnes a Excel i esbrinar si hi ha duplicats, eliminar-les o ressaltar-les amb color. Així doncs, ha arribat el moment!
Excel és una aplicació molt potent i genial per crear i processar grans quantitats de dades. Si teniu diversos llibres de treball amb dades (o només una taula enorme), és probable que vulgueu comparar 2 columnes, trobar valors duplicats i després fer alguna cosa amb ells, per exemple, suprimir, ressaltar o esborrar el contingut . Les columnes poden estar a la mateixa taula, ser adjacents o no adjacents, es poden situar en 2 fulls diferents o fins i tot en llibres diferents.
Imagineu que tenim 2 columnes amb noms de persones: 5 noms per columna A i 3 noms en una columna B. Heu de comparar els noms d'aquestes dues columnes i trobar duplicats. Com enteneu, es tracta de dades fictícias, preses únicament per exemple. En taules reals, estem tractant amb milers o fins i tot desenes de milers de registres.
Opció A: ambdues columnes estan al mateix full. Per exemple, una columna A i columna B.
Opció B: Les columnes estan en fulls diferents. Per exemple, una columna A al full Sheet2 i columna A al full Sheet3.
Excel 2013, 2010 i 2007 tenen una eina integrada Elimina els duplicats (Elimina els duplicats) però és impotent en aquesta situació, ja que no pot comparar dades en 2 columnes. A més, només pot eliminar duplicats. No hi ha altres opcions com ressaltar o canviar els colors. I punt!
A continuació, us mostraré les possibles maneres de comparar dues columnes a Excel, que us permetran trobar i eliminar registres duplicats.
Compareu 2 columnes a Excel i trobeu entrades duplicades mitjançant fórmules
Opció A: les dues columnes estan al mateix full
- A la primera cel·la buida (al nostre exemple, aquesta és la cel·la C1), escrivim la fórmula següent:
=IF(ISERROR(MATCH(A1,$B$1:$B$10000,0)),"Unique","Duplicate")=ЕСЛИ(ЕОШИБКА(ПОИСКПОЗ(A1;$B$1:$B$10000;0));"Unique";"Duplicate")
A la nostra fórmula A1 aquesta és la primera cel·la de la primera columna que compararem. 1 $ B$ и 10000 $ B$ aquestes són les adreces de la primera i l'última cel·la de la segona columna, amb les quals realitzarem la comparació. Tingueu en compte les referències absolutes: les lletres de columna i els números de fila van precedits d'un signe de dòlar ($). Utilitzo referències absolutes perquè les adreces de cel·les siguin iguals quan copieu fórmules.
Si voleu trobar duplicats en una columna B, canvieu les referències perquè la fórmula sembli així:
=IF(ISERROR(MATCH(B1,$A$1:$A$10000,0)),"Unique","Duplicate")=ЕСЛИ(ЕОШИБКА(ПОИСКПОЗ(B1;$A$1:$A$10000;0));"Unique";"Duplicate")En canvi "Només"I"Duplicat» Podeu escriure les vostres pròpies etiquetes, per exemple, «No trobat"I"Trobat", o deixar només"Duplicat' i introduïu un caràcter d'espai en lloc del segon valor. En aquest últim cas, les cel·les per a les quals no es troben duplicats romandran buides i, crec, aquesta representació de les dades és la més convenient per a una anàlisi posterior.
- Ara copiem la nostra fórmula a totes les cel·les de la columna C, fins a la fila inferior, que conté les dades de la columna A. Per fer-ho, moveu el punter del ratolí a la cantonada inferior dreta de la cel·la C1, el punter tindrà la forma d'una creu negra, tal com es mostra a la imatge següent:Feu clic i manteniu premut el botó esquerre del ratolí i arrossegueu la vora del marc cap avall, ressaltant totes les cel·les on voleu inserir la fórmula. Quan totes les cel·les necessàries estiguin seleccionades, deixeu anar el botó del ratolí:
Feu clic i manteniu premut el botó esquerre del ratolí i arrossegueu la vora del marc cap avall, ressaltant totes les cel·les on voleu inserir la fórmula. Quan totes les cel·les necessàries estiguin seleccionades, deixeu anar el botó del ratolí:
Consell: A les taules grans, copiar la fórmula serà més ràpid si utilitzeu tecles de drecera. Ressalteu una cel·la C1 i premi Ctrl + C (per copiar la fórmula al porta-retalls), després feu clic Ctrl + Maj + Final (per seleccionar totes les cel·les que no estiguin en blanc a la columna C) i finalment premeu Ctrl + V (per inserir la fórmula a totes les cel·les seleccionades).
- Genial, ara tots els valors duplicats estan marcats com a "Duplicat":
Opció B: dues columnes estan en fulls diferents (en llibres de treball diferents)
- A la primera cel·la de la primera columna buida del full de treball Sheet2 (en el nostre cas és la columna B) introduïu la fórmula següent:
=IF(ISERROR(MATCH(A1,Sheet3!$A$1:$A$10000,0)),"","Duplicate")=ЕСЛИ(ЕОШИБКА(ПОИСКПОЗ(A1;Лист3!$A$1:$A$10000;0));"";"Duplicate")Aquí Sheet3 és el nom del full en què es troba la 2a columna, i 1 $ A$: 10000 $ A$ són adreces de cel·les de la 1a a l'última d'aquesta 2a columna.
- Copieu la fórmula a totes les cel·les d'una columna B (igual que l'opció A).
- Obtenim aquest resultat:
Processament dels duplicats trobats
Genial, hem trobat entrades a la primera columna que també estan presents a la segona columna. Ara hem de fer alguna cosa amb ells. Repassar manualment tots els registres duplicats d'una taula és força ineficient i requereix massa temps. Hi ha maneres millors.
Mostra només les files duplicades a la columna A
Si les vostres columnes no tenen capçaleres, haureu d'afegir-les. Per fer-ho, col·loqueu el cursor sobre el número que representa la primera línia, i es convertirà en una fletxa negra, tal com es mostra a la figura següent:
Feu clic amb el botó dret i seleccioneu al menú contextual inserció (insereix):
Doneu noms a les columnes, per exemple, "Nom"I"Duplicar?» A continuació, obriu la pestanya dades (Dades) i premeu filtre (Filtre):
Després d'això, feu clic a la petita fletxa grisa al costat de "Duplicar?« per obrir el menú de filtres; desmarqueu tots els elements d'aquesta llista excepte Duplicat, i premeu OK.
Això és tot, ara només veus els elements de la columna А, que es dupliquen a la columna В. Només hi ha dues d'aquestes cel·les a la nostra taula d'entrenament, però, com enteneu, a la pràctica n'hi haurà moltes més.
Per tornar a mostrar totes les files d'una columna А, feu clic al símbol del filtre a la columna В, que ara sembla un embut amb una fletxa petita i seleccioneu Selecciona-ho tot (Seleccionar tot). O podeu fer el mateix a través de la cinta fent clic dades (Dades) > Selecciona i filtra (Ordenar i filtrar) > Clear (Netejar) tal com es mostra a la captura de pantalla següent:
Canvia el color o ressalta els duplicats trobats
Si les notes "Duplicat” no és suficient per als vostres propòsits i voleu marcar les cel·les duplicades amb un color de lletra diferent, color de farciment o algun altre mètode...
En aquest cas, filtreu els duplicats com es mostra a dalt, seleccioneu totes les cel·les filtrades i feu clic Ctrl + 1per obrir el diàleg Format de cel·les (format cel·la). Com a exemple, canviem el color de farciment de les cel·les de les files amb duplicats a un groc brillant. Per descomptat, podeu canviar el color de farciment amb l'eina Omplir pestanya (Color de farciment). Home (Inici) però avantatge del quadre de diàleg Format de cel·les (Format de cel·la), ja que podeu configurar totes les opcions de format al mateix temps.
Ara definitivament no us perdreu cap cel·la amb duplicats:
Eliminació de valors duplicats de la primera columna
Filtreu la taula de manera que només es mostrin les cel·les amb valors duplicats i seleccioneu aquestes cel·les.
Si les 2 columnes que esteu comparant estan en fulls diferents, és a dir, en diferents taules, feu clic amb el botó dret a l'interval seleccionat i seleccioneu Suprimeix la fila (Elimina la línia):
Premsa OKquan Excel us demana que confirmeu que realment voleu suprimir tota la fila del full i després esborrar el filtre. Com podeu veure, només queden files amb valors únics:
Si hi ha 2 columnes al mateix full, a prop l'un de l'altre (adjacents) o no a prop l'un de l'altre (no adjacents), aleshores el procés d'eliminació de duplicats serà una mica més complicat. No podem eliminar tota la fila amb valors duplicats, ja que això també eliminarà les cel·les de la segona columna. Per tant, deixar només entrades úniques en una columna А, fer això:
- Filtreu la taula per mostrar només els valors duplicats i seleccioneu aquestes cel·les. Feu clic amb el botó dret sobre ells i seleccioneu-los al menú contextual Continguts clars (contingut clar).
- Netegeu el filtre.
- Seleccioneu totes les cel·les d'una columna А, començant per la cèl·lula A1 tot el camí fins a la part inferior que conté les dades.
- Feu clic al dades (Dades) i premeu Ordena de la A a la Z (Ordena de la A a la Z). Al quadre de diàleg que s'obre, seleccioneu Continueu amb la selecció actual (Ordeneu dins de la selecció especificada) i feu clic al botó Negre (Classificació):
- Elimina la columna amb la fórmula, ja no la necessitaràs, a partir d'ara només tens valors únics.
- Això és tot, ara la columna А conté només dades úniques que no es troben a la columna В:
Com podeu veure, eliminar els duplicats de dues columnes a Excel mitjançant fórmules no és tan difícil.