Como comparar dúas columnas en Excel e eliminar duplicados (resaltar, colorear, mover)

Este artigo levarache uns 10 minutos para ler. Nos próximos 5 minutos, podes comparar facilmente dúas columnas en Excel e descubrir se hai duplicados nelas, eliminalas ou resaltalas en cor. Entón, chegou o momento!

Excel é unha aplicación moi potente e moi xenial para crear e procesar grandes cantidades de datos. Se tes varios libros de traballo con datos (ou só unha táboa enorme), probablemente queiras comparar 2 columnas, buscar valores duplicados e facer algo con eles, por exemplo, eliminar, resaltar ou borrar o contido . As columnas poden estar na mesma táboa, estar adxacentes ou non, poden estar situadas en 2 follas diferentes ou mesmo en libros diferentes.

Imaxina que temos 2 columnas con nomes de persoas: 5 nomes por columna A e 3 nomes nunha columna B. Debe comparar os nomes destas dúas columnas e atopar duplicados. Como entendes, trátase de datos ficticios, tomados unicamente por exemplo. Nas táboas reais, estamos a tratar con miles ou mesmo decenas de miles de rexistros.

Opción A: ambas as columnas están na mesma folla. Por exemplo, unha columna A e columna B.

Opción B: As columnas están en follas diferentes. Por exemplo, unha columna A na folla Sheet2 e columna A na folla Sheet3.

Excel 2013, 2010 e 2007 teñen unha ferramenta integrada Eliminar duplicados (Eliminar duplicados) pero é impotente nesta situación xa que non pode comparar datos en 2 columnas. Ademais, só pode eliminar duplicados. Non hai outras opcións como resaltar ou cambiar as cores. E punto!

A continuación, mostrareiche as posibles formas de comparar dúas columnas en Excel, o que che permitirá atopar e eliminar rexistros duplicados.

• Compara 2 columnas e busca duplicados usando fórmulas
  
  • Opción A: ambas columnas están na mesma folla
  • Opción B: As columnas están en diferentes follas ou en diferentes cadernos de traballo
• Procesamento de duplicados atopados
  
  • Mostra só filas duplicadas na columna A
  • Cambia a cor ou resalta os duplicados atopados
  • Elimina os duplicados da primeira columna

Compara 2 columnas en Excel e busca entradas duplicadas usando fórmulas

Opción A: ambas columnas están na mesma folla

1. Na primeira cela baleira (no noso exemplo, esta é a cela C1), escribimos a seguinte fórmula:

   =IF(ISERROR(MATCH(A1,$B$1:$B$10000,0)),"Unique","Duplicate")

   =ЕСЛИ(ЕОШИБКА(ПОИСКПОЗ(A1;$B$1:$B$10000;0));"Unique";"Duplicate")

   

   Na nosa fórmula A1 esta é a primeira cela da primeira columna que imos comparar. 1 $ B$ и 10000 $ B$ estes son os enderezos da primeira e última cela da segunda columna, cos que faremos a comparación. Teña en conta as referencias absolutas: as letras de columna e os números de fila van precedidos dun signo de dólar ($). Eu uso referencias absolutas para que os enderezos das celas sigan sendo os mesmos ao copiar fórmulas.

   Se queres atopar duplicados nunha columna B, cambie as referencias para que a fórmula sexa así:

   =IF(ISERROR(MATCH(B1,$A$1:$A$10000,0)),"Unique","Duplicate")

   =ЕСЛИ(ЕОШИБКА(ПОИСКПОЗ(B1;$A$1:$A$10000;0));"Unique";"Duplicate")

   En cambio "Só"E"Duplicar» Podes escribir as túas propias etiquetas, por exemplo, «Non atopado"E"atopar", ou deixar só"Duplicar' e introduza un carácter de espazo en lugar do segundo valor. Neste último caso, as celas das que non se atopan duplicados permanecerán baleiras e, creo, esta representación dos datos é máis conveniente para unha análise posterior.

2. Agora imos copiar a nosa fórmula en todas as celas da columna C, ata a fila inferior, que contén os datos da columna A. Para iso, move o punteiro do rato á esquina inferior dereita da cela C1, o punteiro terá a forma dunha cruz negra, como se mostra na imaxe de abaixo:Fai clic e manteña premido o botón esquerdo do rato e arrastra o bordo do marco cara abaixo, destacando todas as celas onde queres inserir a fórmula. Cando se seleccionen todas as celas necesarias, solte o botón do rato:

   

Consello: Nas táboas grandes, copiar a fórmula será máis rápido se usas atallos de teclado. Destaque unha cela C1 e prensa Ctrl + C (para copiar a fórmula no portapapeis), a continuación, prema Ctrl + Maiús + Fin (para seleccionar todas as celas que non están en branco na columna C) e finalmente prema Ctrl + V (para inserir a fórmula en todas as celas seleccionadas).

3. Xenial, agora todos os valores duplicados están marcados como "Duplicar":

Opción B: dúas columnas están en follas diferentes (en libros de traballo diferentes)

1. Na primeira cela da primeira columna baleira da folla de traballo Sheet2 (no noso caso é a columna B) introduza a seguinte fórmula:

   =IF(ISERROR(MATCH(A1,Sheet3!$A$1:$A$10000,0)),"","Duplicate")

   =ЕСЛИ(ЕОШИБКА(ПОИСКПОЗ(A1;Лист3!$A$1:$A$10000;0));"";"Duplicate")

   Aquí Sheet3 é o nome da folla na que se sitúa a 2a columna, e $A$1:$A$10000 son os enderezos das celas do primeiro ao último desta segunda columna.

2. Copia a fórmula en todas as celas dunha columna B (igual que a opción A).
3. Obtemos este resultado:

Procesamento de duplicados atopados

Xenial, atopamos entradas na primeira columna que tamén están presentes na segunda. Agora temos que facer algo con eles. Percorrer manualmente todos os rexistros duplicados dunha táboa é bastante ineficiente e leva demasiado tempo. Hai mellores formas.

Mostra só filas duplicadas na columna A

Se as túas columnas non teñen cabeceiras, debes engadilas. Para iso, coloque o cursor sobre o número que representa a primeira liña, e converterase nunha frecha negra, como se mostra na seguinte figura:

Fai clic co botón dereito do rato e selecciona no menú contextual inserción (Inserir):

Dálle nomes ás columnas, por exemplo, "nome"E"Duplicar?» A continuación, abra a pestana Data (Datos) e prema filtro (Filtro):

Despois diso, fai clic na pequena frecha gris ao lado de "Duplicar?« para abrir o menú de filtros; desmarque todos os elementos desta lista excepto Duplicare prema OK.

Iso é todo, agora só ves eses elementos da columna А, que se duplican na columna В. Só hai dúas celas deste tipo na nosa táboa de adestramento, pero, como entendes, na práctica haberá moitas máis.

Para mostrar de novo todas as filas dunha columna А, fai clic no símbolo de filtro na columna В, que agora parece un funil cunha frecha pequena e selecciona seleccionar todo (Seleccionar todo). Ou podes facer o mesmo a través da cinta facendo clic Data (Datos) > Selecciona e filtra (Ordenar e filtrar) > claro (Borrar) como se mostra na seguinte captura de pantalla:

Cambia a cor ou resalta os duplicados atopados

Se as notas "Duplicar” non é suficiente para os teus propósitos e queres marcar as celas duplicadas cunha cor de fonte diferente, cor de recheo ou algún outro método...

Neste caso, filtra os duplicados como se mostra arriba, selecciona todas as celas filtradas e fai clic Ctrl + 1para abrir o diálogo Formatear celas (formato de cela). Como exemplo, imos cambiar a cor de recheo das celas das filas con duplicados a un amarelo brillante. Por suposto, pode cambiar a cor de recheo coa ferramenta Encher (Cor de recheo). casa (Inicio) pero vantaxe do cadro de diálogo Formatear celas (Formato de cela) no que pode configurar todas as opcións de formato ao mesmo tempo.

Agora definitivamente non perderás ningunha cela con duplicados:

Eliminando os valores duplicados da primeira columna

Filtra a táboa para que só se mostren as celas con valores duplicados e selecciona esas celas.

Se as 2 columnas que está a comparar están en follas diferentes, é dicir, en diferentes táboas, fai clic co botón dereito do rato no intervalo seleccionado e selecciona Eliminar fila (Eliminar liña):

prensa OKcando Excel che pide que confirmes que realmente queres eliminar toda a fila da folla e despois limpar o filtro. Como podes ver, só quedan filas con valores únicos:

Se hai 2 columnas na mesma folla, preto un do outro (adxacente) ou non preto un do outro (non adxacente), entón o proceso de eliminación de duplicados será un pouco máis complicado. Non podemos eliminar toda a fila con valores duplicados, xa que isto tamén eliminará as celas da segunda columna. Polo tanto, para deixar só entradas únicas nunha columna А, fai isto:

1. Filtra a táboa para mostrar só os valores duplicados e selecciona esas celas. Fai clic co botón dereito sobre eles e selecciona no menú contextual Borrar contidos (contido claro).
2. Limpa o filtro.
3. Seleccione todas as celas dunha columna А, partindo da cela A1 ata a parte inferior que contén os datos.
4. Prema na Data (Datos) e prema Ordenar da A á Z (Ordenar de A a Z). Na caixa de diálogo que se abre, seleccione Continúa coa selección actual (Ordena dentro da selección especificada) e fai clic no botón negro (Clasificación):
5. Elimina a columna coa fórmula, xa non a necesitarás, a partir de agora só tes valores únicos.
6. Xa está, agora a columna А só contén datos únicos que non están na columna В:

Como podes ver, eliminar duplicados de dúas columnas en Excel usando fórmulas non é tan difícil.