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)),"")
y en la columna H (superávit) ponemos
=SI(D2>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.
2 comentarios:
HOLA
HE TRATADO DE PRACTICAR EL TRUCO DE HACER GRAFICOS CON LA FUNCION REPETIR, PERO A LA HORA DE UTILIZAR LA FUNCION =SI(D2<0,repetir("n",-redondear(d2*100,0)),"")>0,REPETIR("n",REDONDEAR(D2*100,0)),"". LA FUNCION NO FUNCIONA.
AGRADEZCO SU COLABORACION PARA ALA UTILIZACION DE DICHA FORMUULA
Hola Guillermo
se trata de un "error de imprenta" (de la época en que mi manejo del HTML era más pobre que hoy en día). En realidad se trata de dos fórmulas, una para el déficit y otra para el superávit. En la columna F(déficit) hay que poner la fórmula
=SI(D2<0,repetir("n",-redondear(d2*100,0)),"")
y el la columna H (superávit) la fórmula
=SI(D2>0,repetir("n",redondear(d2*100,0)),"")
La entrada será corregida.
Publicar un comentario