Symulacja w przedsiębiorstwie Generowanie liczb losowych

Symulacjawprzedsiębiorstwie
Generowanieliczblosowych
Cel
Celem laboratorium jest zapoznanie się z funkcjami generowania liczb pseudolosowych w środowisku Ms‐Excel. Funkcje te są podstawą modeli symulacyjnych tworzonych z wykorzy‐
staniem metody Monte Carlo. Kontekstbiznesowy
Twoim zadaniem jest przygotowanie mechanizmów pozwalających na generowanie liczb pseudolosowych opisujących określone zjawisko wg zadanego rozkładu. Oprogramowanie
Ms‐Excel 2007 lub Ms‐Excel 2010 Czasrealizacji
2 x 45 min. Symulacja w przedsiębiorstwie – funkcje generujące liczby pseudolosowe str. 2 FunkcjaLOS()
Składnia
LOS()
Po wprowadzeniu do komórki arkusza funkcji LOS()otrzymujemy liczbę, którą może być z jednakowym prawdopodobieństwem dowolna wartość z przedziału pomiędzy 0 a 1 0, 1). Przykład1
Załóżmy, że chcemy uzyskać liczby losowe większe lub równe 0 i mniejsze od n. Poniższa formuła realizuje to zadanie. = LOS() * n
Formuła = LOS()*30 wygeneruje więc liczby losowe z przedziału od 0 do 30 a formuła = LOS()*100 z przedziału 0, 100). Zadanie. Wygeneruj liczby z przedziałów 0, 20) oraz 0,45). Przykład2
Załóżmy, że chcemy wygenerować liczby losowe, które mają być większe od pewnej wartości m i mniejsze od innej wartości n. Możemy to zrobić za pomocą następującej formuły: = LOS() * (n - m) + m Formuła = LOS() * (200 - 100) + 100 wygeneruje liczby losowe z przedziału od 100 do 200. Uwaga! Funkcja LOS() jest funkcją „ulotną”. Oznacza to, że po każdym przeliczeniu arku‐
sza będzie ona zwracać inną liczbę. Aby do komórki wprowadzić statyczną wartość losową należy wpisać funkcję =LOS() wcisnąć klawisz funkcyjny F9 a następnie zatwierdzić obli‐
czoną wartość klawiszem ENTER. Zadanie. Wygeneruj liczby z przedziałów 5‐15, 19‐26, oraz 33‐100. Przykład3
Załóżmy, że chcemy wygenerować liczby losowe o określonej liczbie cyfr. W formule należy zastosować funkcję ZAOKR.DO.CAŁK(), która gwarantuje zwracanie liczb całkowitych. Dodając formułę z poprzedniego przykładu otrzymamy = ZAOKR.DO.CAŁK( LOS() * (n-m) + m )
Symulacja w przedsiębiorstwie – funkcje generujące liczby pseudolosowe str. 3 Aby można było generować wartości o określonej liczbie cyfr należy w miejsce n podstawić wartość 10n a w miejsce m 10n-1. Ostatecznie formuła przyjmie postać jak poniżej. = ZAOKR.DO.CAŁK( LOS() * (10n - 10n-1) + 10n-1)
Dla liczb losowych ośmiocyfrowych (10 000 000 – 99 999 999) formuła przyjmie postać = ZAOKR.DO.CAŁK(LOS() * (100000000 - 10000000) + 10000000) Zadanie. Wygeneruj liczby 5, 6 i 9 cyfrowe. Przykład4
Załóżmy, że chcemy wygenerować litery losowe. Funkcja LOS() jest przeznaczona do gene‐
rowania liczb losowych, niemniej jednak stosując pewną sztuczkę może być wykorzystana do generowania liter losowych. Załóżmy, że potrzebujemy losową literę alfabetu. W alfabecie mamy 26 liter. Należy więc najpierw wygenerować liczbę całkowitą z zakresu 1 – 26. Zrobi to poniższa formuła. = ZAOKR.DO.CAŁK(LOS() * 26 + 1)
Jeżeli litery mają być wielkie (od A do Z), to do wyniku należy dodać 64, ponieważ kody ANSI tych liter zaczynają się od 65 i kończą na 90. W końcowej operacji należy zastosować funkcję ZNAK(). = ZNAK(ZAOKR.DO.CAŁK(LOS() * 26 + 1) + 64)
Jeżeli litery losowe mają być małe wówczas dodawaną wartością powinna być 96, ponieważ takie są kody ANSI małych liter alfabetu. = ZNAK(ZAOKR.DO.CAŁK(LOS() * 26 + 1) + 96)
Zadanie. Wygeneruj czteroelementowy losowy łańcuch liter, w którym pierwsza litera jest dużą literą a pozostałe to małe litery. Wskazówka: łącząc litery skorzystaj z operatora konka‐
tenacji („&”). Przykład5
Czasami zachodzi potrzeba losowego posortowania wartości znajdujących się w arkuszu. Aby to zrobić wykonaj następujące operacje: 1. Przy założeniu, że wartości znajdują się w tabeli w wierszach zaznacz zakres w kolum‐
nie, która bezpośrednio sąsiaduje z tabelą po jej lewej lub prawej stronie. Sprawdź, czy zaznaczony zakres liczy tyle samo wierszy, co tabela. 2. Wpisz formułę = LOS() a następnie wprowadź ją, jako formułę tablicową (Na pew‐
no pamiętasz jak to się robi! ). Symulacja w przedsiębiorstwie – funkcje generujące liczby pseudolosowe str. 4 3. Ustaw opcje obliczania na Obliczanie Ręczne. 4. Zaznacz zakres zawierający dane oraz kolumnę z liczbami losowymi. 5. Posortuj tabelę wg kolumny z wartościami losowymi. Excel posortuje dane w zakresie na podstawie wartości losowych, sortując losowo całą tabe‐
lę z danymi. Zadanie. Przygotuj tabelę składającą się z 2 kolumn i kilku wierszy. Korzystając z przedsta‐
wionej procedury posortuj wiersze tabeli losowo. FunkcjaLOS.ZAKR()
Składnia
LOS.ZAKR(dół; góra)
gdzie: dół – najmniejsza losowa liczba całkowita. Excel wygeneruje liczbę, która będzie większa lub równa z argumentem dół. góra – największa losowa liczba całkowita. Excel wygeneruje liczbę, która będzie mniejsza lub równa z argumentem dół. Formuła poniżej zwraca liczbę losową całkowitą z przedziału od 0 do 59. = LOS.ZAKR(0; 59)
Zadania
1. Załóżmy, że popyt na określone dobro jest opisany rozkładem przedstawionym w ta‐
beli poniżej. Korzystając z funkcji WYSZUKAJ.PIONOWO() oraz LOS() przygotuj genera‐
tor zwracający wartość popytu zgodnie z przedstawionym rozkładem. Popyt
15
20
25
30
35
Prawdopodobieństwo
0,1
0,2
0,3
0,25
0,15
2. Opracuj analogiczny generator dla rozkładu przedstawionego poniżej. Popyt
8
9
10
11
12
13
Prawdopodobieństwo
10%
20%
30%
20%
10%
10%
Symulacja w przedsiębiorstwie – funkcje generujące liczby pseudolosowe str. 5 FunkcjaROZKŁAD.NORMALNY.ODW()
Składnia
= ROZKŁAD.NORMALNY.ODW(los(); średnia; odch_stand)
gdzie: los() – wartość losowa z przedziału (0,1) w rozkładzie jednostajnym (jednorodnym) średnia – średnia przyjęta dla generowanych liczb losowych odch_stand – odchylenie standardowe przyjęte dla generowanych liczb losowych Funkcja zwraca wartość liczby losowej o rozkładzie normalnym dla wartości o zadanej śred‐
niej i odchyleniu standardowym. Przykład
Poniższa formuła wygeneruje liczbę losową o wartości średniej 40000 oraz odchyleniu stan‐
dardowym 10000. = ROZKŁAD.NORMALNY.ODW(los(); 40000; 10000) FunkcjaPRÓG.ROZKŁAD.DWUM()
Składnia
= PRÓG.ROZKŁAD.DWUM(n; p; los())
gdzie: n – liczba prób p – prawdopodobieństwo sukcesu los() – wartość losowa z przedziału (0,1) w rozkładzie jednostajnym (jednorodnym) Funkcja symuluje liczbę sukcesów w n próbach, z których każda ma prawdopodobieństwo równe p. Zadania



Zapisz formułę symulującą liczbę celnych rzutów wolnych w 100 próbach zawodnika NBA, który ma skuteczność 90%. Zapisz formułę symulującą liczbę reszek uzyskanych w 100 rzutach monetą. Zapisz formułę symulującą liczbę konkurentów pojawiających się na rynku w ciągu roku, gdzie istnieje możliwość, że pojawi się trzech takich konkurentów i każdy z nich ma 40% szansy wejścia na rynek.