Добро пожаловать в форум, Guest  >>   Войти | Регистрация | Поиск | Правила | В избранное | Подписаться
Все форумы / Microsoft Office Новый топик    Ответить
 генератор случайных чисел в excel  [new]
PlanB
Member

Откуда: Moscow
Сообщений: 2738
добрый день! прочитал много умного про ГПСЧ, ничего не понял вообще. Понял только то, что =слчис() в excel или rnd() в vb работать будут плохо, а точнее, некорректно для моей задачи. может кто встречал реализации алгоритмов ГПСЧ, которые можно прикрутить к excel? заранее спасибо!
6 июн 17, 10:21    [20542267]     Ответить | Цитировать Сообщить модератору
 Re: генератор случайных чисел в excel  [new]
Akina
Member

Откуда: Зеленоград, Москва, Россия
Сообщений: 19291
PlanB
Понял только то, что =слчис() в excel или rnd() в vb работать будут плохо, а точнее, некорректно для моей задачи.
ну как минимум познакомьте социум со своей задачей, что ли...
6 июн 17, 12:46    [20542991]     Ответить | Цитировать Сообщить модератору
 Re: генератор случайных чисел в excel  [new]
PlanB
Member

Откуда: Moscow
Сообщений: 2738
Akina
PlanB
Понял только то, что =слчис() в excel или rnd() в vb работать будут плохо, а точнее, некорректно для моей задачи.
ну как минимум познакомьте социум со своей задачей, что ли...
я много испытаний монте-карло что бы получить распределение потерь в соответствии с моей моделью. участвуют три набора случайных переменных, определяющие исходы кажлдого эксперимента. соответственно, мне нужны три больших ряда числе (по 500к элементов в каждом), чем случайнее, тем лучше. Текущая реализация настроена таким образом, что при пересчете листа все три набора случайных числе так же пересчитываются и это бы не хотелось менять, если честно.


я что-то уже качнул на алгоритме Mersenne Twister. но там вот какая штука, массив генерируется формулой =NtRand(Size;Algorithm;Random seed1;Random seed2), где:
Size - размер генерируемого массива;
Algorithm - алгоритм генерации случайного числа;
Random seed1 и 2 - точки, от которых отталкивается алгоритм при создании псевдослучайного ряда.
вместо Random seed1 и 2 можно воткнуть любые числа, если два раза получить ряд при помощи одинаковых Random seed1 и 2, то он будет полностью идентичен. можно их кодом менять каждый раз на новые тупо по порядку, видимо. надо вкурить сам алгоритм, че за числа такие...
6 июн 17, 13:03    [20543068]     Ответить | Цитировать Сообщить модератору
 Re: генератор случайных чисел в excel  [new]
Akina
Member

Откуда: Зеленоград, Москва, Россия
Сообщений: 19291
Пока не увидел ни полслова, свидетельствующих о том, что RND() будет работать некорректно.
6 июн 17, 13:06    [20543076]     Ответить | Цитировать Сообщить модератору
 Re: генератор случайных чисел в excel  [new]
exp98
Member

Откуда:
Сообщений: 1700
PlanB
Понял только то, что =слчис() в excel или rnd() в vb работать будут плохо, а точнее, некорректно для моей задачи
Предположу, что речь шла о том, что массивы от МС нужно облагородить. Ниже получилось очень длинное ИМХО, хорошо если полезное))

+
Могли говорить например 2-х начальных вещах:
1) ГЧС исторически имел у МС недостаточную равномерность.
2) в результате (1) известная предельная теорема, что распределение выражения (a1+a2+ +an)/n стремится к Нормальному с теми же МатОж и Дисп, если все ai имеют одинаковое распределение.

В своё время в Си у rnd() был параметр: начальное число, запускающее алгоритм.
Чтобы без ухищрений, мне рекомендовали задавать начальное число навроде "не маленькое и простое", например 1021. Для разных реализаций разные начальные числа.
Затем подождать немного времени и начать собирать массив.
Чтобы вместо псевдоравномерного распределения получать требуемое распределени, этот массив фильтруют и проверяют критерием на лету.

Однако слчис() не имеет такого параметра. Впервые решил посмотреть на слчис(), и вот на рис. один из результатов. Теоретически, МатОж среднего в пределе должно чаще сгущаться вокруг 0,5.

Нужно особо отметить, что красная линия "хронологически" вычисляется позднее синей и казалось бы должна вести себя лучше. Ан нет! предельное распределение теоретически зависит только от длины усредняемой выборки. Пример конечно не доказывает, а только иллюстрирует две реализации, где каждая линия, ожидается, реализует 1 случайное значение одной Нормальнораспределённой величины. А вот какой элемент одного массива взять, зависит от облагораживающего фильтра.


К сообщению приложен файл. Размер - 7Kb
16 июн 17, 14:14    [20570120]     Ответить | Цитировать Сообщить модератору
 Re: генератор случайных чисел в excel  [new]
PlanB
Member

Откуда: Moscow
Сообщений: 2738
exp98, начальное число для СЛЧИС() действительно нельзя задать (т.е. нельзя установить зерно set.seed=i и от него плясать дальше и при желании восстановить случайный набор) - это первая проблема. вторая - генератор слчис() имеет маленький период (вроде разрядность винды*2).

я остановился на алгоритме ГПСЧ Мерсенна. У него гигантский период, цитата Вики: "Псевдослучайная последовательность, порождаемая вихрем Мерсенна, имеет очень большой период, равный числу Мерсенна, что более чем достаточно для многих практических приложений". Для криптографии он не годится, но мне нужно просто генерить псевдорандом, так что отлично. Нашлась даже надстройка для excel, которая это делает =NtRand(). Далее я поступил так, как мне позволяет мой программерский скилл: циклом на VBA заводил в формулу =NtRand() значение "зерна" и сохрянял нужный мне результат (благо массив пересчитывается при каждом обновлении "зерна"). Результат отличный: всего за 32 часа я провел 50000 экспериментов...

короче я решил всю эту штуку попытаться повторить в специализированном статистическом ПО. т.к. spss у меня теперь нет, пытаюсь делать на R!. Пока ничего толкового не написал, но по прикидкам 500к экспериментов должны отработаться где-то за 3-4 часа. Главное что бы вообще начало работать...

Если есть на этом форуме спецы по R, буду очень благодарен за советы.
P.S. векторизация - явно не мой конек.
16 июн 17, 16:08    [20570530]     Ответить | Цитировать Сообщить модератору
 Re: генератор случайных чисел в excel  [new]
exp98
Member

Откуда:
Сообщений: 1700
с R не случилось.

А что за векторизация? вряд ли речь о векторизации растровых топографических карт.
Наверное разные матричные операции и уравнения - ну так это спец. разделы, не все их проходили. Полезны аналогии с 2-х --- 3-х мерными векторами, т.е. планиметрия и стереометрия + формальная матричная алгебра.

+
Казалос бы не связанные вещи:
корреляция -- "коэфф-т линейной предсказуемости" -- глядим на формулу: ба! да это же скалярное произведение 2-х векторов (=СУММПРОИЗВ( А; Б) )

А что такое скалярное произведение? это ДЛИНА проекции одного вектора на другой (не важно кого на кого). А там где проекция, там прямоугольные треугольники, теорема Пифагора, косинусы и т.д. Одним словом Планиметрия.

А дисперсия? Д= М( х -М(х) )^2 = М(х^2) - ( М(х) )^2
М(х) = Суммпроизв (Х; Р), где Р=(р1 р2 р3 ...)
Блин! да это тоже скаля-е произв-е.

И т.д.
16 июн 17, 16:50    [20570667]     Ответить | Цитировать Сообщить модератору
 Re: генератор случайных чисел в excel  [new]
exp98
Member

Откуда:
Сообщений: 1700
Добавлю ... а где скал-е прозв-е, там и косинусы угла между векторами.
Cos( А и В)= суммпроизв( А; В) / длина(А) / длина(В)
длина(В) = корень ( суммпроизв( В; В) )

Cos() = 0 === векторы перпендикулярны, не коррелируют === степень предсказуемости одного по другому никакая.

Cos() = 1 === векторы смотрят в одну сторону
Cos() = -1 === смотрят в противоположные стороны

и много других полезных геометрических аналогий.
Всё, закончил.
16 июн 17, 16:57    [20570694]     Ответить | Цитировать Сообщить модератору
 Re: генератор случайных чисел в excel  [new]
PlanB
Member

Откуда: Moscow
Сообщений: 2738
exp98
А что за векторизация?
в R! вместо колонок и строк вектора. Он ими крутит быстрее
16 июн 17, 17:52    [20570831]     Ответить | Цитировать Сообщить модератору
 Re: генератор случайных чисел в excel  [new]
Akina
Member

Откуда: Зеленоград, Москва, Россия
Сообщений: 19291
exp98
и вот на рис. один из результатов.
Две выборки по 170 рандомов - и такие далеко идущие выводы? Нет, Вы это серьёзно? Постройте сотню выборок, тысяч по десять хотя бы - тогда будет хоть какой-то предмет для разговора...
16 июн 17, 20:59    [20571172]     Ответить | Цитировать Сообщить модератору
Все форумы / Microsoft Office Ответить