Formato condicional personalizado con iconos en Excel 2007/10

Entre las funcionalidades cuyas sus posibilidades más se han extendido en Excel 2007/10 se cuenta sin dudas el formato condicional. Ya he tratado el tema en varias notas, en general y el uso de iconos en particular.

Un lector me consulta:

Quiero utilizar conjuntos de iconos de la siguiente manera: 1.Valores entre 0 y 4: flecha verde. 2.Valores entre 4 y 10: flecha naranja 3. Resto de valores, bien por debajo de 0 o por encima de 10: flecha roja ¿¿Se puede hacer?? Es que no encuentro ninguna fórmula

En realidad no necesitamos ninguna fórmula (recordemos que estamos usando Excel 2007 o Excel 2010). Podemos usar el conjunto de iconos, pero tendremos que modificar las opciones por defecto que Excel nos ofrece.

En el caso de mi lector, queremos usar tres iconos/flechas diferentes pero para cuatro posibilidades. Vamos a mostrar el proceso de construir la regla.

Supongamos que esta es la lista de valores a la que queremos aplicar el formato condicional (que por comodidad he ordenado de menor a mayor sin que esto sea necesario para aplicar las reglas)

Empezamos por seleccionar el rango de la lista y elegir la opción “conjunto de iconos-4 flechas de color”

Ahora debemos cambiar el formato por defecto que aplica Excel, para lo cual usamos el menú Formato Condicional-Administrar reglas-Editar reglas

Cambiamos la opción Tipo de “porcentual” a “número”

En la primer regla ponemos “>10” y cambiamos el icono usando el botón de iconos (Aclaración: esta opción existe sólo en Excel 2010. Gracias a Carola por llamar mi atención sobre este punto)

Al hacer esto los valores en la segunda regla cambian automáticamente, determinando el límite inferior de la regla

Ponemos como límite superior “4” para esta regla y cambiamos el icono. Esto determina el límite inferior de la tercera regla

La cuarta regla queda definida por defecto. Apretamos “Aceptar” y..voila!

Diferenciar fechas de números en Excel

Ya hemos explicado en este blog que en Excel, las fechas son números. Por ejemplo, la fecha de hoy, 15/07/2011, es para Excel el número 40739 que es el número de días transcurridos desde el 01/01/1900.

Cuando introducimos en una celda un valor que Excel pueda reconocer como fecha, el formato de fecha es aplicado automáticamente. Pero, por debajo del formato en pantalla, Excel sigue “viendo” el número de serie que representa la fecha. Esta captura de pantalla demuestra el proceso

Esta característica puede crearnos dificultades si tenemos que diferenciar en una fórmula entre fechas y números.

Excel cuenta con varias funciones que nos permiten evaluar el tipo de datos que contiene una celda; por ejemplo: ES.PAR, ESTEXTO, ESNUMERO, etc. (todas bajo la categoría Información en el asistente de funciones). Pero no tiene una función “ESFECHA”, que nos permita establecer si la celda evaluada contiene una fecha.

La única alternativa con funciones es usar la función CELDA con el argumento “formato”. Si el resultado de la fórmula es D1, D2, D3, D4 o D5, entonces la celda evaluada contiene una fecha

Por ejemplo

Pero esta función no reconoce todos los formatos legítimos de fechas (aparentemente debido a los formatos regionales)

Una solución más práctica es crear una UDF (función definida por el usuario) aprovechando que en Vba si hay una función para reconocer fechas: IsDate

Creamos la UDF poniendo este código en un módulo común del editor (si lo guardamos en el Personal, la función estará disponible para todos los cuadernos activos)

Function contiene_fecha(rngCell As Range) As Boolean

    contiene_fecha = IsDate(rngCell)
   
End Function

Al aplicar la función obtenemos el resultado “VERDADERO” o “FALSO” de acuerdo al contenido de la celda

Análisis Pareto en Excel 2003

En la nota anterior sobre análisis de datos según el principio de Pareto mostramos cómo crear el cuadro de resultados y el gráfico con Excel. La solución con tablas dinámicas que mostré en esa nota sólo se aplica a Excel 2010.

Entre las mejoras introducidas en Excel 2010 se cuentan seis funcionalidades en la opción “Mostrar como…” de las tablas dinámicas. Entre ellas “% del Total en…”, que calcula el total acumulado como porcentaje del total. Esta funcionalidad no está disponible en las versiones anteriores de Excel.

En esta nota mostraré un rodeo para dar la misma solución que Excel 2003
.
En la tabla de datos agregamos un campo (columna): “Pct del Total” (porcentaje del total)

La fórmula para calcular los valores del campo es

=C2/SUMA($C$2:$C$287)

que sencillamente calcula el porcentaje de cada valor en relación al total. Como hemos definido nuestra tabla como “lista” no debemos preocuparnos por actualizar las fórmulas cuando agreguemos o quitemos valores. Si usamos versiones anteriores a Excel 2003 tendremos que usar rangos dinámicos.

Una vez creado el campo auxiliar, creamos la tabla dinámica con el campo Categoría en el área de las filas y el campo Ventas en el área de datos. Luego ordenamos la tabla de mayor a menor según el campo de Ventas.

En el próximo paso agregamos el campo “Pct del Total” al área de ventas y definimos “Mostrar datos como..” con la opción “Total en…” y “Categoría” como capo de base

Todo el proceso puede verse en este video



Finalmente creamos el gráfico dinámico, donde deberemos hacer algunos cambios para obtener algo humanamente legible. Empezamos por crear el gráfico (un clic al icono de gráficos en la barra de herramientas de las tablas dinámicas). Por defecto Excel crea el gráfico en una hoja separada de manera que si queremos ubicarlo en la misma hoja de la tabla dinámica tendremos que cambiar la ubicación.

El segundo paso es cambiar las proporciones del gráfico (por defecto, estos gráficos dinámicos sufren de enanismo agudo al cambiarlos de ubicación).

Para que la serie “Pct del Total” sea visible tendremos que definirla en un eje Y secundario. La forma más sencilla es usar la barra de herramientas de los gráficos para seleccionar la serie y el menú Formato para cambiar las definiciones

Asegurándonos que la series esté seleccionada cambiamos a “eje secundario” y el tipo de gráfico a “Línea”. Después de hacer otros cambios obtenemos