Paneles de Excel: controles interactivos
Si tiene más datos para mostrar en el tablero que no caben en una sola pantalla, puede optar por usar los controles de Excel que vienen como parte de Excel Visual Basic. Los controles más utilizados son las barras de desplazamiento, los botones de opción y las casillas de verificación. Al incorporarlos en el tablero, puede hacerlo interactivo y permitir al usuario ver las diferentes facetas de los datos mediante posibles selecciones.
Puede proporcionar controles interactivos como barras de desplazamiento, casillas de verificación y botones de opción en sus paneles para facilitar que los destinatarios vean de forma dinámica las diferentes facetas de los datos que se muestran como resultados. Puede decidir sobre un diseño particular del tablero junto con los destinatarios y usar el mismo diseño en adelante. Los controles interactivos de Excel son fáciles de usar y no requieren ninguna experiencia en Excel.
Los controles interactivos de Excel estarán disponibles en la pestaña DESARROLLADOR de la cinta.
Si no encuentra la pestaña DESARROLLADOR en la cinta, haga lo siguiente:
- Haga clic en Personalizar cinta en el cuadro Opciones de Excel.
- Seleccione Fichas principales en el cuadro Personalizar la cinta.
- Marque la casilla Desarrollador en la lista de pestañas principales.
- Haga clic en Aceptar. Encontrará la pestaña DESARROLLADOR en la cinta.
Barras de desplazamiento en paneles
Una de las características de cualquier tablero es que cada componente del tablero es lo más compacto posible. Suponga que sus resultados se ven como sigue:
Si puede presentar esta tabla con una barra de desplazamiento como se indica a continuación, sería más fácil navegar a través de los datos.
También puede tener una línea objetivo dinámica en un gráfico de barras con barra de desplazamiento. A medida que mueve la barra de desplazamiento hacia arriba y hacia abajo, la línea objetivo se mueve hacia arriba y hacia abajo y las barras que cruzan la línea objetivo se resaltarán.
En las siguientes secciones, aprenderá cómo crear una barra de desplazamiento y cómo crear una línea de destino dinámica que esté vinculada a una barra de desplazamiento. También aprenderá a mostrar etiquetas dinámicas en barras de desplazamiento.
Crear una barra de desplazamiento
Para crear una barra de desplazamiento para una tabla, primero copie los encabezados de las columnas en un área vacía de la hoja como se muestra a continuación.
Inserte una barra de desplazamiento.
Haga clic en la pestaña DESARROLLADOR de la cinta.
Haga clic en Insertar en el grupo Controles.
Haga clic en el icono de la barra de desplazamiento debajo de Controles de formulario en la lista desplegable de iconos.
Lleve el cursor a la columna I y tire hacia abajo para insertar una barra de desplazamiento vertical.
Ajuste la altura y el ancho de la barra de desplazamiento y alinéela con la tabla.
Haga clic derecho en la barra de desplazamiento.
Haga clic en Control de formato en la lista desplegable.
Aparece el cuadro de diálogo Control de formato.
Haga clic en la pestaña Control.
Escriba lo siguiente en los cuadros que aparecen.
Haga clic en el botón Aceptar. La barra de desplazamiento está lista para usarse. Ha elegido la celda O2 como enlace de celda para la barra de desplazamiento, que toma valores de 0 a 36, cuando mueve la barra de desplazamiento hacia arriba y hacia abajo. A continuación, debe crear una copia de los datos en la tabla con una referencia basada en el valor en la celda O2.
En la celda K3, escriba lo siguiente:
= COMPENSACIÓN (Resumen [@ [S. No.]], $ O $ 2,0).
Presiona el botón Enter. Complete las celdas de la columna copiando la fórmula.
Complete las celdas de las otras columnas copiando la fórmula.
Su tabla dinámica y desplazable está lista para copiarse en su tablero.
Mueva la barra de desplazamiento hacia abajo.
Como puede observar, el valor en el enlace de celda de la celda de la barra de desplazamiento cambia y los datos de la tabla se copian en función de este valor. A la vez, se muestran 12 filas de datos.
Arrastra la barra de desplazamiento hacia abajo.
Las últimas 12 filas de datos se muestran como el valor actual es 36 (como se muestra en la celda O2) y 36 es el valor máximo que ha establecido en el cuadro de diálogo Control de formulario.
Puede cambiar la posición relativa de la tabla dinámica, cambiar el número de filas que se mostrarán a la vez, el enlace de la celda a la barra de desplazamiento, etc. según sus requisitos. Como ha visto anteriormente, estos deben configurarse en el cuadro de diálogo Control de formato.
Creación de una línea objetivo dinámica e interactiva
Suponga que desea mostrar las ventas por región durante los últimos 6 meses. También ha establecido objetivos para cada mes.
Puede hacer lo siguiente:
- Cree un gráfico de columnas que muestre toda esta información.
- Cree una línea objetivo a lo largo de las columnas.
- Haga que la línea objetivo sea interactiva con una barra de desplazamiento.
- Haga que la línea objetivo establezca dinámicamente los valores objetivo en sus datos.
- Resalte los valores que cumplen con el objetivo.
Cree un gráfico de columnas que muestre toda esta información
Seleccione los datos. Inserte un gráfico de columnas agrupadas.
Cree una línea objetivo a través de las columnas
Cambie el tipo de gráfico a combinado. Seleccione el tipo de gráfico como Línea para la serie de destino y Columna agrupada para el resto de la serie.
Cree una tabla base para la línea objetivo. Harás esta dinámica más tarde.
Cambie los valores de la serie de datos para la Línea objetivo a la columna Objetivo en la tabla anterior.
Haga clic en el botón Aceptar.
Cambie el esquema de color de la columna agrupada. Cambie la línea de destino a una línea de puntos verde.
Haga que la línea objetivo sea interactiva con una barra de desplazamiento
Inserte una barra de desplazamiento y colóquela debajo del gráfico y ajústela para que abarque de enero a junio.
Introduzca los parámetros de la barra de desplazamiento en el cuadro de diálogo Control de formato.
Cree una tabla con dos columnas: Mes y Objetivo.
Ingrese los valores basados en la tabla de datos y el enlace de celda de la barra de desplazamiento.
Esta tabla muestra el mes y el objetivo correspondiente según la posición de la barra de desplazamiento.
Haga que la línea objetivo sea dinámica estableciendo los valores objetivo en sus datos
Ahora, está listo para hacer que su línea objetivo sea dinámica.
Cambie los valores de la columna de destino en la tabla base que creó para la línea de destino escribiendo = $ G $ 12 en todas las filas.
Como sabe, la celda G12 muestra el valor objetivo de forma dinámica.
Como puede observar, la Línea de destino se mueve según la barra de desplazamiento.
Resalte los valores que cumplen con el objetivo
Este es el paso final. Desea resaltar los valores que cumplen con el objetivo en cualquier momento.
Agregue columnas al lado derecho de su tabla de datos: Resultados Este, Resultados Norte, Resultados Sur y Resultados Oeste.
En la celda H3, ingrese la siguiente fórmula:
= SI (D3> = $ G $ 12, D3, NA ())
Copie la fórmula en las otras celdas de la tabla. Cambie el tamaño de la mesa.
Como puede observar, los valores en las columnas - East-Results, North-Results, SouthResults y West-Results cambian dinámicamente en función de la barra de desplazamiento (es decir, valor objetivo). Se muestran los valores mayores o iguales al objetivo y los otros valores son solo # N / A.
Cambie el rango de datos del gráfico para incluir las columnas recién agregadas en la tabla de datos.
Haga clic en Cambiar tipo de gráfico.
Haga que la serie Target sea Línea y el resto Columna agrupada.
Para la serie de datos recién agregada, seleccione Eje secundario.
Formatee las series de datos de tal manera que las series Este, Norte, Sur y Oeste tengan un color de relleno naranja y las series East-Results, North-Results, South-Results y WestResults tengan un color de relleno verde.
Ingrese una etiqueta de datos para la línea objetivo y hágala dinámica con la referencia de celda al valor del mes en la tabla de datos dinámicos.
Su gráfico con Target Line dinámico está listo para su inclusión en el tablero.
Puede borrar el eje secundario ya que no es necesario. A medida que mueve la barra de desplazamiento, la Línea de destino se mueve y las barras se resaltarán en consecuencia. Target Line también tendrá una etiqueta que muestra el mes.
Botones de opción (radio) de Excel
Los botones de opción se utilizan normalmente para seleccionar una opción de un conjunto de opciones dado. Siempre está representado por un círculo pequeño, que tendrá un punto cuando se seleccione. Cuando tiene un conjunto de botones de opción, puede seleccionar solo uno de ellos.
En Excel, los botones de opción se denominan botones de opción.
Puede utilizar los botones de opción de Excel en los gráficos para elegir los datos específicos que el lector desea ver. Por ejemplo, en el ejemplo de la sección anterior, ha creado una barra de desplazamiento para obtener una línea objetivo dinámica con valores objetivo basados en el mes. Puede utilizar los botones de opción para seleccionar un mes y, por tanto, el valor objetivo, y basar la línea objetivo en el valor objetivo. Los siguientes serán los pasos:
- Cree un gráfico de columnas que muestre toda esta información.
- Cree una línea objetivo a lo largo de las columnas.
- Haga que la línea objetivo sea interactiva con los botones de opción.
- Haga que la línea objetivo establezca dinámicamente los valores objetivo en sus datos.
- Resalte los valores que cumplen con el objetivo.
Los pasos 1 y 2 son los mismos que en el caso anterior. Al final del segundo paso, tendrá el siguiente cuadro.
Haga que la línea objetivo sea interactiva con los botones de opción
Inserte un botón de opción.
Haga clic en la pestaña DESARROLLADOR de la cinta.
Haga clic en Insertar en el grupo Controles.
Haga clic en el icono del botón de opción en Controles de formulario en la lista desplegable de iconos.
Colóquelo en la esquina superior derecha del gráfico.
Haga clic derecho en el botón Opción. Haga clic en la opción Control de formato en la lista desplegable.
Introduzca los parámetros del botón de opción en el cuadro de diálogo Formato de objeto, en la pestaña Control.
La celda F10 está vinculada al botón de opción. Haga 5 copias del botón de opción verticalmente.
Como puede observar, todos los botones de opción tienen el mismo nombre, denominado Nombres de subtítulos. Pero, internamente, Excel tendrá diferentes nombres para estos botones de opción, que puede ver en el cuadro Nombre. Además, como el botón de opción 1 se configuró para vincularse a la celda F10, todas las copias también se refieren a la misma celda.
Haga clic en cualquiera de los botones de opción.
Como puede observar, el número en la celda vinculada cambia al número de serie del botón de opción. Cambie el nombre de los botones de opción a enero, febrero, marzo, abril, mayo y junio.
Cree una tabla con dos columnas: Mes y Objetivo. Ingrese los valores según la tabla de datos y el enlace de celda de la barra de desplazamiento.
Esta tabla muestra el mes y el objetivo correspondiente según el botón de opción seleccionado.
Haga que la línea objetivo sea dinámica estableciendo los valores objetivo en sus datos
Ahora, está listo para hacer que su línea objetivo sea dinámica.
Cambie los valores de la columna de destino en la tabla base que creó para la línea de destino escribiendo = $ G $ 12 en todas las filas.
Como sabe, la celda G12 muestra el valor objetivo de forma dinámica.
Como puede observar, la Línea de destino se muestra en función del Botón de opción seleccionado.
Resalte los valores que cumplen con el objetivo
Este es el paso final. Desea resaltar los valores que cumplen con el objetivo en cualquier momento.
Agregue columnas al lado derecho de su tabla de datos: Resultados Este, Resultados Norte, Resultados Sur y Resultados Oeste.
En la celda H3, ingrese la siguiente fórmula:
= SI (D3> = $ G $ 12, D3, NA ())
Copie la fórmula en las otras celdas de la tabla. Cambie el tamaño de la mesa.
Como puede observar, los valores en las columnas - East-Results, North-Results, SouthResults y West-Results cambian dinámicamente en función de la barra de desplazamiento (es decir, valor objetivo). Se muestran los valores mayores o iguales al objetivo y los otros valores son solo # N / A.
Cambie el rango de datos del gráfico para incluir las columnas recién agregadas en la tabla de datos.
Haga clic en Cambiar tipo de gráfico.
Haga que la serie Target sea Línea y el resto Columna agrupada.
Para la serie de datos recién agregada, seleccione Eje secundario.
Formatee las series de datos de tal manera que las series Este, Norte, Sur y Oeste tengan un color de relleno naranja y las series East-Results, North-Results, South-Results y WestResults tengan un color de relleno verde.
Agregue una etiqueta de datos dinámica a la línea objetivo con el valor de la celda $ G $ 12.
Limpiar el eje secundario ya que no es necesario.
En la pestaña VER de la cinta, desmarque la casilla Líneas de cuadrícula.
Cambie la opción Etiqueta a Alto en las opciones de Formato de eje. Esto desplaza las etiquetas del eje vertical hacia la derecha, haciendo que la etiqueta de datos de la línea de destino sea visible.
Su gráfico con línea de destino dinámica y botones de opción está listo para su inclusión en el tablero.
Al seleccionar un botón de opción, la línea objetivo se muestra según el valor objetivo del mes seleccionado y las barras se resaltarán en consecuencia. La línea objetivo también tendrá una etiqueta de datos que muestra el valor objetivo.
Casillas de verificación de Excel
Las casillas de verificación se utilizan normalmente para seleccionar una o más opciones de un conjunto dado de opciones. Las casillas de verificación siempre están representadas por pequeños cuadrados, que tendrán una marca de verificación cuando se seleccionen. Cuando tiene un conjunto de casillas de verificación, es posible seleccionar cualquier número de ellas. Por ejemplo,
Puede usar las casillas de verificación de Excel en los gráficos para elegir los datos específicos que el lector desea ver. Por ejemplo, en el ejemplo de la sección anterior, ha creado un gráfico de columnas que muestra los datos de 4 regiones: este, norte, sur y oeste. Puede utilizar las casillas de verificación para seleccionar las regiones para las que se muestran los datos. Puede seleccionar cualquier número de regiones a la vez.
Puede comenzar con el último paso de la sección anterior:
Inserte una casilla de verificación.
Haga clic en la pestaña DESARROLLADOR de la cinta.
Haga clic en Insertar en el grupo Controles.
Haga clic en el icono Casilla de verificación debajo de Controles de formulario en la lista desplegable de iconos.
Colóquelo en la esquina superior izquierda del gráfico.
Cambie el nombre de la casilla de verificación a Este.
Haga clic derecho en la casilla de verificación. Haga clic en Control de formato en la lista desplegable.
Introduzca los parámetros de la casilla de verificación en el cuadro de diálogo Control de formato, en la pestaña Control.
Haga clic en el botón Aceptar. Puede observar que en la celda vinculada C19, se mostrará VERDADERO si selecciona la casilla de verificación y se mostrará FALSO si anula la selección de la casilla de verificación.
Copie la casilla de verificación y péguela 3 veces horizontalmente.
Cambie los nombres a Norte, Sur y Oeste.
Como puede observar, cuando copia una casilla de verificación, la celda vinculada también permanece igual para la casilla de verificación copiada. Sin embargo, dado que las casillas de verificación pueden tener múltiples selecciones, debe hacer que las celdas vinculadas sean diferentes.
Cambie las celdas vinculadas para Norte, Sur y Oeste a $ C $ 20, $ C $ 21 y $ C $ 22 respectivamente.
El siguiente paso es tener solo los datos de las regiones seleccionadas en el gráfico.
Cree una estructura de tabla de la siguiente manera:
- Escriba = IF ($ C $ 19, H3, NA ()) en la celda C21.
- Escriba = IF ($ D $ 19, I3, NA ()) en la celda D21.
- Escriba = IF ($ E $ 19, J3, NA ()) en la celda E21.
- Escriba = IF ($ F $ 19, K3, NA ()) en la celda F21.
- Complete otras filas de la tabla.
Agregue la columna Destino.
Cambie los datos del gráfico a esta tabla.
El gráfico muestra los datos de las regiones seleccionadas que superan el valor objetivo establecido para el mes seleccionado.
