¿Te has encontrado alguna vez frente a la pantalla de Excel, rodeado de números y buscando la combinación perfecta que sume un valor específico? ¡No estás solo! La búsqueda de soluciones numéricas puede ser un desafío, pero también una aventura fascinante. En este artículo, te guiaremos a través de las herramientas y técnicas que puedes utilizar para desentrañar todas las combinaciones posibles que suman a un número determinado. Ya seas un profesional que utiliza Excel en su trabajo diario o un entusiasta que busca mejorar sus habilidades, descubrir cómo hacer que Excel trabaje para ti te abrirá un mundo de posibilidades. ¡Prepárate para convertirte en un maestro de la suma y encontrar soluciones donde menos lo esperas!
¿Alguna vez has necesitado encontrar todas las combinaciones de números que sumen a un valor específico en Excel? Si es así, estás en el lugar correcto. En este artículo, te mostraremos cómo puedes utilizar esta poderosa herramienta para realizar este tipo de cálculos de manera rápida y eficiente. Descubre cómo sacar el máximo provecho de Excel para encontrar todas las combinaciones posibles y alcanzar tus objetivos numéricos. ¡Sigue leyendo para aprender más!
3 formas de encontrar todas las combinaciones de un conjunto de números que suman un total específico.
Encontrar combinaciones de valores que sumen una suma determinada es un problema común en el análisis de datos. Por ejemplo, es posible que desee conocer todas las combinaciones posibles de artículos que se pueden comprar con un presupuesto determinado, o todas las formas posibles de asignar recursos para cumplir con ciertos requisitos. En este artículo, exploraremos cómo utilizar Excel Solver y VBA para realizar la tarea. También discutiremos algunos posibles obstáculos y limitaciones de cada enfoque que pueden surgir al manejar grandes conjuntos de datos o restricciones complejas.
Encuentre una combinación de números igual a una suma dada con Excel Solver
Lamentablemente, ninguna de las funciones incorporadas de Excel puede ayudarle a identificar los números que suman un total determinado. Afortunadamente, Excel proporciona un complemento especial para resolver problemas de programación lineal. El complemento Solver se incluye con todas las versiones de Excel, pero no está habilitado de forma predeterminada. Si no está familiarizado con esta herramienta, aquí tiene un excelente artículo sobre cómo agregar y usar Solver en Excel.
Con el complemento Solver activado en tu Excel, continúa con los siguientes pasos:
Para esto, ingrese su conjunto de números en una columna (A3:A12 en nuestro caso) y agregue una columna en blanco a la derecha de sus números para los resultados (B3:B12). En una celda separada (B13), ingrese una fórmula SUMAPRODUCTO similar a esta:
=SUMPRODUCT(A3:A12, B3:B12)
>
Sobre el Datos pestaña, en la Análisis grupo, haga clic en el solucionador botón. 
>
En el Parámetros del solucionador cuadro de diálogo, configure las celdas objetivo y variable:
- En el Establecer objetivo ingrese la dirección de la celda de fórmula (B13).
- En el A sección, seleccione Valor de y escriba el valor de suma deseado (50 en este ejemplo).
- En el Cambiando celdas variables seleccione el rango que se completará con los resultados (B3:B12).
>
Para especificar las restricciones, es decir, las restricciones o condiciones que deben cumplirse, haga clic en el botón Agregar botón. En el Agregar restricción ventana de diálogo, seleccione el rango resultante (B3:B12) y elija papelera de la lista desplegable. La restricción se establecerá automáticamente en binario. Cuando termine, haga clic en Aceptar. 
>
Cuando lo llevan de regreso al Parámetro del solucionador ventana de diálogo, revise su configuración y haga clic en el Resolver botón. 
>
Unos segundos (o minutos) más tarde, el Resultados del solucionador Aparecerá el cuadro de diálogo. Si tiene éxito, seleccione el Mantenga la solución Solver y haga clic en Aceptar para salir del cuadro de diálogo. 
>
Como resultado, tendrá 1 insertado junto a los números que suman la suma especificada. No es una solución fácil de usar, pero es lo mejor que Excel puede hacer.
Para fines de visualización, he resaltado las celdas que dan la suma deseada en verde claro:
>
Limitación: Excel Solver puede encontrar, como máximo, solo una combinación de números que sea igual a una suma específica.
Encuentre todas las combinaciones que igualen una suma determinada con una función personalizada
Para obtener todas las combinaciones posibles de un conjunto determinado de números que suman un valor determinado, puede utilizar la función personalizada a continuación. Si es nuevo en las UDF, encontrará mucha información útil en esta guía: Cómo crear funciones personalizadas definidas por el usuario en Excel.
Función personalizada para encontrar todas las combinaciones que equivalen a una suma determinada
Opción Pública Explícita Función BuscarSumaCombinaciones(rngNumbers Como Rango, lTargetSum Como Largo)
Oscuro arNúmeros() Como Largoparte() Como Largo
Oscuro arRes() Como Cadena
Oscuro indi Como Largo
Oscuro celdaCurr Como Rango
Redim arRes(0)
Si rngNumbers.Count > 1 Entonces
Redim arNumbers(rngNumbers.Count – 1) indI = 0
Para Cada celdaCurr En rngNumbers arNumbers(indI) = CLng(cellCurr.Value) indI = indI + 1
Próximo celdaCurr
Llamar SumUpRecursiveCombinations(arNumbers, lTargetSum, part(), arRes())
Fin Si
Redim Preservar arRes(0 A UBound(arRes) – 1) FindSumCombinations = arRes
Fin Función
Privado Sub SumarCombinacionesRecursivas(Números() Como Largoobjetivo Como Largoparte() Como Largo, Por referencia arRes() Como Cadena)
Oscuro s Como Largoi Como Largoj Como Largonúmero Como LargoindRes Como Largo
Oscuro restante() Como LargoparteRec() Como Largo
s = MatrizSuma(parte)
Si s = objetivo Entonces
indRes = UBound(arRes)
Redim Preservar arRes(0 A indRes + 1) arRes(indRes) = ArrayToString(parte)
Fin Si
Si s > objetivo Entonces Salida Sub
Si (No No Números) <> 0 Entonces
Para yo = 0 A UBound(Números) Borrar resto() num = Números(i)
Para j = yo + 1 A UBound(Números) AddToArray restante, Números (j)
Próximo j Borrar partRec() CopyArray partRec, part AddToArray partRec, num SumUpRecursiveCombinations restantes, target, partRec, arRes
Próximo i
Fin Si
Fin Sub
Privado Función ArrayToString(x()) Como Largo) Como Cadena
Oscuro norte Como Largoresultado Como Cadena
resultado = x(n)
Para norte = LBound(x) + 1 A UBound(x) resultado = resultado & «,» & x(n)
Próximo n ArrayToString = resultado
Fin Función
Privado Función MatrizSuma(x() Como Largo) Como Largo
Oscuro norte Como Largo
Matriz suma = 0
Si (No No x) <> 0 Entonces
Para norte = LBound(X) A UBound(x) MatrizSuma = MatrizSuma + x(n)
Próximo norte
Fin Si
Fin Función
Privado Sub AgregarAArray(arr() Como LargoX Como Largo)
Si (No No arreglo) <> 0 Entonces
Redim Preservar llegar(0 A UBound(arr) + 1)
Demás
Redim Preservar llegar(0 A 0)
Fin Si
llegar(UBound(arr)) = x
Fin Sub
Privado Sub Copiarmatriz(destino() Como Largofuente() Como Largo)
Oscuro norte Como Largo
Si (No No fuente) <> 0 Entonces
Para norte = 0 A UBound(fuente) Destino AddToArray, fuente(n)
Próximo norte
Fin Si
Fin Sub
Cómo funciona esta función
La función principal, Buscar combinaciones de suma, llama a algunas funciones subsidiarias que implementan subtareas más pequeñas. La función nombrada SumUpCombinaciones recursivas ejecuta el algoritmo central que encuentra todas las sumas posibles en el rango especificado y filtra aquellas que alcanzan el objetivo. El matriz a cadena La función controla la forma de las cadenas de salida. Tres funciones más (matriz suma, Agregar a matrizy Copiar matriz) son responsables de procesar matrices intermedias: cada vez que creamos una matriz temporal, le agregamos un elemento de la matriz de origen y verificamos si se alcanza la suma objetivo. El algoritmo central se toma de este hilo de Stackoverflow¡Gracias chicos por compartir!
Sintaxis
Desde la perspectiva del usuario, la sintaxis de nuestra función personalizada es tan simple como esta:
FindSumCombinations(rango, suma)
Dónde:
- Rango es el rango de números a probar.
- Suma es la suma objetivo.
¡Nota! La función personalizada solo funciona en Dynamic Array Excel 365 y 2021.
Cómo utilizar la función FindSumCombinations:
Por ejemplo, para encontrar todas las combinaciones posibles de números en el rango A6:A15 que sean iguales a la suma en A3, la fórmula es:
=FindSumCombinations(A6:A15, A3)
Al igual que con cualquier otra función de matriz dinámica, ingresa la fórmula en solo una celda (C6 en la imagen a continuación) y completa los resultados en tantas celdas como sea necesario. De forma predeterminada, las cadenas separadas por comas se muestran en una fila:
>
Para devolver los resultados en una columna, ajuste la función personalizada en TRANSPONER de esta manera:
=TRANSPOSE(FindSumCombinations(A6:A15, A3))
>
Para generar las cadenas en forma de una matriz encerrada entre llaves, modifique la función ArrayToString de la siguiente manera:
Privado Función ArrayToString(x()) Como Largo) Como Cadena
Oscuro norte Como Largoresultado Como Cadena
resultado = «{« & x(n)
Para norte = LBound(x) + 1 A UBound(x) resultado = resultado & «,» & x(n)
Próximo n resultado = resultado & «}»
ArrayToString = resultado
Fin Función
Los resultados serán similares a este:
>
Limitación: Esta función personalizada solo funciona en Dynamic Array Excel 365 y Excel 2021.
Obtenga todas las combinaciones que equivalen a una suma determinada con la macro VBA
La función personalizada descrita anteriormente devuelve las combinaciones de números como cadenas. Si prefiere tener cada número en una celda separada, esta macro le resultará útil. El código está escrito por otro experto en Excel, Alexander Trifuntov, que ha estado ayudando activamente a los usuarios a resolver varios problemas de Excel en este blog.
Comience agregando el siguiente código a su libro de trabajo. Para conocer los pasos detallados, consulte Cómo insertar código VBA en Excel.
Macro para encontrar todas las combinaciones que suman un valor determinado
Público MatrizRef1 Como Cadena
Público DS Como Variante
Público Suma objetivo Como Largo
Público ColObjetivo Como Entero
Público Fila de destino Como Entero
Sub Combinación() UserForm1.Show
Fin Sub
Función Código Gris(Artículos Como Variante) Como Cadena
Oscuro CódigoVector() Como Entero
Oscuro i, kk, rr, col1, fila1, n1, e Como Entero
Oscuro más bajo Como Enterosuperior Como Entero
Oscuro Sublista Como Cadena
Oscuro NuevoSub Como Cadena
Oscuro hecho Como Booleano
Oscuro Paso impar Como Booleano
Oscuro SSS
Oscuro Matriz objetivo() Como Cadena
kk = TargetCol rr = TargetRow col1 = TargetCol + 3 fila1 = TargetRow OddStep = Verdadero
inferior = LBound(Artículos) superior = UBound(Elementos) Celdas(rr – 1, kk) = «Resultado»
Celdas(rr – 1, kk + 1) = «Suma»
Celdas(rr, kk + 1) = SumaObjetivo Celdas(rr – 1, kk).Font.Bold = Verdadero
Celdas (rr – 1, kk + 1). Fuente. Negrita = Verdadero
Redim CódigoVector(inferior A superior) ‘empieza todo 0
Hacer Hasta hecho NuevoSub = «»
Para yo = inferior A superior
Si VectorCódigo(i) = 1 Entonces
Si NuevoSub = «» Entonces
NuevoSub = «,» & Artículos(i) SSS = SSS + Artículos(i)
Demás
NuevoSub = NuevoSub & «,» & Artículos(i) SSS = SSS + Artículos(i)
Fin Si
Fin Si
Próximo i
Si NuevoSub = «» Entonces NuevoSub = «{}» ‘conjunto vacio
SubLista = SubLista & vbCrLf & NewSub
Si SSS = suma objetivo Entonces
Celdas(rr, kk).NumberFormat = «@» Celdas(rr, kk) = «{ » & Mid(NewSub, 2) & » }» TargetArray() = Split(Mid(NewSub, 2), «,») n1 = UBound(matriz de destino)
Para mi = 0 A n1 Celdas (fila1, columna1) = TargetArray(e) fila1 = fila1 + 1
Próximo e col1 = col1 + 1 fila1 = TargetRow rr = rr + 1
Fin Si
SSS = 0
‘ahora actualiza el vector de código
Si Paso impar Entonces
‘solo voltea el primer bit
CódigoVector(inferior) = 1 – CódigoVector(inferior)
Demás
‘primero localizar primero 1
yo = inferior
Hacer Mientras CódigoVector(i) <> 1 i = i + 1
Bucle
‘hecho si i = superior:
Si yo = superior Entonces
hecho = Verdadero
Demás
‘Si no lo has hecho, invierte el *siguiente* bit:
i = i + 1 VectorCódigo(i) = 1 – VectorCódigo(i)
Fin Si
Fin Si
Paso impar = No Paso impar ‘alterna entre pasos pares e impares
Bucle
CódigoGray = SubLista
Fin Función
A continuación, cree un UserForm con el siguiente diseño y propiedades:
>
Después de completar el diseño del formulario, agregue el código del formulario. Para esto, haga clic derecho en el formulario en el Explorador de proyectos y seleccione Ver código:
Código para el formulario de usuario
Privado Sub BotónComando1_Clic()
Oscuro B
Oscuro C Como Entero
Oscuro d Como Entero
Oscuro A() Como Variante
Oscuro i Como Entero
Oscuro mi Como Entero
DS = Rango(RefEdit1) TargetSum = TextBox1.Value Range(RefEdit2).Seleccionar
TargetCol = Selección.Columna TargetRow = Selección.Fila c = LBound(DS) d = UBound(DS)
Redim B(d – 1)
Para yo = 1 A re = yo – 1 B(e) = DS(i, 1)
Próximo i
Llamar GrayCode(B) Descargarme
Fin Sub
Privado Sub Etiqueta1_Clic()
Fin Sub
Privado Sub Etiqueta3_Clic()
Fin Sub
Consejo. En lugar de volver a crear el formulario desde cero, puede descargar nuestro libro de trabajo de muestra al final de esta publicación y copiar el módulo Código y UserForm1 desde allí (vea cómo copiar una macro a otro libro). Para que la macro sea accesible en todos sus libros, cópiela en el Libro de macros personal.
Con el código y el formulario en su lugar, presione Alt + F8 y ejecutar el Buscar todas las combinaciones macro:
>
En el formulario que aparece, defina lo siguiente:
- El rango con los números de origen (A4:A13)
- La suma objetivo (50)
- La celda superior izquierda del rango de destino (C4).
Al especificar la celda superior izquierda del rango de salida, asegúrese de que haya al menos una fila en blanco arriba (para encabezados) y suficiente celdas en blanco hacia abajo y hacia la derecha. Si no hay suficientes celdas en blanco, se sobrescribirán los datos existentes.
>
Al hacer clic en Aceptar se producirá el resultado como se muestra en la siguiente captura de pantalla:
- En C4:C6, tienes las combinaciones de números como valores separados por comas.
- Las columnas F, G y H contienen las mismas combinaciones de números, y cada número reside en una celda separada.
- En D4, tienes la suma objetivo.
>Esta forma de salida hace que sea más fácil verificar el resultado: simplemente ingrese la fórmula SUMA en la celda F13, arrástrela hacia la derecha a través de dos celdas más y verá que cada combinación de números suma el valor especificado (50).
>
Limitación: Para un conjunto grande de números, la macro puede tardar algún tiempo en generar todas las combinaciones posibles.
Ventajas: Funciona en todas las versiones de Excel 2010 – 365; proporciona dos formas de salida: cadenas de valores separados por comas y números en celdas separadas.
En conclusión, encontrar todas las combinaciones de valores que igualan un determinado valor es una herramienta poderosa para resolver una amplia gama de problemas de análisis de datos. Esperamos que este artículo le haya proporcionado un punto de partida útil para explorar más este tema y manejar problemas similares en su propio trabajo. ¡Gracias por leer!
Libro de práctica
Encuentra combinaciones que sean iguales a una suma dada – ejemplos (archivo .xlsm)
Usted también podría estar interesado en
Encuentre todas las combinaciones de números que equivalen a una suma determinada en Excel
Encuentre todas las combinaciones de números que equivalen a una suma determinada en Excel
Si eres usuario de Excel y necesitas encontrar todas las combinaciones de números que sumen un valor determinado, estás en el lugar correcto. En este artículo, te mostraremos cómo puedes lograrlo utilizando las funciones y herramientas disponibles en Excel.
Paso 1: Preparación de los datos en Excel
Antes de realizar cualquier cálculo, necesitas asegurarte de tener tus datos correctamente organizados en Excel. Para ello, sigue estos pasos:
¡Consejo útil! Si tienes una gran cantidad de datos, puedes utilizar la función «Ordenar» de Excel para ordenar rápidamente tus números en orden ascendente.
Paso 2: Utilización de la función SUMAPRODUCTO
La función SUMAPRODUCTO nos permitirá encontrar todas las combinaciones de números que sumen un valor determinado. Sigue estos pasos para utilizar esta función en Excel:
- valor1, valor2, … representa los valores que deseas que sumen la cantidad deseada.
- A1:A10 es el rango donde tienes tus números en Excel. Asegúrate de ajustar el rango según tus necesidades.
¡Consejo útil! Puedes utilizar la función SUMAPRODUCTO combinada con otras funciones de Excel, como INDIRECTO o DESREF, para tener aún más flexibilidad en tu búsqueda.
Referencias útiles
Aquí te dejamos algunas referencias útiles que te pueden ayudar a comprender más sobre este tema:
- Función SUMAPRODUCTO en Excel – Soporte de Microsoft
- Exceljet – Guía de la función SUMAPRODUCTO de Excel
Parece que has compartido un extenso fragmento de código VBA para Excel que se utiliza para encontrar combinaciones de números que sumen un valor objetivo. el código incluye varias funciones y procedimientos que juntos permiten calcular, almacenar y mostrar combinaciones de números de un rango que tienen una suma específica.
Aquí hay un resumen de sus componentes principales:
- FindSumCombinations: Esta es la función principal que se llama desde una celda de Excel. Toma un rango de números y un objetivo de suma, y trabaja para encontrar todas las combinaciones de los números en el rango que suman al objetivo.
- SumUpRecursiveCombinations: Esta subrutina utiliza recursión para explorar todas las combinaciones de números. Almacena cada combinación que cumple con la condición de suma en una matriz.
- ArrayToString: Convierte una matriz de números en una cadena de texto separada por comas, lo que facilita la salida de los resultados como un texto legible.
- MatrizSuma: Suma los elementos de una matriz.
- AgregarAArray y CopiarMatriz: Estas subrutinas se encargan de agregar elementos a una matriz y copiar el contenido de una matriz a otra, respectivamente.
- Macro adicional: Al final, se incluye un código de macro relacionado con la interfaz de usuario de Excel (UserForm) que complementa el funcionamiento del código principal. Permite a los usuarios seleccionar rangos y ver los resultados en una presentación más intuitiva.
Cómo usarlo en Excel
- Insertar el código:
– Copia el código VBA y pégalo en un módulo de código en el editor de Visual Basic de tu libro de trabajo de Excel (Alt + F11).
– Asegúrate de guardar el libro con la extensión habilitada para macros (.xlsm).
- Uso de la función:
– En una celda de Excel, usa la función =FindSumCombinations(rango, sumaobjetivo), donde rango es el rango de celdas con los números que quieres evaluar y sumaobjetivo es el número que esperas obtener al sumar las combinaciones.
- Celdas vacías:
- Asegúrate de que haya suficiente espacio en las celdas a la derecha para mostrar todos los resultados, dado que la función devolverá múltiples combinaciones.
- Interfaz de usuario:
– Para usar la macro asociada al UserForm, ejecútala desde Alt + F8, selecciona el macro y define tus parámetros en la interfaz que se abrirá.
Consideraciones
- Limitación de rendimiento: El algoritmo puede ser lento para conjuntos grandes de números, ya que explora todas las combinaciones posibles.
- Versiones de Excel: La función está diseñada para funcionar en versiones de Excel que admiten dinámicas de matrices, como Excel 365 y 2021. La macro adicional debe funcionar en versiones anteriores también.
Esta herramienta puede ser especialmente útil para tareas como análisis financiero, optimización de recursos y resolución de problemas combinatorios en diversos contextos. Si tienes alguna pregunta específica sobre el código o cómo implementarlo, ¡házmelo saber!
Pere: ¡Qué interesante! Yo también pasé un mal rato buscando esas combinaciones hasta que un colega me mostró unas fórmulas en Excel que cambiaron todo para mí. Ahora, con artículos como este, siento que tengo un superpoder para hacer mis cálculos. ¡Mil gracias por la info!
Hunlledqv: ¡Qué buen artículo! Siempre he tenido problemas para hacer combinaciones de números en Excel, hasta que un día intenté aplicar lo que leí en un tutorial y me di cuenta de lo fácil que podía ser. Gracias por compartir estos tips, me van a salvar la vida en mis proyectos.
Ispunismovb: ¡Totalmente de acuerdo! Este artículo es una joya, siempre me frustraba tratando de encontrar esas combinaciones, pero desde que seguí unos pasos que vi en un video, todo ha cambiado para mí. De verdad, ¡gracias por compartirlo!
Farkas: ¡Me encanta ver este tipo de contenido! Antes me volvía loco buscando esas combinaciones, pero desde que empecé a usar algunas funciones que vi en un blog, todo es mucho más sencillo. Así que gracias por este artículo, definitivamente me ha ayudado un montón.