Gráficos Excel con listas desplegables

Ya hemos visto que podemos usar controles en hojas de cálculo Excel.

Uno de los usos de estos controles es generar listas desplegables (combo boxes). Cuando de hojas de cálculo se trata, podemos usar la funcionalidad Validación de Datos para generar estas listas desplegables. Pero cómo hacemos para poner una lista desplegable en un gráfico de Excel?
La respuesta: con el control cuadro combinado de la barra de herramientas "Formularios"

Supongamos que tenemos esta lista de ventas por mes y por línea de productos



Nuestro objetivo es generar un gráfico como este:



Cada vez que elegimos un mes de la lista desplegable, los datos del gráfico se adaptan a nuestra elección.

Los pasos a seguir son los siguientes:

1 – Preparamos el rango A1:D2 para que contenga los datos del gráfico




en el rango B2:D2 escribimos esta fórmula

=INDICE($B$7:$D$18,$A$3,COINCIDIR(B$1,$B$6:$D$6,0))

y en la celda A2 escribimos esta: =INDICE(A7:A18,A3)

Por ahora las fórmulas dan un resultado #¡VALOR!, cosa que será corregida escribiendo en la celda A3 el número 1 (o cualquier número entre 1 y 12).

2 – Creamos un gráfico de columnas con el rango A1:D2

borramos la leyenda y le damos un color distinto a cada serie (línea de productos).

3 – Activamos la barra de herramientas "Formularios", y cliqueamos el control "cuadro combinado". Lo ubicamos dentro del gráfico y abrimos el menú de formato del control



4 – En la ventanilla "rango de entrada" señalamos el rango que contiene los meses en la tabla de datos (A7:A18); en la ventanilla "vincular con la celda" señalamos A3; en "líneas de unión verticales" escribimos 12. También podemos marcas "sombreado 3D" para obtener este efecto en el control.

Al apretar "aceptar" las fórmulas darán #¡VALOR!, ya que el valor de la celda vinculada (A3) pasa a ser 0. Todo lo que hay que hacer es elegir un mes en la lista desplegable y veremos los resultados en el gráfico.

Otro truco es cambiar el color de fuente de la celda A3 a blanco, para "hacerla desparecer". Otra variante, más elegante tal vez, es ubicar este argumento en una celda que se encuentra "debajo" del gráfico, por ejemplo H10.




Categorías: Graficos_

Technorati Tags: Excel, Graficos en Excel

Gráficos en Excel usando la función REPETIR

En la nota sobre diagramas Gantt con Excel, vimos que podíamos crear diagramas sin usar el menú Gráficos de Excel. En ese caso creamos el diagrama usando el menú Formato Condicional.

También podemos crear gráficos con fórmulas usando la función REPETIR.

La primera vez que vi esta técnica fue en el libro de John Walkenbach "Microsoft Excel 2000 Formulas". Buscando en la red encontré esta nota en JuiceAnalytics. Así que estas son las dos fuentes para esta entrada.

Supongamos esta tabla que presenta los gastos planeados y realizados en un año determinado

Podemos crear un diagrama de barras horizontales que representen el déficit o superávit de cada mes. Los pasos son los siguientes:
1 – creamos una tabla de tres columnas a la derecha de la tabla de datos




2 – En la columna F (déficit) introducimos la fórmula
=SI(D2<0,repetir("n",-redondear(d2*100,0)),"")>0,REPETIR("n",REDONDEAR(D2*100,0)),"")

Obtenemos este diagrama




3 – Para obtener los pequeños cuadrados usamos la fuente Windings. La columa F está alineada a la derecha y la columna G a la izquierda.

Podemos experimentar otras posibilidades en lugar de la letra "n" con la fuente Windings. Por ejemplo podemos usar la línea vertical (caracter 124), con fuente Arial en tamaño 10




Si queremos que las barras horizontales sean "sólidas" podemos cambiar el tamaño de la fuente a 8, abrir el menú de formato de celdas y poner la alineación vertical a "centrar"



Otra mejora sería usar formato condicional para colorear las barras de acuerdo al tamaño de la desviación. Por ejemplo, toda diferencia de más del 15% que aparezca en rojo si es del lado del déficit o en verde si es superávit.
Para eso establecemos dos condiciones en formato condicional




Hay que prestar atención a la referencia semi-relativa en la fórmula ($D2, columna fija, fila variable).




Si los números a representar son todos positivos (o negativos), la fórmula a usar se simplifica a =REPETIR(CARACTER(124),B2) en esta tabla de temperaturas promedio



En lugar de CARACTER(124) se puede tipear directamente el caracter (por lo general a la derecha del teclado, al lado de Enter).

Otra variante es usar espacios en blanco con un carácter al final. Con esta fórmula
=REPETIR(" ",B2)&"X" se obtiene este diagrama




También podemos agregar el valor representado al lado de la barra, con esta fórmula
=REPETIR(CARACTER(124),B2)&" "&B2




Hay que señalar que REPETIR siempre da como resultado, lógicamente, un número entero de repeticiones.



Categorías: Funciones&Formulas_, Graficos_

Technorati Tags: Excel, Diagramas 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