Excel: Znajdź podzbiór liczb, które dodają się do danej sumy?

Jest to możliwe dzięki dodatkowi Solver. Poniższe kroki działały dla mnie w Excelu 2007 i 2010.

1. Wyznacz komórkę do przechowywania wyniku (C1 w tym przykładzie) - jest to komórka docelowa, oraz kolumnę, którą Excel może wykorzystać do pracy w trybie scratch (B1:B100 w tym przykładzie)
2. W komórce docelowej wpisz formułę “=SUMPRODUCT(A1:A100,B1:B100)” (bez cudzysłowów). Spowoduje to obliczenie sumy A1:A100+A2:B100+…itd.
3. Wybierz opcję Otwórz solver (zakładka Dane, grupa Analiza)
4. Komórka docelowa powinna być oczywista (w tym przykładzie $C$1)
5. Dla ‘Equal To:’ wybierz ‘Value of:’ i wprowadź żądaną wartość
6. W polu ‘By Changing Cells’ wpisz “$B$1:$B$100” (bez cudzysłowów, może być konieczne samodzielne zainicjowanie tych wartości na 0)
7. Dodaj ograniczenie do komórek, które mogą być zmieniane. Z listy rozwijanej wybierz “bin” (Binary). Ogranicza to wartości tych komórek do 0 (usunięcie odpowiadającej im komórki A z sumy) lub 1 (dodanie odpowiadającej im komórki A do sumy).
   
8. Kliknąć “Solve” i czekać. Liczby, które należą do szukanego podzbioru, będą miały 1 w kolumnie B.

Jeśli rozwiązywanie trwa długo, możesz mu pomóc, usuwając wiersze, które w oczywisty sposób nie będą działać (suma jest w dolarach i tylko jeden wiersz ma niezerowe centy)

Bonus: Możesz sprawić, że Excel automatycznie zaznaczy komórki, których szukasz, dodając do nich formatowanie warunkowe. Zaznacz wszystkie komórki, które chcesz sformatować, i w zakładce Strona główna (grupa Style) wybierz opcję “Użyj formuły do określenia komórek do sformatowania”. W formule wpisz “=$B1=1” (bez cudzysłowów), co będzie miało wartość true, jeśli w odpowiednim wierszu w kolumnie B będzie 1. Dla formatu możesz dodać co tylko chcesz (pogrubienie, kursywa, zielone wypełnienie, itp.).

Innym prostym sposobem na znalezienie ważnych wierszy jest posortowanie kolumny B Z->A, a wszystkie 1 pojawią się na górze.

Dodatek solver można zainstalować, wykonując następujące czynności

1. Kliknij przycisk Microsoft Office, a następnie kliknij polecenie Opcje programu Excel.
2. Kliknij pozycję Dodatki, a następnie w polu Zarządzaj wybierz pozycję Dodatki programu Excel.
3. Kliknij przycisk Go.
4. W polu Dostępne dodatki zaznacz pole wyboru Dodatek Solver, a następnie kliknij przycisk OK. (Jeśli dodatek Solver Add-in nie jest wymieniony w polu Dostępne dodatki, kliknij przycisk Przeglądaj, aby zlokalizować dodatek).
5. Jeśli zostanie wyświetlony monit, że dodatek Solver Add-in nie jest obecnie zainstalowany na komputerze, kliknij Tak, aby go zainstalować.

Istnieje niedrogi dodatek do Excela SumMatch , który podświetli podzbiór liczb, które sumują się do sumy docelowej.