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
• Trouver une combinaison de cellules égale à une somme donnée avec le complément Solver
• Rechercher une combinaison de cellules égale à une somme donnée avec la fonction définie par l'utilisateur
• Trouvez toutes les combinaisons qui égalent une somme donnée avec une fonctionnalité étonnante

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:

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:

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:

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.

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.

Lire la suite... Téléchargement Gratuit... Achat... 

---

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!

Lire la suite... Téléchargement Gratuit... Achat...