Рассмотрим Равномерное дискретное распределение, построим график функции распределения, вычислим среднее значение и дисперсию. Сгенерируем случайные значения (выборку) с помощью функции MS EXCEL
СЛУЧМЕЖДУ()
. На основании выборки оценим среднее и стандартное отклонение распределения.
Равномерное дискретное распределение (англ. Discrete uniform distribution)
имеет место, например, при подбрасывании симметричной монеты. Пусть если выпал «орёл», то случайная величина принимает значение 1, если выпала «решка» — то 0. Т.к. вероятность наступления событий одинакова и всего 2 возможных исхода, то вероятность случайной величины принять значение 1 (или 0) равна 1/2=0,5.
Распределение называется равномерным, т.к. вероятность любого исхода одинакова.
Примечание
: В данном случае, когда возможно всего 2 исхода,
равномерное распределение
является частным случаем
Распределения Бернулли
с параметром
p
=
q
=1-
p
=0,5.
Другой пример. Результат бросания симметричной игральной кости является
равномерной дискретной случайной величиной
, т.к. количество точек на грани кубика принимает одно из 6 равновероятных значений. Вероятность выпадения каждой из шести граней равна 1/6.
Для этого примера
функция распределения
будет выглядеть следующим образом.
Примечание
: Для построения графика использованы идеи из статьи про
ступенчатый график
.
СОВЕТ
: Подробнее о
Функции распределения
см. статью
Функция распределения и плотность вероятности в MS EXCEL
.
Математическое ожидание и дисперсия
В
файле примера на листе График
приведен расчет
математического ожидания
по формуле =(a+b)/2.
Дисперсия (квадрат стандартного отклонения)
для
равномерного дискретного распределения
может быть вычислена по формуле =((b-a+1)^2-1)/12.
Генерация случайных значений
Случайные числа, имеющие
равномерное дискретное распределение
, можно сгенерировать с помощью функции MS EXCEL
СЛУЧМЕЖДУ()
. В функции можно задать нижнюю и верхнюю границу интервала [a; b]. Функцией будут сгенерированы
целые
случайные числа из указанного интервала (см.
файл примера лист Генерация
).
Обратите внимание, что массив случайных чисел, сгенерированных с помощью функции
СЛУЧМЕЖДУ()
, автоматически обновится при пересчете листа. Пересчет листа в MS EXCEL производится при вводе нового значения в ячейку или при нажатии клавиши
F9
.
Примечание
: Подробнее про функцию
СЛУЧМЕЖДУ()
см. статью
Функция СЛУЧМЕЖДУ() — Случайное число из заданного интервала в MS EXCEL
.
Чтобы сгенерировать
нецелые
случайные числа, например из интервала [1,1; 2,5], необходимо записать формулу =
СЛУЧМЕЖДУ(1,1*10;2,5*10)/10
.
Множитель 10 отражает тот факт, что
нецелые
случайные числа будут сгенерированы с точностью до десятых. Если интервал задан с точностью до сотых, то нужно использовать множитель 100.
Как видно из формулы — границы интервала также могут быть нецелыми числами. Хотя, конечно, можно сгенерировать числа, например, с точностью до сотых с помощью формулы =
СЛУЧМЕЖДУ(10*100;20*100)/100
. В этом случае случайные числа будут принадлежать интервалу [10;20] и иметь вид 10,37; 16,08; 15,43 и т.д.
Оценка среднего и стандартного отклонения
Сгенерируем 50 чисел (выборку) и разместим их в диапазоне
B17:B66
. Нижнюю и верхнюю границу интервала возьмем [1; 6] и разместим их в диапазоне
B5:B6
.
Математическое ожидание
этого распределения
=(B5+B6)/2
и равно (6+1)/2=3,5.
Стандартное отклонение
распределения равно =
КОРЕНЬ(((B6-B5+1)^2-1)/12)
=1,71
Чтобы оценить
математическое ожидание
воспользуемся значениями выборки
=СУММ(B17:B66)/СЧЁТ(B17:B66)
.
Оценить
стандартное отклонение
можно с помощью формулы
=СТАНДОТКЛОН.В(B17:B66)
в MS EXCEL 2010 или =
СТАНДОТКЛОН(B17:B66)
для более ранних версий.
Чтобы оценить
дисперсию
используйте формулу
=ДИСП.В(B17:B66)
в MS EXCEL 2010 или
=ДИСП(B17:B66)
для более ранних версий. Также можно использовать формулу
=СТАНДОТКЛОН.В(B17:B66)^2
.
СОВЕТ
: О других распределениях MS EXCEL можно прочитать в статье
Распределения случайной величины в MS EXCEL
.
Генерация случайных чисел в MS EXCEL
Смотрите также что ты хочешь RAND()): Pelena, китин, Спасибо
прилагаю фото и 0,75 (проценты), в — 11. Используя числу 2. Следовательно, графике функции распределения); случае 4 значениям
Зададим вероятности, что дискретная определенным распределением. Например, MS EXCEL имеется шаг равен 2)
- них будут сгенерированы
- Примечание
- о генерировании случайных
- Рассмотрим генерацию случайных чисел
- >…чтобы в каждой
В русском экселе за помощь! файлик. В таблице другом — 14; функцию СЛЧИС() сделать генератор, который результат функции СЛЧИС() будет вФункция ПОИСКПОЗ() возвращает позицию из диапазона случайной величины сопоставлены
случайная величина Х вес куска мыла, специальная функция ВЕРОЯТНОСТЬ(). будут повторены по совершенно одинаковые данные,: С помощью другого чисел с помощью с помощью надстройки следующей ячейки выпадало не помню, какКакую формулу в EXCEL синим, отмечены ячейки, 11; 8 (минуты). будет случайно выдавать среднем в 50%Е7:Е10 4 значения вероятности).
примет определенное значение, изготавливаемого на фабрике,СОВЕТ 2 раза, а то нет особого инструмента надстройки Пакет надстройки Пакет Анализа Пакет Анализа и число 0 или называются функции использовать для генерации
Модельное распределение
в которые мнеПримечание значения в этом случаях соответствовать именно, в которой содержитсяДля генерации случайного числа с использованием таблицы (см. файл может быть распределен
: О генерации чисел, каждая последовательность будет смысла указывать в анализа, который называется и с помощью формул MS EXCEL. 1, но мат.Сергей шепелев случайного числа от необходимо сгенерировать случайные
: Про функцию ВПР() можно интервале. этому числу. наименьшее значение Е7:Е10). создадим дополнительный столбец примера). по нормальному закону, имеющих произвольное дискретное повторена по 3 поле Число переменных «Выборка», можно извлечь формул.В надстройку Пакет анализа ожидание смещено в: Решение у меня 0 до 1, числа в диапазоне прочитать здесь.Решением вышеуказанной задачи являетсяЧтобы убедиться в этом, Подробнее о функции ПОИСКПОЗ() см.E
Произвольное дискретное распределение
Для вычисления Функции распределения а число автомобилей, распределение, см. статью раза (4*2*3=24). значение отличное от случайную выборку изВ MS EXCEL имеется входит инструмент Генерация
сторону 1 есть, но нужно при условии что от 9,8 доСОВЕТ
формула =ВПР(СЛЧИС();{0;14:0,5;11:0,75;8};2) вычислим математическое ожидание в статье Функция ПОИСКПОЗ(). используем функцию ВЕРОЯТНОСТЬ(). прибывающих на стоянку
Генерация дискретного случайногоС помощью надстройки Пакет 1. конечной генеральной совокупности. множество функций, с случайных чисел, скакое предполагаемое число знать какая требуется с вероятностью 80% 10,2. Заранее благодарю
excel2.ru
Генерация дискретного случайного числа с произвольной функцией распределения в MS EXCEL
: О других распределенияхЕсли случайное число меньше нашего распределения =СУММ(F7:F10). в MS EXCEL;Этот столбец практически совпадаетФормула =ВЕРОЯТНОСТЬ($A$7:$A$10;$B$7:$B$10;$A$7;A7) вернет Функцию распределения.
в определенный период числа с произвольной Анализа можно сгенерироватьПоле Число случайных чисел
Подробнее см. статью помощью которых можно помощью которого можно «следующих ячеек»? точность для генерации при расчете будет за помощь. MS EXCEL можно 0,5 (50%), то Оно равно 1,8.Функция ИНДЕКС() возвращает значение из диапазона A7:A10,
со значениями функции Того же результата времени — распределению функцией распределения в числа, имеющие произвольное можно оставить незаполненным, Случайная выборка из сгенерировать случайные числа, сгенерировать случайные числа,при очень большом чисел? Допустим, нас 1Pelena
прочитать в статье Распределения время обслуживания составляет Теперь сгенерируем массив у которого позиция распределения, но он
можно добиться с Пуассона.
MS EXCEL. В дискретное распределение, т.е. т.к. оно рассчитывается генеральной совокупности в
принадлежащие различным распределениям. имеющие различные распределения.
- кол-ве случайных чисел интересует второй знакПолосатый жираф алик: Здравствуйте.
- случайной величины в 14 минут, если случайных чисел в совпадает с найденной
- начинается с 0 помощью формулы =СУММ($B$6:B7).Однако, бывают ситуации, когда этой статье также распределение, где пользователь
автоматически в зависимости MS EXCEL. И лишь частьС помощью надстройки Пакет
— может получиться после запятой:: Теперь то же,Дробные числа от MS EXCEL. от 0,5 до
столбце W и функцией ПОИСКПОЗ(). Подробнее о функции ИНДЕКС() см.
и не содержит
Функция ВЕРОЯТНОСТЬ() удобна тем, что она
- форма распределения неизвестна, рассмотрена функция ВЕРОЯТНОСТЬ(). сам задает значения от значений, указанныхС помощью надстройки Пакет из этих распределений Анализа можно сгенерировать
- что среднее случайноеВПР (СЛЧИС ();{0;1:0,8;СЛУЧМЕЖДУ но более внятно! 0 до 1muratmgb 0,75 (25%), то вычислим оценку среднего
- в статье Функция 1. выполняет проверку: известно лишь, чтоЗадана произвольная функция распределения случайной величины и в группе Параметры
Анализа можно сгенерировать представлено в окне случайные числа следующих уйдет в интервал (0;99)/100;};2)Роберт нескажу генерирует функция СЛЧИС().: Здравствуйте уважаемые пользователи 11 минут, если (математического ожидания). В файле ИНДЕКС() в MSТеперь запишем формулу длясумма вероятностей в столбце
дискретная случайная величина дискретной случайной величины. соответствующие вероятности. диалогового окна. Например, числа, имеющее так инструмента Генерация случайных распределений: 0,5:1Думаю, идея ясна: А нельзя сделать Для Вашего диапазона форума! от 0,75 до
Задача
примера видно, что оценка, EXCEL. генерации случайного числа:В принимает некие значения Сгенерируем случайное числоВ поле Входной интервал при параметрах, указанных называемое модельное распределение. чисел. Чтобы сгенерировать
Равномерное распределение (непрерывное)Abram pupkin
(пишу с телефона, случайное от 0200?’200px’:»+(this.scrollHeight+5)+’px’);»>=СЛЧИС()*0,4+9,8Помогите мне пожалуйста 1, то 8 вычисленная по формуле =СРЗНАЧ(W6:W55), близкаПочему это работает? Рассмотрим=ИНДЕКС($A$7:$A$10;ПОИСКПОЗ(СЛЧИС();$E$7:$E$10;1))должна быть равна с определенной вероятностью. из этой генеральной
значений и вероятностей на рисунке выше, В этом распределении случайные числа изНормальное распределение: «…при расчете будет потестить не могу) до 5, а
СЛУЧМЕЖДУ тоже можно с решением вопроса. минут (см. файл
к истинному значению интервал между 0,3Разберем ее работу: 1; Назовем такую функцию совокупности. Также рассмотрим
excel2.ru
Генератор случайных дробных чисел (Формулы)
необходимо ввести ссылку в каждом столбце нет никакой случайности
других распределений см.Распределение Бернулли 1…»No name потом просто слишком использовать Необходимо сделать генератор примера). 1,8. и 0,8, которыйФункция СЛЧИС() возвращает равномерно распределенное случайноевероятность для каждого значения распределения «произвольной» функцией функцию ВЕРОЯТНОСТЬ(). на двухстолбцовый диапазон будет выведено по — генерируются заранее
статью про распределенияБиномиальное распределение
это ключевая фраза: вопрос ни о большие снижать доКод200?’200px’:»+(this.scrollHeight+5)+’px’);»>=СЛУЧМЕЖДУ(98;102)/10
случайных дробных чисел.
Вместо {массива констант} вДля 50% покупателей время
равен половине интервала
число в интервале должна быть в распределения, т.е. которая
Перед прочтением данной статьи (см. файл примера). 24 «случайных» числа:
excelworld.ru
Случайное число в EXCEL
заданные последовательности чисел. MS EXCEL, вРаспределение Пуассона в задании! кто чем единицы?китин Все перепробовал, но
формуле можно использовать обслуживания 14 минут, вероятностей (т.е. равен
от 0 до пределе от 0 не соответствует известным рекомендуется освежить вНеобходимо следить, чтобы сумма четыре нечетных числаПоле Число переменных задает
которой можно найтиКликнув на нужное распределение, что и какответы и комметарии
Лайт ягами: а если так: функция =СЛУЧМЕЖДУ не
ссылку на диапазон для 25% - 0,5). На графике 1 (см. эту статью). до 1; законам распределения, а памяти понятие Функция вероятностей модельного распределения
1; 3; 5; количество столбцов генерируемых
ссылки на другие можно открыть статью, будет расчитывать ?
ясносности не внесли…: Как-то так: =IF(RAND()200?’200px’:»+(this.scrollHeight+5)+’px’);»>=СЛУЧМЕЖДУ(98;102)/10
хочет генерировать дробные ячеек: в одном
8 минут и плотности вероятности видно, что
Результат соответствует вероятностиколичество значений должно соответствовать задана пользователем из распределения случайной величины. была равна 1. 7 (от 1
данных. Т.к. в распределения.
где подробно написанорасчеты в студиюприведи конкретный пример < 0.8; 1;muratmgb числа. К теме
столбце 0; 0,5; для оставшихся 25% этот интервал соответствует
(ось y на вероятностям (в нашем опыта.Часто случайная величина моделируется
Для этого в
и до 7;
Содержание
- Создаём генератор случайных чисел с помощью функции СЛЧИС
- Генерация случайной величины, распределенной по равномерному закону
- Способ применения функции «СЛУЧМЕЖДУ( ; )»:
- Способ применения функции «СЛЧИС()»:
- Функция случайного числа в Excel
- Выборка случайных чисел с помощью СЛЧИС
- Функция СЛУЧМЕЖДУ
- Выбор рандом чисел в заданном диапазоне
- Дробные числа больше единицы
- Как сделать генератор чисел в экселе. Генератор случайных чисел в Excel
- Случайное число в определенном диапазоне. Функция
- Случайное число с определенным шагом
- Как применять рандом для проверки модели?
- Использование надстройки Analysis ToolPack
- Произвольное дискретное распределение
- Генератор случайных чисел нормального распределения
- Как предотвратить повторное вычисление СЛЧИС и СЛУЧМЕЖДУ
Создаём генератор случайных чисел с помощью функции СЛЧИС
С помощью функции СЛЧИС, мы имеем возможность генерировать любое случайное число в диапазоне от 0 до 1 и эта функция будет выглядеть так:
=СЛЧИС();
Если возникает необходимость, а она, скорее всего, возникает, использовать случайное число большого значения, вы просто можете умножить вашу функцию на любое число, к примеру 100, и получите:
=СЛЧИС()*100;
А вот если вам не нравятся дробные числа или просто нужно использовать целые числа, тогда используйте такую комбинацию функций, это позволит вам отсечь значения после запятой или просто отбросить их:
=ОКРУГЛ((СЛЧИС()*100);0);
=ОТБР((СЛЧИС()*100);0)
Когда возникает необходимость использовать генератор случайных чисел в каком-то определённом, конкретном диапазоне, согласно нашим условиям, к примеру, от 1 до 6 надо использовать следующую конструкцию (обязательно закрепите ячейки с помощью абсолютных ссылок):
=СЛЧИС()*(b-а)+а, где,
- a – представляет нижнюю границу,
- b – верхний предел
и полная формула будет выглядеть: =СЛЧИС()*(6-1)+1, а без дробных частей вам нужно написать: =ОТБР(СЛЧИС()*(6-1)+1;0)
Генерация случайной величины, распределенной по равномерному закону
Дискретное равномерное распределение – это такое распределение, для которого вероятность каждого из значений случайной величины одна и та же, то есть
Р(ч)=1/N,
где N – количество возможных значений случайной величины
Для получения случайной величины, распределенной по равномерному закону, в библиотеке Мастера функций табличного процессора в категории Математические есть специальная функция СЛЧИС(), которая генерирует случайные вещественные числа в диапазоне 0 -1. Функция не имеет параметров
Если необходимо сгенерировать случайные числа в другом диапазоне, то для этого нужно использовать формулу:
= СЛЧИС() * (b – a) +a, где
a – число, устанавливающее нижнюю границу диапазона;
b – число, устанавливающее верхнюю границу диапазона.
Например, для генерации чисел распределенных по равномерному закону в диапазоне 10 – 20, нужно в ячейку рабочего листа ввести формулу:
=СЛЧИС()*(20-10)+10.
Для генерации целых случайных чисел, равномерно распределенных в диапазоне между двумя заданными числами в библиотеке табличного процессора есть специальная функция СЛУЧМЕЖДУ. Функция имеет параметры:
СЛУЧМЕЖДУ(Нижн_гран; Верхн_гран), где
Нижн_гран – число, устанавливающее нижнюю границу диапазона;
Верхн_гран – число, устанавливающее верхнюю границу диапазона. Применение функций СЛЧИС и СЛУЧМЕЖДУ рассмотрим на примере.
Пример 1. Требуется создать массив из 10 чисел, распределенных равномерно в диапазоне 50 – 100.
Решение
1. Выделим диапазон, включающий десять ячеек рабочего листа, например B2:B11 (рис. 1).
2. На ленте Формулы в группе Библиотека функций кликнем на пиктограмме Вставить функцию.
3. В открывшемся окне диалога Мастер функций выберем категорию Математические, в списке функций – СЛЧИС, кликнем на ОК – появится окно диалога Аргументы функции.
4. Нажмем комбинацию клавиш <Ctrl> + <Shift> + <Enter> – в выделенном диапазоне будут помещены числа, распределенные по равномерному закону в диапазоне 0 – 1 (рис. 1).
5. Щелкнем указателем мыши в строке формул и изменим имеющуюся там формулу, приведя ее к виду: =СЛЧИС()*(100-50)+50.
6. Нажмем комбинацию клавиш <Ctrl> + <Shift> + <Enter> – в выделенном диапазоне будут размещены числа, распределенные по равномерному закону в диапазоне 50 – 100 (рис. 2).
Способ применения функции «СЛУЧМЕЖДУ( ; )»:
- Установить курсор в ячейку, которой присваиваете значение;
- Выбрать функцию «СЛУЧМЕЖДУ( ; )»;
- В меню указать начальное и конечное число диапазона или ячейки, содержащие эти числа;
- Нажать «ОК»
Наряду с функцией «СЛУЧМЕЖДУ» существует «СЛЧИС()», эта функция в отличие от «СЛУЧМЕЖДУ» выбирает случайное число из диапазона от 0 до 1. То есть присваивает ячейке случайное дробное число до единицы.
Способ применения функции «СЛЧИС()»:
- Установить курсор в ячейку, которой присваиваете значение;
- Выбрать функцию «СЛЧИС()»;
- Нажать «ОК»
У нас есть последовательность чисел, состоящая из практически независимых элементов, которые подчиняются заданному распределению. Как правило, равномерному распределению.
Сгенерировать случайные числа в Excel можно разными путями и способами.
- Функция СЛЧИС возвращает случайное равномерно распределенное вещественное число. Оно будет меньше 1, больше или равно 0.
- Функция СЛУЧМЕЖДУ возвращает случайное целое число.
Выборка случайных чисел с помощью СЛЧИС
Данная функция аргументов не требует (СЛЧИС()).
Чтобы сгенерировать случайное вещественное число в диапазоне от 1 до 5, например, применяем следующую формулу: =СЛЧИС()*(5-1)+1.
Возвращаемое случайное число распределено равномерно на интервале .
При каждом вычислении листа или при изменении значения в любой ячейке листа возвращается новое случайное число. Если нужно сохранить сгенерированную совокупность, можно заменить формулу на ее значение.
- Щелкаем по ячейке со случайным числом.
- В строке формул выделяем формулу.
- Нажимаем F9. И ВВОД.
Проверим равномерность распределения случайных чисел из первой выборки с помощью гистограммы распределения.
- Сформируем «карманы». Диапазоны, в пределах которых будут находиться значения. Первый такой диапазон – 0-0,1. Для следующих – формула =C2+$C$2.
- Определим частоту для случайных чисел в каждом диапазоне. Используем формулу массива {=ЧАСТОТА(A2:A201;C2:C11)}.
- Сформируем диапазоны с помощью знака «сцепления» (=»»).
- Строим гистограмму распределения 200 значений, полученных с помощью функции СЛЧИС ().
Диапазон вертикальных значений – частота. Горизонтальных – «карманы».
Функция СЛУЧМЕЖДУ
Синтаксис функции СЛУЧМЕЖДУ – (нижняя граница; верхняя граница). Первый аргумент должен быть меньше второго. В противном случае функция выдаст ошибку. Предполагается, что границы – целые числа. Дробную часть формула отбрасывает.
Пример использования функции:
Случайные числа с точностью 0,1 и 0,01:
Выбор рандом чисел в заданном диапазоне
Вы можете получить случайное целое число в нужном диапазоне. Для этого используем функцию =СЛУЧМЕЖДУ(мин макс). Первым аргументом функции будет минимальное допустимое число, вторым – максимальное.
Например, так можно получить число в промежутке от -100 до 100:
Функции СЛЧИС и СЛУЧМЕЖДУ изменяют свой результат при каждом пересчёте листа. Если Вам нужно этого избежать – замените формулы на значения с помощью специальной вставки.
А теперь немного примеров использования приведенных функций.
Дробные числа больше единицы
Как вы уже поняли, функция СЛЧИС всегда возвращает дробное число от 0 до 1, а СЛУЧМЕЖДУ – целое в указанном диапазоне. А как выбрать число рандомно, дробное и больше единицы? К примеру, нам нужно случайное дробное число в пределах от 10 до 90. Поможет такая формула:
Как сделать генератор чисел в экселе. Генератор случайных чисел в 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 штук.
Как применять рандом для проверки модели?
Проверить придуманную модель можно при помощи большого количества случайных чисел. Например проверить будет ли прибыльным бизнес-план
Использование надстройки Analysis ToolPack
Другой способ получения случайных чисел в листе состоит в использовании надстройки Analysis ToolPack (которая поставлялась вместе с Excel). Этот инструмент может генерировать неравномерные случайные числа. Они генерируются не формулами, поэтому, если вам нужен новый набор случайных чисел, необходимо перезапустить процедуру.
Получите доступ к пакету Analysis ToolPack, выбрав Данные Анализ Анализ данных.
Если эта команда отсутствует, установите пакет Analysis ToolPack с помощью диалогового окна Надстройки . Самый простой способ вызвать его — нажать Atl+TI.
В диалоговом окне Анализ данныхвыберите Генерация случайных чисели нажмите ОК. Появится окно, показанное на рис. 130.1.
Выберите тип распределения в раскрывающемся списке Распределение, а затем задайте дополнительные параметры (они изменяются в зависимости от распределения). Не забудьте указать параметр Выходной интервал, в котором хранятся случайные числа.
Чтобы выбрать из таблицы случайные данные, нужно воспользоваться функцией в Excel «Случайные числа». Это готовый генератор случайных чисел в Excel.
Эта функция пригодится при проведении выборочной проверки или при проведении лотереи, т.д. Итак, нам нужно провести розыгрыш призов для покупателей.
В столбце А стоит любая информация о покупателях – имя, или фамилия, или номер, т.д. В столбце в устанавливаем функцию случайных чисел. Выделяем ячейку В1. На закладке «Формулы» в разделе «Библиотека функций» нажимаем на кнопку «Математические» и выбираем из списка функцию «СЛЧИС». Заполнять в появившемся окне ничего не нужно. Просто нажимаем на кнопку «ОК».
Копируем формулу по столбцу. Получилось так.
Эта формула ставит случайные числа меньше нуля. Чтобы случайные числа были больше нуля, нужно написать такую формулу. =СЛЧИС()*100
При нажатии клавиши F9, происходит смена случайных чисел. Можно выбирать каждый раз из списка первого покупателя, но менять случайные числа клавишей F9.
Случайное число из диапазона Excel.
Чтобы получить случайные числа в определенном диапазоне, установим функцию «СЛУЧМЕЖДУ» в математических формулах. Установим формулы в столбце С. Диалоговое окно заполнили так.
Укажем самое маленькое и самое большое число. Получилось так.
Укажем самое маленькое и самое большое число. Получилось так. Можно формулами выбрать из списка со случайными числами имена, фамилии покупателей.
Внимание!
В таблице случайные числа располагаем в первом столбце. У нас такая таблица.
В ячейке F1 пишем такую формулу, которая перенесет наименьшие случайные числа.
=НАИМЕНЬШИЙ($A$1:$A$6;E1)
Копируем формулу на ячейки F2 и F3 – мы выбираем трех призеров.
В ячейке G1 пишем такую формулу. Она выберет имена призеров по случайным числам из столбца F. =ВПР(F1;$A$1:$B$6;2;0)
Получилась такая таблица победителей.
В ячейке F1 пишем такую формулу, которая перенесет наименьшие случайные числа.
=НАИМЕНЬШИЙ($A$1:$A$6;E1)
Копируем формулу на ячейки F2 и F3 – мы выбираем трех призеров.
В ячейке G1 пишем такую формулу. Она выберет имена призеров по случайным числам из столбца F. =ВПР(F1;$A$1:$B$6;2;0)
Получилась такая таблица победителей.
Если нужно выбрать призеров по нескольким номинациям, то нажимаем на клавишу F9 и произойдет не только замена случайных чисел, но и связанных с ними имен победителей.
Как отключить обновление случайных чисел в Excel.
Чтобы случайное число не менялось в ячейке, нужно написать формулу вручную и нажать клавишу F9 вместо клавиши «Enter», чтобы формула заменилась на значение.
В Excel есть несколько способов, как копировать формулы, чтобы ссылки в них не менялись. Смотрите описание простых способов такого копирования в статье »
Доброго времени суток, уважаемый, читатель!
Недавно, возникла необходимость создать своеобразный генератор случайных чисел в Excel в границах нужной задачи, а она была простая, с учётом количества человек выбрать случайного пользователя, всё очень просто и даже банально. Но меня заинтересовало, а что же ещё можно делать с помощью такого генератора, какие они бывают, каковые их функции для этого используются и в каком виде. Вопросом много, так что постепенно буду и отвечать на них.
Итак, для чего же собственно мы можем использовать этом механизм:
- во-первых: мы можем для тестировки формул, заполнить нужный нам диапазон случайными числами;
- во-вторых: для формирования вопросов различных тестов;
- в-третьих: для любого случайно распределения заранее задач между вашими сотрудниками;
- в-четвёртых: для симуляции разнообразнейших процессов.
Произвольное дискретное распределение
С помощью надстройки Пакет Анализа можно сгенерировать числа, имеющие произвольное дискретное распределение , т.е. распределение, где пользователь сам задает значения случайной величины и соответствующие вероятности. 
В поле Входной интервал значений и вероятностей необходимо ввести ссылку на двухстолбцовый диапазон (см. файл примера ). 
Необходимо следить, чтобы сумма вероятностей модельного распределения была равна 1. Для этого в MS EXCEL имеется специальная функция ВЕРОЯТНОСТЬ() .
СОВЕТ : О генерации чисел, имеющих произвольное дискретное распределение , см. статью Генерация дискретного случайного числа с произвольной функцией распределения в MS EXCEL . В этой статье также рассмотрена функция ВЕРОЯТНОСТЬ() .
Генератор случайных чисел нормального распределения
Функции СЛЧИС и СЛУЧМЕЖДУ выдают случайные числа с единым распределением. Любое значение с одинаковой долей вероятности может попасть в нижнюю границу запрашиваемого диапазона и в верхнюю. Получается огромный разброс от целевого значения.
Нормальное распределение подразумевает близкое положение большей части сгенерированных чисел к целевому. Подкорректируем формулу СЛУЧМЕЖДУ и создадим массив данных с нормальным распределением.
Себестоимость товара Х – 100 рублей. Вся произведенная партия подчиняется нормальному распределению. Случайная переменная тоже подчиняется нормальному распределению вероятностей.
При таких условиях среднее значение диапазона – 100 рублей. Сгенерируем массив и построим график с нормальным распределением при стандартном отклонении 1,5 рубля.
Используем функцию: =НОРМОБР(СЛЧИС();100;1,5).
Программа Excel посчитала, какие значения находятся в диапазоне вероятностей. Так как вероятность производства товара с себестоимостью 100 рублей максимальная, формула показывает значения близкие к 100 чаще, чем остальные.
Перейдем к построению графика. Сначала нужно составить таблицу с категориями. Для этого разобьем массив на периоды:
- Определим минимальное и максимальное значение в диапазоне с помощью функций МИН и МАКС.
- Укажем величину каждого периода либо шаг. В нашем примере – 1.
- Количество категорий – 10.
- Нижняя граница таблицы с категориями – округленное вниз ближайшее кратное число. В ячейку Н1 вводим формулу =ОКРВНИЗ(E1;E5).
- В ячейке Н2 и последующих формула будет выглядеть следующим образом: =ЕСЛИ(G2;H1+$E$5;””). То есть каждое последующее значение будет увеличено на величину шага.
- Посчитаем количество переменных в заданном промежутке. Используем функцию ЧАСТОТА. Формула будет выглядеть так:
На основе полученных данных сможем сформировать диаграмму с нормальным распределением. Ось значений – число переменных в промежутке, ось категорий – периоды.
График с нормальным распределением готов. Как и должно быть, по форме он напоминает колокол.
Сделать то же самое можно гораздо проще. С помощью пакета «Анализ данных». Выбираем «Генерацию случайных чисел».
О том как подключить стандартную настройку «Анализ данных» читайте здесь.
Заполняем параметры для генерации. Распределение – «нормальное».
Жмем ОК. Получаем набор случайных чисел. Снова вызываем инструмент «Анализ данных». Выбираем «Гистограмма». Настраиваем параметры. Обязательно ставим галочку «Вывод графика».
Получаем результат:
Скачать генератор случайных чисел в Excel
График с нормальным распределением в Excel построен.
Как предотвратить повторное вычисление СЛЧИС и СЛУЧМЕЖДУ
Если вы хотите получить постоянный набор случайных чисел, дат или текстовых строк, которые не будут меняться каждый раз, то есть зафиксировать случайные числа, когда лист пересчитывается, используйте один из следующих способов:
- Чтобы остановить функции СЛЧИС или СЛУЧМЕЖДУ от пересчета в одной ячейке, выберите эту ячейку, переключитесь на панель формул и нажмите F9, чтобы заменить формулу на ее значение.
- Чтобы предотвратить функцию случайных чисел в Excel от автоматического обновления значений в нескольких ячейках, используйте функцию Вставить. Выберите все ячейки с формулой генерации случайных значений, нажмите Ctrl+C, чтобы скопировать их, затем щелкните правой кнопкой мыши выбранный диапазон и нажмите «Вставить специальные»–> «Значения».
Источники
- https://topexcel.ru/sozdaem-generator-sluchajnyx-chisel-v-excel/
- https://zen.yandex.ru/media/id/5d4d8e658da1ce00ad5ece61/5dbadd11e6e8ef00ad7c0e34
- http://word-office.ru/kak-sdelat-random-v-excel.html
- https://officelegko.com/2019/09/09/randomizator-chisel-v-excel/
- https://iiorao.ru/prochee/kak-sdelat-random-v-excel.html
- https://excel2.ru/articles/generaciya-sluchaynyh-chisel-v-ms-excel
- https://exceltable.com/funkcii-excel/generator-sluchaynyh-chisel
- https://naprimerax.org/posts/63/generator-sluchainykh-chisel-v-excel
An example of a probability distribution where all possibilities are equally likely is called a uniform distribution. Since all four suits—heart, club, diamond, and spade—have an equal chance of appearing, a deck of cards has uniform distributions throughout. The chance of receiving either heads or tails in a coin toss is the same, hence a coin also has a uniform distribution. The easiest probability distribution to build in Excel is the uniform distribution. A uniform distribution is a probability distribution in which there is an equal chance of selecting any value between a and b.
The following formula can be used to calculate the probability that a value between x1 and x2 will fall within the range from a to b:
P = (x2 – x1) / (b – a)
Where P is the calculated value between x1 and x2
.png)
For calculating probability, we need:
- a: minimum value in the distribution
- b: maximum value in the distribution
- x1: the minimum value you’re interested in
- x2: the maximum value you’re interested in
Then just by using the formula mentioned above, you can easily calculate the chances or probability of anything.
The characteristics of the uniform distribution are as follows:
The distribution’s average is equal to (a + b) / 2 = μ
The distribution’s variance is σ2 = (b – a)2 / 12
The distribution’s standard deviation is σ = √σ2
How to calculate probability will be clear with the following Example
Example 1: A certain kind of bird has a weight range of 37.25 to 584.32 grams that are equally distributed throughout. What is the chance that a bird will weigh between 100.89 and 477.63 grams if a bird is randomly chosen?
Step 1: First you need to write all these given values in an excel sheet.
a (minimum value in distribution) = 37.25
b (maximum value in distribution) = 584.32
x1 (minimum value you’re interested in) = 100.89
x2 (maximum value you’re interested in) = 477.63
Entering a value in excel
Step 2: For calculating the probability you need to use the formula (x2-x1)/(b-a). Here we enter this enter =(B4-B3)/(B2-B1) because the value of x2 stores in cell B4, the value of x1 stores in cell B3, the value of b stores in cell B2, and the value of a is stored in the cell B1.
.png)
Entering the formula for calculating the chance
Step 3: Press enter then we will get the probability value which is our answer.
.png)
calculated value
Hence, the chance that a bird will weigh between 100.89 and 477.63 grams if a bird is randomly chosen is 0.68865.
Example 2: Every 10 minutes, a bus gets up to a bus stop. What is the chance that the bus will arrive in 5 minutes or less if you arrive at the bus stop?
Step 1: First you need to write all these given values in an excel sheet.
a (minimum value in distribution) = 0
b (maximum value in distribution) = 10
x1 (minimum value you’re interested in) = 0
x2 (maximum value you’re interested in) = 5
Step 2: For calculating the probability you need to use the formula (x2-x1)/(b-a). Here we enter =(B4-B3)/(B2-B1) because the value of x2 stores in cell B4, the value of x1 stores in cell B3, the value of b stores in cell B2, and the value of a is stored in cell B1.
.png)
Step 3: Press enter then we will get the probability value which is our answer.
.png)
Hence, the chance that the bus will arrive in 5 minutes or less if you arrive at the bus stop is 0.5
Рассмотрим равномерное непрерывное распределение. Вычислим математическое ожидание и дисперсию. Сгенерируем случайные значения с помощью функции MS EXCEL СЛЧИС() и надстройки Пакет Анализа, произведем оценку среднего значения и стандартного отклонения.
Равномерно распределенная на отрезке [a; b] случайная величина имеет плотность распределения (вероятности) : 
Функция распределения определяется следующим образом:
Равномерное непрерывное распределение (англ. Continuous uniform d istribution или Rectangular distribution ) часто встречается на практике.
Пример1. Например, известно, что гейзер извергается каждые 50 минут. Найти вероятность, того что турист увидит извержение, если будет ждать у гейзера 20 минут. В соответствии с вышеуказанными формулами вероятность увидеть извержение в течение времени наблюдения равна 20/50=0,4, т.е. 40%.
Пример2. Симметричный волчок после раскручивания падает набок. Вертикальная ось волчка после падения указывает на определенный угол от 0 до 360 градусов. Найти вероятность, того что ось волчка укажет на сектор от 90 до 180 градусов. Вероятность равна (180-90)/(360-0)=0,25.
В файле примера приведены графики плотности распределения вероятности и интегральной функции распределения . 
Математическое ожидание и дисперсия
Математическое ожидание для равномерного непрерывного распределения вычисляется по формуле =(a+b)/2.
Генерация случайных чисел
Случайные числа, имеющие равномерное непрерывное распределение на отрезке [0; 1), можно сгенерировать с помощью функции MS EXCEL СЛЧИС() . В функции нельзя задать нижнюю и верхнюю границу интервала, но записав формулу =СЛЧИС()*(b-a)+a можно сгенерировать равномерно распределенные числа на любом интервале [a; b).
Примечание : Чтобы сгенерировать случайные числа, имеющие равномерное дискретное распределение , воспользуйтесь функцией СЛУЧМЕЖДУ() .
Сгенерировать случайные числа, извлеченные из непрерывного равномерного распределения, можно также с помощью надстройки Пакет анализа .
Сгенерируем массив из 50 чисел из диапазона [3,3; 7,5). Для этого в окне Генерация случайных чисел установим следующие параметры (см. файл примера лист Генерация ):
Как видно из рисунка выше, в поле Случайное рассеивание установлен необязательный параметр равный 2. Параметр Случайное рассеивание может принимать значение от 1 до 32767. Если установить этот параметр, то MS EXCEL будет каждый раз генерировать один и тот же массив чисел, соответствующий этот значению. Этот подход удобен для генерации одинаковых массивов, например, на различных компьютерах.
Оценка среднего и стандартного отклонения
Нижнюю и верхнюю границу интервала возьмем [3,3; 7,5) и разместим их в ячейках B4:B5 . Сгенерируем 50 чисел ( выборку ) и поместим их в диапазоне С14:С63 .
Математическое ожидание этого распределения =(B4+B5)/2 и равно 5,4. Стандартное отклонение распределения равно =КОРЕНЬ(((B5-B4)^2)/12)=1,21
Чтобы оценить математическое ожидание воспользуемся значениями выборки =СУММ(C14:C63)/СЧЁТ(C14:C63) .
Оценить стандартное отклонение можно с помощью формулы =СТАНДОТКЛОН.В(C14:C63) в MS EXCEL 2010 или =СТАНДОТКЛОН(C14:C63) для более ранних версий.
Чтобы оценить дисперсию используйте формулу =ДИСП.В(C14:C63) в MS EXCEL 2010 или =ДИСП(C14:C63) для более ранних версий. Также можно использовать формулу =СТАНДОТКЛОН.В(C14:C63)^2 .
СОВЕТ : О других распределениях MS EXCEL можно прочитать в статье Распределения случайной величины в MS EXCEL .
Как посчитать процентное распределение в Excel по формуле
Процентное распределение отображает нам как определенное значение (например, показатель суммарного дохода) разделяется на отдельные составляющие, которые образуют его целостность.
Формула процентного распределения в Excel
Как видно ниже на рисунке ниже формула вычисления процентного распределения в Excel очень проста:
Каждую часть необходимо разделить на сумму всех частей. В данном случаи ячейка B7 содержит значение суммарного дохода всех отделов регионов. Чтобы вычислить процентное распределение суммарного дохода по всем регионам, достаточно лишь поделить значение отдельного показателя по каждому региону на суммарный доход.
Как видно формула не очень сложна. Она использует просто относительные ссылки на доходы регионов, чтобы поделить их на абсолютную ссылку на суммарный доход. Обратите внимание на абсолютную ссылку. Указанные символы доллара позволяют заблокировать ссылку на одну, конкретную ячейку. Благодаря этому адрес абсолютной ссылки не изменяется при копировании формул в другие ячейки.
Процентное распределение по динамической формуле Excel
Отдельное вычисление для хранения суммарного дохода в отдельной ячейке как константу – не обязательно. Если мы добавим в формулу функцию =СУММ(), тогда мы можем динамически выполнять вычисление процентного распределения. Ниже на рисунке показано решение для создания динамической формулы процентного распределения отдельных значений.
Примечание: Для тех, кто не в курсе – функция СУММ суммирует все значения, которые заданы в ее аргументах.
Снова обратите внимание на то, что все адреса ссылок, которые заданы в аргументах функции СУММ должны быть абсолютными (в данном случаи). Благодаря зафиксированный абсолютными ссылками диапазон ячеек в аргументе функции СУММ, не изменяться в процессе копирования формулы в другие ячейки.
Распределение суммы в таблице Excel помесячно
Добрый день, друзья!
Помогите, если не трудно, справиться с задачкой.
Нужно распределить потребность равномерно помесячно по году. Допускается создание дополнительной таблицы. Но единица измерения меньше единицы не допускается.
Буду Вам очень признателен!
Вложения
Книга1.xlsx (11.6 Кб, 13 просмотров)
В ТАБЛИЦЕ EXCEL РАСПРЕДЕЛЕНИЕ КОЛИЧЕСТВА СООТВЕТСТВУЮЩЕМУ НАИМЕНОВАНИЮ
ЗДРАВСТВУЙТЕ. ПЕРВЫЙ РАЗ НА ФОРУМЕ, НЕ СУДИТЕ СТРОГО. ВОПРОС В ТОМ, ЧТО НАДО СДЕЛАТЬ.
распределение суммы
помогите решить следующую задачу есть общая сумма 2750, которую надо разбить на 70 разных.
Распределение суммы переменной
Здравствуйте у меня вопрос. Я хочу вместо нескольких массивов использовать один. Если я буду их.
Распределение суммы по ФИФО
Необходимо высчитать сколько осталось товара с каждой поставки. За основу взять алгоритм ФИФО.
Вложения
Книга1-4.xlsx (12.7 Кб, 23 просмотров)
Сообщение от andrei_g1
Сообщение было отмечено andrei_g1 как решение
Решение
А можете это сделать в моей табличке? А то я совсем «чайник», боюсь что не правильно пропишу формулы.
Спасибо огромное заранее.
Добавлено через 39 минут
Все. Формулы вставил, все получилось.
Ребята, спасибо огромное. Вы мне здорово помогли
Вложения
Книга1-4.xlsx (13.7 Кб, 21 просмотров)
Сообщение было отмечено andrei_g1 как решение
Решение
заинтересовавшись этим вопросом также выяснил,
что для разных отчётов можно ещё использовать
т.н. банковское (бухгалтерское) округление
также известное, как округление до ближайшего чётного
суть его в том, что все числа, у которых «отбрасываемая» цифра не равна 5,
округляются по правилам математического округления,
а если равна, то цифра округляется до ближайшего кратного целого числа
т.е. 6,5 до 6 а 7,5 до 8.
приводят следующий пример:
Сумма исходных чисел: 1.5 + 2.5 + 3.5 +4.5 = 12
Сумма округленных чисел классическим методом: 2 + 3 + 4 + 5 = 14
Сумма округленных чисел бухгалтерским методом: 2 + 2 + 4 + 4 = 12
Распределение суммы акций
Введите фамилии сотрудников (не менее 20), для всех них укажите даты приема на работу (в диапазоне.
Пропорциональное распределение суммы ЗП
Здравствуйте.:) Поставлена задача распределения суммы з/п сотрудника, которая указана в.
Как задать распределение суммы
Родитель дает ребенку X рублей на месяц, в месяце Y дней (каждый месяц разный). Нужно.
Пропорциональное распределение потока с проверкой суммы
ПОжалуйста помогите написать формулу: Спримерно : J6= ЕСЛИ СУММ (F6: I6)<E6, то D6*J5/(d6+d7) .
Распределение по командам в Excel
Уважаемые коллеги! Помогите решить задачу через Excel Дано: Список 14 учеников в первой.
Распределение задач в Excel
Добрый день, форумчане! Задали не простую задачку над которой бьюсь уже второй день. Общая.