Note: The other languages of the website are Google-translated. Back to English

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

Například mám následující seznam čísel a teď chci vědět, která kombinace čísel v seznamu sečte až 480, na následujícím zobrazeném snímku obrazovky můžete vidět, že existuje pět skupin možných kombinací, které přidávají stejné na 480, například 300 + 60 + 120, 300 + 60 + 40 + 80 atd. V tomto článku budu hovořit o některých metodách, jak zjistit, které buňky sečtou určitou hodnotu v aplikaci Excel.


Najděte kombinaci buněk, která se rovná danému součtu pomocí vzorců

Nejprve musíte vytvořit několik názvů rozsahů a poté použít maticový vzorec k vyhledání buněk, které se shodují s cílovou hodnotou, proveďte následující krok za krokem:

1. Vyberte číselný seznam a definujte tento seznam název rozsahu - Rozsah 1 do Název Box, a stiskněte tlačítko vstoupit klíč k dokončení definovaného názvu rozsahu, viz screenshot:

2. Po definování názvu rozsahu pro číselný seznam musíte vytvořit další dva názvy rozsahů v Správce jmen pole, klikněte prosím Vzorce > Správce jmen, V roce Správce jmen , klepněte na tlačítko OK Nový tlačítko, viz screenshoty:

3. Ve vyskočené Nové jméno dialogové okno, zadejte název Seznam1 do Jméno a Příjmení do pole a zadejte tento vzorec = ŘÁDEK (NEPŘÍMÝ ("1:" & ŘÁDKY (Rozsah1))) (Rozsah 1 je název rozsahu, který jste vytvořili v kroku 1) do Odkazuje na pole, viz screenshot:

4, klikněte OK k návratu na Správce jmen v dialogovém okně a poté klikněte Nový tlačítko pro vytvoření jiného názvu rozsahu v Nové jméno dialogové okno, zadejte název Seznam2 do Jméno a Příjmení do pole a zadejte tento vzorec = ŘÁDEK (NEPŘÍMÝ ("1:" & 2 ^ ŘÁDKY (Rozsah1))) (Rozsah 1 je název rozsahu, který jste vytvořili v kroku 1) do Odkazuje na pole, viz screenshot:

5. Po vytvoření názvů rozsahů použijte do buňky B2 následující vzorec pole:

=IF(ISNUMBER(MATCH(ROWS($1:1),IF(INDEX(MOD(INT((List2-1)/2^(TRANSPOSE(List1)-1)),2),MATCH(TRUE,MMULT(MOD(INT((List2-1)/2^(TRANSPOSE(List1)-1)),2),Range1)=$C$2,0),),TRANSPOSE(List1)),0)),"X",""), a stiskněte tlačítko Shift + Ctrl + Enter klíče, pak přetáhněte popisovač výplně dolů do buňky B8, posledního čísla seznamu a uvidíte čísla, jejichž celková částka je 480, jsou označena jako X ve sloupci B, viz screenshot:

  • Poznámky:
  • Ve výše uvedeném dlouhém vzorci: Seznam1, Seznam2 a Rozsah 1 jsou názvy rozsahů, které jste vytvořili v předchozích krocích, C2 je konkrétní hodnota, ke které chcete čísla přidat.
  • Pokud má více než jedna kombinace hodnot součet rovný konkrétní hodnotě, je uvedena pouze jedna kombinace.

V aplikaci Excel rychle a snadno vyhledejte a vypsejte všechny kombinace, které se rovnají danému součtu

Kutools pro Excel's Doplňte číslo obslužný program vám pomůže rychle a snadno najít a vypsat všechny kombinace a konkrétní kombinace, které se rovnají danému číslu součtu. Klikněte a stáhněte si Kutools pro Excel!

Kutools pro Excel: s více než

300
praktické doplňky aplikace Excel, které lze zdarma vyzkoušet bez omezení
30
dnů. Stáhněte si a vyzkoušejte zdarma hned teď!


Najděte kombinaci buněk, která se rovná danému součtu, pomocí doplňku Řešitele

Pokud jste zmateni výše uvedenou metodou, Excel obsahuje a Doplněk Řešitele Pomocí tohoto doplňku můžete také určit čísla, jejichž celková částka se rovná dané hodnotě.

1. Nejprve to musíte aktivovat Řešitel add-in, prosím jdi na Soubor > možnosti, V roce Možnosti aplikace Excel , klepněte na tlačítko OK Add-Ins v levém podokně a potom klepněte na Doplněk Řešitele z Neaktivní doplňky aplikace sekce, viz screenshot:

2. Pak klikněte na tlačítko Go pro zadání Add-Ins dialog, zkontrolujte Doplněk Řešitele a klepněte na tlačítko OK úspěšně nainstalovat tento doplněk.

3. Po aktivaci doplňku Řešitel musíte tento vzorec zadat do buňky B9: = SUMPRODUCT (B2: B9, A2: A9), (B2: B9 je prázdné buňky sloupce vedle vašeho číselného seznamu a A2: A9 je seznam čísel, který používáte. ) a stiskněte vstoupit klíč, viz screenshot:

4. Pak klikněte na tlačítko 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 B10 kde je váš vzorec 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: B9 kde označí vaše odpovídající čísla.

5. A pak klikněte na tlačítko přidat pro přechod na Přidat omezení , klepněte na tlačítko OK tlačítko pro výběr rozsahu buněk B2: B9a vyberte popelnice z rozevíracího seznamu viz snímek obrazovky:

6, klikněte OK vrátit se zpět Řešitel Parametr v dialogovém okně a poté klikněte na 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 je označeno jako 1. V 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 je také možné získat pouze jednu kombinaci buněk, pokud existuje více než jedna kombinace hodnot, jejichž součet se rovná konkrétní hodnotě.


Najděte kombinaci buněk, která se rovná danému součtu, pomocí funkce definované uživatelem

První dvě metody jsou pro většinu našich uživatelů aplikace Excel komplexní, zde mohu vytvořit kód VBA, abych tuto práci vyřešil rychle a snadno.

Chcete-li získat správný výsledek, musíte nejprve seřadit číselný seznam v sestupném pořadí. A pak proveďte následující kroky:

1. Podržte ALT + F11 klávesy pro otevření Microsoft Visual Basic pro aplikace okno.

2, klikněte Vložit > Modula vložte následující kód do Modul Okno.

Kód VBA: Najděte kombinaci buněk, která se rovná danému součtu:

Function GetCombination(CoinsRange As Range, SumCellId As Double) As String
'updateby Extendoffice
    Dim xStr As String
    Dim xSum As Double
    Dim xCell As Range
    xSum = SumCellId
    For Each xCell In CoinsRange
        If Not (xSum / xCell < 1) Then
            xStr = xStr & Int(xSum / xCell) & " of " & xCell & "  "
            xSum = xSum - (Int(xSum / xCell)) * xCell
        End If
    Next
    GetCombination = xStr
End Function

3. Poté uložte a zavřete toto okno s kódem, poté se vraťte zpět do listu a zadejte tento vzorec = getcombination (A2: A9, C2) do prázdné buňky a stiskněte vstoupit klíč, získáte následující výsledek, který zobrazí čísla kombinací, která se rovnají danému součtu, viz screenshot:

  • Poznámky:
  • Ve výše uvedeném vzorci A2: A9 je číselný rozsah a C2 obsahuje cílovou hodnotu, které se chcete rovnat.
  • Pokud má více než jedna kombinace hodnot součet rovný konkrétní hodnotě, je uvedena pouze jedna kombinace.

Najděte všechny kombinace, které se rovnají danému součtu, s úžasnou funkcí

Možná jsou pro vás všechny výše uvedené metody poněkud obtížné, zde představím výkonný nástroj, Kutools pro Excel, S jeho Doplňte číslo Funkce, můžete rychle získat všechny kombinace, které se rovnají danému součtu.

Tip:Použít toto Doplňte číslo funkce, za prvé, měli byste si stáhnout Kutools pro Excela poté tuto funkci rychle a snadno aplikujte.

Po instalaci Kutools pro Excel, udělejte prosím toto:

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, viz screenshot:

3. A pak klikněte OK tlačítko, vyskočí okno s výzvou, které vám připomene, abyste vybrali buňku k vyhledání výsledku, viz screenshot:

4. Poté klikněte na OK, a nyní jsou zobrazeny všechny kombinace, které se rovnají danému číslu, jak je uvedeno níže:

Klikněte a stáhněte si Kutools pro Excel a bezplatnou zkušební verzi hned teď!


Demo: Najděte kombinaci buněk, která se rovná danému součtu v aplikaci Excel


Nejlepší kancelářské nástroje produktivity

Kutools pro Excel řeší většinu vašich problémů a zvyšuje vaši produktivitu o 80%

  • Opakované použití: Rychle vložte složité vzorce, grafy a cokoli, co jste dříve používali; Šifrovat buňky s heslem; Vytvořte seznam adresátů a posílat e-maily ...
  • Super Formula Bar (snadno upravit více řádků textu a vzorce); Rozložení pro čtení (snadno číst a upravovat velké množství buněk); Vložit do filtrovaného rozsahu...
  • Sloučit buňky / řádky / sloupce bez ztráty dat; Rozdělit obsah buněk; Zkombinujte duplicitní řádky / sloupce... Zabraňte duplicitním buňkám; Porovnat rozsahy...
  • Vyberte možnost Duplikovat nebo Jedinečný Řádky; Vyberte prázdné řádky (všechny buňky jsou prázdné); Super hledání a fuzzy hledání v mnoha sešitech; Náhodný výběr ...
  • Přesná kopie Více buněk beze změny odkazu na vzorec; Automaticky vytvářet reference do více listů; Vložte odrážky, Zaškrtávací políčka a další ...
  • Extrahujte text, Přidat text, Odebrat podle pozice, Odebrat mezeru; Vytváření a tisk mezisoučtů stránkování; Převod mezi obsahem buněk a komentáři...
  • Super filtr (uložit a použít schémata filtrů na jiné listy); Rozšířené řazení podle měsíce / týdne / dne, frekvence a dalších; Speciální filtr tučnou kurzívou ...
  • Kombinujte sešity a pracovní listy; Sloučit tabulky na základě klíčových sloupců; Rozdělte data do více listů; Dávkový převod xls, xlsx a PDF...
  • Více než 300 výkonných funkcí. Podporuje Office / Excel 2007-2021 a 365. Podporuje všechny jazyky. Snadné nasazení ve vašem podniku nebo organizaci. Plné funkce 30denní bezplatná zkušební verze. 60denní záruka vrácení peněz.
karta kte 201905

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!
officetab dno
Komentáře (46)
Zatím žádné hodnocení. Buďte první, kdo ohodnotí!
Tento komentář byl moderátorem webu minimalizován
Existuje způsob, jak rozšířit rozsah tak, aby zahrnoval více než 8 čísel? Také si nejsem jistý, jak tato funkce funguje: "=ROW(NEPŘÍMÝ("1:"&2^ROWS(Range1)))". Pokud se pokusím rozšířit "Range1" nad 15 řádků, zobrazí se chyba #Ref. Funguje to skvěle jen s 8 čísly, ale co kdybyste chtěli zahrnout řekněme 50 čísel nebo dokonce 100.
Tento komentář byl moderátorem webu minimalizován
Stejný problém
Tento komentář byl moderátorem webu minimalizován
Skvělý člověk!!! Skvělý člověk!!!
Tento komentář byl moderátorem webu minimalizován
Existuje způsob, jak rozšířit rozsah, jak říká Thom, říci až 50 čísel, ale také sečíst pouze šest čísel mimo rozsah, jejichž součet odpovídá zadanému součtu? V současné době bude poskytovat všechny kombinace, jejichž celkový součet odpovídá zadanému součtu. dík
Tento komentář byl moderátorem webu minimalizován
Úžasný. Velký vzorec se nepodařilo zprovoznit, ale doplněk řešiče fungoval perfektně. Ušetřil mi tolik práce.
Tento komentář byl moderátorem webu minimalizován
ale zabere to příliš mnoho času
Tento komentář byl moderátorem webu minimalizován
V Excelu jsem maximálně pokročilý začátečník. Zkoušel jsem všechno a nefungovalo to. Co bych mohl dělat špatně?
Tento komentář byl moderátorem webu minimalizován
jak když potřebuji více než jednu kombinaci? Děkuji
Tento komentář byl moderátorem webu minimalizován
Ahoj. Nefungovala mi ani formulová verze. Zdá se, že tomu chybí krok. Nevidím, kde do vzorce přichází číslo uvedené v buňce C2.

Díky
Tento komentář byl moderátorem webu minimalizován
Dobrý den, Dori,


V C2 není žádný vzorec, je to jen konkrétní hodnota, ke které chcete čísla sečíst.
Tento komentář byl moderátorem webu minimalizován
Ahoj, stáhl jsem si Kutools, ale nemůžu najít všechna komba menší než zadaný součet.
Tento komentář byl moderátorem webu minimalizován
Byl jsem schopen získat příklad s Range1, aby pracoval s mým rozsahem ve 12 řádcích, ale když jsem změnil rozsah na 42 řádků, nefungovalo to. Dokonce jsem restartoval celý proces s verzí 42 řádků a ani to nefungovalo. Nějaké nápady?
Tento komentář byl moderátorem webu minimalizován
Brilantní!!!
Tento komentář byl moderátorem webu minimalizován
Mohl bys nahrát excel?
Tento komentář byl moderátorem webu minimalizován
Makro nefungovalo, pokud existuje více než jedno řešení.
Také jsem nefungoval, pokud najdu "0"
Tento komentář byl moderátorem webu minimalizován
Dobrý den, Lorena,
Před použitím výše uvedeného kódu VBA musíte nejprve seřadit číselný seznam v sestupném pořadí.
Za druhé, kód nefunguje správně, aby získal celkové číslo 0.
Doufám, že vám to pomůže, děkuji!
Tento komentář byl moderátorem webu minimalizován
Snažím se určit nejlepší směs produktu a nejsem si jistý, zda je to nejlepší způsob, jak to udělat. Používám maximálně tři produkty ve směsi, každý s 5 specifikacemi. Všechny specifikace jsou lineární a lze je po smíchání zprůměrovat. Jedna směs je obvykle 45,000 30,000 liber a každá dávka 15 30 liber. Většinu času jsou naše směsi 2000 XNUMX + XNUMX XNUMX, ale rád bych byl schopen vypočítat pro neobvyklé směsi pomocí přírůstků až do XNUMX liber.
Tento komentář byl moderátorem webu minimalizován
Dobrý den,

Můj problém s tímto vzorcem je, že mi dává jednu hodnotu tolikrát, abych získal cílovou hodnotu.
V seznamu různých hodnot jsou některé hodnoty, které jsou si navzájem shodné.

Např. mám 0,16 pro 3 časy (první hodnoty v seznamu) a vzorec mi dává odpověď, že moje cílová hodnota je 593 z 0,16.

Proč nekombinuje různé hodnoty, abych získal moji cílovou hodnotu? Vybere pouze jednu hodnotu a udává, kolikrát má být cílovou hodnotou.

Nějaká pomoc nebo nápad?


Díky!
Tento komentář byl moderátorem webu minimalizován
neví někdo, jestli to funguje na google listech
Tento komentář byl moderátorem webu minimalizován
Ano, existuje rozšíření podobné řešiču aplikace Excel s názvem „řešič“
Tento komentář byl moderátorem webu minimalizován
Věděl by někdo, jak upravit funkci VBA Getcombination tak, aby nebylo povoleno žádné opakování?

Například pro čísla 1,2,3,4,5,13, ​​pokud má být dosaženo 14, je řešením 1,13 a ne 14 z 1.
Tento komentář byl moderátorem webu minimalizován
Funkce GetCombination(CoinsRange As Range, SumCellId As Double) As String
'aktualizovat od Extendoffice 20160506
Dim xStr jako řetězec
Dim xSum As Double
Dim xCell As Range
xSum = SumCellId
Pro každý xCell v CoinsRange
Pokud ne (xSum / xCell < 1) Pak
xStr = xStr & "1 z " & xCell & " "
xSum = xSum - xCell
End If
další
GetCombination = xStr
End Function
Tento komentář byl moderátorem webu minimalizován
ahoj, dává mi to nejednoznačnou chybu názvu pro kód vba
jakákoli pomoc, protože ve VBA nic nevím
Tento komentář byl moderátorem webu minimalizován
Ahoj Ram, funguje to dobře, ale neudává skutečnou částku.
EX: pokud mám 23,34,25,28,10,17&12 a mám součet 80 (což je součet 23,28,17&12), potřebuji vba kód, který dokáže najít tuto kombinaci (součet 23,28,17 ,12&XNUMX) Můžete mi s tím prosím pomoci?
Tento komentář byl moderátorem webu minimalizován
Ahoj,
Mnohokrát děkuji za informace;
Jak lze najít nejpřibližnější kombinace, pokud neexistuje přesná hodnota.
Mnohokrát děkuji,
Tento komentář byl moderátorem webu minimalizován
Ahoj,
Díky to je moc dobré,
Jak lze najít nejpřibližnější kombinace, pokud neexistuje přesná hodnota.
Mnohokrát děkuji
Tento komentář byl moderátorem webu minimalizován
Nebude doplněk řešitele fungovat, pokud jsou v seznamu záporná čísla nebo pokud je hodnota čísla 0? Snažím se najít součet čísel v seznamu, který se rovná nule, přičemž některá čísla jsou záporná a kladná, ale řešič nefunguje. Změnil jsem pár čísel na svém seznamu, abych otestoval, abych se ujistil, že postupuji správně a že to v testu fungovalo. Prosím o radu, zda existuje způsob, jak vyřešit zápornými a kladnými čísly, jak najít hodnotu 0.
L     a
Tento komentář byl moderátorem webu minimalizován
dostali jste někdy odpověď nebo jste našli způsob, jak to udělat?
Tento komentář byl moderátorem webu minimalizován
Ahoj,
Pokud jsou ve sloupci kladná i záporná čísla, doporučuji vám použít funkci Make up a number Kutools pro Excel, může váš problém rychle a snadno vyřešit.

Můžete si stáhnout Kutools pro Excel a bezplatnou zkušební dobu 60 dní. Zkuste to prosím!
Tento komentář byl moderátorem webu minimalizován
Mám 1162 buněk k nalezení čísla x. Excel mi říká, že je příliš mnoho proměnných buněk. Velmi malý soubor dat! Nějaké návrhy? Dík!
Zatím zde nejsou žádné komentáře
Načíst další
Zanechat své připomínky
Odesílání jako host
×
Ohodnoťte tento příspěvek:
0   Postavy
Doporučená umístění

Sociální sítě

Copyright © 2009 - www.extendoffice.com. | Všechna práva vyhrazena. Poháněno ExtendOffice. | |. | Sitemap
Microsoft a logo Office jsou ochranné známky nebo registrované ochranné známky společnosti Microsoft Corporation ve Spojených státech a / nebo jiných zemích.
Chráněno Sectigo SSL