Trouvez toutes les combinaisons de nombres qui égalent une somme donnée dans Excel

3 façons de trouver toutes les combinaisons d'un ensemble de nombres qui résument à un total spécifique.

Trouver des combinaisons de valeurs qui s'ajoutent à une somme donnée est un problème courant dans l'analyse des données. Par exemple, vous voudrez peut-être connaître toutes les combinaisons possibles d'articles qui peuvent être achetés avec un budget donné, ou toutes les moyens possibles d'allouer des ressources pour répondre à certaines exigences. Dans cet article, nous explorerons comment utiliser Excel Solver et VBA pour accomplir la tâche. Nous discuterons également de certains pièges et limitations potentielles de chaque approche qui peut survenir lors de la gestion des grands ensembles de données ou des contraintes complexes.

• Trouvez une combinaison de nombres égaux à une somme donnée - Excel Solver
• Obtenez toutes les combinaisons qui résument à une fonction personnalisée totale spécifique
• Renvoie toutes les combinaisons qui s'ajoutent à une valeur donnée - macro

Trouvez une combinaison de nombres égaux à une somme donnée avec un solveur Excel

Malheureusement, aucune des fonctions Excel intégrées ne peut vous aider à identifier les nombres qui s'ajoutent à un total donné. Heureusement, Excel fournit un complément spécial pour résoudre des problèmes de programmation linéaire. Le complément Solver est inclus avec toutes les versions d'Excel mais n'est pas activé par défaut. Si vous n'êtes pas familier avec cet outil, voici un excellent article sur la façon d'ajouter et d'utiliser le solveur dans Excel.

Avec le complément de solveur activé dans votre Excel, procédez aux étapes suivantes:

1. Créez le modèle.

   Pour cela, entrez votre ensemble de numéros dans une colonne (A3: A12 dans notre cas) et ajoutez une colonne vierge à droite de vos nombres pour les résultats (B3: B12). Dans une cellule séparée (B13), entrez une formule de sumproduct similaire à ceci:

   =SUMPRODUCT(A3:A12, B3:B12)

   

2. Exécutez le complément Solver.

   Dans l'onglet Données , dans le groupe d'analyse , cliquez sur le bouton Solver .

   

3. Définissez le problème pour le solveur.

   Dans la boîte de dialogue des paramètres du solveur , configurez les cellules objectives et variables:

   • Dans la boîte d'objectif définie , entrez l'adresse de la cellule de formule (B13).
   • Dans la section TO , sélectionnez la valeur et tapez la valeur de somme souhaitée (50 dans cet exemple).
   • Dans la boîte de cellules variables en modifiant , sélectionnez la plage à remplir avec les résultats (B3: B12).

   

4. Ajoutez les contraintes.

   Pour spécifier les contraintes, c'est-à-dire les restrictions ou les conditions qui doivent être remplies, cliquez sur le bouton Ajouter . Dans la fenêtre de dialogue Ajouter une contrainte , sélectionnez la plage résultante (B3: B12) et choisissez Bin dans la liste déroulante. La contrainte sera automatiquement définie sur binaire . Une fois terminé, cliquez sur OK.

   

5. Résoudre le problème.

   Lorsque vous êtes ramené à la fenêtre de dialogue des paramètres du solveur , passez en revue vos paramètres et cliquez sur le bouton Résoudre .

   

   Quelques secondes (ou minutes) plus tard, la boîte de dialogue des résultats du solveur apparaîtra. En cas de succès, sélectionnez l'option Keep Solver Solution et cliquez sur OK pour quitter la boîte de dialogue.

   

En conséquence, vous aurez 1 inséré à côté des nombres qui s'ajoutent à la somme spécifiée. Pas une solution conviviale, mais c'est le meilleur que l'excel hors de la boîte puisse faire.

À des fins de visualisation, j'ai mis en évidence les cellules qui donnent la somme souhaitée en vert clair:

Limitation : Excel Solver peut trouver, au plus, une seule combinaison de nombres qui équivaut à une somme spécifique.

Trouvez toutes les combinaisons égales par une somme donnée avec une fonction personnalisée

Pour obtenir toutes les combinaisons possibles à partir d'un ensemble donné de nombres qui s'ajoutent à une certaine valeur, vous pouvez utiliser la fonction personnalisée ci-dessous. Si vous êtes nouveau dans UDFS, vous trouverez beaucoup d'informations utiles dans ce guide: comment créer des fonctions définies par l'utilisateur personnalisé dans Excel.

Fonction personnalisée pour trouver toutes les combinaisons égales par une somme donnée

Option Explicit Public Fonction Findsumcombinations (RNGnumbers As Range, ltargetsum As Long ) DIM ARNUMBERS () Aussi long , partie () Aussi longs arres dim () que String Dim Indi As Long Dim Cellcurr comme gamme Redim Arres.Count - If Rngnaumbers.Count> 1 puis Redimcurr (Rnnumbers.Count - 1) arnumbers (indi) = clng (celcurr.value) indi = indi 1 Next Cellcurr Call Sumuprecursivecombinations (arnumbers, ltargetsum , partie ( ) , arres () ) end si redim conserve arre Long , partie () Aussi long , byRef arres () as String ) dim S Aush, i Aussi long , J comme long , num comme long , indres aussi long bail restant () aussi long , partrec () aussi long s = sumarray (partie) si s = cible alors indes = ubound ( arre) redim préserver les arres ( 0 à indu 1) arre ( indres) = Arraytostring Pas pas des nombres) 0 alors pour i = 0 à ubound (nombres) effacer restant () num = nombres ( i) pour j = i 1 à ubound (nombres) addToArray restant, nombres (j) Next j erase partrec ( ) copyArray partrec Arraytostring (x () aussi long ) que la chaîne dim N Aussi long , résultat comme le résultat de la chaîne = x (n) pour n = lbound (x) 1 à ubound (x ) result = result & "," & x ( n ) Next n arraytostring = résultat final function private function sumarray ( x ( ) aussi long ) aussi long (x) sumarray = sumarray x (n) suivant n fin si fin la fonction private sub- addtoArray (arr () Aussi long , x aussi long ) if ( pas arr ) 0 alors redim conserver arr ( 0 to ubound ( arr) 1) else redim conserve arr ( 0 à 0) end si arr ( ubound ( arr )) = x fin sub privé sous - copire ( destination ) 0 alors pour n = 0 à Ubound (source) AddtoArray Destination, source (n) Next n End If End Sub

Comment fonctionne cette fonction

La fonction principale, FindSumcombinations , appelle quelques fonctions subsidiaires qui implémentent des sous-tâches plus petites. La fonction nommée SumUprecursIVECombinations exécute l'algorithme de base qui trouve toutes les sommes possibles dans la plage spécifiée et filtre celles qui atteignent la cible. La fonction ArrayToString contrôle la forme des chaînes de sortie. Trois autres fonctions ( Sumarray , AddToArray et CopyArray ) sont responsables du traitement des tableaux intermédiaires: chaque fois que nous créons un tableau temporaire, ajoutez un élément du tableau source et vérifions si la somme cible est atteinte. L'algorithme de base est tiré de ce fil Stackoverflow, merci pour le partage!

Syntaxe

Du point de vue de l'utilisateur, la syntaxe de notre fonction personnalisée est aussi simple que celle-ci:

FINDSUMCOMBINATIONS (Range, SUM)

Où:

• La plage est la plage de nombres à tester.
• La somme est la somme cible.

Note! La fonction personnalisée ne fonctionne que dans Dynamic Array Excel 365 et 2021.

Comment utiliser la fonction FindSumcombinations:

1. Insérez le code ci-dessus dans un module de code de votre classeur et enregistrez-le en tant que classeur de macro (.xlsm). Les instructions étape par étape sont là.
2. Dans n'importe quelle cellule vierge, entrez une formule FindSumcombinations et appuyez sur la touche Entrée . Assurez-vous qu'il y a suffisamment de cellules vides à droite pour sortir toutes les combinaisons, sinon la formule renverra une erreur #spill.

Par exemple, pour trouver toutes les combinaisons possibles de nombres dans la plage A6: A15 qui égalent la somme en A3, la formule est:

=FindSumCombinations(A6:A15, A3)

Comme pour toute autre fonction de tableau dynamique, vous entrez la formule n'est qu'une cellule (C6 dans l'image ci-dessous) et il remplit les résultats dans autant de cellules que nécessaire. Par défaut, les chaînes séparées des virgules sont sorties dans une ligne:

Pour retourner les résultats dans une colonne, enveloppez la fonction personnalisée dans Transpose comme ceci:

=TRANSPOSE(FindSumCombinations(A6:A15, A3))

Pour sortir les chaînes sous la forme d'un tableau enfermé en accolades bouclées, modifiez la fonction ArrayToString comme suit:

Fonction privée ArraytoString (x () Aussi longue ) que la chaîne dim n comme long , résultat comme string result = "{" & x (n) pour n = lbound (x) 1 à ubound (x) result = result & "," & x (n) Next n result = result & "}" arraytoString = résultat final function final function final function

Les résultats seront similaires à cela:

Limitation : Cette fonction personnalisée fonctionne uniquement dans Dynamic Array Excel 365 et Excel 2021.

Obtenez toutes les combinaisons égales par une somme donnée avec la macro VBA

La fonction personnalisée décrite ci-dessus renvoie les combinaisons de nombres sous forme de chaînes. Si vous préférez avoir chaque numéro dans une cellule séparée, cette macro sera utile. Le code est écrit par un autre expert Excel Alexander Trifuntov qui a activement aidé les utilisateurs à résoudre divers problèmes d'Excel sur ce blog.

Commencez par ajouter le code suivant à votre classeur. Pour les étapes détaillées, veuillez voir comment insérer le code VBA dans Excel.

Macro pour trouver toutes les combinaisons qui s'ajoutent à une valeur donnée

REFARRAY PUBLIC1 As String Public DS en tant que variante publique ciblesum As Long Public TargetCol comme entier public cibleRow en tant qu'Integer Sub Combination () UserForm1.Show End Sub fonction Graycode (éléments comme variante ) que String Dim CodeVector () As Integer Dim I, KK, RR, Col1, Row1, N1, E comme entier Dim Dim Tone Integer , Upper As Integer Dim Sublist As Astubs As Integer Dim Dim Toned Tive Comme booléen dim uddstep as booléen dim sss dim cibleRray () as String kk = targetcol rr = targetRow Col1 = TargetCol 3 row1 = cibleRow oddStep = true inférieur = lbound (iteles ) upper = ubound (item 1, kk) .font.bold = True Cells (Rr - 1, Kk 1) .font.Bold = True Redim CodeVector (plus bas à Upper) 'Il démarre tout ce que le fait jusqu'à ce qu'il soit fait newsUB = "" pour i = inférieur à supérieur si codevector (i) = 1 alors newsub = "" alors newsub = " ," & items (i) SSSS = SSSS items (i) = Éléments SSS (i) terminer si fin si suivant i si newsub = "" alors newsub = "{}" 'vide set sublist = sublist & vbcrlf & newsub if sss = cibleSum alors cellules (rr, kk) .numberformat = "@" cell (rr, kk) = "{" & mid (newub, 2) "}" " "," ) n1 = ubound (cibleArray) pour e = 0 à n1 cellules (row1, col1 ) = cibleRay (e) row1 = row1 1 suivant e col1 = col1 1 row1 = cibleRow rr = rr 1 end if sss = 0 'maintenant le vecteur de code à mettre CodeVector (i) 1 i = i 1 boucle 'fait si i = supérieur: si i = usine alors fait = true else ' si non fait, retournez le * prochain * bit * bit: i = i 1 codevector (i) = 1 - codevector (i) fin si fin if oddstep = ne pas odststep 'togdle entre les étapes pair et les étapes impairs bouc

Ensuite, créez un userForm ( INSERT > UserForm ) avec la conception et les propriétés suivantes:

Après avoir rempli la conception du formulaire, ajoutez le code du formulaire. Pour cela, cliquez avec le bouton droit sur le formulaire dans l'explorateur de projet et sélectionnez Code de vue :

Code de la forme utilisateur

Private Sub CommandButton1_Click () Dim B dim C comme entier dim D comme entier dim a () as variant dim i as Integer dim e as Integer ds = range (rededit1) cibleSum = TextBox1.Value Range (Refedit2) . Sélectionnez TargetCol = Selection.Column TargetRow = SELECTION.ROW C = lbound (ds) d = UBound (ds) redim b (d - 1) pour i = 1 à d e = i - 1 b (e) = ds (i, 1) End i appelle griscode (b) met wed met sub private sub Label1_Click () end sub private sub sabe

Conseil. Au lieu de recréer le formulaire à partir de zéro, vous pouvez télécharger notre exemple de classeur à la fin de cet article et copier le module de code et UserForm1 à partir de là (voir comment copier une macro dans un autre classeur). Pour rendre la macro accessible dans tous vos classeurs, copiez-le dans le classeur de macro personnel.

Avec le code et le formulaire en place, appuyez sur Alt F8 et exécutez la macro Findallcombinations :

Sur la forme qui apparaît, définissez ce qui suit:

• La plage avec les numéros de source (A4: A13)
• La somme cible (50)
• La cellule supérieure gauche de la gamme de destination (C4).

Lorsque vous spécifiez la cellule supérieure gauche de la plage de sortie, assurez-vous qu'il y a au moins une ligne vierge au-dessus (pour les en-têtes) et suffisamment de cellules vierges vers le bas et à droite . S'il y a des cellules vierges insuffisantes, vos données existantes seront écrasées.

Cliquez sur OK produira le résultat comme indiqué dans la capture d'écran ci-dessous:

• Dans C4: C6, vous avez les combinaisons de nombres en tant que valeurs séparées par les virgules.
• Les colonnes F, G et H contiennent les mêmes combinaisons de nombres, chaque nombre résidant dans une cellule séparée.
• En D4, vous avez la somme cible.

Cette forme de sortie facilite la vérification du résultat - entrez simplement la formule de somme dans la cellule F13, faites-la glisser vers la droite sur deux autres cellules et vous verrez que chaque combinaison de nombres s'additionne à la valeur spécifiée (50).

Limitation: Pour un grand ensemble de nombres, la macro peut prendre un certain temps pour générer toutes les combinaisons possibles.

Avantages : fonctionne dans toutes les versions d'Excel 2010 - 365; Fournit deux formes de sortie - chaînes de valeurs et de nombres séparés par les virgules dans des cellules séparées.

En conclusion, trouver toutes les combinaisons de valeurs qui égalent une certaine valeur est un outil puissant pour résoudre un large éventail de problèmes d'analyse des données. J'espère que cet article vous a fourni un point de départ utile pour explorer davantage ce sujet et gérer des problèmes similaires dans votre propre travail. Merci d'avoir lu!

Classeur de pratique

Trouvez des combinaisons qui équivaut à une somme donnée - Exemples (fichier .xlsm)

Ce qui précède est le contenu détaillé de. pour plus d'informations, suivez d'autres articles connexes sur le site Web de PHP en chinois!