Blog

Gráfico de barras interactivo para Dashboard 📊

Crear gráficos interactivos es una forma muy útil y dinámica de mostrar los datos en Excel. Utilizando las segmentaciones de datos y controles de formulario podemos darle una dinamismo a nuestros gráficos que nos facilitan mucho el análisis de los resultados.

A continuación vamos a ver un gráfico que incluye funcionalidades en ese sentido.

En este gráfico horizontal podemos ver las mayores ventas de distintos países.

Los datos de origen de los que parte tienen una forma tabular, como la siguiente.

Las funcionalidades incluidas en este gráfico y cuya implementación se explica en el vídeo son las siguientes.

Segmentación de datos

Para poder elegir el país hemos tenido que transformar los datos en una tabla y crear una segmentación de datos para el campo país.

De esta forma podremos seleccionar el país del cual deseemos mostrar los datos en el gráfico horizontal.

Control de número (Control de formulario)

Incluimos un control de número para indicarle el número de ciudades del país seleccionado que queremos resaltar como las ciudades con mayores ventas.

Serie resaltada en el gráfico

El color de las ciudades con más ventas, según el valor indicado en el control de número, se muestran con un color de barra más resaltado.

Título del gráfico

El título del gráfico no es un texto estático, sino que se adapta a los valores indicados en el control numérico.

Para ver en detalle cómo diseñar este tipo de gráfico interactivo lo mejor es verlo en el siguiente video explicativo.

Video explicativo:

Suscríbete al canal para no perderte los siguientes videos:

Suscríbete

Introducir datos con Formularios en Excel

Cuando estamos introduciendo datos en Excel, en forma de tabla, nos puede interesar hacerlo utilizando un formulario de entrada de datos. De esa forma se añaden los registros de una forma más visual y amigable.

Para ello, lo primero que tenemos que tener son los datos en forma tabular y transformarlos en una tabla de Excel, para lo cual nos situamos encima de cualquiera de los datos y vamos a la ficha Insertar, pulsando en Tabla.

El comando Formulario

En ninguna de las fichas que aparecen por defecto en Excel aparece el comando, por lo que la forma más rápida de acceder al comando Formulario es añadirlo a la barra de herramientas de acceso rápido.

Desplegamos la flecha que aparece al lado de la barra de herramientas de acceso rápido y pulsamos sobre la opción Más comandos…

En la ventana de Opciones de Excel desplegamos la ventana de «Comandos disponibles en:» y seleccionamos Todos los comandos.

Ahora buscamos el comando Formulario… y lo agregamos a la parte derecha. Después pulsamos Aceptar. Podemos comprobar que aparece a la derecha de la barra de herramientas de acceso rápido el icono del comando Formulario.

Utilización de los formularios

Para hacer uso de los formularios nos situamos encima de cualquier celda de la tabla que hemos creado y en la cual queremos insertar datos. Pulsamos sobre el comando Formulario y se abre la ventana correspondiente al formulario.

Pulsando en el botón Nuevo nos muestra el formulario en blanco para ingresar los datos en la tabla mediante el formulario.

También podemos eliminar el registro que queramos y desplazarnos al registro anterior o siguiente.

e la misma forma podemos movernos por los diferentes datos del formulario filtrando por criterios, incluyendo un valor a incluir (por ejemplo, zona: Norte), un importe (por ejemplo, más de 10000) o el uso de comodines (por ejemplo, Nombre: M* para indicar nombres que comiencen por la letra M.

Para cerrar el formulario pulsaríamos sobre el botón Cerrar.

Para ver en detalle cómo utilizar un formulario en Excel lo mejor es verlo en el siguiente video explicativo.

Video explicativo:

Suscríbete al canal para no perderte los siguientes videos:

Suscríbete

Calcular el cuadro de amortización de un préstamo de cuotas constantes

Vamos a ver la manera de calcular el cuadro de amortización de un préstamo de cuotas constantes.

Los préstamos de cuotas constantes son aquellos en los que siempre se paga el mismo importe a la hora de devolver un préstamo. La cuota está compuesta por la parte de amortización de capital y la parte de intereses.

Si bien la cuota es constante, las cantidades correspondientes a capital e intereses sí que van variando. Conforme pasa el tiempo el importe correspondiente a intereses va disminuyendo. En cambio la parte correspondiente a devolución de capital cada vez es mayor.

Este tipo de préstamos corresponde al denominado sistema francés.

Vamos a ver cómo se calcularía el cuadro de amortización de un préstamo de cuotas constantes en Excel.

Datos del préstamo

Los datos de partida del préstamo para este ejercicio serán los siguientes.

El total del préstamo asciende a 36.000 euros, con un tipo de interés anual del 6,5% a devolver mensualmente en 5 años.

Preparación del cuadro de amortización

Introduciremos los datos de partida en Excel.

Para crear el cuadro de amortización tendremos las columnas: cuota, fecha, cuota, interés, devolución de capital y capital pendiente.

Ya podemos rellenar el número de cuota, desde la cuota inicial (cero) a la última que sería la 60 (5 años corresponde a 60 meses).

En la columna de la fecha podemos indicar las 60 fechas.

Y en cuanto al capital pendiente en la primera fila (cuota 0) podemos indicar el total del préstamo, dado que debemos la devolución de la totalidad del préstamo.

Calcular la cuota del préstamo

Para calcular la cuota del préstamo utilizaremos la función PAGO. Los argumentos obligatorios de la función son:

Tasa: Es el tipo de interés. Dado que queremos calcular las cuotas mensuales y el tipo de interés es anual, debemos dividir este entre 12, para obtener el tipo de interés mensual.
Nper: Número de periodos. 5 años, que en meses son 60 periodos.
Va: El valor actual del préstamo. El importe total del préstamo.

Con todo ello obtenemos una cuota constante de 704,38 euros. Este importe aparece siempre en negativo, por lo que si queremos tener el dato en positivo, una posibilidad es que delante de la función incluyamos el signo negativo.

Para poder arrastrar hacia abajo esta fórmula tendremos que fijar las referencias como absolutas.

Y ya podemos arrastrar hacia abajo esta fórmula.

Calcular el interés

Para el cálculo del tipo de interés utilizaremos la función PAGOINT. Los argumentos obligatorios son:

Tasa: tipo de interés mensual.
Período: corresponde al número de cuota. Este valor no se puede incluir como referencia absoluta dado que debe variar con cada cuota.
Nper: Número de períodos.
Va: Importe del préstamo.

También en este caso escribiremos un signo negativo delante de la fórmula para que el importe aparezca en positivo. De la misma forma fijaremos todas las referencias menos el Período, para poder arrastrar la fórmula hacia abajo.

Cálculo del la devolución del capital

Dado que la cuota es constante y ya hemos calculado el interés, mediante una simple resta calculamos el importe correspondiente a amortización o devolución del capital.

Cálculo del capital pendiente

Para calcular el importe de capital pendiente, y partiendo del importe total del préstamo, únicamente tendremos que restar al importe de capital pendiente, la cantidad de devolución de capital de cada periodo.

Para comprobar que hemos calculado correctamente el cuadro de amortización recomiendo que observemos que el capital pendiente después del pago de la última cuota es cero.

Para ver en detalle cómo hemos seguido todos estos pasos lo mejor es verlo en el siguiente video explicativo.

Video explicativo:

Suscríbete al canal para no perderte los siguientes videos:

Suscríbete

Conectar tablas dinámicas

Nos podemos encontrar con la situación de tener varias tablas dinámicas y que queramos incluir unos segmentadores de datos que filtren a la vez todas las tablas dinámicas.

Esto sería muy indicado, por ejemplo, si tenemos un Dashboard, que utilice varias tablas dinámicas y que queramos que con un mismo segmentador se filtren los datos de todas las tablas de la hoja.

En la siguiente imagen tenemos dos tablas dinámicas y dos segmentadores. Si pulsamos sobre una zona o un contrato, el filtro se aplicará únicamente a la tabla desde la que hemos generado la segmentación.

Para conectar las tablas dinámicas para que se filtren todas ellas a la vez al pulsar una opción del segmentador nos situamos sobre el segmentador y nos vamos a la pestaña Segmentación.

Después pulsamos sobre el comando Conexiones de informes.

Se abre una ventana de Conexión de informe para la Segmentación Zona. Vemos que tiene marcado un check para la tabla dinámica a la que afecta esa segmentación.

Como queremos que afecte a más tablas lo único que tenemos que hacer es marcar el nombre de las tablas dinámicas que queremos que se vean afectadas con ese segmentador..

En nuestro caso, como queremos que se filtren las dos tablas dinámicas, marcamos las dos. Incluso se podrían marcar tablas dinámicas de hojas diferentes.

Eso lo tendríamos que hacer para cada una de las segmentaciones.

Ahora, si pulsamos algunas opciones de los segmentadores, veremos que se aplica a la dos tablas dinámicas que le hemos indicado.

Para ver en detalle cómo hemos seguido todos estos pasos lo mejor es verlo en el siguiente video explicativo.

Video explicativo:

Suscríbete al canal para no perderte los siguientes videos:

Suscríbete

Alternativa a la función FILTRAR

Ya vimos lo útil y potente que resulta la nueva función FILTRAR en Excel.

Sin embargo, actualmente solo está disponible para los suscriptores de Microsoft 365, por lo que los usuarios de versiones anteriores de Excel no pueden utilizar esta función.

Por ello, en este artículo presentaremos dos alternativas a la función FILTRAR en Excel.

Supuesto con la función FILTRAR

En el ejercicio original utilizamos la función FILTRAR para mostrar de una forma dinámica los cursos realizados y los cursos pendientes.

En función del valor que aparece en la columna C, que son las celdas vinculadas a las casillas de verificación, se filtran los nombres de los cursos.

Alternativa a FILTRAR con tablas dinámicas

La forma más inmediata de filtrar los cursos si no tenemos la función FILTRAR sería mediante una tabla dinámica en la que mostramos por filas los cursos que cumplen el filtro de que el curso se encuentre realizado o no.

La apariencia es muy similar a la de utilizar la función FILTRAR. Ello se debe a que hemos ocultado una fila, la fila 10, en la que aparece el filtro del campo «Realizado».

Como vemos, hemos tenido que utilizar dos tablas dinámicas: una para los cursos realizado y otra para los pendientes.

Sin embargo, el empleo de tablas dinámicas presenta un importante inconveniente: las tablas dinámicas no se actualizan automáticamente cuando se cambia algún valor.

Por tanto, si marcamos un curso como realizado, con la función FILTRAR se actualizan los datos de cursos realizados y pendientes, pero con las tablas dinámicas no se actualizan hasta que no le demos expresamente a actualizar.

Dado este inconveniente, veremos otra alternativa a la función FILTRAR.

Filtrar mediante las funciones SI, K.ESIMO.MENOR, INDICE y SI.ERROR

La alternativa a la función FILTRAR que sí nos permite filtrar los datos de una forma dinámica sería utilizando varias funciones y columnas intermedias.

La ventaja respecto del uso de las tablas dinámicas es que se actualizarían automáticamente los datos conforme se cambian los datos de origen.

Los pasos y funciones a utilizar serían los siguientes.

Establecer el orden en función de la fila

Lo primero que haremos es establecer en una columna intermedia un número de orden. Lo ideal es empezar por el número 1 por lo que utilizaremos la función FILA y le restaremos en nuestro caso una unidad, para que aparezca el número de orden empezando por el 1.

Arrastramos hacia abajo la fórmula para obtener el orden de los demás cursos.

Separar el orden de los cursos realizados de los pendientes con función SI

Utilizando la función SI separaremos el número de orden de los cursos realizados en una columna (columna E) de los cursos pendientes (columna F).

Ordenar según realización del curso con la función K.ESIMO.MENOR

Una vez que tenemos separados los números de orden de los cursos, separados según estén realizados o pendientes, los ordenaremos de menor a mayor, utilizando la función K.ESIMO.MENOR.

Eso lo haremos para los dos casos: los cursos realizados y los pendientes.

Filtrar utilizando la función INDICE y SI.ERROR

Ahora buscaríamos el nombre del curso en función de su número de orden.

Para ello utilizaríamos la función INDICE, con la fórmula que se ve en la barra de fórmulas.

Dado que al final aparecen errores de tipo #¡NUM! anidaremos la función INDICE dentro de una función SI.ERROR.

Si ahora ocultamos todas las columnas intermedia, tendríamos un filtro de cursos realizados y pendientes idéntico al uso de la función FILTRAR.

Para ver en detalle cómo hemos seguido todos estos pasos lo mejor es verlo en el siguiente video explicativo.

Video explicativo:

Suscríbete al canal para no perderte los siguientes videos:

Suscríbete

Crear un checklist con casillas de verificación

En este ejercicio veremos cómo podemos crear un checklist con casillas de verificación en Excel. Además, veremos cómo podemos utilizar los resultados para cambiar el formato de los valores marcados y para mostrar en otro lugar de la hoja los valores marcados y desmarcados.

Si bien desde hace pocos meses tenemos la herramienta Checkbox, que nos facilita mucho la utilización de las casillas de verificación, en este video utilizaremos los controles de formulario, dado que los usuarios de versiones anteriores de Excel no tendrán disponible la herramienta checkbox.

Para ver la utilización de la nueva herramienta, actualmente únicamente disponible para usuarios de Microsoft 365, os remito al siguiente video.

Crear un checklist con control de formulario (casillas de verificación)

Queremos obtener algo similar a lo que vemos a continuación.

En la columna A tenemos una serie de cursos y en la columna B unas casillas de verificación, que cuando las marcamos significa que hemos realizado el curso y si está desmarcado es que el curso está pendiente de realización.

Además en las columnas E y G, de una forma dinámica, se muestran qué cursos se han realizado y cuáles están pendientes.

Para descargaros directamente el archivo de Excel podéis pinchar en el siguiente enlace.

Datos iniciales

Partimos de los datos iniciales, que son todos los cursos.

Inserción de las casillas de verificación

Para insertar las casillas de verificación vamos a la pestaña Programador y desplegamos la opción Insertar. Dentro de los controles de formulario seleccionamos la tercera opción, casilla.

Ahora vamos a la celda correspondiente, en la columna B e insertamos la casilla.

Marcando la casilla con el botón derecho del ratón y haciendo doble clic sobre el nombre del control de formulario, le podemos cambiar el nombre.

Esa casilla la podemos copiar tantas veces como necesitemos.

Vincular casilla de verificación con celda

Ahora tenemos 7 casillas de verificación que podemos marcar o desmarcar pero no tienen ninguna funcionalidad asociada.

Para ello tenemos que seleccionar, con el botón derecho del ratón, la casilla y seleccionar Formato de control.

Se abre la ventana Formato de control y dentro de la pestaña Control, debemos rellenar el apartado de Vincular con la celda.

Para el curso de Excel lo vamos a vincular con la celda C2.

Ahora vemos que, cuando marcamos la casilla que está en B2 nos muestra en la celda C2 el texto VERDADERO. En caso de que esté desmarcado, nos aparece FALSO.

Haremos esto para todos los cursos.

Cambiar formato a cursos realizados

Como queremos que cuando el curso esté realizado, éste aparezca en color gris y tachado tendremos que utilizar el formato condicional para ello.

Seleccionamos todos los cursos de la columna A y pulsamos sobre Formato condicional y le damos a Nueva regla…

Es muy importante marcar, si procede, si queremos usar los rótulos en fila superior y columna izquierda.

Nos aparece la ventana de Editar la regla de formato y seleccionamos la opción Utilice una formula que determine las celdas para aplicar formato.

Escribimos la fórmula =C2=VERDADERO y pulsamos sobre el botón formato, donde elegimos el formato que deseemos.

En mi caso he cambiado el color de la fuente y lo indico como tachado.

Ya hemos conseguido que cuando se marque nos cambie el formato del curso y aparezca tachado.

Mostrar listado de cursos realizados y pendientes

Para mostrar en unas columnas aparte qué cursos se han realizado y qué cursos están pendientes utilizaremos la función FILTRAR.

Aplicando esa fórmula aparecerán todos los cursos que en la columna C tienen en valor VERDADERO y, por tanto, se han realizado.

De igual forma haremos para los cursos pendientes.

Con esa fórmula mostraremos de forma dinámica los cursos pendientes de realizar.

Ocultar valores de las celdas vinculadas

Finalmente, para mejorar el diseño de la hoja, vamos a ocultar el valor de las celdas vinculadas con las casillas de verificación, para que no veamos el valor de VERDADERO o FALSO.

Para ello marcamos las celdas de la columna C y le damos al botón derecho del ratón, seleccionando Formato de celdas.

Dentro de la ventana que se abre seleccionamos la categoría Personalizada y en tipo escribimos tres veces el símbolo punto y coma ;;;