Rozwiązywanie układów równań w Excelu (Solver)

Kiedyś opisałem narzędzie Szukaj wyniku. Za pomocą tego narzędzia można rozwiązywać proste równania z jedną niewiadomą. Gdy tych niewiadomych (zmiennych, parametrów) jest więcej to należy skorzystać z dodatku Solver. Najpierw sprawdź, na karcie Dane na samym końcu po prawej stronie znajduje się ikona Solver. Jeśli jej nie ma, to zapoznaj się z notką: Instalacja dodatku Solver.

W 5-6 klasie podstawówki w zbiorze zadań natrafiłem na takie zadanie:

Janek mieszka na wsi. Na podwórku były króliki i kury. Zwierzątka miały w sumie 27 głów i 74 nogi . Ile było królików, a ile było kur?

Najpierw należy ułożyć układ dwóch równań z dwiema niewiadomymi. Weźmy pod uwagę następujące fakty:

  • Kura ma 2 nogi
  • Królik ma 4 nogi
  • Zarówno kura jak i królik posiadają jedną głowę
  • Zmienną X oznaczymy liczbę kur
  • Zmienną Y oznaczymy liczbę królików

Nasze równanie ma następującą postać:

Gdybym był wezwany do tablicy, to prawdopodobnie swoim brzydkim pismem bym nabazgrał coś takiego:

No dobra, to teraz zatrudnijmy Solvera

Przekształćmy nasz równanie

Tak, aby po prawej stronie równania były zera a po lewej stornie były niewiadome i wartości tj.

  1. W komórce A1 wpisz kury (x)
  2. W komórce A2 wpisz króliki (y)
  3. W komórkach B1B2 Solver będzie wstawiał jakieś liczby do czasu, aż wszystkie warunki, które za chwilę zdefiniujemy nie zostaną spełnione
  4. W komórce C1 wpisz pierwsze równanie układu równań gdzie za x podstawimy adres komórki B1 a za y adres komórki B2. Nasza formuła będzie miała postać =B1+B2-27
  5. W komórce C2 wpiszmy drugie równanie układu równań. Nasza formuła będzie miała postać: =2*B1+4*B2-74
  6. W komórce C3 wpisz funkcję =SUMA(C1:C2) – chcemy doprowadzić nasze równanie do takiej postaci, aby suma wszystkich liczb po prawej stronie równania wynosiła 0.
  7. Program Solver będzie w komórkach C1C2 w taki sposób dobierał wartości, aby komórka celu znajdująca się w komórce C3 wynosiła 0. Dodatkowo nałożymy warunki, że zawartość komórek C1C2 musi być równa zero, bo po prawej stronie naszego układu równań mamy zera.
  8. Tak powinien wyglądać arkusz:
  1. Połowa zadania za nami. Teraz z karty Dane wybierz polecenie Solver.
  2. W polu Ustaw cel wpisz C3 następnie poniżej zaznacz opcję Wartość i w polu tekstowym obok wpisz 0
  3. W polu Przez zmienianie komórek zmiennych wpisz zakres komórek, które Solver będzie zmieniał tj. B1 B2 (w tych komórkach znajdują się wartości naszych zmiennych xy)
  1. Następnie dodajmy dwa ograniczenia. W tym celu kliknij w przycisk Dodaj i dodaj ograniczenie C1 = 0 a następnie dodaj drugie ograniczenie C2 = 0.
  1. Ponieważ liczba kur i królików raczej nie powinna być liczbą ułamkową (nie można posiadać na podwórku np. 2,5 kury), więc dodajmy dodatkowe ograniczenie tj. wartość w komórce B1 musi być całkowita wybierz z listy rozwijanej wartość int a następnie wybierz to samo ograniczenie dla komórki B2.
  1. Okno programu Solver powinno wyglądać jak na poniższym rysunku.

Kliknij w przycisk Rozwiąż, aby w końcu poznać rozwiązanie naszej zagadki!

Jeśli artykuł Ci się podobał, będę wdzięczny gdy go udostępnisz w mediach społecznościowych
Ten wpis został opublikowany w kategorii Narzędzia inżynierskie i oznaczony tagami , . Dodaj zakładkę do bezpośredniego odnośnika.

Dodaj komentarz

Twój adres e-mail nie zostanie opublikowany. Wymagane pola są oznaczone *

Witryna wykorzystuje Akismet, aby ograniczyć spam. Dowiedz się więcej jak przetwarzane są dane komentarzy.