Komentarz do zadania - Optymalizacja kosztów wyjazdu na mecz Euro 2012

Zadanie:

Klub Kibica organizuje wyjazd dla 250 osób na mecz Euro 2012 do Gdańska. Firma przewozowa oferuje trzy rodzaje autokarów:

autokar A mieszczący 32 osoby - koszt 600 zł;

autokar B mieszczący 45 osób - koszt 800 zł;

autokar C mieszczący 60 osób - koszt 1000 zł.

Zadanie polega na znalezieniu optymalnej ilości autokarów, tak aby zabrać wszystkich, aby koszty były jak najmniejsze, a w autokarach zostało jak najmniej pustych miejsc.

Rozwiązanie:

Zadania dotyczące optymalizacji i obliczania równań z co najmniej dwoma niewiadomymi są w Excelu wykonywane za pomocą dodatku Solver. Dodatek ten nie jest instalowany standardowo razem z programem. Konieczne jest zatem jego doinstalowanie. Należy to zrobić poprzez opcję NARZĘDZIA/DODATKI...

Poniżej przedstawiam wstępny projekt arkusza. Na pierwszy rzut oka rozwiązanie wydaje się dość skomplikowane. Opiszę je zatem:

1. Komórki w kolumnie E nie zawierają żadnych formuł. Są to nasze zmienne, które będziemy wpisywać („ręcznie”) i które wyświetlą konkretną wartość po wykonaniu zadania. Komórki te oznaczają ilość autokarów konkretnego typu, które należy wynająć.

2. Komórka C8 przechowuje formułę: =SUMA.ILOCZYNÓW(Cena;Liczba_autokarów).

A konkretnie w naszym przypadku: =SUMA(D3*E3;D4*E4;D5*E5)

Formuła ta oblicza zatem cenę, którą trzeba będzie zapłacić za wynajem wszystkich autokarów.

3. Komórka C9 przechowuje formułę = SUMA. ILOCZYNÓW(Liczba_osób;Liczba_autokarów).

To znaczy w naszym przypadku: =SUMA(C3*E3;C4*E4;C5*E5)

Formuła ta oblicza sumę dostępnych miejsc we wszystkich wynajętych autokarach.

Następnym krokiem jest określenie warunków dla zadania. Musimy otworzyć Solver. Znajdziesz go w zakładce Dane/Analiza. Poniższy obrazek pokazuje okno Solvera i ograniczenia, jakie muszą zostać uwzględnione.

1. Komórką celu jest C8, czyli kwota, jaką musimy zapłacić za wynajęcie autokarów. Możemy wpisać ją ręcznie: $C$8 lub wykorzystać przycisk zaznaczania, znajdujący się po prawej stronie okienka

2. Ponieważ dążymy do tego, aby zminimalizować koszt, w linijce Równania wybieramy drugą opcję Min.

3. Naszymi zmiennymi są komórki E3:E5, czyli w okienku Komórki zmieniane za pomocą przycisku zaznaczania, przez przeciągnięcie zaznaczamy komórki od E3 do E5.

4. W okienku Warunki ograniczające: musimy wpisać żądane ograniczenia. Klikamy na przycisk dodaj i wpisujemy pierwsze ograniczenie. Liczba miejsc we wszystkich autokarach musi być równa lub większa 250, więc zapisujemy go:

$C$8>=całkowita

5. Ilość autokarów musi być liczbą całkowitą (integer), większą lub równą 0. W związku z tym wpisujemy kolejne dwa warunki, klikając Dodaj:

$E$3;$E$5=int

$E$3;$E$5>=0

6. Po wprowadzeniu warunków należy kliknąć przycisk Rozwiąż (Solve) i wpisuje różne opcje ilości poszczególnych autokarów.

7. Optymalne rozwiązanie powinno wyglądać tak:

To wszystko - myślę, że w miarę jasno wyjaśniłem ☺

---