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
- 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:
- 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)
Krok 3: Nakonfigurujte a spusťte Solver, abyste získali výsledek
- 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 .
- 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:
- 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:
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
- Podržte stisknuté tlačítko ALT + F11 klíče v aplikaci Excel a otevře Microsoft Visual Basic pro aplikace okno.
- 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čtuPublic 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)
=TRANSPOSE(MakeupANumber(A2:A10,B2))
- 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.
- klikněte Kutools > Obsah > Doplňte číslo, viz screenshot:
- 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:
- 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:
- A nyní jsou všechny kombinace, které se rovnají danému číslu, zobrazeny jako níže uvedený snímek obrazovky:
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
- Podržte stisknuté tlačítko ALT + F11 klíče v aplikaci Excel a otevře Microsoft Visual Basic pro aplikace okno.
- 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ý rozsahSub 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
- 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:
- V druhém poli výzvy vyberte nebo zadejte číslo dolního limitu a klikněte OK. Viz snímek obrazovky:
- Ve třetím řádku výzvy vyberte nebo zadejte číslo horního limitu a klikněte OK. Viz snímek obrazovky:
- 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ů.
Nejlepší nástroje pro produktivitu v kanceláři
Rozšiřte své dovednosti Excel pomocí Kutools pro Excel a zažijte efektivitu jako nikdy předtím. Kutools for Excel nabízí více než 300 pokročilých funkcí pro zvýšení produktivity a úsporu času. Kliknutím sem získáte funkci, kterou nejvíce potřebujete...
Office Tab přináší do Office rozhraní s kartami a usnadňuje vám práci
- Povolte úpravy a čtení na kartách ve Wordu, Excelu, PowerPointu, Publisher, Access, Visio a Project.
- Otevřete a vytvořte více dokumentů na nových kartách ve stejném okně, nikoli v nových oknech.
- Zvyšuje vaši produktivitu o 50%a snižuje stovky kliknutí myší každý den!