lunes, enero 23, 2006

Cómo comparar dos listas en Excel

Los ejemplos en esta nota fueron desarrollados usando la versión inglesa de Excel. Por favor, lean esta nota.

Otra tarea bastante común en Excel es comparar entre dos listas. El objetivo es encontrar qué diferencias hay entre dos listas (que personas de una lista no aparecen en la otra, por ejemplo). Este tipo de comparación se puede hacer de varias maneras. En esta nota mostraré cómo hacerlo usando Conditional Formatting (Data ---> Conditional Formatting).
Supongamos esta hoja



Para señalar los faltantes en cada lista, daremos un fondo de color azul a los nombre de la lista 1 que no aparecen en la lista 2 y un fondo de color rosa a los nombre de la lista 2 que no aparecen en la lista 1.

Procedemos de la siguiente manera:

  1. seleccionamos el rango de los nombres en la lista 1 (A2:A10)
  2. en la barra del menú pulsamos Format ---> Conditional Formating
  3. seleccionamos Formula Is
  4. en la ventanilla escribimos la formula =COUNTIF($B$2:$B$10,A2)=0 (prestar atención al signo $ en la fórmula)
  5. apretar el botón Format, seleccionar Pattern y elegir el color azul.


Ahora hacemos lo mismo con la segunda lista (el rango será B2:B10) y utilizamos la fórmula

=COUNTIF($A$2:$A$10,B2)=0

Apretamos OK


Ahora podemos ver claramente quien falta en cada lista.

Otra técnica es utilizar la fórmula =MATCH, lo que mostraré en alguna futura nota.


Si te gustó esta nota anotala en del.icio.us


Tags: , ,



Categorías: Manejo de Datos_

31 comentarios:

  1. La formula no funciona. Da "error en la formula". He reproducido el ejemplo y tampoco funciona.
    Un saludo.

    ResponderBorrar
  2. Hola, la fórmula funciona bien. Por favor, fijate que si usas Excel version castellano, tenés que usar los fórmulas en ese idioma (en lugar de COUNTIF debés usar CONTAR.SI). También controlá que los signos $ estén correctamente ubicados en los rangos.
    La nota la escribí antes de tener el Office en castellano, así que usaba fórmulas en inglés para los ejemplos.
    Si seguís teniendo problemas podés mandarme el archivo con la fórmula para ver dónde falla.

    ResponderBorrar
  3. hola

    uso la version inglesa del excel pero la formula no me funciona!


    gracias

    ResponderBorrar
  4. Hola, el problema puede estar en el uso de ";" (punto y coma) en lugar de "," para separar los argumentos de la función.
    También podés mandarme el archivo para que pueda revisarlo.

    ResponderBorrar
  5. Mil gracias! un exelente trabajo, gracias por compartilo

    La formula en excel en castellano quedaria de la siguiente forma

    =CONTAR.SI($B$2:$B$13;A2)=0

    Notar que se usa el ";" en lugar de ","

    ResponderBorrar
  6. Gracias a vos. El uso de "," o ";" como separador de argumentos en las funciones depende de las definiciones regionales en el panel de control. Como mayormente trabajo en inglés mis definiciones usan ",".

    ResponderBorrar
  7. Hola Jorge

    Te felicito por tu excelente trabajo, gracias a ello das a conocer más allá de la frontera del conocimiento que la mayoría de la gente tiene para con Excel, las diversas aplicaciones del mismo.

    Te pido de favor si me haces llegar el ejemplo en excel de controloes activex especificamete el del calendario aplicado a ventas.
    El enlace existe pero me marca error.

    Gracias


    Atentamente.
    Ing. Cesar Sánchez Martínez
    cesar.sanchez.mtz@gmail.com

    ResponderBorrar
  8. Hola César
    gracias por los conceptos. He corregido el enlace. Puedes entrar en la nota y descargar el archivo.

    ResponderBorrar
  9. Hola!

    Excelentes articulos !

    Quisiera saber si proximamente podria explicarnos como generear una lista con valores unicos pero utilizando macros!!

    Muchas Gracias!

    Atte. Gabriela

    ResponderBorrar
  10. Hola Gabriela

    lo anoto en la lista de temas a tratar.

    Gracias por los elogios

    ResponderBorrar
  11. Muchas gracias por la ayuda; es MUY interesante.

    ResponderBorrar
  12. Lo he intentado hacer con listas numericas pero me surje el problema de las duplicidades, necesito que me encuentre el mismo número de coincidencias en la lista 1 y en la dos, no sé si me explico.

    ResponderBorrar
  13. No, no logro entender la consulta. Puede ser un poco más explícito?

    ResponderBorrar
  14. hola jorge, ,e gustaría por favor que me aclararas una duda...
    trabajo con excel 2007, y en reiteradas ocasiones cuando utilizo "formato condicional" al dar formato a la fuente, solo me permite modificar las características de ésta, y no la fuente en sí...

    ahora bien, existe alguna forma de posibilitar la modificación de la fuente?, o la fuente esta predeterminada por excel?, y si es así, existe alguna manera de modificar la fuente predeterminada?

    en mi caso la fuente predeterminada para el formato condicional es "cambria"...
    sino logro modificar dicha situación tendré que dejar de usar la herramienta porque la presentación de mis informes no me parece del todo pulcra...

    gracias de antemano,

    ResponderBorrar
  15. Hola
    por ahora no me ocupo de temas relacionados con XL2007. Te sugiero que averigües en los distintos foros que hay en la WEB.
    De todas maneras, no me parece que el problema esté relacionado con formato condicional.

    ResponderBorrar
  16. Hola,
    Debo comparar la información de dos ficheros que tienen un origen distinto. Contienen, fecha, código de producto y unidades.
    La comprobación que hay que hacer consiste primero en, ordenar los dos listados. Luego, comparar que los códigos de productos que están en la lista A estén en la lista B, de ser así, debo restar el número de productos de A menos B.
    Esta operación hay que hacerla a la inversa, es decir, comparar los que los códigos de productos de la lista B estén también en la A y hacer la operación.
    Visualmente ayuda mucho alinear en cada fila un producto es decir, ir insertando o eliminando celdas para que el contenido de una lista quede alineado en las filas que está el otro listado. Al final, tendré que los procuctos Z de las dos listas están en la fila 102, por ejemplo.
    Me gustaría que me ayudaras a automatizar, mediante alguna fórmula esta manualidad. Es bastante engorroso ir insertando celdas para que los dos listados queden alineados sobre todo si el catálogo supera los 2000 códigos!

    Muchas gracias

    ResponderBorrar
  17. Oscar
    para este tipo de ayudas tienes que contactarte a través del mail. Lo mejor sería que me envíes un archivo con el ejemplo de los que quieres hacer.

    ResponderBorrar
  18. Muchas gracias por la nota, es muy útil.

    ¿Y hay alguna forma de filtrar los resultados según su color (formato)?
    En el ejemplo saltan a la vista, pero si fuera una lista con miles de registros, ¿habría alguna forma de filtrar sólo azules/rosas de cada lista?
    Gracias!

    ResponderBorrar
  19. Si, habría que crear una columna auxiliar donde "traducir" los colores a su número de serie, para luego filtrarlos con Autofiltro.
    Cómo obtener el número de color está explicado en mi nota sobre operaciones con colores en Excel

    ResponderBorrar
  20. Muy buena esta pagina, es muy util,
    Podrias decirme como comparar dos tablas, pero cuando los datos estan en varias columnas.
    ejemplo: orden de compra; linea; articula.
    estaria comparando filas y no solo una celda. ya q en otra fila se pueden repetir datos pero no todos juntos
    muchas gracias;

    ResponderBorrar
  21. Concatenando los datos de las celda de la fila en una columna auxiliar y comparando luego las columnas auxiliares.
    Supongamos que los valores están en las columnas A, B y C. En la columna D creas la columna auxiliar con la fórmula:

    =CONCATENAR(A1,B1,C1)

    o con esta

    =A1&B1&C1

    ResponderBorrar
  22. hlarque@e3display.com22 septiembre, 2010 19:53

    Hola Jorge que tal,

    Esta excelente tu blog, aunque no encuentro algo que pueda ayudarme en mi problema:

    Tengo una hoja con varias columnas y filas que tienen diferentes datos, esta lista es la lista de proyectos de ventas y se actualiza de una manera variable; de esa hoja extraigo los datos de los proyectos que ya son seguros de realizar.
    Necesito saber como hacer para automatizar o agilizar la hoja que yo hago con los proyectos más probables y la de ventas que es la que varia.

    Espero que me haya dado a entender. Un saludo.

    Hugo Larqué

    ResponderBorrar
  23. Hugo,
    tu consulta es muy general. Existen muchas formas de hacerlo. Te sugiero que te pongas en contacto conmigo por mail (fijate ne la condiciones que aparecen en Ayuda)

    ResponderBorrar
  24. No entiendo que pasa, no me funciona, coloco =COUNTIF($G$2:$G$281,C2)=0, y no funciona!. sale que hay error en la formula. ayudaaaaa

    ResponderBorrar
  25. Fijate si alguna de las celdas del rango contiene un resultado de error.

    ResponderBorrar
  26. Muy bueno y bien explicado, Gracias

    ResponderBorrar
  27. Que pasa si la dos lista son diferente, me explico: Columna A (10,5,3,1) Columna B (7,3,1). como hago para compara la los Columna y en la Columna C tenero los repetidos o lo no Repetido, o resaltar los repetido con un color...etc.

    ResponderBorrar
  28. De la misma manera; el tamaño relativo de las listas no tiene importancia.

    ResponderBorrar
  29. Jorge
    junto con saludarete y felicitarte por el blog.... quisiera tu ayuda.

    como puedo hacer para que un xls al abrir revise si existe una nueva version del mismo en una ruta (\\versiones\) y de ser asi se actualice automaticamente por el ultimo.

    gracias por tu tiempo......

    ResponderBorrar
  30. Bien, sencillo no es. Hay que programar un evento (Workbook_Open) que dispare una rutina que haga la tarea.

    ResponderBorrar
  31. Por si alguien tiene Excel 2013 o superior:

    A partir de Excel 2013, ya existe una herramienta para comparar ficheros:

    http://www.sysadmit.com/2015/10/excel-comparar-ficheros.html

    ResponderBorrar

Nota: sólo los miembros de este blog pueden publicar comentarios.