Aplicando criterios simultáneamente a varias celdas con la herramienta Solver de Microsoft Excel

Una de las ventajas de sol ver es que permiten único paso seleccionar múltiples celdas para aplicar un criterio común a todas.

En el ejemplo vemos cómo aplicar a un rango de celdas la restricción igual a un valor determinado.

En este ejemplo tenemos en la columna c la suma de la columna a y b y lo repetimos para 16 filas.

Vamos a ver como en un único paso podemos aplicar una restricción a las 16 resultado para que sean iguales a un valor determinado.

En solver podemos indicar una única casilla con un valor cómo resultado del cálculo.

Sin embargo podemos aplicar restricciones a múltiples celdas.

En este caso aprovecharemos las restricciones para indicar que todas las celdas de la columna C tengan el valor 10.

Eso lo podemos conseguir seleccionando todo el rango e igualando lo al valor 10.

La restricción se aplicará para cada una de las celdas.

Cómo estáe conjunto de criterios vamos a conseguir que solver introduzca en las columnas A y C datos que provocarán el resultado en la  columna C igual a 10 para todas las filas.

Resolvemos y comprobamos que se cumple nuestro objetivo.

Podríamos tener mayor variedad de resultados si hubiésemos indicado un resultado mayor de 10.

Conseguir el valor más cercano a uno determinado en Solver

En ocasiones, como la del problema de los depósitos con valores de entrada enteros, es imposible obtener el resultado deseado.

En esos casos lo que deberemos intentar es obtener el valor más cercano.

En el vídeo muestro cómo conseguirlo con el mínimo y una restricción.

Partiendo del último ejercicio, veíamos que el resultado no se podía conseguir.

Esta era la configuración.

Para este conjunto de valores la respuesta de solver era que no podía encontrar una solución.

A estas alturas la única forma de conseguir el resultado era usar valores decimales.

Hoy vamos a realizar otra aproximación al problema.

En lugar de buscar exactamente un valor de 1000, intentaremos encontrar el valor más pequeño que cumpla la condición de que se usen valores enteros de entrada, y a la vez sea mayor de 1000.

Para ello aplicaremos en primer lugar una restricción.

La celda del resultado debe ser mayor o igual a 1000.

Y por otra parte, el valor de la celda resultado debe ser el menor posible.

Estos son los cambios aplicados.

En este caso comprobamos que sí es posible encontrar una solución.

Con la adecuada combinación de restricciones solver permite obtener el resultado deseado.

Restricciones en Solver

Solver es una gran herramienta pero si no avanzamos un poco más se queda en una herramienta similar a buscar objetivo.

En el ejemplo de ayer veíamos que el resultado era un número con una cantidad enorme de decimales.

En el vídeo podéis ver un ejemplo completo.

Excel en su herramienta solver permite aplicar restricciones que ayudan a limitar los valores que se pueden introducir en las celdas de entrada.

Pulsamos agregar y seleccionar las celdas de entrada de datos.

En este caso seleccionamos las celdas que contenía dos cantidades decimales. .

Y le aplicamos una restricción int. 

Lo que va a hacer es obligar a que los números sean enteros.

Ahora sí pulsamos en resolver, obtendremos un nuevo resultado, pero solo usando números enteros.

Al limitar todos los datos de entrada a números enteros, es posible que no exista una solución exacta.

En breve veremos cómo solucionarlo.

Cálculo de un depósito cilíndrico cualquiera con Solver en Microsoft Excel

Una de las características de Solver es que permite la modificación de más de una variable de entrada, lo que permite resolver problemas inabordable ande otra forma.

Un ejemplo clásico es el cálculo de un depósito de un volumen determinado, que dependa de dos o más variables.

Cómo ejemplo calcularemos depósitos cilíndricos que tengan un volumen de 1000 litros.

Este es un caso con infinitas soluciones.

Con Solver es posible seleccionar todas las celdas que queramos para cambiar los datos de entrada.

Para este ejemplo deberemos cambiar las celdas de las columnas A y B.

Se pueden seleccionar simplemente arrastrando.

Si pulsamos calcular, Microsoft Excel irá probando valores en las celdas seleccionadas hasta encontrar el resultado buscado.

Los datos introducidos, incluyen valores decimales y negativos.

Es posible indicar criterios para estos datos mediante la opción restricciones.

Solver básico, simulando buscar objetivo

En su configuración más básica Solver se puede usar de la misma manera que "Buscar objetivo".

En el vídeo veis el proceso.

Si hemos instalado el complemento de Solver, al iniciar Microsoft Excel, se nos mostrará un mensaje en la pantalla de carga.

A diferencia de "Buscar objetivo" que sólo permitía seleccionar dos celdas y un valor, en Solver encontramos muchas más opciones.

La adopción por defecto para establecer objetivo es Max.

Para realizar un primer ejemplo simulando "Buscar objetivo" seleccionaremos en su lugar la opción "Valor de"

En "Establecer objetivo" seleccionaremos la celda que contiene la fórmula del volumen de la esfera.

Y en "cambiando las celdas" seleccionamos la celda que contiene el dato del radio de la esfera.

Pulsamos resolver.

A pesar de ser está la.confuguracion más sencilla, ya se pueden apreciar diferencias con la herramienta "Buscar objetivo".

La diferencia más apreciable es que en los dos cuadros de selección que hemos usado para definir las celdas, se nos permite seleccionar más de una celda.

En breve iremos introduciendo diversos usos más avanzados de esta herramienta.

Instalación de Solver en Microsoft Excel

Para mí, la herramienta que realmente demuestra todo el poder de Microsoft Excel es Solver.

La mejor descripción de "Solver" es que es como "Buscar objetivo" pero con esteroides.

Es una herramienta que hay que conocer.

Al tratarse de un complemento que por defecto nonaprace instalado, no es ampliamente conocido.

Veamos cómo instalarlo.

Solver es un complemento creado por Microsoft y disponible en Microsoft Excel desde siempre.

Dado su poder, no entiendo porqué aparece sin instar por defecto.

En la animación podéis ver qué es un proceso muy simple.

La primera vez que se instala, resulta un poco más largo, ya que tiene que descargarse el componente.

Para activar solver, debemos acceder al menú Archivo.

Nota: Si aún tenéis la versión 2007, en esa versión, este menú fué sustituido por un botón circular sin texto y con un logotipo, pero se encuentra en la misma posición, el primero de la cinta de opciones.

Accedemos a la última entrada "Opciones"

 En la parte izquierda de la ventana, seleccionamos "Complementos"

En la parte inferior de la ventana de la derecha, pulsamos el botón "Ir..."

Se nos muestra una ventana con los cuatro complementos por defecto de Microsoft Excel, estando originalmente todos desmarcados.

Marcamos "Solver" 

Y pulsamos.el botón "Aceptar"

Comienza el proceso de instalación.

Si nunca ha estado instalado, resultará un poco más largo porque será preciso descargar el componente de Internet.

Una vez finalizado el proceso, aparecerá una nueva entrada en la cinta de opciones.

Se creará un grupo bueno denominado "Análisis" en el que encontraremos la herramienta "Solver"

Os lo iré presentando en próximos Post.

Formatear números con ceros a la izquierda en Microsoft Excel

En ocasiones, en Microsoft Excel, tenemos que tratar con datos numéricos que comienzan por cero.

Si este valor representa un dato numérico con el que vayamos a realizar un cálculo, los ceros a la izquierda se pueden ignorar, pero si esos datos representan valores sobre los que no se vayan a realizar cálculos numéricos podría ser importante mostrar esos ceros.

Un ejemplo serían los códigos de artículos, los cuales en casos solo incluyen dígitos numéricos, esto hará que Microsoft Excel tienda a aplicarles formato numérico, y por lo tanto no mostrar los ceros a la izquierda.

Una opción es transformar el número en texto.

Pero esta opción presenta un problema, ya que en ocasiones, si pinchamos en en una celda formateada como texto, al salir, Microsoft Excel la reconoce como número y cambia el formato, con lo cual volveríamos a perder los ceros a la izquierda.

Una opción mejor, es aplicar un formato personalizado a la columna, indicándole el número de dígitos que queremos que se visualicen, de esta forma siempre se mostrarán como mínimo el número de dígitos indicado rellenando a izquierda con ceros.

El procedimiento es sencillo, basta con seleccionar los columnas de los datos que deseamos que muestren los ceros a la izquierda, ir al formato de celdas, bien sea pulsando en el botón derecho en la columna y accediendo a la opción formato celdas o accediendo mediante la cinta de opciones.

 

Elegiremos la última opción "Personalizada".

 

La casilla tipo indicaremos tantos  ceros como digitos queremos que se muestren.

Justo encima se muestra como quedaría con el formato aplicado.

 

En la imagen podemos ver cómo quedaría aplicado el formato en una columna de códigos de artículos.

 

En la barra de fórmula se puede ver que el primer elemento no incluye el cero, pero sí que se muestra en la celda

Otra opción de acceder al menos de formato de celdas es desde la cinta de opciones pulsando la flecha que os muestro en la imagen.