PDF de programación - Guía de LibreOffice Calc - Análisis y si...

Guías LibreOffice Calc

Análisis Y si…

Guía 10

¿QUÉ PASARÍA SI…?



CONTENIDO

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

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

Búsqueda de valor destino .................................................... 2

Resolución de una ecuación ................................................. 4

Herramienta Solucionador ..................................................... 6

Planteo de Solucionador ..................................................... 6

Solucionador no lineal ....................................................... 10

Ajuste por mínimos cuadrados ......................................... 12

Escenarios ............................................................................. 14

Creación de un escenario ................................................. 15

Modificación de un escenario ........................................... 18

Operaciones múltiples ......................................................... 19



1

Guías LibreOffice Calc

Análisis Y si…

Guía 10



BÚSQUEDA DE VALOR DESTINO

Una de las prestaciones más útiles de Calc 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 valor destino te permite alterar
los datos a fin de que se obtenga el resultado que deseas.

trabajadora se
Por ejemplo, en el sueldo de una
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
34
Extraordinarias 22



12 €
33 €

Total
mensual

408 €
726 €

1134 €



Copia esta tabla en Calc, de forma que el total caiga en la
celda G8, como verás en la imagen (puedes situarlo en otro
lugar y luego cambiar las referencias de celdas del texto
que sigue).

.

2



Guías LibreOffice Calc

Análisis Y si…

Guía 10

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

Antes de nada, como esta tabla la has copiado en forma de
texto, deberás sustituir
tercera
columna por fórmulas: 408 es en realidad =D6*E6, 725 es
=D7*E7 y 1134 la suma de las dos. Si no efectúas estos
cambios, nada funcionará.

las cantidades de

la

Abre el menú Herramientas y dentro de él la Búsqueda de
valor destino…

En la ventana que obtienes has de rellenar tres datos:



Celda de fórmula: Debes escribir la celda que deseas que
cambie. En este caso G8, que es la que va a contener el
nuevo sueldo.

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

Celda variable: Se trata de la celda que ha de cambiar su
valor para poder obtener esos
1300 €. En nuestro ejemplo
sería la E7.



3

Guías LibreOffice Calc

Análisis Y si…

Guía 10

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.



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:



4

Guías LibreOffice Calc

Análisis Y si…

Guía 10

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

preparamos

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 con el valor 0 para cambiar la celda F5. Así de simple.

Aceptamos y obtendremos la solución más aproximada

8,58893921602703. Además, indirectamente, nos indica los

lejos del cero que queda la celda F7.

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

una



5

Guías LibreOffice Calc

Análisis Y si…

Guía 10

HERRAMIENTA SOLUCIONADOR

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 Solucionador.

Para que funcione en tu equipo has de descargar e instalar
un entorno Java. Es sencillo. En cualquier navegador busca
“instalar Java”.

PLANTEO DE SOLUCIONADOR

La herramienta Solucionador 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 Solucionador 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:



6

Guías LibreOffice Calc

Análisis Y si…

Guía 10

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 inversiones

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?

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
los
parámetros del problema:

tabla podemos concretar

Celda objetivo: E8, que es el rendimiento total.

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



7

Guías LibreOffice Calc

Análisis Y si…

Guía 10

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
Solucionador.

la herramienta

Abre el menú Herramientas y busca
la entrada a
Solucionador. Para que esta herramienta funcione debes
tener instalado Java. Al elegir la opción obtienes esta
ventana:

En la imagen hemos rellenado estos datos:



Celda objetivo: E8 (ganancia total)

Valor de la celda objetivo: Máximo

Celdas variables: C5:C7



8

Guías LibreOffice Calc

Análisis Y si…

Guía 10

Restricciones:
C7<=20000; C8=170000

Las

ya

comentadas: C6>=85000;

Es conveniente, para mayor rapidez, que con el botón
Opciones determines el método que se va a usar, en este
caso el Lineal de LibreOffice, y también que las soluciones
son no negativas. Acepta y pulsa después en Solucionar:



En este caso existe la solución 3.517,5 €. Elige Mantener
resultados y la solución se situará en la celda E8, así como
cambiarán los datos de las celdas variables.

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:



9

Guías LibreOffice Calc

Análisis Y si…

Guía 10

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

 El problema es de tipo indefinido. Existen muchas

soluciones.

 Las soluciones tienden a infinito (especialmente en
se produce un

y

problemas de máximos)
desbordamiento.

 No hay convergencia. Las soluciones no se acercan lo

suficiente al objetivo



SOLUCIONADOR NO LINEAL

La herramienta Solucionador de Calc 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 un No lineal (por

ejemplo DEPS).

Como Valor elegimos Máximo, con