Comment trouver toutes les combinaisons dont la somme correspond exactement à une valeur cible dans Excel ?

Trouver toutes les combinaisons possibles de nombres dans une liste dont la somme correspond exactement à une valeur cible représente un défi courant pour de nombreux utilisateurs d’Excel, qu’il s’agisse de gestion budgétaire, de planification ou d’analyse de données.

Dans cet exemple, nous disposons d’une liste de nombres, et l’objectif est d’identifier les combinaisons dont la somme est égale à 480. La capture d’écran révèle cinq groupes de combinaisons répondant à ce critère, notamment 300 + 120 + 60 ou encore 250 + 120 + 60 + 50. Cet article présente différentes méthodes efficaces pour déterminer précisément quelles combinaisons de nombres d’une liste atteignent une somme cible dans Excel.

Trouver une combinaison de nombres égale à une somme donnée à l’aide de la fonction Solveur

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

• À l’aide d’une fonction personnalisée
• Grâce à une fonctionnalité intelligente – Kutools pour Excel

Obtenir toutes les combinaisons de nombres dont la somme se situe dans une plage à l’aide d’un code VBA

Trouver une combinaison de cellules égale à une somme donnée à l’aide de la fonction Solveur

Explorer Excel pour trouver des combinaisons de cellules dont la somme correspond à un nombre précis peut sembler intimidant, mais le complément Solveur simplifie grandement cette tâche. Nous vous guidons pas à pas pour configurer Solveur et identifier facilement la bonne combinaison de cellules, transformant ainsi une opération apparemment complexe en une démarche simple et réalisable.

Étape 1 : Activer le complément Solveur

1. Accédez à Fichier > Options. Dans la boîte de dialogue Options Excel, cliquez sur Compléments dans le volet gauche, puis sur le bouton Atteindre. Voir la capture d’écran :
   
2. La boîte de dialogue Compléments s’affiche alors. Cochez l’option Complément Solveur, puis cliquez sur OK pour installer ce complément avec succès.
   

Étape 2 : Saisir la formule

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

=SUMPRODUCT(B2:B10,A2:A10)

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

1. Cliquez sur Données > Solveur pour ouvrir la boîte de dialogue Paramètres du Solveur. Dans cette boîte de dialogue, procédez comme suit :
   • (1.) Cliquez sur le bouton pour sélectionner la cellule B11où se trouve votre formule dans la section Définir l’objectif;
   • (2.) Ensuite, dans la section À, sélectionnez Valeur, puis saisissez votre valeur cible 480selon vos besoins ;
   • (3.) Sous la section En modifiant les cellules variables, cliquez sur le bouton pour sélectionner la plage de cellules B2:B10 qui contiendra vos nombres correspondants.
   • (4.) Ensuite, cliquez sur le bouton Ajouter.
   • 
2. La boîte de dialogue Ajouter une contrainte s’affiche. Cliquez sur le bouton pour sélectionner la plage de cellules B2:B10, puis choisissez bin dans la liste déroulante. Enfin, cliquez sur le bouton OK. Voir la capture d’écran :
   
3. Dans la boîte de dialogue Paramètres du Solveur, cliquez sur le bouton Résoudre. Quelques instants plus tard, la boîte de dialogue Résultats du Solveur s’ouvre, et vous pouvez constater que les combinaisons de cellules dont la somme est égale à 480 sont marquées par un « 1 » dans la colonne B. Dans la boîte de dialogue Résultats du Solveur, sélectionnez l’option Conserver la solution du Solveur, puis cliquez sur OK pour fermer la boîte de dialogue. Voir la capture d’écran :
   

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

Explorer les fonctionnalités avancées d’Excel vous permet de trouver facilement toutes les combinaisons de nombres dont la somme correspond exactement à une valeur cible — et c’est plus simple qu’il n’y paraît ! Cette section présente deux méthodes efficaces pour identifier toutes ces combinaisons.

Obtenir toutes les combinaisons de nombres égales à une somme donnée à l’aide d’une fonction personnalisée

Pour découvrir toutes les combinaisons possibles de nombres issues d’un ensemble donné dont la somme atteint une valeur cible, la fonction personnalisée présentée ci-dessous constitue un outil efficace.

Étape 1 : Ouvrir l’éditeur de modules VBA et copier le code

1. Maintenez enfoncées les touches ALT + F11 dans Excel pour ouvrir la fenêtre Microsoft Visual Basic pour Applications.
2. Cliquez sur Insertion>Module, puis collez le code suivant dans la fenêtre du module.
   Code VBA : Obtenir toutes les combinaisons de nombres dont la somme est égale à une valeur 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 : Saisir la formule personnalisée pour obtenir le résultat

Après avoir collé le code, fermez la fenêtre du code pour revenir à votre feuille de calcul. Saisissez la formule suivante dans une cellule vide afin d’afficher les résultats, puis appuyez sur la touche Entrée pour générer toutes les combinaisons. Consultez la capture d’écran :

=MakeupANumber(A2:A10,B2)

Astuce: Si vous souhaitez afficher 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 grâce à une fonctionnalité puissante

Compte tenu des limites de la fonction mentionnée précédemment, nous recommandons une solution rapide et complète : la fonctionnalité **Arrondir les nombres** de Kutools pour Excel, compatible avec toutes les versions d’Excel. Cette alternative gère efficacement les nombres positifs, les décimales et les nombres négatifs, vous permettant d’obtenir en un clin d’œil toutes les combinaisons dont la somme correspond exactement à la valeur souhaitée.

1. Cliquez sur Kutools > Contenu > Arrondir les nombres. Voir la capture d’écran :
   
2. Dans la boîte de dialogue Arrondir les nombres, cliquez sur le bouton pour sélectionner la liste de nombres à utiliser depuis la Plage source, puis saisissez la somme cible dans la zone de texte Somme. Enfin, cliquez sur le bouton OK. Voir la capture d’écran :
   
3. Une boîte de dialogue s’affiche alors pour vous demander de sélectionner une cellule où afficher les résultats. Cliquez ensuite sur OK. Voir la capture d’écran :
   
4. À présent, toutes les combinaisons dont la somme est égale à la valeur indiquée s’affichent comme illustré ci-dessous :
   

Obtenir toutes les combinaisons de nombres dont la somme se situe dans une plage à l’aide d’un code VBA

Il vous arrive parfois de devoir identifier toutes les combinaisons possibles de nombres dont la somme totale se situe dans une plage précise — par exemple, tous les groupements de nombres dont le total est compris entre 470 et 480.

Découvrir toutes les combinaisons possibles de nombres dont la somme se situe dans une plage définie est un défi à la fois fascinant et extrêmement utile dans Excel. Cette section vous présente un code VBA conçu pour résoudre cette tâche avec efficacité.

Étape 1 : Ouvrir l’éditeur de modules VBA et copier le code

1. Maintenez enfoncées les touches ALT + F11 dans Excel pour ouvrir la fenêtre Microsoft Visual Basic pour Applications.
2. Cliquez sur Insertion>Module, puis collez le code suivant dans la fenêtre du module.
   Code VBA : Obtenir toutes les combinaisons de nombres dont la somme se situe dans 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 s’affiche, sélectionnez la plage de nombres à utiliser, puis cliquez sur OK. Voir la capture d’écran :
   
2. Dans la deuxième boîte de dialogue, sélectionnez ou saisissez la borne inférieure, puis cliquez sur OK. Voir la capture d’écran :
   
3. Dans la troisième boîte de dialogue, sélectionnez ou saisissez la borne 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 — c’est-à-dire l’emplacement où les résultats commenceront à s’afficher — puis cliquez sur OK. Voir la capture d’écran :
   

Résultat

Désormais, chaque combinaison correspondante apparaîtra dans des lignes consécutives de la feuille de calcul, à partir de la cellule de sortie que vous avez sélectionnée.

Excel vous propose plusieurs méthodes pour identifier des groupes de nombres dont la somme atteint un total précis. Chaque approche fonctionne différemment, vous permettant de choisir celle qui correspond le mieux à votre niveau de maîtrise d’Excel et aux exigences de votre projet. Si vous souhaitez découvrir davantage d’astuces et de conseils Excel,notre site web propose des milliers de tutoriels. Merci de votre lecture, et nous espérons continuer à vous offrir des informations utiles à l’avenir !

Articles associés :