Découvrir toutes les combinaisons possibles de nombres dans une liste qui totalisent une somme spécifique est un défi que de nombreux utilisateurs d'Excel peuvent rencontrer, que ce soit à des fins de budgétisation, de planification ou d'analyse de données.

Dans cet exemple, nous avons une liste de nombres et l'objectif est d'identifier quelles combinaisons de cette liste totalisent 480. La capture d'écran fournie montre qu'il existe cinq groupes possibles de combinaisons qui atteignent cette somme, y compris des combinaisons comme 300+120. +60, 250+120+60+50, entre autres. Dans cet article, nous explorerons différentes méthodes pour identifier les combinaisons spécifiques de nombres dans une liste qui totalisent une valeur désignée dans Excel.

Trouver une combinaison de nombres égale à une somme donnée avec la fonction Solver

Obtenez toutes les combinaisons de nombres égales à une somme donnée

• En utilisant la fonction définie par l'utilisateur
• Par une fonctionnalité intelligente – Kutools for Excel

Obtenez toutes les combinaisons de nombres dont la somme est comprise dans une plage avec le code VBA

Trouver une combinaison de cellules égale à une somme donnée avec la fonction Solver

Plonger dans Excel pour trouver des combinaisons de cellules qui totalisent un nombre spécifique peut sembler intimidant, mais le complément Solver facilite la tâche. Nous vous guiderons à travers les étapes simples pour configurer Solver et trouver la bonne combinaison de cellules, rendant ainsi ce qui semblait être une tâche complexe simple et réalisable.

Étape 1 : Activer le complément Solver

1. S'il vous plaît aller à Déposez votre dernière attestation > Options, Dans le Options Excel boîte de dialogue, cliquez sur Add-Ins dans le volet de gauche, cliquez ensuite sur Go bouton. Voir la capture d'écran:
   
2. Puis le Add-Ins apparaît, vérifiez la Complément Solver option, et cliquez OK pour installer correctement ce complément.
   

Étape 2: entrez la formule

Après avoir activé le complément Solver, vous devez saisir cette formule dans la cellule B11 :

=SUMPRODUCT(B2:B10,A2:A10)

Étape 3 : Configurez et exécutez Solver pour obtenir le résultat

1. Cliquez Sauvegarde de > 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 B11 où se trouve votre formule depuis le 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: B10 où marquera vos numéros correspondants.
   • (4.) Ensuite, cliquez sur Ajouter .
   • 
2. Puis, un Ajouter une contrainte La boîte de dialogue s'affiche, cliquez sur bouton pour sélectionner la plage de cellules B2: B10, et sélectionnez coffre dans la liste déroulante. Enfin, cliquez OK bouton. Voir la capture d'écran:
   
3. Dans le Paramètre du solveur dialogue, cliquez sur Nous résolvons 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 équivaut à une somme donnée 480 est marquée comme 1 dans la colonne B. Dans la 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:
   

Obtenez toutes les combinaisons de nombres égales à une somme donnée

L'exploration des fonctionnalités plus approfondies d'Excel vous permet de trouver chaque combinaison de nombres correspondant à une somme spécifique, et c'est plus facile que vous ne le pensez. Cette section vous montrera deux méthodes pour trouver toutes les combinaisons de nombres égales à une somme donnée.

Obtenez toutes les combinaisons de nombres égales à une somme donnée avec la fonction définie par l'utilisateur

Pour découvrir toutes les combinaisons possibles de nombres d'un ensemble spécifique qui atteignent collectivement une valeur donnée, la fonction personnalisée décrite ci-dessous constitue un outil efficace.

Étape 1 : Ouvrez l'éditeur de module VBA et copiez le code

1. Maintenez la touche ALT + F11 clés dans Excel, et il ouvre le Microsoft Visual Basic pour applications fenêtre.
2. Cliquez insérer > Moduleet collez le code suivant dans la fenêtre Module.
   Code VBA : Obtenez toutes les combinaisons de nombres égales à une somme donnée

   Public Function MakeupANumber(xNumbers As Range, xCount As Long)
   'updateby Extendoffice
       Dim arrNumbers() As Long
       Dim arrRes() As String
       Dim ArrTemp() As Long
       Dim xIndex As Long
       Dim rg As Range
   
       MakeupANumber = ""
       
       If xNumbers.CountLarge = 0 Then Exit Function
       ReDim arrNumbers(xNumbers.CountLarge - 1)
       
       xIndex = 0
       For Each rg In xNumbers
           If IsNumeric(rg.Value) Then
               arrNumbers(xIndex) = CLng(rg.Value)
               xIndex = xIndex + 1
           End If
       Next rg
       If xIndex = 0 Then Exit Function
       
       ReDim Preserve arrNumbers(0 To xIndex - 1)
       ReDim arrRes(0)
       
       Call Combinations(arrNumbers, xCount, ArrTemp(), arrRes())
       ReDim Preserve arrRes(0 To UBound(arrRes) - 1)
       MakeupANumber = arrRes
   End Function
   
   Private Sub Combinations(Numbers() As Long, Count As Long, ArrTemp() As Long, ByRef arrRes() As String)
   
       Dim currentSum As Long, i As Long, j As Long, k As Long, num As Long, indRes As Long
       Dim remainingNumbers() As Long, newCombination() As Long
       
       currentSum = 0
       If (Not Not ArrTemp) <> 0 Then
           For i = LBound(ArrTemp) To UBound(ArrTemp)
               currentSum = currentSum + ArrTemp(i)
           Next i
       End If
    
       If currentSum = Count Then
           indRes = UBound(arrRes)
           ReDim Preserve arrRes(0 To indRes + 1)
           
           arrRes(indRes) = ArrTemp(0)
           For i = LBound(ArrTemp) + 1 To UBound(ArrTemp)
               arrRes(indRes) = arrRes(indRes) & "," & ArrTemp(i)
           Next i
       End If
       
       If currentSum > Count Then Exit Sub
       If (Not Not Numbers) = 0 Then Exit Sub
       
       For i = 0 To UBound(Numbers)
           Erase remainingNumbers()
           num = Numbers(i)
           For j = i + 1 To UBound(Numbers)
               If (Not Not remainingNumbers) <> 0 Then
                   ReDim Preserve remainingNumbers(0 To UBound(remainingNumbers) + 1)
               Else
                   ReDim Preserve remainingNumbers(0 To 0)
               End If
               remainingNumbers(UBound(remainingNumbers)) = Numbers(j)
               
           Next j
           Erase newCombination()
   
           If (Not Not ArrTemp) <> 0 Then
               For k = 0 To UBound(ArrTemp)
                   If (Not Not newCombination) <> 0 Then
                       ReDim Preserve newCombination(0 To UBound(newCombination) + 1)
                   Else
                       ReDim Preserve newCombination(0 To 0)
                   End If
                   newCombination(UBound(newCombination)) = ArrTemp(k)
   
               Next k
           End If
           
           If (Not Not newCombination) <> 0 Then
               ReDim Preserve newCombination(0 To UBound(newCombination) + 1)
           Else
               ReDim Preserve newCombination(0 To 0)
           End If
           
           newCombination(UBound(newCombination)) = num
   
           Combinations remainingNumbers, Count, newCombination, arrRes
       Next i
   
   End Sub
   

Étape 2 : Saisissez la formule personnalisée pour obtenir le résultat

Après avoir collé le code, fermez la fenêtre de code pour revenir à la feuille de calcul. Entrez la formule suivante dans une cellule vide pour afficher le résultat, puis appuyez sur Entrer clé pour obtenir toutes les combinaisons. Voir capture d'écran :

=MakeupANumber(A2:A10,B2)

Conseil : Si vous souhaitez lister les résultats de la combinaison verticalement dans une colonne, veuillez appliquer la formule suivante :

=TRANSPOSE(MakeupANumber(A2:A10,B2))

Obtenez toutes les combinaisons de nombres égales à une somme donnée avec une fonctionnalité puissante

Compte tenu des limites de la fonction susmentionnée, nous recommandons une solution rapide et complète : la fonctionnalité Make up a Number de Kutools for Excel, qui est compatible avec n'importe quelle version d'Excel. Cette alternative peut gérer efficacement les nombres positifs, les nombres décimaux et les nombres négatifs. Avec cette fonctionnalité, vous pouvez rapidement obtenir toutes les combinaisons égales à une somme donnée.

1. Cliquez 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. Enfin, cliquez OK bouton, voir capture d'écran:
   
3. Et puis, une boîte de dialogue apparaîtra pour vous rappeler de sélectionner une cellule pour localiser le résultat, puis cliquez sur OK, voir capture d'écran:
   
4. Et maintenant, toutes les combinaisons égales à ce nombre donné ont été affichées comme ci-dessous :
   

Obtenez toutes les combinaisons de nombres dont la somme est comprise dans une plage avec le code VBA

Parfois, vous pourriez vous retrouver dans une situation où vous devez identifier toutes les combinaisons possibles de nombres qui totalisent collectivement une somme dans une plage spécifique. Par exemple, vous cherchez peut-être à trouver tous les regroupements possibles de nombres dont le total se situe entre 470 et 480.

Découvrir toutes les combinaisons possibles de nombres qui totalisent une valeur dans une plage spécifique représente un défi fascinant et très pratique dans Excel. Cette section présentera un code VBA pour résoudre cette tâche.

Étape 1 : Ouvrez l'éditeur de module VBA et copiez le code

1. Maintenez la touche ALT + F11 clés dans Excel, et il ouvre le Microsoft Visual Basic pour applications fenêtre.
2. Cliquez insérer > Moduleet collez le code suivant dans la fenêtre Module.
   Code VBA : obtenez toutes les combinaisons de nombres qui totalisent une plage spécifique

   Sub Getall_combinations()
   'Updateby Extendoffice
       Dim xNumbers As Variant
       Dim Output As Collection
       Dim rngSelection As Range
       Dim OutputCell As Range
       Dim LowLimit As Long, HiLimit As Long
       Dim i As Long, j As Long
       Dim TotalCombinations As Long
       Dim CombTotal As Double
       Set Output = New Collection
       On Error Resume Next
       Set rngSelection = Application.InputBox("Select the range of numbers:", "Kutools for Excel", Type:=8)
       If rngSelection Is Nothing Then
           MsgBox "No range selected. Exiting macro.", vbInformation, "Kutools for Excel"
           Exit Sub
       End If
       On Error GoTo 0
       xNumbers = rngSelection.Value
       LowLimit = Application.InputBox("Select or enter the low limit number:", "Kutools for Excel", Type:=1)
       HiLimit = Application.InputBox("Select or enter the high limit number:", "Kutools for Excel", Type:=1)
       On Error Resume Next
       Set OutputCell = Application.InputBox("Select the first cell for output:", "Kutools for Excel", Type:=8)
       If OutputCell Is Nothing Then
           MsgBox "No output cell selected. Exiting macro.", vbInformation, "Kutools for Excel"
           Exit Sub
       End If
       On Error GoTo 0
       TotalCombinations = 2 ^ (UBound(xNumbers, 1) * UBound(xNumbers, 2))
       For i = 1 To TotalCombinations - 1
           Dim tempArr() As Double
           ReDim tempArr(1 To UBound(xNumbers, 1) * UBound(xNumbers, 2))
           CombTotal = 0
           Dim k As Long: k = 0
           
           For j = 1 To UBound(xNumbers, 1)
               If i And (2 ^ (j - 1)) Then
                   k = k + 1
                   tempArr(k) = xNumbers(j, 1)
                   CombTotal = CombTotal + xNumbers(j, 1)
               End If
           Next j
           If CombTotal >= LowLimit And CombTotal <= HiLimit Then
               ReDim Preserve tempArr(1 To k)
               Output.Add tempArr
           End If
       Next i
       Dim rowOffset As Long
       rowOffset = 0
       Dim item As Variant
       For Each item In Output
           For j = 1 To UBound(item)
               OutputCell.Offset(rowOffset, j - 1).Value = item(j)
           Next j
           rowOffset = rowOffset + 1
       Next item
   End Sub
   
   
   

Étape 2 : Exécuter le code

1. Après avoir collé le code, appuyez sur F5 pour exécuter ce code, dans la première boîte de dialogue qui apparaît, sélectionnez la plage de nombres que vous souhaitez utiliser et cliquez sur OK. Voir la capture d'écran:
   
2. Dans la deuxième zone d'invite, sélectionnez ou saisissez le numéro de limite basse, puis cliquez sur OK. Voir la capture d'écran:
   
3. Dans la troisième zone d'invite, sélectionnez ou saisissez le nombre de limite supérieure, puis cliquez sur OK. Voir la capture d'écran:
   
4. Dans la dernière boîte de dialogue, sélectionnez une cellule de sortie, où les résultats commenceront à être générés. Puis clique OK. Voir la capture d'écran:
   

Résultat

Désormais, chaque combinaison qualifiante sera répertoriée dans des lignes consécutives dans la feuille de calcul, en commençant par la cellule de sortie que vous avez choisie.

Excel vous propose plusieurs façons de rechercher des groupes de nombres qui totalisent un certain total. Chaque méthode fonctionne différemment. Vous pouvez donc en choisir une en fonction de votre connaissance d'Excel et de ce dont vous avez besoin pour votre projet. Si vous souhaitez découvrir plus de trucs et astuces Excel, notre site Web propose des milliers de tutoriels. Merci d'avoir lu et nous sommes impatients de vous fournir des informations plus utiles à l'avenir !

Articles connexes: