¡Descarga computacion avanzada,excel y más Resúmenes en PDF de Computación aplicada solo en Docsity!
MICROSOFT EXCEL
TABLAS DINÁMICAS
Capacidad Utiliza herramientas avanzadas para analizar y resumir de
manera interactiva información de una tabla de datos.
Utiliza la segmentación de datos en situaciones de resumen de
información con precisión y exactitud.
Contenidos
14.1. ¿Qué es una tabla dinámica?
14.2. Crear una tabla dinámica
14.3. Campos de una tabla dinámica
14.4. Cálculos en tablas dinámicas
14.5. Personalizar una tabla dinámica
14.6. Segmentación de datos en tablas dinámicas
- Tablas Dinámicas
14.1. ¿Qué es una tabla dinámica?
Si no tiene demasiada experiencia con tablas dinámicas o no sabe cómo empezar, las tablas dinámicas son una
buena elección. Cuando usa esta característica, Excel determina un diseño significativo haciendo coincidir los datos
con las áreas más adecuadas de la tabla dinámica. Esto le ofrece un punto de partida para una experimentación
adicional. Una vez creada la tabla dinámica, podrá explorar las distintas orientaciones y reorganizar los campos para
conseguir resultados específicos.
Definición
Las tablas dinámicas son un excelente modo de resumir, analizar, explorar y presentar los datos que, además, se
pueden crear con unos pocos clics. Las tablas dinámicas son muy flexibles y se pueden ajustar rápidamente en función
de cómo se tengan que mostrar los resultados. También puede crear gráficos dinámicos a partir de tablas dinámicas
que se actualicen automáticamente al hacerlo las tablas dinámicas.
A continuación, tiene una sencilla lista de gastos domésticos y una tabla dinámica que se basada en los datos de la
tabla.
Figura Nº 1: Tabla de gastos del hogar
Figura Nº 2: Resumen de gastos en tabla dinámica
Mes Categoría Cantidad Enero Transporte S/. 74, Enero Compra S/. 235, Enero Hogar S/. 175, Enero Entretenimiento S/. 100, Febrero Transporte S/. 115, Febrero Compra S/. 240, Febrero Hogar S/. 225, Febrero Entretenimiento S/. 15, Marzo Transporte S/. 90, Marzo Compra S/. 260, Marzo Hogar S/. 200, Marzo Entretenimiento S/. 120,
Suma de Cantidad Mes Categoría Enero Febrero Marzo Total general Compra 235 240 260 735 Entretenimiento 100 125 120 345 Hogar 175 225 200 600 Transporte 74 115 90 279 Total general S/. 584 S/. 705 S/. 670 S/. 1.
- Luego se nos mostrará la lista de campos de la tabla dinámica para ya hacer los resúmenes y cálculos
respectivos.
Figura Nº 6: Campos de tabla dinámica
- Luego se pivotea los elementos de la tabla de datos hacia los cuadrantes de la tabla dinámica, de la siguiente manera:
Figura Nª 7: Campos de tabla dinámica
- Finalmente, para nuestro caso vamos se ha resumido los gastos por categoría y mes.
Figura Nº 8: Resumen de gastos por categoría y mes
14.3. Campos de una tabla dinámica
Cuando se trabaja con una tabla dinámica es preciso conocer los distintos campos de una tabla dinámica cuando esta
es creada.
Figura Nº 9: Descripción de los cuadrantes de una tabla dinámica
También puede arrastrar y colocar manualmente cualquier elemento disponible a alguno de los campos de tabla dinámica. Si ya no quiere un elemento de la tabla dinámica, basta con que lo arrastre fuera de la lista de campos o desactive su selección. La posibilidad de reorganizar los elementos de campo es una de las características de tabla dinámica que hace que sea tan fácil cambiarle rápidamente la apariencia.
14.4. Cálculos en tablas dinámicas
De forma predeterminada, los campos de tabla dinámica que se coloquen en el área Valores se mostrarán como
SUMA. Si Excel interpreta los datos como texto, se mostrarán como CONTAR. Por eso es tan importante asegurarse
Suma de Cantidad Etiquetas de columna Etiquetas de fila Enero Febrero Marzo Total general Compra 235 240 260 735 Entretenimiento 100 125 120 345 Hogar 175 225 200 600 Transporte 74 115 90 279 Total general 584 705 670 1959
De forma predeterminada, los campos no numéricos se agregan al área Fila
Puede usar el cuadrante filtros para arrastrar los campos por el cual desea filtrar la información
Los campos numéricos se agregan al área Valores.
Los campos de fecha y hora se agregan al área Columna
Configuración de campo de valor
En ocasiones se desea realizar otros cálculos que no son muy frecuentes pero necesarios para resumir y analizar la
información, la configuración de campo de valor es la opción que incluye las tablas dinámicas, y lo puede obtener
haciendo clic en el cuadrante de valores.
Aquí encontrará operaciones para PROMEDIO, MAX, MIN, PRODUCTO, CONTAR NUMEROS, DESVEST,
DESVESTP, VAR, VARP.
Figura Nº 13: Configuración de campo de valor – Resumir valores por
Para cambiar el cálculo predeterminado, pulse primero en la flecha situada a la derecha del nombre de campo y
seleccione después la opción Configuración de campo de valor.
El siguiente ejemplo muestra el promedio de cantidades por categoría y mes.
Figura Nº 14: Promedio de cantidades por categoría y mes
Ahora se muestran los valores máximos de cada mes y categoría
Promedio de Cantidad Mes
Categoría Enero Febrero Marzo Total general
Compra 235 240 260 245
Entretenimiento 100 125 120 115
Hogar 175 225 200 200
Transporte 74 115 90 93
Total general 146 176,25 167,5 163,
Figura Nº 15: Valores máximos por categoría y mes
Lo contrario a lo anterior es mostrar los valores mínimos de cada mes y categoría.
Figura Nº 16: Valores mínimos por categoría y mes
Mostrar Valores como
Ya se ha mencionado que las tablas dinámicas son muy útiles para resumir información, pero también ayudan a
mostrar los valores como, para ello se accede haciendo clic en el cuadrante de valores configuración de campo
de valor.
Figura Nº 17: Configuración de campo de valor – Mostrar valores como
Máx. de Cantidad Mes
Categoría Enero Febrero Marzo Total general
Compra 235 240 260 260
Entretenimiento 100 125 120 125
Hogar 175 225 200 225
Transporte 74 115 90 115
Total general 235 240 260 260
Mín. de Cantidad Mes
Categoría Enero Febrero Marzo Total general
Compra 235 240 260 235
Entretenimiento 100 125 120 100
Hogar 175 225 200 175
Transporte 74 115 90 74
Total general 74 115 90 74
14.5. Personalizar una tabla dinámica
Si desea personalizar una tabla dinámica puede usar la opción Herramientas de tabla dinámica que se activará en
la medida que seleccione la tabla dinámica, la cual aparecerá en la cinta de opciones, a continuación, se muestra
la cinta de opciones Diseño.
Figura Nº 21: Cinta de opciones Herramientas de tabla dinámica
La pestaña diseño se divide en tres grupos:
Subtotales. Muestra u oculta los subtotales.
Totales generales****. Muestra u oculta los totales generales.
Figura Nº 22: Totales Generales: Desactivado filas y columnas
Diseño de Informe****. Ajusta el diseño del informe de tabla dinámica
Figura Nº 23: Diseño de informe: Formato compacto
Filas en blanco****. Enfatiza los grupos agregando una línea en blanco entre cada elemento agrupado.
Suma de Cantidad Mes Categoría Enero Febrero Marzo Compra 31,97% 32,65% 35,37% Entretenimiento 28,99% 36,23% 34,78% Hogar 29,17% 37,50% 33,33% Transporte 26,52% 41,22% 32,26%
Suma de Cantidad Etiquetas de columna Etiquetas de fila Enero Febrero Marzo Total general Compra 31,97% 32,65% 35,37% 100,00% Entretenimiento 28,99% 36,23% 34,78% 100,00% Hogar 29,17% 37,50% 33,33% 100,00% Transporte 26,52% 41,22% 32,26% 100,00% Total general 29,81% 35,99% 34,20% 100,00%
- Opciones de Estilo de tabla dinámica
Encabezado de fila****. Muestra el formato especial para la primera fila.
Encabezado de columna****. Muestra el formato especial para la primera columna.
Filas con bandas****. Agrega líneas a las filas de la tabla.
Columnas con bandas****. Agrega líneas a las columnas de la tabla.
- Estilos de tabla dinámica
Puede usar esta opción para aplicar estilos de color y líneas a sus tablas dinámicas según su necesidad de diseño o presentación.
Figura Nº 24: Estilo de tabla dinámica - Claro 16
Eliminar una tabla dinámica
Cuando ya no necesite una tabla dinámica, seleccione toda la tabla dinámica y pulse la tecla suprimir para
eliminarla totalmente.
Puede eliminar una tabla dinámica haciendo uso de las opciones de la cinta de opciones herramientas de tablas
dinámicas , pestaña Analizar y dentro de ella seleccionar acciones , toda la tabla dinámica.
Figura Nº 25: Herramientas de tabla dinámica - Analizar – Toda la tabla dinámica
Una vez realizado esta acción solo bastará que se presione la tecla suprimir y la tabla quedará eliminada.
Suma de Cantidad Etiquetas de columna Etiquetas de fila Enero Febrero Marzo Total general Compra 31,97% 32,65% 35,37% 100,00% Entretenimiento 28,99% 36,23% 34,78% 100,00% Hogar 29,17% 37,50% 33,33% 100,00% Transporte 26,52% 41,22% 32,26% 100,00% Total general 29,81% 35,99% 34,20% 100,00%
14.6.2. Elementos de segmentación de datos
Figura Nº 2: Elementos de segmentación de datos
14.6.3. Crear segmentación de datos en tabla dinámica
Para crear una segmentación a una tabla dinámica, se tomará como ejemplo la tabla de datos, donde se creará en
un inicio la tabla dinámica:
Un encabezado de segmentación de datos indica la categoría de los elementos que contiene.
Un botón Borrar filtro quita el filtro mediante la selección de todos los elementos de la segmentación de datos.
Un botón de filtrado que no está activado indica que el elemento no está incluido en el filtro.
Un botón de filtrado que está activado indica que el elemento está incluido en el filtro.
Los controles para mover y cambiar el tamaño de los bordes permiten cambiar el tamaño y la ubicación de la segmentación de datos.
Una barra de desplazamiento permite desplazarse cuando hay más datos de los que pueden verse actualmente en la segmentación de datos.
Fecha Categoría Cantidad 01/06/2017 Transporte S/. 172, 01/06/2017 Compra S/. 50, 01/06/2017 Hogar S/. 159, 01/06/2017 Entretenimiento S/. 197, 23/05/2017 Transporte S/. 87, 23/05/2017 Compra S/. 150, 23/05/2017 Hogar S/. 125, 23/05/2017 Entretenimiento S/. 117, 15/04/2017 Transporte S/. 113, 15/04/2017 Compra S/. 61, 15/04/2017 Hogar S/. 132, 15/04/2017 Entretenimiento S/. 192, 25/03/2017 Transporte S/. 159, 25/03/2017 Compra S/. 80, 25/03/2017 Hogar S/. 116, 25/03/2017 Entretenimiento S/. 95, 14/02/2017 Transporte S/. 79, 14/02/2017 Compra S/. 111, 14/02/2017 Hogar S/. 128, 14/02/2017 Entretenimiento S/. 132, 22/01/2017 Transporte S/. 193, 22/01/2017 Compra S/. 78, 22/01/2017 Hogar S/. 116, 22/01/2017 Entretenimiento S/. 129,
Figura Nº 3: Tabla de datos para generar la segmentación
- Hacer clic en cualquier lugar de la tabla dinámica para la cual desea crear la segmentación de datos.
Figura Nº 4: Selección de una parte de la tabla dinámica
- En la cinta de opciones Herramientas de tabla dinámicas, pestaña Analizar, haga clic en Insertar segmentación
de datos.
Figura Nº 5: Herramientas de tablas dinámica - Analizar
- Aparecerá un cuadro de dialogo, donde se seleccionarán los campos a insertar y hacemos clic en Aceptar.
Figura Nº 6: Selección de campos a segmentar
- Una vez activado los campos, se mostrará la tabla dinámica con sus respectivos botones de segmentación.
Suma de Cantidad Meses Categoría ene feb mar abr may jun Total general Compra 78 111 80 61 150 50 530 Entretenimiento 129 132 95 192 117 197 862 Hogar 116 128 116 132 125 159 776 Transporte 193 79 159 113 87 172 803 Total general 516 450 450 498 479 578 2971
- Seleccionar el campo segmentado.
Figura Nº 9: Campo segmentado
- En la cinta de opciones se activará una pestaña de nombre Opciones.
Figura Nº 10: Opciones para la segmentación
En ella se podrá aplicar lo siguiente:
- Nombre de la segmentación de datos.
Figura Nº 11: Nombre de la segmentación
- Estilos de segmentación de colores
Figura Nº 12: Estilos de segmentación
Figura Nº 13: Organizar elementos de la segmentación
Figura Nº 14: Botones de la segmentación
Figura Nº 15: Tamaño de botones de la segmentación
Para el ejemplo vamos a aplicar Estilos de segmentación de colores, quedando la segmentación de esta manera.
Figura Nº 16 : Estilos de segmentación de colores
14.6.5. Crear escalas de tiempo en campos tipo fecha
Existen situaciones donde la información que se resume está en datos tipo fecha, por ello la segmentación de datos
proporciona una opción para crear escalas de tiempo, para lo cual debe realizar lo siguiente:
Figura Nº 20: Escala de tiempo para el campo fecha
- Luego de insertar la escala de tiempo, esta ofrece la posibilidad de alternar entre años, trimestres, meses y días,
para ello debe hacer clic en el nivel de tiempo y se desplegará las escalas de tiempo.
Figura Nº 21: Escalas de tiempo
- Finalmente puede resumir la tabla por la escala de tiempo de su preferencia.
- Resumen de gastos para el año 2017
Figura Nº 22: Resumen por año
- Resumen de gastos para el primer trimestre del 2017
Suma de Cantidad Meses Categoría ene Total general Compra 78 78 Entretenimiento 129 129 Hogar 116 116 Transporte 193 193 Total general 516 516
Fecha Todos los períodos MESES
JUN JUL AGO SEP OCT NOV DIC
2017
Fecha Todos los períodos MESES JUN JUL AGO SEP OCT NOV DIC
2017
Suma de Cantidad Meses Categoría ene feb mar abr may jun Total general Compra 78 111 80 61 150 50 530 Entretenimiento 129 132 95 192 117 197 862 Hogar 116 128 116 132 125 159 776 Transporte 193 79 159 113 87 172 803 Total general 516 450 450 498 479 578 2971
Figura Nº 23: Resumen por trimestre
- Resumen de gastos para el mes de enero del 2017
Figura Nº 24: Resumen por mes
- Resumen de gastos para el 14 de febrero del 2017
Figura Nº 25: Resumen por día
Suma de Cantidad Meses Categoría ene feb mar Total general Compra 78 111 80 269 Entretenimiento 129 132 95 356 Hogar 116 128 116 360 Transporte 193 79 159 431 Total general 516 450 450 1416
Fecha 1T 2017 TRIMESTRES
1T 2T 3T 4T
2017
Suma de Cantidad Meses Categoría ene Total general Compra 78 78 Entretenimiento 129 129 Hogar 116 116 Transporte 193 193 Total general 516 516
Suma de Cantidad Meses Categoría feb Total general Compra 111 111 Entretenimiento 132 132 Hogar 128 128 Transporte 79 79 Total general 450 450
