Todo lo que siempre quisiste saber y nunca te atreviste a preguntar sobre las herramientas “Buscar Objetivo” y “Solver” de Excel

Ya sabes que Excel nació para calcular, con suma facilidad realiza operaciones que previamente hemos diseñado. Una hoja de cálculo tiene dos tipos de datos:

• Por un lado están las variables, son celdas donde los usuarios insertan valores.
• Por otro lado están las fórmulas que son celdas destinadas a devolver resultados operando con las variables.

El “juego” habitual de una hoja de cálculo es: Yo le doy datos y me devuelve resultados, es decir, yo le doy una cantidad, un precio y un descuento para obtener el total de la factura, yo le doy el salario base, el IRPF, el desplazamiento, las dietas,… y averiguo el total a cobrar en la nómina. Lo normal es que los usuarios finales toquen en las celdas donde hay variables pero que esté prohibido tocar (normalmente estarán protegidas) en las celdas donde hay resultados puesto que habrá en ellas fórmulas diseñadas previamente.

Pues bien, a menudo vas a necesitar que el juego sea al revés, sabrás cuanto quieres que salga el total de la factura y querrás que Excel te diga la cantidad, querrás que salga en el total a cobrar de la nómina (2.000) y querrás que Excel te diga el salario base. En definitiva, ¿qué ocurre cuando queremos que el resultado de una fórmula de un valor concreto?

La herramienta: Buscar Objetivo

El problema es que no podemos escribir el valor que queremos en una celda que contiene una fórmula, dejaría de funcionar la hoja de cálculo. Pues bien, para este fin Excel tiene una herramienta llamada Buscar Objetivo. Te planteo el siguiente ejemplo:

Esto es una hoja de cálculo con 3 variables (B3, B4 y B5) y un resultado (B7). El usuario final podrá probar a cambiar las variables y verá cómo cambia el resultado.

Pero ¿Qué ocurre si tiene pensado gastarse 55 euros? ¿Cuánto tendría que valer la distancia o el precio del litro o el consumo del coche?

¿Ves muy extraña la siguiente pregunta?: Tengo para gastar en un viaje 55 euros, ¿Cuántos kilómetros puedo recorrer? Es un planteamiento muy frecuente sin embargo para resolverlo o lo haces por “la cuenta de la vieja” (probando y probando en la celda B3 hasta que el resultado se acerque a lo que quieres) o usas la herramienta Buscar Objetivo.

Buscar Objetivo sirve para resolver en Excel esa situación a revés de lo habitual, “sé el resultado que quiero obtener y quiero que Excel me calcule una variable”.

Si utilizas Excel 2003 “Buscar Objetivo” se encuentra en el menú Herramientas, Buscar Objetivo. Si utilizas Excel 2007 o 2010 tendrás que buscarlo en la ficha Datos, grupo Herramientas de datos, Análisis Y si, Buscar Objetivo.

En cualquier caso te aparecerá el mismo cuadro de diálogo donde tendrás que indicar 3 cosas:

• Definir la celda: deberás poner la celda objetivo, es decir, la celda que contiene la fórmula y por tanto no puedes escribir en ella. En nuestro ejemplo se trata de la celda B7.
• Con el valor: le deberás indicar cuánto quieres que sea el resultado, tendrás que decirle cuál es tu objetivo. En nuestro caso queremos que la celda B7 tenga un valor de 55 euros.
• Para cambiar la celda: tendrás que decir qué celda variable quieres que Excel averigüe para conseguir que la B7 valga 55. En el ejemplo sería la distancia, la B3.

Ahora que el cuadro de diálogo está relleno, y antes de dar a Aceptar, míralo e interprétalo de la siguiente manera: “Quiero que Excel cambie la celda B3 para conseguir que la B7 valga 55”. A esto le llamo yo hacer que Excel calcule al revés (en matemáticas sería despejar la X).

Al dar a Aceptar debería salir lo siguiente:

Excel ha calculado la celda B3 dándole tú el resultado que querías obtener. En la celda B3 hay más decimales de los que aparecen a simple vista, colócate en la celda B3, mira en la barra de fórmulas y verás… un número un poco complicado para haberlo conseguido tú por la “cuenta de la vieja”.

Si has entendido la filosofía de Buscar Objetivo entenderás la herramienta Solver.

La herramienta: Solver

«Buscar Objetivo» tiene un par de carencias. Sólo le puedes pedir que “juegue” con una variable y además las variables no tienen límites. En nuestro ejemplo anterior le podemos decir ¿Cuánto tendría que consumir mi coche para recorrer 925km con 10 euros? El buscar objetivo diría que si tu coche gastara 0,85 litros a los 100km podrías recorrer los 925km con 10 euros. Pero todos sabemos que de momento eso no es imposible, un coche no puede gastar eso sin embargo al buscar objetivo no se le puede explicar eso, no pone límites.

Por otro lado estaría bien que para llegar a un objetivo Excel pudiera “jugar” con varias variables, por ejemplo que para que un viaje cueste menos cambiara la distancia, el consumo y el precio del combustible. En este caso tanto el consumo como el precio del litro no es algo que podamos decidir sobre ellos con lo que el ejemplo no nos valdría pero esa sería la idea.

La finalidad de Solver es la misma que la del Buscar Objetivo pero con la posibilidad de que intervengan más de una variable y pudiendo poner restricciones a las mismas. La verdad es que un Solver está pensado para problemas más complejos donde las variables que intervienen son muchas y las posibles soluciones son varias, en el mundo matemático serviría para resolver ecuaciones.

Antes de continuar he de decirte que para usar la herramienta Solver debes activar un complemento que no viene por defecto habilitado. En Excel 2003 lo encontrarás en Herramientas, Complementos. En Excel 2007 lo puedes activar desde botón de office, Opciones de Excel y en 2010 desde Archivo, opciones. En 2007-2010 debes continuar entrando en la opción de complementos que aparece en el cuadro de diálogo y dando a la opción “Ir…” que aparece en la parte inferior de la pantalla.

En cualquiera de los casos debes activar la casilla correspondiente a Solver:

Una vez habilitada la herramienta podrás usarla desde:

• Excel 2003: Herramientas, Solver
• Excel 2007-2010: Ficha Datos, grupo Análisis

Pongamos un ejemplo parecido al anterior aunque como te he contado Solver esté pensado para casos más complejos de análisis matemáticos. En este caso también tenemos un ejemplo en el que hay tres variables y un resultado.

Piensa en la siguiente cuestión: Quiero pagar 600 euros de mensualidad, ¿cuánto puedo pedir? Esto es un claro ejemplo de Buscar Objetivo, lo podrías resolver como hemos visto anteriormente.

Pero y si el problema fuera el siguiente: Quiero pagar 600 euros de mensualidad. Me gustaría que Excel jugara con las 3 variables (cantidad, interés y plazos) para conseguirlo pero teniendo en cuenta que el interés no debe bajar del 8% y que no quiero estar pagando más de 180 plazos. Aquí podemos aplicar el siguiente Solver:

Al dar a resolver puede ocurrir que haya o no solución (como en cualquier ecuación). En nuestro caso sí la hay e incluso podrás pedir que saque un informe detallando los cambios que ha realizado Excel para hallar la solución. Si no eres experto en matemáticas (yo tampoco) te aconsejo el informe de “respuestas” donde se detalla claramente lo que valían cada una de las variables antes y después del cambio. De todas formas no es obligatorio elegir un informe.Por último decirte que este tutorial sobre Buscar Objetivo y Solver está destinado a usuarios que no buscan la connotación matemática que tiene el asunto (que de hecho la tiene). Por ejemplo si en el cuadro de diálogo de Solver haces clic en el botón Opciones podrás ver una demostración de poderío matemático que no cubriremos en este tutorial.

No obstante, si te interesa el trasfondo matemático que tiene Solver, hay varios sitios en internet donde se habla de ello por ejemplo “La herramienta Solver de Excel“ de la Universidad de Burgos o el artículo “Solución de sistemas de ecuaciones con con Excel” publicado en monografias.com.

Como siempre espero que te sirva y recuerda que puedes hacer uso de este y de todo el material de este blog de forma gratuita siempre que no te lucres con él.

Academias, profesionales, empresas, organizaciones o colectivos pueden ponerse en contacto conmigo antes de hacer un uso extensivo de estos materiales.

Suscríbete al blog si quieres tener noticias mías por correo cada vez que publique un nuevo tutorial, manual, artículo, truco, ejercicio o resuelva una duda.