Ugrás a tartalomra

Hogyan lehet megtalálni az összes kombinációt, amely megegyezik egy adott összeggel az Excelben?

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

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)
Megjegyzések: Ebben a képletben: B2: B10 egy üres cellákból álló oszlop a számlista mellett, és A2: A10 az Ön által használt számlista.

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:
Megjegyzések: Ennek a módszernek azonban van egy korlátja: csak egy olyan cellakombinációt képes azonosítani, amelyek összege adja a megadott összeget, még akkor is, ha több érvényes kombináció létezik.

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)
Megjegyzések: Ebben a képletben: A2: A10 a számlista, és B2 az a teljes összeg, amelyet szeretne kapni.

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))
Ennek a módszernek a korlátai:
  • Ez az egyéni funkció csak az Excel 365-ben és 2021-ben működik.
  • Ez a módszer kizárólag pozitív számokra hatásos; A decimális értékeket a rendszer automatikusan a legközelebbi egész számra kerekíti, és a negatív számok hibákat eredményeznek.

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ő.

tippek: Ennek alkalmazásához Töltsön fel egy számot funkciót, először is le kell töltenie Kutools az Excel számára, majd gyorsan és egyszerűen alkalmazza a funkciót.
  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ó:
Megjegyzések: A funkció alkalmazásához kérjük töltse le és telepítse a Kutools for Excel alkalmazást először.

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:

  • Listázza vagy generálja az összes lehetséges kombinációt
  • Tegyük fel, hogy a következő két oszlopom van, és most szeretnék létrehozni egy listát az összes lehetséges kombinációról a bal oldali képernyőképen látható két értéklista alapján. Lehetséges, hogy az összes kombinációt felsorolhatja egyenként, ha kevés érték van, de ha több oszlop van, több értékkel kell felsorolni a lehetséges kombinációkat, akkor itt néhány gyors trükk segíthet kezelni ezt a problémát az Excel programban .
  • Hozzon létre 3 vagy több oszlop összes kombinációját
  • Tételezzük fel, hogy 3 adatoszlopom van, most szeretném létrehozni vagy felsorolni az adatok összes kombinációját ebben a 3 oszlopban, az alábbi képernyőképen. Van valami jó módszere a feladat Excelben történő megoldására?
  • Készítsen listát az összes lehetséges 4 számjegy kombinációról
  • Bizonyos esetekben előfordulhat, hogy létre kell hoznunk egy listát az összes lehetséges 4 számjegyű kombinációról a 0 és 9 közötti számokról, ami azt jelenti, hogy létrehozzunk egy 0000, 0001, 0002 ... 9999 listát. A listafeladat gyors megoldásához Excelben bemutatok néhány trükköt az Ön számára.
Comments (51)
No ratings yet. Be the first to rate!
This comment was minimized by the moderator on the site
hola esta muy interesante la demostración del código para excel, pero si deseo que me combine números, con cierta cantidad de números por combinación, por ejemplo 4 números por combinación, y solo me arroje eso, para optimizar la memoria del procesador:
1,2,3,4
2,3,4,5
5,2,6,8

en este caso como sería el código
This comment was minimized by the moderator on the site
Hola muy interesante la explicación, me sirvió bastante, pero deseo consultar como serpia el código si deseo que me de las combinaciones de 6 dígitos, o 5 digitos, según corresponda
This comment was minimized by the moderator on the site
Hi everyone,

I'm trying to find a way to to find all possible combinations of workershifts for a specific amount of workhours within a specific amount of working days.

Both, solver and Kutools basically work, but:

Solver only ever displays one combination not all of them (which is what I would need).

Kutools on the other hand only uses each value once, for example I know there are solutions with say 3 7-hour shifts but it won't pick the shift more than once.

Thanks in advance for any responses!

Hope I'm not threadnecroing too hard here.
This comment was minimized by the moderator on the site
Hello, simsok,
Sorry, I can't understand you clearly.
Could you give your problem more detailed, or can you insert a screenshot of your problem here?
Thank you!
This comment was minimized by the moderator on the site
Thank you so much!!! The solver add-in worked for me!
This comment was minimized by the moderator on the site
How to get list of cell names that add to a given no.
This comment was minimized by the moderator on the site
Hello, Ranka,
I'm sorry, at present, there is no good way for getting the cells that add to a given number.
Thank you!
This comment was minimized by the moderator on the site
ExtendOffice - How To Find All Combinations That Equal A Given Sum In Excel

In this example, I would like to run 100 rows instead of the 8 in your example. When I try to create more rows the formula stops working. I started over with a fresh sheet and I still can not get the formula to work.

Running windows 10
Excel 2207
Office 365
This comment was minimized by the moderator on the site
Hello, Shaw

Unfortunately, the formula in this article has a limit of 20 values, if there are more than 20 numbers, the result will not come out. In this case, I will recommend the Make Up A Number feature of Kutools for Excel, with it, you can find all combinations from the list of numbers. If you have a lot of numbers and many combination results, it will take much time, but you can set the number of combinations to make it faster. See screenshot:
https://www.extendoffice.com/images/stories/comments/comment-skyyang/doc-make-up-number-1.png

You can download this tool from here: https://www.extendoffice.com/download/kutools-for-excel.html
You can try it for free 30 days, please have a try, hope it can help you!
This comment was minimized by the moderator on the site
Hi I have tried doing this with the first method and it just isn't working. I'm unsure what I'm doing wrong as I seem to have followed the instructions exactly. Are there any common errors to watch out for? There is no error showing in the formula itself and all cells are all in the same position as yours are, but it's a list of 48 numbers rather than 8.
This comment was minimized by the moderator on the site
Hello, Angie
If you can't get the result by using the first method, you can view the video at the bottom of this article:
https://www.extendoffice.com/documents/excel/3557-excel-find-all-combinations-that-equal-given-sum.html#demo
Also, you can try our tool-Kutools for Excel's Make up a number feature, it will get all combinations quickly and easily.
Thank you!
This comment was minimized by the moderator on the site
So will any of these work when I have been paid by a client, but with no remit so don’t know which invoices have been paid.
This comment was minimized by the moderator on the site
For the solver add in on excel, can you make it solve for closest to 480 rather than value of 480 (example used above)? I really need some help on how this might work out, thanks in advance

This comment was minimized by the moderator on the site
Could this be adapted to find combinations that sum up to specific range i.e. sum between 450 and 500? Is there a way to set it so that each cell value can be used only in one combination not more?
There are no comments posted here yet
Load More
Please leave your comments in English
Posting as Guest
×
Rate this post:
0   Characters
Suggested Locations