Funkcje programu Excel są takie, że umożliwiają rozwiązywanie zadań, które można wykonać ręcznie lub zbyt trudne. Może to być pracochłonne zadanie poprzez przytłaczanie dużej liczby opcji lub obliczanie dużych liczb. Znając możliwości programu Excel, ich rozwiązywanie nie będzie trudne.

Parametr wyboru funkcji

Przykład takiego pracochłonnego i praktycznie nierozpuszczalnego zadania ręcznego jest następujący. Wiesz, jaki wynik z obliczeń musisz uzyskać, jakie obliczenia do niego prowadzą, ale nieznany punkt początkowy - wartość danych wyjściowych. Czasami można wykonywać akcje odwrotne, a czasami to zadanie może doprowadzić do ślepego zaułku. Excel oferuje metodę rozwiązania tego problemu zwanego wyborem parametru. Wywołanie funkcji znajduje się w zakładce Dane na pasku narzędzi Data. W wersjach zaczynających się od MS Excel 2007 - "Analiza" co jeśli "", pozycja menu "Wybór parametru".


W przypadku funkcji określ atrybuty: określ komórki, w których chcesz zmienić, aby osiągnąć pożądany wynik, oraz pola, w których wybrany jest wynik. Innym atrybutem funkcji jest wartość, którą chcesz uzyskać. Jak widać na zdjęciu, formuła pozwala wprowadzić tylko jeden argument dotyczący zmiany i podstawienia. To nie zawsze satysfakcjonuje użytkownika. W przypadku, gdy chcesz wybrać kilka wartości dla pożądanego wyniku, wybór parametrów "Excel" nie jest odpowiedni. W tej sytuacji używany jest specjalny dodatek, na którym jesteśmyzamieszkaj poniżej.

Obliczanie kwoty pożyczki

Jednym z najbardziej pożądanych zadań, które pomagają rozwiązać ten moduł, jest obliczenie możliwej kwoty pożyczki lub kredytu bankowego, na podstawie miesięcznych płatności, terminu zapadalności i stopy procentowej. Załóżmy, że oprocentowanie pożyczki wynosi 10%, chcemy pożyczyć pieniądze na okres jednego roku i możemy zapłacić 7 tysięcy rubli miesięcznie.


W "Excelu" 2007 istnieje odpowiednia funkcja do obliczania miesięcznych płatności od kredytu o znanej stopie procentowej i okresie. Nazywa się SPL. Składnia polecenia: PMT (stopa; kp; ps; [bs]; [typ]), gdzie:

Stawka jest odsetkami od pożyczki.

Kper - liczba płatności (w przypadku kredytu rocznego w przypadku płatności miesięcznych jest 12-krotna).

PS to kwota poczÄ…tkowa.

BS jest wartością przyszłą (jeśli zamierza się zapłacić nie całą kwotę, ale tylko jej część, to wskazuje, który dług powinien pozostać). Jest to opcjonalny argument, domyślnie 0.

Typ - kiedy dokonywana jest płatność - na początku miesiąca lub na końcu. Ta opcja nie musi być określona, ​​jeśli nie jest wypełniona, przyjmuje wartość 0, co oznacza płatność na koniec miesiąca.

Należy pamiętać, że atrybut "Oferta" określa nie roczne odsetki, ale miesięcznie, dlatego powinniśmy podzielić stopę procentową przez liczbę płatności w roku - 12. Dodamy dane z listu "Excel" 2007. Jako wstępną kwotę, przepisujemy arbitralne 100 000 rubli, a my znajdziemy prawdziwą sumę. Wywołujemy okno dialogowe wyboru parametrów. Punktem wyjścia do znalezienia jest kwotamiesięczna płatność. Formuła SPL zwraca ujemne dane, więc wprowadzamy liczbę ze znakiem minus: - 7000 rubli w polu Wartość. Powinniśmy otrzymać tę kwotę w komórce z płatnością, zmieniając informacje w polu z pożyczką.
Przepisamy to wszystko w oknie i rozpoczniemy wybór parametru "Excel". W rezultacie funkcja obliczyła, na jaką pożyczkę możemy sobie pozwolić - 7962156 rubli.

Określenie stopy procentowej

Rozważmy teraz problem odwrotny. Bank wydaje pożyczkę w wysokości 100 tysięcy rubli na 2 lata i chce otrzymać dochód w wysokości 10 tysięcy rubli. Jaka jest minimalna stopa procentowa, którą należy ustalić dla zysku? Do istniejącego arkusza informacyjnego dodajemy wiersz "Zysk". Oblicza się ją według wzoru: Zysk = | Płatność | * Terminowo-terminowy Ustawiamy termin 24 miesiące. Zwróć uwagę na pole "Bet". Wartość liczbowa powinna być wyrażona w procentach. Aby to zrobić, wybierz format liczbowy "Procent" w programie Excel: karta Strona główna - pasek narzędzi Liczba - przycisk procentowy. Wzywamy funkcję selekcji i przedstawiamy jej argumenty. Oczekiwany wynik zostanie zapisany w polu "Zysk", zmieniając wartość w komórce "Stawka" i wynoszącej 10.000 rubli. Po uruchomieniu program pokazuje wymagany procent równy 932354423334073%.

Wybór kilku parametrów do znalezienia optymalnego wyniku

Jak już wspomniano powyżej, funkcja wyboru parametru w Excelu nie zawsze spełnia wymagania zadania. Jeśli chcesz odebrać kilka argumentów, dodatek jest używany jako "rozwiązanie wyszukiwania". Od niejpomóc uzyskać optymalny wynik, wybierając kilka parametrów.
Upewnij się, że jest dostępny do użycia: zakładka Dane, pasek narzędzi Analiza. Jeśli nie ma takiego panelu w programie lub jeśli nie ma on wymaganego polecenia, aktywuj je. Przejdź do ustawień programu Excel (menu Plik przycisku Microsoft Office Excel 2007 w wersjach 2010 i nowszych) i znajdź element dodatków. Przejdź do zarządzania dodatkami i zaznacz pole wyboru "Znajdź rozwiązanie". Teraz funkcja jest aktywowana.

Zadanie transportowe

Klasyczne zastosowanie metody - rozwiązanie problemu transportowego. Na przykład firma przechowuje produkty w kilku magazynach i dostarcza je do kilku sklepów. Pojawia się pytanie, z którego magazynów dostarczać produkty do sklepów, aby koszty transportu były minimalne. Wprowadź dane. W tym celu zbudujemy tabele z informacjami o kosztach wysyłki, ilości towarów w każdym z magazynów i wymaganej ilości towarów dla każdego punktu sprzedaży. Wybierzemy następujące wartości dostaw, aby spełnić warunki: 1) Pełne koszty były minimalne. 2) Całkowita dostawa towarów w punktach sprzedaży spełniała wymagania. 3) Całkowity wywóz produktów z magazynów nie przekroczył dostępnych zapasów. 4) Liczba jednostek produkcji musi być integralna i niezbywalna. Wynik znalezienia rozwiązania.

Inne sposoby analizy danych

Oprócz opcji wymienionych powyżej istnieją inne metody analizy danych. Znajdują się one w punkcie menu "Analiza", jeśli "". To jest "Dispatcher".Skrypty "i" Tabela danych ".

Menedżer skryptów może, przy pomocy wyboru wartości w zakresie komórek, obliczyć możliwe warianty rozwoju wydarzeń. Wraz z nim realizowana jest prognoza możliwych wyników bieżącego procesu, na przykład wyniku pracy firmy za ten okres. Po wprowadzeniu różnych opcji, podsumowanie pokazuje raport scenariusza, który umożliwia analizę parametrów. Podobną funkcję pełni "Tabela danych", z tą różnicą, że wszystkie sumy nie są wyświetlane w osobnym raporcie, ale są zawarte na tym samym arkuszu. Ułatwia to analizę. Ale tabele obsługują tylko dwa parametry, w związku z tym w przypadku większej liczby danych wejściowych, które używają skryptów. Powyżej szczegółowo opisujemy funkcję "Excel" "Wybór parametru" i praktyczne przykłady jej użycia. Znając tę ​​możliwość, możesz łatwo rozwiązać problemy za jej pomocą.