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

Función JERARQUIA en Excel - Rangos continuos

En la nota sobre la función JERARQUIA vimos que esta función asigna la misma posición relativa a valores duplicados en el rango que estamos considerando. La presencia de números duplicados afecta la jerarquía de los números subsiguientes. Por ejemplo, en una lista de números enteros, si un número aparece dos veces y tiene una jerarquía de 5, entonces el número siguiente en tamaño tendrá una jerarquía de 7 y ningún número en la lista tendrá la jerarquía 6.
En esa nota vimos una técnica para lograr que cada valor en la lista tenga un número de jerarquía único.

Uno de mis lectores me hace la siguiente pregunta:

Tenemos valores con sus jerarquías así:

valor	jerarquía
5	5
7	4
10	1
10	1
8	3

¿No hay manera de que en efecto, los dos valores de 10 tengan la jerarquía de 1 pero el 8 tenga jerarquía de 2, el 7 tenga jerarquía de 3 y el 5 tenga jerarquía de 4?
En otras palabras, lograr un rango continuo de orden de jerarquía.

En esta nota veremos como hacerlo, pero primero daremos un repaso al tema de la función JERARQUIA.

Supongamos esta lista de valores




A los valores duplicados les hemos puesto un fondo de color de acuerdo al orden de aparición, para poder distinguirlos cuando ordenemos la lista de acuerdo al orden de jerarquía.

Aplicamos la fórmula =JERARQUIA(A2, $A$2:$A$11) a la celda B2 y la copiamos al resto del rango (hasta la celda B11)



En el rango E1:F11 mostramos la misma lista ordenada de acuerdo al resultado de JERARQUIA.
Podemos ver que los dos valores 7 reciben el mismo número de posición (2) y que no existe el número de posición 3. Lo mismo sucede con el valor 2 de la lista que también aparece dos veces.

Si queremos que cada valor en la lista reciba un orden de jerarquía único, aplicamos la fórmula

=JERARQUIA(A2, $A$2:$A$11)+CONTAR.SI($A2:A$11,A2)-1




Si queremos revertir el orden de aparición de los duplicados (los de fondo morado aparecerán antes de los de fondo turquesa), usamos la fórmula

=JERARQUIA(A2, $A$2:$A$11)+CONTAR.SI($A$2:$A$11,A2)-CONTAR.SI($A$2:A2,A2)



Si queremos que cada valor muestre su verdadero orden de jerarquía, pero que el rango de jerarquías sea continuo (sin "saltos") creamos primero una tabla corriente con la función JERARQUIA



Luego escribimos esta fórmula matricial en la celda C2

={SUMA((SI($B$2:$B$11<B2,1/CONTAR.SI($B$2:$B$11,$B$2:$B$11),0)))+1}

y la copiamos a lo largo del rango C2:C11



En la tabla E1:G11 podemos comparar los resultados de usar la función JERARQUIA y la fórmula matricial.

Una explicación sobre la fórmula, analizando el resultado de C2:

1 – La expresión $B$2:$B$11<B2 genera esta matriz

$B$2:$B$11<B2	Resultado
9<9	FALSO
7<9	VERDADERO
1<9	VERDADERO
5<9	VERDADERO
9<9	FALSO
2<9	VERDADERO
6<9	VERDADERO
7<9	VERDADERO
2<9	VERDADERO
4<9	VERDADERO

2 – La expresión 1/CONTAR.SI($B$2:$B$11,$B$2:$B$11) genera la siguiente matriz

0.5
0.5
1
1
0.5
0.5
1
0.5
0.5
1

El argumento si_es_falso genera una matriz de 0 (cero)

Finalmente sumamos el resultado de la función SI y le agregamos 1. Para la celda C2 el esquema de cálculo es el siguiente:



Referencias a esta nota: Tushar Metha – Excel




Categorías: Funciones&Formulas_, Manejo de Datos_, Formulas Matriciales_

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