Comment trouver toutes les combinaisons qui sont égales à une somme donnée dans Excel ?

Découvrir toutes les combinaisons possibles de nombres dans une liste qui s'additionnent pour atteindre une somme spécifique est un défi que de nombreux utilisateurs d'Excel peuvent rencontrer, que ce soit pour des raisons 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 diverses 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 Solveur

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

• En utilisant une fonction définie par l'utilisateur
• Avec une fonctionnalité intelligente – Kutools pour Excel

Obtenir toutes les combinaisons de nombres ayant une somme dans une plage avec le code VBA

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

Plonger dans Excel pour trouver des combinaisons de cellules qui s'additionnent pour obtenir un nombre spécifique peut sembler intimidant, mais le complément Solveur rend cela facile. Nous vous guiderons à travers les étapes simples pour configurer le Solveur et trouver la bonne combinaison de cellules, transformant ainsi une tâche complexe en quelque chose de simple et réalisable.

Étape 1 : Activer le complément Solveur

1. Veuillez aller dans Fichier > Options, dans la boîte de dialogue Options Excel , cliquez sur Compléments dans le panneau de gauche, puis cliquez sur le bouton Aller . Voir la capture d'écran :
   
2. Ensuite, la fenêtre Compléments apparaît, cochez l'option Complément Solveur et cliquez sur OK pour installer ce complément avec succès.
   

Étape 2 : Entrer la formule

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

=SUMPRODUCT(B2:B10,A2:A10)

Étape 3 : Configurer et exécuter le Solveur pour obtenir le résultat

1. Cliquez sur Données > Solveur pour accéder à la boîte de dialogue Paramètres du Solveur . Dans la boîte de dialogue, veuillez effectuer les opérations suivantes :
   • (1.) Cliquez sur le bouton pour sélectionner la cellule B11 où se trouve votre formule dans la section Définir l'objectif ;
   • (2.) Ensuite, dans la section À, sélectionnez Valeur de, et entrez votre valeur cible 480 selon vos besoins ;
   • (3.) Sous la section En modifiant les cellules variables veuillez cliquer sur le bouton pour sélectionner la plage de cellules B2:B10 qui marquera vos nombres correspondants.
   • (4.) Ensuite, cliquez sur le bouton Ajouter.
   • 
2. Ensuite, une boîte de dialogue Ajouter une contrainte s'affiche, cliquez sur le bouton pour sélectionner la plage de cellules B2:B10, et sélectionnez bin depuis la liste déroulante. Enfin, cliquez sur OK . Voir la capture d'écran :
   
3. Dans la boîte de dialogue Paramètres du Solveur cliquez sur le bouton Résoudre quelques minutes plus tard, une boîte de dialogue Résultats du Solveur s'affiche, et vous pouvez voir la combinaison de cellules dont la somme est égale à 480 marquée par 1 dans la colonne B. Dans la boîte de dialogue Résultats du Solveur veuillez sélectionner Conserver la solution du Solveur et cliquez sur OK pour quitter la boîte de dialogue. Voir la capture d'écran :
   

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

Explorer les capacités avancées d'Excel vous permet de trouver chaque combinaison de nombres qui correspond à 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.

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

Pour découvrir chaque combinaison possible de nombres à partir d'un ensemble spécifique qui atteint collectivement une valeur donnée, la fonction personnalisée décrite ci-dessous sert d'outil efficace.

Étape 1 : Ouvrir l'éditeur de module VBA et copier le code

1. Maintenez les touches ALT + F11 dans Excel, et cela ouvre la fenêtre Microsoft Visual Basic for Applications.
2. Cliquez sur Insérer > Module, et collez le code suivant dans la fenêtre Module.
   Code VBA : Obtenir 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 : Entrer 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 la touche Entrée pour obtenir toutes les combinaisons. Voir la capture d'écran :

=MakeupANumber(A2:A10,B2)

Astuce: Si vous souhaitez lister les résultats des combinaisons verticalement dans une colonne, veuillez appliquer la formule suivante :

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

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

Compte tenu des limitations de la fonction susmentionnée, nous recommandons une solution rapide et complète : la fonction Arrondir les nombres de Kutools pour Excel, qui est compatible avec toutes les versions d'Excel. Cette alternative peut gérer efficacement les nombres positifs, les décimales et les nombres négatifs. Avec cette fonctionnalité, vous pouvez rapidement obtenir toutes les combinaisons égales à une somme donnée.

1. Cliquez sur Kutools > Texte > Arrondir un Nombre, voir la capture d'écran :
   
2. Ensuite, dans la boîte de dialogue Arrondir un nombre veuillez cliquer sur le bouton pour sélectionner la liste de nombres que vous souhaitez utiliser depuis la Plage source, puis entrez le nombre total dans la zone de texte Somme Enfin, cliquez sur OK . Voir la capture d'écran :
   
3. Ensuite, une boîte de dialogue contextuelle apparaîtra pour vous rappeler de sélectionner une cellule où placer le résultat, puis cliquez sur OK, voir la capture d'écran :
   
4. Et maintenant, toutes les combinaisons égales au nombre donné sont affichées comme le montre la capture d'écran ci-dessous :
   

Obtenir toutes les combinaisons de nombres ayant une somme 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 s'additionnent pour donner une somme dans une plage spécifique. Par exemple, vous pourriez chercher à trouver tous les groupements possibles de nombres où 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 introduira un code VBA pour résoudre cette tâche.

Étape 1 : Ouvrir l'éditeur de module VBA et copier le code

1. Maintenez les touches ALT + F11 dans Excel, et cela ouvre la fenêtre Microsoft Visual Basic for Applications.
2. Cliquez sur Insérer > Module, et collez le code suivant dans la fenêtre Module.
   Code VBA : Obtenir 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 la touche 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 boîte de dialogue, sélectionnez ou tapez le nombre limite basse, et cliquez sur OK. Voir la capture d'écran :
   
3. Dans la troisième boîte de dialogue, sélectionnez ou tapez le nombre limite haute, et cliquez sur OK. Voir la capture d'écran :
   
4. Dans la dernière boîte de dialogue, sélectionnez une cellule de sortie, qui sera là où les résultats commenceront à être affichés. Puis cliquez sur OK. Voir la capture d'écran :
   

Résultat

Maintenant, chaque combinaison qualifiante sera listé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 offre plusieurs façons de trouver des groupes de nombres qui s'additionnent pour atteindre un total spécifique, chaque méthode fonctionne différemment, donc vous pouvez en choisir une en fonction de votre familiarité avec Excel et de vos besoins pour votre projet. Si vous êtes intéressé par l'exploration de plus d'astuces Excel, notre site web propose des milliers de tutoriels. Merci d'avoir lu, et nous espérons vous fournir plus d'informations utiles à l'avenir !

Articles connexes :