Tip: andere talen zijn Google-Vertaald. Je kunt het English versie van deze link.
Log in
x
or
x
x
Registreren
x

or

Hoe vind je alle combinaties die gelijk zijn aan een gegeven bedrag in Excel?

Ik heb bijvoorbeeld de volgende lijst met getallen en nu wil ik weten welke combinatie van getallen in de lijst tot 480 is samengevat, in het volgende screenshot is te zien dat er vijf groepen mogelijke combinaties zijn die gelijk optellen naar 480, zoals 300 + 60 + 120, 300 + 60 + 40 + 80, etc. In dit artikel zal ik het hebben over enkele methoden om te vinden welke cellen een Excel samenvatten tot een specifieke waarde.


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 te vermelden. Klik om Kutools voor Excel te downloaden!

Kutools for Excel: met meer dan 300 handige Excel-add-ins, gratis om zonder beperking in 60-dagen te proberen. Download en gratis proef nu!


Zoek cellencombinatie die gelijk is aan een gegeven som met formules

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

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

2. Nadat u een bereiknaam voor de nummerlijst hebt gedefinieerd, moet u twee verdere bereiknamen maken in de Name Manager vak, klik alstublieft Formules > Name Manager, in de Name Manager dialoogvenster, klik New knop, zie screenshots:

3. In de popped out Nieuwe naam dialoogvenster, voer een naam in List1 in de Naam veld en typ deze formule = ROW (INDIRECT ( "1:" & RIJEN (Range1))) (Range1 is de bereiknaam die u in step1 hebt gemaakt) in de Verwijst naar veld, zie screenshot:

4. Klikken OK terugkeren naar de Name Manager dialoogvenster en klik vervolgens verder New om een ​​andere bereiknaam te creëren, in de Nieuwe naam dialoogvenster, voer een naam in List2 in de Naam veld en typ deze formule = ROW (INDIRECT ( "1:" & 2 ^ RIJEN (Range1))) (Range1 is de bereiknaam die u in step1 hebt gemaakt) in de Verwijst naar veld, zie screenshot:

5. Nadat u de bereiknamen hebt gemaakt, past u 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 dan de vulgreep omlaag naar cel B8, het laatste nummer van de lijst, en u kunt de getallen zien waarvan het totale aantal 480 is gemarkeerd als X in kolom B, zie screenshot:

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

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

Als u verward bent met de bovenstaande methode, bevat Excel een Oplosser invoegtoepassing functie, kunt u met behulp van deze invoegtoepassing ook de nummers identificeren die in totaal gelijk zijn aan een bepaalde waarde.

1. Eerst moet je dit activeren Solver add-in, Ga aub naar filet > opties, in de Excel-opties dialoogvenster, klik Add-Ins Klik in het linkerdeelvenster op 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 dialoog, check Oplosser invoegtoepassing optie en klik op OK om deze invoegtoepassing met succes te installeren.

3. Na het activeren van de Oplosser-invoegtoepassing, moet u deze formule invoeren in de cel B9: = SOMPRODUCT (B2: B9, A2: A9)(B2: B9 is een lege kolomcellen naast uw nummerlijst, en A2: A9 is de nummerlijst die u gebruikt. ) en druk op invoeren sleutel, zie screenshot:

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

(1.) Klik op om de cel te selecteren B10 waar je formule in komt van de Stel 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 veranderlijke cellen te veranderen sectie, klik alstublieft om het celbereik te selecteren B2: B9 waar markeert u uw overeenkomstige nummers.

5. En klik vervolgens op Toevoegen knop om naar de Constraint toevoegen dialoogvenster, klik om het celbereik te selecteren B2: B9En Select bak uit de drop-down lijst, zie screenshot:

6. Klikken OK om terug te gaan naar Oplosserparameter dialoogvenster en klik vervolgens op Oplossen knop, enkele minuten later, a Oplossen van resultaten dialoogvenster wordt uitgeklapt en u kunt de combinatie van cellen zien die gelijk is aan een gegeven som 480 zijn gemarkeerd als 1. In de Oplossen van resultaten dialoogvenster, selecteer alstublieft Keep Solver Solution optie en klik op OK om het dialoogvenster te sluiten. Zie screenshot:

Notes: Met deze methode kunt u ook slechts één combinatiecellen krijgen als er meer dan één combinatie van waarden een som heeft die gelijk is aan de specifieke waarde.


Zoek een cellencombinatie die gelijk is aan een gegeven 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 dan met de volgende stappen:

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

2. Klikken bijvoegsel > moduleen plak de volgende code in de module Venster.

VBA-code: zoek cellencombinatie die gelijk is aan een gegeven 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 het codevenster op en sluit het en ga terug naar het werkblad en voer deze formule in = Getcombination (A2: A9, C2) in een lege cel en druk op invoeren sleutel, krijgt u het volgende resultaat dat de combinatienummers weergeeft die gelijk zijn aan een gegeven som, zie screenshot:

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

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

Misschien zijn alle bovenstaande methoden enigszins moeilijk voor je, hier zal ik een krachtig hulpmiddel introduceren, Kutools for Excel, Met Verzin een nummer 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, zou u het moeten downloaden Kutools for Excelen pas de functie snel en eenvoudig toe.

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

1. Klikken Kutools > Content > Verzin een nummerzie screenshot:

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

3. En klik vervolgens op OK knop verschijnt een promptvenster om u eraan te herinneren een cel te selecteren om het resultaat te zoeken, zie screenshot:

4. Dan klikken OKen nu zijn alle combinaties die gelijk zijn aan dat gegeven nummer weergegeven zoals onderstaand screenshot getoond:

Klik om Kutools voor Excel te downloaden en nu gratis uitproberen!


Demo: zoek cellencombinatie die gelijk is aan een gegeven som in Excel


Kutools voor Excel - De beste Office-productiviteitstool Verhoog uw productiviteit met 80%

  • visfuik: Snel invoegen complexe formules, grafieken en alles wat je eerder hebt gebruikt; Coderen van cellen met wachtwoord; Maak een mailinglijst en stuur e-mails ...
  • Super Formula Bar (bewerk eenvoudig meerdere regels tekst en formule); Lay-out lezen (gemakkelijk grote aantallen cellen lezen en bewerken); Plakken op gefilterd bereik...
  • Cellen / rijen / kolommen samenvoegen zonder gegevens te verliezen; Inhoud gesplitste cellen; Combineer dubbele rijen / kolommen... voorkomen dubbele cellen; Ranges vergelijken...
  • Selecteer Dupliceren of Uniek rijen; Selecteer Lege rijen (alle cellen zijn leeg); Super Find en Fuzzy Find in veel werkboeken; Willekeurig selecteren ...
  • Exacte kopie Meerdere cellen zonder formule-referentie te wijzigen; Automatisch referenties maken naar meerdere vellen; Voeg kogels toe, Selectievakjes en meer ...
  • extract Text, Tekst toevoegen, verwijderen op positie, Verwijder de spatie; Subtotalen voor paging maken en afdrukken; Converteren tussen cellen Inhoud en opmerkingen...
  • Super filter (bewaar en pas filterschema's toe op andere bladen); Geavanceerde sortering 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. Volledige functionaliteit 60-daagse gratis proefversie.
kte-tab 201905

Tabblad Office Brengt interface met tabbladen naar Office en maakt uw werk veel eenvoudiger

  • Bewerken en lezen met tabbladen inschakelen in Word, Excel, PowerPoint, 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 verlaagt dagelijks honderden muisklikken voor u!
Officetab onderaan
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.
    Jeremy · 28 days 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 · 24 days 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 · 3 months ago
    How do you do for the list of numbers like 480
  • To post as a guest, your comment is unpublished.
    Guilherme Dorn · 5 months ago
    Thank you very much! Resolved my problem correctly.
  • To post as a guest, your comment is unpublished.
    Miss Jones · 6 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 · 7 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 · 10 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.
    • To post as a guest, your comment is unpublished.
      L · 21 days 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 · 20 days 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 · 1 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 · 1 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 · 1 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 · 1 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 · 7 months 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 · 11 months 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 · 1 years ago
    does anyone know if this works on google sheets
  • To post as a guest, your comment is unpublished.
    epp · 1 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 · 1 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 · 1 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 · 1 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 · 1 years ago
    Could you upload the excel?
  • To post as a guest, your comment is unpublished.
    Ruchir · 1 years ago
    Brilliant!!!
  • To post as a guest, your comment is unpublished.
    LL · 1 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 · 2 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 · 2 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 · 2 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 · 2 years ago
    how if i need more than one combination? thank you
  • To post as a guest, your comment is unpublished.
    DJ · 2 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 · 2 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 · 2 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 · 2 years ago
    Superb Man!!! Superb Man!!!
  • To post as a guest, your comment is unpublished.
    Thom · 2 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.