Як з допомогою Excel створити генератор випадкових чисел. Генератор випадкових чисел Excel у функціях та аналізі даних

У нас є послідовність чисел, що складається з практично незалежних елементів, які підпорядковуються заданому розподілу. Як правило, рівномірний розподіл.

Згенерувати випадкові числа в Excel можна різними шляхами та способами. Розглянемо лише найкраще з них.

Функція випадкового числа в Excel

1. Функція СЛЧИС повертає випадкове рівномірно розподілене речове число. Воно буде менше 1, більше або 0.
2. Функція РОЗМІЖ повертає випадкове ціле число.

Розглянемо їх використання на прикладах.

Вибірка випадкових чисел за допомогою СЛЧИС

Ця функція аргументів не вимагає (СЛЧИС()).

Щоб згенерувати випадкове речове число в діапазоні від 1 до 5, наприклад, застосовуємо наступну формулу: =СЛЧИС()*(5-1)+1.

Поворотне випадкове число розподілено рівномірно на інтервалі.

При кожному обчисленні аркуша або зміні значення у будь-якій комірці аркуша повертається нове випадкове число. Якщо потрібно зберегти згенеровану сукупність, можна замінити формулу її значення.

1. Клацаємо по комірці з випадковим числом.
2. У рядку формул виділяємо формулу.
3. Натискаємо F9. І ВВЕДЕННЯ.

Перевіримо рівномірність розподілу випадкових чисел із першої вибірки за допомогою гістограми розподілу.

Діапазон вертикальних значень – частота. Горизонтальних – «кишені».

Функція ВИПАДМІЖ

Синтаксис функції РАЗМІЖ – (нижня межа; верхня межа). Перший аргумент має бути меншим за другий. В іншому випадку, функція видасть помилку. Передбачається, що межі – цілі числа. Дробну частину формула відкидає.

Приклад використання функції:

Випадкові числа з точністю 0,1 та 0,01:

Як зробити генератор випадкових чисел в Excel

Зробимо генератор випадкових чисел з генерацією значення певного діапазону. Використовуємо формулу виду: = ІНДЕКС (A1: A10; ЦІЛО (СЛЧИС () * 10) +1).

Зробимо генератор випадкових чисел у діапазоні від 0 до 100 з кроком 10.

Зі списку текстових значень потрібно вибрати 2 випадкові. За допомогою функції СЛЧИС можна порівняти текстові значення в діапазоні А1:А7 з випадковими числами.

Скористайтеся функцією ІНДЕКС для вибору двох випадкових текстових значень із вихідного списку.

Щоб вибрати одне випадкове значення зі списку, застосуємо таку формулу: = ІНДЕКС (A1: A7; ВИПАД МІЖ (1; РАХУНОК (A1: A7))).

Генератор випадкових чисел нормального розподілу

Функції СЛЧИС і СПРАВМІЖ видають випадкові числа з єдиним розподілом. Будь-яке значення з однаковою часткою ймовірності може потрапити в нижню межу діапазону запиту і у верхню. Виходить величезний розкид від цільового значення.

Нормальний розподіл має на увазі близьке положення більшої частини згенерованих чисел до цільового. Підкоригуємо формулу РАЗМІЖ і створимо масив даних з нормальним розподілом.

Собівартість товару Х - 100 рублів. Вся партія підкоряється нормальному розподілу. Випадкова змінна теж підпорядковується нормальному розподілу ймовірностей.

За таких умов середнє значення діапазону – 100 рублів. Згенеруємо масив і побудуємо графік з нормальним розподілом при стандартному відхиленні 1,5 рубля.

Використовуємо функцію: =НОРМОБР(СЛЧИС();100;1,5).

Програма Excel вирішила, які значення знаходяться в діапазоні можливостей. Оскільки можливість виробництва товару з собівартістю 100 рублів максимальна, формула показує значення близькі до 100 частіше, ніж інші.

Перейдемо до побудови графіка. Спочатку необхідно скласти таблицю з категоріями. Для цього розіб'ємо масив на періоди:

На основі отриманих даних зможемо сформувати діаграму із нормальним розподілом. Вісь значень – кількість змінних у проміжку, вісь категорій – періоди.

Випадкові числа часто корисні в електронних таблицях. Наприклад, ви можете заповнити діапазон випадковими числами для тестування формул або згенерувати випадкові числа для симуляції самих різних процесів. Excel надає кілька способів для створення випадкових чисел.

Використання функції СЛЧИС

Представлена ​​в Excel функція СЛЧИСгенерує рівномірне випадкове число проміжку між 0 і 1. Іншими словами, будь-яке число від 0 до 1 має рівну ймовірність бути повернутим цією функцією. Якщо вам потрібні випадкові числа з більшими значеннями, використовуйте просту формулумноження. Наступна формула, наприклад, генерує рівномірне випадкове число між 0 та 1000:
=СЛЧИС()*1000 .

Щоб обмежити випадкове число цілими числами, скористайтеся функцією ОКРУГЛ:
=ОКРУГЛ((СЛЧИС()*1000);0) .

Використання функції ВИПАД МІЖ

Для створення рівномірних випадкових чисел між будь-якими двома числами ви можете використовувати функцію ВИПАДМІЖ. Наступна формула, наприклад, генерує випадкове число між 100 і 200:
= ВИПАД МІЖ (100; 200) .

У версіях, що передують Excel 2007, функція ВИПАДМІЖдоступна лише за умови встановлення додаткового пакета аналізу. Для сумісності з попередніми версіями (і щоб уникнути використання цієї надбудови) використовуйте таку формулу, де апредставляє нижній, a b- верхня межа: =СЛЧИС()*(b-а)+а. Щоб згенерувати випадкове число між 40 і 50, використовуйте таку формулу: =СЛЧИС()*(50-40)+40 .

Використання надбудови Analysis ToolPack

Інший спосіб отримання випадкових чисел у аркуші полягає у використанні надбудови Analysis ToolPack(яка постачалася разом з Excel). Цей інструмент може генерувати випадкові нерівномірні числа. Вони генеруються не формулами, тому якщо вам потрібен новий набір випадкових чисел, необхідно перезапустити процедуру.

Отримайте доступ до пакета Analysis ToolPack, обравши Дані Аналіз Аналіз даних. Якщо ця команда відсутня, установіть пакет Analysis ToolPackза допомогою діалогового вікна Надбудови. Найпростіший спосіб викликати його – натиснути Atl+TI. У діалоговому вікні Аналіз данихВиберіть Генерація випадкових чиселі натисніть ОК. З'явиться вікно, показане на рис. 130.1.

Виберіть тип розподілу в списку, що розкривається Розподіл, а потім вкажіть додаткові параметри (вони змінюються залежно від розподілу). Не забудьте вказати параметр Вихідний інтервал, У якому зберігаються випадкові числа.

Функція СЛЧИС() повертає рівномірно розподілене випадкове число x, де 0 £ x< 1. Вместе с тем путем несложных преобразований с помощью функции СЛЧИС() можна отримати будь-яке випадкове речове число. Наприклад, щоб отримати випадкове число між aі b, достатньо задати в будь-якому осередку таблиці Excel наступну формулу: =СЛЧИС()*( b-a)+a .

Зауважимо, що починаючи з Excel 2003 функція СЛЧИС() була покращена. Тепер вона реалізує алгоритм Вічмана-Хілла, який проходить усі стандартні тести на випадковість і гарантує, що повторення в комбінації випадкових чисел почнеться не раніше, ніж через 10 13 чисел, що генеруються.

Генератор випадкових чисел у STATISTICA

Для генерації випадкових чисел STATISTICA треба двічі клацнути в таблиці даних (у якій передбачається записати згенеровані числа) на імені змінної. У вікні специфікації змінної натисніть кнопку Functions. У вікні (рис. 1.17) треба виділити Math та вибрати функцію Rnd .

RND(X ) – генерація рівномірно розподілених чисел. Ця функція має лише один параметр - X , який задає праву межу інтервалу, що містить довільні числа. При цьому 0 є лівою межею. Щоб вписати загальний виглядфункції RND (X ) у вікно специфікації змінної достатньо двічі клацнути на імені функції у вікні Function Browser . Після вказівки числового значення параметра X треба натиснути ОК . Програма видасть повідомлення про правильність написання функції та запитатиме підтвердження про перерахунок значення змінної. Після підтвердження відповідний стовпець заповнюється випадковими числами.

Завдання для самостійної роботи

1. Згенерувати ряди із 10, 25, 50, 100 випадкових чисел.

2. Обчислити описові статистики

3. Побудувати гістограми.

Які висновки можна зробити щодо виду розподілу? Чи буде воно рівномірним? Як впливає кількість спостережень на цей висновок?

Заняття 2

Імовірність. Моделювання повної групи подій

Лабораторна робота №1

Лабораторна робота є самостійним дослідженням з подальшим захистом.

Цілі заняття

– Формування навичок стохастичного моделювання.

– З'ясування сутності та зв'язку понять «імовірність», «відносна частота», «статистичне визначення ймовірності».

– Експериментальна перевірка властивостей ймовірності та можливості обчислення ймовірності випадкової події досвідченим шляхом.

- Формування навичок дослідження явищ, що мають імовірнісну природу.

Спостережені нами події (яви) можна поділити на такі три види: достовірні, неможливі і випадкові.

Достовірнимназивають подію, яка обов'язково відбудеться, якщо буде здійснено певну сукупність умов S.

Неможливимназивають подію, яка свідомо не станеться, якщо буде здійснено сукупність умов S.

Випадковимназивають подія, яка при здійсненні сукупності умов S може або відбутися або не відбутися.

Предметом теорії ймовірностейє вивчення ймовірнісних закономірностей масових однорідних випадкових подій.

Події називають несумісними, якщо поява одного з них виключає появу інших подій в тому самому випробуванні.

Декілька подій утворюють повну групу якщо в результаті випробування з'явиться хоча б одне з них. Іншими словами, поява хоча б однієї з подій повної групи є достовірною подією.

Події називають рівноможливими, якщо є підстави вважати, що жодна з цих подій не є більш можливою, ніж інші.

Кожен із рівноможливих результатів випробування називається елементарним результатом.

Класичне визначення ймовірності:ймовірністю події Аназивають відношення числа сприятливих цієї події наслідків до загального числа всіх рівноможливих несумісних елементарних наслідків, що утворюють повну групу.

Авизначається формулою ,

де m- Число елементарних результатів, сприятливих події А, n- Число всіх можливих елементарних результатів випробування.

Одним із недоліків класичного визначення ймовірності є те, що воно не застосовується до випробувань з нескінченним числом результатів.

Геометричне визначенняймовірності узагальнює класичне на випадок нескінченного числа елементарних результатів і є ймовірністю потрапляння точки в область (відрізок, частина площини і т.д.).

Таким чином, ймовірність події Авизначається формулою , де - міра множини A(Довжина, площа, обсяг); – міра простору елементарних подій.

Відносна частота поряд з ймовірністю належить до основних понять теорії ймовірностей.

Відносною частотою подіїназивають відношення числа випробувань, у яких подія з'явилося, до загального числа фактично здійснених випробувань.

Таким чином, відносна частота події Авизначається формулою , де m- Число появи події, n – загальне числовипробувань.

Ще одним недоліком класичного визначення ймовірності слід вважати те, що важко вказати підстави, що дозволяють вважати елементарні події рівноможливими. З цієї причини поряд з класичним визначеннямкористуються також статистичним визначенням ймовірностіприймаючи за ймовірність події відносну частоту або число, близьке до неї.

1. Моделювання випадкової події, що має ймовірність p.

Генерується випадкове число y y≤ p, та подія A настала.

2. Моделювання повної групи подій.

Занумеруємо події, що утворюють повну групу, числами від 1 до n(де n– кількість подій) та складемо таблицю: у першому рядку – номер події, у другому – ймовірність появи події із зазначеним номером.

Розіб'ємо відрізок на осі Ойточками з координатами p 1 , p 1 +p 2 , p 1 +p 2 +p 3 ,…, p 1 +p 2 +…+p n-1 на nчасткових інтервалів Δ 1 , Δ 2 ,…, Δ n. При цьому довжина часткового інтервалу з номером jдорівнює ймовірності p j.

Генерується випадкове число yрівномірно розподілене на відрізку. Якщо yналежить інтервалу Δ j, та подія A jнастало.

Лабораторна робота №1. Експериментальне обчислення ймовірності.

Цілі роботи:моделювання випадкових подій, вивчення властивостей статистичної ймовірностіподії залежно від кількості випробувань.

Лабораторну роботупроведемо у два етапи.

Етап 1. Моделювання підкидання симетричної монети.

Подія Aполягає у випаданні герба. Ймовірність pподії Aдорівнює 0,5.

a) Потрібно з'ясувати, якою має бути кількість випробувань n, щоб з ймовірністю 0,9 відхилення (за абсолютною величиною) відносної частоти появи герба m/nвід ймовірності p = 0,5 не перевищувало числа ε > 0: .

Розрахунки провести для ε = 0,05 та ε = 0,01. Для обчислень скористаємося наслідком з інтегральної теоремиМуавра-Лапласа:

Де ; q=1-p.

Як пов'язані між собою значення ε і n?

b) Провести k= 10 серій за nвипробувань у кожній. У скількох серіях нерівність виконана і скількох порушена? Яким буде результат, якщо k→ ∞?

Етап 2. Моделювання реалізації наслідків випадкового експерименту.

а) Розробити алгоритм моделювання реалізації досвіду з випадковими наслідками відповідно до індивідуальних завдань (див. дод. 1).

б) Розробити програму (програми) для моделювання реалізації наслідків досвіду певне кінцеве число разів, з обов'язковим збереженням початкових умов досвіду і для розрахунку частоти появи події, що цікавить.

в) Скласти статистичну таблицю залежності частоти появи заданої події від кількості проведених дослідів.

г) По статистичній таблиці побудувати графік залежності частоти події від кількості дослідів.

д) Скласти статистичну таблицю відхилень значень частоти події від ймовірності появи цієї події.

е) Відобразити отримані табличні дані на графіках.

ж) Знайти значення n(кількість випробувань), щоб і .

Зробити висновки щодо роботи.