Upload
cindy18ut
View
24
Download
0
Embed Size (px)
DESCRIPTION
Ejemplos
Citation preview
Microsoft Excel 2013
1
Informes de tabla dinámica
Contenido
Informe de tabla dinámica
Crear un informe de tabla dinámica
o Crear el informe de tabla dinámica.
o Organizar los campos en el informe de tabla dinámica.
Cómo funciona la lista de campos de tabla dinámica.
Agregar campos al informe de tabla dinámica.
Usar el campo de filtros.
Filtrar por campos de fila o campos de columna.
Añadir otros campos al informe de tabla dinámica.
Intercambiar los campos del informe de tabla dinámica.
Expander o contraer el contenido de los campos.
Agrupar filas o columnas en la tabla dinámica.
o Acceder a los registros consolidados en un valor del informe de tabla
dinámica.
o Actualizar los datos del informe de tabla dinámica.
o Deshabilitar los filtros del informe de tabla dinámica.
Deshabilitar el filtro de un campo específico.
Deshabilitar todos los filtros en el informe de tabla dinámica.
o Agregar un campo calculado al informe de tabla dinámica.
Crear un gráfico dinámico
Microsoft Excel 2013
2
Informe de tabla dinámica
Utilice un informe de tabla dinámica para resumir, analizar, explorar y presentar datos
de resumen. Utilice un informe de gráfico dinámico para ver esos datos de resumen
contenidos en un informe de tabla dinámica y ver fácilmente comparaciones, patrones y
tendencias. Ambos informes le permiten tomar decisiones informadas sobre datos críticos
de su empresa.
Un informe de tabla dinámica es una forma interactiva de resumir rápidamente
grandes volúmenes de datos. Utilice un informe de tabla dinámica para analizar datos
numéricos en profundidad y para responder preguntas no anticipadas sobre los datos. Un
informe de tabla dinámica está especialmente diseñado para:
Consultar grandes cantidades de datos de muchas maneras diferentes y cómodas
para el usuario.
Calcular el subtotal y agregar datos numéricos, resumir datos por categorías y
subcategorías, y crear cálculos y fórmulas personalizados.
Expandir y contraer niveles de datos para destacar los resultados y desplazarse
hacia abajo para ver los detalles de los datos de resumen de las áreas de interés.
Desplazar filas a columnas y columnas a filas para ver resúmenes diferentes de
los datos de origen.
Filtrar, ordenar, agrupar y dar formato condicional a los subconjuntos de datos
más útiles e interesantes para poder centrarse en la información que le interesa.
Presentar informes electrónicos o impresos concisos, atractivos y con comentarios.
Por ejemplo a partir de una gran tabla que contiene los detalles de ventas de productos
de distintas categorías
podemos obtener un resumen de las ventas por categorías, tal como se muestra en el
cuadro siguiente.
Informes de tabla dinámica
3
Crear un informe de tabla dinámica
Para crear un informe de tabla dinámica el origen de los datos puede ser una tabla o
rango de Excel, o un origen de datos externo como una tabla o vista de Microsoft SQL
Server, Oracle o alguna otra base de datos.
En este ejemplo el origen de los datos será la tabla de Excel VENTAS almacenada en el
archivo VENTAS.xlsx Se desea crear un informe de tabla dinámica que consolida las
ventas por categoría y que se guardará en otra hoja del mismo archivo.
Crear el informe de tabla dinámica
1. Abra el archivo VENTAS.xlsx que contiene la tabla VENTAS.
2. Coloque el puntero de celda en una de las celdas de la tabla.
3. En la ficha Insertar, en el grupo Tablas, haga clic en Tabla dinámica, luego en
Tabla dinámica. Se abre el diálogo Crear tabla dinámica.
Microsoft Excel 2013
4
4. En Seleccione los datos que desea analizar aparece seleccionada la VENTAS.
En Elija dónde desea colocar el informe de tabla dinámica seleccione Nueva
hoja de cálculo y haga clic en Aceptar. Se añade una hoja de cálculo nueva con
un informe de tabla dinámica vacío con la lista de campos disponibles para
diseñar y personalizar el informe.
Organizar los campos en el informe de tabla dinámica
Es importante comprender el modo en que funciona la lista de campos de tabla dinámica
y el modo en que puede organizar diferentes tipos de campos para obtener los resultados
deseados al diseñar un informe de tabla dinámica.
Informes de tabla dinámica
5
Cómo funciona la lista de campos de tabla dinámica
(1) Una tabla de Excel o un origen de datos externo contiene datos estructurados
organizados como uno o varios campos (denominados también columnas) que se
muestran en la Lista de campos.
(2) Si desea filtrar los datos del informe de tabla dinámica en base a los valores de un
campo, mueva dicho campo al área de Filtro de informe en la lista de campos,
lo que a su vez moverá el campo al área de filtro de informe en el informe de
tabla dinámica.
(3) Si desea mostrar las etiquetas de columnas, mueva el campo al área de Rótulos
de columna en la lista de campos, lo que a su vez moverá el campo al área de
etiqueta de columna en el informe de tabla dinámica.
(4) Si desea mostrar las etiquetas de filas, mueva el campo al área de Rótulos de
fila en la lista de campos, lo que a su vez moverá el campo al área de fila de
columna en el informe de tabla dinámica.
(5) Para especificar los valores que el informe de tabla dinámica debe mostrar, mueva
el campo al área de Valores en la lista de campos, lo que a su vez moverá el
campo al área de valores en el informe de tabla dinámica.
Agregar campos al informe de tabla dinámica
Para agregar campos al informe, siga uno o varios de estos procedimientos:
Active la casilla de verificación situada junto a cada nombre de campo en la Lista
de campos. El campo se coloca en el área predeterminada de la sección de
diseño, pero luego puede organizar los campos si lo desea.
De forma predeterminada, los campos no numéricos se agregan al área de
Rótulos de fila, los campos numéricos se agregan al área de Valores y las
jerarquías OLAP de fecha y hora (año, trimestre, mes, día, etc.) se agregan al
área de Rótulos de columna.
Haga clic con el botón secundario del mouse y seleccione el comando
correspondiente: Agregar a filtro de informe, Agregar a etiqueta de
columna, Agregar a etiqueta de fila y Agregar a valores, para colocar el
campo en un área específica de la sección de diseño.
Arrastre el campo entre la Lista de campos y un área de la sección de diseño.
Para agregar un campo varias veces, repita la operación.
Microsoft Excel 2013
6
Para diseñar nuestro informe de tabla dinámica ejecute lo siguiente:
1. En la Lista de campos seleccione el campo Categoría. Observe que por defecto
se ubica en el área de Rótulos de fila.
2. En la Lista de campos haga un clic secundario sobre el campo Producto y luego
clic en Agregar a etiquetas de fila. Observe que cada producto se ubica bajo la
categoría que le corresponde.
Informes de tabla dinámica
7
3. Desde la Lista de campos arrastre el campo Unidades al área de Valores.
Excel calcula el acumulado de unidades vendidas de cada producto.
Microsoft Excel 2013
8
4. Arrastre al área de Valores el campo Monto. Excel suma los montos vendidos de
cada producto.
Informes de tabla dinámica
9
5. Arrastre el campo Fecha al área Filtro de informe. Esto permitirá obtener las
unidades y monto vendido de cada producto para fechas específicas.
Informes de tabla dinámica
11
Usar el campo de filtros
1. En el área de Filtro de informe de la tabla, en el campo Fecha, haga clic en el
botón de selección de fechas.
2. Seleccione la fecha 14 de junio del 2010, y haga clic en Aceptar. Observe como
cambian los valores en el informe de tabla dinámica.
Microsoft Excel 2013
12
Filtrar por campos de filas o campos de columna
Es posible definir filtros en los campos de fila o campos de columna. A diferencia de los
filtros de página (filtros en el Campo de filtros), en este caso existen casillas de
verificación que se pueden marcar para los elementos que permanecerán visibles y
desmarcar para los elementos que se desea ocultar.
Por ejemplo, podemos hacer que el informe muestre solo los datos de ventas del 14 de
junio del 2010 de los productos de las categorías Embutidos y Lácteos.
1. Haga clic en el botón de selección de elementos del rótulo de fila a filtrar. Se
muestra la lista de elementos que pertenecen al rótulo de fila.
Informes de tabla dinámica
13
2. Haga clic en (Seleccionar todo) para desmarcar todos los elementos.
3. Haga clic en EMBUTIDOS y en LACTEOS para seleccionarlos; luego clic en
Aceptar.
Microsoft Excel 2013
14
Añadir otros campos al informe de tabla dinámica
Se desea tener la posibilidad de analizar las ventas de cada producto por local.
1. Desde la Lista de campos arrastre lel campo Local al área de Rótulos de
columna. Se muestra el local en el que se vendieron los productos de la
categoría Embutidos el 14 de junio del 2010.
Informes de tabla dinámica
15
2. Elimine los filtros establecidos de modo tal que se muestren todas las fechas y
todas las categorías de productos. Ahora se muestra las ventas por locales.
Observe que Suma de Unidades contiene a todos los locales; lo mismo ocurre
con Suma de Monto.
Microsoft Excel 2013
16
3. En el área Rótulos de columna haga clic cobre la punta de flecha del campo
Local. Se muestra su menú contextual.
Informes de tabla dinámica
17
4. Haga clic en Mover al principio. El campo Local se mueve hacía arriba en el
área Rótulos de columna. Observe el cambio en la presentación de los datos;
ahora cada local contiene a Suma de Unidades y Suma de Monto.
Microsoft Excel 2013
18
5. Guarde el archivo como VENTAS-TD1.xlsx.
Intercambiar los campos del informe de tabla dinámica
El diseño de la tabla dinámica puede ajustarse a distintas situaciones y necesidades de
análisis de datos. Podemos intercambiar la ubicación de los campos para que la
presentación de los datos se ajuste mejor a nuestras necesidades.
Se desea conocer el nivel de ventas del producto Whisky Johnnie Walker Etiqueta
Roja en el periodo que va desde el 1 al 7 de junio del 2010 en el local de Miraflores.
1. Arrastre el campo Local desde Rótulos de columna al área Filtro de informe.
2. Arrastre el campo Categoría desde Rótulos de fila a Filtro de informe.
Informes de tabla dinámica
19
3. En el campo Fecha seleccione las fechas del 1 al 7 de junio del 2010.
4. En el campo Local seleccione Miraflores.
5. En el campo Categoría seleccione Licores y gaseosas.
Microsoft Excel 2013
20
6. Si desea que en el detalle del informe se muestren las fechas, mueva la columna
Fecha al área de Rótulos de fila, y mueva la columna Producto al área de
Filtro de informe de modo que quede debajo de la columna Categoría.
Microsoft Excel 2013
22
8. Guarde el archivo como VENTAS.xlsx.
Expandir o contraer el contenido de los campos
1. Abra el archivo VENTAS.xlsx.
2. Haga un clic secundario sobre la categoría EMBUTIDOS. Se muestra el menú
contextual.
3. Haga clic en Expandir o contraer.
Informes de tabla dinámica
23
4. Haga clic en Contraer todo el campo. Se oculta el contenido de cada categoría.
Microsoft Excel 2013
24
5. Incorpore el campo Local en el informe de tabla dinámica. Arrastre el campo
Local al área Rótulos de fila de modo que quede al inicio de la lista de campos
en el área.
Informes de tabla dinámica
25
6. Contraiga el campo Local de modo que el informe quede como se muestra a
continuación.
Microsoft Excel 2013
26
Agrupar filas o columnas de la tabla dinámica
En el informe se desea agrupar los locales del siguiente modo:
Zona Alfa: Breña, Los Olivos y Pueblo Libre
Zona Beta: Callao y Miraflores
1. Seleccione los locales Breña, Los Olivos y Pueblo Libre haciendo clic en Breña,
Ctrl+clic en Los Olivos, y Ctrl+clic en Pueblo Libre.
2. En la cinta de opciones de Herramientas de tabla dinámica, en la ficha
ANALIZAR, en el grupo Agrupar, haga clic en Agrupar selección. Se crea el
Grupo1 conteniendo a los locales seleccionados.
3. Repetir el procedimiento de los pasos 1 y 2 con los locales Callao y Miraflores.
Informes de tabla dinámica
27
4. Cambiar los rótulos Grupo1 y Grupo2 por Zona Alfa y Zona Beta
respectivamente.
Microsoft Excel 2013
28
5. Si desea desagrupar, haga un clic secundario sobre el rótulo del grupo y luego clic
en Desagrupar.
6. Guardar el archivo como VENTAS.xlsx.
Informes de tabla dinámica
29
Acceder a los registros consolidados en un valor del informe de tabla dinámica
Abra el archivo VENTAS.xlsx.
Se desea conocer cuáles son los registros en el origen de datos que han sido
consolidados en el valor 200 de la columna Suma de Unidades para el producto
Chorizo Parrillero Laive.
1. Haga doble clic sobre el valor 200 cuyo detalle desea conocer. Se abre una nueva
hoja de cálculo mostrando los registros que han sido consolidados en dicho valor.
Microsoft Excel 2013
30
Actualizar los datos del informe de tabla dinámica
En la cinta de opciones de Herramientas de tabla dinámica, en la ficha ANALIZAR,
en el grupo Datos, haga clic en Actualizar, y luego en Actualizar.
Informes de tabla dinámica
31
Deshabilitar los filtros del informe de tabla dinámica
Para desarrollar este tema abra el archivo VENTAS.xlsx.
Deshabilitar el filtro de un campo específico
Se desea deshabilitar el filtro en el campo Local.
1. Haga clic en el botón de filtrado del campo Local en el informe de tabla dinámica
o en la Lista de campos de tabla dinámica. Se muestra la lista de valores del
campo.
Microsoft Excel 2013
32
2. Seleccione (Todas) y haga clic en Aceptar.
Deshabilitar todos los filtros en el informe de tabla dinámica
1. En la cinta de opciones de Herramientas de tabla dinámica, en la ficha
Opciones, en el grupo Acciones, haga clic en Borrar, luego clic en Borrar
filtros.
Informes de tabla dinámica
33
Agregar un campo calculado al informe de tabla dinámica
Para desarrollar este tema abra el archivo VENTAS.xlsx.
El margen de utilidad promedio es de 25%. Se desea añadir una columna que muestre
monto de la utilidad para cada producto y categoría.
Microsoft Excel 2013
34
1. En Herramientas de tabla dinámica, en la ficha ANALIZAR, en el grupo
CALCULO, haga clic en CALCULOS, ELEMENTOS Y CONJUNTO, luego en
Campo calculado. Se abre el diálogo Insertar campo calculado.
2. En Nombre digite Utilidad como identificador del campo calculado.
3. En Fórmula escriba la fórmula que calcula la utilidad:
=Monto/1.25*0.25
Informes de tabla dinámica
35
4. Haga clic en Aceptar. Se añade un nuevo campo al informe de tabla dinámica.
Microsoft Excel 2013
36
5. Guarde el archivo como VENTAS.xlsx
Crear un gráfico dinámico
A partir de los datos del informe de tabla dinámica podemos crear un gráfico dinámico. A
diferencia de un gráfico convencional, el gráfico dinámico permite filtrar los datos en el
mismo gráfico.
Para ilustrar este tema usaremos el archivo VENTAS.xlsx.
1. Ubique el puntero de celda en una celda del informe de tabla dinámica.
2. En Herramientas de tabla dinámica, en la ficha ANALIZAR, en el grupo
Herramientas, haga clic en Gráfico dinámico. Se abre el diálogo Insertar
gráfico.
Informes de tabla dinámica
37
3. Seleccione el tipo de gráfico. Por ejemplo, Columna agrupada 3D, y haga clic en
Aceptar.
Microsoft Excel 2013
38
4. Observe que para el gráfico dinámico se habilita el Panel de filtros del gráfico
dinámico. En Campos de eje (categorías) haga clic en el botón de filtrado de
Local2.
5. Quítele la marca a la casilla de verificación de Zona Beta, y haga clic en Aceptar.
Observe que el filtro en el gráfico dinámico afecta también al informe de tabla
dinámica.