Comparar listas en Excel

Una de los temas más corrientes en Excel es el de la comparación de listas, y su "hermano gemelo" la eliminación o prevención de duplicados.
Ya he tratado el tema en diversas oportunidades, por ejemplo como comparar dos listas usando formato condicional o como evitar duplicados usando validación de datos. También he mostrado como usar consolidación de datos para comparar listas


En esta nota haremos una revisión ordenada del tema. Empecemos por plantearnos un ejemplo. En una misma hoja tenemos dos listas de productos

Para facilitar nuestro trabajo hemos definido dos nombres

lista1 =Hoja1!$A$2:$A$11
lista2 =Hoja1!$D$2:$D$11

1 - Encontrar duplicados usando la función COINCIDIR

En el rango B2:B11 ponemos la fórmula =COINCIDIR(A2,lista2,0); en el rango E2:E11 ponemos la fórmula =COINCIDIR(D2,lista1,0). El resultado es




Los resultados N/A identifican los valores de la lista 1 que no se encuentran en la lista 2, y viceversa. Podemos lograr un resultado más "elegante" combinando COINCIDIR con SI y ESERROR en esta fórmula

=SI(ESERROR(COINCIDIR(A2,lista2,0)),"","duplicado")

El resultado es




Podemos mejorar aún más la presentación usando formato condicional para dar un fondo de color a los duplicados. Por ejemplo, para la lista2 definimos esta condición



La dirección de la celda de referencia debe ser relativa (sin los signos $)

2 – Señalar duplicados usando formato condicional.

Como explicamos en la nota sobre el tema, usamos la función CONTAR.SI en el menú de formato condicional para comprobar si valores de la lista 1 también figuran en la lista 2. Usamos una fórmula distinta para cada lista.
En la lista 1 usamos la fórmula =CONTAR.SI(lista2,A2)>0





En la lista 2 usamos la fórmula =CONTAR.SI(lista1,A2)>0



El resultado es idéntico al método anterior



3 – Generar una lista de valores comunes a ambas listas

Digamos que ahora queremos generar una lista de valores que aparecen en ambas listas.
En nuestro ejemplo, escribimos esta fórmula en la columna F:

=SI(CONTAR.SI(lista2,A2)>0,A2,"")

Alternativamente podemos usar la fórmula =SI(CONTAR.SI(lista1,D2)>0,D2,""), que dará el mismo resultado aunque en distinto orden.

Como podemos ver en la lista de valores comunes aparecen celdas en blanco. Una alternativa para eliminar las celdas en blanco es copiar los valores de la lista a otro rango y usar la función Ir A--Especial--Celdas en Blanco, como explicamos en esta nota.

También podemos hacerlo usando fórmulas. Para esto necesitaremos crear dos rangos de fórmulas auxiliares.
Siguiendo con nuestro ejemplo, escribimos esta fórmula en la celda H2 y la copiamos a lo largo del rango

=SI(CELDA("contenido",F2)="",0,FILA())

Esta fórmula nos permite dar un número único si en la columna F aparece un producto común a ambas listas o 0 (cero) si el resultado en F es blanco. Usamos la función CELDA y no ESBLANCO, ya ninguna de las celdas en la columna F está en blanco (todas contienen fórmulas).

El próximo paso es usar la función JERARQUIA en la columna I

=JERARQUIA(H2,$H$2:$H$11)

En esta fórmula hemos omitido el argumento opcional "orden", por lo tanto el resultado aparece en forma descendiente.




Podemos ver que para las celda con resultado 0 en la columna H, obtenemos el mismo número de orden.

El resultado en la columna I nos sirve de variable en la fórmula que escribimos en la columna J,

=INDICE($F$2:$F$11,COINCIDIR(FILA()-1,$I$2:$I$11,0))

que nos da esta lista




El problema con esta fórmula es que aparecen resultados N/A. Para evitar esto agregamos una condición a nuestra fórmula, resultando

=SI(ESERROR(INDICE($F$2:$F$11,COINCIDIR(FILA()-1,$I$2:$I$11,0))),"",INDICE($F$2:$F$11,COINCIDIR(FILA()-1,$I$2:$I$11,0)))

es decir, primero evaluamos la fórmula y si el resultado da error, la función SI da como resultado "blanco", si no se aplica la función INDICE.




4 – Generar una lista de valores únicos

Para generar una lista de valores únicos, usamos el mismo método con la diferencia que la fórmula en la columna F pasa a ser

=SI(CONTAR.SI(lista2,A2)=0,A2,SI(CONTAR.SI(lista1,D2)=0,D2,""))

donde combinamos dos condiciones para obtener los productos que sólo aparecen en una de las listas.


Por supuesto, existen otros métodos para comparar listas, incluyendo el uso de macros. Esto será tema de una futura nota.


Categorías: Funciones&Formulas_, Manejo de Datos_

Technorati Tags: Excel, Duplicados en Excel

Gráficos Excel - Agregar rótulos al eje de los valores (Y)

Después de leer mi nota sobre como agregar líneas dinámicas en gráficos de Excel, el jefe de control de calidad de mi empresa me pidió que le preparara el siguiente gráfico:

-en el eje de las X (categorías) aparecen meses del año.

-los valores (Y) son representados por columnas que representan un índice entre 0% y 100% de productos aprobados de la producción del mes.

-en el eje de las Y en lugar de los valores quiere que aparezcan los rótulos "reprobado", "aceptable", "bueno", "muy bueno", "excelente", de acuerdo a los resultados de cada mes.

-partiendo de cada rótulo debe haber una línea horizontal que ayude a interpretar el nivel de resultados alcanzado en cada mes.

Supongamos que esta es la tabla de calificaciones del laboratorio





Y esta la tabla de índices para el año:



El gráfico que queremos construir es el siguiente:



Para lograr este gráfico, con rótulos de texto en el eje de las Y en lugar de valores numéricos, usaremos una combinación de gráfico de columnas y puntos XY.
Mostraremos el proceso paso por paso:

1 - Seleccionamos la tabla de datos (el rango A1:B9) y creamos un gráfico de columnas, sin leyenda y con el título.




2 - Creamos una tabla auxiliar para definir la nueva serie de puntos con los que definiremos las líneas auxiliares. En el rango E3:F9 entramos los valores que definirán los puntos



3 - Seleccionamos el rango E3:F9 y lo copiamos (Ctrl+C). Luego seleccionamos el gráfico con un clic, abrimos el menú Edición—Pegado Especial, y elegimos las opciones señaladas en la imagen



Al pulsar Aceptar aparece una nueva serie de columnas



4 - Seleccionamos la nueva serie y en el menú Tipo de Grafico elegimos el tipo XY (Dispersión) con puntos conectados por líneas. Como resultado aparecen los puntos sobre el eje de las Y y dos ejes secundarios a la derecha y por encima del gráfico



5 - Seleccionamos el eje secundario de las Y, abrimos el menú de formato del eje. En la pestaña Escala fijamos el valor máximo en 100 y el mínimo en 0. En la pestaña Tramas ponemos todos los valores en "ninguna", con lo cual ocultamos el eje.

6 - Hacemos lo mismo con el eje secundario de las X.

7 - Seleccionamos la serie de los puntos XY que aparecen sobre el eje de las Y y abrimos el menú de Formato de serie de datos. En la pestaña Tramas elegimos el color negro para la línea; elegimos un marcador adecuado, con color negro




8 - En la pestaña Barras de Error X, señalamos la opción "por exceso" y ponemos 100 para "valor fijo" (coincide con la escala del eje secundario de las X).



9 - En la pestaña Rótulos de Datos elegimos "nombre de la serie". Luego de pulsar aceptar, seleccionamos los rótulos con un clic y abrimos el menú Formato de rótulos de Datos. En la pestaña Alineación seleccionamos "izquierda" para Posición.



10 – Seleccionamos el eje principal de la Y y los hacemos "desaparecer" poniendo "ninguno" en todas las opciones de la pestaña Tramas.



11 – Ahora nos ocuparemos de los rótulos "Serie 2". En primer lugar borramos el rótulo inferior que aparece en el punto de intersección de las coordenadas. Para esto seleccionamos el rotulo con dos clics y pulsamos "borrar".
Para cambiar el texto de los restantes rótulos tenemos dos opciones: seleccionar cada rótulo con dos clis y escribir el texto correspondiente, o crear un vínculo dinámico a los textos que aparecen en el rango D4:D9.
Para crear un vínculo dinámico, seleccionamos, por ejemplo, el rótulo superior, que debe decir "sobresaliente", luego ponemos el signo "=" en la barra de fórmulas y cliqueamos la celda D9 (donde aparece el texto "sobresaliente").




Volvemos sobre lo mismo para cada uno de los rótulos. De esta manera los valores de los rótulos y de las líneas se adaptarán automáticamente a los cambios en la tabla auxiliar. Es decir, nuestro gráfico es totalmente dinámico.



Categorías: Graficos_

Technorati Tags: Excel, Graficos en Excel

Agregando líneas dinámicas a gráficos de Excel

En mi nota sobre formato de números en un gráfico de Excel, mostraba como agregar líneas horizontales para señalar, por ejemplo, límites de valores.
El problema con el método propuesto es que las líneas que creamos no comienzan en el eje de las Y, lo cual hace que la intención de estas líneas no sea clara



La solución que dábamos era quitar la marca de la opción "Eje de valores (Y) cruza entre categorías" en el menú de formato del eje de las X.
Esto crea un nuevo problema, ya que el rótulo del primer punto de la serie aparece debajo del punto de intersección de los ejes.

En esta nota mostraré un método más avanzado, para agregar líneas a un gráfico. Nuestro objetivo es que estas líneas sean dinámicas. Primero describiremos la técnica y luego daremos algunas explicaciones.

Supongamos la tabla de datos del ejemplo anterior



Como en ese caso, queremos crear una línea que marque el límite superior aceptable (valor = 6,000) y una que marque el límite inferior aceptable (valor = -6,000). Los pasos a dar son los siguientes:

1 - Creamos dos tablas auxiliares, una para el límite superior y otra para el inferior




2 - Seleccionamos el rango G1:H3 y lo copiamos (Ctrl+C).
3 – Seleccionamos el gráfico con un clic, y abrimos el menú Edición—Pegado Especial. En el diálogo que se abre, seleccionamos las opciones "Nueva Serie" y "Columnas"




4 – Seleccionamos la nueva serie de datos, abrimos el menú de gráficos y en el menú Tipo de Gráficos seleccionamos XY (Dispersión) con datos conectados por líneas, sin marcadores de datos



5 – Excel agrega dos eje secundarios, a la izquierda y en la parte superior del gráfico.



Seleccionamos uno de los ejes, y definimos la escala con un máximo de 100, un mínimo de 0, y en la pestaña "Tramas" elegimos la opción "ninguno" para todas las marcas de graduación y para "Líneas" (con lo cual ocultamos el eje).

6 – Seleccionamos el otro eje definimos la escala y los formatos como en el caso anterior. También podemos apretamos F4 para copiar los formatos.



El resultado es el siguiente



Para introducir el límite inferior seleccionamos el rango K1:L3 y volvemos sobres lo indicado en el paso 3. Luego formamos las líneas con el color y el grosor adecuados



Ahora explicaremos el método, es decir, cómo calculamos los valores 75.00 y 25.00.
Hacemos reaparecer por un momento el eje secundario de la Y




Aquí podemos ver que el valor 6,000 en el eje de la izquierda coincide con el valor 75 del eje de la derecha. Y lo mismo respecto a -6,000 y 25.
En ambos ejes los valores están distribuidos a intervalos regulares. Por lo tanto tenemos que calcular en qué posición está ubicado el valor del límite en el eje principal teniendo en cuenta que el valor máximo de la escala (en nuestro caso 12,000) ocupa la posición 0 (cero), el segundo valor (10,000) la posición 1 y así sucesivamente.
En nuestro caso 6,000 ocupa la posición 3 (-6,000 ocupa la posición 9). Para calcular la posición equivalente en el eje secundario dividimos 100 (el valor máximo del eje) por la cantidad de puntos en el eje (sin tomar en cuenta el 0), des decir 100/12 = 8.33
Este es el intervalo entre los puntos del eje. Para calcular el valor multiplicamos este factor por el número de posición y lo restamos de 100; en nuestro caso 100 – (8.33*3) = 75
Construimos una tabla auxiliar




En el rango F3:F5 ponemos las definiciones de la escala del eje.
En la celda F6 calculamos el número de posiciones en el eje con la fórmula =SUMA(ABS(F3),ABS(F4))/F5
En la celda F7 calculamos el valor del intervalo entre cada posición con la fórmula =100/F6
Y en la celda F8 calculamos la posición (número de orden) del punto buscado con la fórmula =(F3-F2)/F5

De esta manera nuestro gráfico es totalmente dinámico. Lo único a tener en cuenta es que si cambiamos la escala del eje principal, debemos cambiar los valores correspondientes en la tabla auxiliar.




Categorías: Graficos_

Technorati Tags: Excel, Graficos en Excel