Astuce: Les autres langues sont Google-Traduction. Vous pouvez visiter le English version de ce lien.
Se connecter
x
or
x
x
S'enregistrer
x

or

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

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


Trouvez et listez toutes les combinaisons qui correspondent à une somme donnée rapidement et facilement dans Excel

Kutools for Excel's Faire un nombre Cet utilitaire peut vous aider à trouver et lister rapidement et facilement toutes les combinaisons et combinaisons spécifiques égales à un nombre donné. Cliquez pour télécharger Kutools for Excel!

Kutools for Excel: avec plus de 300 handy Excel add-ins, gratuit à essayer sans limitation en jours 30. Téléchargez et essai gratuit maintenant!


Trouver une combinaison de cellules é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 rechercher les cellules dont la somme correspond à la valeur cible. Effectuez l'opération étape par étape suivante:

1. Sélectionnez la liste des numéros et définissez cette liste comme nom de plage ... Range1 dans le 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 des numéros, vous devez créer deux autres noms de plage dans la liste. Gestionnaire de noms boîte, s'il vous plaît cliquez Formules > Gestionnaire de noms, Dans le Gestionnaire de noms boîte de dialogue, cliquez sur Nouveau bouton, voir les captures d'écran:

3. Dans le sauté Nouveau nom dialogue, entrez un nom List1 dans le Nom champ, et tapez cette formule = ROW (INDIRECT ("1:" & ROWS (Range1))) (Range1 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 dialogue, puis continuez à cliquer Nouveau bouton pour créer un autre nom de plage, dans le Nouveau nom dialogue, entrez un nom List2 dans le Nom champ, et tapez cette formule = ROW (INDIRECT ("1:" & 2 ^ ROWS (Range1))) (Range1 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, appliquez la formule matricielle 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 jusqu'à la cellule B8, le dernier numéro de la liste, et vous pouvez voir les numéros dont le montant total est 480 sont marqués comme X dans la colonne B, voir capture d'écran:

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

Trouvez une combinaison de cellules égale à une somme donnée avec Solver Add-in

Si vous êtes confus avec la méthode ci-dessus, Excel contient un Solveur Add-in En utilisant ce complément, vous pouvez également identifier les nombres dont le montant total est égal à une valeur donnée.

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

2. Puis clique Go bouton pour entrer dans le Add-Ins dialogue, vérifier Solveur Add-in option, et cliquez OK pour installer ce complément avec succès.

3. Après avoir activé le complément Solver, vous devez entrer cette formule dans la cellule B9: = SUMPRODUCT (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 touche, voir capture d'écran:

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

(1.) Cliquez sur bouton pour sélectionner la cellule B10 où votre formule à partir de la Définir l'objectif section;

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

(3.) Sous la En changeant les cellules variables section, s'il vous plaît cliquez bouton pour sélectionner la plage de cellules B2: B9 où marquera vos numéros correspondants.

5. Et puis cliquez Ajoutez 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 la capture d'écran:

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

Note: Cette méthode est également uniquement possible d'obtenir une combinaison de cellules s'il existe plusieurs combinaisons de valeurs dont la somme est égale à la valeur spécifique.


Trouver 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 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 des numéros dans l'ordre décroissant. Et puis faites 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: Trouver 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 touche, vous obtiendrez le résultat suivant qui affiche les numéros de combinaison qui correspondent à une somme donnée, voir capture d'écran:

  • À noter :
  • Dans la formule ci-dessus, A2: A9 est la plage de numéros, et C2 contient la valeur cible que vous voulez é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 égales à une somme donnée avec une fonctionnalité étonnante

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

Conseils:Appliquer cette Faire un nombre Pour commencer, vous devez télécharger le Kutools for Excel, puis appliquez la fonctionnalité rapidement et facilement.

Après l'installation de Kutools for Excel, faites comme ceci:

1. Cliquez Kutools > Contenu > Faire un nombre, voir capture d'écran:

2. Ensuite, dans le Faire un numéro boîte de dialogue, s'il vous plaît cliquez bouton pour sélectionner la liste de numéros que vous voulez utiliser parmi les La source de données, puis entrez le nombre total dans le champ 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 la capture d'écran:

4. Puis clique OK, et maintenant, toutes les combinaisons égales à ce nombre donné ont été affichées comme suit:

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


Demo: recherche la combinaison de cellules qui équivaut à une somme donnée dans Excel


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 emails ...
  • Super Formula Bar (éditez facilement plusieurs lignes de texte et de formule); Disposition de lecture (facilement lire et éditer un grand nombre de cellules); Coller à la gamme filtrée...
  • Fusionner les cellules / rangées / colonnes sans perdre de données; Contenu des cellules divisées; Combiner les lignes / colonnes en double... Prévenir les cellules en double; Comparer les plages...
  • Sélectionnez Dupliquer ou Unique Des rangées; Sélectionnez les lignes vierges (toutes les cellules sont vides); Super Find et Fuzzy Find dans de nombreux cahiers d'exercices; Sélection aléatoire ...
  • Copie exacte Plusieurs cellules sans changer la référence de la formule; Créer automatiquement des références à plusieurs feuilles; Insérer des balles, Cases à cocher et plus ...
  • Extrait du texte, Ajouter du texte, Supprimer par position, Supprimer l'espace; Créer et imprimer des sous-totaux de pagination; Conversion entre contenu de cellules et commentaires...
  • 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 en gras, en italique ...
  • Combinaison de classeurs et de feuilles de calcul; Fusionner les tables en fonction des colonnes clés; Fractionner les données en plusieurs feuilles; Conversion par lots xls, xlsx et PDF...
  • Plus que de puissantes fonctionnalités 300. Prend en charge Office / Excel 2007-2019 et 365. Prend en charge toutes les langues. Déploiement facile dans votre entreprise ou organisation. Fonctionnalités complètes Essai gratuit du jour 30.
kte tab 201905

Office Tab apporte une interface à onglets à Office et simplifie grandement votre travail

  • Activer l'édition par onglets et la lecture 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 le nombre de clics de souris pour vous chaque jour!
fond officetab
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 · 2 months 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 months 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 · 5 months ago
    How do you do for the list of numbers like 480
  • To post as a guest, your comment is unpublished.
    Guilherme Dorn · 7 months ago
    Thank you very much! Resolved my problem correctly.
  • To post as a guest, your comment is unpublished.
    Miss Jones · 8 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 · 9 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 · 1 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 months 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 months 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 · 9 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 · 1 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 · 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 · 2 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 · 3 years ago
    Superb Man!!! Superb Man!!!
  • To post as a guest, your comment is unpublished.
    Thom · 3 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.