Note: The other languages of the website are Google-translated. Back to English
English English

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 Nouveautés 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:

4Cliquez sur OK revenir à la Gestionnaire de noms boîte de dialogue, puis continuez à cliquer Nouveautés 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 B2:

=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 des tours 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 Données > 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 Ajouter 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:

6Cliquez sur 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.

2Cliquez sur 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:

1Cliquez sur Kutools > Contenu > 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éeplus
  • 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 gammesplus
  • 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 cellulesplus
  • 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 PDFplus
  • 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
Commentaires (46)
Pas encore de notes. Soyez le premier à évaluer!
Ce commentaire a été minimisé par le modérateur sur le site
Existe-t-il un moyen d'élargir la gamme afin qu'elle comprenne plus de 8 numéros ? De plus, je ne sais pas comment cette fonction fonctionne : "=ROW(INDIRECT("1:"&2^ROWS(Range1)))". Si j'essaie d'étendre "Range1" au-delà de 15 lignes, j'obtiens une erreur #Ref. Cela fonctionne très bien avec seulement les 8 numéros, mais que se passe-t-il si vous vouliez inclure, disons, 50 numéros ou même 100.
Ce commentaire a été minimisé par le modérateur sur le site
Même problème
Ce commentaire a été minimisé par le modérateur sur le site
Superbe Homme !!! Superbe Homme !!!
Ce commentaire a été minimisé par le modérateur sur le site
Existe-t-il un moyen d'élargir la plage comme le dit Thom, pour dire jusqu'à 50 nombres, mais aussi pour ne totaliser que six des nombres hors de la plage qui totalisent le total spécifié? Actuellement, il fournira toutes les combinaisons qui totalisent le total spécifié. Merci
Ce commentaire a été minimisé par le modérateur sur le site
Génial. Impossible de faire fonctionner la grande formule, mais le complément du solveur a parfaitement fonctionné. M'a sauvé tellement de travail.
Ce commentaire a été minimisé par le modérateur sur le site
mais ça prend trop de temps
Ce commentaire a été minimisé par le modérateur sur le site
Je suis au mieux un débutant avancé à Excel. J'ai tout essayé et ça n'a pas marché. Qu'est-ce que je peux faire de mal ?
Ce commentaire a été minimisé par le modérateur sur le site
comment si j'ai besoin de plus d'une combinaison? Merci
Ce commentaire a été minimisé par le modérateur sur le site
Salut. La version formule n'a pas fonctionné pour moi non plus. On a l'impression qu'il manque une étape. Je ne vois pas où le nombre spécifié dans la cellule C2 entre dans la formule.

Merci
Ce commentaire a été minimisé par le modérateur sur le site
Bonjour Dori,


Il n'y a pas de formule dans C2, c'est juste la valeur spécifique à laquelle vous voulez additionner les nombres.
Ce commentaire a été minimisé par le modérateur sur le site
HI, j'ai téléchargé Kutools mais je n'arrive pas à trouver tous les combos inférieurs à un total spécifié.
Ce commentaire a été minimisé par le modérateur sur le site
J'ai pu faire en sorte que l'exemple avec Range1 fonctionne avec ma plage en 12 lignes, mais lorsque j'ai changé la plage en 42 lignes, cela n'a pas fonctionné. J'ai même redémarré tout le processus avec la version 42 lignes et cela n'a pas fonctionné non plus. Des idées?
Ce commentaire a été minimisé par le modérateur sur le site
Brillant!!!
Ce commentaire a été minimisé par le modérateur sur le site
Pourriez-vous télécharger l'excel?
Ce commentaire a été minimisé par le modérateur sur le site
La macro ne fonctionnait pas s'il y avait plus d'une solution.
Aussi, je n'ai pas travaillé si je trouve "0"
Ce commentaire a été minimisé par le modérateur sur le site
Bonjour Loréna,
Avant d'appliquer le code VBA ci-dessus, vous devez d'abord trier la liste des numéros par ordre décroissant.
Deuxièmement, le code ne fonctionne pas correctement pour obtenir le nombre total 0.
J'espère que cela peut vous aider, merci!
Ce commentaire a été minimisé par le modérateur sur le site
J'essaie de déterminer le meilleur mélange de produit et je ne sais pas si c'est la meilleure façon de le faire. Au maximum, j'utilise trois produits dans un mélange avec 5 spécifications chacun. Toutes les spécifications sont linéaires et peuvent être moyennées lorsqu'elles sont mélangées. Un mélange pèse généralement 45,000 30,000 lb et chaque lot pèse 15 30 lb. La plupart du temps, nos mélanges sont de 2000 XNUMX + XNUMX XNUMX, mais j'aimerais pouvoir calculer les mélanges inhabituels en utilisant les incréments jusqu'à XNUMX XNUMX livres.
Ce commentaire a été minimisé par le modérateur sur le site
Hi,

Mon double avec cette formule est qu'elle me donne une valeur suffisamment de fois pour obtenir la valeur cible.
Dans la liste des différentes valeurs, certaines valeurs sont égales les unes aux autres.

Par exemple, j'ai 0,16 pour 3 fois (les premières valeurs de la liste) et la formule me donne la réponse que ma valeur cible est 593 sur 0,16.

Pourquoi ne combine-t-il pas différentes valeurs pour obtenir ma valeur cible ? Il ne choisit qu'une seule valeur et indique combien de fois elle doit être la valeur cible.

Une aide ou une idée?


Merci !
Ce commentaire a été minimisé par le modérateur sur le site
est-ce que quelqu'un sait si cela fonctionne sur les feuilles de google
Ce commentaire a été minimisé par le modérateur sur le site
Oui, il existe une extension similaire au solveur d'Excel appelée "solver"
Ce commentaire a été minimisé par le modérateur sur le site
Quelqu'un saurait-il comment ajuster la fonction VBA Getcombination afin qu'aucune répétition ne soit autorisée?

Par exemple, pour les nombres 1,2,3,4,5,13 si 14 doit être atteint alors 1,13 est une solution, et non 14 sur 1.
Ce commentaire a été minimisé par le modérateur sur le site
Fonction GetCombination(CoinsRange As Range, SumCellId As Double) As String
'mise à jour par Extendoffice 20160506
Dim xStr As String
Dim xSum As Double
Dim xCell As Range
xSum = SumCellId
Pour chaque xCell dans CoinsRange
Sinon (xSum / xCell < 1) Alors
xStr = xStr & "1 de " & xCell & " "
xSomme = xSomme - xCellule
Si fin
Suivant
GetCombination = xStr
Fonction de fin
Ce commentaire a été minimisé par le modérateur sur le site
salut ça me donne une erreur de nom ambigüe pour le code vba
toute aide car je ne connais rien en VBA
Ce commentaire a été minimisé par le modérateur sur le site
Salut Ram, cela fonctionne bien mais ne donne pas la somme réelle.
EX : si j'ai 23,34,25,28,10,17&12 et que j'ai une somme de 80 (qui est la somme de 23,28,17&12), j'ai besoin d'un code vba qui peut trouver cette combinaison (somme de 23,28,17 ,12&XNUMX) Pouvez-vous m'aider s'il vous plaît ?
Ce commentaire a été minimisé par le modérateur sur le site
Bonjour,
Merci beaucoup pour l'information;
Comment trouver les combinaisons les plus approximatives s'il n'y a pas de valeur exacte.
Merci beaucoup,
Ce commentaire a été minimisé par le modérateur sur le site
Bonjour,
Merci c'est très bien,
Comment trouver les combinaisons les plus approximatives s'il n'y a pas de valeur exacte.
Merci beaucoup
Ce commentaire a été minimisé par le modérateur sur le site
Le complément du solveur ne fonctionnera-t-il pas s'il y a des nombres négatifs dans la liste ou si la valeur de number est 0 ? J'essaie de trouver une somme de nombres dans une liste qui équivaut à zéro, certains nombres étant négatifs et positifs, mais le solveur ne fonctionne pas. J'ai changé quelques chiffres sur ma liste pour tester pour m'assurer que j'ai suivi les étapes correctement et que cela a fonctionné pour le test. Veuillez indiquer s'il existe un moyen de résoudre avec des nombres négatifs et positifs pour trouver une valeur 0.
L     a
Ce commentaire a été minimisé par le modérateur sur le site
avez-vous déjà obtenu une réponse ou avez-vous trouvé un moyen de le faire?
Ce commentaire a été minimisé par le modérateur sur le site
Bonjour,
S'il y a à la fois des nombres positifs et négatifs dans la colonne, je vous recommande d'appliquer la fonctionnalité Kutools for Excel's Make up a number, cela peut résoudre votre problème rapidement et facilement.

Vous pouvez télécharger Kutools pour Excel et un essai gratuit de 60 jours. S'il vous plaît essayez!
Ce commentaire a été minimisé par le modérateur sur le site
J'ai 1162 cellules pour trouver le numéro x. Excel me dit qu'il y a trop de cellules variables. Très petit ensemble de données ! Aucune suggestion? Merci!
Il n'y a pas encore de commentaires postés ici
TÉLÉCHARGER PLUS
Laisser vos commentaires
Publier en tant qu'invité
×
Évaluez cet article:
0   Personnages
Emplacements suggérés