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.

Ordenación por niveles en Microsoft Excel

Todo el mundo sabe que en Microsoft Excel se puede ordenar alfabéticamente ya sea en orden creciente o decreciente.

Lo que no todo el mundo sabe es que se pueden aplicar ordenaciones anidadas de forma que una vez aplicado el primer criterio de orden, los elementos que sean iguales para ese criterio, se podrán ordenar por el segundo criterio.

Vamos a mostrar este producto con un clásico, un listado de nombres y apellidos.

Aunque existe un botón directo de para ordenar de forma ascendente (A-Z o 0-9) y otro en forma descendiente  (Z-A o 9-0) en este caso, vamos a ordenar mediante la opción "orden personalizado..."

Una vez que pulsamos en la opción "orden personalizado..." , se nos muestra una ventana en la que podemos seleccionar el campo y el criterio de ordenación que queremos aplicar.

 Para la primera parte del ejemplo vamos a ordenar solo por el primer apellido de forma ascendente.

El resultado quedaría como se muestra a continuación.

Vemos que hay mucha gente cuyo primer apellido es Arosa y podemos comprobar que el criterio de ordenación es un poco raro.

En este caso lo habitual es que a todos los que tengan mismo primer apellido se desordene por el segundo apellido, de esta forma conseguiremos tener juntos todos los hermanos.

Adicionalmente suele ser normal también finalmente ordenarlos por nombre.

Esto se consigue pulsando el botón agregar nivel.

Cómo vimos en el ejemplo hemos añadido dos niveles.

El segundo nivel ordenará por el segundo apellido todos los que tengo un primer apellido coincidente.

Y del tercer nivel ordenará por nombre todos aquellos que tengan primer y segundo apellido coincidente.

El resultado se muestra a continuación.

Adicionalmente es posible cambiar el criterio de ordenación de alguno de los niveles, por ejemplo ordenar por nombre de forma descendente.