Gráficos en Excel usando la función REPETIR

viernes, septiembre 01, 2006

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: ,

4 comments:

Anónimo,  02 octubre, 2008 17:45  

Digamos que tenemos 3 valores en 3 columnas consecutivas representando autos, camiones y buses:

a1=20
b1=7
c1=4
y en d1 queremos representar esto en una barra digamos de la siguiente forma:
d1=REPETIR("|";a1)&" "&REPETIR("|";b1)&" "&REPETIR("|";c1)
queda muy bien.
pero un paso más allá, si quisieramos que cada tramo de la barra fuera de un color, podríamos usar la función texto a la que se le puede agregar un formato.
Esto implica tener distintos colores en una misma celda.
yo trate con el siguiente formato pero no me resulta:
#,##0.00 ;-#,##0.00;[Color10];[Color5]
me podrian decir que estoy haciendo mal?

muchas gracias.
Alvaro G.

Jorge L. Dunkelman 03 octubre, 2008 18:51  

No puedes hacer lo que quieres con el formato personalizado. La estructura del formato personalizado es:

[color]número positivo;[color]número negativo;[color]cero;[color]texto

Como puedes ver sólo úedes aplicar un color al texto en la celda (la celda contiene sólo texto, no números)

Anónimo,  03 octubre, 2008 21:20  

Ok, yo entiendo eso, pero la función 'texto' te permite definir el formato del texto que estas creando o convirtiendo. Mi intención era crear, en una celda, una concatenación de dos funciones texto con dos formatos distintos (en este caso el color). Lo que yo quisiera es que estas dos definiciones se mantuvieran por sobre el formato de la celda. Me imagino que tu respuesta va a ser, no no puedes, pero sería fantástico si la respuesta fuera que si se puede.

Saludos

Alvaro G.

Jorge L. Dunkelman 04 octubre, 2008 09:10  

Alvaro
la funcion TEXTO te permite establecer el formato de un valor numérico, no de texto. Por ese motivo no la puedes usar para lo que intentas hacer.

Publicar un comentario

Google+ Followers

Seguidores

Google+ Badge

Términos Legales

  © Blogger template On The Road by Ourblogtemplates.com 2009

Back to TOP