Czy Excel może generować liczby losowe?

Czy wiesz, że możesz generować liczby, a nawet listy liczb w ciągu milisekund za pomocą Excela?

W rzeczywistości jest to dość proste, aby zrobić eksperymenty, rodzaj hipotetycznych obliczeń ze scenariuszami za pomocą Excela dzięki dwóm funkcjom Excela dającym liczby losowe:

=R A N D ( )

oraz

=RANDBETWEEN ( dół, góra )

Generowanie liczb w Excelu

Powiedzmy, że musisz przypisać losowe numery do grupy ludzi. Może to być spowodowane tym, że zamierzasz nadać im symboliczne numery w wyścigu. LUB

• Losowo wybrać pewną liczbę osób z listy mailingowej,
• Wybierz kilku pracowników z listy, aby przeprowadzić na nich losowe badanie, ponieważ nie możesz tego zrobić na całym badanym świecie,
• Dobierz dwie różne klasy w szkole, aby rywalizowały w konkursie wiedzy.
• dobrać w pary kluby piłkarskie, aby rozegrały ze sobą mecz, wybierając losowo spośród klubów o tym samym poziomie.

We wszystkich tych przykładach, możesz przypisać losowe liczby do odpowiednich elementów/grup, a następnie tworzyć scenariusze What-If i inne obliczenia, aby zorganizować związek przyczynowo-skutkowy między nimi.

Zacznijmy od funkcji RAND:

Funkcja RAND daje nam równomiernie rozłożone liczby losowe (technicznie liczby pseudolosowe), które są większe lub równe 0 i mniejsze od 1.

Składnia funkcji RAND to:
=RAND ( )

Na przykład, jeśli spojrzysz na 1. przykładowy arkusz, zobaczysz liczbę losową w komórce A2 wygenerowaną za pomocą funkcji RAND.

Zauważ, że za każdym razem, gdy wpiszesz coś innego do dowolnej komórki (może to być liczba, litera lub symbol, cokolwiek), usuniesz wartość z innej komórki, odświeżysz stronę za pomocą klawisza F9 lub naciśniesz enter, gdy mysz znajduje się w komórce A2, wartość w komórce A2 zmieni się automatycznie. Dzieje się tak dlatego, że komórki, w których działają funkcje RAND są dynamiczne, więc arkusz jest przeliczany i za każdym razem podawana jest nowa liczba losowa. Może to być jednak irytujące, a także może przeszkadzać w pracy, jeśli chcesz mieć stabilne liczby do obliczeń. Istnieje więc możliwość wyłączenia automatycznego przeliczania. Proszę postępować zgodnie z poniższymi wskazówkami:

Plik > Opcje > Formuły i zmienić opcję Obliczenia skoroszytowe na Ręczne

(Dotyczy to również funkcji RANDBETWEEN)

Należy pamiętać, że należy być bardzo ostrożnym przy wyłączaniu tej automatyzacji, ponieważ w konsekwencji może to zaszkodzić innym formułom.

Możesz również pozbyć się automatycznego przeliczania w tabeli w inny sposób. Możesz skopiować kolumnę, w której znajdują się liczby losowe, które właśnie wygenerowałeś za pomocą funkcji RAND, kliknąć prawym przyciskiem myszy, wybrać Wklej Specjalnie, a następnie Wartości. W ten sposób, ponieważ nie będziesz miał już formuły, liczby nie zmienią się automatycznie.

(Jest to również ważne dla funkcji RANDBETWEEN)

Jeśli chcesz nie tylko pojedynczą liczbę losową, ale listę liczb losowych, możesz utworzyć jedną liczbę losową za pomocą funkcji RAND w jednej komórce, a następnie użyć uchwytu wypełnienia, aby przeciągnąć komórkę w dół. Proszę sprawdzić 2. przykładowy arkusz.

(Jest to również ważne dla funkcji RANDBETWEEN)

Jeśli chcesz uzyskać listę liczb losowych w jednej formule, zaznaczenie komórek w kolumnie i napisanie formuły nie wystarczy, trudno. Najpierw należy zaznaczyć komórki, w których ma się pojawić lista, a następnie wpisać formułę =RAND() , po czym kliknąć CTRL + Enter zamiast zwykłego ENTER.

(Dotyczy to również funkcji RANDBETWEEN)

Aby uzyskać więcej informacji na temat funkcji RAND, możesz odwiedzić nasz wpis na blogu.

Jeśli zdecydujesz, że potrzebujesz liczb losowych, ale nie w zakresie od 0 do 1, ale w dowolnym innym zakresie, możesz użyć funkcji RANDBETWEEN. Tak więc, funkcja RANDBETWEEN daje jeszcze więcej opcji niż funkcja RAND podczas generowania liczb losowych!

Składnia funkcji RAND to:
=RANDBETWEEN (dół, góra)

Dół: Najmniejsza liczba, którą funkcja może podać

Góra: Najwyższa liczba, którą funkcja może podać

Zarówno dolna jak i górna liczba może pojawić się na liście.

Proszę zauważyć, że funkcja RAND daje nam liczbę dziesiętną (ponieważ musi być pomiędzy 0 a 1), podczas gdy RANDBETWEEN daje nam liczbę całkowitą. Proszę sprawdzić 3. przykładowy arkusz, aby zobaczyć formułę w szczegółach. Załóżmy, że chcesz otrzymać losową liczbę z przedziału od 1 do 100:

Jednakże, jeśli napiszesz tę samą formułę i nie klikniesz ENTER, ale zamiast tego klikniesz przycisk F9, zobaczysz, że formuła zamienia się w stabilną liczbę i nie zmienia się już. Tak więc, jest to skrót, aby zrobić kopiuj-wklej specjalne do wartości. Proszę zobaczyć przykładowy 4. arkusz i spróbować samemu:

Weźmy przykład

Masz grupę pracowników i chcesz wylosować wśród 5 z nich, kto był najlepszy w sprzedaży, a następnie dasz nagrodę zwycięzcy losowania.

To jest właśnie ta grupa:

Jeśli posortujesz ich wyniki sprzedaży od największej do najmniejszej poprzez Dane -> Filtr -> Sortuj od największej do najmniejszej, otrzymasz;

Wybierzmy losowo kogoś spośród 5 osób, które mają najwyższą sprzedaż, używając funkcji MIN i MAX Excela, a także RANDBETWEEN (proszę spojrzeć na przykładowy arkusz 5, aby zrozumieć szczegółowo):

Formuła wybrała Mike'a jako zwycięzcę, dając nam pracownika numer 2.

Proszę zauważyć, że umieściliśmy inną formułę o nazwie VLOOKUP w komórce F i G, aby pobrać odpowiednie nazwisko pracownika i wynik sprzedaży:

Jeśli chcesz dowiedzieć się więcej o tym, jak korzystać z funkcji VLOOKUP, sprawdź ten przewodnik.

Ważna uwaga: Jeśli zakres używany w funkcji RANDBETWEEN jest większy niż zakres na liście, możesz otrzymać zduplikowane liczby lub błędy. Na przykład, powiedzmy, że w ostatnim ćwiczeniu wybrałeś zakres funkcji RANDBETWEEN od 1 do 10, ale na Twojej liście nie ma pracowników, których numery to 8, 9 lub 10. Dlatego też, gdy przeciągniesz formułę w dół komórek, a formuła da liczbę 9, otrzymasz błędy w funkcji VLOOKUP.

Aby uzyskać więcej wyjaśnień na temat funkcji RANDBETWEEN, możesz odwiedzić nasz wpis na blogu.

Przeprowadzanie symulacji w Excelu

Symulacje są używane do imitowania sytuacji, która ma kilka zmiennych i pewne scenariusze. Najczęściej model matematyczny jest niezbędny do naśladowania systemu, sytuacji lub procesu. W ten sposób staje się widoczne, jak działa system, która zmienna wpływa na to, jak i oczywiście wyniki poszczególnych działań.

W życiu biznesowym można wykorzystać symulacje do rozwiązywania różnych problemów, np. do podjęcia decyzji, który sprzedawca powinien wziąć ile premii w zależności od jego sprzedaży i jak wysokość przyznanej premii wpływa na finanse firmy lub można obliczyć ilość składników produktu, który będziemy produkować i jak ilość dodanych składników wpływa na koszt sprzedanych towarów, a w konsekwencji ile zysku osiągniemy zgodnie z różnymi prognozami sprzedaży. Możesz również obliczyć nasze ryzyko finansowe w zależności od wielkości produkcji i sprzedaży poprzez symulacje tworząc formułę ze zmiennymi sytuacji i naśladować prawdopodobieństwa przyszłości.

Tworzenie danych wejściowych

Aby stworzyć symulację w Excelu należy zacząć od stworzenia odpowiednich danych wejściowych, tak aby przy każdym uruchomieniu symulacji otrzymać nowe zmienne losowe, które będą wykorzystane jako dane wejściowe do modelu obliczeniowego. Jeśli przeprowadzimy symulacje wystarczającą ilość razy, obliczenia staną się bardziej znaczące, ponieważ losowość pomiędzy wynikami staje się mniejsza. Jak już wiesz, do generowania zmiennych losowych używamy funkcji RAND lub RANDBETWEEN Excela.

Wiemy, że możemy tworzyć liczby losowe za pomocą tych dwóch funkcji i możemy je odnowić po prostu naciskając klawisz F9, jednak, aby utworzyć rzeczywisty scenariusz, musimy wygenerować liczby zgodnie z rozkładem prawdopodobieństwa. Tak więc, system oblicza inny wynik, który prawdopodobnie pojawi się w zależności od zmiennych i danych wejściowych.

Excel posiada funkcje do łatwego obliczania rozkładów prawdopodobieństwa. Możesz znaleźć ich listę tutaj:

• Normal: DIST, INV
• Standardowy normalny: DIST, NORM.S.INV
• Rozkład t: DIST, INV
• Rozkład F: DIST, INV
• Chi-kwadrat: DIST, INV
• Lognormalna: DIST, INV
• Dwumianowy: DIST, INV
• Hipergeometryczna: DIST
• Beta: DIST, INV
• Gamma: DIST, GAMMA.INV
• Wykładniczy: DIST
• Weibull: DIST
• Poisson: DIST
• Ujemna dwumianowa: DIST

Jednym z najbardziej popularnych rozkładów jest rozkład normalny. Można do tego celu użyć funkcji NORM.INV. Składnia funkcji to:

= NORM.INV(probability,mean,standard_dev)

Nie zapominajmy, że aby randomizować wyniki, do funkcji NORM dołączamy funkcję RAND.

Na przykład:
= NORM.INV(RAND(),mean,standard_dev)

Średnia i odchylenie standardowe powinny mieć znaczenie zgodne z naszymi danymi wejściowymi. Na przykład, jeśli obliczamy koszt nowego towaru i uwzględniamy dane wejściowe takie jak wielkość produkcji czy ilość zużytych surowców, musimy podstawić naszą średnią i odchylenie standardowe do tych z podobnego produktu wyprodukowanego np. w poprzednim roku.

Dla przypomnienia, składnia funkcji Mean to :

=AVERAGE(liczby)

składnia funkcji Odchylenie standardowe to:

• S(liczby)
• P(liczby)
• STDDEVA(liczby)
• STDDEVPA(liczby)

Sprawdź nasz Symulator Loterii i zobacz inne funkcje szablonów Excela. Teraz dzięki Excelowi łatwiej jest prowadzić bezpieczne loterie, a dzięki naszym szablonom możesz wygodnie przygotowywać loterie.

Symulacja Monte Carlo w Excelu:

John von Neumann i Stanisław Ulam ukuli termin Monte Carlo Simulation w latach 40-tych XX wieku nawiązując do Monte Carlo (w języku Monako), gdzie znajduje się miejsce popularne jako punkt hazardu europejskich elit.

Symulacja Monte Carlo jest metodą obliczania ogromnej ilości losowań i rozwiązywania bardzo złożonych problemów. Ekonomia, finanse, fizyka, chemia, inżynieria i łańcuch dostaw to niektóre dziedziny, w których symulacja Monte Carlo jest szeroko wykorzystywana. Jak można zauważyć, wszystkie te dziedziny mogą mieć przypadki, które mają różne zmienne do rozważenia.

Na przykład, w biznesie, metody losowe i probabilistyczne mogą być również wykorzystywane do badania złożonych opcji lub oceny ryzyka, że firma nie spłaci swoich długów.

Czasami mamy złożony problem i nie da się go rozwiązać za pomocą bezpośrednich obliczeń. Tworzymy matematyczną metodę obliczeniową, która zawiera szereg iteracji, które prowadzą do symulacji rozkładu normalnego. Szczególnie systemy obliczania ryzyka i modele prognostyczne często wykorzystują tę metodę.

Aby stworzyć symulację Monte Carlo, musimy wziąć kluczowe zmienne problemu i utworzyć rozkład prawdopodobieństwa po osiągnięciu pewnej liczby próbek. Jako przykład, zobaczmy model gry w kości w 6 fazach.

Załóżmy, że rzucamy 3 kostkami przez 3 razy i wszystkie kostki mają 6 stron.

Powiedzmy, że zasady są następujące:

• Jeśli suma kostek wynosi: 7 lub 11; gracz wygrywa grę.
• Jeśli suma kostek wynosi: 3, 4, 5, 16, 17 lub 18; gracz przegrywa grę.
• Jeśli suma kostek jest jakakolwiek inna niż te: gracz dostaje jedno prawo do ponownego rzucenia kostkami.

(Ważne: Nie zapominaj, że potrzebujemy 5.000 wyników, aby utworzyć symulację Monte Carlo. Ponadto, tabela z danymi jest bardzo przydatna podczas generowania wyników).

Faza 1: Rzucanie kostką

Pierwszą rzeczą, którą musimy zrobić jest utworzenie zakresu danych, powiedzmy dla 50 rzutów. Musimy użyć funkcji RANDBETWEEN (1,6), aby otrzymać nowe losowe wyniki za każdym razem, gdy wciśniemy F9 lub odświeżymy stronę.

Musimy obliczyć sumę całkowitych wyników jako "wynik" w innej komórce.

Etap 2: Zakres wyników

Aby opracować możliwe wyniki, musimy utworzyć zakres danych. Tak więc, rozwijamy zakres danych 3 kolumny. W pierwszej kolumnie, mamy numery od 3 do 18 reprezentujących sumę wszystkich kostek z toczenia ich 3 razy.

3 jest minimalną liczbą, ponieważ; 1 + 1 + 1 = 3

18 jest liczbą maksymalną, ponieważ: 6 + 6 + 6 = 18

Dla komórek 1 i 2, powinniśmy otrzymać wyniki jako N/A, ponieważ uzyskanie 1 lub 2 jest niemożliwe w tych obliczeniach.

W drugiej kolumnie powinniśmy zobaczyć możliwe wyniki po pierwszej rundzie jako Wygrana, Przegrana lub Ponowny przewrót w zależności od wyniku z pierwszej kolumny (suma wszystkich kostek).

W trzeciej kolumnie powinniśmy znaleźć możliwe wyniki po kolejnych rundach. Aby to osiągnąć, możemy użyć funkcji IF Excela. Zapewnia to ostateczny wynik, jak wygrana lub ponowne przewrócenie itp.

Wygrana lub przegrana są ostatecznymi wynikami. Musimy turlać jeszcze raz i jeszcze raz, aż uzyskamy te wyniki.

Faza 3: Wnioski

Teraz, powinniśmy znaleźć wyniki 50 rolek. Aby to osiągnąć, możemy użyć funkcji INDEX. Dzięki niej wyniki zostaną wyrównane względem siebie, np. jeśli wyrzuciliśmy 8, musimy wyrzucić jeszcze raz.

Możemy wykorzystać funkcję OR Excela oraz funkcję JEŻELI wraz z funkcją INDEX do stworzenia sytuacji warunkowej. Na przykład, jeśli wynikiem poprzedniej rundy jest Wygrana lub Przegrana, system powinien zatrzymać toczenie.

Faza 4: Liczba rzutów kostką

Aby upewnić się, że Excel wymaga ponownego przewrócenia kości i dodaje liczbę z dodatkowej rundy, możemy skorzystać z funkcji COUNTIF. W ten sposób możemy obliczyć liczbę rzutów kostką, która jest niezbędna do wyciągnięcia wniosku o wygranej lub przegranej.

Faza 5: Symulacja

Teraz obliczmy wyniki różnych symulacji. Pamiętaj, że potrzebujesz 5000 symulacji dla symulacji Monte Carlo.

Potrzebujemy 3 kolumn. W pierwszej z nich mamy liczbę 5000. W drugiej kolumnie, potrzebujemy wyników po 50 rolkach. Wreszcie, w trzeciej kolumnie, potrzebujemy liczbę rzutów kostką przed uzyskaniem ostatecznego wyniku Wygrana lub Przegrana.

Tak więc teraz musimy stworzyć tabelę analizy wrażliwości. Możemy ją wygenerować za pomocą tabeli funkcji lub tabeli danych. W tej tabeli analizy wrażliwości, będziemy umieszczać liczby zdarzeń od 1 do 5000. Wyniki te możemy umieścić w dowolnych komórkach, które mają wystarczająco dużo pustych następnych komórek. Robimy to tak, aby nie uszkodzić żadnych formuł w innych komórkach.

Etap 6: Prawdopodobieństwo

Teraz zasłużyliśmy na uzyskanie ostatecznych wyników! Jest to faza obliczania wyników wygranej lub przegranej. Używamy funkcji COUNTIF w Excelu, aby upewnić się, że formuła zlicza liczbę wygranych lub przegranych, a następnie dzieli ją przez liczbę zdarzeń, która wynosi 5000. Stąd, otrzymujemy wynik, że prawdopodobieństwo uzyskania wyniku Win jest 73,2% i prawdopodobieństwo uzyskania wyniku Lose jest 26,8%.

Rozdział 3: Generator liczb losowych Szablon Excela:

Jak być może już wiesz, Excel posiada funkcje statystyczne dla rozkładów prawdopodobieństwa. Funkcje te mogą być używane w połączeniu z funkcją RAND; takie jak S.DIST , T.INV lub GAMMA.IMV itp.

Jeśli wolisz korzystać z naszych szablonów Generatora Liczb Losowych, możesz generować liczby zgodnie z typem dystrybucji, który wybrałeś w przygotowanym arkuszu. Możesz łatwo stworzyć listę zgodnie z cechami, które chcesz, aby mieć, a także można sortować lub randomizować go wiele razy, jak chcesz, można również tasować go z znaków, które chcesz, takich jak litery, liczby, słowa itp. Bardzo prosty, łatwy i całkiem przydatny system!