11-Excel Tablas Dinámicas

Informática Aplicada: Unidad 10

Tecsup

Tablas Dinám Dinámicas icas Informática Aplicada

Índice • • • •

Introducción Objetivos Concepto de Tabla Dinámica Creación de una Tabla Dinámica – Preparar los datos – Procedimiento de creación Tabla Dinámica – Diseño de la Tabla – Uso de filtros – Mostrar detalles

Edición de una Tabla Dinámica – Añadir, mover, mover, eliminar campos – Funciones resumen – Subtotales – Cálculos adicionales • Formato de Tablas Dinámicas • Gráficos dinámicos • Bibliografía •

2

Dpto de Informática

1


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.

3

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.

4


2


Tecsup

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

analizar 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. • Por ejemplo, dada una Hoja Excel con la información de ventas a nivel nacional, una Tabla Dinámica permitiría comparar las ventas por Ciudad, Almacén o Producto.

5

Tablas dinámicas: Utilidad • Utilizando Tablas Dinámicas se puede analizar la información

desde distintos ángulos. • Por ejemplo: comparar por regiones o por períodos de tiempo; qué ventas están creciendo, o disminuyendo, etc.

6


3


Tecsup

Creación: Procedimiento • Para crear una tabla dinámica: – Utilice la opción Tabla dinámica del grupo Tablas, ficha Insertar .

• Cuando previamente se ha creado una Tabla Tabla Excel: – Seleccione una celda de la Tabla Tabla – Use la ficha Herramientas de Tabla / Diseño – En el grupo Herramientas, haga clic sobre Resumir con Tabla dinámica

7

Creación: Procedimiento •

En el cuadro de diálogo: Crear tabla dinámica posici onado – Puede seleccionar un rango o una Tabla previamente creada, si se ha posicionado en una celda dentro de toda la data, automáticamente se coloca el rango.

– Puede elegir la ubicación de la Tabla dinámica : en una hoja nueva, o en una

existente.

8


4


Tecsup

Creación: Procedimiento • •

Por defecto, la Tabla Dinámica se crea en una Hoja nueva. En esta hoja aparecen la Tabla Dinámica vacía y un panel con la Lista de campos para seleccionarlos y asignarles una ubicación.

9

Creación: Diseño • En la parte inferior de la Lista de

campos se muestran las cuatro áreas de la Tabla Dinámica:

informe : es el dato de mayor – Filtro de informe:

nivel que se quiere quier e analizar. – Etiquetas de columna: columna: los datos que se mostrarán en columnas. fila : los datos que se – Etiquetas de fila: mostrarán en filas. – Valores: los datos con que se harán los cálculos numéricos: contar, sumar, promediar, etc.

10


5


Tecsup

Creación: Diseño • Según el análisis que se desea

realizar, realizar, bastará con arrastrar los campos al área correspondiente. • Nótese que no es indispensable utilizar las cuatro áreas.

1

3

2

4

1 3

2

4

Creación: Ejemplo de diseño 1 •

Se desea comparar las ventas realizadas en las ciudades de l a región Cusco, para ello se están utilizando los campos Región / Ciudad / Categoría / Producto / Total Final

12


6


Tecsup

Creación: Ejemplo Ejemplo de diseño 2 • Se desea analizar analizar la venta v enta de papel por regiones, para ello ello se

están utilizando los campos Categoría / Región / Producto / Total Final

13

Ejercicio 1: Creación • Abrir el archivo Libreria.xlsx y ubicarse en la hoja Ventas. • Ir a la ficha Insertar , grupo Tablas y Tabla dinámica . • La tabla queda marcada en el rango A1:L454. • Elegir la opción Nueva hoja de cálculo. • Clic en Aceptar.

14


7


Tecsup

Ejercicio 2: Diseño Calcular la Suma de los Totales Totales Finales por Región. Seleccionar los campos: campos: Región, Categoría, Producto, Ciudad y Total Total final y ubicarlos como se muestran. • Renombre la hoja con el nombre Tablad1 • •

1

4

2 3

5

15

Uso de Filtros En el área Filtro de informe es posible utilizar el botón de filtro para uno o varios de los valores posibles. • Asimismo, Asimismo, los Etiquetas de columna y de fila ofrecen opciones para filtrar y ordenar los val ores mostrados. •

16


8


Tecsup

Ejercicio 3: Filtros En la hoja TablaD1 • Mostrar los datos de la región Cusco y luego Aceptar. Aceptar. • Luego seleccionar en los etiquetas de fila Corrector y Lapicero. •

17

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

18


9


Tecsup

Edición: Añadir, Añadir, eliminar eliminar,, mover campos • Para añadir o eliminar

campos de la Tabla 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.

19

Ejercicio 4: Detalles y edición • En la TablaD1, ir a la celda B11 B11 hacer clic derecho y seleccionar Mostrar detalles . • Cámbiele el nombre a esta hoja y colóquele Detalles . • Copie el contenido de la hoja TablaD1 a una nueva hoja de

cálculo y colóquele de nombre TablaD2. • En la hoja TablaD2, deshacer filtros y modifique la ubicación de los campos como se muestra en la siguiente siguiente imagen.

20


10


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: – 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, Mín, Producto, Producto, etc.

21

Funciones resumen disponibles F u n c i ón :

D e s c r i p c i ó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 Calcula la varianza de la población

Varp

22


11


Tecsup

Ejercicio 5: Cambio de Función En la hoja TablaD2, elimine el campo Producto. • Cambie la función Suma de Total Final por la función Promedio de Total Final. •

– En la celda B6 haga clic derecho, seleccione Resumir Resumir datos datos por y seleccione: Promedio.

23

Edición: Dos funciones resumen • Para utilizar utilizar dos funciones resumen

sobre un mismo valor, por ejemplo Suma y Promedio, incluya en el área Valores el mismo elemento dos veces. El segundo valor v alor aparecerá con el sufijo “2”.

• Para cambiar de función, se hará

clic derecho sobre una celda de la columna a cambiar y elegir otra función resumen (por ejemplo, Promedio). • Luego podrá modifi m odificar car el encabezado de la columna por uno más apropiado.

24


12


Tecsup

Ejercicio 6: Uso de dos funciones En la hoja TablaD2, agregue en el área de Valores Valores el campo Total Final 2 veces más. • A la columna columna que muestra el encabezado de de Suma de Total Total Final2, deberá cambiarle su función para que cuente (Recuento). •

•

Modifique Modifique el encabez encabezado, ado, elimine elimine el número número “2”. “2”.

Edición: 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.

Grupo

26


13


Tecsup

Edición: Función de subtotal • Los subtotales son

sumados por defecto. Es posible elegir una función distinta: – Haga clic derecho sobre el

rótulo de fila que desea modificar. – Use la opción Configuración de campo

– En este diálogo puede elegir la opción Personalizado y

una o varias funciones de subtotal.

27

Ejercicio Ejercic io 7: Subtotales •

En la hoja TablaD1 se cambiará la forma de mostrar los subtotales. – En la ficha Diseño, de las Herramientas de la tabl a dinámica, seleccione Subtotales y Mostar todos los subtotales en la parte inferior el grupo .

•

Ubicarse en la celda A8, hacer clic derecho y seleccionar la opción Configuración de Campo…, seleccionar Personalizado y en él las funciones Suma y Promedio.

28


14


Tecsup

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 opciones:: como, con las opciones •

– – – – – – – –

Diferencia de % de % de la diferencia de Total en % del total de filas % del total de columnas % del total general Índice

29

Cálculos adicionales disponibles Cálculo

Ejemplo de uso

Diferencia de

Comparar las ventas de cada ciudad con las ventas en una ciudad en particular

% de

Mostrar las ventas de cada región como porcentaje de las ventas de otra región

% de la diferencia de

Ver el cambio porcentual porcentual de las ventas de cada mes respecto al mes anterior

Total en

Mostrar las ventas anuales a la fecha, por producto o región

% del tota totall de fila filass

Ver las las ven ventas tas de cad cadaa producto cto co como porcen centaj taje de de las las ven ventas tas tota totale less

% del total de columnas

Ver las ventas de cada región como porcentaje de las ventas a nivel nacional

% del t otal general

Mostrar la venta regional de cada producto como un porcentaje del total de ventas

Índice

Comparar las ventas regionales de cada producto con las ventas por producto, producto, las ventas regionales y la s ventas totales 30


15


Tecsup

Ejercicio 8: Cálculos adicionales • En la hoja TablaD1, adicione

el campo Total Final en el área de Valores. • Seleccione la celda C7, clic derecho, seleccionar Resumir Valores por y Más opciones. • Seleccione la ficha Mostrar valores como , y de la lista seleccione: % del total general. • Personalice Personalice el nombre de la columna con: % del Total General.

31

Edición: Actualizar los datos • Es posible que, luego de construida la Tabla dinámica, los datos

originales originales sufran modificaci m odificaciones. ones. • 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

32


16


Tecsup

Ejercicio 9: Actualización de Datos Vaya a la hoja Ventas (datos originales) y ubíquese en la celda L152, modifique el datos de 2077.74 a 20770.74. • Regrese a la hoja TablaD1 y note que el valor en B8 no se ha modificado. • Para que se refleje este cambio deberá ir a la Ficha Opciones, Grupo datos y seleccionar Actualizar . • Note ahora cómo los valores de la tabla dinámica han variado. •

Formato de Tabla Dinámica •

En la ficha Diseño, grupo Diseño, el botón Diseño de informe presenta tres diseños predefinidos: – Compacto (diseño por defecto). – Esquema. – Tabular.

Compacto

Esquema

Tabular

34


17


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 Tabla Dinámica. Di námica. l as opciones de • También están disponibles todas las formato de una Hoja convencional.

35

Ejercicio 10: Formato y diseño • En la hoja TablaD2, modifique el diseño del informe,

seleccione el tipo: Esquema. • Ahora cambie cambie el estilo de la tabla dinámica, dinámica, puede escoger algún estilo predeterminado de la ficha Diseño, grupo Estilos de la Tabla Dinámica.


18


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 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á mostrará dentro del Gráfico 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


Tecsup

Ejercicio 11: Gráficos Dinámicos • • • • •

En la hoja Tablad2 elimine el campo Ciudad y en el campo Valores solo deje la Suma del total. En la ficha Herramientas de Tabla dinámica, en la ficha Opciones, grupo herramientas, seleccione Gráfico Dinámico. Seleccione el gráfico Columna agrupada con efecto 3D Aceptar . En la ficha Presentación del Gráfico Dinámico, oculte la leyenda.

39

Excel Avanzado Tablas dinámicas dinámicas

Sirven para

Analizar datos

Contiene

Vinculadas Vinculadas con

Tiene

Campos

Diseño

Compacto

Tales como

Esquema

Áreas Que son

Gráficos dinámicos

Tabular Filtro de informe

Rótulos de columna

Rótulos de fila

Valores

Muestran Contiene


Permiten

Funciones resumen

Cálculos adicionales

Filtrar valores

Ejemplos

Ejemplos

Suma, Cuenta, Promedio, Max, Min, Producto, Desvest, Var

Diferencia de, % de, Total en, % de fila, % de columna, % de total

20


Tecsup

Glosario • Registro: conjunto de atributos o características (campos) (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. específico. Ejemplo: Precio, Fecha de venta. • Filtro: opción para ocultar / mostrar las filas que cumplan una especificación determinada.

41

Bibliografía • Fernando Rosino Alonso. Excel 2010. Grupo Anaya

Comercial. 2010. • Walkenbach, John. La Biblia de Excel 2010. Grupo Anaya Comercial. 2010. • 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/inform /es-es/excel-help/informacion-generalacion-general – http://office.microsoft.com RZ101773335.aspx?CTT=1§ion=1

42


21


Tecsup

Enlaces • http://office.m http://office.microsoft.com/e icrosoft.com/es-es/excel-help s-es/excel-help/novedades-de/novedades-de-

excel-2010-HA010369709.aspx?CTT=1#_Toc274394078 • http://www.uv.mx/personal/llopez/files/2013/03/ManualMicrosoft-Office-Excel-2010.pdf • http://www.aulaclic.es/excel2010/index.htm • http://www.aulafacil.com/excel-2010/curso/Temario.htm

Preguntas 1. ¿Por defecto, defecto, las las Tablas Tablas dinámicas dinámicas se crean crean en la misma misma Hoja que los datos? 2. ¿Dónde ¿Dónde se ubic ubica a la opción opción Tabla Tabla dinámic dinámica? a? 3. ¿Dónde ¿Dónde se ubic ubica a la opción opción Resumir Resumir como como tabla dinám dinámica? ica? 4. Cuando Cuando los datos datos origi original nales es sufren modi modifica ficacion ciones es ¿se actualiza automáticamente la Tabla dinámica?

44


22


Tecsup

Respuestas 1. ¿Por defecto, defecto, las las Tablas Tablas dinámicas dinámicas se crean crean en la misma misma Hoja que los datos? – No. Se crean en una Hoja nueva.

2. ¿Dónde ¿Dónde se ubic ubica a la opción opción Tabla Tabla dinámic dinámica? a? Tablas. – En la ficha Insertar, grupo Tablas.

3. ¿Dónde ¿Dónde se ubic ubica a la opción opción Resumir Resumir como como tabla dinám dinámica? ica? – En la ficha Diseño de Herramientas de tabla, grupo Herramientas

4. Cuando Cuando los datos datos origina originales les sufren sufren modific modificacio aciones nes ¿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.

45


23

11-Excel Tablas Dinámicas

Recommend Documents