Ediciones ENI Editorial | líder en informática
Las tablas dinámicas
Introducción
1. Preámbulo
En los capítulos anteriores, ha podido aplicar diferentes técnicas para obtener estadísticas consolidadas a partir de bases de datos de diferentes orígenes. Estas técnicas (subtotales, tablas de datos, cálculos multicriterios, etc.) requieren un dominio de las funciones de cálculo de Excel, pero no tienen límites en el diseño y configuración de las tablas. Otra función de Excel que debe conocer es la técnica de creación de tablas dinámicas.
En este libro abordaremos la creación de tablas dinámicas simples, ya que en la misma colección existe un libro entero dedicado a las tablas cruzadas dinámicas.
Como para los capítulos anteriores, los ejemplos del libro se pueden descargar desde la página Información. Esto le evitará tener que escribir todos los datos y le permitirá controlar sus resultados. Las bases de datos propuestas son variadas y le permitirán aplicar las técnicas de creación de tablas dinámicas en muchos ámbitos.
Si ya utilizaba las tablas dinámicas en una versión anterior de Excel, Excel 2016 le permitirá importar y relacionar importantes cantidades de datos de varias fuentes mediante un modelo de datos integrado. También podrá generar modelos de datos que le servirán de base para las tablas...
Las tablas dinámicas
1. Definiciones
|
Base de datos |
Conjunto de datos estructurados guardados en un disco. Este conjunto de datos se puede consultar y modificar. |
|
Registro |
Cada registro corresponde a una información relativa a un elemento almacenado en la base de datos. |
|
Campo |
Un campo representa una característica precisa de registro. Para distinguir un registro son necesarios varios campos; por ejemplo, para un trabajador: N° identificador, Nombre, Apellido, Sexo, Función, Salario... |
|
Filtro de relación |
Permite filtrar los datos que se van a mostrar en función del elemento seleccionado en el filtro de la relación. Un filtro de relación le permite presentar rápidamente un subconjunto de datos en una relación de tabla cruzada. Cuando sus datos de origen consten de un gran número de datos, el filtro permite trabajar en una parte de los datos fuente de la relación. También puede ver las síntesis para una familia de productos, un periodo de tiempo o una categoría de personal específicos. |
|
Segmentos |
Autoriza el uso de botones para segmentar y filtrar rápidamente los datos. |
|
Modelo de datos |
Colección de tablas y sus relaciones entre sí. |
|
Etiquetas de filas |
Permiten ver los campos en forma de filas al lado izquierdo de la relación. Una fila de posición inferior se anida en la fila que se muestra inmediatamente por encima de esta. |
|
Etiquetas de columnas |
Permiten ver los campos en forma de columnas en la parte superior de la relación. Una columna de posición inferior se anida en la columna que se muestra inmediatamente por encima de esta. |
|
Campos de valores |
Sirven para ver datos de síntesis numéricas. Puede usar las distintas funciones de síntesis (suma, promedio, min, max, contar, etc.). |
2. Los límites de las tablas dinámicas
|
Característica |
Límite máximo |
|
Número máximo de tabla dinámica por hoja |
Limitado por la cantidad de memoria disponible |
|
Número máximo de elementos únicos por campo |
1.048.576 |
|
Número máximo de campos de fila o de columna |
Limitado por la cantidad de memoria disponible |
|
Número máximo de filtros de relación con respecto a la tabla dinámica |
256 (límite posible debido a la cantidad de memoria disponible) |
|
Número máximo de campos de valor... |
Nuestra primera tabla dinámica
1. Los datos de origen
Para crear nuestra primera tabla, abra el archivo TD.xlsx.
Los datos de la hoja ListaVentas son los siguientes. Las ventas de los dos años se han memorizado (2015 y 2016).
Este archivo aúna las diferentes ventas de suministros de una empresa.
Las ventas se han hecho en cinco sectores.
-
ADMINISTRACIÓN
-
COMERCIO
-
INDUSTRIA
-
SANIDAD
-
SERVICIOS
Tres comerciales, cada uno encargado de varios departamentos, se encargan de las ventas.
-
MENARGUES, códigos provincia 03, 30 y 02
-
LEIVA, códigos provincia, 46, 16 y 44
-
REINA, códigos provincia 12 y 43
2. Primera síntesis
La tabla de datos consta de cinco columnas, así que nuestra primera tabla dinámica será fácil de crear. En primer lugar, vamos a calcular el importe total de las ventas por comercial y por sector.
Coloque el cursor en una celda del origen, A1 por ejemplo.
Pestaña Insertar - grupo Tablas - haga clic en Tabla dinámica:
Aparecerá la ventana Crear tabla dinámica:
El rango de datos que vamos a analizar se selecciona automáticamente y aparece rodeado de una línea de puntos móviles. La ubicación Nueva hoja de cálculo se selecciona por defecto.
Haga clic en Aceptar.
Excel crea una nueva hoja de cálculo en la que aparece la zona de relación en la parte izquierda.
En la parte derecha de la ventana aparece...
Dar formato a la tabla dinámica
1. Modificar la disposición de la tabla dinámica
En general, es preferible presentar un informe de tabla dinámica con los datos más numerosos en los encabezados de las filas. Así, la tabla podrá imprimirse directamente en orientación vertical.
Para ilustrar esta técnica, en la ventana de los campos de la tabla dinámica desmarque los tres campos Fecha, Trimestres y Años, y luego marque SECTOR.
En este caso tenemos más sectores que comerciales, por eso hemos colocado los sectores en los encabezados de las filas y los comerciales en los encabezados de las columnas.
Vamos a ver cuál habría sido la presentación de la tabla si los campos hubiesen estado a la inversa.
En el cuadro de diálogo, deslice el campo SECTOR a la zona Columnas y COMERCIAL a la zona Filas.
La tabla dinámica se ha reorganizado:
Uno de los puntos fuertes de Excel es que nos permite hacer pruebas de disposición. Si durante la creación de la tabla dinámica se da cuenta de que no es funcional, en pocos segundos podrá organizarla de otra forma.
Si ha cerrado el cuadro de diálogo con la cruz situada en la parte superior derecha, haga clic con el botón derecho en la tabla dinámica y seleccione la opción Mostrar lista de campos para verla.
2. Modificar el formato de los números
En función del tipo de valores que analice, puede ser útil que convierta los números en formato de miles para definirlos con un punto cada...
Estadísticas en un archivo de empleados
1. Funciones estándar
Abra el archivo Empleados.xlsx.
La hoja Lista de este libro contiene datos sobre los 116 empleados de una empresa. Se enumeran los empleados con un contrato de duración indeterminada (CDI) y los empleados con contrato de duración determinada (CDD).
a. Plantilla por departamento y por sexo
Inserte la tabla dinámica, como el número de departamentos es evidentemente superior al número de sexos, es preferible colocar los departamentos en el encabezado de las filas y los sexos en el encabezado de las columnas.
El cuadro de diálogo Campos de tabla dinámica aparece a continuación.
Al insertar el campo Apellido en el área ∑ Valores, Excel utiliza automáticamente la función Contar ya que este campo solo consta de valores de tipo texto.
Habríamos podido deslizar en esta área uno de los campos, Nombre, Sexo, TipoContrato... esto no habría modificado el resultado obtenido.
Al insertar un campo que contiene los valores numéricos (Salario o Antigüedad), nos habríamos visto obligados a modificar en este campo la Configuración de campo de valor, para utilizar la función Contar.
La tabla insertada se representa a continuación:
El total general se puede comprobar fácilmente. Basta con ir a la hoja Lista, y como la última fila era la 117, si restamos la fila...
Las tablas dinámicas recomendadas
1. Objetivo
Excel 2016 le propone varios métodos para sintetizar sus datos. Le mostrará una breve vista preliminar de las disposiciones de campos para que pueda escoger la que le parece más adecuada en función de lo que quiera analizar.
Puede utilizar en cualquier momento este método de diseño simple y rápido para hacer pruebas y así explorar las diferentes posibilidades de presentación propuestas.
Las sugerencias de tablas dinámicas permitirán a los principiantes crear una tabla dinámica que nunca habrían imaginado.
2. Utilización
Para comprobar este método, vamos a utilizar los datos de nuestra hoja Lista.
Haga clic en la lista de datos.
Pestaña Insertar - grupo Tablas - haga clic en el segundo botón Tablas dinámicas recomendadas:
Excel muestra una ventana de sugerencias.
Si hace clic sucesivamente en las sugerencias en la parte izquierda de la ventana, podrá ver inmediatamente la tabla dinámica propuesta. No dude en ampliar el ancho de la ventana si fuera necesario.
Al hacer clic en Aceptar, la tabla seleccionada se inserta en una nueva hoja. Ya solo le falta dar formato a los números y modificar los títulos y etiquetas.
