Přejít k hlavnímu obsahu

Jak najít všechny kombinace, které se v aplikaci Excel rovnají danému součtu?

Objevování všech možných kombinací čísel v seznamu, které se sčítají do určitého součtu, je problém, se kterým se může setkat mnoho uživatelů Excelu, ať už pro účely rozpočtování, plánování nebo analýzy dat.

V tomto příkladu máme seznam čísel a cílem je identifikovat, které kombinace z tohoto seznamu mají součet 480. Poskytnutý snímek obrazovky ukazuje, že existuje pět možných skupin kombinací, které dosahují tohoto součtu, včetně kombinací jako 300+120 +60, 250+120+60+50, mimo jiné. V tomto článku prozkoumáme různé metody, jak určit konkrétní kombinace čísel v seznamu, které dohromady tvoří určenou hodnotu v Excelu.

Najděte kombinaci čísel rovných danému součtu pomocí funkce Řešitel

Získejte všechny kombinace čísel rovné danému součtu

Získejte všechny kombinace čísel, které mají součet v rozsahu, pomocí kódu VBA


Najděte kombinaci buněk, která se rovná danému součtu pomocí funkce Řešitel

Ponořit se do Excelu za účelem nalezení kombinací buněk, které sčítají konkrétní číslo, se může zdát skličující, ale s doplňkem Řešitel je to hračka. Provedeme vás jednoduchými kroky k nastavení Solveru a nalezení správné kombinace buněk, takže to, co vypadalo jako složitý úkol, bude jednoduché a proveditelné.

Krok 1: Povolte doplněk Řešitel

  1. Prosím, jděte na Soubor > možnosti, V roce Možnosti aplikace Excel , klepněte na tlačítko OK Add-Ins v levém podokně poté klepněte na Go knoflík. Viz screenshot:
  2. Poté Add-Ins se objeví dialogové okno, zkontrolujte Doplněk Řešitele a klepněte na tlačítko OK úspěšně nainstalovat tento doplněk.

Krok 2: Zadejte vzorec

Po aktivaci doplňku Řešitel musíte do buňky B11 zadat tento vzorec:

=SUMPRODUCT(B2:B10,A2:A10)
Poznámka: V tomto vzorci: B2: B10 je sloupec prázdných buněk vedle vašeho číselného seznamu a A2: A10 je číselný seznam, který používáte.

Krok 3: Nakonfigurujte a spusťte Solver, abyste získali výsledek

  1. klikněte Data > Řešitel přejděte na Řešitel Parametr v dialogovém okně proveďte následující operace:
    • (1.) Klikněte vyberte buňku B11 kde se váš vzorec nachází z Stanovit cíl sekce;
    • (2.) Pak v Na vyberte část Hodnotaa zadejte cílovou hodnotu 480 jak potřebujete;
    • (3.) Pod Změnou proměnných buněk v sekci klikněte prosím tlačítko pro výběr rozsahu buněk B2: B10 kde označí vaše odpovídající čísla.
    • (4.) Poté klikněte na přidat .
  2. Pak, an Přidat omezení Zobrazí se dialogové okno, klepněte na tlačítko pro výběr rozsahu buněk B2: B10a vyberte popelnice z rozevíracího seznamu. Konečně klikni OK knoflík. Viz screenshot:
  3. v Řešitel Parametr , klepněte na tlačítko Vyřešit tlačítko, o několik minut později, a Výsledky řešitele vyskočí dialogové okno a můžete vidět kombinaci buněk, které se rovnají danému součtu 480, jsou označeny jako 1 ve sloupci B. Výsledky řešitele dialogové okno, vyberte Ponechat řešení Řešitele a klepněte na tlačítko OK pro opuštění dialogu. Viz snímek obrazovky:
Poznámka: Tato metoda má však omezení: dokáže identifikovat pouze jednu kombinaci buněk, jejichž součet tvoří zadaný součet, i když existuje více platných kombinací.

Získejte všechny kombinace čísel rovné danému součtu

Prozkoumání hlubších možností Excelu vám umožní najít každou číselnou kombinaci, která odpovídá konkrétnímu součtu, a je to jednodušší, než si možná myslíte. Tato část vám ukáže dva způsoby, jak najít všechny kombinace čísel rovných danému součtu.

Získejte všechny kombinace čísel rovnající se danému součtu pomocí funkce definované uživatelem

Chcete-li odhalit každou možnou kombinaci čísel z konkrétní sady, která společně dosahuje dané hodnoty, slouží jako účinný nástroj vlastní funkce popsaná níže.

Krok 1: Otevřete editor modulu VBA a zkopírujte kód

  1. Podržte stisknuté tlačítko ALT + F11 klíče v aplikaci Excel a otevře Microsoft Visual Basic pro aplikace okno.
  2. klikněte Vložit > Modula vložte následující kód do okna modulu.
    Kód VBA: Získejte všechny kombinace čísel rovnající se danému součtu
    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
    

Krok 2: Zadejte vlastní vzorec, abyste získali výsledek

Po vložení kódu zavřete okno kódu a vraťte se zpět do listu. Zadejte následující vzorec do prázdné buňky pro výstup výsledku a stiskněte vstoupit klíč k získání všech kombinací. Viz snímek obrazovky:

=MakeupANumber(A2:A10,B2)
Poznámka: V tomto vzorci: A2: A10 je číselný seznam a B2 je celková částka, kterou chcete získat.

Tip: Pokud chcete výsledky kombinací uvést svisle ve sloupci, použijte prosím následující vzorec:
=TRANSPOSE(MakeupANumber(A2:A10,B2))
Omezení této metody:
  • Tato vlastní funkce funguje pouze v Excelu 365 a 2021.
  • Tato metoda je účinná výhradně pro kladná čísla; desetinné hodnoty jsou automaticky zaokrouhleny na nejbližší celé číslo a záporná čísla budou mít za následek chyby.

Získejte všechny kombinace čísel rovnající se danému součtu pomocí výkonné funkce

Vzhledem k omezením výše uvedené funkce doporučujeme rychlé a komplexní řešení: Kutools for Excel's Make up a Number, která je kompatibilní s jakoukoli verzí Excelu. Tato alternativa dokáže efektivně zpracovat kladná, desetinná i záporná čísla. Pomocí této funkce můžete rychle získat všechny kombinace, které se rovnají danému součtu.

Tipy: Použít toto Doplňte číslo funkci, nejprve byste si měli stáhnout Kutools pro Excela poté tuto funkci rychle a snadno aplikujte.
  1. klikněte Kutools > Obsah > Doplňte číslo, viz screenshot:
  2. Pak v Vytvořte číslo dialogové okno, klikněte prosím tlačítko a vyberte číselný seznam, který chcete použít z Zdroj data poté zadejte celkový počet do Součet Textové pole. Nakonec klikněte OK tlačítko, viz screenshot:
  3. A pak se objeví okno s výzvou, které vám připomene, abyste vybrali buňku pro nalezení výsledku, a potom klepněte OK, viz screenshot:
  4. A nyní jsou všechny kombinace, které se rovnají danému číslu, zobrazeny jako níže uvedený snímek obrazovky:
Poznámka: Chcete-li použít tuto funkci, prosím stáhnout a nainstalovat Kutools pro Excel jako první.

Získejte všechny kombinace čísel, které mají součet v rozsahu, pomocí kódu VBA

Někdy se můžete dostat do situace, kdy potřebujete identifikovat všechny možné kombinace čísel, které dohromady tvoří součet v určitém rozsahu. Můžete se například snažit najít všechna možná seskupení čísel, kde součet spadá mezi 470 a 480.

Objevování všech možných kombinací čísel, které dávají dohromady hodnotu v určitém rozsahu, představuje v Excelu fascinující a vysoce praktickou výzvu. Tato část představí kód VBA pro řešení tohoto úkolu.

Krok 1: Otevřete editor modulu VBA a zkopírujte kód

  1. Podržte stisknuté tlačítko ALT + F11 klíče v aplikaci Excel a otevře Microsoft Visual Basic pro aplikace okno.
  2. klikněte Vložit > Modula vložte následující kód do okna modulu.
    Kód VBA: Získejte všechny kombinace čísel, které dávají dohromady určitý rozsah
    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
    
    
    

Krok 2: Spusťte kód

  1. Po vložení kódu stiskněte F5 klíč pro spuštění tohoto kódu, v prvním vyskakovacím dialogu vyberte rozsah čísel, který chcete použít, a klikněte OK. Viz snímek obrazovky:
  2. V druhém poli výzvy vyberte nebo zadejte číslo dolního limitu a klikněte OK. Viz snímek obrazovky:
  3. Ve třetím řádku výzvy vyberte nebo zadejte číslo horního limitu a klikněte OK. Viz snímek obrazovky:
  4. V poli poslední výzvy vyberte výstupní buňku, kde se začnou vypisovat výsledky. Pak klikněte OK. Viz snímek obrazovky:

Výsledek

Nyní bude každá kvalifikující kombinace uvedena v po sobě jdoucích řádcích v listu, počínaje výstupní buňkou, kterou jste vybrali.

Excel vám nabízí několik způsobů, jak najít skupiny čísel, jejichž součet tvoří určitý součet, každá metoda funguje jinak, takže si můžete vybrat jednu podle toho, jak dobře znáte Excel a co potřebujete pro svůj projekt. Pokud máte zájem prozkoumat další tipy a triky pro Excel, naše webové stránky nabízejí tisíce výukových programů pro přístup k nim klikněte sem. Děkujeme za přečtení a těšíme se, že vám v budoucnu poskytneme další užitečné informace!


Související články:

  • Vypište nebo vygenerujte všechny možné kombinace
  • Řekněme, že mám následující dva sloupce dat, a teď chci vygenerovat seznam všech možných kombinací na základě dvou seznamů hodnot, jak je zobrazen levý snímek obrazovky. Možná můžete vypsat všechny kombinace jeden po druhém, pokud existuje několik hodnot, ale pokud existuje několik sloupců s více hodnotami, aby bylo možné uvést možné kombinace, zde je několik rychlých triků, které vám mohou pomoci při řešení tohoto problému v aplikaci Excel .
  • Vypište všechny možné kombinace z jednoho sloupce
  • Pokud chcete vrátit všechny možné kombinace z dat jednoho sloupce, abyste získali výsledek, jak je zobrazen níže na snímku obrazovky, máte nějaké rychlé způsoby, jak se s tímto úkolem v Excelu vypořádat?
  • Vygenerujte všechny kombinace 3 nebo více sloupců
  • Předpokládejme, že mám 3 sloupce dat, teď chci generovat nebo vypsat všechny kombinace dat v těchto 3 sloupcích, jak je uvedeno níže. Máte nějaké dobré metody pro řešení tohoto úkolu v aplikaci Excel?
  • Vytvořte seznam všech možných kombinací 4 číslic
  • V některých případech možná budeme muset vygenerovat seznam všech možných čtyřmístných kombinací čísel 4 až 0, což znamená vygenerovat seznam 9, 0000, 0001 ... 0002. Abych rychle vyřešil úlohu seznamu v aplikaci Excel, představuji vám několik triků.
Comments (49)
No ratings yet. Be the first to rate!
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?
This comment was minimized by the moderator on the site
kutools works only integer value. Not support double. Like (395,52) ! Best solution is excel solver extention.
This comment was minimized by the moderator on the site
is there a way to find combination for a target average instead of sum ?
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