PDF de programación - Guías Excel 2010 - Análisis y si...

Guías Excel 2010

Análisis Y si…

Guía 111

¿QUÉ PASARÍA SI…?



CONTENIDO

¿Qué pasaría si…?.............................................................................................................................. 1

Contenido ......................................................................................................................................... 1

Búsqueda de objetivos ........................................................................................................................ 1

Resolución de una ecuación ................................................................................................................ 3

Herramienta Solver .......................................................................................................................... 4

Planteo de Solver ......................................................................................................................... 4

Solver no lineal ............................................................................................................................... 7

Escenarios ........................................................................................................................................ 8



BÚSQUEDA DE OBJETIVOS

Una de las prestaciones más útiles de Excel es la de búsqueda de objetivos. Si
dispones de una tabla con un resultado y deseas fijar el mismo con una cantidad
concreta, la búsqueda de objetivos te permite alterar los datos a fin de que se obtenga
el resultado que deseas.

Por ejemplo, en el sueldo de una trabajadora se contabilizan las horas de trabajo
dentro de su horario laboral (34 horas semanales) a 12 €, y las extraordinarias a 33 €

Podemos resumir la situación en la siguiente tabla:

Sueldo semanal


Horas

Ordinarias

Extraordinarias



34

22



12 €

33 €

Total mensual

408 €

726 €
1134 €


Copia esta tabla en Excel, de forma que el total caiga en la celda E8, como verás en la
imagen.



1

Guías Excel 2010

Análisis Y si…

Guía 111



Si el total del sueldo (1134 €) se encuentra en la celda E8, el número de horas
extraordinarias en la C7. Podemos ajustar dichas horas para que la trabajadora
perciba un sueldo semanal de 1300 €. Para ello puedes acudir a la Búsqueda de
Objetivos. Los pasos serían:

Antes de nada, como esta tabla la has copiado en forma de texto, deberás
sustituir las cantidades de la tercera columna por fórmulas: 408 es en realidad
=C6*D6, 726 es =C7*D7 y 1134 la suma de las dos.

En la ficha Datos busca Análisis Y si y después abre Buscar objetivo...



En la ventana que obtienes has de rellenar tres datos:

Definir la celda: Debes escribir la celda que deseas
que cambie. En este caso E8, que es la que va a
contener el nuevo sueldo.

Con el valor: Escribes el nuevo valor del sueldo, en
este caso 1300 €

Para cambiar la celda: Se trata de la celda que ha de cambiar su valor para poder
obtener esos 1300 €. En nuestro ejemplo sería la C7 (observa que se le añade a su

referencia el signo $)

Al pulsar Aceptar se te comunicará si lo que pides
es posible o no, si ha encontrado una solución.
Confirmas con Aceptar y cambiará el número de
horas extraordinarias con el valor adecuado para
que el total sea el sueldo deseado, unas 27 horas.



2

Guías Excel 2010

Análisis Y si…

Guía 111



Esta operación sólo funciona entre dos celdas: una variable (en este ejemplo las horas
extraordinarias) y otra que recibirá el valor deseado (el sueldo).

RESOLUCIÓN DE UNA ECUACIÓN

La búsqueda de objetivos te permite resolver ecuaciones de todo tipo si tienes idea de
en qué intervalo aproximado puede existir una solución.

Por ejemplo, deseamos resolver la siguiente ecuación:

X+LN(3X)=10 (LN logaritmo neperiano)

Un procedimiento práctico y que te permite tener todo preparado para otra ecuación
sería el siguiente:



En una celda (en la imagen F5) preparamos la estimación de la solución. En este caso
creemos que estará cerca del 8. También con la opción de Definir nombre, le damos el
nombre de X,

En otra celda, por ejemplo la F7, escribimos la ecuación pasándola toda al primer
miembro (no es necesario, pero así usaremos el mismo procedimiento siempre), es

decir, en este caso =X+LOG(3*X)-10

Después, con la Búsqueda de objetivos, pedimos definir
la F7 (ver la imagen anterior) con el valor 0 para cambiar
la celda F5. Así de simple. Aceptamos y obtendremos la
solución más aproximada



3

Guías Excel 2010

Análisis Y si…

Guía 111

Además, indirectamente, nos indica los lejos del cero que queda la celda F7.

Si practicas un poco, resolverás, con una aproximación aceptable, cualquier ecuación
por complicada que sea.



HERRAMIENTA SOLVER

La herramienta anterior sólo es útil si hacemos depender el valor de una celda de otra,
siendo esta la única que deseamos cambiar. Sin embargo, existen situaciones en las
que el valor último depende de varias celdas. Para buscar objetivos en este caso
disponemos de la herramienta Solver.

PLANTEO DE SOLVER

La herramienta Solver nos permite optimizar el valor de una celda, a la que
llamaremos Objetivo, que depende de las celdas de un rango determinado, el cual
puede estar sometido a restricciones. Si la dependencia es lineal, es en realidad el
problema matemático de Programación Lineal.

Así que en una resolución de Solver intervienen tres tipos de datos:

Celda Objetivo que depende de otras
Rango de celdas que determinan el valor del objetivo

Restricciones a las que está sometido este rango de celdas.

Su funcionamiento se puede estudiar con un ejemplo:

Después de vender una casa, a una persona le quedan 170.000 € para invertir. Desea
una inversión conservadora, por lo que duda entre varias

A) Depósito en banca de Internet, que está dando el 2,2% TAE, pero es un producto
novedoso que no le termina de convencer

B) Su banco de toda la vida le ofrece plazo fijo con interés de 1,75% TAE, y que ella
considera seguros.

C) Un producto vinculado a un fondo, con rendimientos del 3% pero sujeto a
volatilidad.

En vista de la situación, decide invertir en B) al menos la mitad del capital, y en C) no
más de 20,000 €

¿Qué cesta de inversiones le daría el máximo rendimiento?



4

Guías Excel 2010

Análisis Y si…

Guía 111

Volcamos los datos en la tabla siguiente:



En la columna C hemos concretado unos capitales inventados, pero cercanos a la
posible solución y con suma 170000. Sobre esta tabla podemos concretar los
parámetros del problema:

Celda objetivo: E8, que es el rendimiento total.

Celdas que cambian: C5 a C7, la composición de la cesta.

Restricciones: C6 ha de valer, como mínimo, 170000/2 = 85,000 €, la celda C7 no
debe pasar de 20.000 €, y la C8 ha de contener 170,000 €

Objetivo que se pretende: Maximizar

Todo esto se puede concretar en la herramienta Solver.

Abre la cinta de Datos y busca la entrada a Solver en el extremo
derecho:

Si no lo encuentras, será porque no está activado.
Abre la ficha Programador, busca Complementos y
activa Solver

De esta forma lo tendrás activo en la citada ficha
Datos.

Pulsa sobre él para ir concretando las condiciones del
problema.



Obtendrás esta ventana para concretar
opciones. Estudia bien la forma de hacerlo:

tus

Hemos rellenado estos datos:



5

Guías Excel 2010

Análisis Y si…

Guía 111

Celda objetivo: E8 (ganancia total)

Valor de la celda objetivo: Máximo

Celdas variables: C5:C7

Restricciones: Las ya comentadas: C6>=85000; C7<=20000; C8=170000

Aunque no se ve en la imagen, hemos elegido el método Simplex, que es el adecuado
para problemas lineales (Un problema es lineal cuando las operaciones básicas son
las de multiplicar y sumar, sin cuadrados o raíces o logaritmos)

Pulsamos Resolver, y en este caso existe la solución 3.517,5 €. Automáticamente te
rellena las cantidades que has de invertir en cada caso, pero te da la opción de volver
a los valores originales o de abrir de nuevo el cuadro de datos de Solver.



Pulsa en Aceptar y verás la solución: Invertir
65.000 € en A, 85.000 € en B y 20.000 € en C,
con una ganancia de 3517,5 €

Puedes también lograr que la inversión rinda una
cantidad determinada, por ejemplo 3475 €. Para
ello elige Valor de e iguálalo a 3475. Obtendrás
esta solución:



A veces Solver no puede encontrar la solución. Esto se puede deber a tres causas:

El problema es de tipo indefinido. Existen muchas soluciones.
Las soluciones tienden a infinito (especialmente en problemas de máximos) y

se produce un desbordamiento.

No hay convergencia. Las soluciones no se acercan lo suficiente al objetivo



6

Guías Excel 2010

Análisis Y si…

Guía 111



SOLVER NO LINEAL

La herramienta Solver de Excel también resuelve casos no lineales, e incluso con el
uso de logaritmos, exponenciales o funciones trigonométricas. En el siguiente ejemplo
buscaremos el máximo de una fórmula polinómica. Supongamos que deseamos
estudiar la función x2(100-Kx) en el intervalo de 0 a 100, en el que sospechamos que
existe un máximo. Deseamos localizarlo según los valores de la constante K.



Escribimos el valor de x en la celda D9 y la fórmula en la
celda E9 y añadimos las restricciones x>=0 y x<=100.
Como método elegimos el No lineal (GRG Nonlinear).

Como Valor elegimos Máximo, con lo que se nos devuelve
el valor de x en el que se llega al máximo según el valor de

K. En la imagen hemos fijado K=26, obteniendo el valor máximo x=2,56 y=216,15

.

Si repitiéramos estos cálculos para var