Excel: Zoek een deelverzameling van getallen die optellen tot een bepaald totaal?

Het is mogelijk met de Solver add-in. De volgende stappen werkten voor mij in Excel 2007 en 2010.

1. Wijs een cel aan om het resultaat in op te slaan (C1 in dit voorbeeld) - dit is de doelcel, en een kolom die Excel kan gebruiken voor scratch-werk (B1:B100 in dit voorbeeld)
2. In de doelcel voert u de formule “=SOMPRODUCT(A1:A100,B1:B100)” in (zonder aanhalingstekens). Dit berekent de som van A1*B1+A2*B2+…etc
3. Selecteer Open de oplosser (tabblad Gegevens, groep Analyse)
4. De doelcel moet duidelijk zijn ($C$1 in dit voorbeeld)
5. 5. Kies bij ‘Gelijk aan:’ ‘Waarde van:’ en voer de gewenste waarde in
6. Voer bij ‘Door cellen te veranderen’ “$B$1:$B$100” in (geen aanhalingstekens, en het kan nodig zijn om deze waarden zelf op 0 te initialiseren)
7. Voeg een restrictie toe aan de cellen die gewijzigd kunnen worden. Selecteer in de vervolgkeuzelijst “bin” (binair). Dit beperkt de waarden van deze cellen tot 0 (de overeenkomstige A-cel uit de som verwijderen) of 1 (de overeenkomstige A-cel aan de som toevoegen).
   
8. Klik op ‘Oplossen’ en wacht. De getallen die deel uitmaken van de deelverzameling die je zoekt, krijgen een 1 in de B-kolom

• *

Als de oplosser veel tijd nodig heeft, kun je hem helpen door rijen te verwijderen die duidelijk niet werken (het totaal is in dollars, en slechts één rij heeft niet-nul centen)

• *

Bonus: je kunt Excel automatisch de cellen laten markeren die je zoekt, door voorwaardelijke opmaak aan die cellen toe te voegen. Selecteer alle cellen die je wilt opmaken en selecteer in het tabblad (Start) de optie (groep Stijlen) voorwaardelijke opmaak en nieuwe regel de optie Gebruik een formule om te bepalen welke cellen moeten worden opgemaakt. In de formule voert u ‘=$B1=1’ (zonder aanhalingstekens) in, die waar wordt als de overeenkomstige rij in de kolom B een 1 is. Voor de opmaak kunt u toevoegen wat u wilt (vet, cursief, groen gevuld, enz.).

Een andere gemakkelijke manier om de belangrijke rijen te vinden is om kolom B Z- te sorteren>A, en alle 1'en komen bovenaan te staan.

• *

*De solver add-in kan geïnstalleerd worden met deze stappen

1. Klik op de Microsoft Office knop, en dan op Excel Opties.
2. Klik op Invoegtoepassingen en selecteer vervolgens in het vak Beheren de optie Excel-invoegtoepassingen.
3. Klik op Ga.
4. Schakel in het vak Add-Ins beschikbaar het selectievakje Solver Add-in in en klik op OK. (Als Solver Add-in niet voorkomt in het vak Add-Ins beschikbaar, klikt u op Bladeren om de invoegtoepassing te vinden).
5. Als u de melding krijgt dat de Solver Add-in momenteel niet op uw computer is geïnstalleerd, klikt u op Ja om deze te installeren.

Er is een goedkope Excel-invoegtoepassing SumMatch , die de deelverzameling van getallen markeert die optellen tot een doelsom.