PDF de programación - Guías LibreOffice Calc - ¿Qué pasaría si…?

Guías LibreOffice Calc

¿Qué pasaría si…?

Guía 10

¿QUÉ PASARÍA SI…?



CONTENIDO

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

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

Búsqueda de valor destino .................................................................................................................. 1

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

Herramienta Solucionador............................................................................................................... 4

Planteo de Solucionador.............................................................................................................. 4

Solucionador no lineal ..................................................................................................................... 7

Ajuste por mínimos cuadrados ........................................................................................................ 8

Escenarios ...................................................................................................................................... 10

Creación de un escenario .............................................................................................................. 10

Modificación de un escenario........................................................................................................ 12



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.

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

1

408 €
726 €

1134 €

Guías LibreOffice Calc

¿Qué pasaría si…?

Guía 10

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

.

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 las
cantidades de la 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á.

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.

Al pulsar Aceptar se te comunicará si lo
que pides es posible o no, si ha
encontrado una solución.

Confirmas con Sí 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 LibreOffice Calc

¿Qué pasaría si…?

Guía 10



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:

la

(en

En una celda
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 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 una
aproximación aceptable, cualquier ecuación por complicada que sea.



3

Guías LibreOffice Calc

¿Qué pasaría 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.

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:

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:



4

Guías LibreOffice Calc

¿Qué pasaría si…?

Guía 10



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

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



5

Guías LibreOffice Calc

¿Qué pasaría si…?

Guía 10

Celdas variables: C5:C7

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

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
las celdas
variables.

los datos de

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 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 problemas de máximos) y

se produce un desbordamiento.

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



6

Guías LibreOffice Calc

¿Qué pasaría si…?

Guía 10



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 N