15.1. Crear una tabla dinámica
Con un informe de tabla dinámica puede resumir, analizar, explorar y presentar un resumen de los datos de la hoja de cálculo o un origen de datos externos (datos externos: datos que se almacenan fuera de Excel; por ejemplo, bases de datos creadas en Access, dBASE, SQL Server o en un servidor Web.). Un informe de tabla dinámica es especialmente útil cuando tiene una larga lista de cifras para sumar y los datos agregados o subtotales podrían servir para mirar los datos desde perspectivas diferentes y comparar las cifras de datos similares.
Excel generará automáticamente subtotales y totales generales en el informe de tabla dinámica. Si la lista de origen contiene subtotales y totales generales generados automáticamente, habrá que quitar todos los totales antes de crear el informe de tabla dinámica o gráfico dinámico.
Para facilitar la compresión de la creación y posibilidades de una tabla dinámica vas a crear una tabla dinámica a partir de los datos de los siguientes datos:
En un libro con la denominación Dinámica Trimestres 2011. crea las siguientes hojas:
- En la hoja 1 con el nombre PRODUCTOS crea la siguiente tabla:
CÓDIGO |
PRODUCTO |
PRECIO |
L1 |
Lavavajillas “Don Pulcro” |
0,75 |
S1 |
Suavizante “SedaFresh” |
1,29 |
D1 |
Detergente MaxBlanco |
5,34 |
D2 |
Detergente “VivaColor” |
5,49 |
LI1 |
Limpiador “ComoSol” |
1,65 |
- En la hoja 2 con el nombre AGENTES
Cód. AGENTE |
NOMBRE Y APELLIDOS |
ZONA |
JEF |
Judit Egea Fernández |
Levante |
CCF |
Carlos Chiclano Fernández |
Sur |
DEF |
David Egea Fernández |
Centro |
- En la hoja 3 con el nombre VENTAS 2011 que servirá para crear la tabla dinámica. Es recomendable como repaso y aplicación de las posibilidades de Excel que utilices las funciones y reglas de validación oportunas:
- Trimestre: utiliza una función anidada que dependiendo del mes indicado en la columna Mes devuelva: Primer trimestre, Segundo trimestre, Tercer trimestre y Cuarto trimestre.
- Cod. PRODUCTO: valida con una lista, para evitar introducir datos incorrectos.
- PRODUCTO: se ha introducido la siguiente función: =SI(F8="";"";BUSCARV(F8;Productos!$A$12:$C$16;2;FALSO)) para que busque la denominación del producto en la hoja PRODUCTOS y cuando se arrastre la fórmula no introduzca #N/A e introduzca “ “.
- PRECIO: se deben utilizar funciones similares a las de PRODUCTO.
- TOTAL: será PRECIO por CANTIDAD, como el valor de estas celdas depende de celdas con función BUSCARV también se debe procurar que no se introduzca #N/A.
Trimestre | Mes | Cód. AGENTE | CANTIDAD | Cód. PRODUCTO | PRODUCTO | PRECIO | TOTAL |
Primer trimestre | Marzo | DEF | 20 | D2 | Detergente "VivaColor" | 5,49 | 109,80 € |
Tercer trimestre | Julio | CCF | 15 | D2 | Detergente "VivaColor" | 5,49 | 82,35 € |
Primer trimestre | Enero | DEF | 20 | S1 | Suavizante "SedaFresh" | 1,29 | 25,80 € |
Primer trimestre | Enero | CCF | 15 | S1 | Suavizante "SedaFresh" | 1,29 | 19,35 € |
Primer trimestre | Enero | JEF | 50 | D1 | Detergente MaxBlanco | 5,34 | 267,00 € |
Primer trimestre | Enero | JEF | 35 | D2 | Detergente "VivaColor" | 5,49 | 192,15 € |
Primer trimestre | Febrero | CCF | 40 | LI1 | Limpiador "ComoSol" | 1,65 | 66,00 € |
Primer trimestre | Febrero | DEF | 20 | D1 | Detergente MaxBlanco | 5,34 | 106,80 € |
Primer trimestre | Febrero | DEF | 10 | D1 | Detergente MaxBlanco | 5,34 | 53,40 € |
Primer trimestre | Febrero | CCF | 31 | S1 | Suavizante "SedaFresh" | 1,29 | 39,99 € |
Primer trimestre | Marzo | JEF | 25 | L1 | Lavavajillas "Don Pulcro" | 0,75 | 18,75 € |
Primer trimestre | Marzo | JEF | 45 | S1 | Suavizante "SedaFresh" | 1,29 | 58,05 € |
Primer trimestre | Marzo | DEF | 120 | L1 | Lavavajillas "Don Pulcro" | 0,75 | 90,00 € |
Segundo trimestre | Abril | CCF | 62 | D1 | Detergente MaxBlanco | 5,34 | 331,08 € |
Segundo trimestre | Abril | DEF | 25 | D1 | Detergente MaxBlanco | 5,34 | 133,50 € |
Segundo trimestre | Abril | CCF | 78 | D2 | Detergente "VivaColor" | 5,49 | 428,22 € |
Segundo trimestre | Abril | DEF | 64 | S1 | Suavizante "SedaFresh" | 1,29 | 82,56 € |
Segundo trimestre | Mayo | JEF | 45 | D1 | Detergente MaxBlanco | 5,34 | 240,30 € |
Segundo trimestre | Mayo | CCF | 35 | L1 | Lavavajillas "Don Pulcro" | 0,75 | 26,25 € |
Segundo trimestre | Mayo | JEF | 51 | LI1 | Limpiador "ComoSol" | 1,65 | 84,15 € |
Segundo trimestre | Junio | DEF | 36 | D1 | Detergente MaxBlanco | 5,34 | 192,24 € |
Segundo trimestre | Junio | DEF | 61 | L1 | Lavavajillas "Don Pulcro" | 0,75 | 45,75 € |
Segundo trimestre | Junio | JEF | 15 | S1 | Suavizante "SedaFresh" | 1,29 | 19,35 € |
Segundo trimestre | Junio | CCF | 63 | D1 | Detergente MaxBlanco | 5,34 | 336,42 € |
Tercer trimestre | Julio | CCF | 25 | D1 | Detergente MaxBlanco | 5,34 | 133,50 € |
Tercer trimestre | Julio | JEF | 34 | LI1 | Limpiador "ComoSol" | 1,65 | 56,10 € |
Tercer trimestre | Julio | DEF | 18 | D2 | Detergente "VivaColor" | 5,49 | 98,82 € |
Tercer trimestre | Julio | DEF | 35 | LI1 | Limpiador "ComoSol" | 1,65 | 57,75 € |
Tercer trimestre | Julio | JEF | 64 | S1 | Suavizante "SedaFresh" | 1,29 | 82,56 € |
Tercer trimestre | Septiembre | CCF | 45 | D1 | Detergente MaxBlanco | 5,34 | 240,30 € |
Tercer trimestre | Septiembre | JEF | 60 | LI1 | Limpiador "ComoSol" | 1,65 | 99,00 € |
Tercer trimestre | Septiembre | CCF | 65 | LI1 | Limpiador "ComoSol" | 1,65 | 107,25 € |
Tercer trimestre | Septiembre | JEF | 35 | D2 | Detergente "VivaColor" | 5,49 | 192,15 € |
Cuarto trimestre | Octubre | CCF | 150 | S1 | Suavizante "SedaFresh" | 1,29 | 193,50 € |
Cuarto trimestre | Octubre | CCF | 30 | LI1 | Limpiador "ComoSol" | 1,65 | 49,50 € |
Cuarto trimestre | Octubre | JEF | 15 | S1 | Suavizante "SedaFresh" | 1,29 | 19,35 € |
Cuarto trimestre | Octubre | DEF | 32 | D1 | Detergente MaxBlanco | 5,34 | 170,88 € |
Cuarto trimestre | Octubre | CCF | 45 | D2 | Detergente "VivaColor" | 5,49 | 247,05 € |
Cuarto trimestre | Octubre | DEF | 75 | L1 | Lavavajillas "Don Pulcro" | 0,75 | 56,25 € |
Cuarto trimestre | Noviembre | JEF | 39 | S1 | Suavizante "SedaFresh" | 1,29 | 50,31 € |
Cuarto trimestre | Noviembre | CCF | 81 | D1 | Detergente MaxBlanco | 5,34 | 432,54 € |
Cuarto trimestre | Noviembre | DEF | 64 | S1 | Suavizante "SedaFresh" | 1,29 | 82,56 € |
Cuarto trimestre | Noviembre | JEF | 68 | S1 | Suavizante "SedaFresh" | 1,29 | 87,72 € |
Cuarto trimestre | Diciembre | CCF | 64 | D2 | Detergente "VivaColor" | 5,49 | 351,36 € |
Cuarto trimestre | Diciembre | JEF | 45 | S1 | Suavizante "SedaFresh" | 1,29 | 58,05 € |
Cuarto trimestre | Diciembre | JEF | 15 | LI1 | Limpiador "ComoSol" | 1,65 | 24,75 € |
Excel facilita la creación de una tabla dinámica a través de un Asistente. Para activarlo habrá que ejecutar el comando Informe de tablas y gráficos dinámicos del menú Datos.
15.1.1. Pasos para crear un informe de tabla dinámica
1 ► Define el origen de datos del informe de tabla dinámica. Para usar datos de la hoja de cálculo como origen de datos, haz clic en una celda del rango de celdas que contiene los datos.
Es muy importante que el rango tenga encabezados de columna, asimismo, que no haya filas en blanco en el rango o tabla. |
2 ► En el grupo Tablas de la pestaña Insertar, haz clic en Tabla dinámica. Excel determina automáticamente el rango del informe de tabla dinámica, pero puedes reemplazarlo por otro rango diferente o por un nombre que hayas definido para el rango. En el ejemplo seleccionas el rango correspondiente a todos los datos de la hoja Ventas 2011.
3 ► Realiza una de las siguientes acciones:
3►a◄ Para poner el informe de tabla dinámica en una hoja de cálculo nueva empezando por la celda A1, haz clic en Nueva hoja de cálculo.
3►b◄ Para colocar el informe de tabla dinámica en una ubicación específica en una hoja de cálculo existente, selecciona Hoja de cálculo existente y, a continuación, en el cuadro Ubicación, escribe la primera celda del rango de celdas en la que deseas ubicar el informe de tabla dinámica.
4 ► Acepta.
5 ► Excel agrega un informe de tabla dinámica vacío en la ubicación especificada y muestra la Lista de campos de tabla dinámica para agregar campos, crear un diseño o personalizar el informe de tabla dinámica Para agregar campos al informe de tabla dinámica, en Lista de campos de tabla dinámica, realizar alguno de los siguientes procedimientos:
5►a◄ Para colocar un campo en el área predeterminada de la sección de diseño, activa la casilla junto al nombre del campo en la sección del campo. De forma predeterminada, los campos no numéricos se agregan al área Etiquetas de fila, los campos numéricos se agregan al área Valores y las jerarquías de fecha y hora se agregan al área Etiquetas de columna.
5►b◄ Para colocar un campo en un área específica de la sección de diseño, haz clic con el botón secundario en el nombre de campo en la sección del campo y, a continuación, selecciona Agregar a filtro de informe, Agregar a etiquetas de columna, Agregar a etiquetas de fila o Agregar a valores.
5►c◄ Para arrastra un campo al área que desea, haz clic y mantener presionado en el nombre del campo en la sección de campo y, a continuación, arrástralo hasta un área en la sección de diseño.
Para reorganizar los campos más tarde según sea necesario; para ello, haz clic con el botón secundario en los campos de la sección de diseño y selecciona el área que se desee, o bien arrastra los campos entre las áreas de la sección de diseño.
En el ejemplo Ventas 2011, los campos se organizan de la siguiente forma:
- En filtro de informe coloca el campo Trimestre. Puedes utilizar para filtrar el informe por trimestre. Con el campo Trimestre, puedes mostrar datos resumidos sólo del 1er Trimestre, sólo del 2º Trimestre, 3er Trimestre o del 4º Trimestre.
- En el campo Etiquetas de fila se sitúan los MES y PRODUCTO. Los campos de fila interiores y exteriores tienen atributos diferentes. Los elementos del campo de fila exterior más lejano (MES) se muestran sólo una vez, pero los elementos del resto de los campos (PRODUCTO) se repiten tantas veces como sea necesario.
- En el campo Etiquetas de columna se ocupa con COD. AGENTE.
- En el campo Valores contiene los datos que se vas a resumir. En este cao, Suma de Total es un campo de datos que resume las entradas del campo en los datos de origen.
Resultado de informe tabla dinámica
1 ► Haz clic en los botones Expandir o Contraer que están junto al elemento que desea expandir o contraer. Si no visualizas los botones Expandir o Contraer, en la pestaña Opciones, en el grupo Mostrar, haz clic en los botones +/- para mostrar u ocultar los botones Expandir y Contraer.
2 ► Haz doble clic en el elemento que deseas expandir o contraer.
3 ► Haz clic con el botón secundario del mouse en el elemento, clic en Expandir o contraer y, a continuación, realiza una de las siguientes acciones:
3►a◄ Para ver los detalles del elemento actual, haz clic en Expandir.
3►b◄ Para ocultar los detalles del elemento actual, haz clic en Contraer.
3►c◄ Para ocultar los detalles de todos los elementos de un campo, haz clic en Contraer todo el campo
3►d◄ Para ver los detalles de todos los elementos de un campo, haz clic en Expandir todo el campo.
3►e◄ Para ver un nivel de detalle posterior al siguiente nivel, haz clic en Expandir hasta "<nombre del campo>".
3►f◄ Para ocultar un nivel de detalle posterior al siguiente nivel, haz clic en Ocultar hasta "<nombre del campo>"
Para quitar un campo, realice una de las siguientes acciones en la lista de campos de tabla dinámica:
1 ► En la casilla Seleccionar campos para agregar al informe, desactiva la casilla de verificación del campo que deseas quitar. Al desactivar una casilla de verificación se quitan todas las apariciones del campo del informe.
2 ► En un área de diseño, haz clic en el campo que deseas quitar y luego en Quitar campo.
3 ► En un área de diseño, haz clic y mantener presionado el botón del mouse sobre el campo que desee quitar y después arrástrelo fuera de la lista de campos de tabla dinámica