Gráfico de barras animado emulando Google Public Data Explorer - Parte 2 de 3

Continuamos una entrada anterior con los pasos para elaborar un gráfico de barras animado emulando Google Public Data Explorer. En esta ocasión explicamos el tercer paso.

1. Título del gráfico dinámico.
2. Formato personalizado del eje vertical.
3. Series ordenadas con los 10 primeros valores en orden descendente.
4. Control de número ActiveX para avanzar y retroceder en el tiempo.
5. Animación con código VBA asociada a dos botones para iniciar y detener la animación

3. Series ordenadas con los 10 primeros valores en orden descendente.

Creamos la tabla dinámica y ordenamos el campo GEO (países) en orden descendente por la suma del número de Value (desempleados). Clic con el botón secundario sobre GEO, Ordenar, Más opciones de ordenación . Allí, clic sobre Descendente y elegimos Sum of value.

Una vez ordenada la tabla dinámica necesitamos crear una tabla, con los 10 primeros países por mes, que será el origen de datos de nuestro gráfico. También necesitamos una celda que será el argumento número de fila de la función índice para encontrar los 10 primeros países de cada mes. Esa celda estará vinculada al control de número con el que incrementar o disminuir su valor.

Si mostramos las fórmulas de la hoja (Alt + º):

Empleamos la fórmula INDICE para obtener los países y el nº de desempleados:

 En el argumento Matriz seleccionamos la columna B de la tabla dinámica con los nombres de países. En el argumento Núm_fila la celda vinculada al cuadro de número. Le añadimos FILA() - 7 para incrementar una fila más al arrastrar la fórmula hacia abajo. El argumento Núm_columna lo dejamos a 0, pues queremos que devuelva la columna B. Hacemos exactamente lo mismo para el nº de desempleados, cambiando la columna B por la C.

Entradas para construir un gráfico animado emulando Google Public Data Explorer:
1. Título del gráfico dinámico.
2. Formato personalizado del eje vertical.
3. Series ordenadas con los 10 primeros valores en orden descendente.
4. Control de número ActiveX para avanzar y retroceder en el tiempo.
5. Animación con código VBA asociada a dos botones para iniciar y detener la animación

Proteger y desproteger hojas mediante VBA

En Excel no es posible proteger más de una hoja a la vez. Es necesario ir una a una. En el grupo Cambios de la ficha Revisar, clic en Proteger hoja.

No obstante, podemos emplear VBA para proteger varias hojas de un libro, en este caso todas:

Sub ProtegerHojas()
Dim i As Integer
    For i = 1 To Sheets.Count
        Sheets(i).Protect Password:="Contraseña"
    Next i
End Sub

Sub DesprotegerHojas()
Dim i As Integer
    For i = 1 To Sheets.Count
        Sheets(i).Unprotect Password:="Contraseña"
    Next i
End Sub

Otra opción, para evitar la contraseña escrita en el código, es usar un cuadro de entrada donde escribiremos la contraseña deseada. En caso de dejarse en blanco, protegerá las hojas sin contraseña.

Sub ProtegerHojas2()
Dim i As Integer, contr As String
contr = InputBox("Contraseña para proteger las hojas")
    For i = 1 To Sheets.Count
        Sheets(i).Protect Password:=contr
    Next i
End Sub

Sub DesprotegerHojas2()
Dim i As Integer, contr As String
contr = InputBox("Contraseña para desproteger las hojas")
    For i = 1 To Sheets.Count
        Sheets(i).Unprotect Password:=contr
    Next i
End Sub

Mostrar u ocultar formas en Excel mediante VBA

En una entrada anterior vimos como mediante el panel de selección podemos cambiar el nombre, ordenar, mostrar, ocultar y seleccionar varios objetos. ↓ Cómo acceder al panel de selección

Una alternativa a mostrar u ocultar objetos manualmente mediante el panel de selección, es usar VBA. Para ilustrarlo con sencillo ejemplo, hemos creado un semáforo cuyo color cambia en función de la selección en un cuadro combinado (verde, ámbar o rojo).

1. Creamos tres círculos del mismo tamaño. Para dibujar un círculo, hacemos clic en elipse, luego en el lugar donde deseemos iniciar el círculo. Para que la forma sea un círculo, mantenemos presionada la tecla MAYÚS mientras dibujamos la forma. A continuación les aplicamos el color de relleno correspondiente y alineamos vertical y horizontalmente.

Es esencial que asignemos a las formas los nombres que usaremos en el código del paso 3 (Shapes: verde, rojo y ambar). Mediante el panel de selección, doble clic sobre el nombre del objeto y tecleamos el nombre escogido. También seleccionando el objeto y en el cuadro de nombres escribimos el nuevo nombre.

2. Insertamos un cuadro combinado (control ActiveX). En propiedades, en ListFillRange le indicamos el rango utilizado para rellenar la lista (Verde, Rojo y Ámbar). Los nombres del rango deben de coincidir con los valores asignados a Case en el código siguiente.

3. Asignamos código al cuadro combinado, haciendo clic en Ver código.

Private Sub ComboBox1_Change()
ActiveSheet.Shapes.Range(Array("verde", "rojo", "ambar")).Visible = False
Select Case ComboBox1.Value
    Case "Verde"
        ActiveSheet.Shapes("verde").Visible = True
    Case "Rojo"
        ActiveSheet.Shapes("rojo").Visible = True
    Case Else
        ActiveSheet.Shapes("ambar").Visible = True
    End Select    
End Sub

El código oculta las tres formas y muestra la forma correspondiente en función del valor del cuadro combinado (Verde, Rojo y Ámbar).

4. Desactivamos el modo diseño y el semáforo estará listo para cambiar según el color seleccionado en el cuadro combinado.

El objetivo no es en modo alguno dificultar lo que sencillamente podemos lograr utilizando un conjunto de iconos aplicando el formato condicional. Es un ejemplo muy simple pero puede servir como punto de partida para empezar a manipular formas y objetos mediante VBA.

Cómo acceder al panel de selección En la ficha Inicio, en el grupo Modificar, haga clic en Buscar y seleccionar y clic en Panel de selección. O bien clic sobre la forma u objeto, Herramientas de dibujo, y en el grupo Organizar clic en Panel de selección.

Limpiar celdas aparentemente en blanco en Excel

En algunos hojas de Excel nos encontramos con celdas que están aparentemente vacías, pero que realmente no lo están. De acuerdo con la documentación:

Este comportamiento puede producirse cuando la celda contiene una cadena de longitud cero. Una cadena de longitud cero puede ser consecuencia de las condiciones siguientes:

• Una fórmula.
• Una operación de copiar y pegar.
• Una celda que contiene una cadena de longitud cero se importa desde una base de datos que admite cadenas de longitud cero y que contiene cadenas de longitud cero.

Para identificar estas celdas, empleamos la fórmula ESBLANCO. Y devolverá FALSO cuando la celda no esté vacía. Como se puede observar, a pesar de estar en blanco, la fórmula devuelve FALSO.

Una vez identificadas, para borrar su contenido disponemos de varias alternativas:

1. En Buscar y reemplazar, dejamos Buscar: vacío y en Reemplazar con: 0. Presionamos Reemplazar todos.

El resultado sería:

2. Mediante código VBA. Es necesario seleccionar el rango que deseamos limpiar antes de ejecutar el código. En lugar de ceros, como con el método anterior, dejaría las celdas en blanco.

-La solución proporcionada por galileogali es:

Option Explicit
Sub limpiar() 
Dim rng As Range, celRng As Range 
Set rng = Selection
For Each celRng In rng 
    If celRng = Empty Then 
        celRng.ClearContents 
    End If 
Next celRng 
End Sub

-Otra solución propuesta por glh es:

Option Explicit 
Sub find_newlines()
Dim c As Object
Dim firstAddress As String
With Selection
        Set c = .Find("", LookIn:=xlValues, LookAt:=xlWhole)
        If Not c Is Nothing Then
            firstAddress = c.Address
            Do
                c.Value = ""
                Set c = .FindNext(c)
                If c Is Nothing Then Exit Do
            Loop While c.Address <> firstAddress
        End If
    End With
End Sub

Gráfico de barras animado emulando Google Public Data Explorer - Parte 1 de 3

Google Public Data Explorer permite explorar series de datos públicas de organismos e instituciones como el Banco Mundial, Eurostat, la OCDE o de la Oficina del Censo de los Estados Unidos. Estos datos se pueden visualizar de diferentes modos (líneas, barras, mapas, etc.) y animar en el tiempo. Ver ejemplo a continuación:



Siguiendo el ejemplo anterior, he tratado de emular en la medida de lo posible, el mismo gráfico animado en Excel. Lo primero es descargar los datos de Eurostat, importarlos en Excel y hacer algún pequeño ajuste para emplearlos como origen de una tabla dinámica. Solamente incluyo los diez primeros países de cada año para aumentar la claridad.

Describiremos por encima el proceso, deteniéndonos sólo en las técnicas más generales y versátiles que se pueden aplicar en otras ocasiones:

1. Título del gráfico dinámico.
2. Formato personalizado del eje vertical.
3. Series ordenadas con los 10 primeros valores en orden descendente.
4. Control de número ActiveX para avanzar y retroceder en el tiempo.
5. Animación con código VBA asociada a dos botones para iniciar y detener la animación

El resultado final sería:

1. Título del gráfico dinámico. 

Insertamos un cuadro de texto vinculado a la celda que cambiará. En la ficha Insertar, grupo Texto, clic sobre Cuadro de texto. Después arrastramos para dibujar el cuadro del tamaño deseado. Mientras está el cursor dentro del cuadro de texto, en lugar de escribir, situamos el cursor en la barra de fórmulas y tecleamos = y clic sobre la celda a vincular. Cambiamos el formato del cuadro, a p.ej, borde sin línea.

2. Formato personalizado del eje vertical.

Para acceder al cuadro de diálogo Dar formato a eje: botón secundario sobre el eje vertical y clic sobre Dar formato a eje o CTRL+1. O bien clic sobre el gráfico y en la ficha Formato en el grupo Selección actual, en el cuadro Elementos de gráfico seleccionamos el EjeVertical (Valor) y clic sobre Aplicar formato a la selección. En Código de formato escribimos: #." M"

Entradas para construir un gráfico animado emulando Google Public Data Explorer:

1. Título del gráfico dinámico.
2. Formato personalizado del eje vertical.
3. Series ordenadas con los 10 primeros valores en orden descendente.
4. Control de número ActiveX para avanzar y retroceder en el tiempo.
5. Animación con código VBA asociada a dos botones para iniciar y detener la animación

Cuadros combinados en cascada en Ms Access

Los cuadros combinados en cascada en un formulario son cuadros combinados sincronizados. La elección de un elemento de un cuadro combinado limita los elementos disponibles en los cuadros combinados subsiguientes. Pueden ser muy útiles para guiar, agilizar y reducir la probabilidad de errores en la búsqueda o la introducción de datos en un formulario. Por ejemplo, si en nuestro ejemplo seleccionamos Castilla-La Mancha en CC.AA., en provincia no podremos elegir Granada o en municipio Salamanca.

Para construir cuadros combinados dependientes necesitamos:

1. Orígenes de filas con consultas cuyos criterios se basen en los cuadros combinados precedentes.
2. Eventos que recalculen los valores de los cuadros combinados dependientes.

Tenemos tres tablas relacionadas Tbl_CCAA, Tbl_Provincias y Tbl_Municipios. Creamos tres cuadros combinados con origen en sus respectivos campos.

1. Cuadro combinado CC.AA.

Accedemos a las propiedades del cuadro combinado haciendo clic sobre icono Hoja de propiedades o seleccionando el cuadro y presionando Alt+Entrar o F4. En la pestaña Datos, en la propiedad Origen de la fila seleccionamos las comunidades autónomas de la tabla homónima:

En la pestaña Eventos, seleccionamos Después de actualizar, clic en el cuadro con tres puntos suspensivos y, a continuación, clic en Generador de código.

En el editor de visual escribimos el siguiente código:

Private Sub CCAA_AfterUpdate()
Provincia.Value = Null
Provincia.Requery
Municipio.Value = Null
Municipio.Requery
End Sub

Cada vez que cambia la selección del cuadro combinado CCAA, asigna un valor nulo a los cuadros Provincia y Municipio, y recalcula ambos. Sin recalcular, el cambio en el cuadro combinado precedente no tendría efecto en las consultas de origen de fila basadas en los mismos. Pues los cuadros combinados se actualizan al abrir el formulario.

2. Cuadro combinado Provincia.

En la Hoja de propiedades, en las pestaña Datos, Origen de la fila, hacemos clic sobre los tres puntos para diseñar una consulta que relacione las tablas Tbl_CCAA y Tbl_Provincias. Introducimos un criterio basado en la selección del cuadro de combinado precedente: CCAA.

En la pestaña Eventos, seleccionamos Después de actualizar, clic en el cuadro con tres puntos suspensivos y, a continuación, clic en Generador de código:

Private Sub Provincia_AfterUpdate()
Municipio.Value = Null
Municipio.Requery
End Sub

Cada vez que cambia la selección del cuadro combinado Provincia, asigna un valor nulo a Municipio, y lo recalcula.

2. Cuadro combinado Municipio.

Repetimos el paso anterior, esta vez con el cuadro municipio. En la Hoja de propiedades, en las pestaña Datos, Origen de la fila, hacemos clic sobre los tres puntos para diseñar una consulta que relacione ambas tablas, Tbl_Provincias y Tbl_Municipios. En criterios acotamos la consulta en función de la elección del cuadro provincias.

Esta vez no es necesario crear el evento Después de actualizar.

Tras estos pasos, ya tendríamos los tres cuadros combinados sincronizados en cascada.

Entradas relacionadas

• Usar un cuadro combinado para buscar al teclear en Access
• Seleccionar elementos en cuadros combinados o de lista