PDF de programación - Herramientas de análisis de datos

H E R R A M I E N T A S D E A N Á L I S I S D E D A T O S

HERRAMIENTAS DE ANÁLISIS DE DATOS

Una situación que se nos plantea algunas veces es la de resolver un problema
hacia atrás, esto es, encontrar el valor que debemos dar a una celda para lograr que
otra, cuyo valor depende de la primera, alcance determinado valor objetivo.

Otra situación que interesa es conocer cómo afecta el cambio de uno o varios
datos a uno o más resultados. Se trata de responder a la pregunta ¿qué pasa si?
Cada conjunto de datos que aplicamos al modelo constituye un escenario y
queremos calcular los resultados del modelo en los diferentes escenarios.

En muchas situaciones de la vida real se nos plantean problemas de optimización.
Disponemos de unos recursos limitados y tratamos de sacarles el mayor fruto
posible: qué solución da el máximo beneficio o produce el mínimo gasto.

Buscar objetivo

Cuando en una fórmula no es fácil, o no es posible, despejar una variable para
resolver el problema inverso ¿Qué valor de la variable produce tal resultado?
todos hemos utilizado, alguna vez, el método prueba y error. Probamos con
diferentes valores del dato hasta logar el resultado deseado.

El comando Datos >Análisis Y si > Buscar objetivo… encuentra la mayoría
de las veces el valor que debe tener una celda, dato inicial, para alcanzar
determinado resultado, valor objetivo, en otra celda. Necesariamente la celda dato
inicial, cuyo valor queremos determinar, debe contener un valor, no puede ser una
fórmula, y la celda objetivo debe contener una fórmula cuyo valor varía con el
dato inicial.

En algunos casos el proceso de búsqueda de objetivo puede concluir que no hay
ningún valor del dato inicial que de cómo resultado el valor objetivo propuesto.
En otras ocasiones puede ocurrir que el valor logrado para el dato inicial sea
absurdo.

Ejemplo 1

Queremos comprar un coche y pagarlo en seis años. La financiera nos presta el
dinero a una tasa de interés anual del 6,75%. ¿Cuánto dinero nos financian
pagando 300,00€ al mes?

Ninguna de las funciones financieras de Excel resuelve este problema. Sin
embargo la función PAGO resuelve el problema directo “conocida la cantidad
prestada hallar el pago mensual”.

Para resolver el problema inverso partimos del problema directo. Supongamos
que el coche de nuestros sueños cuesta 30.000€ ¿Cuál es el pago mensual?

1

C U R S O D E E X C E L A V A N Z A D O


Para comprar el coche de nuestros sueños tenemos que pagar 507,88€ al mes y
nuestro objetivo es pagar 300€ al mes.

Seleccionamos, aunque no es necesario, la celda objetivo B4 y ejecutamos el
comando Datos >Análisis Y si > Buscar objetivo…

Entramos el valor objetivo -300€ en el cuadro de diálogo Con el valor:, la
referencia de la celda cuyo valor queremos determinar en el cuadro de diálogo
Para cambiar la celda:



y pulsamos el botón Aceptar.



Excel encuentra la solución, pagando 300€ al mes podemos financiar un coche de
17.720,85€.

Si hacemos clic en el botón Aceptar dejará el valor objetivo en la celda objetivo y
si hacemos clic en el botón Cancelar volveremos a la situación inicial.

Ejemplo 2

En un problema anterior obteníamos las previsiones de Ventas, Costes, Beneficio
Bruto, Impuestos y Beneficio Neto de una empresa hasta el año 2010.

¿Cuál debería ser el porcentaje de Costes/Ventas si queremos lograr un Beneficio
Neto de 80.000€ en el año 2010?

2

H E R R A M I E N T A S D E A N Á L I S I S D E D A T O S


Si el porcentaje de Costes/Ventas fuese del 32,04% en lugar del 45,00% el
Beneficio Neto en el año 2010 sería de 80.000,00€ en lugar de 64.741,81€.

Escenarios

Si no conocemos la herramienta Escenarios de Excel y queremos estudiar el
comportamiento de un modelo ante diferentes conjuntos de datos
(ESCENARIOS), podemos copiar el modelo varias veces, en la misma hoja o en
hojas diferentes, y entrar un conjunto diferente de datos en cada copia del
modelo. Para ver los resultados del modelo en los diferentes escenarios podemos
abrir varias ventanas con los resultados de cada escenario, si son pocos resultados,
o copiarlos juntos en algún sitio.

El comando Datos >Análisis Y si > Administrador de escenarios: desde el
Administrador de escenarios podemos crear escenarios, guardar cada conjunto de
datos que constituye un escenario con un nombre; cambiar de un escenario a
otro, eliminar, modificar y combinar escenarios y, crear informes resumen con
todos los escenarios.

Al igual que ocurre con Buscar objetivo, las celdas que contienen los datos del
escenario debe contener un valor, no pueden ser una fórmula.

Ejemplo 3

Queremos solicitar un préstamo de 15.000€ a devolver inicialmente en 60 meses a
una tasa de interés anual del 6,50%.

3

C U R S O D E E X C E L A V A N Z A D O

Supongamos que el préstamo es a interés variable y que se actualiza cada seis
meses. Además, la entidad financiera permite, en el momento de la actualización,
que podamos personalizar el plazo restante. Cada seis meses, la entidad financiera
actualiza la tasa de interés y nosotros actualizamos la duración.

Calcular el pago mensual que debemos efectuar durante el siguiente periodo de
seis meses en diferentes escenarios:

 Escenario MM: tasa de interés 6,50%, número de pagos 54.
 Escenario AA: tasa de interés 6,75%, número de pagos 60.
 Escenario AD: tasa de interés 6,75%, número de pagos 48.
 Escenario DA: tasa de interés 6,25%, número de pagos 60.
 Escenario DD: tasa de interés 6,25%, número de pagos 48.

M (Mantener), A (Aumentar) y D (Disminuir).

Si la situación no cambia, Escenario MM, el pago mensual sería el mismo.


Cada escenario son dos datos la tasa de interés, celda B13, y el número de pagos,
celda B12.

Para crear los escenarios, seleccionamos las celdas B12 y B13, datos de entrada del
escenario, y ejecutamos el comando Datos >Análisis Y si > Administrador de
escenarios

4

H E R R A M I E N T A S D E A N Á L I S I S D E D A T O S

Pulsamos el botón Agregar… del Administrador de escenarios.

Entramos el nombre del primer escenario MM



5

C U R S O D E E X C E L A V A N Z A D O

y pulsamos el botón Aceptar.

Se abre la ventana Valores del escenario en la que podemos ver las referencias
de las celdas que constituyen el escenario con los valores actuales de dichas celdas.



Como los valores que aparecen son los del primer escenario hacemos clic en el
botón Agregar para crear el siguiente escenario.



6

H E R R A M I E N T A S D E A N Á L I S I S D E D A T O S



Cuando entremos los datos del último escenario pulsamos el botón Aceptar en
lugar de Agregar.

7

C U R S O D E E X C E L A V A N Z A D O

Si ahora seleccionamos un escenario, por ejemplo DA, y hacemos clic en el botón
Mostrar las celdas de datos del escenario cambian lo datos que contiene por los
del escenario y vemos los resultados para ese escenario.



Podemos crear un informe resumen en el que aparezcan los datos de entrada y los
resultados que nos interesen para cada escenario. Para ello, hacemos clic en el
botón Resumen…



Excel selecciona una serie de celdas resultado. Nosotros queremos saber cuál será
el pago mensual durante el segundo semestre, celda B16, y cuánto deberemos al
final del segundo semestre, celda J17 en cada escenario.

8

H E R R A M I E N T A S D E A N Á L I S I S D E D A T O S

Si hacemos clic en el botón Aceptar, Excel crea la siguiente hoja de cálculo.



El resumen incluye una columna Valores actuales. Podemos modificar el
aspecto de la hoja Resumen de escenario.

9

C U R S O D E E X C E L A V A N Z A D O


En lugar de elegir Resumen como tipo de informe podemos elegir Informe de
tabla dinámica de escenario.



Optimización con Solver

Los problemas de optimización se caracterizan por tener un único objetivo a
optimizar (maximizar, minimizar o igual a). Este objetivo depende de uno o más
valores iniciales de entrada. Además suele haber una serie de reglas o restricciones
que deben satisfacerse. Resolver un problema de este tipo consiste en hallar los
valores de entrada que cumplen las restricciones y dan el valor objetivo óptimo.

La celda con el valor a optimizar, función objetivo, debe ser una fórmula que
depende, de modo directo o indirecto, de las celdas de entrada.

Las celdas de entrada, cuyos valores queremos determinar, deben contener
valores, no pueden ser fórmulas.

10

H E R R A M I E N T A S D E A N Á L I S I S D E D A T O S

Las restricciones se introducen en la ventana Parámetros de Solver que se abre
cuando ejecutamos el comando Datos > Análisis > Solver…

Puede que no aparezca la herramienta Solver. Esto se debe a que al instalar Excel
en nuestro ordenador, salvo que hagamos una instalación personalizada, sólo se
instalan las herramientas más comunes. Este problema ya apareció con las
funciones, en principio Excel no carga todas las funciones.

Para añadir la herramienta Solver seleccionamos Opciones de Excel >
Complementos > Ir… activamos el complemento Solver y hacemos clic en el
botón Aceptar.



Ejemplo 4

El Servicio de Formación de la Universidad de Alicante tiene un presupuesto de
300.000 € para organizar curso de formación para el personal de la universidad.

Se pueden organizar dos tipos de grupos: grupos pequeños de 20 alumnos con un
coste de 15.000€ y grupos grandes de 30 alumnos con un coste 20.000€.

Para atender los grupos se contratan profesores, uno cuando el grupo es pequeño
y dos cuando el grupo es grande. Moti