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

or

Comment trouver toutes les combinaisons qui égalent une somme donnée dans Excel?

Par exemple, j'ai la liste de nombres suivante, et maintenant, je veux savoir quelle combinaison de nombres dans la liste totalise jusqu'à 480, dans la capture d'écran suivante, vous pouvez voir qu'il y a cinq groupes de combinaisons possibles qui s'additionnent égaux à 480, comme 300 + 60 + 120, 300 + 60 + 40 + 80, etc. Cet article, je vais parler de certaines méthodes pour trouver quelles cellules se résument à une valeur spécifique dans Excel.


Trouver une combinaison de cellules qui égale une somme donnée avec des formules

Tout d'abord, vous devez créer des noms de plage, puis appliquer une formule matricielle pour trouver les cellules qui correspondent à la valeur cible, procédez comme suit étape par étape:

1. Sélectionnez la liste de numéros et définissez cette liste un nom de plage-- Gamme1 into the Zone Nomet appuyez sur Entrer touche pour terminer le nom de plage défini, voir capture d'écran:

2. Après avoir défini un nom de plage pour la liste de numéros, vous devez créer deux autres noms de plage dans le Gestionnaire de noms boîte, veuillez cliquer Formules > Gestionnaire de noms, Dans le Gestionnaire de noms boîte de dialogue, cliquez sur Neuf bouton, voir les captures d'écran:

3. Dans le sauté Nouveau nom boîte de dialogue, entrez un nom List1 into the Nom champ et saisissez cette formule = ROW (INDIRECT ("1:" & ROWS (Range1))) (Gamme1 est le nom de plage que vous avez créé à l'étape 1) dans le Se réfère à champ, voir capture d'écran:

4. Cliquez OK revenir à la Gestionnaire de noms boîte de dialogue, puis continuez à cliquer Neuf pour créer un autre nom de plage, dans le Nouveau nom boîte de dialogue, entrez un nom List2 into the Nom champ et saisissez cette formule = ROW (INDIRECT ("1:" & 2 ^ ROWS (Range1))) (Gamme1 est le nom de plage que vous avez créé à l'étape 1) dans le Se réfère à champ, voir capture d'écran:

5. Après avoir créé les noms de plage, veuillez appliquer la formule de tableau suivante dans la cellule 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","")et appuyez sur Maj + Ctrl + Entrée clés ensemble, puis faites glisser la poignée de remplissage vers la cellule B8, le dernier numéro de la liste, et vous pouvez voir les nombres dont le montant total est de 480 sont marqués comme X dans la colonne B, voir capture d'écran:

  • Notes:
  • Dans la formule longue ci-dessus: List1, List2 et Gamme1 sont les noms de plages que vous avez créés lors des étapes précédentes, C2 est la valeur spécifique à laquelle vous voulez ajouter les nombres.
  • Si plus d'une combinaison de valeurs a une somme égale à la valeur spécifique, une seule combinaison est répertoriée.

Trouvez et répertoriez toutes les combinaisons qui égalent une somme donnée rapidement et facilement dans Excel

Kutools pour Excel's Créer un numéro L'utilitaire peut vous aider à trouver et lister toutes les combinaisons et combinaisons spécifiques qui égalent un nombre donné de somme rapidement et facilement. Cliquez pour télécharger Kutools for Excel!

Kutools pour Excel: avec plus de 300 compléments Excel pratiques, essai gratuit sans limitation dans 30 jours. Téléchargez et essayez gratuitement maintenant!


Trouver une combinaison de cellules égale à une somme donnée avec le complément Solver

Si vous êtes confondu avec la méthode ci-dessus, Excel contient un Complément Solver fonctionnalité, en utilisant ce complément, vous pouvez également identifier les nombres dont le montant total est égal à une valeur donnée.

1. Tout d'abord, vous devez l'activer Solver add-in, s'il vous plaît allez à Déposez le > Options :, Dans le Options Excel boîte de dialogue, cliquez sur Add-Ins dans le volet gauche, puis cliquez sur Complément Solver du Compléments d'application inactifs section, voir capture d'écran:

2. Puis clique Go bouton pour entrer dans le Add-Ins boîte de dialogue, vérifier Complément Solver option, et cliquez OK pour installer correctement ce complément.

3. Après avoir activé le complément Solver, vous devez entrer cette formule dans la cellule B9: = SOMMEPROD (B2: B9, A2: A9), (B2: B9 est une colonne vide à côté de votre liste de numéros, et A2: A9 est la liste de numéros que vous utilisez. ) et appuyez sur Entrer clé, voir capture d'écran:

4. Puis clique Data > Solver pour aller à la Paramètre du solveur boîte de dialogue, dans la boîte de dialogue, veuillez effectuer les opérations suivantes:

(1.) Cliquez sur  bouton pour sélectionner la cellule B10 d'où vient votre formule Fixer un objectif section;

(2.) Puis dans le À section, sélectionnez Valeur deet entrez votre valeur cible 480 Comme vous le souhaitez;

(3.) Sous le En changeant les cellules variables section, veuillez cliquer bouton pour sélectionner la plage de cellules B2: B9 où marquera vos numéros correspondants.

5. Et puis cliquez Add bouton pour aller à la Ajouter une contrainte boîte de dialogue, cliquez sur bouton pour sélectionner la plage de cellules B2: B9Et sélectionnez coffre dans la liste déroulante, voir capture d'écran:

6. Cliquez OK revenir le Paramètre du solveur boîte de dialogue, puis cliquez sur Résoudre bouton, quelques minutes plus tard, un Résultats du solveur La boîte de dialogue apparaît et vous pouvez voir la combinaison de cellules qui égalent une somme donnée 480 sont marquées comme 1. Dans le Résultats du solveur boîte de dialogue, veuillez sélectionner Gardez la solution du solveur option, et cliquez OK pour quitter la boîte de dialogue. Voir la capture d'écran:

Notes: Cette méthode ne peut également obtenir qu'une seule combinaison de cellules s'il y a plus d'une combinaison de valeurs a une somme égale à la valeur spécifique.


Rechercher une combinaison de cellules égale à une somme donnée avec la fonction définie par l'utilisateur

Les deux premières méthodes sont toutes complexes pour la plupart de nos utilisateurs d'Excel, ici, je peux créer un code VBA pour résoudre ce travail rapidement et facilement.

Pour obtenir le résultat correct, vous devez d'abord trier la liste de numéros par ordre décroissant. Et puis faites avec les étapes suivantes:

1. Maintenez le ALT + F11 clés pour ouvrir le Microsoft Visual Basic pour applications fenêtre.

2. Cliquez insérer > Moduleet collez le code suivant dans le Module Fenêtre.

Code VBA: recherchez une combinaison de cellules égale à une somme donnée:

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. Ensuite, enregistrez et fermez cette fenêtre de code, puis revenez à la feuille de calcul et entrez cette formule = getcombination (A2: A9, C2) dans une cellule vide et appuyez sur Entrer clé, vous obtiendrez le résultat suivant qui affiche les nombres de combinaison qui correspondent à une somme donnée, voir capture d'écran:

  • Notes:
  • Dans la formule ci-dessus, A2: A9 est la plage de nombres, et C2 contient la valeur cible que vous souhaitez égaler.
  • Si plus d'une combinaison de valeurs a une somme égale à la valeur spécifique, une seule combinaison est répertoriée.

Trouvez toutes les combinaisons qui égalent une somme donnée avec une fonctionnalité étonnante

Peut-être que toutes les méthodes ci-dessus sont quelque peu difficiles pour vous, ici, je vais vous présenter un outil puissant, Kutools pour Excel, Avec son Créer un numéro fonctionnalité, vous pouvez obtenir rapidement toutes les combinaisons égales à une somme donnée.

Conseils:Appliquer cette Créer un numéro fonctionnalité, tout d'abord, vous devez télécharger le Kutools pour Excel, puis appliquez la fonction rapidement et facilement.

Après l'installation de Kutools pour Excel, veuillez faire comme ceci:

1. Cliquez Kutools > du Paquet > Créer un numéro, voir capture d'écran:

2. Ensuite, dans le Composez un numéro boîte de dialogue, veuillez cliquer pour sélectionner la liste de numéros que vous souhaitez utiliser dans le La source de données, puis entrez le nombre total dans le Somme zone de texte, voir capture d'écran:

3. Et puis, cliquez OK bouton, une boîte de dialogue apparaîtra pour vous rappeler de sélectionner une cellule pour localiser le résultat, voir capture d'écran:

4. Cliquez ensuite sur OK, et maintenant, toutes les combinaisons égales à ce nombre donné ont été affichées comme ci-dessous la capture d'écran:

Cliquez pour télécharger Kutools pour Excel et un essai gratuit maintenant!


Démo: trouver une combinaison de cellules égale à une somme donnée dans Excel


Les meilleurs outils de productivité de bureau

Kutools for Excel résout la plupart de vos problèmes et augmente votre productivité de 80%

  • Réutilisation: Insérer rapidement formules complexes, graphiques et tout ce que vous avez utilisé auparavant; Crypter les cellules avec mot de passe; Créer une liste de diffusion et envoyer des e-mails ...
  • Barre Super Formula (modifiez facilement plusieurs lignes de texte et de formule); Disposition de lecture (lire et modifier facilement un grand nombre de cellules); Coller dans la plage filtrée...
  • Fusionner les cellules / lignes / colonnes sans perdre de données; Contenu des cellules divisées; Combiner des lignes / colonnes en double... Empêcher les cellules en double; Comparer les gammes...
  • Sélectionnez Dupliquer ou Unique Lignes; Sélectionnez les lignes vides (toutes les cellules sont vides); Super Find et Fuzzy Find dans de nombreux classeurs; Sélection aléatoire ...
  • Copie exacte Plusieurs cellules sans changer la référence de formule; Créer automatiquement des références à plusieurs feuilles; Insérer des puces, Cases à cocher et plus encore ...
  • Extrait du texte, Ajouter du texte, Supprimer par position, Supprimer l'espace; Créer et imprimer des sous-totaux de pagination; Conversion entre le contenu et les commentaires des cellules...
  • Super filtre (enregistrer et appliquer des schémas de filtrage à d'autres feuilles); Tri avancé par mois / semaine / jour, fréquence et plus; Filtre spécial par gras, italique ...
  • Combiner des classeurs et des feuilles de travail; Fusionner les tableaux en fonction des colonnes clés; Diviser les données en plusieurs feuilles; Conversion par lots xls, xlsx et PDF...
  • Plus de 300 fonctionnalités puissantes. Prend en charge Office / Excel 2007-2019 et 365. Prend en charge toutes les langues. Déploiement facile dans votre entreprise ou organisation. Essai gratuit de 30 jours. Garantie de remboursement de 60 jours.
onglet kte 201905

Office Tab apporte une interface à onglets à Office et simplifie considérablement votre travail

  • Activer l'édition et la lecture par onglets dans Word, Excel, PowerPoint, Publisher, Access, Visio et Project.
  • Ouvrez et créez plusieurs documents dans de nouveaux onglets de la même fenêtre, plutôt que dans de nouvelles fenêtres.
  • Augmente votre productivité de 50% et réduit des centaines de clics de souris chaque jour!
bas de cabine
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 · 4 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 · 6 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 · 11 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 · 4 years ago
    Brilliant!!!
  • To post as a guest, your comment is unpublished.
    LL · 4 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 · 5 years ago
    Superb Man!!! Superb Man!!!
  • To post as a guest, your comment is unpublished.
    Thom · 5 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