GUIA DE AUTOAPRENDIZAJE #8 EXCEL 2007 Tema: Resumen:
TABLAS DINAMICAS Construcción de Tablas Dinámicas.
Acción # Haga lo que se indica Destaca el resultado de una acción. Lea y observe. <Tecla> Nombre de tecla <Tecla1>+<Tecla2> Presione tecla1 y sin soltar presione tecla2 Arrastrar Dar clic sobre un elemento, y sin soltar botón, mover Mouse hacia otra posición.
Introducción Una tabla dinámica es una hoja de cálculo interactiva que resume rápidamente grandes cantidades de datos usando el formato y los métodos de cálculo que usted elija. Una tabla dinámica proporciona una manera sencilla de mostrar y analizar información resumida acerca de los datos ya creados en EXCEL o en otra aplicación. Preparación de los datos A continuación se muestran los datos correspondientes a transacciones realizadas por una empresa dedicada a impartir cursos de capacitación en distintas comunas del país. Los datos muestran el CODIGO CURSO como el código identificador de cada curso. Se especifica el MES en el cual se hizo el curso, el código de la COMUNA donde se dictó, el NRO ALUMNOS inscritos, el costo de la matricula, señalado como COSTO POR ALUMNO y los INGRESOS BRUTOS obtenidos.
◄ ACCION 1 ►
Transcriba u obtenga los datos mostrados en la imagen de arriba.
Ejemplos de Tablas Dinámicas Observe la siguiente imagen (IMAGEN 1). Ella muestra el total de INGRESOS BRUTOS por COMUNA Efectivamente, se puede comprobar que para la COMUNA 1, se han sumado los valores 253356, 186585, 286335 y 314350 (filas 2 a 5 de la imagen de arriba). De forma similar ocurre para las siguientes comunas. Esta imagen corresponde a una tabla dinámica obtenida desde los datos anteriores que calcula la suma de los ingresos brutos por comuna.
Material Docente de los profesores Eduardo y Leopoldo Meza Honorato. Prohibida su reproducción sin la autorización de sus autores
2 La imagen inferior (IMAGEN 2), muestra los mismos resultados anteriores, esta vez desagregados por mes.
Observe que, bajo la COLUMNA TOTAL GENERAL, aparecen los mismos resultados parciales por cada comuna. Y que la FILA TOTAL GENERAL, nos permite ver los resultados mensuales. Es la misma tabla dinámica anterior a la cual se le han agregado los meses. Finalmente, la imagen inferior (IMAGEN 3) muestra la misma operación anterior, pero solo referido al CODIGO CURSO A100
Es una nueva modificación de la tabla dinámica, esta vez agregando una suerte de filtro para ver los datos referidos a un solo ítem. Fundamentos de Tablas Dinámicas Una tabla dinámica se construye en base a los CAMPOS que se muestran en los datos (Encabezados de columnas). Los campos pueden ser colocados en alguna de las siguientes AREAS de la TABLA DIBNAMICA: ROTULOS DE FILA (para definir contenido de las filas de la tabla dinámica), ROTULOS DE COLUMNA (para definir contenido de las columnas de la tabla dinámica) VALORES o DATOS (para definir la operación a realizar sobre uno o más campos de la tabla dinámica) PÁGINA o FILTRO DE INFORME (para establecer un filtro general sobre la tabla dinámica). La siguiente figura muestra la estructura general de una tabla dinámica.
Rótulos de página o filtro de Informe
Rótulos de fila ó área fila
Rótulos de columnas ó área columnas
Valores o Datos
3 Creación de una tabla dinámica. Crearemos una tabla dinámica para obtener los totales de ingresos brutos por columna (Imagen 1).
◄ ACCION 2 ►
Seleccione alguna celda que contenga datos (por ejemplo la celda B4)...
◄ ACCION 3 ►
Desde la FICHA INSERTAR, en el GRUPO TABLAS, de clic sobre el botón Insertar Tabla dinámica
Aparece la ventana Crear tabla dinámica... Esta ventana le permite especificar el origen de los datos y la ubicación de la tabla dinámica... Bajo el titulo „Seleccione los datos que desea analizar‟, en la opción „Seleccione una tabla o rango‟, se especifica el rango donde se encuentran los datos… Puesto que inició este proceso detenido dentro de los datos, Excel determina la región donde estos se encuentran y aparece el rango de los datos. Bajo el titulo „Elija dónde desea colocar el informa de tabla dinámica‟, se especifica donde debe aparecer la tabla dinámica. Esto puede ser: en una nueva hoja o en alguna ya existente…
Crearemos la tabla en una nueva hoja de cálculo…
◄ ACCION 4 ►
De clic en el botón Aceptar
Aparece una nueva hoja de cálculo, similar a la figura de más abajo. Se destacan los siguientes elementos: A la izquierda, dentro de la hoja de cálculo, la TABLA DINAMICA VACIA. (Esta es la tabla dinámica) A la derecha, el PANEL LISTA DE CAMPOS DE TABLA DINAMICA. (Estos son los elementos necesarios para diseñar la tabla dinámica). Este panel aparece cuando la tabla dinámica esta seleccionada o el puntero está detenido sobre algún elemento de la tabla dinámica.
Campos obtenidos desde los datos.
Tabla dinámica vacía
Área Filtro o página de la tabla
Área de las columnas de la tabla dinámica
Área valores de la tabla dinámica
Área filas de la tabla dinámica
Para colocar los campos en las distintas áreas de la tabla dinámica, desde el panel LISTA DE CAMPOS DE TABLA DINAMICA, se arrastran los campos hasta el área deseada.
4 (Recuerde, arrastrar significa, dar un clic sobre el elemento, luego, SIN SOLTAR el botón del mouse, llevar el puntero hacia la posición de destino)
Definición del Área FILAS de la tabla dinámica Puesto que se necesitan las COMUNAS en el área fila de la tabla dinámica…
◄ ACCION 5 ►
Arrastre el campo COMUNA hasta el área Rótulos de fila
Verá como, en la tabla dinámica, aparecen los rótulos de la fila. De la siguiente forma:
Definición del Área Valores de la tabla dinámica Seguidamente, se necesita que, por cada COMUNA, se sumen los valores correspondientes a INGRESOS BRUTOS. Puesto que se trata de una operación (SUMA) que afecta a un campo (INGRESOS BRUTOS), se debe arrastrar este campo hacia el área valores de la tabla dinámica
◄ ACCION 6 ►
Arrastre el campo INGRESOS BRUTOS hacia el área Valores
Ahora la tabla dinámica incorpora los totales por Ingresos Brutos por Comuna… Con la operación anterior hemos concluido la estructura operativa de la tabla dinámica… Veremos cómo mejorar su apariencia Modificar la apariencia de la Tabla Dinámica La tabla tiene una apariencia similar a la figura inferior…
Será más descriptivo si reemplazamos la frase „Rótulos de fila‟ y „Suma de INGRESOS BRUTOS‟ por títulos que describan más adecuadamente los elementos que aquí se representan. Evidentemente, deseamos que en vez de „Rotulo de fila‟ aparezca „Comuna‟ y, en reemplazo de „Suma de INGRESOS BRUTOS‟ aparezca „Total‟
5
◄ ACCION 7 ►
En la Tabla Dinámica, seleccione la celda que contiene el titulo „Rótulos de Fila‟
◄ ACCION 8 ►
Desde la barra de formulas, reemplace por la palabra Comunas.
Del mismo modo…
◄ ACCION 9 ►
En la Tabla Dinámica, seleccione la celda que contiene el titulo „Suma de INGRESOS BRUTOS‟…
◄ ACCION 10 ►
Desde la Barra de formulas, reemplace por la palabra: Total
Establecer el formato de los números. Puestos que los datos numéricos corresponden a valores en pesos, será más adecuado que estos aparezcan en formato moneda… Esto se hace desde el panel LISTA DE CAMPOS DE TABLA DINAMICA…
◄ ACCION 11 ►
Desde el Área Valores, despliegue el menú correspondiente al campo Total
◄ ACCION 12 ►
Seleccione la opción „Configuración de campo de valor‟
Aparece la ventana „Configuración de campo de valor‟ Desde esta ventana es posible: En el cuadro „Nombre personalizado‟, dar un nombre personalizado al campo. En la etiqueta RESUMIR POR En el cuadro „Resumir campo de valor por‟, se especifica o modifica la operación que se realiza (en nuestro caso tenemos la operación SUMA sobre el campo Ingresos brutos. Mas abajo, en el botón „Formato de número‟ se establece el formato con el cual aparecerán los números en el área valor de la tabla dinámica…
◄ ACCION 13 ►
De clic en el botón Formato de número
Aparece la ventana „Formato de celdas‟ que le permitirá modificar el formato de los números…
◄ ACCION 14 ►
Seleccione formato Moneda, sin decimales.
◄ ACCION 15 ►
De clic en el botón Aceptar, para concluir el formato de los números…
◄ ACCION 16 ►
De clic en el botón Aceptar para concluir la configuración del campo valor.
Ahora los números aparecen con formato moneda sin decimales…
Agregar columnas a una Tabla Dinámica Habrá notado que la característica más notable de esta herramienta es la posibilidad de ir viendo los resultados en la medida que se construye la tabla. A continuación desagregaremos los totales por mes para construir una tabla similar a la que se muestra en la Imagen 2. Haremos esta operación sobre la misma tabla dinámica que estamos trabajando.
6 Esto se hace desde el panel LISTA DE CAMPOS DE TABLA DINAMICA…
◄ ACCION 17 ►
Arrastre el campo MES hasta el área Rotulo de columna
Inmediatamente, la tabla dinámica se actualiza agregando el campo MES mostrando el detalle mensual por comuna… Similar a la imagen inferior.
De la misma forma anterior, reemplazaremos la frase „Rótulos de columna‟ por „Meses‟
◄ ACCION 18 ►
En la Tabla Dinámica, seleccione la celda que contiene el titulo „Rótulos de Columna‟
◄ ACCION 19 ►
Desde la barra de formulas, reemplace por la palabra: Meses.
Forma de visualizar los datos Los datos que usamos corresponden a cursos realizados. Tal como están la tabla no es posible apreciar el impacto que cada curso tiene sobre los ingresos generales. Veremos tres formas de visualizar el detalle de cada curso. 1. Como un detalle de las comunas. 2. Como un detalle de los meses 3. Como un filtro aplicable a la Tabla Dinámica Agregar más filas al área Filas Para agregar más campos al área de filas, simplemente se agregan a esta área… Esto se hace desde el panel LISTA DE CAMPOS DE TABLA DINAMICA…
◄ ACCION 20 ►
Arrastre el campo CODIGO CURSO a continuación del campo COMUNA en el área Rotulo de Fila
Inmediatamente, la tabla dinámica se actualiza agregando el campo CODIGO CURSO. Ahora se muestra por cada comuna el detalle de los cursos realizados… Similar a la imagen inferior.
Al dar clic en los botones (-) que aparecen junto al numero de cada comuna, es posible ocultar el detalle de los cursos.
7 Cambiar un campo desde el área fila al área columna Para desplazar un campo de un área hacia otra, basta arrastrarlo desde el área donde se encuentra hasta el área de destino. Esto se hace desde el panel LISTA DE CAMPOS DE TABLA DINAMICA…
◄ ACCION 21 ►
Arrastre el campo CODIGO CURSO desde el área Rótulos de Fila, a continuación del campo MES, en el área Rotulo de Columna
Inmediatamente, la tabla dinámica se actualiza agregando el campo CODIGO CURSO en las columnas. Ahora se muestra por cada mes el detalle de los cursos realizados… Similar a la imagen inferior.
Al dar clic en los botones (-) que aparecen junto a cada mes, es posible ocultar el detalle de los cursos. Crear un campo de filtro También es posible tener uno varios campos de filtro, que muestren una tabla en base a una parte de los datos. Esto se hace desde el panel LISTA DE CAMPOS DE TABLA DINAMICA…
◄ ACCION 22 ►
Arrastre el campo CODIGO CURSO desde el área Rótulos de Columna, al área Filtro de Informe.
Inmediatamente, la tabla dinámica se actualiza. Aparece en la parte superior una lista desplegable con todos los cursos dictados que permite establecer un filtro sobre los datos a procesar… Similar a la imagen inferior.
Al seleccionar una curso desde esta lista desplegable, la tabla dinámica se actualiza mostrando solo los datos correspondientes a al curso seleccionado.
◄ ACCION 23 ►
Desde la lista desplegable, seleccione el curso: A100
Inmediatamente, la tabla dinámica se actualiza mostrando todos los datos en base al curso seleccionado. Quitar el filtro. Para quitar un filtro, simplemente seleccione, desde la lista desplegable, la opción (Todas)
◄ ACCION 24 ►
Desde la lista desplegable, seleccione la opción: (Todas)
Inmediatamente, la tabla dinámica se actualiza mostrando todos los datos.
8 OBSERVE QUE JUNTO AL ROTULO DE FILA COMUNAS Y JUNTO AL ROTULO DE COLUMNA MESES TAMBIÉN APARECEN LISTAS DESPLEGABLES. DESDE ELLAS TAMBIÉN ES POSIBLE FILTRAR LOS DATOS QUE SE DESEAN VER. Por ejemplo, suponga que solo interesa ver las comunas 1, 4 y 8
◄ ACCION 25 ►
En la tabla dinámica, desde el rotulo de la fila COMUNAS, despliegue la lista…
◄ ACCION 26 ►
Similar a un filtro, deje seleccionadas solo las columnas 1, 4 y 8.
◄ ACCION 27 ►
De clic en el botón Aceptar
Ahora la tabla dinámica muestra los datos referidos a las comunas seleccionadas. Vista porcentual Puesto que los datos que se muestran en la tabla dinámica, corresponden a valores parciales de los totales mostrados en la ultima columna o fila de la tabla dinámica. Se pueden visualizar estos como porcentajes del total. Veamos la forma de hacer esto. Esto se hace desde el panel LISTA DE CAMPOS DE TABLA DINAMICA…
◄ ACCION 28 ►
Desde el Área Valores, despliegue el menú correspondiente al campo Total
◄ ACCION 29 ►
Seleccione la opción „Configuración de campo de valor‟
Aparece la ventana „Configuración de campo de valor‟
◄ ACCION 30 ►
De clic en la etiqueta MOSTRAR VALORES COMO…
◄ ACCION 31 ►
Despliegue el cuadro ‘Mostrar valores como’, seleccione la opción ‘% de la fila’
◄ ACCION 32 ►
De clic en el botón Aceptar
Los datos aparecen como porcentajes. Similar a la imagen inferior.
OBSERVE QUE EL 100% APARECE EN LA COLUMNA DEL TOTAL GENERAL. ESTO SIGNIFICA QUE LOS PORCENTAJES ESTÁN REFERIDOS A CADA UNA DE LAS COMUNAS. Es decir, el 17,93% que aparece bajo el mes de enero, referido a la comuna 1, debe interpretarse como sigue: “Del los ingresos brutos recaudado por cursos realizados en la comuna 1, el 17,93% fue recaudado en el mes de enero” Veámoslo de otra manera. Desde el panel LISTA DE CAMPOS DE TABLA DINAMICA…
◄ ACCION 33 ►
Desde el Área Valores, despliegue el menú correspondiente al campo Total
◄ ACCION 34 ►
Seleccione la opción „Configuración de campo de valor‟
9 Aparece la ventana „Configuración de campo de valor‟
◄ ACCION 35 ►
De clic en la etiqueta MOSTRAR VALORES COMO…
◄ ACCION 36 ►
Despliegue el cuadro ‘Mostrar valores como’, seleccione la opción ‘% de la columna’
◄ ACCION 37 ►
De clic en el botón Aceptar
Los datos aparecen como porcentajes. Similar a la imagen inferior.
OBSERVE QUE EL 100% APARECE EN LA FILA DEL TOTAL GENERAL. ESTO SIGNIFICA QUE LOS PORCENTAJES ESTÁN REFERIDOS A CADA UNO DE LOS MESES. Es decir: “Del los ingresos brutos recaudados durante el mes de Enero, el 27,11% se obtuvo en la comuna 1” Del mismo modo pueden establecerse porcentajes sobre el total general. Desde el panel LISTA DE CAMPOS DE TABLA DINAMICA…
◄ ACCION 38 ►
Desde el Área Valores, despliegue el menú correspondiente al campo Total
◄ ACCION 39 ►
Seleccione la opción „Configuración de campo de valor‟
Aparece la ventana „Configuración de campo de valor‟
◄ ACCION 40 ►
De clic en la etiqueta MOSTRAR VALORES COMO…
◄ ACCION 41 ►
Despliegue el cuadro ‘Mostrar valores como’, seleccione la opción ‘% del total’
◄ ACCION 42 ►
De clic en el botón Aceptar
Los datos aparecen como porcentajes. Similar a la imagen inferior.
En este caso… “Del total de ingresos brutos recaudados en las comunas 1, 4 y 8, durante los meses de Enero, Marzo, Abril Mayo y Junio, el 7,46% se obtuvo en la comuna 1 durante el mes de Enero” Finalmente Esta guía provee una visión general sobre la forma de operar de las tablas dinámicas. La utilidad de estas reside en su capacidad de resumir grandes volúmenes de datos mostrando inmediatamente los resultados. Otras características de las tablas dinámicas pueden ser aprendidas revisando las opciones disponibles en la cinta de opciones. No deje de revisarlas y aprender por si mismo.