UNIVERSIDAD DE LIMA

FACULTAD DE CIENCIAS EMPRESARIALES Y ECONÓMICAS

CARRERA DE NEGOCIOS INTERNACIONALES
2018-2

NOTA TÉCNICA SOBRE EL USO DEL SOLVER EXCEL

Versión 2.0

Gutiérrez Villaverde, Herberth E.1

1
Docente de la Universidad de Lima: hgutierr@[Link]
 Contenido

Introducción. 3
Instalación del Solver en Excel 2010/2016. 3
Solución con Solver de un problema de programación lineal de mezclas. 5
Descripción de las opciones de cálculo del Solver. 11
Fuentes de información 16
Introducción
Existen varios programas de optimización lineal, no lineal y los que usan otros algoritmos como los
genéticos. Sin embargo, en las empresas es muy importante el uso del programa Solver del Excel
puesto que prácticamente cada microcomputadora de escritorio o portátil tiene Excel y cualquier
empleado la sabe usar.
Instalación del Solver en Excel 2010/2016.
Normalmente el Solver no está activo cuando se carga el Excel por que ocupa memoria. Por lo tanto,
lo primero que tenemos que hacer es revisar si tenemos activo el Solver, seleccionando la pestaña
Datos. Si el Solver está cargado veremos en el extremo derecho de la cinta:

Indica que el Solver

Fig. 1. Pestaña Datos mostrando el Solver activado. está activo

Si el Solver no está activo, hay que cargarlo, para lo que es necesario hacer lo siguiente: en la pestaña
Archivo seleccionar Opciones y en esta ventana seleccionar Complementos:

Fig 2. Localizando la opción Complementos para activar el Solver.

Después de seleccionar Complementos, en la parte inferior de la ventana aparece la opción de
Complementos de Excel y para acceder a ellos presionar el botón Ir…

Fig. 3. Activación de los complementos disponibles en Excel.

Finalmente, aparece la ventana con los complementos de Excel disponibles, entre ellos el Solver.
Para cargarlo hacemos click en el recuadro Solver y presionamos el botón Aceptar.

Fig4. Activación del complemento Solver.

Después de esto veremos en la pestaña Datos el Solver ya cargado.
Solución con Solver de un problema de programación lineal de mezclas.
Para aprender el uso del Solver, usaremos el siguiente problema de mezclas.
Una empresa al sur de Lima que se dedica a la comercialización de pollos requiere preparar una
mezcla diaria de 2,000 Kg. de alimentos balanceados, con requerimientos nutricionales en
proteínas, vitaminas y calcio. Para la preparación de la mezcla la empresa usa como materia prima:
harina de pescado, soya y productos carbonatados.
En la siguiente tabla se proporcionan los requerimientos de la mezcla alimenticia para los pollos así
como los costos de la materia prima y los contenidos de los nutrientes respectivos:

Materia Prima Costo Proteínas Vitaminas Calcio

S/. Kg. Unidades/Kg. Unidades/Kg. Unidades/Kg.
Harina de Pescado 10 1200 6000 3000
Soya 5 800 1000 1500
Productos carbonatados 15 40 100 8000
Requerimiento Mínimo 900 4000 2000
Requerimiento Máximo - - 6000

Formular un problema lineal para encontrar la mezcla más económica.

En esta oportunidad no discutiremos el proceso de formulación del modelo, puesto que nuestro
interés es aprender a usar el Solver. Así que proporcionamos la formulación del modelo:

Sea: X1 = Kg de harina de pescado a usar

X2 = Kg de soya a usar
X3 = kg de productos carbonatados a usar
MIN Z =10X1 + 5X2 + 15X3 Minimizar el costo de la mezcla
s.a.:
X1 + X2 + X3 = 2000 Peso requerido en la mezcla
0.6 X1 + 0.40 X2 + 0.02 X3 >= 900 Requerimiento mínimo de proteínas

3 X1 + 0.50 X2 + 0.05 X3 >= 4000 Requerimiento mínimo de vitaminas

1.5 X1 + 0.75 X2 + 4 X3 >= 2000 Requerimiento mínimo de calcio

1.5 X1 + 0.75 X2 + 4 X3 <= 6000 Requerimiento máximo de calcio
X1, X2, X3 >= 0

La solución de un problema en Excel tiene los siguientes pasos:

a. Desarrollo de una representación del modelo en la hoja electrónica.
b. Indicación de la celda que contiene la Función Objetivo.
c. Especificación de las variables de decisión, que se conocen como ¨celdas cambiantes¨
d. Especificación de las celdas que contienen las restricciones
e. Solución del modelo e interpretación de los resultados
A continuación, desarrollaremos cada uno de estos pasos.
En la hoja de la Fig. 5 se ha plasmado una representación del modelo descrito arriba con los
coeficientes de la función objetivo en las celdas C25:E25, los coeficientes de las restricciones en
las celdas C26:E30 y los valores de los lados derechos en las celdas H26:H30. En las celdas C31:E31
aparecerán los valores de X1, X2 y X3 una vez resuelto el problema. Estas celdas pintadas de
amarillo (solo por conveniencia), se conocen como ¨celdas cambiantes¨, porque en el proceso de
encontrar la solución irán cambiando hasta tener un valor final.

Fig. 5. Representación del modelo en hoja electrónica.

Observemos que hasta el momento solo hemos colocado los coeficientes de la función objetivo
y de las restricciones, pero no hemos expresado como son las expresiones matemáticas.
Tomemos como ejemplo la restricción del Peso requerido en la mezcla:
X1 + X2 + X3 = 2000

Lado izquierdo (LI) Lado derecho (LD)

de la restricción de la restricción

En la hoja electrónica en las celdas C26:E26 solo se han colocado los coeficientes de X1, X2 y X3,
pero no hemos establecido que la suma de 1 x X1 más 1 x X2 más 1 x X3, es decir, X1 +X2 + X3, es
el lado izquierdo de la restricción.
Para construir el lado izquierdo de la restricción del peso requerido de la mezcla tenemos que
incluir en la celda F26 la siguiente fórmula:
=C31*C26+D31*D26+E31*E26
De igual manera debemos construir los lados izquierdos del resto de restricciones en las celdas
[Link]
=C31*C27+D31*D27+E31*E27 (Lado izquierdo de restricción de proteínas)
=C31*C28+D31*D28+E31*E28 (Lado izquierdo de restricción de vitaminas)
=C31*C29+D31*D29+E31*E29 (Lado izquierdo de restricción del mínimo de Calcio)
=C31*C30+D31*D30+E31*E30 (Lado izquierdo de restricción de máximo de Calcio)
De manera análoga debemos construir la expresión de la función objetivo en la celda F25:
=C25*D31+D25*D31+E25*E31
En este momento ya estamos listos para ingresar el modelo al Solver. Para ello, hagamos un click
sobre la celda que tiene la función objetivo: F25. Luego seleccionamos la opción Solver en la
pestaña Datos e inmediatamente aparecerá la ventana de Parámetros del Solver:

Fig. 6. Activación del Solver y carga de la ventana de Parámetros del Solver.

Como se puede observar, el recuadro “Establecer objetivo:” ya tiene la celda que corresponde a
la función objetivo, en este caso la celda $F$25.
En el campo “Para:” marcar la opción “Min” porque vamos a minimizar el costo de la mezcla.
En el recuadro “Cambiando las celdas de variables:” hay que indicar las celdas que contiene a las
variables de decisión o “celdas cambiantes”, en este problema las celdas C31:E31 (las celdas en
amarillo). Hasta el momento la ventana de parámetros del Solver debe verse como en la Fig. 7.
 Fig. 7. Ingreso de la celda objetivo, tipo de optimización y las variables de decisión.

Ahora tenemos que indicar al Solver como están conformadas las restricciones, para ello usamos el
recuadro “Sujeto a las restricciones:”. Iniciamos el ingreso de las restricciones presionando el botón
“Agregar”, después de lo cual aparecerá la ventana “Agregar restricción” con tres campos para
ingresar la dirección del lado izquierdo, indicar el tipo de restricción =, > o < y poner la dirección del
lado derecho de cada restricción, como se muestra el caso de la restricción del peso de la mezcla en
la Fig. 8.

Fig. 8. Ingreso de las restricciones del problema.

De manera análoga podemos ingresar las demás restricciones, después de lo cual tendremos:

Fig. 9. Modelo ingresado en la ventana de paramentros de Solver.

Adicionalmente, notar que se tiene que marcar el cajón de “Convertir variables sin restricciones en
no negativas”, que es el equivalente a decir que los Xj >= 0. Así mismo, puesto que estamos en
programación lineal, el “Método de resolución:” debe ser el Simplex LP.
En este punto ya estamos en posición de presionar el botón “Resolver” para que el Solver inicie los
cálculos internos y nos comunique si el problema tiene solución si se ha presentado algún
inconveniente.

Como vemos en la Fig. 10, después de presionar “Resolver” el Solver nos muestra la ventana
“Resultados del Solver”, con el mensaje: “Solver encontró una solución. Se cumplen todas las
restricciones y condiciones óptimas” y en la hoja del modelo podemos apreciar que los valores de
las variables de decision X1, X2 y X3, tienen los valores de 1200, 800 y 0 respectivamente en las
celdas en amarillo.
 Fig. 10. Ventana de resultados del Solver.

Descripción de las opciones de cálculo del Solver

 Método “Simplex LP”

LP significa programación Lineal. Este método se usa para modelos con ecuaciones de primer orden. Las
ecuaciones de primer orden son aquellas en las que las variables de decisión están elevadas a la primera
potencia y su gráfico es una línea recta.
El Método Simplex LP producirá siempre soluciones óptimas globales para los problemas de optimización
que puede resolver.
 Método “GRG Nonlinear”
Se debe seleccionar este método cuando las ecuaciones formadas con las variables de decisión son no
lineales pero continuas.
Un problema de optimización no lineal (NLP, smooth nonlinear programming) es uno en el que la función
objetivo, o al menos una de las restricciones, es una función no lineal diferenciable de las variables de
decisión. Por ejemplo:
4 X12 +3 X23 + log X3
Es una función no lineal diferenciable, donde X1, X2 y X3 son variables de decisión. Las funciones no
lineales, a diferencia de las lineales, involucran variables que están elevadas a una potencia, pueden estar
multiplicadas o divididas por otras variables. Adicionalmente pueden usar funciones trascendentales
como la logarítmica, la exponencial, seno y coseno.
Los problemas no lineales y sus métodos de solución requieren que las funciones no lineales sean
continuas y que generalmente sean diferenciables con respecto a cada variable de decisión, es decir, que
las gradientes de la función sean continuas.
Una función es continua si no tiene quiebres cuando es graficada. Por ejemplo, la función del Excel
=IF(C1>40, D1, 5*D1) es discontinua, asumiendo que C1 es una variable de decisión, porque su valor
“salta” de D1 a 5*D1. De otro lado, la función Excel ABS(C1) es continua pero no diferenciable, su gráfico
es una “V” continua, pero su derivada es discontinua, ya que salta de -1 a +1 en C1 = 0.
 Método Evolutivo (Evolutionary)
Este método se debe usar si cualquiera de las funciones del modelo son discontinuas o no diferenciables.
Se llama método evolutivo por que utiliza algoritmos evolutivos.
Los problemas no diferenciables o discontinuos son los más complejos de resolver y le pueden tomar
mucho tiempo al Solver Excel. Adicionalmente, el método evolutivo solo puede encontrar “buenas”
soluciones y no soluciones locales o globales.
Las funciones de Excel que son no diferenciables son MIN, MAX y ABS. Las funciones discontinuas del Excel
son: INDICE, CONSULTAH, CONSULTAV, BUSCAR, ENTERO, REDONDEAR, CONTAR, BDMAX, BDMIN, SI,
ELEGIR, NO, Y, O, MAYOR O IGUAL.
Si cualquiera de estas funciones forma parte del modelo en Excel se debe usar el Método Evolutivo.
Establecimiento de Opciones del Solver
A continuación, se muestra las opciones generales para todos los métodos antes de ejecutar el Solver para
la mayoría de problemas. Esta ventana aparece cuando seleccionamos el botón de “Opciones” en el diálogo
Solver.

Establecimiento de Opciones – Todos los Métodos

Precisión de restricciones. Este valor establece la máxima diferencia entre la celda restringida y el valor
real de la restricción. Se considera que la restricción es satisfecha si la diferencia entre ambos valores es
menor o igual al valor considerado (0.000001 en el caso mostrado).
Usar escala automática. Esta opción hace que el Solver re-escale las variables, restricciones y la función
objetivo. Esto es necesario si en el problema existen parámetros de entrada que tienen diferencias de
magnitud importantes entre sus valores (por ejemplo, existen valores pequeños como 0.0001 y a la vez
grandes como millones). Esto hará que en los cálculos se pierda precisión y se produzcan errores
inesperados o condiciones para que el algoritmo se detenga. Consideramos una buena práctica tener
activada siempre esta opción.
Mostrar resultados de iteraciones. Esta opción hará que el Solver se detenga después de cada iteración
mostrando los resultados alcanzados en esa iteración. Generalmente no se solicita esta opción a no ser
que se tenga alguna razón particular para hacerlo. A continuación, se muestra el diálogo después de cada
parada en un modelo de mezclas.

Omitir restricciones de enteros. Esta opción se usa para “relajar” el problema lineal en enteros, es decir
omitir todas las declaraciones en enteros para las variables de decisión. En general es una buena práctica
correr un problema en enteros primero como un problema relajado, ya que, si la solución resulta en forma
natural en enteros, tendremos el beneficio de los reportes de sensibilidad.
Optimalidad de entero (%). Define la máxima diferencia en % entre el valor de la función objetivo de la
mejor solución del problema considerando las restricciones en enteros y el problema relajado. El valor
por defecto es 1%. Seleccionando 0% se garantiza obtener la mejor solución, pero puede tomar mucho
tiempo.
Límites para la solución (mostrado como “Resolviendo Límites”)
Tiempo máximo (segundos). El tiempo máximo de ejecución de un problema permitido al Solver. Sin
embargo, presionando la tecla ESC se puede detener la ejecución en cualquier momento. Si hacemos esto
el Solver preguntará si queremos detener la ejecución definitivamente o continuar.
Iteraciones. El máximo número de iteraciones (ejecuciones de prueba) que se le permite ejecutar la
Solver.
Restricciones de enteros y Evolutionary. Esta opción solo aplica si se utiliza el Método Evolutivo o si se usa
cualquier tipo de restricciones en enteros (entero, binario o “todos diferentes”).
Máximo de sub problemas. El número máximo de sub problemas permitido al Método evolutivo para
evaluar.
Máximo de soluciones viables. El número máximo de soluciones factibles de generar permitidas al Solver.
Establecimiento de opciones para el Método Evolutivo
A continuación, se explicará las opciones para resolver problemas con el Método Evolutivo.

Convergencia. La convergencia permite especificar qué tan cerca deseamos que la solución final del Solver
esté de la solución óptima. El valor de la convergencia establece la máxima diferencia en % que el 99% de
las últimas soluciones encontradas tienen entre ellas, antes de que el Solver presente el siguiente
mensaje: “Solver no puede mejorar la solución actual. Se cumplen todas las restricciones” y presente su
solución final.
A medida que el valor de la convergencia es más pequeño se requerirán más iteraciones para alcanzar
este valor, pero la solución proporcionada por el Solver estará más cercana a la solución óptima.
Tasa de Mutación. Es la tasa o frecuencia de cambio, a la cual “mutarán” (es decir cambiarán) las
soluciones (cada solución representa un individuo, y la generación es el conjunto de soluciones
consideradas en una iteración) que mantiene el Solver en el espacio de soluciones, a fin de incrementar
la probabilidad de no quedarse atrapado en un óptimo local y a la vez explorara otras regiones que
pudieran aportar mejores soluciones. La frecuencia de mutación es un número entre 0 y 1.
Tamaño de población. Este valor establece cuántos puntos muestreados del espacio de soluciones serán
mantenidos como soluciones candidatas en todo momento para cada variable de decisión. Este valor debe
ser un número entre 10 y 200.
Valor de inicialización aleatorio. El algoritmo evolutivo utiliza un generador de números aleatorios para
diferentes elecciones aleatorias, que inicia una serie de números aleatorios a partir de un número
 “semilla”. Si se ingresa un número o semilla, el Solver realizará las mismas elecciones cada vez que se
ejecute. Si se deja en blanco o cero, el Solver usará una semilla diferente para generar números aleatorios
cada vez que se ejecute, lo cual puede llevar a una solución diferente que puede ser mejor o peor que la
anterior.
Tiempo máximo sin mejora. Este es el tiempo en segundos permitido para que el Solver continúe
trabajando sin lograr una mejora significativa en la solución final. Después de transcurrido este límite de
tiempo, el Solver emitirá el mensaje ”Solver no puede mejorar la solución actual. Se cumplen todas las
restricciones”.
Requerir límites en variables. Esto indicará que el Solver trabajará solo si las variables de decisión tienen
límites superiores e inferiores establecidos. En general, el algoritmo evolutivo será más eficiente si las
variables de decisión tienen límites más estrechos.

Fuentes de información

1. Fylstra, D., Lasdon, L., Watson, J., & Waren, A. (1998). Design and use of the Microsoft Excel
Solver. Interfaces, 28(5), 29-55. DOI: 10.1287/inte.28.5.29
2. Harmon, M. (2012). Step-By-Step Optimization With Excel Solver-The Excel Statistical Master.
Excel Master Series.