Informática Aplicada TECSUP

Tablas Dinámicas

Informática Aplicada

Índice
• Introducción
• Objetivos • Ficha Analizar
• Concepto de Tabla Dinámica – Agrupar fechas
• Creación de una Tabla Dinámica – Insertar segmentación de
– Preparar los datos datos
– Procedimiento de creación – Añadir campo calculado
• Edición de una Tabla Dinámica – Actualizar Datos
– Añadir, mover, eliminar – Gráficos dinámicos
campos • Formato de Tablas Dinámicas
– Funciones resumen – Estilos de Tablas dinámicas
– Cálculos adicionales – Subtotales
– Filtros – Diseño de informe
– Mostrar detalles • Bibliografía

1
Informática Aplicada TECSUP

Introducción

• Cuando se trabaja con grandes cantidades de datos en

Excel, sean estos de tipo estadístico, financiero, comercial,
de servicios, etc. Las Tablas Dinámicas pueden ayudar a
revisar, analizar, monitorear y generar informes resumidos de
manera más rápida y confiable que utilizando fórmulas y
funciones tradicionales.

Objetivos

• Crear tablas dinámicas.

• Personalizar el diseño de las tablas dinámicas.
• Dar formato a las tablas dinámicas.
• Generar gráficos dinámicos.

2
Informática Aplicada TECSUP

Tablas dinámicas

• Una Tabla Dinámica es una herramienta de Excel que permite

analizar rápidamente grandes cantidades de datos.

• Cientos o miles de filas y columnas pueden resumirse en un solo

cuadro o reporte utilizando Tablas Dinámicas.

Tablas dinámicas: Utilidad

Nos permiten
analizar la
información desde
distintos ángulos.

Por ejemplo ver las ventas totales por

región o ver las ventas por producto 6

3
Informática Aplicada TECSUP

Datos de origen
En el siguiente ejemplo se muestra parte del registro de
ventas de una Librería. Utilizando tablas dinámicas podremos
ver que Región presenta más ventas; ver que producto tiene
mayor venta; o ver la tendencia por años de las ventas.

Tablas dinámicas: Casos

Vamos a crear una tabla dinámica para cada uno de estos

casos:
• Total de ventas por región
• Cantidad por categoría y producto
• % de ventas que representa cada ciudad filtrado por región
• Tendencia de ventas por años

• Abra el archivo Unidad09.xlsx hoja Libreria

4
Informática Aplicada TECSUP

Creación de una tabla

dinámica: Caso1
Caso1: Total de ventas por
región
Paso1
• Vayamos a la ficha Insertar
• Seleccionemos la opción
Tabla dinámica
• Nueva hoja de cálculo y
Aceptar

Creación de una tabla

dinámica: Caso1
Paso 2
•Seleccionar solo los valores
que se desean mostrar. El Panel derecho
superior contiene los
campos de la tabla.

Las áreas de la tabla dinámica que

podremos operar según los
requerimientos.
1. Filtros: Dato de mayor nivel que se
quiere analizar.
2. Columna: Muestra los datos en
columnas.
3. Fila: Muestra los datos en Filas
4. Valores: los datos con que se
harán los cálculos: contar, sumar,
promediar, etc.

10

5
Informática Aplicada TECSUP

Edición: Añadir, eliminar,

mover campos
• Para añadir o eliminar
campos de la Tabla
Dinámica bastará con
marcarlos o desmarcarlos
de la Lista de campos.
• Para mover campos de
un área a otra, bastará
con arrastrarlos en el
panel inferior de la Lista
de campos.

11

Creación de una tabla

dinámica: Caso1
Para este caso la
región se mostrará en
el área Filas y el Total
Final en Valores.
La tabla dinámica
calcula
automáticamente la
suma de todos los
valores del Total Final
de cada Región.
Cambiemos el
nombre de la hoja a
TablaD1.
12

6
Informática Aplicada TECSUP

Edición de la Tabla Dinámica:

Caso2
Caso2: Cantidad por
categoría y producto

Realizaremos una copia

de la hoja TablaD1 y la
modificaremos para que
muestre lo solicitado, le
colocaremos el nombre
TablaD2.

13

¿Y si no quisiéramos ver la suma,

si no el promedio, el mayor o el
menor valor?

14

7
Informática Aplicada TECSUP

Edición: Cambio de función

resumen

• Por defecto, las cantidades

numéricas son sumadas. Pero
esta función puede modificarse.
• Haga clic derecho sobre una celda
que contenga valores y elegir una
de estas dos opciones:
– La opción Resumir datos por
permite elegir otras funciones.
– La opción Configuración de
campo de valor muestra el
diálogo con las funciones
disponibles: Cuenta,
Promedio, Máx, Mín,
Producto, etc.

15

Funciones resumen
disponibles
Función: Descripción
Suma Suma todos los datos
Cuenta Cuenta números, textos, y errores
Promedio Suma de los datos dividida por la cantidad de valores
Máx Muestra el valor más alto
Mín Muestra el valor más bajo
Producto Multiplica todos los datos

Contar números Cuenta solo números

Desvest Calcula la desviación estándar de una muestra
Desvestp Calcula la desviación estándar de la población
Var Calcula la varianza de un muestra
Varp Calcula la varianza de la población

16

8
Informática Aplicada TECSUP

Caso1 Resumir valores por:

En la hoja TablaD1 mostraremos el promedio de las ventas por
región y la mayor venta.
Hagamos clic derecho en la celda B3 Suma de Total final y
seleccionemos Resumir valores por: Promedio

17

Caso1: Resumir por más de

una función
• Además del Promedio mostraremos el Mayor Total Final.
• Para ello duplicaremos el campo Total final en el área de Valores.
• Luego modificaremos la función de resumen, haciendo clic derecho y
seleccionaremos Configuración de Campo de Valor

18

9
Informática Aplicada TECSUP

Caso1: Resumir por más de

una función
Seleccionaremos la función Max

19

Edición: Cálculos adicionales

• Existen otros cálculos que permiten comparar
valores, con otros de la misma Tabla dinámica.
• El diálogo Configuración de campo valor, mostrado
antes, tiene la ficha Mostrar valores como, con las
opciones:
– % del total general
– Diferencia de
– % de
– % de la diferencia de
– Total en
– % del total de filas
– % del total de columnas
– Índice

20

10
Informática Aplicada TECSUP

Cálculos adicionales Caso 3

Caso3: % de ventas que

representa cada ciudad
filtrado por región.
• Insertaremos una tabla
dinámica que muestre
Ciudades y el Total Final
con un filtro por región.
• En Filtro
seleccionaremos Lima

21

Cálculos adicionales Caso 3

• Ingrese a Configuración de Campo de valor.

• Seleccione Mostrar valores como.
• % del total general. Usted podrá modificar
el nombre de la
columna

22

11
Informática Aplicada TECSUP

Filtrar datos
•Además de utilizar el Filtro principal de la tabla dinámica
podemos agregar filtros a las filas o a los valores.
•Para ello deberemos de desplegar los filtros de Etiqueta de
filas.
Ejemplo: Inserte una tabla dinámica nueva que muestre, las
regiones, las ciudades, la suma y la cuenta del total final.

23

Filtrar datos: Ejemplo

•Insertaremos un filtro para que solo se muestren las
ciudades que empiezan con la letra C.

24

12
Informática Aplicada TECSUP

Filtrar datos: Ejemplo

•Insertaremos un filtros para que se muestren la Cuenta de

los totales finales mayores a 15

25

Filtrar datos: Ejemplo

Ciudades que Cuenta de

empiezan con total final
C mayor a 15

26

13
Informática Aplicada TECSUP

Mostrar detalles

• Una vez creada la Tabla Dinámica, puede ser

necesario mostrar los datos que originan cada valor
mostrado.
• Para ello haga clic derecho sobre al valor que desea
detallar:
– Use la opción Mostrar detalles
– Los datos originales se mostrarán en una nueva
Hoja

27

Tabla Dinámica Ficha

Analizar
•Al insertar una tabla dinámica se mostrarán dos fichas
nuevas Analizar y Diseño.
•En la ficha Analizar encontraremos herramientas que nos
permitirán agrupar, insertar segmentación de datos y escalas
de tiempo.
•Además podremos actualizar o modificar los datos de
origen, crear un campo calculado e insertar un gráfico
dinámico.

28

14
Informática Aplicada TECSUP

Agrupar Campos: Caso 4

Caso4: Tendencia de ventas

por años
Para este caso necesitamos ver
las ventas por cada año, y no
tenemos el campo Años, pero si
tenemos el campo Fecha.
Por ello insertaremos las fechas
en la tabla dinámica y luego las
agruparemos por año.
• Inserte una nueva tabla
dinámica y solo seleccione el
campo Fecha. La hoja tendrá
el nombre TablaD4. 29

Agrupar Campos: Caso 4

•Haga clic en la primera celda con fechas e ingrese a la

ficha Analizar.
•Seleccione Agrupar, opción Agrupar campos.
•Seleccione Meses y Años.

30

15
Informática Aplicada TECSUP

Agrupar Campos: Caso 4

•Elimine el campo Fecha y agregue el campo Total Final en el

área Valores.

31

Insertar segmentación de
datos
•Otra forma de filtrar los datos es utilizando Segmentación de Datos.
•Por ejemplo en la hoja TablaD1, agreguemos una segmentación por
Categoría.
•Haga clic en la Ficha Analizar, Insertar Segmentación de Datos.

32

16
Informática Aplicada TECSUP

Campo Calculado

•Esta herramienta nos permite agregar un nuevo campo, las

cuales son columnas nuevas que obtienen su valor a partir
de una operación realizada entre las columnas existentes.
Ejemplo: Agregue una tabla dinámica que muestre las
regiones, la suma de las cantidades y el total final.

33

Campo Calculado

• En la ficha Analizar, grupo Cálculos, despliegue la opción

Campos, elementos y conjuntos. Seleccione la opción Campo
Calculado.
• Crearemos un Descuento2, este será del 5% del Total Final, si la
suma de la cantidad es mayor a 1000, en caso contrario será 0.

34

17
Informática Aplicada TECSUP

Campo Calculado

• Se agregará uno nuevo campo.

35

Edición: Actualizar los datos

• Es posible que, luego de

construida la Tabla dinámica, los
datos originales sufran
modificaciones.
• Para que estas modificaciones
se reflejen en la Tabla Dinámica
debe utilizar:
– La ficha Opciones de las
Herramientas de tabla
dinámica
– Botón Actualizar del grupo
Datos

36

18
Informática Aplicada TECSUP

Gráfico dinámico
• Un gráfico dinámico
se basa en los datos
de una Tabla
dinámica.
• Para crear un gráfico
dinámico puede
utilizar el grupo
Herramientas de la
ficha Opciones de la
Herramientas de
tabla dinámica.
• Las facilidades para
elegir el tipo de gráfico
y darle formato son
similares a las de los
gráficos
convencionales.

37

Gráfico dinámico

• Excel mostrará dentro del Gráfico Dinámico opciones para poder aplicar
los filtros que se requieran.
• Tenga presente que el Gráfico dinámico y la Tabla dinámica están vinculados:
– Los cambios en la estructura o en los filtros de uno se reflejarán en el otro. Sin embargo, si
se elimina uno de ellos, el otro permanecerá.
– Se pueden crear varios gráficos dinámicos de la misma Tabla dinámica, y ubicarlos y
formatearlos independientemente.

38

19
Informática Aplicada TECSUP

Formato de Tabla Dinámica

• Adicionalmente, la ficha Diseño presenta los grupos

Estilos y Opciones de estilos para mejorar la presentación
de la Tabla Dinámica.

39

Diseño: Ubicación de
subtotales
• Los subtotales pueden ocultarse, o mostrarse por encima o por debajo de las
filas que representan.
• Su ubicación se puede establecer mediante el botón Subtotales del grupo
Diseño de la ficha Diseño de las Herramientas de Tabla dinámica.

40

20
Informática Aplicada TECSUP

Dos subtotales

• Para agregar un subtotal adicional haremos clic derecho en el subtotal

y seleccionaremos Configuración de campo.
• Seleccionaremos Personalizado y más de una función.

41

Diseño de informe
Podemos
modificar el diseño
de una tabla
dinámica con la
herramienta
Diseño de Informe.

42

21
Informática Aplicada TECSUP

Glosario

• Registro: conjunto de atributos o características (campos)

de un ente (persona, objeto, concepto). Ejemplo: el ente
CLIENTE puede tener los atributos Código, Apellido,
Dirección, Fecha de Nacimiento, etc.
• Campo: un atributo o característica con un valor específico.
Ejemplo: Precio, Fecha de venta.
• Filtro: opción para ocultar / mostrar las filas que cumplan
una especificación determinada.

43

Bibliografía
• Fernando Rosino Alonso. Excel 2013. Grupo Anaya
Comercial. 2013.
• Walkenbach, John. La Biblia de Excel 2013. Grupo Anaya
Comercial. 2013.
• Harvey, Greg. Microsoft Office Excel 2010 for dummies.
Wiley Publishing Inc. 2010.

• Se ha utilizado como consulta la página de Microsoft

Latinoamérica:
– http://office.microsoft.com/es-es/excel-help/informacion-general-
RZ101773335.aspx?CTT=1&section=1

44

22
Informática Aplicada TECSUP

Enlaces

• http://office.microsoft.com/es-es/excel-help/novedades-de-
excel-2013-HA010369709.aspx?CTT=1#_Toc274394078
• http://www.uv.mx/personal/llopez/files/2013/03/Manual-
Microsoft-Office-Excel-2013.pdf
• http://www.aulaclic.es/excel2013/index.htm
• http://www.aulafacil.com/excel-2013/curso/Temario.htm+
• https://exceltotal.com/como-crear-una-tabla-dinamica/

Preguntas

1. ¿Por defecto, las Tablas dinámicas se crean en la misma

Hoja que los datos?
2. ¿Dónde se ubica la opción Tabla dinámica?
3. ¿Dónde se ubica la opción Resumir como tabla dinámica?
4. Cuando los datos originales sufren modificaciones ¿se
actualiza automáticamente la Tabla dinámica?

46

23
Informática Aplicada TECSUP

Respuestas

1. ¿Por defecto, las Tablas dinámicas se crean en la misma Hoja

que los datos?
– No. Se crean en una Hoja nueva.
2. ¿Dónde se ubica la opción Tabla dinámica?
– En la ficha Insertar, grupo Tablas.
3. ¿Dónde se ubica la opción Resumir como tabla dinámica?
– En la ficha Diseño de Herramientas de tabla, grupo Herramientas
4. Cuando los datos originales sufren modificaciones ¿se actualiza
automáticamente la Tabla dinámica?
– No. Hay que utilizar el botón Actualizar del grupo Datos de las
Herramientas de tabla dinámica.

47

24