Note: The other languages of the website are Google-translated. Back to English
Inloggen  \/ 
x
or
x
Registreer  \/ 
x

or

Hoe vind ik alle combinaties die gelijk zijn aan een bepaalde som in Excel?

Ik heb bijvoorbeeld de volgende lijst met getallen, en nu wil ik weten welke combinatie van getallen in de lijst 480 is, in de volgende schermafbeelding zie je dat er vijf groepen mogelijke combinaties zijn die gelijk zijn tot 480, zoals 300 + 60 + 120, 300 + 60 + 40 + 80, enz. In dit artikel zal ik het hebben over enkele methoden om te achterhalen welke cellen een bepaalde waarde in Excel opleveren.


Zoek een celcombinatie die gelijk is aan een bepaalde som met formules

Eerst moet u een aantal bereiknamen maken en vervolgens een matrixformule toepassen om de cellen te vinden die optellen bij de doelwaarde. Voer de volgende stap voor stap uit:

1. Selecteer de nummerlijst en definieer voor deze lijst een bereiknaam-- Bereik 1 in de Naam Boxen druk op Enter toets om de gedefinieerde bereiknaam te voltooien, zie screenshot:

2. Nadat u een bereiknaam voor de nummerlijst hebt gedefinieerd, moet u nog twee bereiknamen maken in de Name Manager box, klik dan Formules > Name Manager, in de Name Manager dialoogvenster, klik Nieuw knop, zie screenshots:

3. In de pop-out Nieuwe naam voer een naam in List1 in de Naam veld en typ deze formule = RIJ (INDIRECT ("1:" & RIJEN (Bereik1))) (Bereik 1 is de bereiknaam die u in stap1) hebt gemaakt in de Verwijst naar veld, zie screenshot:

4. Klikken OK terugkeren naar de Name Manager dialoogvenster en blijf klikken Nieuw om een ​​andere bereiknaam te maken, in de Nieuwe naam voer een naam in List2 in de Naam veld en typ deze formule = RIJ (INDIRECT ("1:" & 2 ^ RIJEN (Bereik1))) (Bereik 1 is de bereiknaam die u in stap1) hebt gemaakt in de Verwijst naar veld, zie screenshot:

5. Pas na het maken van de bereiknamen de volgende matrixformule toe in cel 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","")en druk op Shift + Ctrl + Enter toetsen samen, sleep vervolgens de vulgreep naar cel B8, het laatste nummer van de lijst, en je kunt zien dat de nummers waarvan het totale aantal 480 is, zijn gemarkeerd als X in kolom B, zie screenshot:

  • Opmerkingen:
  • In de bovenstaande lange formule: List1, List2 als Bereik 1 zijn de bereiknamen die u in eerdere stappen hebt gemaakt, C2 is de specifieke waarde waaraan u getallen wilt toevoegen.
  • Als meer dan één combinatie van waarden een som heeft die gelijk is aan de specifieke waarde, wordt slechts één combinatie vermeld.

Zoek en vermeld alle combinaties die gelijk zijn aan een bepaalde som snel en gemakkelijk in Excel

Kutools for Excel's Verzin een nummer hulpprogramma kan u helpen om alle combinaties en specifieke combinaties die gelijk zijn aan een bepaald somnummer snel en gemakkelijk te vinden en weer te geven. Klik om Kutools voor Excel te downloaden!

Kutools for Excel: met meer dan 300 handige Excel-invoegtoepassingen, gratis te proberen zonder beperking in 30 dagen. Download en probeer nu gratis!


Zoek een celcombinatie die gelijk is aan een gegeven som met de Oplosser-invoegtoepassing

Als u verward bent met de bovenstaande methode, bevat Excel een Oplosser-invoegtoepassing functie, door deze add-in te gebruiken, kunt u ook de getallen identificeren waarvan het totale bedrag gelijk is aan een bepaalde waarde.

1. U moet dit eerst activeren Oplosser add-in, Ga aub naar Dien in > Opties, in de Excel-opties dialoogvenster, klik Add-Ins in het linkerdeelvenster en klik vervolgens op Oplosser-invoegtoepassing van de Inactieve invoegtoepassingen voor apps sectie, zie screenshot:

2. Dan klikken Go om het te openen Add-Ins dialoogvenster, vink aan Oplosser-invoegtoepassing optie en klik op OK om deze invoegtoepassing met succes te installeren.

3. Nadat u de invoegtoepassing Oplosser hebt geactiveerd, moet u deze formule invoeren in cel B9: = SOMPRODUCT (B2: B9; A2: A9)(B2: B9 is een lege kolomcel naast uw nummerlijst, en A2: A9 is de nummerlijst die u gebruikt. ) en druk op Enter key, zie screenshot:

4. Dan klikken Data > Oplosser naar de Oplosserparameter dialoogvenster, voer in het dialoogvenster de volgende bewerkingen uit:

(1.) Klik  knop om de cel te selecteren B10 waar uw formule uit de Stel een doelstelling in sectie;

(2.) Vervolgens in de Naar sectie, selecteer Waarde vanen voer uw streefwaarde in 480 zoals je nodig hebt;

(3.) Onder de Door variabele cellen te veranderen sectie, klik dan om het celbereik te selecteren B2: B9 waar zal uw overeenkomstige nummers markeren.

5. En klik dan Toevoegen knop om naar de Beperking toevoegen dialoogvenster, klik om het celbereik te selecteren B2: B9En Select bak zie screenshot uit de vervolgkeuzelijst:

6. Klikken OK om terug te gaan naar het Oplosserparameter dialoogvenster en klik op Oplossen knop, enkele minuten later, een Oplosser resultaten dialoogvenster verschijnt en u kunt zien dat de combinatie van cellen die gelijk zijn aan een gegeven som 480 zijn gemarkeerd als 1. In het Oplosser resultaten dialoogvenster, selecteer alstublieft Houd Oplosser-oplossing optie en klik op OK om het dialoogvenster te verlaten. Zie screenshot:

Opmerking:: Deze methode kan ook slechts één combinatiecel krijgen als er meer dan één combinatie van waarden is met een som gelijk aan de specifieke waarde.


Zoek een celcombinatie die gelijk is aan een bepaalde som met de door de gebruiker gedefinieerde functie

De eerste twee methoden zijn allemaal complex voor de meeste van onze Excel-gebruikers, hier kan ik een VBA-code maken om deze taak snel en gemakkelijk op te lossen.

Om het juiste resultaat te krijgen, moet u de nummerlijst eerst in aflopende volgorde sorteren. En doe dan met de volgende stappen:

1. Houd de ALT + F11 toetsen om de te openen Microsoft Visual Basic voor toepassingen venster.

2. Klikken Invoegen > Moduleen plak de volgende code in het Module Venster.

VBA-code: zoek een celcombinatie die gelijk is aan een bepaalde som:

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. Sla vervolgens dit codevenster op en sluit het, ga terug naar het werkblad en voer deze formule in = getcombination (A2: A9, C2) in een lege cel en druk op Enter key, krijgt u het volgende resultaat dat de combinatienummers weergeeft die gelijk zijn aan een bepaalde som, zie screenshot:

  • Opmerkingen:
  • In de bovenstaande formule, A2: A9 is het nummerbereik, en C2 bevat de doelwaarde waaraan u gelijk wilt zijn.
  • Als meer dan één combinatie van waarden een som heeft die gelijk is aan de specifieke waarde, wordt slechts één combinatie vermeld.

Vind alle combinaties die gelijk zijn aan een bepaalde som met een geweldige functie

Misschien zijn alle bovenstaande methoden enigszins moeilijk voor u, hier zal ik een krachtig hulpmiddel introduceren, Kutools for Excel, Met Verzin een nummer functie, kunt u snel alle combinaties krijgen die gelijk zijn aan een bepaalde som.

Tips:Om dit toe te passen Verzin een nummer -functie, ten eerste moet u de Kutools for Excelen pas de functie vervolgens snel en gemakkelijk toe.

Na het installeren van Kutools for Excel, doe dit als volgt:

1. Klikken Kutools > Content > Verzin een nummer, zie screenshot:

2. Vervolgens in de Verzin een nummer dialoogvenster, klik dan om de nummerlijst die u wilt gebruiken te selecteren uit de Databronen voer vervolgens het totale aantal in het Som textbox, zie screenshot:

3. En klik dan OK knop, verschijnt er een promptvenster om u eraan te herinneren een cel te selecteren om het resultaat te vinden, zie screenshot:

4. Klik vervolgens op OK, en nu zijn alle combinaties die gelijk zijn aan dat gegeven nummer weergegeven zoals onderstaand screenshot getoond:

Klik om Kutools voor Excel en nu gratis uit te proberen!


Demo: zoek een celcombinatie die gelijk is aan een bepaalde som in Excel


De beste tools voor kantoorproductiviteit

Kutools voor Excel lost de meeste van uw problemen op en verhoogt uw productiviteit met 80%

  • visfuik: Snel invoegen complexe formules, grafieken en alles wat je eerder hebt gebruikt; Versleutel cellen met wachtwoord; Maak een mailinglijst en stuur e-mails ...
  • Super Formula-balk (bewerk eenvoudig meerdere regels tekst en formule); Lay-out lezen (gemakkelijk grote aantallen cellen lezen en bewerken); Plakken in gefilterd bereik...
  • Voeg cellen / rijen / kolommen samen zonder gegevens te verliezen; Gespleten cellen inhoud; Combineer dubbele rijen / kolommen... Voorkom dubbele cellen; Vergelijk Ranges...
  • Selecteer Dupliceren of Uniek Rijen; Selecteer lege rijen (alle cellen zijn leeg); Super zoeken en fuzzy zoeken in veel werkboeken; Willekeurige selectie ...
  • Exacte kopie Meerdere cellen zonder de formuleverwijzing te wijzigen; Maak automatisch verwijzingen naar meerdere bladen; Plaats kogels, Selectievakjes en meer ...
  • Extraheer tekst, Tekst toevoegen, Verwijderen op positie, Ruimte verwijderen; Paging-subtotalen maken en afdrukken; Converteren tussen celinhoud en opmerkingen...
  • Super filter (bewaar en pas filterschema's toe op andere bladen); Geavanceerd sorteren per maand / week / dag, frequentie en meer; Speciaal filter door vet, cursief ...
  • Combineer werkmappen en werkbladen; Tabellen samenvoegen op basis van sleutelkolommen; Gegevens splitsen in meerdere bladen; Batch Converteer xls, xlsx en PDF...
  • Meer dan 300 krachtige functies. Ondersteunt Office / Excel 2007-2019 en 365. Ondersteunt alle talen. Eenvoudig te implementeren in uw onderneming of organisatie. Gratis proefperiode van 30 dagen met volledige functies. 60 dagen geld-terug-garantie.
kte tabblad 201905

Office-tabblad Brengt een interface met tabbladen naar Office en maakt uw werk veel gemakkelijker

  • Schakel bewerken en lezen met tabbladen in Word, Excel, PowerPoint in, Publisher, Access, Visio en Project.
  • Open en maak meerdere documenten in nieuwe tabbladen van hetzelfde venster in plaats van in nieuwe vensters.
  • Verhoogt uw productiviteit met 50% en vermindert elke dag honderden muisklikken voor u!
officetab onderkant
Say something here...
symbols left.
You are guest
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.
    harry · 3 months ago
    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 


  • To post as a guest, your comment is unpublished.
    Headache · 4 months ago
    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?
  • To post as a guest, your comment is unpublished.
    MRT · 1 years ago
    kutools works only integer value. Not support double. Like (395,52) ! Best solution is excel solver extention.
  • To post as a guest, your comment is unpublished.
    dora · 1 years ago
    is there a way to find combination for a target average instead of sum ?
  • To post as a guest, your comment is unpublished.
    Jeremy · 2 years ago
    How come i can not use the Make up a Number in Kutools for numbers with decimals?
    • To post as a guest, your comment is unpublished.
      skyyang · 2 years ago
      Hello, Jeremy,
      So far, this Make up a number feature can not support the decimals, but, you can apply it with a workaround.
      First, you can enlarge all the decimal numbers as whole numbers, such as multiply 100 to all the decimal numbers, and then apply this Make up a number feature, after getting the result, you should divide 100 to these numbers for returning them back to decimal numbers.
      Please try it, hope it can help you!
  • To post as a guest, your comment is unpublished.
    Feroz · 2 years ago
    How do you do for the list of numbers like 480
  • To post as a guest, your comment is unpublished.
    Guilherme Dorn · 2 years ago
    Thank you very much! Resolved my problem correctly.
  • To post as a guest, your comment is unpublished.
    Miss Jones · 2 years 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 · 2 years 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.
    dietz · 3 years 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.
    • To post as a guest, your comment is unpublished.
      L · 2 years ago
      did you ever get an answer or did you find a way to do this?
      • To post as a guest, your comment is unpublished.
        skyyang · 2 years ago
        Hello,
        If there are both positive and negative numbers in the column, I recommend you apply the Kutools for Excel's Make up a number feature, it can solve your problem quickly and easily.

        You can download Kutools for Excel and free trial 60 days. Please try!
  • To post as a guest, your comment is unpublished.
    Fattir · 3 years ago
    Hello,
    Thanks this is very good,
    How can find the most approximate combinations if there is no exact value.
    Many thanks
  • To post as a guest, your comment is unpublished.
    Fattir · 3 years ago
    Hello,
    Many thanks for information;
    How can find the most approximate combinations if there is no exact value.
    Many thanks,
  • To post as a guest, your comment is unpublished.
    Igor Wilk · 3 years ago
    Would somebody know how to adjust the VBA Getcombination function so that no repetition should be allowed?

    For example, for numbers 1,2,3,4,5,13 if 14 is to be achieved than 1,13 is a solution, and not 14 of 1.
    • To post as a guest, your comment is unpublished.
      Ram · 3 years ago
      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 & "1 of " & xCell & " "
      xSum = xSum - xCell
      End If
      Next
      GetCombination = xStr
      End Function
      • To post as a guest, your comment is unpublished.
        Shashanth · 2 years ago
        Hi Ram, this works fine but doesnot give the actual sum.
        EX: if i have 23,34,25,28,10,17&12 and i have a sum of 80(which is the sum of 23,28,17&12), I need a vba code which can find this combination (sum of 23,28,17&12) Can you please help me with this ?
      • To post as a guest, your comment is unpublished.
        ddddddd7 · 3 years ago
        hi it is giving me ambigious name error for the vba code
        any help cause i know nothing in VBA
  • To post as a guest, your comment is unpublished.
    alex · 3 years ago
    does anyone know if this works on google sheets
    • To post as a guest, your comment is unpublished.
      heee · 10 months ago
      Yes there is an extension similar to excel's solver called "solver"
  • To post as a guest, your comment is unpublished.
    epp · 3 years ago
    Hi,

    My drouble with this formula is that it gives me one value for enough times to get the target value..
    In the list of different values there are some values which are equal to each other.

    E.g. I have 0,16 for 3 times(the first values in the list) and the formula gives me the answer that my target value is 593 of 0,16.

    Why does it not combine different values to get my target value? It only chooses one value and gives how many times it is to be the target value.

    Any help or idea?


    Thanks!
  • To post as a guest, your comment is unpublished.
    Dana · 3 years ago
    I am trying to determine the best blend of product and am unsure if this is the best way to do it. At most I use three products in a blend with 5 specifications each. All of the specifications are linear and can be averaged when blended. One blend is usually 45,000lbs and each batch is 30,000lbs. Most of the time our blends are 15k+30k but I would like to be able to calculate for the unusual blends using the increments all the way down to 2000lbs.
  • To post as a guest, your comment is unpublished.
    Lorena · 3 years ago
    The macro didn't work if there are more than one solution.
    Also, I didn't work if I find "0"
    • To post as a guest, your comment is unpublished.
      skyyang · 3 years ago
      Hello,Lorena,
      Before applying the above VBA code, you must sort the number list in descending order first.
      Second, the code is not work correctly to get the total number 0.
      Hope it can help you, thank you!
  • To post as a guest, your comment is unpublished.
    laura · 3 years ago
    Could you upload the excel?
  • To post as a guest, your comment is unpublished.
    Ruchir · 3 years ago
    Brilliant!!!
  • To post as a guest, your comment is unpublished.
    LL · 3 years ago
    I was able to get the example with Range1 to work with my range in 12 rows, but when I changed the range to 42 rows it did not work. I even restarted the entire process with the 42 row version and that didn't work either. Any ideas?
  • To post as a guest, your comment is unpublished.
    WL · 4 years ago
    HI, I downloaded Kutools but cannot get it to find all the combos less than a specified total.
  • To post as a guest, your comment is unpublished.
    Dori · 4 years ago
    Hi. The formula version didn't work for me either. It feels like it is missing a step. I do not see where the number specified in cell C2 comes into the formula.

    Thanks
    • To post as a guest, your comment is unpublished.
      skyyang · 4 years ago
      Hello, Dori,


      There is no formula in C2, it is just the specific value that you want numbers added up to.
  • To post as a guest, your comment is unpublished.
    tarra · 4 years ago
    how if i need more than one combination? thank you
  • To post as a guest, your comment is unpublished.
    DJ · 4 years ago
    I'm at best a advanced beginner at Excel. I tried everything and it didn't work. What could I be doing wrong?
  • To post as a guest, your comment is unpublished.
    Alan · 4 years ago
    Awesome. Couldn't get the large formula to work but the solver add-in worked perfectly. Saved me so much work.
  • To post as a guest, your comment is unpublished.
    Rick · 4 years ago
    Is there a way to expand the range as Thom says, to say up to 50 numbers, but to also only total six of the numbers out of the range that sum to the specified total? Currently it will provide all combinations that total to the specified total.

    thanks
  • To post as a guest, your comment is unpublished.
    nitin · 4 years ago
    Superb Man!!! Superb Man!!!
  • To post as a guest, your comment is unpublished.
    Thom · 4 years ago
    Is there a way to expand the range so that it includes more than 8 numbers? Also, I'm not sure how this function is working: "=ROW(INDIRECT("1:"&2^ROWS(Range1)))". If I try to expand "Range1" beyond 15 rows, I get an #Ref error. It works great with just the 8 numbers, but what if you wanted to include, say, 50 numbers or even 100.
    • To post as a guest, your comment is unpublished.
      nitin · 1 years ago
      Same issue