Доброго часу доби, шановний, читачу!
Нещодавно виникла необхідність створити своєрідний генератор випадкових чисел в Excel у межах потрібного завдання, а вона була проста, з урахуванням кількості людей вибрати випадкового користувача, все дуже просто і навіть банально. Але мене зацікавило, а що ще можна робити за допомогою такого генератора, які вони бувають, які їх функції для цього використовуються і в якому вигляді. Питанням багато, тому поступово буду і відповідати на них.
Отже, навіщо ми можемо використовувати цьому механізм:
- по-перше: ми можемо для тестування формул заповнити потрібний нам діапазон випадковими числами;
- по-друге: на формування питань різних тестів;
- по-третє: для будь-якого випадкового розподілу заздалегідь завдань між вашими співробітниками;
- по-четверте: для симуляції найрізноманітніших процесів;
…… та й у багатьох інших ситуаціях!
У цій статті я розгляну тільки 3 варіанти створення генератора (можливості макросу, я не описуватиму), а саме:
Створюємо генератор випадкових чисел за допомогою функції СЛЧИС
За допомогою функції СЛЧИС, ми маємо можливість генерувати будь-яке випадкове число в діапазоні від 0 до 1 і ця функція буде виглядати так:
=СЛЧИС();
Якщо виникає необхідність, а вона, швидше за все, виникає, використовувати випадкове число великого значення, ви можете помножити вашу функцію на будь-яке число, наприклад 100, і отримаєте:
=СЛЧИС()*100;
А ось якщо вам не подобаються дробові числа або просто потрібно використовувати цілі числа, тоді використовуйте таку комбінацію функцій, це дозволить вам після коми або просто відкинути їх:
=ОКРУГ((СЛЧИС()*100);0);
= ВІДБР ((СЛЧИС () * 100); 0)
Коли виникає необхідність використовувати генератор випадкових чисел у якомусь певному, конкретному діапазоні, згідно з нашими умовами, наприклад, від 1 до 6 треба використовувати наступну конструкцію (обов'язково закріпіть комірки за допомогою ):
=СЛЧИС()*(b-а)+а, де,
- a – представляє нижню межу,
- b – верхня межа
і повна формула виглядатиме: =СЛЧИС()*(6-1)+1, а без дробових частин вам потрібно написати: =ВІДБР(СЛЧИС()*(6-1)+1;0)
Створюємо генератор випадкових чисел за допомогою функції РАЗМІЖ
Ця функція більш проста і почала нас радувати в базовій комплектації Excel після 2007 версії, що значно полегшило роботу з генератором, коли необхідно використовувати діапазон. Наприклад, для створення випадкового числа в діапазоні від 20 до 50 ми будемо використовувати конструкцію наступного виду:
= ВИПАД МІЖ (20; 50).
Створюємо генератор за допомогою надбудови AnalysisToolPack
У третьому способі не використовується жодна функція генерації, а все робиться за допомогою надбудови AnalysisToolPack(Ця надбудова входить до складу Excel). Вбудований у табличному редакторі інструмент можна використовувати як інструмент генерації, але потрібно знати, якщо ви хочете змінити набір випадкових чисел, то вам потрібно цю процедуру перезапустити.
Для отримання доступу до цієї, безперечно, корисної надбудови потрібно, для початку, за допомогою діалогового вікна «Надбудови»встановити цей пакет. Якщо у вас його вже встановлено, то справа за малим, вибираєте пункт меню "Дані" - "Аналіз" - "Аналіз даних", вибираєте в запропонованому програмою списку і тиснемо "ОК".
У вікні ми вибираємо тип у меню «Розподіл», після вказуємо додаткові параметри, що змінюються, виходячи з типу розподілу. Ну і фінальний крок, це вказівка «Вихідний інтервал», саме той інтервал де зберігатимуться, ваші випадкові числа.
А на цьому все в мене! Я дуже сподіваюся, щопитання щодо створення генератора випадкових чисел я розкрив повністю і вам все зрозуміло. Буду дуже вдячний за залишені коментарі, оскільки це показник читання та надихає на написання нових статей! Діліться з друзями прочитаним та ставте лайк!
Чи не додумуй занадто багато. Так ти створюєш проблеми, яких спочатку не було.
Фрідріх Ніцше
У Excel є функція знаходження випадкових чисел = СЛЧИС (). Можливість знайти випадкове число в Excel, важлива складова планування чи аналізу, т.к. ви можете спрогнозувати результати вашої моделі на велику кількість даних або просто знайти одне рандомне число для перевірки своєї формули або досвіду.
Найчастіше ця функція застосовується отримання великої кількості випадкових чисел. Тобто. 2-3 числа завжди можна придумати самому, для великої кількості найпростіше застосувати функцію. У більшості мов програмування подібна функція відома як Random (від англ. Випадковий), тому часто можна зустріти вирус «в рандомному порядку» і т.п. В англійському Excel функція СЛЧИС числиться як RAND
Почнемо з опису функції =СЛЧИС(). Для цієї функції не потрібні аргументи.
А працює вона в такий спосіб — виводить випадкове число від 0 до 1. Число буде речове, тобто. за великим рахунком будь-яке, як правило, це десяткові дроби, наприклад 0,0006.
При кожному збереженні число змінюватиметься, щоб оновити число без оновлення натисніть F9.
Випадкове число у певному діапазоні. Функція
Що робити, якщо вам не підходить наявний діапазон випадкових чисел, і потрібно набір випадкових чисел від 20 до 135. Як це можна зробити?
Потрібно записати таку формулу.
СЛЧИС()*115+20
Тобто. до 20 буде випадково додаватися число від 0 до 115, що дозволить отримувати щоразу число в потрібному діапазоні (див. першу картинку).
До речі, якщо вам необхідно знайти ціле число в такому самому діапазоні, для цього існує спеціальна функція, де ми вказуємо верхню та нижню межу значень
ВИПАДМІЖ(20;135)
Просто, але дуже зручно!
Якщо потрібно безліч осередків випадкових чисел просто протягніть осередок нижче.
Випадкове число з певним кроком
Якщо нам потрібно отримати рандомне число з кроком, наприклад п'ять, ми скористаємося однією з . Це буде ОКРВВЕРХ()
ОКРВВЕРХ(СЛЧИС()*50;5)
Де ми знаходимо випадкове число від 0 до 50, а потім округляємо його до найближчого зверху кратного значення 5. Зручно, коли ви робите розрахунок для комплектів по 5 штук.
Як використовувати рандом для перевірки моделі?
Перевірити вигадану модель можна за допомогою великої кількості випадкових чисел. Наприклад, перевірити чи буде прибутковим бізнес-план
Вирішено винести цю тему на окрему статтю. Слідкуйте за оновленнями, буде цього тижня.
Випадкове число у VBA
Якщо вам необхідно записати макрос і ви не знаєте як це зробити, то можна прочитати .
У VBA застосовується функція Rnd(), при цьому вона не працюватиме без включення команди Randomizeдля запуску генератора випадкових чисел Розрахуємо довільне число від 20 до 135 макросом.
Sub MacroRand() Randomize Range("A24") = Rnd * 115 + 20 End Sub
Вставте цей код у редактор VBA (Alt + F11)
Як і завжди, прикладаю приклад* З усіма варіантами розрахунку.
Напишіть коментарі, якщо у вас є питання!
Поділіться нашою статтею у ваших соцмережах:Функція СЛЧИС() повертає рівномірно розподілене випадкове число 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– кількість подій) та складемо таблицю: у першому рядку – номер події, у другому – ймовірність появи події із зазначеним номером.
| Номер події | … | j | … | 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(кількість випробувань), щоб і .
Зробити висновки щодо роботи.
Щоб вибрати з таблиці випадкові дані, потрібно скористатися функцією в Excel «Випадкові числа». Це готовий генератор випадкових чисел у Excel.
Ця функція стане в нагоді під час проведення вибіркової перевірки або під час проведення лотереї, тощо.
Отже, нам потрібно здійснити розіграш призів для покупців. У стовпці А стоїть будь-яка інформація про покупців – ім'я або прізвище, або номер, тощо. У стовпці встановлюємо функцію випадкових чисел. Виділяємо комірку В1. На закладці «Формули» у розділі «Бібліотека функцій» натискаємо на кнопку «Математичні» та вибираємо зі списку функцію «СЛЧИС». Заповнювати у вікні нічого не потрібно. Просто натискаємо кнопку «ОК». Копіюємо формулу по стовпцю. Вийшло так.Ця формула ставить випадкові числа менше від нуля. Щоб випадкові числа були більшими за нуль, потрібно написати таку формулу. =СЛЧИС()*100
При натисканні клавіші F9 відбувається зміна випадкових чисел. Можна вибирати щоразу зі списку першого покупця, але міняти випадкові числа F9.
Випадкове число з діапазонуExcel.
Щоб одержати випадкові числа у певному діапазоні, встановимо функцію «СПРАВМІЖ» у математичних формулах. Встановимо формули у стовпці С. Діалогове вікно заповнили так. 
Вкажемо найменше та найбільше число. Вийшло так. 
Можна вибрати формулами зі списку з випадковими числами імена, прізвища покупців.
Увага!У таблиці випадкові числа розташовуємо у першому стовпці. В нас така таблиця.
У осередку F1 пишемо таку формулу, яка перенесе найменші випадкові числа.
=Найменший($A$1:$A$6;E1)
Копіюємо формулу на комірки F2 та F3 – ми вибираємо трьох призерів.
У осередку G1 пишемо таку формулу. Вона вибере імена призерів за випадковими числами зі стовпця F. = ВПР (F1; $ A $ 1: $ B $ 6; 2; 0)
Вийшла така таблиця переможців.
Якщо потрібно вибрати призерів за декількома номінаціями, то натискаємо на клавішу F9 і відбудеться не лише заміна випадкових чисел, а й пов'язаних із ними імен переможців.
Як вимкнути оновлення випадкових чисел уExcel.
Щоб випадкове число не змінювалося у комірці, потрібно написати формулу вручну і натиснути клавішу F9 замість клавіші «Enter», щоб формула замінилася на значення.
У Excel є кілька способів, як копіювати формули, щоб посилання на них не змінювалися. Дивіться опис простих способів такого копіювання у статті "