¿Cómo encontrar todas las combinaciones que equivalen a una suma determinada en Excel?
Descubrir todas las combinaciones posibles de números dentro de una lista que suman una suma específica es un desafío que muchos usuarios de Excel pueden encontrar, ya sea con fines de elaboración de presupuestos, planificación o análisis de datos.
En este ejemplo, tenemos una lista de números y el objetivo es identificar qué combinaciones de esta lista suman 480. La captura de pantalla proporcionada demuestra que hay cinco grupos posibles de combinaciones que logran esta suma, incluidas combinaciones como 300+120. +60, 250+120+60+50, entre otros. En este artículo, exploraremos varios métodos para identificar las combinaciones específicas de números dentro de una lista que suman un valor designado en Excel.
Encuentre una combinación de números igual a una suma dada con la función Solver
Obtener todas las combinaciones de números iguales a una suma determinada
Obtenga todas las combinaciones de números que tengan una suma en un rango con código VBA
Encuentre una combinación de celdas que sea igual a una suma dada con la función Solver
Bucear en Excel para encontrar combinaciones de celdas que sumen un número específico puede parecer desalentador, pero el complemento Solver lo hace muy sencillo. Lo guiaremos a través de los sencillos pasos para configurar Solver y encontrar la combinación correcta de celdas, haciendo que lo que parecía una tarea compleja sea sencilla y factible.
Paso 1: habilite el complemento Solver
- Por favor, vaya a Archive > Opciones, En la Opciones de Excel cuadro de diálogo, haga clic en Complementos desde el panel izquierdo, luego, haga clic en Go botón. Ver captura de pantalla:
- Entonces, el Complementos Aparece el cuadro de diálogo, verifique el Complemento Solver opción, y haga clic OK para instalar este complemento correctamente.
Paso 2: ingrese la fórmula
Después de activar el complemento Solver, debe ingresar esta fórmula en la celda B11:
=SUMPRODUCT(B2:B10,A2:A10)
Paso 3: Configure y ejecute Solver para obtener el resultado
- Haga Clic en Datos > Solver para ir a la Parámetro del solucionador cuadro de diálogo, en el cuadro de diálogo, realice las siguientes operaciones:
- (1.) Haga clic en botón para seleccionar la celda B11 dónde se encuentra su fórmula desde el Establecer objetivo sección;
- (2.) Luego en el A sección, seleccionar Valor dee ingrese su valor objetivo 480 como necesites;
- (3.) Bajo el Cambiando celdas variables sección, haga clic en botón para seleccionar el rango de celdas B2: B10 donde marcará sus números correspondientes.
- (4.) Luego, haga clic en Añada del botón.
- Entonces, un Agregar restricción Se muestra el cuadro de diálogo, haga clic en botón para seleccionar el rango de celdas B2: B10y seleccione papelera de la lista desplegable. Por último, haz clic OK botón. Ver captura de pantalla:
- En Parámetro del solucionador diálogo, haga clic en Resolver , unos minutos después, un Resultados del solucionador Aparece el cuadro de diálogo y puede ver que la combinación de celdas que equivalen a una suma determinada 480 están marcadas como 1 en la columna B. En el Resultados del solucionador cuadro de diálogo, seleccione Mantener la solución Solver opción, y haga clic OK para salir del diálogo. Ver captura de pantalla:
Obtener todas las combinaciones de números iguales a una suma determinada
Explorar las capacidades más profundas de Excel le permite encontrar cada combinación de números que coincida con una suma específica, y es más fácil de lo que piensa. Esta sección le mostrará dos métodos para encontrar todas las combinaciones de números iguales a una suma determinada.
Obtenga todas las combinaciones de números iguales a una suma determinada con la función definida por el usuario
Para descubrir todas las combinaciones posibles de números de un conjunto específico que en conjunto alcanza un valor determinado, la función personalizada que se describe a continuación sirve como una herramienta eficaz.
Paso 1: abre el editor de módulos de VBA y copia el código
- Mantenga pulsado el botón ALT + F11 claves en Excel, y abre el Microsoft Visual Basic para aplicaciones ventana.
- Haga Clic en recuadro > Móduloy pegue el siguiente código en la ventana del módulo.
Código VBA: obtenga todas las combinaciones de números iguales a una suma determinadaPublic Function MakeupANumber(xNumbers As Range, xCount As Long) 'updateby Extendoffice Dim arrNumbers() As Long Dim arrRes() As String Dim ArrTemp() As Long Dim xIndex As Long Dim rg As Range MakeupANumber = "" If xNumbers.CountLarge = 0 Then Exit Function ReDim arrNumbers(xNumbers.CountLarge - 1) xIndex = 0 For Each rg In xNumbers If IsNumeric(rg.Value) Then arrNumbers(xIndex) = CLng(rg.Value) xIndex = xIndex + 1 End If Next rg If xIndex = 0 Then Exit Function ReDim Preserve arrNumbers(0 To xIndex - 1) ReDim arrRes(0) Call Combinations(arrNumbers, xCount, ArrTemp(), arrRes()) ReDim Preserve arrRes(0 To UBound(arrRes) - 1) MakeupANumber = arrRes End Function Private Sub Combinations(Numbers() As Long, Count As Long, ArrTemp() As Long, ByRef arrRes() As String) Dim currentSum As Long, i As Long, j As Long, k As Long, num As Long, indRes As Long Dim remainingNumbers() As Long, newCombination() As Long currentSum = 0 If (Not Not ArrTemp) <> 0 Then For i = LBound(ArrTemp) To UBound(ArrTemp) currentSum = currentSum + ArrTemp(i) Next i End If If currentSum = Count Then indRes = UBound(arrRes) ReDim Preserve arrRes(0 To indRes + 1) arrRes(indRes) = ArrTemp(0) For i = LBound(ArrTemp) + 1 To UBound(ArrTemp) arrRes(indRes) = arrRes(indRes) & "," & ArrTemp(i) Next i End If If currentSum > Count Then Exit Sub If (Not Not Numbers) = 0 Then Exit Sub For i = 0 To UBound(Numbers) Erase remainingNumbers() num = Numbers(i) For j = i + 1 To UBound(Numbers) If (Not Not remainingNumbers) <> 0 Then ReDim Preserve remainingNumbers(0 To UBound(remainingNumbers) + 1) Else ReDim Preserve remainingNumbers(0 To 0) End If remainingNumbers(UBound(remainingNumbers)) = Numbers(j) Next j Erase newCombination() If (Not Not ArrTemp) <> 0 Then For k = 0 To UBound(ArrTemp) If (Not Not newCombination) <> 0 Then ReDim Preserve newCombination(0 To UBound(newCombination) + 1) Else ReDim Preserve newCombination(0 To 0) End If newCombination(UBound(newCombination)) = ArrTemp(k) Next k End If If (Not Not newCombination) <> 0 Then ReDim Preserve newCombination(0 To UBound(newCombination) + 1) Else ReDim Preserve newCombination(0 To 0) End If newCombination(UBound(newCombination)) = num Combinations remainingNumbers, Count, newCombination, arrRes Next i End Sub
Paso 2: ingrese la fórmula personalizada para obtener el resultado
Después de pegar el código, cierre la ventana del código para volver a la hoja de trabajo. Ingrese la siguiente fórmula en una celda en blanco para generar el resultado y luego presione Participar clave para obtener todas las combinaciones. Ver captura de pantalla:
=MakeupANumber(A2:A10,B2)
=TRANSPOSE(MakeupANumber(A2:A10,B2))
- Esta función personalizada solo funciona en Excel 365 y 2021.
- Este método es eficaz exclusivamente para números positivos; los valores decimales se redondean automáticamente al número entero más cercano y los números negativos generarán errores.
Obtenga todas las combinaciones de números iguales a una suma determinada con una característica poderosa
Dadas las limitaciones de la función antes mencionada, recomendamos una solución rápida y completa: la función Crear un número de Kutools for Excel, que es compatible con cualquier versión de Excel. Esta alternativa puede manejar eficazmente números positivos, decimales y números negativos. Con esta función, puede obtener rápidamente todas las combinaciones que equivalen a una suma determinada.
- Haga Clic en Kutools > Contenido > Hacer un número, ver captura de pantalla:
- Entonces, en el Inventa un número cuadro de diálogo, haga clic en para seleccionar la lista de números que desea utilizar del Fuente de datosy luego ingrese el número total en el Suma caja de texto. Finalmente, haga clic en OK botón, ver captura de pantalla:
- Y luego, aparecerá un cuadro emergente para recordarle que seleccione una celda para ubicar el resultado, luego haga clic en OK, ver captura de pantalla:
- Y ahora, todas las combinaciones que equivalen a ese número dado se muestran como se muestra a continuación:
Obtenga todas las combinaciones de números que tengan una suma en un rango con código VBA
A veces, es posible que se encuentre en una situación en la que necesite identificar todas las combinaciones posibles de números que en conjunto suman una suma dentro de un rango específico. Por ejemplo, es posible que desee encontrar todos los grupos posibles de números donde el total se encuentre entre 470 y 480.
Descubrir todas las combinaciones posibles de números que suman un valor dentro de un rango específico representa un desafío fascinante y muy práctico en Excel. Esta sección presentará un código VBA para resolver esta tarea.
Paso 1: abre el editor de módulos de VBA y copia el código
- Mantenga pulsado el botón ALT + F11 claves en Excel, y abre el Microsoft Visual Basic para aplicaciones ventana.
- Haga Clic en recuadro > Móduloy pegue el siguiente código en la ventana del módulo.
Código VBA: obtenga todas las combinaciones de números que suman un rango específicoSub Getall_combinations() 'Updateby Extendoffice Dim xNumbers As Variant Dim Output As Collection Dim rngSelection As Range Dim OutputCell As Range Dim LowLimit As Long, HiLimit As Long Dim i As Long, j As Long Dim TotalCombinations As Long Dim CombTotal As Double Set Output = New Collection On Error Resume Next Set rngSelection = Application.InputBox("Select the range of numbers:", "Kutools for Excel", Type:=8) If rngSelection Is Nothing Then MsgBox "No range selected. Exiting macro.", vbInformation, "Kutools for Excel" Exit Sub End If On Error GoTo 0 xNumbers = rngSelection.Value LowLimit = Application.InputBox("Select or enter the low limit number:", "Kutools for Excel", Type:=1) HiLimit = Application.InputBox("Select or enter the high limit number:", "Kutools for Excel", Type:=1) On Error Resume Next Set OutputCell = Application.InputBox("Select the first cell for output:", "Kutools for Excel", Type:=8) If OutputCell Is Nothing Then MsgBox "No output cell selected. Exiting macro.", vbInformation, "Kutools for Excel" Exit Sub End If On Error GoTo 0 TotalCombinations = 2 ^ (UBound(xNumbers, 1) * UBound(xNumbers, 2)) For i = 1 To TotalCombinations - 1 Dim tempArr() As Double ReDim tempArr(1 To UBound(xNumbers, 1) * UBound(xNumbers, 2)) CombTotal = 0 Dim k As Long: k = 0 For j = 1 To UBound(xNumbers, 1) If i And (2 ^ (j - 1)) Then k = k + 1 tempArr(k) = xNumbers(j, 1) CombTotal = CombTotal + xNumbers(j, 1) End If Next j If CombTotal >= LowLimit And CombTotal <= HiLimit Then ReDim Preserve tempArr(1 To k) Output.Add tempArr End If Next i Dim rowOffset As Long rowOffset = 0 Dim item As Variant For Each item In Output For j = 1 To UBound(item) OutputCell.Offset(rowOffset, j - 1).Value = item(j) Next j rowOffset = rowOffset + 1 Next item End Sub
Paso 2: ejecuta el código
- Después de pegar el código, presione F5 para ejecutar este código, en el primer cuadro de diálogo emergente, seleccione el rango de números que desea usar y haga clic en OK. Ver captura de pantalla:
- En el segundo cuadro, seleccione o escriba el número de límite bajo y haga clic en OK. Ver captura de pantalla:
- En el tercer cuadro, seleccione o escriba el número de límite superior y haga clic en OK. Ver captura de pantalla:
- En el último cuadro de mensaje, seleccione una celda de salida, que es donde comenzarán a generarse los resultados. Luego haga clic OK. Ver captura de pantalla:
Resultado
Ahora, cada combinación calificada aparecerá en filas consecutivas en la hoja de trabajo, comenzando desde la celda de salida que eligió.
Excel te ofrece varias formas de encontrar grupos de números que suman un total determinado, cada método funciona de manera diferente, por lo que puedes elegir uno según tu nivel de familiaridad con Excel y lo que necesitas para tu proyecto. Si está interesado en explorar más consejos y trucos de Excel, nuestro sitio web ofrece miles de tutoriales, por favor haga clic aquí para acceder a ellos. ¡Gracias por leer y esperamos brindarle más información útil en el futuro!
Artículos relacionados:
- Enumerar o generar todas las combinaciones posibles.
- Digamos, tengo las siguientes dos columnas de datos, y ahora, quiero generar una lista de todas las combinaciones posibles basadas en las dos listas de valores como se muestra en la captura de pantalla de la izquierda. Tal vez, puede enumerar todas las combinaciones una por una si hay pocos valores, pero, si hay varias columnas con valores múltiples necesarios para enumerar las combinaciones posibles, aquí hay algunos trucos rápidos que pueden ayudarlo a lidiar con este problema en Excel .
- Enumere todas las combinaciones posibles de una sola columna.
- Si desea devolver todas las combinaciones posibles de datos de una sola columna para obtener el resultado como se muestra a continuación, ¿tiene alguna forma rápida de manejar esta tarea en Excel?
- Genere todas las combinaciones de 3 o varias columnas.
- Supongamos que tengo 3 columnas de datos, ahora quiero generar o enumerar todas las combinaciones de los datos en estas 3 columnas como se muestra a continuación. ¿Tiene algún buen método para resolver esta tarea en Excel?
- Genere una lista de todas las combinaciones posibles de 4 dígitos
- En algunos casos, es posible que necesitemos generar una lista de todas las combinaciones posibles de 4 dígitos del número 0 al 9, lo que significa generar una lista de 0000, 0001, 0002… 9999. Para resolver rápidamente la tarea de la lista en Excel, le presento algunos trucos.
Las mejores herramientas de productividad de oficina
Mejore sus habilidades de Excel con Kutools for Excel y experimente la eficiencia como nunca antes. Kutools for Excel ofrece más de 300 funciones avanzadas para aumentar la productividad y ahorrar tiempo. Haga clic aquí para obtener la función que más necesita...
Office Tab lleva la interfaz con pestañas a Office y hace que su trabajo sea mucho más fácil
- Habilite la edición y lectura con pestañas en Word, Excel, PowerPoint, Publisher, Access, Visio y Project.
- Abra y cree varios documentos en nuevas pestañas de la misma ventana, en lugar de en nuevas ventanas.
- ¡Aumenta su productividad en un 50% y reduce cientos de clics del mouse todos los días!
Tabla de contenidos.
- Encuentra una combinación de números igual a una suma dada.
- Obtener todas las combinaciones de números iguales a una suma determinada
- Con función definida por el usuario
- Con Kutools para Excel
- Obtener todas las combinaciones de números que tienen una suma en un rango
- Artículos Relacionados
- Las mejores herramientas de productividad de oficina
- Comentarios