SOLVER to rewelacyjne narzędzie do rozwiązywania problemów optymalizacyjnych. Będziemy wybierać kadrę piłkarzy, którą umownie nazwiemy „drużyną marzeń”. Celem jest to, aby zmaksymalizować wartość drużyny, uwzględniając szereg warunków ograniczających.
Warunki zadania
- Potrzebujemy wybrać 15-graczy. Wszystkich jest 60.
- Każdy gracz ma przypisane pewne atrybuty:
– Team. Drużyna w której gra.
– Pos. Pozycja na boisku.
– Pts. Punkty jakie uzbierał za swoją grę.
– Cost. Cena rynkowa. - Naszym zadaniem jest wybrać 15-zawodników, których łączna wartość rynkowa nie przekroczy 100 jednostek, a których wartość sportowa (punkty) jest najwyższa.
- Istnieje cały szereg warunków ograniczających:
– Łączny koszt drużyny. Nie może przekraczać 100 jednostek (napisałem o tym powyżej).
– Maksymalnie 3-graczy z jednej drużyny. Mamy sporo drużyn.
– Musimy wybrać: 2-bramkarzy (GKP), 5-obrońców (DEF), 5-pomocników (MID) i 3-napastników (FWD).
„Drużyna marzeń” – rozwiązanie
Warunki ograniczające
- W pierwszej kolejności zdecydowałem się do oryginalnej tabeli dorzucić kolumnę BestTeam. Będzie ona przechowywać informację 0/1 czy dany zawodnik znajdzie się w „drużynie marzeń”. Ta kolumna to nasze zmienne, którymi będzie mógł manipulować Solver. Nie znajduje się w nich rzecz jasna żadna formuła.
- W środku wypisałem wszystkie możliwe drużyny i formułę zliczającą ilość zawodników wybranych do „drużyny marzeń” właśnie z każdej z tych drużyn. Formuła w komórce I2 to: =SUMA.JEŻELI(Team;$H2;BestTeam).
- Podobnie, w kolumnie K na górze wypisałem wszystkie możliwe pozycje i formułę zliczającą ilość zawodników wybranych do „drużyny marzeń” właśnie dla każdej z tych pozycji. Formuła w komórce L2 to: =SUMA.JEŻELI(Pos;$K2;BestTeam)
- W komórce L7 mam formułę: =MAX($I$2:$I$18) – pamiętajmy, że nie możemy mieć więcej niż trzech graczy z jednej drużyny.
- W komórce L8 mam formułę: =SUMA.ILOCZYNÓW(Cost;BestTeam) pamiętajmy, że nasza drużyna nie może kosztować więcej niż 100 jednostek.
Kolumna celu
Kolumna celu (na prawo od pomarańczowego pola) zawiera formułę: =SUMA.ILOCZYNÓW(Pts;BestTeam) – czyli przemnażamy dwie tablice (pierwsza dotyczy punktów, druga informacji 0/1 czy gracz znajdzie się w „drużynie marzeń”).
Solver
Aby uzyskać wyniki takie jak na screenie musiałem zdefiniować warunki w Solverze.
Poza opisanymi warunkami musiałem zaznaczyć, że kolumna BestTeam powinna zawierać wartości binarne (zawodnik nie może być w połowie w „drużynie marzeń” – albo jest albo go nie ma).
Skład „drużyny marzeń”
A tak wygląda moja „drużyna marzeń”. Jak widać Solver nie wybrał tych najdroższych piłkarzy, ale tych względnie tańszych, których wartość sportowa (punkty) jest bardzo wysoka.
Wszystkie ograniczenia zostały uwzględnione. Udało nam się zbudować optymalną drużynę o wartości 1706 punktów i cenie równej dokładnie 100 jednostek.
