Paneles de Excel - Tablas dinámicas

Si tiene sus datos en una sola tabla de Excel, puede resumir los datos de la forma requerida utilizando las tablas dinámicas de Excel. Una tabla dinámica es una herramienta extremadamente poderosa que puede utilizar para cortar y cortar datos. Puede rastrear, analizar cientos de miles de puntos de datos con una tabla compacta que se puede cambiar dinámicamente para permitirle encontrar las diferentes perspectivas de los datos. Es una herramienta simple de usar, pero poderosa.

Excel le ofrece una forma más eficaz de crear una tabla dinámica a partir de varias tablas, diferentes fuentes de datos y fuentes de datos externas. Se denomina Power PivotTable que funciona en su base de datos conocida como modelo de datos. En otros capítulos, conocerá Power PivotTable y otras herramientas eléctricas de Excel, como Power PivotChart y Power View Reports.

Las tablas dinámicas, Power PivotTables, Power PivotCharts y los informes de Power View son útiles para mostrar resultados resumidos de grandes conjuntos de datos en su tablero. Puede dominar la tabla dinámica normal antes de aventurarse en las herramientas eléctricas.

Crear una tabla dinámica

Puede crear una tabla dinámica a partir de un rango de datos o de una tabla de Excel. En ambos casos, la primera fila de datos debe contener los encabezados de las columnas.

Puede comenzar con una tabla dinámica vacía y construirla desde cero o hacer uso del comando Tablas dinámicas recomendadas de Excel para obtener una vista previa de las posibles tablas dinámicas personalizadas para sus datos y elegir una que se adapte a su propósito. En cualquier caso, puede modificar una tabla dinámica sobre la marcha para obtener información sobre los diferentes aspectos de los datos disponibles.

Considere el siguiente rango de datos que contiene los datos de ventas de cada vendedor, en cada región y en los meses de enero, febrero y marzo:

Para crear una tabla dinámica a partir de este rango de datos, haga lo siguiente:

  • Asegúrese de que la primera fila tenga encabezados. Necesita encabezados porque serán los nombres de los campos en su tabla dinámica.

  • Nombre el rango de datos como SalesData_Range.

  • Haga clic en el rango de datos - SalesData_Range.

  • Haga clic en la pestaña INSERTAR en la cinta.

  • Haga clic en Tabla dinámica en el grupo Tablas.

Aparece el cuadro de diálogo Crear tabla dinámica.

Como puede observar, en el cuadro de diálogo Crear tabla dinámica, en Elija los datos que desea analizar, puede seleccionar una Tabla o Rango del libro actual o usar una fuente de datos externa. Por lo tanto, puede utilizar los mismos pasos para crear un formulario de tabla dinámica, ya sea un rango o una tabla.

  • Haga clic en Seleccionar una tabla o rango.

  • En el cuadro Tabla / Rango, escriba el nombre del rango: SalesData_Range.

  • Haga clic en Nueva hoja de trabajo debajo de Elija dónde desea colocar el informe de tabla dinámica.

También puede observar que puede optar por analizar varias tablas, agregando este rango de datos al modelo de datos. El modelo de datos es una base de datos Excel Power Pivot.

  • Haga clic en el botón Aceptar. Se insertará una nueva hoja de trabajo en su libro de trabajo. La nueva hoja de trabajo contiene una tabla dinámica vacía.

  • Nombra la hoja de trabajo: Range-PivotTable.

Como puede observar, la lista de Campos de tabla dinámica aparece en el lado derecho de la hoja de trabajo, que contiene los nombres de encabezado de las columnas en el rango de datos. Además, en la cinta de opciones, aparecen Herramientas de tabla dinámica: ANALIZAR y DISEÑO.

Debe seleccionar los campos de la tabla dinámica según los datos que desee mostrar. Al colocar los campos en las áreas apropiadas, puede obtener el diseño deseado para los datos. Por ejemplo, para resumir el monto del pedido en términos de vendedor para los meses (enero, febrero y marzo), puede hacer lo siguiente:

  • Haga clic en el campo Vendedor en la lista Campos de tabla dinámica y arrástrelo al área FILAS.

  • Haga clic en el campo Mes en la lista Campos de tabla dinámica y arrástrelo también al área FILAS.

  • Haga clic en Monto del pedido y arrástrelo al área area VALORES.

Su tabla dinámica está lista. Puede cambiar el diseño de la tabla dinámica simplemente arrastrando los campos por las áreas. Puede seleccionar / deseleccionar campos en la lista Campos de tabla dinámica para elegir los datos que desea mostrar.

Filtrado de datos en tabla dinámica

Si debe centrarse en un subconjunto de los datos de la tabla dinámica, puede filtrar los datos en la tabla dinámica en función de un subconjunto de los valores de uno o más campos. Por ejemplo, en el ejemplo anterior, puede filtrar los datos según el campo Rango para que pueda mostrar datos solo para las regiones seleccionadas.

Hay varias formas de filtrar datos en una tabla dinámica:

  • Filtrado mediante filtros de informes.
  • Filtrado mediante Slicers.
  • Filtrar datos manualmente.
  • Filtrado mediante filtros de etiquetas.
  • Filtrado mediante filtros de valor.
  • Filtrado mediante filtros de fecha.
  • Filtrado con el filtro Top 10.
  • Filtrado usando la línea de tiempo.

Conocerá el uso de los filtros de informes en esta sección y los segmentadores en la siguiente. Para otras opciones de filtrado, consulte el tutorial de tablas dinámicas de Excel.

Puede asignar un filtro a uno de los campos para poder cambiar dinámicamente la tabla dinámica en función de los valores de ese campo.

  • Arrastre el campo Región al área FILTROS.
  • Arrastre el campo Vendedor al área FILAS.
  • Arrastre el campo Mes al área COLUMNAS.
  • Arrastre el campo Importe del pedido al área ∑ VALORES.

El filtro con la etiqueta Región aparece encima de la tabla dinámica (en caso de que no tenga filas vacías encima de la tabla dinámica, la tabla dinámica se empuja hacia abajo para dejar espacio para el filtro).

Como puedes observar,

  • Los valores del vendedor aparecen en filas.

  • Los valores de los meses aparecen en columnas.

  • El filtro de región aparece en la parte superior con la opción predeterminada seleccionada como TODAS.

  • El valor resumido es la suma del monto del pedido.

    • Suma de la cantidad de la orden El vendedor aparece en la columna Total general.

    • Suma del importe del pedido mensual aparece en la fila Gran total.

  • Haga clic en la flecha en el filtro de región.

Aparece una lista desplegable con los valores del campo Región.

  • Marque la casilla Seleccionar varios elementos. Aparecerán casillas de verificación para todos los valores. De forma predeterminada, todas las casillas están marcadas.

  • Desmarque la casilla (Todo). Todas las casillas quedarán sin marcar.

  • Marque las casillas: Sur y Oeste.

  • Haga clic en el botón Aceptar. Solo se resumirán los datos pertenecientes a las regiones Sur y Oeste.

Como puede observar, en la celda al lado del Filtro de región, se muestra (Elementos múltiples), lo que indica que ha seleccionado más de un valor. Pero cuántos valores y / o qué valores se desconocen del informe que se muestra. En tal caso, usar Slicers es una mejor opción para filtrar.

Usar segmentaciones en tabla dinámica

Filtrar con Slicers tiene muchas ventajas:

  • Puede tener varios filtros seleccionando los campos para las rebanadoras.

  • Puede visualizar los campos sobre los que se aplica el Filtro (un Slicer por campo).

  • Un Slicer tendrá botones que indican los valores del campo que representa. Puede hacer clic en los botones de la cortadora para seleccionar / deseleccionar los valores en el campo.

  • Puede visualizar qué valores de un campo se utilizan en el Filtro (los botones seleccionados se resaltan en el Rebanador).

  • Puede utilizar un Slicer común para varias tablas dinámicas y / o gráficos dinámicos.

  • Puede ocultar / mostrar una rebanadora.

Para comprender el uso de Slicers, considere la siguiente tabla dinámica.

Suponga que desea filtrar esta tabla dinámica según los campos: Región y Mes.

  • Haga clic en la pestaña ANALIZAR debajo de HERRAMIENTAS PIVOTABLES en la cinta.
  • Haga clic en Insertar rebanador en el grupo Filtro.

Aparece el cuadro de diálogo Insertar rebanadores. Contiene todos los campos de sus datos.

  • Marque las casillas Región y Mes.

  • Haga clic en el botón Aceptar. Las rebanadoras para cada uno de los campos seleccionados aparecen con todos los valores seleccionados por defecto. Las herramientas de la cortadora aparecen en la cinta para trabajar en la configuración, apariencia y sensación de la cortadora.

Como puede observar, cada Slicer tiene todos los valores del campo que representa y los valores se muestran como botones. De forma predeterminada, todos los valores de un campo están seleccionados y, por lo tanto, todos los botones están resaltados.

Suponga que desea mostrar la tabla dinámica solo para las regiones Sur y Oeste y para los meses de febrero y marzo.

  • Haga clic en Sur en el Rebanador de regiones. Solo el sur se resaltará en la región de Slicer.

  • Mantenga presionada la tecla Ctrl y haga clic en West en el Region Slicer.

  • Haga clic en febrero en el cortador de meses.

  • Mantenga presionada la tecla Ctrl y haga clic en Marzo en el Rebanador de meses. Los valores seleccionados en las Slicers están resaltados. La tabla dinámica se resumirá para los valores seleccionados.

Para agregar / eliminar valores de un campo del filtro, mantenga presionada la tecla Ctrl y haga clic en esos botones en el Slicer respectivo.