Liczby losowe są często przydatne w arkuszach kalkulacyjnych. Można na przykład wypełnić zakres liczbami losowymi, aby przetestować formuły, lub wygenerować liczby losowe w celu symulacji różnorodnych procesów. Excel udostępnia kilka sposobów generowania liczb losowych.
Korzystanie z funkcji LOS
Funkcja dostępna w Excelu SKRAJ generuje jednolitą liczbę losową z zakresu od 0 do 1. Innymi słowy, każda liczba z zakresu od 0 do 1 ma takie samo prawdopodobieństwo, że zostanie zwrócona przez tę funkcję. Jeśli potrzebujesz liczb losowych o dużych wartościach, użyj prostego wzoru na mnożenie. Na przykład poniższa formuła generuje jednolitą liczbę losową z zakresu od 0 do 1000:
=RANDA()*1000 .
Aby ograniczyć liczbę losową do liczb całkowitych, użyj funkcji OKRĄGŁY:
=OKRĄG((RANDA()*1000);0) .
Korzystanie z funkcji RANDBETWEEN
Aby wygenerować jednakowe liczby losowe pomiędzy dowolnymi dwiema liczbami, możesz użyć tej funkcji SPRAWA MIĘDZY. Na przykład poniższa formuła generuje liczbę losową z zakresu od 100 do 200:
=LOSOWE POMIĘDZY(100 200) .
W wersjach wcześniejszych niż Excel 2007 funkcja SPRAWA MIĘDZY Dostępne tylko po zainstalowaniu dodatkowego pakietu analitycznego. Aby zachować kompatybilność wsteczną (i aby uniknąć używania tego dodatku), użyj takiej formuły: A reprezentuje dno, a B- górna granica: =RAND()*(b-a)+a. Aby wygenerować liczbę losową z zakresu od 40 do 50, użyj następującej formuły: =RAND()*(50-40)+40 .
Korzystanie z dodatku Analysis ToolPack
Innym sposobem uzyskania losowych liczb w arkuszu jest użycie wtyczki Pakiet narzędzi analitycznych(który był dostarczany z programem Excel). To narzędzie może generować nierówne liczby losowe. Nie są one generowane przez formuły, więc jeśli potrzebujesz nowego zestawu liczb losowych, musisz powtórzyć procedurę.
Uzyskaj dostęp do pakietu Pakiet narzędzi analitycznych Wybierając Analiza danych Analiza danych. Jeśli brakuje tego polecenia, zainstaluj pakiet Pakiet narzędzi analitycznych za pomocą okna dialogowego Dodatki. Najłatwiej to wywołać, naciskając Atl+TI. W oknie dialogowym Analiza danych wybierać Generowanie liczb losowych i naciśnij OK. Pojawi się okno jak pokazano na rys. 130.1.
Z listy rozwijanej wybierz typ dystrybucji Dystrybucja, a następnie ustaw dodatkowe parametry (różnią się one w zależności od dystrybucji). Nie zapomnij podać parametru Interwał wyjściowy, który przechowuje liczby losowe.
Funkcjonować SKRAJ() zwraca równomiernie rozłożoną liczbę losową x, gdzie 0 £ x< 1. Вместе с тем путем несложных преобразований с помощью функции SKRAJ() możesz otrzymać dowolną losową liczbę rzeczywistą. Na przykład, aby uzyskać losową liczbę pomiędzy A I B, wystarczy ustawić następującą formułę w dowolnej komórce tabeli Excel: =RANDA()*( B-A)+A .
Należy pamiętać, że począwszy od programu Excel 2003 funkcja SKRAJ() zostało ulepszone. Obecnie implementuje algorytm Wichmana-Hilla, który przechodzi wszystkie standardowe testy na losowość i gwarantuje, że powtarzanie w kombinacji liczb losowych rozpocznie się nie wcześniej niż po 10 13 wygenerowanych liczbach.
Generator liczb losowych w STATISTICA
Aby wygenerować liczby losowe w programie STATISTICA należy dwukrotnie kliknąć nazwę zmiennej w tabeli danych (w której należy wpisać wygenerowane liczby). W oknie specyfikacji zmiennej kliknij przycisk Funkcje. W oknie, które zostanie otwarte (ryc. 1.17), musisz wybrać Matematyka i wybierz funkcję Rnd .
RND(X ) - generowanie liczb o rozkładzie jednostajnym. Ta funkcja ma tylko jeden parametr - X , który określa prawą granicę przedziału zawierającego liczby losowe. W tym przypadku 0 jest lewą krawędzią. Aby dopasować ogólną postać funkcji RND (X ) do okna specyfikacji zmiennej wystarczy dwukrotnie kliknąć nazwę funkcji w oknie Przeglądarka funkcji . Po podaniu wartości liczbowej parametru X trzeba nacisnąć OK . Program wyświetli komunikat informujący o poprawnym zapisaniu funkcji i poprosi o potwierdzenie ponownego przeliczenia wartości zmiennej. Po potwierdzeniu odpowiednia kolumna zostaje wypełniona liczbami losowymi.
Przydział do samodzielnej pracy
1. Wygeneruj serie 10, 25, 50, 100 liczb losowych.
2. Obliczać statystykę opisową
3. Konstruuj histogramy.
Jakie wnioski można wyciągnąć odnośnie rodzaju dystrybucji? Czy będzie jednolicie? Jak liczba obserwacji wpływa na ten wniosek?
Lekcja 2
Prawdopodobieństwo. Symulacja całej grupy zdarzeń
Praca laboratoryjna nr 1
Praca laboratoryjna to niezależne badanie, po którym następuje obrona.
Cele Lekcji
– Kształcenie umiejętności modelowania stochastycznego.
– Zrozumienie istoty i powiązania pojęć „prawdopodobieństwo”, „częstotliwość względna”, „statystyczna definicja prawdopodobieństwa”.
– Eksperymentalna weryfikacja właściwości prawdopodobieństwa i możliwości eksperymentalnego obliczenia prawdopodobieństwa zdarzenia losowego.
- Kształcenie umiejętności badania zjawisk o charakterze probabilistycznym.
Obserwowane przez nas zdarzenia (zjawiska) można podzielić na trzy typy: wiarygodne, niemożliwe i losowe.
Niezawodny podaj nazwę zdarzenia, które z pewnością nastąpi, jeśli zostanie spełniony określony zestaw warunków S.
Niemożliwe zdarzenie, o którym wiadomo, że nie nastąpi, jeśli zostanie spełniony zestaw warunków S.
Losowy wywołać zdarzenie, które po spełnieniu zbioru warunków S może zaistnieć lub nie wystąpić.
Przedmiot teorii prawdopodobieństwa to badanie probabilistycznych wzorców masowych jednorodnych zdarzeń losowych.
Wydarzenia nazywane są niekompatybilny, jeżeli wystąpienie jednego z nich wyklucza wystąpienie innych zdarzeń w tym samym badaniu.
Tworzy się kilka wydarzeń pełna grupa, jeżeli w wyniku badania pojawi się przynajmniej jeden z nich. Innymi słowy, wystąpienie przynajmniej jednego ze zdarzeń z całej grupy jest zdarzeniem wiarygodnym.
Wydarzenia nazywane są równie możliwe, jeśli istnieją podstawy, aby sądzić, że żadne z tych zdarzeń nie jest bardziej prawdopodobne niż inne.
Każdy z równie możliwych wyników testu nazywany jest elementarny wynik.
Klasyczna definicja prawdopodobieństwa: prawdopodobieństwo zdarzenia A nazywają to stosunkiem liczby wyników sprzyjających temu zdarzeniu do całkowitej liczby wszystkich równie możliwych, niezgodnych wyników elementarnych, które tworzą pełną grupę.
A wyznacza się ze wzoru,
Gdzie M– liczba elementarnych wyników sprzyjających zdarzeniu A, N– liczba wszystkich możliwych wyników testu elementarnego.
Jedną z wad klasycznej definicji prawdopodobieństwa jest to, że nie ma ona zastosowania do prób z nieskończoną liczbą wyników.
Definicja geometryczna prawdopodobieństwo uogólnia klasyczne prawdopodobieństwo na przypadek nieskończonej liczby elementarnych wyników i reprezentuje prawdopodobieństwo wpadnięcia punktu w obszar (odcinek, część płaszczyzny itp.).
Zatem prawdopodobieństwo zdarzenia A jest określona wzorem , gdzie jest miarą zbioru A(długość, powierzchnia, objętość); – miara przestrzeni zdarzeń elementarnych.
Częstotliwość względna, wraz z prawdopodobieństwem, należy do podstawowych pojęć teorii prawdopodobieństwa.
Względna częstotliwość zdarzenia nazwać stosunkiem liczby prób, w których wystąpiło zdarzenie, do całkowitej liczby prób faktycznie przeprowadzonych.
Zatem względna częstotliwość zdarzenia A określa się za pomocą wzoru, gdzie M– liczba wystąpień zdarzenia, N– łączna liczba testów.
Kolejną wadą klasycznej definicji prawdopodobieństwa jest to, że trudno jest wskazać powody, dla których zdarzenia elementarne są uznawane za równie możliwe. Z tego powodu, wraz z klasyczną definicją, używają również statystyczne wyznaczanie prawdopodobieństwa, przyjmując względną częstotliwość lub liczbę bliską niej jako prawdopodobieństwo zdarzenia.
1. Symulacja zdarzenia losowego z prawdopodobieństwem p.
Generowana jest liczba losowa y y≤ P, to nastąpiło zdarzenie A.
2. Symulacja całej grupy zdarzeń.
Ponumerujmy zdarzenia tworzące kompletną grupę liczbami od 1 do N(Gdzie N– liczba zdarzeń) i sporządź tabelę: w pierwszym wierszu – numer zdarzenia, w drugim – prawdopodobieństwo wystąpienia zdarzenia o podanym numerze.
| Numer wydarzenia | … | J | … | N | ||
| Prawdopodobieństwo zdarzenia | … | … |
Podzielmy odcinek na oś Oj punkty ze współrzędnymi P 1 , P 1 +P 2 , P 1 +P 2 +P 3 ,…, P 1 +P 2 +…+p.n-1 włączone N przedziały cząstkowe Δ 1 , Δ 2 ,…, Δ N. W tym przypadku długość przedziału częściowego z liczbą J równe prawdopodobieństwu pj.
Generowana jest liczba losowa y, równomiernie rozłożone na segmencie. Jeśli y należy do przedziału Δ J, następnie zdarzenie A J dotarło.
Praca laboratoryjna nr 1. Eksperymentalne obliczenie prawdopodobieństwa.
Cele pracy: modelowanie zdarzeń losowych, badanie własności statystycznego prawdopodobieństwa zdarzenia w zależności od liczby prób.
Prace laboratoryjne będziemy realizować dwuetapowo.
Scena 1. Symulacja symetrycznego rzutu monetą.
Wydarzenie A polega na utracie herbu. Prawdopodobieństwo P wydarzenia A równy 0,5.
a) Należy dowiedzieć się, jaka powinna być liczba testów N, tak że z prawdopodobieństwem 0,9 odchylenie (w wartości bezwzględnej) względnej częstotliwości pojawiania się herbu M/N od prawdopodobieństwa p = 0,5 nie przekroczyło liczby ε
> 0: 
.
Wykonaj obliczenia dla ε = 0,05 i ε = 0,01. Do obliczeń wykorzystujemy wniosek z twierdzenia całkowego Moivre’a-Laplace’a:
Gdzie 
; Q=1-P.
Jak powiązane są wartości? ε I N?
b) Wykonaj k= 10 odcinków N testy w każdym. W ilu szeregach nierówność jest spełniona, a w ilu łamana? Jaki będzie wynik jeśli k→ ∞?
Etap 2. Modelowanie implementacji wyników eksperymentu losowego.
a) Opracować algorytm modelowania realizacji eksperymentu z losowymi wynikami według poszczególnych zadań (patrz Załącznik 1).
b) Opracować program (programy) symulujący realizację wyników eksperymentu określoną skończoną liczbę razy, z obowiązkowym zachowaniem warunków początkowych eksperymentu i obliczający częstotliwość występowania interesującego nas zdarzenia.
c) Sporządzić tabelę statystyczną zależności częstotliwości występowania danego zdarzenia od liczby przeprowadzonych eksperymentów.
d) Korzystając z tabeli statystycznej, skonstruuj wykres częstotliwości zdarzenia w zależności od liczby eksperymentów.
e) Sporządzić tabelę statystyczną odchyleń wartości częstotliwości zdarzenia od prawdopodobieństwa wystąpienia tego zdarzenia.
f) Odzwierciedlaj uzyskane dane tabelaryczne na wykresach.
g) Znajdź wartość N(liczba prób), tak że i .
Wyciągnij wnioski z pracy.
Aby wybrać losowe dane z tabeli, musisz użyć funkcja w Excelu „Liczby losowe”. To jest gotowe generator liczb losowych w Excelu.
Funkcja ta jest przydatna podczas przeprowadzania kontroli wyrywkowej, przeprowadzania loterii itp.
Musimy więc zorganizować losowanie nagród dla klientów. Kolumna A zawiera wszelkie informacje o klientach - imię, nazwisko, numer itp. W kolumnie c ustawiamy funkcję liczb losowych. Wybierz komórkę B1. W zakładce „Formuły” w sekcji „Biblioteka funkcji” kliknij przycisk „Matematyczne” i wybierz z listy funkcję „RAND”. W wyświetlonym oknie nie trzeba nic wypełniać. Wystarczy kliknąć przycisk „OK”. Skopiuj formułę według kolumny. Okazało się tak.Ta formuła umieszcza liczby losowe mniejsze od zera. Aby liczby losowe były większe od zera, należy napisać następujący wzór. =RANDA()*100
Po naciśnięciu klawisza F9 liczby losowe ulegają zmianie. Za każdym razem możesz wybrać pierwszego kupującego z listy, ale zmień losowe liczby klawiszem F9.
Losowa liczba z zakresuPrzewyższać.
Aby uzyskać liczby losowe z określonego zakresu, należy we wzorach matematycznych ustawić funkcję RANDBETWEEN. Ustawmy formuły w kolumnie C. Okno dialogowe wypełnia się w ten sposób. 
Wskażmy najmniejszą i największą liczbę. Okazało się tak. 
Możesz użyć formuł, aby wybrać imiona i nazwiska klientów z listy z losowymi liczbami.
Uwaga! W tabeli w pierwszej kolumnie umieszczamy liczby losowe. Mamy taki stół.
W komórce F1 piszemy formułę, która przekaże najmniejsze liczby losowe.
=MAŁY($A$1:$A$6,E1)
Kopiujemy formułę do komórek F2 i F3 – wybieramy trzech zwycięzców.
W komórce G1 piszemy następującą formułę. Ona wybierze nazwiska zwycięzców, korzystając z losowych liczb z kolumny F. =WYSZUKAJ.PIONOWO(F1,$A$1:$B$6,2,0)
Rezultatem jest tabela zwycięzców.
Jeśli chcesz wybrać zwycięzców w kilku kategoriach, naciśnij klawisz F9, a zastąpione zostaną nie tylko liczby losowe, ale także powiązane z nimi nazwiska zwycięzców.
Jak wyłączyć aktualizację liczb losowych wPrzewyższać.
Aby zapobiec zmianie losowej liczby w komórce, należy ręcznie wpisać formułę i zamiast klawisza Enter nacisnąć klawisz F9, aby formuła została zastąpiona wartością.
W programie Excel istnieje kilka sposobów kopiowania formuł, aby zawarte w nich odwołania nie uległy zmianie. Zobacz opis prostych metod takiego kopiowania w artykule „
Mamy ciąg liczb składający się z praktycznie niezależnych elementów, które spełniają zadany rozkład. Z reguły rozkład równomierny.
Liczby losowe można generować w programie Excel na różne sposoby i metody. Rozważmy tylko najlepsze z nich.
Funkcja liczb losowych w programie Excel
- Funkcja LOS zwraca losową liczbę rzeczywistą o równomiernym rozkładzie. Będzie mniejsza niż 1, większa lub równa 0.
- Funkcja RANDBETWEEN zwraca losową liczbę całkowitą.
Przyjrzyjmy się ich zastosowaniu na przykładach.
Próbkowanie liczb losowych za pomocą RAND
Ta funkcja nie wymaga żadnych argumentów (RAND()).
Aby na przykład wygenerować losową liczbę rzeczywistą z zakresu od 1 do 5, użyj następującej formuły: =RANDA()*(5-1)+1.
Zwrócona liczba losowa jest rozłożona równomiernie w przedziale.
Za każdym razem, gdy arkusz jest obliczany lub gdy zmienia się wartość w dowolnej komórce arkusza, zwracana jest nowa liczba losowa. Jeśli chcesz zapisać wygenerowaną populację, możesz zastąpić formułę jej wartością.
- Kliknij komórkę z losową liczbą.
- Na pasku formuły wybierz formułę.
- Naciśnij klawisz F9. I WEJDŹ.
Sprawdźmy równomierność rozkładu liczb losowych z pierwszej próbki za pomocą histogramu rozkładu.
Zakres wartości pionowych to częstotliwość. Poziomo - „kieszenie”.
Funkcja RANDBETWEEN
Składnia funkcji RANDBETWEEN jest następująca (granica dolna; granica górna). Pierwszy argument musi być mniejszy niż drugi. W przeciwnym razie funkcja zgłosi błąd. Zakłada się, że granice są liczbami całkowitymi. Formuła odrzuca część ułamkową.
Przykład użycia funkcji:
Liczby losowe z dokładnością 0,1 i 0,01:
Jak zrobić generator liczb losowych w programie Excel
Stwórzmy generator liczb losowych, który generuje wartość z pewnego zakresu. Używamy formuły takiej jak: =INDEKS(A1:A10,INTEGER(RANDA()*10)+1).
Stwórzmy generator liczb losowych z zakresu od 0 do 100 w krokach co 10.
Musisz wybrać 2 losowe z listy wartości tekstowych. Korzystając z funkcji LOS, porównujemy wartości tekstowe z zakresu A1:A7 z liczbami losowymi.
Użyjmy funkcji INDEX, aby wybrać dwie losowe wartości tekstowe z oryginalnej listy.
Aby wybrać jedną losową wartość z listy, użyj następującej formuły: =INDEKS(A1:A7,RANDBETWEEN(1,COUNT(A1:A7))).
Generator liczb losowych o rozkładzie normalnym
Funkcje RAND i RANDBETWEEN generują liczby losowe o rozkładzie równomiernym. Dowolna wartość z takim samym prawdopodobieństwem może wpaść w dolną granicę żądanego zakresu i do górnej. Powoduje to ogromny spread od wartości docelowej.
Rozkład normalny oznacza, że większość wygenerowanych liczb jest bliska wartości docelowej. Dostosujmy formułę RANDBETWEEN i utwórzmy tablicę danych o rozkładzie normalnym.
Koszt produktu X wynosi 100 rubli. Cała wyprodukowana partia ma rozkład normalny. Zmienna losowa również ma normalny rozkład prawdopodobieństwa.
W takich warunkach średnia wartość zakresu wynosi 100 rubli. Wygenerujmy tablicę i zbudujmy wykres o rozkładzie normalnym z odchyleniem standardowym 1,5 rubla.
Używamy funkcji: =NORMINV(RANDA();100;1,5).
Excel obliczył, które wartości mieściły się w zakresie prawdopodobieństwa. Ponieważ prawdopodobieństwo wytworzenia produktu o koszcie 100 rubli jest maksymalne, formuła częściej niż inne pokazuje wartości bliskie 100.
Przejdźmy do narysowania wykresu. Najpierw musisz utworzyć tabelę z kategoriami. W tym celu dzielimy tablicę na okresy:
Na podstawie uzyskanych danych możemy wygenerować diagram o rozkładzie normalnym. Oś wartości to liczba zmiennych w przedziale, oś kategorii to okresy.
Dzień dobry, drogi czytelniku!
Niedawno pojawiła się potrzeba stworzenia swego rodzaju generatora liczb losowych w Excelu w granicach wymaganego zadania i było to proste, biorąc pod uwagę liczbę osób, wybrać losowego użytkownika, wszystko jest bardzo proste, a nawet banalne. Ale ciekawiło mnie, co jeszcze można zrobić za pomocą takiego generatora, czym one są, jakie są do tego ich funkcje i w jakiej formie. Pytań jest mnóstwo, dlatego będę sukcesywnie na nie odpowiadać.
Do czego dokładnie możemy wykorzystać ten mechanizm:
- Po pierwsze: do testowania formuł możemy wypełnić potrzebny zakres liczbami losowymi;
- Po drugie: generowanie pytań do różnych testów;
- Po trzecie: za dowolny losowy podział zadań z góry pomiędzy Twoich pracowników;
- po czwarte: do symulacji szerokiej gamy procesów;
…… i w wielu innych sytuacjach!
W tym artykule rozważę tylko 3 opcje stworzenia generatora (nie będę opisywał możliwości makro), a mianowicie:
Tworzenie generatora liczb losowych za pomocą funkcji LOS
Korzystając z funkcji LOS mamy możliwość wygenerowania dowolnej liczby losowej z zakresu od 0 do 1 i funkcja ta będzie wyglądać następująco:
=Losowanie();
Jeśli zajdzie potrzeba, a to najprawdopodobniej ma miejsce, użyć dużej liczby losowej, możesz po prostu pomnożyć swoją funkcję przez dowolną liczbę, na przykład 100, i otrzymasz:
=LOSOWA()*100;
Ale jeśli nie lubisz ułamków zwykłych lub po prostu potrzebujesz liczb całkowitych, użyj tej kombinacji funkcji, która pozwoli ci przestrzegać przecinka dziesiętnego lub po prostu je odrzucić:
=RUNDA((Losowa()*100);0);
=WYNIK((RANDA()*100);0)
Gdy zachodzi potrzeba użycia generatora liczb losowych w pewnym, konkretnym zakresie, według naszych warunków, np. od 1 do 6, należy zastosować następującą konstrukcję (pamiętaj o zabezpieczeniu komórek ):
=RANDA()*(b-a)+a, Gdzie,
- a – reprezentuje dolną granicę,
- b – górna granica
a pełna formuła będzie wyglądać następująco: =LOS()*(6-1)+1 i bez części ułamkowych musisz napisać: =WYNIK(RANDA()*(6-1)+1;0)
Utwórz generator liczb losowych za pomocą funkcji RANDBETWEEN
Ta funkcja jest prostsza i zaczęła nas cieszyć w podstawowej wersji Excela, po wersji 2007, co znacznie ułatwiło pracę z generatorem, gdy konieczne jest użycie zakresu. Przykładowo, aby wygenerować liczbę losową z zakresu od 20 do 50, zastosujemy następującą konstrukcję:
=LOSOWE POMIĘDZY(20,50).
Utwórz generator za pomocą dodatku AnalysisToolPack
Trzecia metoda nie wykorzystuje żadnej funkcji generowania, ale wszystko odbywa się za pomocą dodatku Pakiet narzędzi analitycznych(Ten dodatek jest dołączony do programu Excel.) Narzędzie wbudowane w edytor tabel może służyć jako narzędzie do generowania, jednak musisz wiedzieć, że jeśli chcesz zmienić zbiór liczb losowych, to musisz rozpocząć tę procedurę od nowa.
Aby uzyskać dostęp do tego niezaprzeczalnie przydatnego dodatku, należy najpierw skorzystać z okna dialogowego „Dodatki” zainstaluj ten pakiet. Jeśli masz już go zainstalowanego, sprawa jest niewielka, wybierz pozycję menu „Dane” – „Analiza” – „Analiza danych”, wybierz z listy oferowanej przez program i kliknij "OK".
W oknie, które się otworzy, wybieramy typ z menu "Dystrybucja", następnie wskazujemy dodatkowe parametry, które zmieniają się w zależności od rodzaju rozkładu. Cóż, ostatnim krokiem jest to wskazanie „Przedział wyjściowy”, dokładnie interwał, w którym będą przechowywane liczby losowe.
I to wszystko dla mnie! Naprawdę mam taką nadzieję W pełni wyjaśniłem kwestię stworzenia generatora liczb losowych i wszystko jest dla ciebie jasne. Byłbym bardzo wdzięczny za komentarze, gdyż jest to wyznacznik czytelności i inspiruje mnie do pisania nowych artykułów! Podziel się tym, co czytasz ze swoimi przyjaciółmi i polub to!
Nie myśl za dużo. W ten sposób stwarzasz problemy, których wcześniej nie było.
Fryderyk Nietzsche