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.

Centrar textos en varias celdas sin combinar

Personalmente si hay algo que odio de Microsoft Excel son las celdas combinadas.

A pesar de ser un recurso muy utilizado por algunas gentes son incomodisimas a la hora de aplicar filtros.

Hoy os muestro una alternativa qué consige el mismo efecto sin tener que combinar las celdas.

Se trata de la opción "centrar en la selección."

 

La opción típica para conoce quiere poner un título como una varias columnas es seleccionar todas las celdas y aplicar combinación de celdas

 La combinación de celdas la tenemos disponible desde el menú inicio.

Esto transforma las celdas seleccionadas en una única celda.

Usualmente se utiliza para centrar un texto en varias columnas.

 

Otra opción que produce el mismo efecto estético es "centrar en la selección."

Seleccionamos las celdas que queremos usar como título.

Botón derecho. formato de celdas.

Además de las acciones habituales y más conocidas existe una poco conocida denominada centrar en la selección.

Esta opción hace que el texto aparezca centrado en las celdas seleccionadas pero sin llegar a combinarlas en una única celda.

 De esta forma se consigue el mismo resultado estético pero evitando la problemática de interferir la gestión de los filtros que tiene las celdas combinadas.

En la imagen podéis ver cómo quedaría un texto alineado en el centro de las columnas ABC.

Cálculo de previsiones con Microsoft Excel

Otra de las herramientas de Microsoft Excel es la previsión.

Excel puede, a partir de un grupo de datos basados en fechas, predecir en un gráfico los valores más probables. 

Partimos de un listado de fechas y datos de ventas.

En el grupo Datos, Previsión, encontramos la herramienta Previsión.

Automáticamente se genera un gráfico, que se prolonga hacia el futuro intentando predecir los valores futuros.

El gráfico por defecto es de líneas, pero se puede seleccionar un gráfico de barras.

También es posible elegir la fecha de Final de pronóstico.

Cambiar esta fecha final, aumenta el tamaño del gráfico.

Evidentemente cuanto mas alejada es la fecha en el futuro mayor es la desviación que se puede obervar.

Además la herramienta cuenta con diversos parámetros configurables, como puede ser la fecha de inicio del pronóstico, que no tiene porqué coincidir con el inicio del gráfico.

Finalmente también es posible marcar el check "Incluir estadísticas de previsión" para conocer los valores que se han usado para realizar la previsión.

En la animación muestro el proceso completo.

Resolver sistema de ecuaciones usando buscar objetivo

Normalmente los ejemplos que vienen en los libros son simplificaciones a veces excesivas.

Hoy os traigo un ejemplo un poco más complicado de la opción que se enseñaba ayer buscar objetivo.

En este caso la celda que vamos a fijar con un valor no está relacionada directamente con la celda que se va a cambiar.

En medio habrá dos fórmulas adicionales.

Para usar buscar objetivo el único requisito es que cambiando la cerda que nos indica la app la herramienta se modifique el valor de la celda objetivo, pudiendo haber cualquier cantidad de fórmulas intermedias.

Cómo ejemplo hoy resolveremos una ecuación.

La celda amarilla representará el valor de la incógnita x.

En la celda verde representaremos la diferencia de las dos partes de la ecuación, para que la cooperación se cumpla la diferencia debe ser cero.

Os muestro las fórmulas que estoy usando.

Si dejo la casilla amarilla de la x vacía este dato cogerá el valor 0 y el resto de las fórmulas tendrán los valores que se muestran a continuación.

Como veis para el valor 0 de la x un lado de la igualdad vale menos uno y el otro vale 8 por lo tanto este valor de x no cumple la igualdad.

Como no sabemos resolver este tipo de ecuaciones podemos afrontarlo asignando valores a la x primero el 0 después el uno después el dos etcétera etcétera etcétera.

En este caso vamos a pedirle a Microsoft Excel que haga ese proceso por nosotros el de prueba-error usando la herramienta buscar objetivo.

El animación veis el proceso y el resultado obtenido.

La igualdad se cumple para un valor de x igual a -3.