A számok összes lehetséges kombinációjának felfedezése egy listán belül, amelyek egy adott összeget adnak ki, olyan kihívás, amellyel sok Excel-felhasználó találkozhat, akár költségvetési, tervezési vagy adatelemzési célból.

Ebben a példában van egy számlistánk, és a cél az, hogy azonosítsuk, mely kombinációk adják össze a 480-at. A képernyőkép azt mutatja, hogy öt lehetséges kombinációs csoport létezik, amelyek elérik ezt az összeget, beleértve a 300+120-as kombinációkat is. Többek között +60, 250+120+60+50. Ebben a cikkben különféle módszereket fogunk megvizsgálni, amelyek segítségével meghatározhatjuk a listán belüli meghatározott számkombinációkat, amelyek egy meghatározott értéket adnak össze az Excelben.

Keresse meg egy adott összeggel egyenlő számkombinációt a Solver függvénnyel

Kapja meg a megadott összeggel egyenlő számkombinációkat

• Felhasználó által meghatározott funkció használatával
• Egy intelligens funkcióval – Kutools for Excel

VBA-kóddal lekérheti az összes olyan számkombinációt, amelynek összege egy tartományban van

Keresse meg a Solver függvénnyel egy adott összeggel egyenlő cellakombinációt

Elrettentőnek tűnhet az Excelbe búvárkodni, hogy olyan cellakombinációkat találjon, amelyek egy adott számot adnak ki, de a Solver bővítmény gyerekjáték. Végigvezetjük a Solver beállításának egyszerű lépésein, és megtalálja a megfelelő cellakombinációt, így a bonyolultnak tűnő feladat egyszerűvé és végrehajthatóvá válik.

1. lépés: Engedélyezze a Solver bővítményt

1. Kérjük, látogasson el filé > Opciók, A Excel beállítások párbeszédpanelen kattintson a gombra Bővítmények a bal oldali ablaktáblában, majd kattintson Go gomb. Lásd a képernyőképet:
   
2. Aztán a Bővítmények párbeszédablak jelenik meg, ellenőrizze a Solver bővítmény opciót, és kattintson rá OK a bővítmény sikeres telepítéséhez.
   

2. lépés: Írja be a képletet

A Solver bővítmény aktiválása után a következő képletet kell beírnia a B11 cellába:

=SUMPRODUCT(B2:B10,A2:A10)

3. lépés: Konfigurálja és futtassa a Solvert az eredmény eléréséhez

1. Kattints dátum > Megoldó menni Megoldó paraméter párbeszédpanelen a párbeszédpanelen tegye a következőket:
   • (1.) Kattintson gombbal válassza ki a cellát B11 ahol a képlete található a Cél beállítása szakasz;
   • (2.) Majd a Nak nek válasszon Értéke, és adja meg a célértéket 480 amire szüksége van;
   • (3.) A Változó cellák megváltoztatásával részben kattintson a gombra gombot a cellatartomány kiválasztásához B2: B10 hol fogja jelölni a megfelelő számokat.
   • (4.) Ezután kattintson hozzáad gombot.
   • 
2. Aztán egy Add kényszer párbeszédpanel jelenik meg, kattintson gombot a cellatartomány kiválasztásához B2: B10, és válassza ki tartó a legördülő listából. Végül kattintson OK gomb. Lásd a képernyőképet:
   
3. A Megoldó paraméter párbeszédpanelen kattintson a Oldja gomb, néhány perccel később, a Megoldó eredmények párbeszédpanel kiugrik, és láthatja, hogy a B oszlopban 480-el vannak megjelölve azok a cellák, amelyek egy adott 1 összeggel egyenlők. Megoldó eredmények párbeszédpanelt, válassza ki Tartsa a Megoldó megoldást opciót, és kattintson rá OK a párbeszédablakból való kilépéshez. Lásd a képernyőképet:
   

Kapja meg a megadott összeggel egyenlő számkombinációkat

Az Excel mélyebb képességeinek felfedezésével minden számkombinációt megtalálhat, amely egy adott összegnek felel meg, és ez könnyebb, mint gondolná. Ez a rész két módszert mutat be az adott összeggel egyenlő számkombinációk megtalálására.

A felhasználói függvény segítségével egy adott összeggel egyenlő számkombinációkat kaphat

Az alábbiakban vázolt egyéni függvény hatékony eszközként szolgál egy adott halmaz összes lehetséges számkombinációjának feltárásához, amely együttesen elér egy adott értéket.

1. lépés: Nyissa meg a VBA-modul szerkesztőjét, és másolja ki a kódot

1. Tartsa lenyomva a ALT + F11 kulcsokat az Excelben, és megnyitja a Microsoft Visual Basic for Applications ablak.
2. Kattints betétlap > Modulok, és illessze be a következő kódot a Modul ablakba.
   VBA-kód: Adott összeggel megegyező összes számkombináció lekérése

   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
   

2. lépés: Adja meg az egyéni képletet az eredmény eléréséhez

A kód beillesztése után zárja be a kód ablakot, hogy visszatérjen a munkalaphoz. Írja be a következő képletet egy üres cellába az eredmény kiadásához, majd nyomja meg a gombot belép kulcs az összes kombináció eléréséhez. Lásd a képernyőképet:

=MakeupANumber(A2:A10,B2)

típus: Ha a kombinációs eredményeket függőlegesen szeretné felsorolni egy oszlopban, használja a következő képletet:

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

Szerezzen be egy adott összeggel egyenlő számkombinációt egy hatékony funkcióval

Tekintettel a fent említett funkció korlátaira, egy gyors és átfogó megoldást ajánlunk: a Kutools for Excel Make up a Number funkcióját, amely az Excel bármely verziójával kompatibilis. Ez az alternatíva hatékonyan tudja kezelni a pozitív számokat, a tizedesjegyeket és a negatív számokat. Ezzel a funkcióval gyorsan megkaphatja az összes olyan kombinációt, amely egy adott összeggel egyenlő.

1. Kattints Kutools > Tartalom > Töltsön fel egy számot, lásd a képernyőképet:
   
2. Aztán a Töltsön fel egy számot párbeszédpanelen kattintson a gombra gombbal válassza ki a használni kívánt számlistát a Adatforrás, majd írja be a teljes számot a Összeg szövegdoboz. Végül kattintson OK gomb, lásd a képernyőképet:
   
3. Ezután egy prompt ablak jelenik meg, amely emlékezteti Önt, hogy válasszon ki egy cellát az eredmény megkereséséhez, majd kattintson a gombra OK, lásd a képernyőképet:
   
4. És most az adott számmal megegyező összes kombináció az alábbi képernyőképen látható:
   

VBA-kóddal lekérheti az összes olyan számkombinációt, amelynek összege egy tartományban van

Néha előfordulhat, hogy olyan helyzetbe kerül, amikor meg kell határoznia a számok összes lehetséges kombinációját, amelyek együttesen összegeznek egy bizonyos tartományon belüli összeget. Például előfordulhat, hogy meg akarja találni a számok összes lehetséges csoportját, ahol az összeg 470 és 480 közé esik.

A számok összes lehetséges kombinációjának felfedezése, amelyek egy adott tartományon belüli értéket adnak össze, lenyűgöző és rendkívül praktikus kihívást jelent az Excelben. Ez a rész egy VBA kódot mutat be ennek a feladatnak a megoldásához.

1. lépés: Nyissa meg a VBA-modul szerkesztőjét, és másolja ki a kódot

1. Tartsa lenyomva a ALT + F11 kulcsokat az Excelben, és megnyitja a Microsoft Visual Basic for Applications ablak.
2. Kattints betétlap > Modulok, és illessze be a következő kódot a Modul ablakba.
   VBA-kód: Az összes olyan számkombináció lekérése, amelyek egy adott tartományt összegeznek

   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
   
   
   

2. lépés: Hajtsa végre a kódot

1. A kód beillesztése után nyomja meg a gombot F5 gombot a kód futtatásához, az első felugró párbeszédpanelen válassza ki a használni kívánt számtartományt, és kattintson a gombra OK. Lásd a képernyőképet:
   
2. A második prompt mezőben válassza ki vagy írja be az alsó határértéket, majd kattintson a gombra OK. Lásd a képernyőképet:
   
3. A harmadik prompt mezőben válassza ki vagy írja be a felső határértéket, és kattintson a gombra OK. Lásd a képernyőképet:
   
4. Az utolsó prompt mezőben válasszon ki egy kimeneti cellát, amelyből az eredmények megjelennek. Ezután kattintson OK. Lásd a képernyőképet:
   

Eredmény

Most minden egyes minősítő kombináció egymást követő sorokban jelenik meg a munkalapon, a kiválasztott kimeneti cellától kezdve.

Az Excel többféle módszert kínál arra, hogy olyan számcsoportokat keressen, amelyek egy bizonyos összeget adnak ki, mindegyik módszer eltérően működik, így kiválaszthat egyet az alapján, hogy mennyire ismeri az Excelt, és mire van szüksége a projekthez. Ha további Excel-tippeket és trükköket szeretne felfedezni, webhelyünk több ezer oktatóanyagot kínál, kérjük, ide kattintva érheti el őket. Köszönjük, hogy elolvasta, és a jövőben is további hasznos információkkal szolgálunk!

Kapcsolódó cikkek: