Sugerencias y ayuda para Excel en español. Ejemplos de funciones y fórmulas, gráficos, automatización con Vba, Power Query y PowerPivot
lunes, febrero 06, 2012
Formato condicional numérico
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
martes, enero 10, 2012
Formato condicional en los últimos n valores de una serie
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
jueves, julio 21, 2011
Formato Condicional en Excel 2010 – Cambios y Mejoras
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
sábado, julio 16, 2011
Formato condicional personalizado con iconos en Excel 2007/10
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
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!
lunes, febrero 15, 2010
Diagramas Gantt dinámicos en Excel
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í
.
jueves, octubre 22, 2009
Extraer elementos únicos y repetidos con 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
jueves, agosto 06, 2009
Determinar colores en el 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
lunes, febrero 09, 2009
Formato condicional con iconos en Excel 2007
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