15.1. Crear una tabla dinámica

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 €

Descarga ejercicio resuelto 

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.

Cuadro de diálogo Crear 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.

 Lista de campos de tabla dinámica

 

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.

 Arrastrar 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. 

Filtrar y organizar campos

Resultado de informe tabla dinámica

Resultado de informe tabla dinámica

15.1.2. Mostrar u ocultar los detalles de los datos en un informe de tabla dinámica

15.1.2.1. Expandir o contraer niveles en una 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>"

15.1.3. Quitar campos de la tabla dinámica o del gráfico dinámico

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


Licencia Creative Commons