Att upptäcka alla möjliga kombinationer av siffror inom en lista som summerar till en specifik summa är en utmaning som många Excel-användare kan stöta på, oavsett om det gäller budgetering, planering eller dataanalys.

I det här exemplet har vi en lista med siffror och målet är att identifiera vilka kombinationer från denna lista som summerar till 480. Skärmdumpen visar att det finns fem möjliga grupper av kombinationer som uppnår denna summa, inklusive kombinationer som 300+120 +60, 250+120+60+50, bland annat. I den här artikeln kommer vi att utforska olika metoder för att lokalisera de specifika kombinationerna av siffror i en lista som sammanlagt ett angivet värde i Excel.

Hitta en kombination av tal lika med en given summa med Solver-funktionen

Få alla kombinationer av tal lika med en given summa

• Genom att använda användardefinierad funktion
• Med en smart funktion - Kutools för Excel

Få alla kombinationer av tal som har en summa i ett intervall med VBA-kod

Hitta cellkombinationer som är lika med en given summa med Solver-funktionen

Att dyka in i Excel för att hitta cellkombinationer som summerar till ett visst antal kan verka skrämmande, men Solver-tillägget gör det enkelt. Vi leder dig genom de enkla stegen för att ställa in Solver och hitta rätt kombination av celler, vilket gör vad som verkade vara en komplex uppgift enkel och genomförbar.

Steg 1: Aktivera tillägget Solver

1. Vänligen gå till Fil > TillbehörI Excel-alternativ dialogrutan, klicka Tillägg från den vänstra rutan, klicka sedan Go knapp. Se skärmdump:
   
2. Sedan Tillägg dialogrutan visas, kontrollera Solver-tillägg alternativet och klicka på OK för att installera detta tillägg framgångsrikt.
   

Steg 2: Ange formeln

Efter att ha aktiverat Solver-tillägget måste du ange denna formel i cellen B11:

=SUMPRODUCT(B2:B10,A2:A10)

Steg 3: Konfigurera och kör Solver för att få resultatet

1. Klicka Data > Lösare för att gå till Lösningsparameter dialogrutan, gör följande i dialogrutan:
   • (1.) Klicka på för att välja cellen B11 var din formel finns från Ställ in mål sektion;
   • (2.) Sedan i Till avsnitt, välj Värdet avoch ange ditt målvärde 480 som du behöver;
   • (3.) Under Genom att ändra variabla celler avsnitt, klicka för att välja cellintervall B2: B10 där markerar dina motsvarande nummer.
   • (4.) Klicka sedan på Lägg till knapp.
   • 
2. Sedan, en Lägg till begränsning dialogrutan visas, klicka för att välja cellintervall B2: B10, och välj bin från rullgardinsmenyn. Äntligen, klicka OK knapp. Se skärmdump:
   
3. I Lösningsparameter dialogrutan, klicka på Lös knappen, några minuter senare, a Lösningsresultat dialogrutan dyker upp och du kan se kombinationen av celler som motsvarar en given summa 480 är markerade som 1 i kolumn B. I Lösningsresultat välj Behåll lösningsmedelslösningen alternativet och klicka på OK för att avsluta dialogen. Se skärmdump:
   

Få alla kombinationer av tal lika med en given summa

Genom att utforska Excels djupare möjligheter kan du hitta varje sifferkombination som matchar en specifik summa, och det är enklare än du kanske tror. Det här avsnittet kommer att visa dig två metoder för att hitta alla kombinationer av tal som är lika med en given summa.

Få alla kombinationer av tal lika med en given summa med användardefinierad funktion

För att avslöja alla möjliga kombinationer av siffror från en specifik uppsättning som tillsammans når ett givet värde, fungerar den anpassade funktionen som beskrivs nedan som ett effektivt verktyg.

Steg 1: Öppna VBA-modulredigeraren och kopiera koden

1. Håll ner ALT + F11 nycklar i Excel, och det öppnar Microsoft Visual Basic för applikationer fönster.
2. Klicka Insert > Modulernaoch klistra in följande kod i modulfönstret.
   VBA-kod: Få alla kombinationer av tal lika med en given summa

   Public 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
   

Steg 2: Ange den anpassade formeln för att få resultatet

När du har klistrat in koden, stäng kodfönstret för att gå tillbaka till kalkylbladet. Skriv in följande formel i en tom cell för att mata ut resultatet och tryck sedan på ange för att få alla kombinationer. Se skärmdump:

=MakeupANumber(A2:A10,B2)

Tips: Om du vill lista kombinationsresultaten vertikalt i en kolumn, använd följande formel:

=TRANSPOSE(MakeupANumber(A2:A10,B2))

Få alla kombinationer av tal lika med en given summa med en kraftfull funktion

Med tanke på begränsningarna för den ovannämnda funktionen rekommenderar vi en snabb och omfattande lösning: Kutools for Excels Make up a Number-funktion, som är kompatibel med alla versioner av Excel. Detta alternativ kan effektivt hantera positiva tal, decimaler och negativa tal. Med den här funktionen kan du snabbt få alla kombinationer som är lika med en given summa.

1. Klicka Kutools > Innehåll > Gör upp ett nummer, se skärmdump:
   
2. Då, i Gör upp ett nummer Klicka i dialogrutan för att välja den nummerlista som du vill använda från Datakällaoch ange sedan det totala antalet i Sum textruta. Klicka slutligen OK knapp, se skärmdump:
   
3. Och sedan kommer en uppmaningsruta att dyka upp för att påminna dig om att välja en cell för att hitta resultatet, klicka sedan på OK, se skärmdump:
   
4. Och nu har alla kombinationer som är lika med det givna numret visats som nedanstående skärmdump:
   

Få alla kombinationer av tal som har en summa i ett intervall med VBA-kod

Ibland kan du hamna i en situation där du behöver identifiera alla möjliga kombinationer av tal som tillsammans summerar till en summa inom ett specifikt intervall. Du kanske till exempel försöker hitta alla möjliga grupper av siffror där summan ligger mellan 470 och 480.

Att upptäcka alla möjliga kombinationer av siffror som summerar till ett värde inom ett specifikt område är en fascinerande och mycket praktisk utmaning i Excel. Det här avsnittet kommer att introducera en VBA-kod för att lösa denna uppgift.

Steg 1: Öppna VBA-modulredigeraren och kopiera koden

1. Håll ner ALT + F11 nycklar i Excel, och det öppnar Microsoft Visual Basic för applikationer fönster.
2. Klicka Insert > Modulernaoch klistra in följande kod i modulfönstret.
   VBA-kod: Få alla kombinationer av tal som summerar till ett specifikt intervall

   Sub 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
   
   
   

Steg 2: Kör koden

1. När du har klistrat in koden trycker du på F5 för att köra den här koden, i den första öppnade dialogrutan, välj det antal nummer du vill använda och klicka OK. Se skärmdump:
   
2. I den andra uppmaningsrutan, välj eller skriv det låga gränsvärdet och klicka OK. Se skärmdump:
   
3. I den tredje uppmaningsrutan, välj eller skriv det högsta gränsnumret och klicka OK. Se skärmdump:
   
4. I den sista uppmaningsrutan väljer du en utdatacell, det är där resultaten kommer att börja matas ut. Klicka sedan OK. Se skärmdump:
   

Resultat

Nu kommer varje kvalificerande kombination att listas i på varandra följande rader i kalkylbladet, med början från den utdatacell du valde.

Excel ger dig flera sätt att hitta grupper av siffror som summerar till en viss summa, varje metod fungerar olika, så du kan välja ett baserat på hur bekant du är med Excel och vad du behöver för ditt projekt. Om du är intresserad av att utforska fler Excel-tips och tricks, erbjuder vår webbplats tusentals handledningar, vänligen klicka här för att komma åt dem. Tack för att du läser, och vi ser fram emot att ge dig mer användbar information i framtiden!

Relaterade artiklar: