computacion avanzada,excel, Resúmenes de Computación aplicada

¡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 14 Módulo de Microsoft Excel 2 | 27 14. 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 74,00S/. Enero Compra 235,00S/. Enero Hogar 175,00S/. Enero Entretenimiento 100,00S/. Febrero Transporte 115,00S/. Febrero Compra 240,00S/. Febrero Hogar 225,00S/. Febrero Entretenimiento 15,00S/. Marzo Transporte 90,00S/. Marzo Compra 260,00S/. Marzo Hogar 200,00S/. Marzo Entretenimiento 120,00S/. 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 584S/. 705S/. 670S/. 1.959S/. Módulo de Microsoft Excel 5 | 27 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 Módulo de Microsoft Excel 6 | 27 de no mezclar tipos de datos en los campos de valor. 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 resumen es la SUMA de cantidad agrupados por categoría y mes. Figura Nº 10: Resumen de datos con la función SUMA Si lo que se desea es CONTAR la cantidad de veces de una categoría, podemos resumirlo así. Figura Nº 11: Resumen de datos con la función CONTAR También podemos aplicar Filtros, si lo que deseamos es mostrar la información de acuerdo a una condición, para el caso se muestra la suma de cantidades para el mes de enero. Figura Nº 12: Resumen de datos con Filtros 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 584 705 670 1959 Cuenta de Categoría Categoría Total Compra 3 Entretenimiento 3 Hogar 3 Transporte 3 Total general 12 Mes Enero Suma de Cantidad Categoría Total Compra 235 Entretenimiento 100 Hogar 175 Transporte 74 Total general 584 Módulo de Microsoft Excel 7 | 27 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,25 Módulo de Microsoft Excel 10 | 27 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: • Diseño 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% Módulo de Microsoft Excel 11 | 27 • 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% Módulo de Microsoft Excel 12 | 27 14.6. Segmentación de datos en tablas dinámicas 14.6.1. ¿Qué es la segmentación de datos? Si utiliza un filtro normal de informe de tabla dinámica para filtrar varios elementos, el filtro sólo indica que se han filtrado varios elementos y tiene que abrir una lista desplegable para buscar los detalles del filtrado. Sin embargo, una segmentación de datos etiqueta claramente el filtro que se aplica y proporciona detalles para que pueda comprender fácilmente los datos que se muestran en el informe filtrado de la tabla dinámica. Definición Las segmentaciones de datos son componentes de filtrado fáciles de usar que contienen un conjunto de botones que permiten filtrar los datos rápidamente en un informe de tabla dinámica, sin tener que abrir listas desplegables para buscar los elementos que desea filtrar. La segmentación de datos proporciona botones en los que puede hacer clic para filtrar datos de una tabla o una tabla dinámica. Además del filtrado rápido, las segmentaciones de datos también indican el estado de filtrado actual, de este modo es fácil entender qué se muestra exactamente en una tabla dinámica filtrada. Figura Nº 1: Tabla dinámica con sus campos segmentados 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 Categoría Compra Entretenimiento Hogar Transporte Cantidad S/. 50,00 S/. 61,00 S/. 78,00 S/. 79,00 S/. 80,00 S/. 87,00 S/. 95,00 S/. 111,00 Meses ene feb mar abr may jun jul ago Módulo de Microsoft Excel 15 | 27 Figura Nº 7: Tabla dinámica con su respectiva segmentación de datos 5. A continuación, podrá hacer clic en los botones de la segmentación, el ejemplo muestra la suma de gastos por categoría correspondiente al mes de enero. Figura Nº 8: Segmentación de datos por categoría y mes 14.6.4. Personalizar elementos de la segmentación de datos Es posible que cuando se esté trabajando con la segmentación se tenga la necesidad de personalizar los elementos de la segmentación, debiendo seguir las siguientes acciones: Módulo de Microsoft Excel 16 | 27 1. Seleccionar el campo segmentado. Figura Nº 9: Campo segmentado 2. 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 • Organizar Figura Nº 13: Organizar elementos de la segmentación Módulo de Microsoft Excel 17 | 27 • Botones Figura Nº 14: Botones de la segmentación • Tamaño 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: Módulo de Microsoft Excel 20 | 27 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 TRIMESTRES1T 2017 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 Módulo de Microsoft Excel 21 | 27 14.6.6. Personalizar elementos de la segmentación de datos tipo fecha Es posible que cuando se esté trabajando con la segmentación de escala de tiempo se tenga la necesidad de personalizar los elementos de la escala, debiendo seguir las siguientes acciones: 1. Seleccionar el campo segmentado. Figura Nº 26: Selección de campo segmentado 3. En la cinta de opciones se activará una pestaña de nombre Opciones. Figura Nº 27: Selección de opciones de segmentación En ella se podrá aplicar lo siguiente: • Nombre de la escala de tiempo Figura Nº 28: Escala de tiempo • Estilos de escala de tiempo Figura Nº 29: Estilos de escala de tiempo Fecha MESESene 2017 ENE FEB MAR ABR MAY JUN JUL AGO 2017 Módulo de Microsoft Excel 22 | 27 • Organizar Figura Nº 30: Organizar elementos de escala • Botones Figura Nº 31: Botones elementos de escala • Tamaño Figura Nº 32: Tamaño elementos de escala Para el ejemplo vamos a aplicar Estilos de escala de tiempo, quedando la segmentación de esta manera. Figura Nº 33: Estilos de escala de tiempo Suma de Cantidad Meses Categoría mar Total general Compra 80 80 Entretenimiento 95 95 Hogar 116 116 Transporte 159 159 Total general 450 450 Módulo de Microsoft Excel 25 | 27 7. Listar los elementos de lo que se compone una Segmentación de Datos: a. b. c. d. e. f. 8. Indicar a qué tipos de datos se puede aplicar una Segmentación de Datos: a. b. c. d. PARTE III: Caso Práctico En el mismo archivo, insertar hojas y resolver según lo indicado en cada hoja de cálculo: • Hoja Ejercicio 01: Diseñar la siguiente tabla de datos, para luego dar solución a las premisas que se resolverán mediante Tablas Dinámicas: 1. Promedio de edad de los empleados por provincia y ocupación. 2. Mostrar la cantidad de empleados de cada ocupación que hay por provincias. 3. Suma de ingresos por ocupación y por provincia. 4. Suma de ingresos por provincias con su respectivos distritos y ocupaciones. 5. Suma de ingresos filtrado por distrito de una provincia determinada. 6. Suma de ingresos por ocupación y distrito, debiendo ser mostrado de manera porcentual respecto al total general. 7. Resumen de ingresos por distrito, de manera porcentual respecto al total general. 8. Cantidad de trabajadores por edad, de manera porcentual respecto al total general. Módulo de Microsoft Excel 26 | 27 • Hoja Ejercicio 02: Crear una tabla dinámica que resuma los de ingresos por ocupación y por provincia. Luego aplicar la segmentación de datos para: 1. Distrito 2. Provincia 3. Ocupación 4. Ingresos 5. Escala de tiempo para el campo Fecha CENTRO DE INFORMATICA Y SISTEMAS 6