Excel : Trouver un sous-ensemble de nombres qui s'additionnent à un total donné ?

C'est possible avec l'add-in Solver. Les étapes suivantes ont fonctionné pour moi dans Excel 2007 et 2010.

1. Désignez une cellule pour contenir le résultat (C1 pour cet exemple) - c'est la cellule cible, et une colonne qui excelle peut être utilisée pour le travail de grattage (B1:B100 pour cet exemple)
2. Dans la cellule cible, entrez la formule “=SUMPRODUIT(A1:A100,B1:B100)” (sans les guillemets). Cela permettra de calculer la somme de A1*B1+A2*B2+…etc
3. Sélectionnez Ouvrir le solveur (onglet Données, Groupe d'analyse)
4. La cellule cible doit être évidente ($C$1 pour cet exemple)
5. Pour “Egal à :”, sélectionnez “Valeur de :” et entrez la valeur souhaitée
6. Dans la case “En changeant de cellule”, entrez “$B$1:$B$100” (pas de guillemets, et il peut être nécessaire d'initialiser vous-même ces valeurs à 0)
7. Ajoutez une contrainte aux cellules qui peuvent être modifiées. Dans la liste déroulante, sélectionnez “bin” (binaire). Cela limite les valeurs de ces cellules à 0 (en retirant la cellule A correspondante de la somme) ou à 1 (en ajoutant la cellule A correspondante à la somme).
   
8. Cliquez sur “Résoudre” et attendez. Les nombres qui font partie du sous-ensemble que vous recherchez auront un 1 dans la colonne B

Si la résolution prend beaucoup de temps, vous pouvez l'aider en supprimant les lignes qui ne fonctionneront manifestement pas (le total est en dollars, et une seule ligne a des cents non nuls)

• *

Bonus : vous pouvez faire en sorte qu'Excel mette automatiquement en évidence les cellules que vous recherchez en ajoutant un formatage conditionnel à ces cellules. Sélectionnez toutes les cellules que vous souhaitez formater et à partir de (onglet Accueil)>>(groupe Styles)>>Formatage conditionnel>>Nouvelle règle, sélectionnez “Utiliser une formule pour déterminer les cellules à formater”. Dans la formule, entrez ‘=$B1=1’ (sans les guillemets) qui sera considéré comme vrai si la ligne correspondante dans la colonne B est 1. Pour le format, vous pouvez ajouter ce que vous voulez (gras, italique, remplissage vert, etc).

Un autre moyen facile de trouver les lignes importantes est de trier la colonne B Z->A, et tous les 1 viendront en haut.

* L'add-in du solveur peut être installé en suivant les étapes suivantes

1. Cliquez sur le bouton Microsoft Office, puis sur Options Excel.
2. Cliquez sur Add-Ins, puis dans la case Gérer, sélectionnez Add-ins Excel.
3. Cliquez sur Go.
4. Dans la case Add-Ins available, cochez la case Solver Add-in, puis cliquez sur OK. (Si le complément Solveur n'est pas répertorié dans la case Compléments disponibles, cliquez sur Parcourir pour localiser le complément).
5. Si vous êtes invité à indiquer que le complément Solver n'est pas actuellement installé sur votre ordinateur, cliquez sur Oui pour l'installer.

Il existe un complément Excel peu coûteux SumMatch , qui mettra en évidence le sous-ensemble des chiffres qui correspondent à une somme cible.