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

Mam kolumnę liczb (powiedzmy, że to A1: A100) i muszę znaleźć ich podzbiór, który sumuje się do pewnej sumy.

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

1. Wyznacz komórkę do przechowywania wyniku (C1 w tym przykładzie) - jest to komórka docelowa i kolumna, której program Excel może użyć do zarysowania (B1: B100 w tym przykładzie)
2. W komórce docelowej wprowadź formułę „= SUMPRODUCT (A1: A100, B1: B100)” (bez cudzysłowów). To obliczy sumę A1 * B1 + A2 * B2 + ... itd
3. Wybierz Otwórz solver (karta Dane, grupa Analiza)
4. Komórka docelowa powinna być oczywista (w tym przykładzie $ C $ 1)
5. W polu „Równe do” wybierz „Wartość:” i wprowadź żądaną wartość
6. W polu „Zmieniając komórki” wpisz „$ B $ 1: $ B $ 100” (bez cudzysłowu i może być konieczne samodzielne zainicjowanie tych wartości na 0)
7. Dodaj ograniczenie do komórek, które można zmienić. Z rozwijanego menu wybierz „bin” (binarny). Ogranicza to wartości tych komórek do 0 (usunięcie odpowiedniej komórki A z sumy) lub 1 (dodanie odpowiedniej komórki A do sumy).
8. Kliknij „Rozwiąż” i poczekaj. Liczby, które są częścią poszukiwanego podzbioru, będą miały 1 w kolumnie B.

Jeśli solver zajmuje dużo czasu, możesz pomóc, usuwając wiersze, które oczywiście nie będą działać (suma jest w dolarach, a tylko jeden wiersz ma niezerowe centy)

Premia: Możesz mieć program Excel automatycznie podświetlający poszukiwane komórki przez dodanie formatowania warunkowego do tych komórek. Wybierz wszystkie komórki, które chcesz sformatować, a następnie z (karta Strona główna) >> (Grupa stylów) >> Formatowanie warunkowe >> Nowa reguła wybierz „Użyj formuły, aby określić, które komórki sformatować”. W formule wpisz „= $ B1 = 1” (bez cudzysłowów), który będzie miał wartość true, jeśli odpowiadający mu wiersz w kolumnie B ma wartość 1. W tym formacie możesz dodać, co chcesz (pogrubienie, kursywa, zielone wypełnienie, itp).

Innym łatwym sposobem na znalezienie ważnych wierszy jest posortowanie kolumny B Z-> A, a wszystkie 1 zostaną przeniesione na górę.

* Dodatek solvera można zainstalować, wykonując poniższe czynności

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

Istnieje tani dodatek Excel SumMatch , który podświetli podzbiór liczb, które składają się na sumę docelową.