Microsoft Excel 2013 Microsoft Excel 2013
II n
nffor
orme
mes
s de
de tabl
tabla di
a din
ná
ámi
mi ca
ca
Contenido
Contenido
Informe de tabla dinámicaInforme de tabla dinámica
Crear un informe de tabla dinámicaCrear un informe de tabla dinámica
o
o Crear el informe de tabla dinámica.Crear el informe de tabla dinámica. o
o Organizar los campos en el informe de Organizar los campos en el informe de tabla dinámica.tabla dinámica.
Cómo funciona la lista de campos de tabla Cómo funciona la lista de campos de tabla dinámica.dinámica.
Agregar campos al informe de Agregar campos al informe de tabla dinámica.tabla dinámica.
Usar el campo de filtros.Usar el campo de filtros.
Filtrar por campos de fila o Filtrar por campos de fila o campos de columna.campos de columna.
Añadir otros campos al informe de tablAñadir otros campos al informe de tabla dinámica.a dinámica.
Intercambiar los campos del informe de tabla dinámica.Intercambiar los campos del informe de tabla dinámica.
Expander o contraer el contenido de los campos.Expander o contraer el contenido de los campos.
Agrupar filas o columnas en la tabla dinámica.Agrupar filas o columnas en la tabla dinámica. o
o Acceder a los registros consolidados en un valor del informe de tablaAcceder a los registros consolidados en un valor del informe de tabla
dinámica. dinámica.
o
o Actualizar los datos del informe de tabla diActualizar los datos del informe de tabla dinámica.námica. o
o Deshabilitar los filtros del informe de tabla dinámica.Deshabilitar los filtros del informe de tabla dinámica.
Deshabilitar el filtro de un campo específico.Deshabilitar el filtro de un campo específico.
Deshabilitar todos los filtros en el informe de Deshabilitar todos los filtros en el informe de tabla dinámica.tabla dinámica. o
o Agregar un campo calculado al informe dAgregar un campo calculado al informe de tabla dinámica.e tabla dinámica.
2 2
Informe de tabla dinámica
Informe de tabla dinámica
Utilice un
Utilice un informe de tabla dinámicainforme de tabla dinámica para resumir, analizar, explorar y p para resumir, analizar, explorar y presentar datosresentar datos de resumen. Utilice un
de resumen. Utilice un informe de gráfico dinámicoinforme de gráfico dinámico para ver esos datos de resumen para ver esos datos de resumen contenidos en un informe de tabla dinámica y ver fácilmente comparaciones, patrones y contenidos en un informe de tabla dinámica y ver fácilmente comparaciones, patrones y tendencias. Ambos informes le permiten
tendencias. Ambos informes le permiten tomar decisiones informadas sobre datos críticostomar decisiones informadas sobre datos críticos de su empresa.
de su empresa. Un
Un informe de tabla dinámicainforme de tabla dinámica es una forma interactiva de resumir rápidamente es una forma interactiva de resumir rápidamente grandes volúmenes de datos. Utilice un informe de tabla dinámica para analizar datos 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 numéricos en profundidad y para responder preguntas no anticipadas sobre los datos. Un informe de tabla dinámica está especialmente diseñado para:
informe de tabla dinámica está especialmente diseñado para:
Consultar grandes cantidades de datos de muchas maneras diferentes y cómodasConsultar grandes cantidades de datos de muchas maneras diferentes y cómodas
para el usuario. para el usuario.
Calcular el subtotal y agregar datos numéricos, resumir datos por categorías yCalcular el subtotal y agregar datos numéricos, resumir datos por categorías y
subcategorías, y crear cálculos y fórmulas personalizados. subcategorías, y crear cálculos y fórmulas personalizados.
Expandir y contraer niveles de datos para destacar los resultados y desplazarseExpandir 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. 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 deDesplazar filas a columnas y columnas a filas para ver resúmenes diferentes de
los datos de origen. los datos de origen.
Filtrar, ordenar, agrupar y dar formato condicional a los subconjuntos de datosFiltrar, 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. 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.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 Por ejemplo a partir de una gran tabla que contiene los detalles de ventas de productos de distintas categorías
de distintas categorías
podemos obtener un resumen de las ventas por categorías, tal como se muestra en el podemos obtener un resumen de las ventas por categorías, tal como se muestra en el cuadro siguiente.
Microsoft Excel 2013 Microsoft Excel 2013
Informe de tabla dinámica
Informe de tabla dinámica
Utilice un
Utilice un informe de tabla dinámicainforme de tabla dinámica para resumir, analizar, explorar y p para resumir, analizar, explorar y presentar datosresentar datos de resumen. Utilice un
de resumen. Utilice un informe de gráfico dinámicoinforme de gráfico dinámico para ver esos datos de resumen para ver esos datos de resumen contenidos en un informe de tabla dinámica y ver fácilmente comparaciones, patrones y contenidos en un informe de tabla dinámica y ver fácilmente comparaciones, patrones y tendencias. Ambos informes le permiten
tendencias. Ambos informes le permiten tomar decisiones informadas sobre datos críticostomar decisiones informadas sobre datos críticos de su empresa.
de su empresa. Un
Un informe de tabla dinámicainforme de tabla dinámica es una forma interactiva de resumir rápidamente es una forma interactiva de resumir rápidamente grandes volúmenes de datos. Utilice un informe de tabla dinámica para analizar datos 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 numéricos en profundidad y para responder preguntas no anticipadas sobre los datos. Un informe de tabla dinámica está especialmente diseñado para:
informe de tabla dinámica está especialmente diseñado para:
Consultar grandes cantidades de datos de muchas maneras diferentes y cómodasConsultar grandes cantidades de datos de muchas maneras diferentes y cómodas
para el usuario. para el usuario.
Calcular el subtotal y agregar datos numéricos, resumir datos por categorías yCalcular el subtotal y agregar datos numéricos, resumir datos por categorías y
subcategorías, y crear cálculos y fórmulas personalizados. subcategorías, y crear cálculos y fórmulas personalizados.
Expandir y contraer niveles de datos para destacar los resultados y desplazarseExpandir 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. 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 deDesplazar filas a columnas y columnas a filas para ver resúmenes diferentes de
los datos de origen. los datos de origen.
Filtrar, ordenar, agrupar y dar formato condicional a los subconjuntos de datosFiltrar, 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. 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.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 Por ejemplo a partir de una gran tabla que contiene los detalles de ventas de productos de distintas categorías
3 3
Crear un informe de tabla dinámica
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 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 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.
Server, Oracle o alguna otra base de datos.
En este ejemplo el origen de los datos será la tabla de Excel
En este ejemplo el origen de los datos será la tabla de Excel VENTASVENTAS almacenada en elalmacenada en el archivo
archivo VENTAS.xlsxVENTAS.xlsx Se desea crear un informe de tabla dinámica que consolida las 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.
ventas por categoría y que se guardará en otra hoja del mismo archivo.
Crear el informe de tabla dinámica
Crear el informe de tabla dinámica
1.
1. Abra el archivoAbra el archivo VENTAS.xlsxVENTAS.xlsx que contiene la tabla que contiene la tabla VENTASVENTAS.. 2.
2. Coloque el puntero de celda en una de las celdas de la tabla.Coloque el puntero de celda en una de las celdas de la tabla. 3.
3. En la fichaEn la ficha InsertarInsertar, en el grupo, en el grupo TablasTablas, haga clic en, haga clic en Tabla dinámicaTabla dinámica, luego en, luego en Tabla dinámica
Microsoft Excel 2013
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.
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.
Microsoft Excel 2013
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.
7
3. Desde la Lista de campos arrastre el campo Unidades al área de Valores. Excel calcula el acumulado de unidades vendidas de cada p roducto.
Microsoft Excel 2013
4. Arrastre al área de Valores el campo Monto. Excel suma los montos vendidos de cada producto.
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.
Microsoft Excel 2013
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
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.
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
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.
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
3. En el área Rótulos de columna haga clic cobre la punta de flecha del campo Local. Se muestra su menú contextual.
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
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.
19
3. En el campo Fecha seleccione las fechas del 1 al 7 de junio del 2010. 4. En el campo Local seleccione Miraflores.
Microsoft Excel 2013
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.
21
Microsoft Excel 2013
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.
23
Microsoft Excel 2013
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.
25
6. Contraiga el campo Local de modo que el informe quede como se muestra a continuación.
Microsoft Excel 2013
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.
27
4. Cambiar los rótulos Grupo1 y Grupo2 por Zona Alfa y Zona Beta respectivamente.
Microsoft Excel 2013
5. Si desea desagrupar, haga un clic secundario sobre el rótulo del grupo y luego clic en Desagrupar.
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
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.
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
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.
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
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.
35
Microsoft Excel 2013
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.
37
3. Seleccione el tipo de gráfico. Por ejemplo, Columna agrupada 3D, y haga clic en Aceptar.
Microsoft Excel 2013
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.
