Excel : Trouver un sous-ensemble de nombres dont la somme donne un total donné ?

Microsoft 365 & Office Excel & VBA
,
1

Excel : Trouver un sous-ensemble de nombres dont la somme donne un total donné ?

2

C’est possible avec le complément Solveur*. Les étapes suivantes ont fonctionné pour moi dans Excel 2007 et 2010.

  • Désignez une cellule pour contenir le résultat (C1 pour cet exemple) - c’est la cellule cible, et une colonne qu’Excel peut utiliser comme espace de travail (B1:B100 pour cet exemple)

  • Dans la cellule cible, entrez la formule “=SUMPRODUCT(A1:A100,B1:B100)” (sans guillemets). Cela calculera la somme de A1B1+A2B2+…etc

  • Sélectionnez Ouvrir le solveur (onglet Données, groupe Analyse)

  • La cellule cible devrait être évidente ($C$1 pour cet exemple)

  • Pour ‘Égal à :’ sélectionnez ‘Valeur de :’ et entrez la valeur souhaitée

  • Dans ‘En modifiant les cellules’, entrez “$B$1:$B$100” (sans guillemets, et il peut être nécessaire d’initialiser ces valeurs à 0 vous-même)

  • Ajoutez une contrainte aux cellules qui peuvent être modifiées. Dans le menu déroulant, sélectionnez ‘bin’ (Binaire). Cela limite les valeurs de ces cellules à 0 (supprimant la cellule A correspondante de la somme) ou 1 (ajoutant la cellule A correspondante à la somme).

  • Cliquez sur ‘Résoudre’ et attendez. Les nombres qui font partie du sous-ensemble recherché auront un 1 dans la colonne B

Si le solveur prend trop de temps, vous pouvez l’aider en supprimant les lignes qui ne fonctionneront évidemment pas (le total est en dollars, et une seule ligne a des centimes non nuls)

Bonus : Vous pouvez faire en sorte qu’Excel mette automatiquement en surbrillance les cellules que vous recherchez en ajoutant une mise en forme conditionnelle à ces cellules. Sélectionnez toutes les cellules que vous souhaitez formater et depuis (onglet Accueil)>>(groupe Styles)>>Mise en forme conditionnelle>>Nouvelle règle, sélectionnez ‘Utiliser une formule pour déterminer les cellules à formater’. Dans la formule, entrez ‘=$B1=1’ (sans guillemets) qui sera évalué 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 de Z->A, et tous les 1 apparaîtront en haut.

*Le complément Solveur peut être installé avec ces étapes

  • Cliquez sur le bouton Microsoft Office, puis cliquez sur Options Excel.

  • Cliquez sur Compléments, puis dans la zone Gérer

(Réponse tronquée)