Usando SOLVER para hallar los sumandos de un total

En ocasiones he visto en diversos foros y grupos (Facebook) de Excel que algunos/as usuarios/as tienen la necesidad de hallar los números cuya suma sirva para obtener un total buscado, es decir, necesitan hallar los sumandos de una suma o total.  Aunque hay varias formas de hacerlo (ninguna es simple) en esta ocasión vamos a usar el complemento SOLVER. Manos a la obra.

Empecemos colocando algunos valores en el rango A1:A10.

Por si no se dieron cuenta la suma de todos esos valores da 960 por ende, el total que buscaremos no debe superar ese número.

Ahora nos vamos a situar en A1 y le daremos un formato condicional con la opción de “Nueva regla de formato” y ahí vamos a elegir “Utilice una fórmula que determine las celdas para aplicar formato” y en “Dar formato a los valores donde esta fórmula sea verdadera” vamos a colocar lo siguiente:

=B1=1

Esto es lo que deberíamos tener hasta el momento:

Ahí mismo con el botón “Formato” podemos aplicar, por ejemplo, un fondo de color rojo.

Le damos clic al botón “Aceptar” y luego del mismo modo al cuadro de dialogo anterior. Para no estar dando Formato Condicional a celda por celda, solo copiaremos el formato de este modo:

Ahora, para nuestro ejemplo, vamos a introducir la siguiente fórmula en B11:

Ahora activaremos la herramienta SOLVER, y en el cuadro de dialogo correspondiente vamos a llenar así:

Yo he colocado 600, pero evidentemente ustedes elegirán el número total de su conveniencia. Ahora le daremos clic al botón “Agregar” y en el nuevo cuadro de diálogo dejaremos así las opciones:

Hemos elegido “bin” (de binario) para que los resultados sean 0 (cero) o 1 (uno). Démosle clic a “Aceptar” y obtendremos esto:

Ahora clic a “Resolver” y en el siguiente diálogo igual en “Aceptar”.

Si todo salió bien las celdas con los valores que suman 600, estarán resaltadas con fondo de color rojo.

Prueben con diversos totales y verán que consiguen resultados similares. Ah, eso sí, el total debe ser un número cuyos sumandos se encuentren sí o sí en las alternativas a marcar; asimismo, si hay más alternativas cuyos sumandos den el mismo resultado, la herramienta usada en esta ocasión solo nos brindará un grupo. Igual sé que les será útil.

Abraham Valencia
Lima, Perú

Curiosidades en Excel: Valores ocultos en celdas combinadas

Pasan los años y diferentes versiones y nunca, pero nunca, Excel deja de sorprendernos. Hace pocos días en uno de los foros en los que siempre participo, un usuario mencionaba que, en unas celdas combinadas, al ver la celda y la barra de fórmulas, en ambos casos se mostraba un solo valor, pero que al usar macros y/o devolver con fórmulas el valor de ambas celdas por separado (como si no estuviesen combinadas), pues devolvía dos valores distintos ¿Qué cómo es eso? Para que se entienda mejor, antes de seguir con este artículo, descarguen y miren el siguiente archivo: Enlace.

Como ven, las celdas A1 y A2 están combinadas y en dicha combinación se observa la frase “Hola, yo soy visible”. A pesar de ello, en las celdas B1 y B2 verán dos frases distintas, en la primera la misma de las celdas combinadas y en la segunda “Hola, yo estoy oculto”, a pesar de que cada una de las segundas está vinculada a una de las primeras.

¿Vieron que en B2 hay un vínculo a A2 y que a pesar de que A2 está combinada con A1 tiene un valor propio?  Si aún tienen dudas, miren bien la celda combinada, no hay nada más que la primera frase.

Ah, si aún tienen dudas no olviden que al combinar celdas solo se mantiene el valor de la primera (superior y/o superior izquierda del grupo de celdas que se combina). Se supone que descargaron el archivo y están viéndolo, pero si aun así persisten las dudas, como ven no tiene macros y no hay más celdas con valores que las vistas hasta el momento… busquen si desean.

¿Ahora sí convencidos/as? Pues veamos cómo lo logré hacer. Primero en A1 (o en donde deseen) escriban algo, lo que sea.

Ahora inserten un TextBox (ActiveX) en la hoja (en cualquier lugar).

Luego en la propiedad “LinkedCell” del TextBox escriban “A2” (obviamente sin las comillas).

Ahora combinen A1 y A2. Como ven, la frase ingresada es el único valor en la celda(s).

Ahora, escriban lo que quieran en el TextBox (evidentemente previa salida del “Modo diseño”). Verán que el texto ingresado no se refleja en la celda combinada.

Ahora eliminen el Textbox, pero ¡sin borrar el texto que contiene antes! Solo elimínenlo tal cual lo habían dejado (no olviden facilitarse la vida eliminando el TextBox en “Modo diseño”). Ahora en la celda que deseen coloquen un vínculo a A1 (=A1).

El resultado será este

Ahora agreguen un vínculo a la celda A2 y miren el resultado:

Como ven, a pesar de haber eliminado el TextBox y de estar combinadas las celdas, en A2 se mantiene el valor ingresado en el TextBox dado que estaban vinculados, es más, al descombinar las celdas veremos esto:

Y listo, misterio resuelto, aunque más que misterio, una curiosidad más del gran Excel. Hasta la próxima.

Abraham Valencia
Lima, Perú

La hora mundial en Excel a través de Power Query

Hay muchas cosas curiosas y/o interesantes que se pueden hacer con Excel y definitivamente la inclusión de Power Query ha incrementado dichas posibilidades. En esta ocasión vamos a aprender a tener en una hoja de Excel las diferentes horas que hay en los países del mundo.

De las páginas web que miré, la siguiente es la que me pareció mejor para este tema: Enlace.

Si miran bien, hay un detalle que nos interesa y es la propiedad de dicha web que nos da la hora por países. La activaremos para copiar el enlace que nos será más útil en este caso.

https://www.horlogeparlante.com/reloj-mundial.html?sort=country

Ahora en nuestra hoja de Excel vamos a ir a la pestaña “Datos” y en el grupo “Obtener y transformar datos” vamos a darle clic al botón “Desde la web”.

En el cuadro de diálogo que sale ingresaremos el enlace comentado líneas arriba y le daremos clic al botón “aceptar”.

En el siguiente cuadro, no nos hagamos un mundo y solo démosle clic a “Conectar”.

En el cuadro “Navegador” vamos a elegir la segunda opción “Hora mundial: La hora actual en todos…” y le daremos clic al botón “Transformar datos”.

Se abrirá el editor de Power Query y ahí como la primera columna no nos interesa, le daremos clic derecho a su encabezado y elegiremos “Quitar”.

Ahora, podemos darle doble clic a cada encabezado y colocar los nombres que deseemos.

.

Una vez hecho eso, vamos a la pestaña “Inicio” y le damos clic al botón “Cerrar y cargar”.

f

Si hicimos todo bien, en nuestra hoja tendremos algo así:

Para terminar, vamos a la pestaña “Datos” de nuestro Excel y en el grupo “Consultas y conexiones” dale clic al botón “Actualizar todo” y en la lista que se despliega dale clic a “Propiedades de conexión…” y en el diálogo que se mostrará vamos a ir a la pestaña “Uso” y ahí activen la opción “Actualizar cada” y coloquen 1 minuto (o lo que deseen).  También activen la opción “Actualizar al abrir el archivo”. Clic al botón “Aceptar” y listo, ya esta listo nuestro archivo. Cada minuto, o lo que hayamos elegido, y al abrir el archivo, tendremos la hora de todos los países de nuestras listas.

Ah, por supuesto que se puede ordenar alfabéticamente, pero eso ya es tarea para ustedes. Hasta la próxima.

Abraham Valencia
Lima, Perú

Descargue el archivo aquí