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.

doc trouver combinaison égale somme 1

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

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

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


Lister toutes les combinaisons possibles à partir de plusieurs colonnes:

Kutools for Excel's Lister toutes les combinaisons L'utilitaire peut vous aider à générer / répertorier rapidement toutes les combinaisons possibles en fonction de plusieurs listes de valeurs.

doc trouver combinaison égale somme 2

Kutools for Excel: avec plus que 200 compléments Excel pratiques, libre d'essayer sans limitation dans les jours 60. 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 trouver les cellules dont la somme correspond à la valeur cible, procédez comme suit étape par étape:

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:

doc trouver combinaison égale somme 2

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 capture d'écran:

doc trouver combinaison égale somme 3

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:

doc trouver combinaison égale somme 4

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:

doc trouver combinaison égale somme 5

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:

doc trouver combinaison égale somme 6

À noter :

1. Dans la longue formule 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.

2. 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:

doc trouver combinaison égale somme 7

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.

doc trouver combinaison égale somme 8

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

doc trouver combinaison égale somme 9

4. Puis clique Données > 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 doc 1 bouton pour sélectionner la cellule B9 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 doc 1 bouton pour sélectionner la plage de cellules B1: B8 où marquera vos numéros correspondants.

doc trouver combinaison égale somme 10

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

doc trouver combinaison égale somme 11

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:

doc trouver combinaison égale somme 12

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


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


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 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. Ensuite, enregistrez et fermez cette fenêtre de code, puis revenez à la feuille de calcul et entrez cette formule = getcombination (A1: A8, 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:

doc trouver combinaison égale somme 13

À noter :

1. Dans la formule ci-dessus, A1: A8 est la plage de numéros, et C2 contient la valeur cible que vous voulez égaler.

2. Si plus d'une combinaison de valeurs a une somme égale à la valeur spécifique, une seule combinaison est répertoriée.



Outils de productivité recommandés

Office Tab

étoile d&#39;or1 Apportez des onglets pratiques à Excel et à d'autres logiciels Office, tout comme Chrome, Firefox et Internet Explorer.

Kutools for Excel

étoile d&#39;or1 Incroyable! Augmentez votre productivité dans les minutes 5. Ne nécessite pas de compétences particulières, économisez deux heures par jour!

étoile d&#39;or1 300 Nouvelles fonctionnalités pour Excel, rendent Excel facile et puissant:

  • Fusionner des cellules / lignes / colonnes sans perdre de données.
  • Combiner et consolider plusieurs feuilles et classeurs.
  • Comparez les gammes, copiez plusieurs gammes, convertissez le texte en date, l'unité et la conversion de devise.
  • Compter par couleurs, sous-totaux de recherche, tri avancé et super filtre,
  • Plus Sélectionner / Insérer / Supprimer / Texte / Format / Lier / Commenter / Classeurs / Feuilles de calcul Outils ...

Capture d&#39;écran de Kutools pour Excel

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.
    Guilherme Dorn · 13 days ago
    Thank you very much! Resolved my problem correctly.
  • To post as a guest, your comment is unpublished.
    Miss Jones · 1 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 · 2 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 · 5 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.
    Fattir · 9 months ago
    Hello,
    Thanks this is very good,
    How can find the most approximate combinations if there is no exact value.
    Many thanks