Cómo Crear y Mostrar un Gráfico en una Celda
Este es un sencillo tutorial sobre cómo crear y mostrar un gráfico de barras en una celda; una técnica que funciona muy bien a la hora de crear informes de gestión.
Pasos:
1. En la columna A introduzca los valores que desea mostrar, es decir, en la celda A1 introduzca el valor 10, en la celda A2 20, etc.
2. En la columna B2 introduce la siguiente fórmula: =REPETIR (“n”;A2). Esta fórmula simplemente le dice a Excel que repita el valor almacenado entre “ ” por el número de la celda A1.
3. Cambiar la fuente a “Wingdings”.
4. Consulte el ejemplo 1 del archivo Excel adjunto.
5. Si desea reducir la longitud del gráfico de barras, divida “A1” en la fórmula anterior por 10 o por el número que le parezca más lógico. A modo de ejemplo, la fórmula sería la siguiente =REPETIR (“n”;A2/10). Véase el ejemplo 2 en el Archivo Excel Adjunto.
Hay que tener en cuenta que cambiando la “n” en la fórmula mencionada se pueden mostrar imágenes diferentes. Por ejemplo, una “J” mayúscula mostrará una cara sonriente mientras que una “L” mayúscula mostrará una cara triste. Véase el ejemplo 3 en el Archivo Excel Adjunto.
Tratamiento de Valores Negativos
Las fórmulas anteriores funcionan bien cuando se trata de valores positivos. Sin embargo, si el valor de la columna A es negativo, el gráfico de la columna B cambiará a una cadena formada por una serie de símbolos diferentes, con lo que se perderá el efecto deseado (véase el ejemplo 4 de Archivo de Excel Adjunto).
Una forma de superar esta limitación es mediante una sentencia SI, por ejemplo:
=SI(A21<0;REPETIR(“n”;ABS(A21/10));REPETIR(“n”;A21/10))
Explicación de la fórmula anterior:
1. Supongamos que el valor que se intenta mostrar en un gráfico de barras se encuentra en la celda A21. Este valor también es negativo.
2. La fórmula empieza diciendo que si el valor en A21 es menor que 0, es decir, negativo, entonces repite “n” por el valor absoluto (ABS) contenido en la celda A21 y luego divide este número por 10. Al utilizar el valor absoluto le estás diciendo a Excel que ignore el signo negativo y trate el número como un valor positivo.
3. La siguiente parte de la fórmula le dice a Excel qué hacer si el valor es mayor que 0.
4. Consulte el ejemplo 4 del archivo adjunto.
Sería interesante añadir a lo anterior el uso de formato condicional para cambiar el color del gráfico, por ejemplo, a rojo para los valores negativos y a azul para los positivos. Deje que su imaginación le guíe
El siguiente tutorial describirá cómo crear un gráfico en una celda como la que se muestra en la tabla anterior bajo la columna “Tendencia”.
El gráfico se crea utilizando una función llamada “CellChart”. Se introduce en Excel como cualquier otra función estándar, por ejemplo SUMA, PROMEDIO o BUSCARV, etc. Esta función se denomina “Función definida por el usuario” y no es una función estándar disponible en Microsoft Excel. Debe ser creada por el usuario utilizando VBA.
Cuando se introduce en Excel, la función CellChart tiene el siguiente aspecto:
Si observamos la función CellChart más de cerca, el rango para el gráfico se define en la primera parte de la función, C3:F3 en el ejemplo anterior. A continuación se define el color del gráfico, 203 en el ejemplo anterior.
Ahora, la parte de VBA
1. Entre en la ventana del proyecto VBA haciendo clic con el botón derecho en el nombre de una hoja y seleccionando “Ver Código” o seleccionando “ALT + F11”.
2. En la parte derecha, haga clic con el botón derecho del ratón en el nombre de su proyecto y seleccione insertar “módulo”.
3. Copia y pega el siguiente código en el nuevo módulo que acabas de crear:
'Crea una nueva función llamada CellChart
Function CellChart(Plots As Range, Color As Long) As String
'Define las variables que se utilizarán más adelante en el código
Const cMargin = 2
Dim rng As Range, arr() As Variant, i As Long, j As Long, k As Long
Dim dblMin As Double, dblMax As Double, shp As Shape
'A continuación se calculan las líneas que se utilizarán para el gráfico
Set rng = Application.Caller
ShapeDelete rng
For i = 1 To Plots.Count
If j = 0 Then
j = i
ElseIf Plots(, j) > Plots(, i) Then
j = i
End If
If k = 0 Then
k = i
ElseIf Plots(, k) < Plots(, i) Then k = i End If Next dblMin = Plots(, j) dblMax = Plots(, k) 'El siguiente fragmento de código determina la forma y la posición del gráfico With rng.Worksheet.Shapes For i = 0 To Plots.Count - 2 Set shp = .AddLine( _ cMargin + rng.Left + (i * (rng.Width - (cMargin * 2)) / (Plots.Count - 1)), _ cMargin + rng.Top + (dblMax - Plots(, i + 1)) * (rng.Height - (cMargin * 2)) / (dblMax - dblMin), _ cMargin + rng.Left + ((i + 1) * (rng.Width - (cMargin * 2)) / (Plots.Count - 1)), _ cMargin + rng.Top + (dblMax - Plots(, i + 2)) * (rng.Height - (cMargin * 2)) / (dblMax - dblMin)) 'Define lo que ocurre si se produce un error On Error Resume Next j = 0: j = UBound(arr) + 1 On Error GoTo 0 ReDim Preserve arr(j) arr(j) = shp.Name Next With rng.Worksheet.Shapes.Range(arr) .Group If Color > 0 Then .Line.ForeColor.RGB = Color Else .Line.ForeColor.SchemeColor = -Color
End With
End With
CellChart = ""
End Function
Sub ShapeDelete(rngSelect As Range)
'Define las variables que se utilizarán más adelante en el código
Dim rng As Range, shp As Shape, blnDelete As Boolean
For Each shp In rngSelect.Worksheet.Shapes
blnDelete = False
Set rng = Intersect(Range(shp.TopLeftCell, shp.BottomRightCell), rngSelect)
If Not rng Is Nothing Then
If rng.Address = Range(shp.TopLeftCell, shp.BottomRightCell).Address Then blnDelete = True
End If
If blnDelete Then shp.Delete
Next
End Sub4. Haz clic en el botón Guardar.
5. Haga clic en el pequeño icono de Excel situado en la parte superior derecha del menú “Archivo” para salir de la ventana del proyecto VBA y volver a Excel
6. Introduzca la función CellChart en cualquier celda como se muestra arriba.
7. Consulte el libro de trabajo adjunto para ver un ejemplo práctico de lo anterior.
Para más información sobre este tipo de gráficos en celdas, visite:
