Namig: drugi jeziki so prevedeni v Google. Lahko obiščete English različico te povezave.
Vpiši se
x
or
x
x
Registracija
x

or

Kako najti vse kombinacije, ki so enake določeni vsoti v Excelu?

Na primer imam naslednji seznam številk in zdaj želim vedeti, katere kombinacije številk na seznamu seštevajo na 480, v naslednjem prikazu zaslona lahko vidite, da obstaja pet skupin možnih kombinacij, ki seštejejo enako na 480, na primer 300 + 60 + 120, 300 + 60 + 40 + 80 itd. Ta članek bom govoril o nekaterih metodah, da bi ugotovili, katere celice se bodo povzele v določeni vrednosti v Excelu.

doc najde kombinacijo enake vsote 1

Poiščite kombinacijo celic, ki je enaka določeni vsoti s formulami

Poiščite kombinacijo celic, ki je enaka določeni vsoti z dodatkom Solver

Poiščite kombinacijo celic, ki je enaka določeni vsoti s funkcijo, ki jo določa uporabnik


Seznam vseh možnih kombinacij iz več stolpcev:

Kutools za ExcelJe Seznam vseh kombinacij pripomoček vam lahko pomaga hitro ustvariti / seznam vseh možnih kombinacij na podlagi več seznamov vrednosti.

doc najde kombinacijo enake vsote 2

Kutools za Excel: z več kot 200 priročnimi dodatki Excel, lahko brezplačno poskusite brez omejitev v dnevih 60. Prenesite in brezplačno preizkusite zdaj!


Poiščite kombinacijo celic, ki je enaka določeni vsoti s formulami


Najprej morate ustvariti nekaj imen domene in nato uporabiti matrično formulo, da poiščete celice, ki seštejejo na ciljno vrednost, storite z naslednjim korakom za korakom:

1. Izberite seznam številk in določite ta seznam ime razdalje - Range1 v ime Boxin pritisnite Vnesite ključ za dokončanje določenega imena razpona, glejte sliko zaslona:

doc najde kombinacijo enake vsote 2

2. Po določitvi imena območja za seznam številk, morate ustvariti še dve nadaljnji imeniki v območju Ime Manager polje, kliknite Formule > Ime ManagerV Ime Manager pogovorno okno, kliknite New gumb, si oglejte posnetek zaslona:

doc najde kombinacijo enake vsote 3

3. V izpuščenem Novo ime pogovorno okno, vnesite ime Seznam1 v Imenu polje in vnesite to formulo = ROW (INDIRECT ("1:" in ROWS (Range1))) (Range1 je ime območja, ki ste ga ustvarili v koraku1) v Se nanaša na polje, si oglejte posnetek zaslona:

doc najde kombinacijo enake vsote 4

4. Kliknite OK da se vrnete v Ime Manager pogovorno okno, nato še kliknite New gumb, če želite ustvariti novo ime območja, v Novo ime pogovorno okno, vnesite ime Seznam2 v Imenu polje in vnesite to formulo = ROW (INDIRECT ("1:" in 2 ^ ROWS (Range1))) (Range1 je ime območja, ki ste ga ustvarili v koraku1) v Se nanaša na polje, si oglejte posnetek zaslona:

doc najde kombinacijo enake vsote 5

5. Ko ustvarite imena razponov, uporabite naslednjo formulo matrike v celico B1:

=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","")in pritisnite Shift + Ctrl + Enter ključe skupaj, nato povlecite ročico za polnjenje navzdol v celico B8, zadnjo številko seznama in si lahko ogledate številke, katerih skupna vsota je 480, označeni kot X v stolpcu B, si oglejte posnetek zaslona:

doc najde kombinacijo enake vsote 6

Opombe:

1. V zgornji dolgi formuli: Seznam1, Seznam2 in Range1 imena imen, ki ste jih ustvarili v prejšnjih korakih, C2 je specifična vrednost, za katero želite dodati številke.

2. Če ima več kot ena kombinacija vrednosti vsoto, ki je enaka določeni vrednosti, je navedena samo ena kombinacija.


Poiščite kombinacijo celic, ki je enaka določeni vsoti z dodatkom Solver

Če z zgornjo metodo zamenjate, Excel vsebuje Nadgradnja dodatka Z uporabo tega dodatka lahko določite tudi številke, katerih skupna vsota je enaka določeni vrednosti.

1. Najprej morate aktivirati to Reševalec dodatekProsim pojdi na file > možnostiV Možnosti programa Excel pogovorno okno, kliknite Dodatki v levem podoknu in kliknite Nadgradnja dodatka Iz Neaktivni dodatki aplikacij razdelek, glejte sliko zaslona:

doc najde kombinacijo enake vsote 7

2. Nato kliknite Go za vstop v Dodatki pogovor, preveri Nadgradnja dodatka možnost in kliknite OK namestite ta dodatek uspešno.

doc najde kombinacijo enake vsote 8

3. Po aktiviranju dodatka Solver, potem morate to formulo vnesti v celico B9: = SUMPRODUCT (B1: B8, A1: A8)(B1: B8 je prazna stolpnica poleg seznama številk in A1: A8 seznam številk, ki ga uporabljate. ) in pritisnite Vnesite ključ, si oglejte posnetek zaslona:

doc najde kombinacijo enake vsote 9

4. Nato kliknite datum > Reševalec Pojdite na Parameter Solver pogovorno okno, v pogovornem oknu, naredite naslednje operacije:

(1.) Kliknite doc gumb 1 gumb, da izberete celico B9 kjer je vaša formula iz Nastavi cilj oddelek;

(2.) Nato v Da izberite, izberite Vrednostin vnesite svojo ciljno vrednost 480 kot potrebujete;

(3.) Pod S spreminjanjem spremenljivih celic kliknite, prosim kliknite doc gumb 1 gumb, da izberete obseg celic B1: B8 kjer bodo označene vaše ustrezne številke.

doc najde kombinacijo enake vsote 10

5. In nato kliknite Dodaj gumb, da odprete Dodaj omejitev pogovorno okno, kliknite doc gumb 1 gumb, da izberete obseg celic B1: B8in izberite bin s spustnega seznama si oglejte sliko zaslona:

doc najde kombinacijo enake vsote 11

6. Kliknite OK da se vrnem Parameter Solver pogovorno okno, nato kliknite Rešite nekaj minut kasneje, a Rezultati rešitve pogovorno okno je izpuščeno in vidite kombinacijo celic, ki so enake določeni vsoti 480, označene kot 1. V Ljubljani Rezultati rešitve pogovorno okno, izberite Naj bo rešitev Solver možnost in kliknite OK za izhod iz pogovornega okna. Prikaz slike:

doc najde kombinacijo enake vsote 12

Opomba: Ta metoda lahko dobi tudi eno kombinacijo celic, če obstaja več kot ena kombinacija vrednosti, ki ima vsoto, ki je enaka določeni vrednosti.


Demo: Poiščite kombinacijo celic, ki je enaka določeni vsoti v Excelu


Poiščite kombinacijo celic, ki je enaka določeni vsoti s funkcijo, ki jo določa uporabnik

Prvi dve metodi sta za večino naših Excelovih uporabnikov zapleteni, tukaj lahko ustvarim kodo VBA za hitro in preprosto reševanje tega posla.

Da bi dobili pravi rezultat, morate najprej sortirati seznam številk v padajočem vrstnem redu. In naredite naslednje korake:

1. Drži dol ALT + F11 tipke za odpiranje Microsoft Visual Basic za aplikacije okno.

2. Kliknite Vstavi > Moduli, in prilepite naslednjo kodo v Moduli Okno.

VBA koda: poiščite kombinacijo celic, ki je enaka določenemu znesku:

Function GetCombination(CoinsRange As Range, SumCellId As Double) As String
'updateby Extendoffice 20160506
    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. Nato shranite in zaprite to okno kode, nato pa se vrnite na delovni list in vnesite to formulo = getcombination (A1: A8, C2) v prazno celico in pritisnite Vnesite ključ, boste dobili naslednji rezultat, ki prikazuje številke kombinacij, ki so enake določenemu znesku, glejte sliko zaslona:

doc najde kombinacijo enake vsote 13

Opombe:

1. V zgornji formuli, A1: A8 je obseg števila in C2 vsebuje ciljno vrednost, ki jo želite izenačiti.

2. Če ima več kot ena kombinacija vrednosti vsoto, ki je enaka določeni vrednosti, je navedena samo ena kombinacija.



Priporočena orodja za produktivnost za Excel

Kutools za Excel vam pomaga, da vedno končate delo pred časom in izstopite iz množice

  • Več kot zmogljive napredne funkcije 300, zasnovane za 1500 delovne scenarije, ki povečujejo produktivnost z 70%, vam dajejo več časa za skrb za družino in uživanje v življenju.
  • Ne potrebujete več pomnilniških formul in VBA kod, od zdaj naprej pa dajate svojim možganom počitek.
  • Postanite strokovnjak za Excel v minutah 3, zapletene in ponavljajoče se operacije lahko opravite v nekaj sekundah,
  • Vsak dan zmanjšajte število operacij tipkovnice in miške, zdaj se poslovite od poklicnih bolezni.
  • 110,000 visoko učinkovite ljudi in 300 + svetovno priznanih podjetij izbiro.
  • Brezplačna preizkusna različica 60 dneva. 60-dnevno jamstvo vračila denarja. 2 let brezplačne nadgradnje in podpore.

Prinaša kartično brskanje in urejanje za Microsoft Office, veliko močnejši od zavihkov brskalnika

  • Office Tab je namenjen za Word, Excel, PowerPoint in druge Office aplikacije: Založnik, Dostop, Visio in Projekt.
  • Odprite in ustvarite več dokumentov v novih zavihkih istega okna in ne v novih oknih.
  • Z 50% poveča vašo produktivnost in vsak dan zmanjša na stotine klikov z miško!
Say something here...
symbols left.
You are guest ( Sign Up? )
or post as a guest, but your post won't be published automatically.
Loading comment... The comment will be refreshed after 00:00.
  • To post as a guest, your comment is unpublished.
    Feroz · 26 days ago
    How do you do for the list of numbers like 480
  • To post as a guest, your comment is unpublished.
    Guilherme Dorn · 2 months ago
    Thank you very much! Resolved my problem correctly.
  • To post as a guest, your comment is unpublished.
    Miss Jones · 3 months ago
    Thank you so much for the VBA coding, it has solved a major headache trying to find combinations to equal an exact amount.
  • To post as a guest, your comment is unpublished.
    Stephanie · 4 months ago
    I have 1162 cells to find number x. Excel tells me that is too many variable cells. Very small data set! Any suggestions? Thanks!
  • To post as a guest, your comment is unpublished.
    a · 7 months ago
    Will the solver add-in not work if there are negative numbers in the list or if the value of number is 0? I'm trying to find a sum of numbers in a list that equate to zero with some numbers being negative and positive, but the solver does not work. I changed a couple numbers on my list to test to make sure I followed the steps correctly and it did work for the test. Please advise if there is a way to solve with negative and positive numbers to find a 0 value.