Poner fondos en gráficos de columnas Excel

Supongamos, como me consultaba una lectora, que tenemos esta tabla que mide la velocidad de lectura de los alumnos de un curso



Hemos puesto un fondo para resaltar los datos del rango entre 45 palabras a 100 palabras.

A partir de estos datos creamos este gráfico de columnas



¿Cómo hacemos para poner un fondo, tal como en la tabla, en el gráfico? Es decir, queremos obtener este gráfico



La técnica consiste en crear una serie auxiliar para definir el fondo. Éste esta formado por una serie de columnas, donde hemos cambiado la definición de ancho del rango a 0 y hemos cancelado los bordes, para crear la ilusión de una única columna.
Veamos los pasos:

1 - creamos una columna auxiliar en el rango C2:C22



En las celdas paralelas a los valores del rango que queremos resaltar ponemos el valor 70 (el valor máximo del eje Y del gráfico). En las celdas fuera del rango ponemos 0 (o dejamos en blanco).

2 - Agregamos la nueva serie al gráfico. Seleccionamos la serie y la copiamos (Ctrl+C), seleccionamos el gráfico y abrimos el menú Edición-Pegado Especial y en el formulario seleccionamos "nueva serie" y "columnas"



3 - Seleccionamos la primer serie (no la que acabamos de agregar). Abrimos el menú de Formato de Series de Datos y en la pestaña Eje la relacionamos al eje secundario. Nos aseguramos que ambos ejes tengan la misma escala y formato



4 - Seleccionamos la serie que acabamos de agregar y abrimos el menú Formato Serie de Datos. En Tramos ponemos Bordes a Ninguno y elegimos un color claro



En la pestaña Opciones definimos Ancho de rango como 0



En la pestaña Eje nos aseguramos que la serie pertenece al eje principal. A pesar que el sentido común parece decir lo contrario, en los gráficos de Excel las series del eje secundario son representadas en el frente y las del primario en el fondo.

Una técnica similar es crear una columna auxiliar donde todos los valores equivalen al valor superior del eje de la Y del gráfico original



Repetimos los pasos anteriores: copiamos la nueva serie y relacionamos la serie original al eje secundario. Cambiamos las definiciones de la nueva serie, como hicimos en el paso 4. El resultado será



A pesar que vemos sólo una única columna en el fondo, en realidad la serie incluye 21 columnas. Seleccionamos la columna correspondiente al valor 45 (el primero que queremos resaltar en la serie) y le cambiamos el color. Para seleccionamos un punto de la serie (columna en el gráfico) hacemos un clic para seleccionar la serie y un segundo para seleccionar el punto/columna



A continuación seleccionamos las otras columnas y apretamos F4 para copiar el nuevo formato. El resultado será



La primer técnica nos permite refinar nuestro modelo para hacer aún más dinámico. En las celdas C3 y D3 ponemos los límites de los valores del fondo



En el rango E3:E22 introducimos esta fórmula (la fórmula original era matricial y fue reemplazada por esta más sencilla)

=(A3>=$C$3)*(A3<=$D$3)*MAX($B$3:$B$23)



Agregamos la serie al gráfico y seguimos todos los pasos señalados en la primer técnica. Luego movemos el gráfico de manera que oculte la columna. De esta manera podemos cambiar los parámetros en las celda C3 y D3 y veremos los cambios en el gráfico en forma instantánea

Technorati Tags: MS Excel

Actualización simultánea de tablas dinámicas en Excel.

Después de realizar cambios en la base de datos de una tabla dinámica tenemos que apretar el botón de actualización para que los cambios se reflejen en la tabla



Si tenemos más de una tabla dinámica en el cuaderno, tenemos que actualizar cada una por separado. Además de la molestia de tener que hacer varios clics existe también el riesgo de olvidar de actualizar alguna de las tablas.
Aparentemente Excel no tiene ningún método para actualizar todas las tablas simultáneamente, pero podemos usar el botón Actualizar Todo, que por defecto aparece en la barra de herramientas de Datos Externos, para esta tarea. Para agregar el botón a la barra de herramientas Tabla Dinámica hacemos lo siguiente:

1 - pulsamos la flecha de Opciones de la barra de Tablas Dinámicas para abrir la posibilidad Agregar o quitar botones



2 - Elegimos Tabla Dinámica y señalamos el botón Actualizar todo



Al apretar el botón se actualizarán todas las tablas dinámicas del cuaderno. Hay que tomar en cuenta que si hay rangos externos en el cuaderno, éstos se actualizarán.

Technorati Tags: MS Excel

Cuadro de texto flotante en Excel - segunda nota

En la nota anterior sobre cómo crear cuadros de texto flotantes en Excel mostramos una técnica para simular el efecto de un objeto flotante.
La técnica consiste en crear una cuadro de texto y ligarlo a una celda, o mejor aún usar la cámara fotográfica de Excel para crear una imagen dinámica de un rango.
El problema con la técnica que describimos en esa nota es que el objeto se desplaza sólo verticalmente. Es decir que si creamos el objeto con la cámara y lo ubicamos en el área de la columna D, cuando nos movamos hacia la derecha hasta la columna AB, por ejemplo, no veremos el objeto ya que éste no se desplaza horizontalmente.

Para lograr que el objeto se desplace también horizontalmente creamos este evento en el módulo de Vba de la hoja correspondiente

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
    On Error Resume Next
    With Shapes("Picture 2")
        .Left = Target.Offset(0, 1).Left
        .Top = Target.Offset(-1, 0).Top
    End With
    On Error GoTo 0
End Sub


Usamos la propiedad OFFSET para colocar el objeto a la izquierda y arriba de la celda activa. Así por ejemplo, si tenemos una tabla de cotizaciones en el rango A1:B4, después de crear el objeto con la cámara y poner el código en el módulo de la hoja, al seleccionar la celda C6 el cuadro se desplaza de esta manera



Si seleccionamos la celda N3, el cuadro se moverá para sobreponerse al rango O2:P5.



Usamos "On Error Resume Next" para evitar que el código produzca un error y se detenga en caso de seleccionar alguna celda en la fila 1.

Technorati Tags: MS Excel