Cómo encontrar combinaciones que equivalen a una suma dada en Excel? 2a Parte - Formulas

En la entrada anterior vimos la primera parte, de

Cómo encontrar combinaciones que equivalen a una suma dada en Excel? 1a Parte - Solver.

Vimos cómo resolver el problema mediante el complemento Solver.

Veamos cómo hacerlo.

Primero, debemos crear algunos nombres de rango y luego aplicar una fórmula matricial para encontrar las celdas se suman al valor objetivo. Para ello sigamos los siguientes pasos:

1. Seleccionamos la lista de números y definimos esta lista con un nombre de rango: Rango1 en el Administrador de nombres (dentro del menú Fórmulas) 

2. Después de definir un nombre de rango para la lista de números, entonces se necesita crear dos nombres de rango adicionales en el Administrador de nombres. Para ello hacemos clic en Fórmulas> Administrador de nombres, en el cuadro de diálogo Administrador de nombres, hacemos clic en el botón Nuevo.

3. En el cuadro de diálogo Nuevo nombre , ingresamos el Nombre Lista1 en el campo Nombre, y escribimos esta fórmula = FILA(INDIRECTO ("1:" & FILAS(Rango1))) (Rango1 es el nombre del rango que se creó en el paso 1).

4. Hacemos clic en Aceptar para volver al cuadro de diálogo Administrador de nombres, creamos otro nombre como antes, solo que esta vez lo llamamos Lista2 en el campo Nombre y escribimos esta fórmula = FILA(INDIRECTO ("1:"&2^FILAS(Rango1))) (Rango1 es el nombre del rango que se ha creado en el paso 1).

5. Después de crear los nombres de rango, aplicamos la siguiente fórmula matricial en la celda B1:

=SI(ESNUMERO(COINCIDIR(FILAS($1:1),SI(INDICE(RESIDUO(ENTERO((Lista2-1)/2^(TRANSPONER(Lista1)-1)),2),COINCIDIR(VERDADERO,MMULT(RESIDUO(ENTERO((Lista2-1)/2^(TRANSPONER(Lista1)-1)),2),Rango1)=$C$2,0),),TRANSPONER(Lista1)),0)),"X","")

Al ser una fórmula matricial se han de presionar Mayús + Ctrl + Intro a la vez. Despues copiamos al rango B2:B8, y se podrá ver que los números cuya cantidad total es 480 están marcados como X en la columna B

Notas:

1. En la fórmula larga anterior: Lista1, Lista2 y Rango1 son los nombres de rango que se crearon en los pasos anteriores, C2 es el valor específico al que desea sumar los números.

2. Si más de una combinación de valores tiene una suma igual al valor específico, solo se enumera una combinación.

Entradas relacionadas

1. Reconciliación de débitos y créditos utilizando el complemento Solver
2. Tutorial sobre Excel Solver

Quieres más trucos?

1. Cómo crear un gráfico con botones de casilla

2. Trucos con la segmentación de datos

3. Cómo permitir solo la entrada de caracteres alfanuméricos

4. Escondiendo/mostrando un gráfico de Excel con un sólo clic o al cumplirse una condición

5. Trucos utilizando CONTAR.SI

Te ha gustado el artículo?

No te olvides de mostrar tu gratitud hacia el autor (yo) mediante una de las 2 siguientes acciones:

1. Puedes dejar una donación pinchando en la siguiente imagen.

2. Puedes recomendar el artículo en Google haciendo clic en g+1 (justo después del artículo), también puedes compartirlo en Facebook, y/o dejar comentarios.

Quieres aprender más en Excel o tienes algún problema que necesita solución?

Puedo ofrecer servicios de consultoría así como cursos personalizados online. Mírate el siguiente enlace si estás interesado/a.

Consultoría Excel / Cursos de Excel

El Sabio de Excel busca colaborades del blog

Si te interesa compartir tu conocimiento sobre Excel de tal manera que puedas ayudar a otras personas, por favor mándame un email a elsabiodeexcel@gmail.com

Subscríbete y recibe todos las entradas por email

Para ello regístrate suministrando tu email en el sitio "Recibe los trucos en tu email" del menú de la derecha.

O sígueme por las redes sociales

Facebook, Twitter, Youtube y Google+