MICROSOFT OFFICE EXCEL AVANZADO
Unidad Temรกtica 3: Tablas y grรกficos dinรกmicos. Macros M
Servicio Andaluz de Salud Consejería de Salud Area de Gestión Sanitaria Norte de Cádiz
Curso Excel Avanzado
Introducción La potencia de Excel en el tratamiento y uso de rangos y listas de datos se complementa con las herramientas que nos proporciona para extraer información de esos datos. Tablas y gráficos dinámicos, búsqueda de objetivos, escenarios, Solver, estadísticas,... son herramientas que nos permiten extraer conclusiones de nuestros grandes rangos de datos.
En Tablas dinámicas aprenderemos a crear y modificar tablas dinámicas a partir de rangos de datos. En Gráficos dinámicos conoceremos cómo crear gráficos dinámicos a partir de una tabla dinámica o de un rango de datos. Por último, en Análisis de datos conoceremos cómo usar las herramientas de búsqueda de objetivos, creación de escenarios, Solver y análisis estadístico.
UT3: Tablas y gráficos dinámicos. Macros.
2
Servicio Andaluz de Salud Consejería de Salud Area de Gestión Sanitaria Norte de Cádiz
Curso Excel Avanzado
Tablas Dinámicas. ¿Qué es una Tabla Dinámica? Una tabla dinámica en Excel es una serie de datos agrupados en forma de resumen que enfatizan aspectos concretos de la información global de la que partimos.
Crear una Tabla Dinámica. Para crear una tabla dinámica, partimos de un rango de datos y usamos el menú Datos >> Informe de tablas y gráficos dinámicos... Esto nos abrirá el Asistente para tablas y gráficos dinámicos.
Veamos un ejemplo El procedimiento básico a seguir para crear una tabla dinámica es el siguiente: Puedes descargar el archivo que hay en esta sección de la UT3 para practicar de forma simultánea.
UT3: Tablas y gráficos dinámicos. Macros.
3
Servicio Andaluz de Salud Consejería de Salud Area de Gestión Sanitaria Norte de Cádiz
UT3: Tablas y gráficos dinámicos
Curso Excel Avanzado
4
Servicio Andaluz de Salud Consejería de Salud Area de Gestión Sanitaria Norte de Cádiz
UT3: Tablas y gráficos dinámicos
Curso Excel Avanzado
5
Servicio Andaluz de Salud Consejería de Salud Area de Gestión Sanitaria Norte de Cádiz
UT3: Tablas y gráficos dinámicos
Curso Excel Avanzado
6
Servicio Andaluz de Salud Consejería de Salud Area de Gestión Sanitaria Norte de Cádiz
UT3: Tablas y gráficos dinámicos
Curso Excel Avanzado
7
Servicio Andaluz de Salud Consejería de Salud Area de Gestión Sanitaria Norte de Cádiz
UT3: Tablas y gráficos dinámicos
Curso Excel Avanzado
8
Servicio Andaluz de Salud Consejería de Salud Area de Gestión Sanitaria Norte de Cádiz
UT3: Tablas y gráficos dinámicos
Curso Excel Avanzado
9
Servicio Andaluz de Salud Consejería de Salud Area de Gestión Sanitaria Norte de Cádiz
Curso Excel Avanzado
A Navegar... El enlace siguiente nos lleva a un apartado de Microsoft Office Online en el que se nos explica lo más básico de las tablas dinámicas. Haz clic aquí para abrir ir a la página
Partes de una Tabla Dinámica Es importante diferenciar con claridad las distintas partes de una tabla dinámica:
UT3: Tablas y gráficos dinámicos
10
Servicio Andaluz de Salud Consejería de Salud Area de Gestión Sanitaria Norte de Cádiz
Curso Excel Avanzado
A tener en cuenta … Es importante recordar los cuatro tipos de campos que tenemos en una tabla dinámica: de página, fila, columna y datos. La Ayuda de Excel nos ofrece bastante información sobre las tablas dinámicas. En concreto, en la Tabla de contenidos de la Ayuda, podemos ir a Gráficos >> Informes de tabla dinámica y de gráfico dinámico
Modificar una tabla dinámica. Para trabajar con una tabla dinámica después de crearla disponemos de una serie de herramientas disponibles desde la barra de herramientas "Tabla dinámica" o bien desde el menú contextual (botón derecho): Es importante seleccionar previamente el campo o elemento sobre el que queremos realizar las acciones, ya que, cuando hagamos clic sobre el botón derecho o bien usemos la barra de herramientas, las opciones disponibles harán referencia a lo que tengamos seleccionado.
UT3: Tablas y gráficos dinámicos
11
Servicio Andaluz de Salud Consejería de Salud Area de Gestión Sanitaria Norte de Cádiz
Curso Excel Avanzado
Podemos ampliar ... El enlace siguiente nos lleva a un apartado de Microsoft Office Online en el que podemos ver y prácticas acciones básicas de modificación y trabajo con tablas dinámicas. Haz clic aquí para abrir ir a la página
A estudiar: 1.
¿Para qué sirven las opciones de "Agrupar y mostrar detalles"? Estúdialo mediante un ejemplo de uso a partir del archivo ejemplo_tabla_dinamica.xls que has descargado.
2.
Investiga las posibilidades de configuración de la tabla que nos proporciona el cuadro "Opciones de tabla dinámica":
Por ejemplo, ¿cómo conseguiremos que, en aquellas celdas donde no hay datos, aparezca un mensaje determinado? A partir del archivo ejemplo tabla_dinamica.xls intenta crear una tabla dinámica como la siguiente:
UT3: Tablas y gráficos dinámicos
12
Servicio Andaluz de Salud Consejería de Salud Area de Gestión Sanitaria Norte de Cádiz
Curso Excel Avanzado
3. ¿Qué ocurre si hacemos doble clic en una celda del área de datos de una tabla dinámica?
Para ampliar más... Agrupar por un campo determinado, incluir más de un campo en el área de datos, dar formato a la tabla dinámica, mostrar los datos como porcentajes, crear nuestros propios cálculos en la tabla... Son muchas las acciones que podemos hacer sobre nuestras tablas dinámicas y que las convierten en una potente herramienta para extraer información a partir de un rango de datos. El enlace siguiente nos lleva a un apartado de Microsoft Office Online en el que podemos ver y practicar acciones que amplían nuestro trabajo con tablas dinámicas. Haz clic aquí para abrir ir a la página
A tener en cuenta ... El comando "Configuración de campo" es muy importante a la hora de trabajar y modificar aspectos de nuestras tablas dinámicas:
UT3: Tablas y gráficos dinámicos
13
Servicio Andaluz de Salud Consejería de Salud Area de Gestión Sanitaria Norte de Cádiz
Curso Excel Avanzado
No debemos olvidar que las opciones que nos presentará el cuadro que se abre con este comando depende del tipo de campo que hayamos seleccionado (página, columna, fila o datos). Por ejemplo, para la siguiente tabla.
Tenemos los siguientes cuadros de configuración de campos:
UT3: Tablas y gráficos dinámicos
14
Servicio Andaluz de Salud Consejería de Salud Area de Gestión Sanitaria Norte de Cádiz
Curso Excel Avanzado
Gráficos Dinámicos. Para crear un gráfico dinámico a partir de una tabla dinámica sólo tenemos que usar el botón "Asistente para gráficos" de la barra de herramientas "Tabla dinámica".
Veamos con un ejemplo. Veamos el procedimiento básico a seguir para crear un gráfico dinámico a partir de una tabla dinámica. Puedes practicar con el archivo ejemplo_ tabla_dinamica.xls
UT3: Tablas y gráficos dinámicos
15
Servicio Andaluz de Salud Consejería de Salud Area de Gestión Sanitaria Norte de Cádiz
UT3: Tablas y gráficos dinámicos
Curso Excel Avanzado
16
Servicio Andaluz de Salud Consejería de Salud Area de Gestión Sanitaria Norte de Cádiz
UT3: Tablas y gráficos dinámicos
Curso Excel Avanzado
17
Servicio Andaluz de Salud Consejería de Salud Area de Gestión Sanitaria Norte de Cádiz
UT3: Tablas y gráficos dinámicos
Curso Excel Avanzado
18
Servicio Andaluz de Salud Consejería de Salud Area de Gestión Sanitaria Norte de Cádiz
UT3: Tablas y gráficos dinámicos
Curso Excel Avanzado
19
Servicio Andaluz de Salud Consejería de Salud Area de Gestión Sanitaria Norte de Cádiz
Curso Excel Avanzado
Tenga en cuenta… Para trabajar correctamente con gráficos dinámicos, es imprescindible dominar el trabajo básico con gráficos estáticos en Excel, incluido en el curso de nivel básico.
UT3: Tablas y gráficos dinámicos
20
Servicio Andaluz de Salud Consejería de Salud Area de Gestión Sanitaria Norte de Cádiz
Curso Excel Avanzado
También puedes encontrar información al respecto en los siguientes enlaces de Microsoft Office Online: - Gráficos I: crear un gráfico - Gráficos II: elegir el tipo de gráfico adecuado - Gráficos III: crear un gráfico de apariencia profesional
Crear un gráfico dinámico desde cero. Crear un gráfico dinámico partiendo directamente del rango de datos es un proceso análogo al de crear una tabla dinámica partiendo del mismo rango. Si dominamos la creación de tablas dinámicas y el trabajo
con
gráficos
estáticos,
no
tendremos
problemas a la hora de crear un gráfico dinámico basado en un rango de datos. Para crear un gráfico de este tipo hacemos uso del ya conocido Asistente para tablas y gráficos dinámicos. En su primera pantalla o paso, debemos elegir "Informe de gráfico dinámico" en lugar de "Tabla dinámica".
Pruebe lo siguiente: Partiendo de la lista de pedidos que tenemos en el archivo pedidos.xls (ubicado en la zona de “Ejercicios para practicar”), obtenga el siguiente gráfico dinámico:
UT3: Tablas y gráficos dinámicos
21
Servicio Andaluz de Salud Consejería de Salud Area de Gestión Sanitaria Norte de Cádiz
Curso Excel Avanzado
Análisis de Datos. Vamos a conocer algunas herramientas que nos permiten llevar a cabo análisis, estudios de nuestros datos y extraer información de ellos.
Tenga en cuenta… Algunas de las funciones que vamos a ver pueden no estar instaladas por defecto en Excel. En estos casos, debemos usar el menú Herramientas >> Complementos e instalar las funciones deseadas.
Buscar Objetivos. La función de la búsqueda de objetivos es en realidad hacer formulas hacia atrás, es decir conocemos el resultado que deseamos tener y desconocemos uno de los valores del origen de la formula. Imaginemos que vamos a comprar una serie de materiales que nos hacen falta y nos salen los siguientes valores en la hoja de cálculo:
Los subtotales tiene introducidas las fórmulas de “Cantidad” * “Precio/Und” y el TOTAL es la suma de los Subtotales.
UT3: Tablas y gráficos dinámicos
22
Servicio Andaluz de Salud Consejería de Salud Area de Gestión Sanitaria Norte de Cádiz
Curso Excel Avanzado
El problema está en que disponemos de 400,00 € para poder gastar en esta compra y puede que nos interese invertir el dinero que nos sobre en CR Sony. ¿Cómo sabremos cuántos CR Sony podremos comprar? Usamos el menú Herramientas >> Buscar objetivo...
-
La celda objetivo es la celda de la que conocemos el valor (el resultado de la una formula).
-
"Con el valor" es donde colocamos el resultado deseado.
-
"Para cambiar la celda" es donde indicamos qué celda es la que va a variar su valor.
Cuando pulsemos Aceptar, Excel indicara lo siguiente:
Si aceptamos el cambio, Excel cambiará a los nuevos valores para obtener el resultado buscado. Durante el proceso de búsqueda de objetivo puede ocurrir que el sistema de fallos. Estos fallos suelen ser: -
"La referencia no es válida": El cuadro "para cambiar la celda" contiene una formula.
-
"La celda debe contener una fórmula": El cuadro "definir la celda" no contiene formulas.
UT3: Tablas y gráficos dinámicos
23
Servicio Andaluz de Salud Consejería de Salud Area de Gestión Sanitaria Norte de Cádiz
Curso Excel Avanzado
Escenarios. Con los escenarios en Excel se permite crear y guardar distintos tipos de valores de entrada en una misma celda. El administrador de escenarios se utiliza principalmente para: -
Crear diversos escenarios con varios conjuntos de celdas cambiantes.
-
Ver los resultados de cada escenario en la hoja de trabajo.
-
Crear informes de resumen de todos los valores de entradas y resultados.
Veamos un ejemplo. Imaginemos que disponemos de una hoja de cálculo para conocer los costes de desplazamientos de un vehículo. La hoja de uso podría ser algo así: En el ejemplo anterior, la celda A8 se ha obtenido con la fórmula =(B2*B5)/100, y la celda B8 con la fórmula =A5*A8. Supongamos que queremos ver cómo cambian estas celdas, es decir, cómo cambian el consumo y precio total, para distintas velocidades medias desarrolladas durante el viaje. Así, queremos que la misma hoja nos sirva para tres tipos de velocidades medias: 60, 80 y 110.
Usamos el menú Herramientas >> Escenarios: Cada velocidad media será un escenario distinto, debemos dar un nombre a cada escenario e indicar el valor o valores que cambian en cada uno de ellos. Para nuestro caso, vamos a disponer de tres escenarios, y en cada uno de ellos los que varían es los valores de las celdas B1 y B2: -
Velocidad prudente --> 60 y 6,4
-
Velocidad rápida --> 80 y 8
-
Velocidad rayo --> 110 y 12
UT3: Tablas y gráficos dinámicos
24
Area de Gestión Sanitaria Norte de Cádiz SAS Consejería de Salud y Bienestar Social
Formación Continuada
Pulsaremos el botón Agregar e introduciremos los datos correspondientes al primer escenario: Al pulsar Aceptar nos sale un cuadro en el que agregamos los valores de las celdas cambiantes:
Pulsamos Aceptar y seguimos usando los cuadros para introducir el resto de los escenarios como hemos hecho con el primero. Nos volverá a salir la pantalla anterior y solo tendremos que introducir los valores y nombre oportunos. El resultado final será:
Si deseamos ver los valores de la hoja en el caso de que el conductor sea "rápido", sólo tendremos que seleccionar el escenario Velocidad rápida y pinchar en Mostrar, comprobaras como los valores de las celdas B1 y B2 cambian, de forma que los resultados de las formular también cambian, todo eso sin necesidad de cambiar de hoja. Así, podremos examinar los resultados en los distintos escenarios. Imaginemos que ahora deseamos hacer una comparativa de los costes a diferentes velocidades. Para ellos existe una cómoda herramienta dentro de los escenarios, la opción Resumen, a la que se accede desde el botón "Resumen..." del cuadro "Administrador de escenarios":
UT3: Tablas y gráficos dinámicos
25
Area de Gestión Sanitaria Norte de Cádiz SAS Consejería de Salud y Bienestar Social
Formación Continuada
En "Celdas resultantes" indicaremos cuales son aquellas en las que deseamos conocer sus resultados cambiantes. Cuando se pulsa Aceptar aparece una nueva hoja de cálculo de la siguiente forma:
Con esta hoja podemos comprobar cómo los costes de una velocidad prudente a una muy rápida se duplican, así que este tipo de hojas es muy práctico para realizar comparativa de datos.
UT3: Tablas y gráficos dinámicos
26
Area de Gestión Sanitaria Norte de Cádiz SAS Consejería de Salud y Bienestar Social
Formación Continuada
La herramienta Solver. El Solver es una completa herramienta que incluye Excel como complemento y que permite resolver "ecuaciones" de una forma bastante simple. Imaginemos que nos encontramos con la siguiente hoja de cálculo
(están
marcadas
en
amarillo
las
celdas
que
contienen formulas):
En el presupuesto que acabamos de realizar nos da un total de 937,50€. Pero nosotros disponemos de 1.150,00€ para poder gastar en este pedido. Tenemos la opción de acudir a la búsqueda de objetivos, pero esa herramienta solo nos permite tener un valor cambiante. ¿Qué pasaría si nosotros deseáramos distribuir el dinero sobrante entre todos los productos?. La única posibilidad es acudir al Solver. A Solver se accede desde el menú Herramientas >> Solver.... Lo que ocurre es que Solver es un complemento que, por defecto viene sin instalar en Excel. Para instalarlo debemos hacer uso del menú Herramientas >> Complementos... y seleccionar Solver para instalar el complemento. Una vez hecho esto, lo tendremos disponible en el menú Herramientas.
UT3: Tablas y gráficos dinámicos
27
Area de Gestión Sanitaria Norte de Cádiz SAS Consejería de Salud y Bienestar Social
Formación Continuada
-
Celda Objetivo: Es la celda que contiene el resultado que deseamos (en este caso el Total).
-
Valor de la celda Objetivo: Indicaremos si deseamos que esta celda contenga el máximo valor posible para formula, el mínimo posible o un valor concreto.
-
Cambiando
las
donde indicaremos
celdas: qué
Es
celdas
o
rangos de celdas son los que deseamos que se modifiquen. Si pulsamos Estimar Excel
deducirá
celdas
de
la
automáticamente fórmula
de
la
las celda
objetivo. -
Sujeta
a
las
siguientes
restricciones: Indicaremos
Excel
reglas
generen
serán
las
que
qué el
resultado. En nuestro ejemplo, podemos plantear que: 1. Los precios de unidad nos lo impone el distribuidor, así que no podemos cambiarlos (cuidado con "Estimar"). 2. Los valores máximos de productos que podemos llevarnos, debido al espacio que tenemos es: -
Bolígrafos, un máximo de 100 unidades
-
Paquetes A4, un máximo de 150 unidades
-
Teclados y ratones, un máximo de 50 unidades
-
CDRW, un máximo de 500 unidades
-
CDR un máximo de 700 unidades
Nos debería quedar algo así: Al pulsar el botón de Resolver nos encontraremos que Excel nos dice lo siguiente:
UT3: Tablas y gráficos dinámicos
28
Area de Gestión Sanitaria Norte de Cádiz SAS Consejería de Salud y Bienestar Social
-
Formación Continuada
Si pulsamos Aceptar nos cambiara automáticamente los valores de las unidades por los que ya nos está dejando visualizar.
-
Si cancelamos nos devolverá a los valores originales
-
La opción de Guardar escenario nos permite que los valores de este Solver se guarden en un escenario aparte, de forma que podemos tener diferentes resultados y gestionarlos de una manera rápida y fiable.
-
También disponemos de tres posibles informes que Solver puede generar: Respuestas, Sensibilidad y Límites.
Análisis estadísticos. Excel nos proporciona una completa herramienta para el Análisis estadístico de datos. Al igual que Solver, esta herramienta debe ser incluida previamente desde el menú Herramientas >> Complementos...
Una vez instalado el complemento, tendremos acceso a la herramienta desde el menú Herramientas >> Análisis de datos.
Evidentemente, para usar estas herramientas es necesario un conocimiento previo de las propias técnicas estadísticas, su significado, características y uso.
Tenga en cuenta... La Ayuda de Excel nos proporciona abundante información acerca de las herramientas para el análisis de datos. En concreto, en la Tabla de contenidos, podemos ir a Trabajar con datos >> Analizar datos.
UT3: Tablas y gráficos dinámicos
29
Area de Gestión Sanitaria Norte de Cádiz SAS Consejería de Salud y Bienestar Social
Formación Continuada
MACROS. Si hay tareas de Microsoft Excel que realiza reiteradamente, puede grabar una macro para automatizarlas. Una macro es una acción o un conjunto de acciones que se puede ejecutar todas las veces que desee. Cuando se crea una macro, se graban los clics del mouse y las pulsaciones de las teclas. Después de crear una macro, puede modificarla para realizar cambios menores en su funcionamiento. Imagina que tienes un listado de pacientes atendidos por cada UGC. Durante el día, se va añadiendo registros por los pacientes que han ido asistiendo y, a final de la jornada, nos interesa tenerlos ordenados por fecha de atención, de forma descendente y luego, por UGC, de forma ascendente. El proceso de grabación de una macro, de forma sencilla, sería algo así: 1. Seleccionamos, del menú “Herramientas”, la opción “Macro” -> “Grabar nueva macro…”
2. Especificamos los siguientes datos: a. Nombre: descriptivo para que sepamos lo que hace b. Método abreviado: especificación de una tecla que, junto a CTRL, nos facilitará la ejecución de la macro de forma más cómoda (sin tener que acceder a Herramientas -> Macros -> Macros …) c. Guardar macro en : indicamos dónde será grabada y tendrá efecto la macro que crearemos. d. Descripción: podemos usarlo para especificar con más detalle lo que hace la macro.
UT3: Tablas y gráficos dinámicos
30
Area de Gestión Sanitaria Norte de Cádiz SAS Consejería de Salud y Bienestar Social
Formación Continuada
Una vez que pulsemos en el botón Aceptar, todo lo que hagamos será memorizado y almacenado en forma de macro, hasta el momento en el que pulsemos el botón “Detener grabación”:
Para comprobar el funcionamiento de dicha macro, podemos lanzar su ejecución de dos formas distintas, mediante la combinación de teclas indicada en el método abreviado, o bien, accediendo mediante la ruta de opciones: Herramientas -> Macros -> Macros … y seleccionando la macro en cuestión, para luego pulsar el botón “Ejecutar”. Si deseamos eliminar o modificar alguna macro, lo haremos desde la ruta de opciones comentada.
UT3: Tablas y gráficos dinámicos
31