Formato Condicional en Excel con mas de tres condiciones

En más de una nota nos hemos referido al Formato Condicional de Excel. Con Formato Condicional podemos realizar tareas tan diversas como facilitar la lectura de tablas o crear gráficos tipo Gantt.

Esta herramienta tiene una serie de limitaciones:

- sólo permite definir tres condiciones
- sólo podemos dar formato al estilo de las fuentes, a los bordes y a las tramas

Al apretar el botón Formato del diálogo de Formato Condicional vemos este diálogo

Estas limitaciones pueden ser superadas con el complemento (Add In) desarrollado por Frank Kabel y Bob Phillips y que pueden descargar del sitio xlDynamic.

Este complemento permite definir hasta 30 formatos condicionales y extiende la capacidad a formar también números, alineación, fuentes y más.

Para usar este complemento, después de descargar el archivo, tenemos que instalarlo, como con todo complemento.

Para los lectores que no estén familiarizados con la instalación de complementos, he aquí las instrucciones:

1 – Descargamos el archivo y los guardamos en alguna carpeta. Por lo general Excel guarda los complementos en la carpeta Addins bajo el directorio como C:\Program Files\Microsoft Office\Office10\Library

2 – Abrimos Excel y en el menú Herramientas abrimos la opción Complementos

Apretamos el botón Examinar y elegimos la carpeta donde guardamos el complemento. Elegimos el complemento y apretamos Aceptar.

A partir de este momento el complemento está instalado y podemos usarlo




El menú del complemento se instala inmediatamente antes del menú de Ayuda (?).

Veamos ahora un ejemplo del uso de este complemento

Supongamos esta tabla



Para dar a cada celda un formato distinto, basado en el valor de la celda, necesitamos definir 6 formatos condicionales.
Empezamos por activar el complemento apretando el menú xlsTools



Si se trata de un cuaderno Excel que acabamos de abrir veremos este diálogo



Apretamos OK y veremos el diálogo de formato (después de seleccionar el rango A1:A6)



Apretamos el botón Add para empezar a establecer los formatos condicionales. A diferencia del Formato Condicional nativo de Excel, aquí no podemos cliquear las celdas para crear referencias, sino que debemos introducirlas manualmente.

Después de aplicar los formatos, apretando cada vez el botón Add,



luego de apretar OK, vemos, de acuerdo a nuestras definiciones, los siguientes formatos dependientes del valor de cada celda:






Categorías: Manejo de Datos_, Varios_

Technorati Tags: Excel

Formato Condicional en Excel – Ocultar Datos

A veces queremos ocultar datos de una hoja. La opción más común es usar el menú Formato-Fila-Ocultar (o Formato-Columna-Ocultar).
Esta opción no nos permite aplicar ningún criterio, como podríamos hacer con Autofiltro, por ejemplo.
Pero con Formato Condicional y un pequeño truco podemos lograr un efecto similar al de Autofiltro, que también es válido para ocultar datos en columnas.

Supongamos que tenemos esta tabla de ventas de tres productos para los años 2002 – 2006.



Usaremos Formato Condicional y Validación de Datos para crear un mecanismo que nos permita decidir que rango de años mostrar.

Primero agregamos una celda con una lista desplegable para elegir el último año del período a mostrar. En la celda B8 agregamos la lista




Ahora usaremos Formato Condicional para hacer "desaparecer" las celdas que estén fuera del período elegido.

Seleccionamos el rango B1:F5 y abrimos el menú de Formato Condicional. Con la celda B1 seleccionada, elegimos la opción "Fórmula" y escribimos ésta =B$1>$B$8



Es importante poner atención al tipo de dirección de las celdas en la fórmula (B$1 es mixta y $B$8 es absoluta).

Luego apretamos el botón Formato y en la pestaña "Fuente" elegimos el color blanco, para que el contenido de la celda se "mimetice" con el fondo de la celda.



En la pestaña "Bordes" apretamos el botón "ninguno"



Finalmente apretamos "Aceptar".

A partir de este momento, si por ejemplo, elegimos el año 2004 en la celda B8, veremos



Los datos siguen estando, pero se han vuelto invisibles al mimetizarse con el fondo de la celda.
Si queremos compartir nuestro cuaderno, es recomendable proteger las celdas para evitar que un usuario desprevenido borre los datos originales.




Categorías: Manejo de Datos_

Technorati Tags: Excel

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

Formato condicional en Excel – Facilitando la lectura de tablas.

En la nota de ayer sobre diagramas Gantt, mostraba como utilizar formato condicional para alternar los colores de las barras en el diagrama. Esta misma técnica se puede usar para facilitar la lectura de tablas "cargadas" de números.
Supongamos esta tabla de ventas por mes y sucursales:




Para facilitar la lectura, queremos poner un fondo gris fila de por medio. Queremos que la tabla se vea así



Por supuesto podemos hacer esto manualmente. Pero también podemos hacerlo con formato condicional. Seleccionamos el rango de datos de la tabla y luego abrimos el menú de formato condicional. En la ventanilla Condición 1, elegimos la opción "fórmula" y escribimos =RESIDUO(FILA(),2)


La función FILA() da como resultado el número de la fila donde se encuentra la celda. La función RESIDUO da como resultado el residuo o resto de la división entre número y el divisor. Así, por ejemplo, el residuo de dividir 3 por 2 da 1, que Excel interpreta como VERDADERO, y por lo tanto para la fila 3 la condición se cumple.

En nuestro ejemplo la primer fila con fondo gris es la segunda fila de datos. Esto se debe a que en nuestra tabla la primer fila de datos se encuentra en la fila 2, que es par.
Qué hacer si queremos que la primer fila de datos tenga un fondo gris? Cambiamos la fórmula a =RESIDUO(FILA(),2)-1 o =RESIDUO(FILA()-1,2)

Fórmulas equivalentes a estas son:
- para intervalos pares =FILA()=REDONDEA.PAR(FILA())
- para intervalos impares =FILA()=REDONDEA.IMPAR(FILA())

Supongamos ahora que queremos que cada tercer fila tenga un fondo gris. La fórmula que nos permite lograr esto es =NO(RESIDUO(FILA(),3)). La diferencia con la fórmula anterior es que combinamos la función NO en nuestra fórmula.




Si queremos un fondo gris cada 5 líneas, usamos el argumento "5" en lugar de "3"
=NO(RESIDUO(FILA(),5))

En términos generales podemos establecer:
- para intervalos pares usamos la fórmula =RESIDUO(FILA(),número par))
- para intervalos impares la fórmula =NO(RESIDUO(FILA(),número impar))


Categorías: Funciones&Formulas_, Varios_

Technorati Tags: Excel, Formato Condicional

Usar Nombres en Excel para Formato Condicional

Ayer hablamos sobre el uso de "nombre" en Excel. En esa nota decía que el uso de nombres nos permite realizar operaciones que de otra manera no serían posibles.
Veamos un ejemplo. Una de las funcionalidades que empleo a menudo en mis modelos es el "Formato Condicional".

Un ejemplo puede verse en esta nota sobre comparación entre dos listas. En ese ejemplo usamos una fórmula para condicionar el formato.
Ahora supongamos que queremos condicionar el formato de una celda de acuerdo al resultado de una celda que se encuentra en otra hoja. En este ejemplo tenemos en la Hoja1 los totales de ventas para el primer trimestre del año de nuestros agentes de ventas.

En la Hoja2 tenemos las ventas por mes y por agente

Para verificar que no falten datos mensuales queremos hacer un formato condicional en la Hoja1 que de un fondo rojo cuando falte algún dato de ventas de algún agente. Para hacer esto tenemos que detectar si hay alguna celda vacía en el rango B2:D6. La forma de verificar esto es con la función CONTAR. Por ejemplo, si CONTAR(B2:D2) da un resultado menor de 3, señal que falta algún dato. Ahora lo que tenemos que hacer es aplicar un formato condicional en la celdas con los nombre de los agentes, de manera que si falta algún dato reciban un fondo rojo.

Como veremos Excel no nos permitirá hacer esto:
Seleccionamos la celda A2 en la Hoja1, entramos en el menú Formato--->Formato Condicional

En al ventanilla de la condición escribimos la formula =CONTAR(Hoja2!B2:D2)<3>

Elegimos la trama roja, pero cuando apretemos "aceptar" recibiremos esta nota

Excel no nos permite usar referencias a otras hojas en el formato condicional.

La forma de superar este problema es usando nombres. Veamos primero la variante sencilla. Seleccionamos la Hoja2, seleccionamos el rango B2:D2 y en el cuadro de nombres escribimmos el nombre "agente1".

Ahora volvemos a la Hoja1, abrimos el menú Formato--->Formato Condicional, pero usaremos la fórmula =CONTAR(agente1)<3>

Como pueden ver, Excel acepta esta notación y podemos ver que para el Agente 1 nos faltan datos de ventas en la Hoja2.

Una variante un poco más compleja es incluir toda la fórmula en el nombre:

Esta misma técnica la podemos utilizar para superar un problema similar que existe en la Validación de Datos. Tampoco aquí Excel permite referencias a otras hojas, excepto que estas referencias estén incluidas en nombres.


Categorías: Funciones&Formulas_, Manejo de Datos_

Technorati Tags:Formato Condicional, Nombres en Excel

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: comparing two lists, comparar dos listas, conditional formatting

Categorías: Manejo de Datos_