At opdage alle mulige kombinationer af tal på en liste, der summerer til en bestemt sum, er en udfordring, som mange Excel-brugere kan støde på, hvad enten det er til budgettering, planlægning eller dataanalyse.

I dette eksempel har vi en liste over tal, og målet er at identificere, hvilke kombinationer fra denne liste, der summer op til 480. Det viste skærmbillede viser, at der er fem mulige grupper af kombinationer, der opnår denne sum, inklusive kombinationer som 300+120 +60, 250+120+60+50, blandt andre. I denne artikel vil vi udforske forskellige metoder til at lokalisere de specifikke kombinationer af tal inden for en liste, der giver en samlet værdi i Excel.

Find en kombination af tal svarende til en given sum med Solver-funktionen

Få alle kombinationer af tal lig med en given sum

• Ved at bruge brugerdefineret funktion
• Ved en smart funktion - Kutools til Excel

Få alle kombinationer af tal, der har en sum i et interval med VBA-kode

Find cellekombination, der er lig med en given sum med Solver-funktionen

At dykke ned i Excel for at finde cellekombinationer, der tæller op til et bestemt antal, kan virke skræmmende, men Solver-tilføjelsesprogrammet gør det til en leg. Vi vil lede dig gennem de enkle trin til at konfigurere Solver og finde den rigtige kombination af celler, hvilket gør det, der virkede som en kompleks opgave ligetil og gennemførlig.

Trin 1: Aktiver Solver Add-in

1. Gå venligst til File (Felt) > IndstillingerI Excel-indstillinger dialogboksen, klik Add-Ins fra venstre rude skal du derefter klikke Go knap. Se skærmbillede:
   
2. Derefter Add-Ins dialogboksen vises, skal du kontrollere Solver-tilføjelsesprogram valgmulighed og klik OK for at installere dette tilføjelsesprogram med succes.
   

Trin 2: Indtast formlen

Når du har aktiveret Solver-tilføjelsen, skal du indtaste denne formel i cellen B11:

=SUMPRODUCT(B2:B10,A2:A10)

Trin 3: Konfigurer og kør Solver for at få resultatet

1. Klik data > Solver at gå til Løserparameter dialogboks, i dialogboksen skal du udføre følgende handlinger:
   • (1.) Klik på for at vælge cellen B11 hvor din formel er placeret fra Indstil mål afsnit;
   • (2.) Derefter i Til sektion, vælg Værdi af, og indtast din målværdi 480 som du har brug for;
   • (3.) Under Ved at ændre variable celler sektion, klik venligst knappen for at vælge celleområdet B2: B10 hvor markerer dine tilsvarende tal.
   • (4.) Klik derefter på Tilføj .
   • 
2. Derefter en Tilføj begrænsning dialogboksen vises, klik knappen for at vælge celleområdet B2: B10, og vælg bin fra rullelisten. Klik endelig OK knap. Se skærmbillede:
   
3. I Løserparameter dialog, klik på Løs knap, nogle minutter senere, a Løsningsresultater dialogboksen springes ud, og du kan se kombinationen af ​​celler, der svarer til en given sum 480, er markeret som 1 i kolonne B. Løsningsresultater dialog, vælg venligst Behold løsningsløsningen valgmulighed og klik OK for at afslutte dialogen. Se skærmbillede:
   

Få alle kombinationer af tal lig med en given sum

Ved at udforske Excels dybere muligheder kan du finde hver talkombination, der matcher en bestemt sum, og det er nemmere, end du måske tror. Dette afsnit viser dig to metoder til at finde alle kombinationer af tal, der er lig med en given sum.

Få alle kombinationer af tal lig med en given sum med brugerdefineret funktion

For at afdække enhver mulig kombination af tal fra et specifikt sæt, der tilsammen når en given værdi, fungerer den tilpassede funktion, der er skitseret nedenfor, som et effektivt værktøj.

Trin 1: Åbn VBA-moduleditoren og kopier koden

1. Hold nede i ALT + F11 nøgler i Excel, og det åbner Microsoft Visual Basic til applikationer vindue.
2. Klik indsatte > Moduler, og indsæt følgende kode i modulvinduet.
   VBA-kode: Få alle kombinationer af tal lig med en given sum

   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
   

Trin 2: Indtast den brugerdefinerede formel for at få resultatet

Når du har indsat koden, skal du lukke kodevinduet for at gå tilbage til regnearket. Indtast følgende formel i en tom celle for at udskrive resultatet, og tryk derefter på Indtast tasten for at få alle kombinationer. Se skærmbillede:

=MakeupANumber(A2:A10,B2)

Tip: Hvis du vil angive kombinationsresultaterne lodret i en kolonne, skal du anvende følgende formel:

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

Få alle kombinationer af tal lig med en given sum med en kraftfuld funktion

I betragtning af begrænsningerne af den førnævnte funktion anbefaler vi en hurtig og omfattende løsning: Kutools til Excel's Make up a Number-funktion, som er kompatibel med enhver version af Excel. Dette alternativ kan effektivt håndtere positive tal, decimaler og negative tal. Med denne funktion kan du hurtigt få alle kombinationer, der er lig med en given sum.

1. Klik Kutools > Indhold > Fyld et nummer op, se skærmbillede:
   
2. Derefter i Sæt et nummer op Klik på dialogboksen for at vælge den nummerliste, du vil bruge, fra Datakilde, og indtast derefter det samlede antal i Sum tekstboks. Klik til sidst OK knap, se skærmbillede:
   
3. Og så vil en promptboks poppe ud for at minde dig om at vælge en celle for at finde resultatet, og klik derefter på OK, se skærmbillede:
   
4. Og nu er alle kombinationer, der svarer til det givne tal, blevet vist som vist nedenfor skærmbillede:
   

Få alle kombinationer af tal, der har en sum i et interval med VBA-kode

Nogle gange kan du finde dig selv i en situation, hvor du har brug for at identificere alle mulige kombinationer af tal, der tilsammen summerer til en sum inden for et specifikt interval. For eksempel søger du måske at finde alle mulige grupperinger af tal, hvor totalen falder mellem 470 og 480.

At opdage alle mulige kombinationer af tal, der summerer op til en værdi inden for et specifikt område, repræsenterer en fascinerende og yderst praktisk udfordring i Excel. Dette afsnit introducerer en VBA-kode til løsning af denne opgave.

Trin 1: Åbn VBA-moduleditoren og kopier koden

1. Hold nede i ALT + F11 nøgler i Excel, og det åbner Microsoft Visual Basic til applikationer vindue.
2. Klik indsatte > Moduler, og indsæt følgende kode i modulvinduet.
   VBA-kode: Få alle kombinationer af tal, der summerer op til et specifikt interval

   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
   
   
   

Trin 2: Udfør koden

1. Når du har indsat koden, skal du trykke på F5 tasten for at køre denne kode, i den første åbnede dialog, vælg den række af tal, du vil bruge, og klik OK. Se skærmbillede:
   
2. I den anden promptboks skal du vælge eller skrive det lave grænsenummer og klikke OK. Se skærmbillede:
   
3. I den tredje promptboks skal du vælge eller skrive det høje grænsenummer og klikke OK. Se skærmbillede:
   
4. I den sidste promptboks skal du vælge en outputcelle, hvor resultaterne vil begynde at blive outputtet. Klik derefter OK. Se skærmbillede:
   

Resultat

Nu vil hver kvalificerende kombination blive opført i på hinanden følgende rækker i regnearket, startende fra den outputcelle, du valgte.

Excel giver dig flere måder at finde grupper af tal, der lægger op til en vis total, hver metode fungerer forskelligt, så du kan vælge en baseret på, hvor fortrolig du er med Excel, og hvad du har brug for til dit projekt. Hvis du er interesseret i at udforske flere Excel-tip og tricks, tilbyder vores hjemmeside tusindvis af selvstudier, klik her for at få adgang til dem. Tak fordi du læste med, og vi ser frem til at give dig mere nyttig information i fremtiden!

Relaterede artikler: