| Artículos | 01 SEP 2002

Excel 2002 (y IV)

Tags: Histórico
Rodolfo de Benito.
En esta última entrega hablaremos sobre cómo utilizar los escenarios, hacer más interactivas las hojas insertando controles, automatizar tareas grabando macros y encontrar soluciones con Buscar objetivo y Solver.

A diferencia de entregas anteriores, en las que un solo ejemplo o supuesto práctico servía de hilo conductor, en esta ocasión recurriremos a tres ejemplos diferentes:
-Escenarios. Veremos cómo sustituir automáticamente datos en una misma hoja por otros almacenados en un escenario. Es una versión mejorada del ejemplo utilizado en la tercera entrega de este curso -ver PC World nº 189-.
-Simulador de hipotecas. Calcularemos el pago mensual para amortizar una hipoteca dependiendo del banco que se seleccione y las condiciones del mismo. Incluye controles de formulario y ActiveX para mostrar la página web del banco. Se emplea la herramienta Buscar Objetivo.
-Resolución de problemas con Solver. Muestra la utilización de esta potente herramienta para resolver problemas de optimización. En el ejemplo se parte de unos kilogramos de manzana de varias clases y se distribuyen entre los lagares para lograr el máximo de litros de sidra manteniendo la producción de cada lagar por encima del 70%. Este ejemplo es una mejora del utilizado en la segunda entrega -ver PC World nº 188-.
Pero vayamos al grano y empecemos por ver las posibilidades que ofrecen los escenarios.

El Administrador de escenarios
Un escenario es un conjunto de valores de la hoja de cálculo almacenados e identificados mediante un nombre y que pueden ser mostrados de forma inmediata. Se utilizan para sustituir automáticamente los valores actuales de la hoja por los guardados en el escenario. De esta forma se posibilita la comparación inmediata de datos e incluso la creación automática de un informe resumen o una tabla dinámica. Todas estas posibilidades están centralizadas en el Administrador de escenarios, que además permite mostrar, agregar, eliminar, modificar, combinar y resumir escenarios.
En la Figura 2 puede ver una hoja con las ventas totales de un trimestre agrupadas por vendedor y unos datos calculados en base a éstas. Podrían emplearse cuatro escenarios diferentes, uno para cada trimestre, y mostrar a voluntad el que convenga. Excel recalculará sin problemas los nuevos valores para cada uno.
Pongámonos manos a la obra y veamos la forma de definir estos escenarios.
1. Para crear el primer escenario tendremos que seleccionar las ventas en euros y la celda que contiene el rótulo Primer trimestre -utilice la tecla CTRL para selecciones discontinuas-. No necesitamos más datos, ya que los demás son fijos o son calculados en función de las ventas.
2. Seleccionaremos en el menú HerramientasEscenarios... y pulsaremos el botón Agregar del cuadro Administrador de escenarios. Excel pedirá a continuación un nombre para identificar el escenario y las celdas cambiantes que contienen los valores a guardar -ver Figura 3-.
3. En el nombre del escenario introduciremos Primer trimestre y dejaremos como están las celdas cambiantes, ya que corresponden a las seleccionadas con anterioridad. Además, tendremos oportunidad de introducir un comentario y, en caso necesario, ocultar el escenario o preservarlo de cambios si activamos las opciones Ocultar y Evitar cambios respectivamente. Recuerde que la hoja debe estar protegida para que estas dos opciones surtan efecto.
4. Tras pulsar el botón Aceptar accederemos a un cuadro de diálogo que nos facilitará la entrada de valores en las celdas cambiantes. En este caso, como los valores corresponden precisamente a los del primer trimestre, no es necesario introducir nada, pero sí en el resto de los escenarios que definiremos a continuación.
5. Pulsaremos Agregar para definir el segundo escenario y repetiremos desde el paso número tres hasta que los datos de los cuatro trimestres estén almacenados en sendos escenarios. Tan sólo habrá que definir el nombre del escenario e introducir los valores, es decir, las ventas del trimestre correspondiente.
Una vez definidos, podemos cerrar el Administrador de escenarios.

Operaciones con escenarios
Siguiendo los pasos anteriores hemos conseguido que en una misma hoja de cálculo se puedan mostrar datos diferentes según cambiemos de escenario. Todo ello sin necesidad de introducir ninguna función, macro o código. ¿Qué hay que hacer para mostrar los datos de un escenario? Tan sólo ir al Administrador de escenarios, seleccionar el nombre del que interese y pulsar el botón Mostrar -ver Figura 4-.
Si deseamos eliminar, modificar o agregar un escenario podemos hacerlo fácilmente con los botones que para tal efecto tiene el Administrador. Pero quizá la opción más interesante sea poder crear un informe resumen de los escenarios. Pulsando el botón Resumen, Excel nos ofrecerá dos posibilidades: crear un informe resumen en una nueva hoja de cálculo -ver Figura 5- o crear una tabla dinámica. Esta última es interesante cuando los escenarios han sido creados por distintos usuarios, ya que la tabla dinámica posee una lista desplegable con los nombres de estos para mostrar únicamente los escenarios del usuario seleccionado.
Quizá se pregunte cómo puede copiar escenarios de una hoja de cálculo a otra, incluso entre libros diferentes. Hay que seguir estas indicaciones:
1. Situarse en la hoja de cálculo en la que desean copiarse.
2. Seleccionar el menú Herramientas Escenarios... y pulsar el botón Combinar.
3. En el cuadro de diálogo Combinar escenarios seleccionar el libro y hoja en la que se encuentran los escenarios y pulsar el botón Aceptar.

Automatización de tareas repetitivas con macros
Suponga que la hoja de cálculo anterior con los cuatro escenarios definidos tiene que ser utilizada por una persona con escasos conocimientos de Excel. Posible-mente sería adecuado incluir en la hoja un botón para acceder a cada escenario, de tal forma que el usuario al pulsar sobre él viera directamente los datos asociados al escenario correspondiente. En este punto se nos plantean dos cuestiones sencillas: cómo crear un botón y cómo hacer que al pulsarlo se visualice el escenario correspondiente.
En la barra de herramientas Formularios encontraremos un control llamado Botón y lo utilizaremos para asociarle una macro que se encargará de mostrar el escenario. Una macro o macroinstrucción es un conjunto de instrucciones almacenadas en un módulo de Visual Basic y que normalmente se utiliza para ejecutar tareas repetitivas. Suponga que frecuentemente necesita aplicar a las celdas un mismo conjunto de formatos. Por ejemplo, determinadas alineaciones verticales y horizontales, tipo de letra, bordes y color. Siempre los mismos. Podría crear una macro y asociarla a un botón para que aplicara todo este conjunto de formatos a las celdas que previamente seleccione.
¿Cómo crearlas? Las macros pueden crearse escribiendo directamente el código o “grabando” las acciones realizadas en la hoja de cálculo, opción que para la mayor parte de los usuarios es la más adecuada. Evidentemente, Excel se encarga de traducir a código estas acciones y convertirlas en una macro.
Para ejecutar una macro podemos elegir entre:
- Utilizar el cuadro de diálogo Macro en el menú HerramientasMacroMacros..., seleccionar el nombre de la misma y pulsar el botón Ejecutar.
- Asociarla a una combinación de teclas.
- Asociarla a un botón de la barra de herramientas.
- Asoc

Contenidos recomendados...

Comentar
Para comentar, es necesario iniciar sesión
Se muestran 0 comentarios
X

Uso de cookies

Esta web utiliza cookies técnicas, de personalización y análisis, propias y de terceros, para facilitarle la navegación de forma anónima y analizar estadísticas del uso de la web. Consideramos que si continúa navegando, acepta su uso. Obtener más información