Formato condicional numérico

Josep me consulta cómo cambiar el formato numérico de una celda en función del valor de otra celda (celda de control). La idea es que si en la celda de control aparece el símbolo del Euro (€) el número de la celda formada condicionalmente reciba el forma “moneda-Euro”; si en la celda de control aparece el símbolo del dólar ($), el formato del número cambiará de acuerdo.

Microsoft introdujo en Excel 2007 (y en Excel 2010) la posibilidad de dar formato condicional numérico. Con las nuevas versiones de Excel podemos podemos hacer esto:

y si cambiamos el símbolo, veremos esto

En la celda B2 usamos Validación de Datos con la opción Lista

En E2 definimos dos reglas para el formato condicional

En las versiones de Excel Clásico (97-2003) no tenemos la posibilidad de definir el formato numérico con Formato Condicional. Así que tendremos que echar mano al Vba (macros). Lo que haremos es programar un evento en la hoja relevante

Private Sub Worksheet_Change(ByVal Target As Range)

    If Union(Target, Range("B2")).Address = Range("B2").Address Then
        If Range("B2") = "€" Then Range("E2").NumberFormat = "[$€-2] #,##0.00"
        If Range("B2") = "$" Then Range("E2").NumberFormat = "[$$-409]#,##0.00"
    End If
  
End Sub

¿Cómo saber qúe código de formato usar (por ejemplo "[$€-2] #,##0.00" para el Euro)? Formamos una celda con el formato deseado; abrimos el menú de formato de celdas y en Número elegimos la opción Personalizado. En la ventanilla Tipo veremos la sintaxis del formato

Formato condicional en los últimos n valores de una serie

El lector Willy me consultaba en la nota de ayer cómo aplicar un fondo con formato condicional a las celdas que participan en el cálculo (las últimas n celdas de la serie). La consulta es interesante ya que esto nos permite un control visual efectivo de los valores comprendidos en el cálculo. Si bien le respondí en un comentario en la nota, vale la pena ampliar el tema.

Formato condicional funciona con fórmulas booleanas, es decir, que el resultado debe ser VERDADERO o FALSO. Sólo cuando el resultado es VERDADERO, se aplica el formato elegido.

Dado esto, nuestra táctica será calcular si la fila de la celda evaluada cae dentro del rango de la fórmula. Por ejemplo, si queremos marcar las últimas 10 filas

seleccionamos el rango C5:C160 (siguiendo con nuestro ejemplo) y en “formato condicional-nueva regla-utilice fórmula…” usamos

Y(FILA(C5)>=CONTAR($C$5:$C$160)+5-$C$1,FILA(C5)<=CONTAR($C$5:$C$160)+4)

donde C1 contiene el número de valores a incluir en el cálculo.

La fórmula funciona así: calculamos el número fila de la primer celda en el rango con

 CONTAR($C$5:$C$160)+5-$C$1

 Calculamos el número de fila del última valor en el rango con

 CONTAR($C$5:$C$160)+4

 donde 4 es el número de filas por encima de la primer fila del rango de valores.

 Ahora usamos la función FILA para comparar si el número de fila de la celda evaluada cae dentro del rango relevante. Esto lo hacemos con la función Y, que da VERDADERO sólo si todas las condiciones dentro la fórmula se cumplen.

 Personalmente prefiero una técnica distinta. Por algún motivo que no termino de comprender, muchos usuarios evitan usar columnas auxiliares en los modelos de Excel. Esto nos lleva casi siempre a crear fórmulas complicadas cuyo principal problema reside en la dificultad de controlar los resultados (o recordar que quisimos hacer cuando volvemos a trabajar con el modelo dos semanas más tarde).

 En el caso que estamos analizando, mi propuesta es la siguiente:

 1 – insertamos 3 columnas a la izquierda de la tabla de datos (dos columnas son suficientes)

 2 – En la celda B2 ponemos la fórmula “=CONTAR(E5:E160)+4”, que calcula la última fila con valores en el rango (tal como hicimos en la fórmula anterior)

 3 – En la celda A2 ponemos “=B2-E1+1” que calcula la primer fila del rango (E1 contiene el número de valores que queremos incluir en el cálculo)

 4 – En la celda B5 ponemos la fórmula “=Y(FILA(E5)>=$A$2,FILA(E5)<=$B$2)” y la copiamos a lo largo del rango a evaluar (en nuestro ejemplo B5:B160)

 Como se ve, creamos una serie de valores VERDADERO (cuando la fila cae dentro del rango) o FALSO (cuando está fuera del rango). Ahora usamos esto valores para accionar el formato condicional 

Como puede apreciarse, la fórmula en el formato condicional es obviamente sencilla, lo mismo que las fórmulas en las columnas auxiliares.







El último toque es ocultar las columnas auxiliares

Descarga del archivo del ejemplo

Formato Condicional en Excel 2010 – Cambios y Mejoras

En la nota sobre formato condicional personalizado con iconos pasé por alto las diferencias y mejoras introducidas en la versión 2010 de Excel (gracias a Carola por llamar mi atención sobre el error). Si bien los cambios y mejoras son menos dramáticos de los introducidos en Excel 2007, vale la pena pasar revista a las diferencias entre Excel 2007 y Excel 2010 en lo que a formato condicional se refiere.

En esta nota veremos los cambios en el formato condicional con conjuntos de iconos. Esta funcionalidad fue introducida en Excel 2007

La limitación en Excel 2007 es que no podemos combinar entre iconos que pertenecen a distintos conjuntos. En Excel 2010 podemos cambiar el icono de cada una de las condiciones

Esta funcionalidad incluye la posibilidad de determinar que bajo determinada condición no aparezca ningún icono

Esto es útil cuando queremos que aparezca un icono sólo cuando se cumple una determinada condición, por ejemplo los tres mejores meses de ventas

Otro cambio es la posibilidad de elegir el conjunto de iconos visualmente desde una lista desplegable

Otra herramienta introducida en Excel 2007 y mejorada en Excel 2010 son las barras de datos.

En este ejemplo aplicamos formato condicional con barra de datos en la versión Excel 2007

Podemos ver que las proporciones entre las barras del mejor mes (Agosto) y el peor (Setiembre) no concuerdan con la diferencia entre los números. Las barras de datos en Excel 2007 no sirven para comparar valores. Este problema fue solucionado en Excel 2010. Los mismos datos en Excel 2010 se ven así

Otras mejoras en formato condicional con barras de datos son:

• Posibilidad de elegir entre relleno degradado y relleno sólido (en Excel 2007 sólo degradado)
• Posibilidad de poner borde a la barra

• Posibilidad de representar valores negativos en forma efectiva

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!

Diagramas Gantt dinámicos en Excel

En los albores de este blog publiqué una nota sobre cómo construir un diagrama Gantt con Excel. Mostramos allí dos posibilidades: usando formato condicional o usando gráficos.

En esta nota mostraremos como construir un diagrama de Gantt dinámico que nos permite mostrar en pantalla tareas que se extienden por períodos muy largos.

Cuando construimos el diagrama de Gantt en Excel con formato condicional, usamos una fila para cada tarea y una celda para cada unidad de tiempo. El modelo general consiste en usar la primer columna para definir las tareas, la segunda para la fecha de iniciación, la tercera para la duración, la cuarta para la fecha de finalización y a partir de la quinta columna usamos cada celda para representar una unidad de tiempo. Este es un ejemplo clásico

Las barras de color del diagrama las logramos usando estas fórmulas de formato condicional:



para las filas pares =Y($C3=F$2,$E3=F$2,RESIDUO(FILA(),2)=0)

para las filas impares =Y($C3=F$2,$E3;=F$2,RESIDUO(FILA(),2)=1)

Para construir la escala del tiempo (en la fila 2) ponemos en la celda F2 esta fórmula

=MIN(C3:C6)+7

que calcula la primer fecha más una semana de la primer tarea a realizar. En la celda G2 ponemos "=F2+7" y así sucesivamente.

Si observamos atentamente veremos que si bien la primer tarea concluye el 15/02/10, el diagrama parece indicar que lo hace el 12/02/10. Esto se debe a que usamos una resolución semanal para mostrar las tareas.
Podemos solucionar este problema usando una resolución diaria. En total necesitamos 90 días (la última fecha es el 26/03/10).

Si bien esto resuelve el problema, nos crea uno nuevo, el diagrama excede los límites de la pantalla

La solución ideal es, por lo tanto, trabajar con una resolución diaria pero que no exceda el ancho de la pantalla.

Empezamos por insertar algunas filas por encima de nuestro diagrama.

En la celda E3 calculamos la primer fecha del proyecto usando la función MIN. En la celda F5 creamos una referencia a la celda E3; en la celda G5 ponemos "=F5+1" y así sucesivamente para crear la escala de tiempo.

En la celda F6 creamos una referencia a la celda F5, seleccionamos el rango que comprende la semana (F6:L6) y aplicamos "combinar y centrar"

Cambiamos el ancho de las columnas de la selección a 0.6, quitamos los bordes interiores y obtenemos este resultado

Nótese que la fecha 01/01/10 parece ocupar una columna pero en realidad comprende 7 columnas, una para cada día de la semana.

Volvemos a aplicar esta técnica a cada grupo de siete columnas hasta obtener este resultado

Hemos mejorado en buena medida nuestro diagrama, pero aún nos queda una cuestión por solucionar.

Nuestro diagrama cubre ahora 13 semanas, con resolución diaria. Pero, ¿que pasa si una tarea se extienda por más de de 90 días del inicio del proyecto?
Esto lo solucionaremos creando un diagrama Gantt dinámico con fórmulas sencillas y una barra de desplazamiento de la barra de formularios.

Introducimos estos cambios en nuestro modelo:

1 – insertamos una columna a la izquierda de la hoja (la tabla del diagrama empieza ahora en la columna C).

2 – en la celda G3 ponemos la fórmula "=MIN(D5:D8)+A2"; el papel a cumplir por la celda A2 será explicado enseguida

3 –reemplazamos la fórmula en la celda G4 por una referencia a la celda G3

4 – en la pestaña Programador abrimos la etiqueta Insertar e insertamos una barra de desplazamiento

5 – definimos los parámetros de la barra de desplazamiento en la pestaña "control" del menú "formato de control"

Nótese que vinculamos el control con la celda A2. Esto hará que cuando movemos el cursor de la barra, la fecha en la celda G3 se va incrementando y así el resto de las celdas.

Para ejemplificarlo cambiamos la cantidad de días en la duración y podemos ver como al desplazar el cursor de la barra, se van modificando las fechas y actualizando el diagrama




El archivo puede descargarse aquí 
.

Extraer elementos únicos y repetidos con fórmulas.

Si tenemos una lista de valores ordenados en una única columna podemos obtener una lista de los valores repetidos o de los valores únicos usando fórmulas.

Supongamos esta lista de nombres. Los nombres repetidos los hemos marcado con un fondo de color usando Formato condicional





Si queremos crear una lista de valores únicos en el rango B2:B19, usamos esta fórmula matricial:

=INDICE(Lista,K.ESIMO.MAYOR((CONTAR.SI(Lista,Lista)=1)*FILA()-1,FILA(INDIRECTO("1:"&FILAS(Lista)))))




El archivo con el ejemplo se puede descargar aquí
Dos observaciones importantes en relación a esta fórmula:

1 – ésta es una fórmula matricial y debe ser introducida apretando simultáneamente Ctrl+Mayúsculas+Enter

2 – ésta es una fórmula matricial “multicelular”, es decir, la misma fórmula da un resultado distinto en cada celda. Por esto primero debemos seleccionar el rango que va a ocupar la fórmula y luego introducirla.

Si queremos obtener una lista de los elementos repetidos, modificamos levemente la fórmula

=INDICE(Lista,K.ESIMO.MAYOR((CONTAR.SI(Lista,Lista)<>1)*FILA()-1,FILA(INDIRECTO("1:"&FILAS(Lista)))))


De la misma manera, si queremos extraer los elementos que se repiten 3 veces (no los hay en el ejemplo), usaríamos

=INDICE(Lista,K.ESIMO.MAYOR((CONTAR.SI(Lista,Lista)=3)*FILA()-1,FILA(INDIRECTO("1:"&FILAS(Lista)))))

Es decir, si queremos extraer los elementos que se repitan n veces, usamos
=INDICE(Lista,K.ESIMO.MAYOR((CONTAR.SI(Lista,Lista)=n)*FILA()-1,FILA(INDIRECTO("1:"&FILAS(Lista)))))
Si queremos que los resultados #¡VALOR! no aparezcan podemos usar Formato condicional





Una breve explicación de las fórmulas:
La fórmula

=(CONTAR.SI(Lista,Lista)=1)

genera un vector de valores VERDADERO o FALSO que multiplicamos por el número de fila para generar una serie ordenada
=(CONTAR.SI(Lista,Lista)=1)*FILA()-1


Restamos 1 para dado que la primer fila en el rango es 2.

Los nombres repetidos dan como resultado -1; los valores únicos dan el número de fila menos 1.
Ahora tenemos que ordenar este vector, para lo que usamos K.ESIMO.MAYOR
=K.ESIMO.MAYOR((CONTAR.SI(Lista,Lista)=1)*FILA()-1,FILA(INDIRECTO("1:"&FILAS(Lista))))


Este vector nos sirve de argumento en la función INDICE para encontrar el elemento indicado.

¿Cómo haríamos para obtener los mismos resultados si los nombres estuvieran divididos en dos listas?




Technorati Tags: MS Excel

Determinar colores en el formato personalizado de números

Uno de los blogs que sigo, Bacon Bits, trae una nota sobre cómo determinar el color de un formato personalizado de números.

En notas anteriores he explicado que podemos determinar el color de un formato de número condicionalmente.

Por ejemplo, si queremos que la fuente de todo número superior a 1000 sea verde, números menores de 500 aparezcan en rojo y los restantes en negro. podemos usar este formato personalizado

[Verde][>1000]General;[Rojo][<500]general;[negro]general

Convengamos que nuestra idea del verde es diferente de la que parece tener Microsoft.

Una solución es renunciar al verde y usar el azul



Otra solución es usar el número de índice del color en la paleta en lugar del nombre explícito del color. De esta manera tenemos total control del color de la fuente.

El único problema a resolver es saber cuál es el número de índice del color que queremos usar. Para averiguar el número de índice del color podemos usar la técnica que mostramos con las macrofunciones de XLM o una macro como ésta:

Sub nr_color()
    Dim cell As Range, rngNrColor As Range
    
    Set rngNrColor = Application.InputBox(prompt:="select range", Type:=8)
    
    For Each cell In rngNrColor
        cell.Offset(0, 1).Interior.ColorIndex = cell.Value
    Next
    
End Sub

En una hoja creamos un rango con una serie de números del 1 al 56 (por ejemplo A1:A56), corremos la macro y obtenemos esta lista (para facilitar la visualización he divido el rango en tres columnas)

Para usar el verde cuyo número de índice (en mi paleta) es el 10, modificamos el formato personalizado de esta manera

[Color10][>1000]General;[Rojo][<500]general;[negro]general>

Es decir, usamos “Color” y el número de índice.

Technorati Tags: MS Excel

Formato condicional con iconos en Excel 2007

Sin lugar a dudas, las posibilidades del formato condicional en Excel 2007 han sido ampliadas enormemente. Sin embargo, algunas de las posibilidades están lejos de ser intuitivas.

Por ejemplo, el uso de las colecciones de iconos. Supongamos, como me plantea un lector, que tenemos una lista de fechas de vencimientos. Queremos sacar partido de los semáforos del formato condicional de Excel 2007 de manera que si la fecha del vencimiento cae más de 30 días de la fecha corriente aparezca un semáforo verde; si la fecha está dentro de los 30 días un semáforo amarillo y si la fecha ha vencido, un semáforo rojo. Es decir, esto:




Empecemos por agregar una columna auxiliar que muestre la diferencia en días entre la fecha corriente y la fecha de vencimiento



Para poner los semáforos en esta columna abrimos el menú de Formato Condicional –Nueva regla, elegimos el conjunto de iconos (semáforo con recuadro), en Tipo elegimos Número, para el semáforo verde ponemos “>30” y para el amarillo “>=0”. Esto hace que el rojo quede para los valores inferiores a 0. Como dije al principio, no muy intuitivo que digamos



El resultado será



Una posibilidad interesante es editar la regla y marcar la opción “Mostrar icono únicamente”



Pero si queremos prescindir de la columna auxiliar tendremos que definir las reglas del formato condicional de esta manera



EL primer cambio es que definimos el tipo de regla como Fórmula; en Valor creamos una referencia a la celda que contiene la fecha corriente ($B$1 en nuestro caso) + 30 para el semáforo verde y $B$1 para el amarillo (el rojo queda definido por defecto)

Technorati Tags: MS Excel