Cree increíbles tarjetas de datos de indicadores clave de rendimiento en Excel

En esta publicación, vamos a aprender cómo crear algunas tarjetas de datos de indicadores clave de rendimiento (KPI) súper geniales en Excel.

Estos son excelentes para llamar la atención sobre una sola métrica como las ventas totales en sus paneles.

En esta publicación, vamos a ser más sofisticados y agregaremos algunas pequeñas adiciones a las tarjetas, como un cambio porcentual con respecto al año anterior y un pequeño gráfico de líneas en el fondo.

Descargar archivo de ejemplo

Para crear estas elegantes tarjetas de KPI, vamos a extraer tablas dinámicas, gráficos dinámicos, GetPivotData, formas y formato de número personalizado de nuestra caja de herramientas de trucos de Excel.

Contenidos
  1. Configuración de datos
  2. Configuración de la tabla dinámica
  3. Extraiga el KPI con GetPivotData
  4. Creación de la tarjeta de datos
    1. Tarjetas de datos de subtotal de subtotal y diferencia porcentual
    2. Crear un encabezado de título de tarjeta de datos
    3. Organizar Alinear y agrupar las formas
  5. Agregar un gráfico de líneas
  6. Organizar el gráfico de líneas sobre la tarjeta de datos
  7. Agregue Slicers para controlar la tarjeta de datos
  8. Conclusiones

Configuración de datos

En este ejemplo, usaré el conjunto de datos AdventureWorks que tiene datos de pedidos para una tienda de bicicletas ficticia.

Estos datos contienen la fecha del pedido y el total del pedido que usaremos para las tarjetas de datos.

Configuración de la tabla dinámica

Luego necesitaremos crear dos tablas dinámicas con estos datos de pedido.

Esto se puede hacer seleccionando una celda dentro de los datos del pedido y yendo a la pestaña Insertar de la cinta y seleccionando el comando Tabla dinámica .

Para la primera tabla dinámica, debemos agregar el campo Fecha de pedido en el área Filas y dos instancias del campo Total del pedido en el área Valores .

Esto debería agrupar la fecha del pedido en años, trimestres y meses automáticamente. Solo necesitaremos la agrupación de año y mes, por lo que podemos eliminar la agrupación de trimestre de la tabla dinámica.

Si Excel no agrupa nuestras fechas de pedido automáticamente, las fechas se enumerarán en las filas de la tabla dinámica y podemos hacer clic derecho sobre ellas y elegir el comando Agrupar del menú.

La primera instancia del total del pedido en el área de valores de nuestra tabla dinámica será una SUM regular . Para la segunda instancia, crearemos un cálculo de diferencia porcentual.

Podemos hacer clic con el botón derecho en los números de nuestro segundo campo Total del pedido y luego elegir Mostrar valores como% de diferencia desde .

Luego podemos elegir Años como nuestro campo base y Anterior como nuestro elemento base. Esto calculará la diferencia porcentual entre el año actual y el año anterior para cada mes.

Para nuestra segunda tabla dinámica, agregaremos la agrupación Fecha de pedido Mes al área Filas , la agrupación Año de fecha de pedido al área Columnas y el campo Total del pedido al área Valores .

Ambas tablas dinámicas se pueden filtrar en los años para mostrar solo los dos años de datos que queremos comparar.

También necesitaremos subtotales en la primera tabla dinámica. Podemos agregarlos seleccionando la tabla dinámica y yendo a la pestaña Analizar ➜ SubtotalesMostrar todos los subtotales en la parte superior del grupo .

Extraiga el KPI con GetPivotData

Ahora podemos usar la fórmula GetPivotData para extraer los números de nuestras tablas dinámicas que aparecerán como el indicador clave de rendimiento. En nuestro caso, serán los pedidos totales de 2016 y la diferencia porcentual de 2016 a 2015.

Primero, debemos activar la función GetPivotData. Esta es la característica (a veces molesta) que crea automáticamente una fórmula GetPivotData cuando intenta hacer referencia a una celda dentro de una tabla dinámica.

Esto debería estar habilitado de forma predeterminada, pero en caso de que no lo esté, vaya a la pestaña Analizar y haga clic en la flecha pequeña al lado de Opciones , luego seleccione Generar GetPivotData (debe tener una pequeña marca de verificación al lado cuando está activado).

En una celda fuera de la tabla dinámica, podemos crear una fórmula que haga referencia al subtotal de 2016 en la tabla dinámica. Esto creará automáticamente una fórmula GetPivotData para hacer referencia al valor dentro de la tabla dinámica.

En otra celda fuera de la tabla dinámica, podemos crear otra referencia GetPivotData al subtotal de diferencia porcentual de 2016.

Tendremos que formatear estos dos valores ya que el formato en las celdas es como se formatearán en nuestras tarjetas de datos.

El subtotal del pedido se puede formatear como una moneda sin decimales. Vaya a la pestaña Inicio y seleccione Moneda en la sección Número de la cinta y ajuste los lugares decimales para que no muestre ninguno.

El subtotal de diferencia porcentual lo agregaremos en un formato personalizado con símbolos de flecha hacia arriba y hacia abajo para valores positivos y negativos. Presione Ctrl + 1 para abrir el cuadro de diálogo Formato de celdas.

  1. Vaya a la pestaña Número .
  2. Seleccione Personalizado de la lista.
  3. Agregue ▲ 0.0 %;▼ -0.0 %en el campo Tipo y presione el botón Aceptar . Esto mostrará una flecha hacia arriba para cualquier número positivo y una flecha hacia abajo para cualquier número negativo.

Creación de la tarjeta de datos

Tendremos que agregar 4 formas de rectángulo a nuestra hoja de cálculo de Excel. Uno es para el fondo, dos para mostrar los subtotales y uno para mostrar un encabezado de título.

Vaya a la pestaña Insertar y haga clic en el botón Formas para seleccionar entre todas las formas. Seleccione la forma Rectángulo y luego haga clic y arrastre en la hoja para dibujar la forma.

Con las 4 formas, formatearemos el relleno y el contorno en función de su propósito.

  1. Para el fondo, el Relleno de forma y el Contorno de forma pueden ser de un color sólido. Más tarde usaremos tanto el blanco como el negro para el color de la fuente, por lo que debemos elegir un color para el relleno que contraste bien con el blanco y el negro. Un contorno negro y un relleno verde funcionarán bien.
  2. Para el subtotal elegiremos Sin relleno y Sin contorno . De esta manera se verá el fondo.
  3. Para el subtotal de porcentaje diferente, también elegiremos Sin relleno y Sin contorno .
  4. Para el título elegiremos un Relleno de forma blanco y un Contorno de forma negro .

Si creamos las formas en este orden, no necesitaremos ajustar el orden de las capas más adelante. La forma de fondo estará debajo de las otras formas.

Tarjetas de datos de subtotal de subtotal y diferencia porcentual

Ahora podemos agregar el subtotal a nuestro rectángulo sin relleno ni contorno.

  1. Seleccione la forma.
  2. En la barra de fórmulas, escriba un = y luego seleccione la celda que contiene la referencia GetPivotData a nuestro valor subtotal.
  3. Vaya a la pestaña Inicio y aumente el tamaño de la fuente y cambie el color de la fuente.
  4. También podemos alinear el texto en el medio y en el centro .

Necesitamos hacer lo mismo con nuestro segundo rectángulo sin relleno ni contorno y nuestro valor subtotal de diferencia porcentual.

Crear un encabezado de título de tarjeta de datos

Nuestra última forma de rectángulo contendrá el encabezado del título de nuestra tarjeta de datos. Simplemente podemos seleccionar la forma y comenzar a escribir nuestro título. Luego, podemos ir a la pestaña Inicio para aumentar el tamaño de la fuente, cambiar el color de la fuente, alinear el texto en el medio y en el centro.

Organizar Alinear y agrupar las formas

Ahora que tenemos todas las formas, podemos seleccionarlas y arrastrarlas para organizarlas como en la imagen de arriba.

Cuando estemos satisfechos con la ubicación aproximada, podremos alinearlos perfectamente con las herramientas de alineación. Seleccione todas las formas (seleccione una y luego presione Ctrl + A ), luego vaya a la pestaña Diseño de herramientas de dibujo ➜ Organizar sección ➜ AlinearAlinear al centro .

También podemos agruparlos para no estropear accidentalmente la alineación. Con todas las formas seleccionadas, vaya a la pestaña Diseño de herramientas de dibujo ➜ Organizar sección ➜ GrupoGrupo .

Agregar un gráfico de líneas

Aquí es donde vamos a usar nuestra segunda tabla dinámica. Selecciónelo y vaya a la pestaña Analizar y presione el comando Gráfico dinámico .

A continuación, elija un gráfico de líneas de las opciones.

También queremos eliminar todos los botones, ejes, líneas de cuadrícula, mover la leyenda hacia abajo y eliminar el relleno y el contorno.

  1. Haga clic con el botón derecho en cualquiera de los botones y seleccione Ocultar todos los botones de campo en el gráfico .
  2. Seleccione ambos ejes y presione Eliminar .
  3. Seleccione las líneas de la cuadrícula y presione Eliminar .
  4. Mover la leyenda a la parte inferior. Seleccione el gráfico y vaya a la pestaña Diseño de herramientas de gráfico dinámico ➜ Agregar elemento de gráficoLeyendaInferior .
  5. Retire cualquier relleno.Ficha Formato de herramientas de gráfico dinámicoRelleno de forma ➜ Sin relleno .
  6. Eliminar cualquier contorno.Ficha Formato de herramientas de gráfico dinámicoContorno de formaSin contorno .
  7. También podemos cambiar el texto de la leyenda a blanco para una mejor visibilidad contra nuestro fondo verde. Seleccione la leyenda y vaya a la pestaña Inicio para cambiar el color de la fuente.
  8. Cambie la paleta de colores del gráfico a una que contraste bien con el fondo.Pestaña Diseño de herramientas de gráfico dinámicoCambiar colores ➜ Elija de una de las paletas de colores.

Organizar el gráfico de líneas sobre la tarjeta de datos

Ahora, podemos colocar el gráfico sobre la tarjeta de datos.

Seleccione el gráfico y vaya a la pestaña Formato de herramientas de gráfico dinámico ➜ AlinearAjustar a forma . Esto facilitará la alineación de los bordes del gráfico con los bordes del fondo de la tarjeta de datos.

Ahora arrastre los bordes del gráfico para que queden alineados con el fondo. El gráfico será el objeto de la capa superior desde que se creó por última vez. Queremos que esto esté justo debajo del gran número total de pedidos. Con solo el gráfico seleccionado, vaya a la pestaña Formato de herramientas de gráfico dinámico ➜ Enviar hacia atrásEnviar hacia atrás hasta que esté detrás del número total del pedido.

Ahora podemos agrupar todo junto. Seleccione cada objeto y vaya a la pestaña Formato de herramientas de dibujo ➜ GrupoGrupo .

Ahora tenemos una elegante tarjeta de datos que se puede mover y cambiar de tamaño como un solo objeto.

Agregue Slicers para controlar la tarjeta de datos

Dado que la tarjeta de datos se basa en tablas dinámicas, ¡ahora podemos agregar cortadores para controlar la tarjeta de datos!

Seleccione cualquiera de las tablas dinámicas y vaya a la pestaña Analizar y seleccione el comando Insertar segmentación .

Podemos seleccionar cualquier campo(s) en nuestros datos para una segmentación. En este ejemplo, agregaremos una segmentación para nuestros productos.

También necesitamos conectar la(s) nueva(s) cortadora(s) a la otra tabla dinámica. Haga clic con el botón derecho en la segmentación y seleccione Conexiones de informes en el menú, luego marque ambas tablas dinámicas en el cuadro de diálogo Conexiones de informes.

Ahora, la tarjeta de datos mostrará los pedidos totales de 2016 para cualquier conjunto de productos seleccionados en la segmentación, así como el cambio porcentual desde 2015.

Conclusiones

Con un poco de trabajo y creatividad, podemos hacer algunas cosas geniales para nuestros tableros de Excel.

Configurar correctamente las tablas dinámicas para extraer las métricas que queremos mostrar en las tarjetas de datos es esencial para todo el proceso. Las tablas dinámicas se encargan de resumir las métricas clave de nuestros datos.

Sin embargo, las formas no pueden hacer referencia directamente a los valores en nuestras tablas dinámicas, por lo que es necesario usar la fórmula GetPivotData como un paso intermedio para hacer referencia a los valores.

Me encantaría saber qué tipo de efectos visuales geniales para el tablero has podido crear. ¡Házmelo saber en los comentarios!

Deja una respuesta

Tu dirección de correo electrónico no será publicada. Los campos obligatorios están marcados con *

Subir

Utilizamos cookies para asegurar que damos la mejor experiencia al usuario en nuestra web. Si sigues utilizando este sitio asumiremos que estás de acuerdo.