Jedna z najciekawszych funkcji w programu Microsoftu Excel jest rozwiązaniem do wyszukiwania. Należy jednak zauważyć, że tego narzędzia nie można uznać za najpopularniejsze wśród użytkowników ta aplikacja. Ale na próżno. Przecież ta funkcja, korzystając z danych początkowych, poprzez wyliczenie, znajduje najbardziej optymalne rozwiązanie ze wszystkich dostępnych. Dowiedzmy się, jak korzystać z funkcji Znajdź rozwiązanie w programie Microsoft Excel.
Możesz długo wyszukiwać na taśmie, na której znajduje się narzędzie Solution Search, ale nadal nie możesz znaleźć tego narzędzia. Po prostu, aby aktywować tę funkcję, musisz ją włączyć w ustawieniach programu.
Aby aktywować funkcję Szukaj rozwiązań w programie Microsoft Excel 2010 i nowszych wersjach należy przejść do zakładki „Plik”. W przypadku wersji 2007 kliknij przycisk Microsoft Office w lewym górnym rogu okna. W oknie, które zostanie otwarte, przejdź do sekcji „Ustawienia”.
W oknie parametrów kliknij pozycję „Dodatki”. Po przejściu, w dolnej części okna, naprzeciw parametru „Zarządzanie”, wybierz wartość „Dodatki Excel” i kliknij przycisk „Przejdź”.
Otworzy się okno z dodatkami. Zaznaczamy obok nazwy potrzebnego nam dodatku - „Wyszukaj rozwiązanie”. Kliknij przycisk „OK”.
Następnie na wstążce Excela w zakładce „Dane” pojawi się przycisk umożliwiający uruchomienie funkcji Szukaj rozwiązań.
Przygotowanie stołu
Teraz, gdy włączyliśmy tę funkcję, zobaczmy, jak to działa. Najłatwiej sobie to wyobrazić konkretny przykład. Mamy więc stół wynagrodzenie pracownicy przedsiębiorstwa. Na każdego pracownika powinniśmy liczyć premię, która jest iloczynem wynagrodzenia wskazanego w osobnej kolumnie przez określony współczynnik. Jednocześnie suma całkowita Pieniądze przeznaczone na premię wynosi 30 000 rubli. Komórka, w której znajduje się ta kwota, nazywa się docelową, ponieważ naszym celem jest wybranie danych dokładnie dla tej liczby.
Musimy obliczyć współczynnik, który służy do obliczenia kwoty bonusu, korzystając z funkcji Szukaj rozwiązań. Komórka, w której się znajduje, nazywana jest pożądaną.
Komórki docelowe i wyszukiwania muszą być ze sobą powiązane za pomocą formuły. W naszym konkretnym przypadku formuła znajduje się w komórce docelowej i ma następującą postać: „=C10*$G$3”, gdzie $G$3 to adres bezwzględny żądanej komórki, a „C10” to całkowita ilość wynagrodzenia, od których naliczana jest premia, pracownicy przedsiębiorstwa.
Uruchamianie narzędzia Znajdź rozwiązanie
Po przygotowaniu tabeli, będąc w zakładce „Dane”, należy kliknąć na przycisk „Wyszukaj rozwiązanie”, który znajduje się na wstążce w bloku narzędziowym „Analiza”.
Otworzy się okno ustawień, w którym należy wprowadzić dane. W polu „Optymalizuj funkcję celu” należy wpisać adres komórki docelowej, w której będzie się znajdować łączna kwota premii dla wszystkich pracowników. Można to zrobić albo wpisując współrzędne ręcznie, albo klikając przycisk znajdujący się po lewej stronie pola wprowadzania danych.
Następnie okno parametrów zwinie się i będziesz mógł wybrać żądaną komórkę tabeli. Następnie należy ponownie kliknąć w ten sam przycisk po lewej stronie formularza z wprowadzonymi danymi, aby ponownie rozwinąć okno parametrów.
Pod oknem z adresem komórki docelowej należy ustawić parametry wartości, które będą się w niej znajdować. Może to być wartość maksymalna, minimalna lub określona. W naszym przypadku tak będzie ostatnia opcja. Dlatego ustawiamy przełącznik w pozycji „Wartości”, a w polu po lewej stronie wpisujemy liczbę 30000. Jak pamiętamy, jest to liczba zgodnie z warunkami całkowita kwota premie dla wszystkich pracowników przedsiębiorstwa.
Poniżej znajduje się pole „Zmiana komórek zmiennych”. Tutaj musisz wskazać adres żądanej komórki, w której, jak pamiętamy, znajduje się współczynnik, mnożąc wynagrodzenie podstawowe, przez które zostanie obliczona kwota premii. Adres można wprowadzić w taki sam sposób, jak zrobiliśmy to w przypadku komórki docelowej.
W polu „Podlega ograniczeniom” możesz ustawić pewne ograniczenia dla danych, na przykład ustawić wartości na całkowite lub nieujemne. W tym celu należy kliknąć przycisk „Dodaj”.
Następnie otworzy się okno dodawania ograniczeń. W polu „Link do komórek” wpisz adres komórek, dla których wprowadzane jest ograniczenie. W naszym przypadku jest to pożądana komórka ze współczynnikiem. Następnie umieszczamy wymagany znak: „mniejszy lub równy”, „większy lub równy”, „równy”, „liczba całkowita”, „binarny” itp. W naszym przypadku do utworzenia współczynnika wybierzemy znak „większy lub równy”. Liczba dodatnia. Odpowiednio w polu „Ograniczenie” wskazujemy cyfrę 0. Jeśli chcemy ustawić kolejne ograniczenie, należy kliknąć przycisk „Dodaj”. W przeciwnym razie kliknij przycisk „OK”, aby zapisać wprowadzone ograniczenia.
Jak widać, po tym ograniczenie pojawia się w odpowiednim polu okna parametrów wyszukiwania rozwiązania. Możesz także ustawić zmienne nieujemne, zaznaczając pole obok odpowiedniego parametru tuż poniżej. Wskazane jest, aby ustawione tutaj parametry nie były sprzeczne z parametrami określonymi w ograniczeniach, w przeciwnym razie może wystąpić konflikt.
Dodatkowe ustawienia można określić klikając na przycisk „Opcje”.
Tutaj możesz ustawić dokładność wiązania i granice rozwiązania. Po wprowadzeniu wymaganych danych kliknij przycisk „OK”. Jednak w naszym przypadku nie ma potrzeby zmiany tych parametrów.
Po skonfigurowaniu wszystkich ustawień kliknij przycisk „Znajdź rozwiązanie”.
Następnie program Excel działa w komórkach niezbędne obliczenia. Równocześnie z wyprowadzeniem wyników otwiera się okno, w którym możesz zapisać znalezione rozwiązanie lub przywrócić oryginalne wartości, przesuwając przełącznik do odpowiedniej pozycji. Niezależnie od wybranej opcji, zaznaczając checkbox „Wróć do okna dialogowego opcji”, możesz wrócić do ustawień wyszukiwania rozwiązań. Po zaznaczeniu pól wyboru i przełączników kliknij przycisk „OK”.
Jeżeli z jakiegoś powodu wyniki wyszukiwania rozwiązań Cię nie zadowalają lub przy ich obliczaniu program generuje błąd, to w takim przypadku wracamy sposobem opisanym powyżej do okna dialogowego parametrów. Sprawdzamy wszystkie wprowadzone dane, ponieważ mógł gdzieś wystąpić błąd. Jeśli błąd nie został znaleziony, przejdź do parametru „Wybierz metodę rozwiązania”. Tutaj możesz wybrać jedną z trzech metod obliczeniowych: „Poszukiwanie rozwiązań problemów nieliniowych metodą OPG”, „Szukanie rozwiązań problemów liniowych metodą simplex” oraz „Ewolucyjne poszukiwanie rozwiązań”. Domyślnie używana jest pierwsza metoda. Staramy się rozwiązać problem wybierając inną metodę. Jeśli się nie powiedzie, spróbuj ponownie, używając ostatniej metody. Algorytm działań jest taki sam, jak opisaliśmy powyżej.
Jak widać, funkcja Szukaj rozwiązania jest dość skomplikowana ciekawe narzędzie, które, kiedy prawidłowe użycie, może znacznie zaoszczędzić czas użytkownika na różnych obliczeniach. Niestety nie każdy użytkownik wie o jego istnieniu, a tym bardziej nie wie, jak poprawnie pracować z tym dodatkiem. W pewnym sensie to narzędzie przypomina tę funkcję , ale jednocześnie ma z nim znaczące różnice.
Rozwiązywanie równań i układów nieliniowych”
Cel pracy: Badanie możliwości pakietu Ms Excel 2007 podczas rozwiązywania równania nieliniowe i systemy. Nabycie umiejętności rozwiązywania równań i układów nieliniowych z wykorzystaniem pakietu.
Ćwiczenie 1. Znajdź pierwiastki wielomianu x 3 - 0,01x 2 - 0,7044x + 0,139104 = 0.
Najpierw rozwiążmy równanie graficznie. Wiadomo, że graficznym rozwiązaniem równania f(x)=0 jest punkt przecięcia wykresu funkcji f(x) z osią odciętych, tj. wartość x, przy której funkcja zanika.
Zapiszmy nasz wielomian w przedziale od -1 do 1 z krokiem 0,2. Wyniki obliczeń pokazano na rys., gdzie do komórki B2 wpisano formułę: = A2^3 - 0,01*A2^2 - 0,7044*A2 + 0,139104. Z wykresu wynika, że funkcja trzykrotnie przecina oś Wółka, a ponieważ wielomian trzeciego stopnia ma nie więcej niż trzy pierwiastki rzeczywiste, znaleziono graficzne rozwiązanie problemu. Innymi słowy, korzenie zostały zlokalizowane, tj. wyznacza się odstępy, w których znajdują się pierwiastki tego wielomianu: [-1, -0,8], oraz .
Teraz możesz znaleźć pierwiastki wielomianu metodą kolejnych przybliżeń za pomocą polecenia Dane → Praca z danymi → Analiza typu „co by było, gdyby” → Wybór parametrów.
Po wprowadzeniu przybliżeń początkowych i wartości funkcji można skorzystać z polecenia Dane → Praca z danymi → Analiza typu „co by było, gdyby” → Wybór parametrów i wypełnij okno dialogowe w następujący sposób.
W polu Ustaw na komórkę podany jest link do komórki, w której wpisywana jest formuła obliczająca wartość lewej strony równania (równanie należy zapisać tak, aby jego prawa strona nie zawierała zmiennej). W polu Oznaczający wpisz prawą stronę równania i w polu Zmiana wartości komórek podany jest link do komórki przydzielonej dla zmiennej. Pamiętaj, że wprowadzanie odwołań do komórek w polach okna dialogowego Dobór parametrów Wygodniej jest nie z klawiatury, ale klikając odpowiednią komórkę.
Po kliknięciu przycisku OK pojawi się okno Wynik wyboru parametru z komunikatem o pomyślnym zakończeniu poszukiwania rozwiązania, przybliżona wartość pierwiastka zostanie umieszczona w komórce A14.
W ten sam sposób znajdujemy pozostałe dwa pierwiastki. Wyniki obliczeń zostaną umieszczone w komórkach A15 i A16.
Zadanie 2. Rozwiąż równanie e X - (2x - 1) 2 = 0.
Zlokalizujmy pierwiastki równania nieliniowego.
Aby to zrobić, przedstawmy to w postaci f(x) = g(x), tj. e x = (2x - 1) 2 lub f(x) = e x , g(x) = (2x - 1) 2 i rozwiąż graficznie.
Graficznym rozwiązaniem równania f(x) = g(x) będzie punkt przecięcia prostych f(x) i g(x).
Zbudujmy wykresy f(x) i g(x). W tym celu wprowadzamy wartości argumentów do zakresu A3:A18. W komórce B3 wpisujemy wzór na obliczenie wartości funkcji f(x): = EXP(A3), a w C3 na obliczenie g(x): = (2*A3-1)^2.
Wyniki obliczeń i wykreślenie f(x) i g(x):
Z wykresu wynika, że proste f(x) i g(x) przecinają się dwukrotnie, tj. Równanie to ma dwa rozwiązania. Jedno z nich jest trywialne i można je dokładnie obliczyć:
Po drugie, możesz określić interwał izolacji roota: 1,5< x < 2.
Teraz możesz znaleźć pierwiastek równania na odcinku, stosując metodę kolejnych przybliżeń.
Wpiszmy przybliżenie początkowe w komórkę H17 = 1,5, a samo równanie w odniesieniu do przybliżenia początkowego w komórkę I17 = EXP(H17) - (2*H17-1)^2.
i wypełnij okno dialogowe Wybór parametrów.
Wynik poszukiwania rozwiązania zostanie wyświetlony w komórce H17.
Ćwiczenia3 . Rozwiąż układ równań:
Zanim zastosujemy metody opisane powyżej do rozwiązywania układów równań, znajdźmy graficzne rozwiązanie tego układu. Należy zauważyć, że oba równania układu są określone implicytnie i aby skonstruować wykresy funkcji odpowiadających tym równaniom, konieczne jest rozwiązanie podanych równań ze względu na zmienną y.
Dla pierwszego równania układu mamy:
Znajdźmy OD wynikowej funkcji:
Drugie równanie tego układu opisuje okrąg.
Fragment arkusza programu MS Excel zawierającego formuły, które należy wpisać do komórek, aby skonstruować linie opisane równaniami układu. Punkty przecięcia pokazanych linii stanowią graficzne rozwiązanie układu równań nieliniowych.
Nietrudno zauważyć, że dany układ ma dwa rozwiązania. Dlatego procedurę znalezienia rozwiązań układu należy wykonać dwukrotnie, po wcześniejszym ustaleniu odstępu izolacji korzeni wzdłuż osi Ox i Oy. W naszym przypadku pierwszy pierwiastek leży w przedziałach (-0,5;0) x i (0,5;1) y, a drugi - (0;0,5) x i (-0,5;-1) y. Dalej to zrobimy w następujący sposób. Wprowadźmy początkowe wartości zmiennych x i y, wzory reprezentujące równania układu oraz funkcję celu.
Użyjmy teraz dwukrotnie polecenia Dane → Analiza → Szukaj rozwiązań, wypełniając pojawiające się okna dialogowe.
Porównując otrzymane rozwiązanie systemu z rozwiązaniem graficznym, mamy pewność, że system został rozwiązany poprawnie.
Zadania do samodzielnego rozwiązania
Ćwiczenie 1. Znajdź pierwiastki wielomianu
Zadanie 2. Znajdź rozwiązanie równania nieliniowego.
Zadanie 3. Znajdź rozwiązanie układu równań nieliniowych.
Jak już wiesz, formuły w programie Microsoft Excel pozwalają określić wartość funkcji na podstawie jej argumentów. Może się jednak zdarzyć sytuacja, gdy znana będzie wartość funkcji i trzeba znaleźć argument (czyli rozwiązać równanie). Zaprojektowany, aby rozwiązać tego typu problemy specjalna funkcja Szukanie celu .
Wyszukaj parametr.
Specjalna funkcja Szukanie celu pozwala określić parametr (argument) funkcji, jeśli znana jest jej wartość. Podczas wybierania parametru wartość wpływającej komórki (parametru) zmienia się do momentu, aż formuła zależna od tej komórki zwróci określoną wartość.
Rozważmy procedurę wyszukiwania parametru na prosty przykład: Rozwiązać równanie 10 * x - 10 / x = 15
. Tutaj parametr (argument) jest X
. Niech to będzie komórka A3
. Wpiszmy do tej komórki dowolną liczbę leżącą w dziedzinie definicji funkcji (w naszym przykładzie liczba ta nie może być równa zeru). Wartość ta zostanie użyta jako wartość początkowa. Niech będzie 3
. Przedstawmy formułę =10*A3-10/A3
, z którego należy uzyskać wymaganą wartość, do jakiejś komórki, np. B3
. Można teraz uruchomić funkcję wyszukiwania parametrów poprzez wybranie polecenia Szukanie celu
w menu Narzędzia
. Podaj parametry wyszukiwania:
- W polu Ustaw komórkę Wprowadź odwołanie do komórki zawierającej wymaganą formułę.
- Wpisz wynik, którego szukasz w polu Cenić .
- W polu Zmieniając komórkę (Zmieniając wartość komórki) Wprowadź odwołanie do komórki zawierającej wartość do dopasowania.
- Kliknij klucz OK .
Po zakończeniu funkcji na ekranie pojawi się okno z wynikami wyszukiwania. Znaleziony parametr pojawi się w komórce, która została dla niego zarezerwowana. Zwróć uwagę, że w naszym przykładzie równanie ma dwa rozwiązania, ale został wybrany tylko jeden parametr - dzieje się tak dlatego, że parametr jest zmieniany tylko do momentu zwrócenia wymaganej wartości. Pierwszy znaleziony w ten sposób argument jest nam zwracany jako wynik wyszukiwania. Jeśli w naszym przykładzie określimy jako wartość początkową -3
, wówczas zostanie znalezione drugie rozwiązanie równania: -0,5
.
Prawidłowe określenie najbardziej odpowiedniego jest dość trudne wartość początkowa. Częściej możemy poczynić pewne założenia co do pożądanego parametru, np. parametr musi być liczbą całkowitą (wtedy otrzymamy pierwsze rozwiązanie naszego równania) lub niedodatnią (drugie rozwiązanie).
Specjalny dodatek do programu Microsoft Excel pomoże rozwiązać problem znalezienia parametru w narzuconych warunkach brzegowych Solver (Wyszukaj rozwiązanie) .
Szukaj rozwiązania.
Dodatek Microsoft Excel Solver (Wyszukaj rozwiązanie) nie jest instalowany automatycznie podczas normalnej instalacji:
- W menu Narzędzia Wybierz drużynę Dodatki . Jeśli okno dialogowe Dodatki nie zawiera polecenia Solver (Wyszukaj rozwiązanie) , naciśnij przycisk Przeglądać i określ dysk i folder zawierający plik dodatku Solver.xla (zazwyczaj jest to folder Biblioteka\Solver ) lub uruchom Instalator pakietu Microsoft Office, jeśli nie można znaleźć pliku.
- W oknie dialogowym Dodatki Sprawdź pudełko Solver (Wyszukaj rozwiązanie) .
Procedura wyszukiwania rozwiązań pozwala znaleźć optymalną wartość formuły zawartej w komórce zwanej komórką docelową. Ta procedura działa na grupie komórek powiązanych z formułą w komórce docelowej. Procedura zmienia wartości w komórkach wpływających aż do uzyskania optymalnego wyniku w oparciu o formułę zawartą w komórce docelowej. Aby zawęzić zbiór wartości, stosowane są ograniczenia, które mogą zawierać odniesienia do innych wpływających komórek. Procedurę wyszukiwania rozwiązań można również zastosować do określenia wartości komórki wpływającej, która odpowiada ekstremum komórki docelowej, na przykład liczby szkolenia, zapewniając maksymalne wyniki w nauce.
W oknie dialogowym Solver (Wyszukaj rozwiązanie)
tak samo jak w oknie dialogowym Szukanie celu
, musisz określić komórkę docelową, jej wartość oraz komórki, które należy zmodyfikować, aby osiągnąć cel. Aby rozwiązać problemy optymalizacyjne, należy określić komórkę docelową równą wartości maksymalnej lub minimalnej.
Jeśli klikniesz przycisk Zgadnij (Zgadnij) Sam Excel spróbuje znaleźć wszystkie komórki mające wpływ na formułę.
Klikając przycisk możesz dodać warunki brzegowe Dodać .
Klikając przycisk Opcje , możesz zmienić warunki poszukiwania rozwiązania: maksymalny czas poszukiwania rozwiązania, liczbę iteracji, dokładność rozwiązania, tolerancję odchyleń od optymalne rozwiązanie, metoda ekstrapolacji (liniowa lub kwadratowa), algorytm optymalizacji itp.
Wróćmy do poprzedniego przykładu: aby otrzymać drugie (niedodatnie) rozwiązanie wystarczy dodać warunek brzegowy A3 . Podobnie jak przy wyborze parametru, na ekranie pojawi się okno, w którym wyświetli się raport z wyników wyszukiwania żądanego rozwiązania. Samo rozwiązanie zostanie pokazane w przeznaczonych dla niego komórkach (w komórce A3 wartość zostanie wyświetlona -0.50 ).
Dodatek Microsoft Excel Solver (Wyszukaj rozwiązanie)
Umożliwia także rozwiązywanie układów równań czy nierówności. Rozważmy prosty przykład: spróbujmy rozwiązać układ równań
x + y = 2
x - y = 0