Комментарии 11
Спасибо за статью, но:
> Метод исключения (...)
...Это связано с тем, что на хвостах распределения значения 
существенно меньше 
и почти все разыгранные значения
будут отклонены.
Метод, конечно, весьма соблазнительный в силу своей простоты. Настолько, что хочется его применять и вне Excell, где нет ограничений на размер выборки. Но если Вы будете это делать, то трижды проверьте "хвосты" получившейся выборки, прежде чем пускать ее в дело! Я вот однажды обжегся при решении очень похожей задачи. Я строил псевдослучайные временные ряды длиной всего-то в десяток миллиардов отсчетов. Сущая ерунда по сравнению с ожидаемым
периодом повторяемости ГСЧ
Даже если учесть, что я хотел нормальное распределение, а ГСЧ давал равномерное, из-за чего я фактически обращался к ГСЧ 6-12 раз в каждой точке, а потом суммировал полученные значения (ЦПТ!)
В общем, я даже не думал о возможной подставе. Пока внезапно не
обнаружил в своих сигналах квазипериодичность.
Так бы я ее никогда не заметил (там период порядка E+9), но мне пришлось раскапывать один баг, и по ходу дела я на эту квазипериодичность наткнулся
А потом мне попалась вот эта статья, из которой вполне очевидно, что в статистиках второго порядка неидеальность ГСЧ начинает прослеживаться гораздо раньше, если не принимать специальные меры.
Попросту говоря, если вы генерируете более-менее объемную выборку методом исключения (вызывая ГСЧ многократно большее число раз, чем потребный объем выборки), то при этом потенциально возможны всякие неожиданности.
Другой вопрос - насколько они способны исказить целевое распределение. Ответ я не знаю, но вообще было бы интересно, если кто-то такое расследование проведет...
там период порядка E+9
Алексей, спасибо за комментарий))
Я когда писал статью, озаботился тем, какой алгоритм генерации случайных чисел (ГСЧ) использует Excel. Это алгоритм Mersenne Twister. Его период порядка Е+6000. Так что в Excel описанная вами проблема не возникла бы. Правда, Excel не справился бы с генераций миллиардов случайных чисел. Подробнее об эксельном ГСЧ https://chatgpt.com/share/67ab2aae-1f68-800f-b57d-1bf6578312e7
Да, ГСЧ в современном Exctll явно лучше, чем в фортране 2008 года. Но в моем случае E+9 - это, конечно, не период повторяемости ГСЧ, а период возникновения определенных аномалий в моем сигнале. Причем это не точный период, а плавающий. И вот он на порядки меньше
периода повторяемости самого ГСЧ (около E+18)
RANDOM_NUMBER
Intrinsic Subroutine: Returns one pseudorandom number or an array of such numbers.
CALL RANDOM_NUMBER (harvest)
harvest
(Output) Must be of type real. It can be a scalar or an array variable. It is set to contain pseudorandom numbers from the uniform distribution within the range 0 <= x < 1.
The seed for the pseudorandom number generator used by RANDOM_NUMBER can be set or queried with RANDOM_SEED. If RANDOM_SEED is not used, the processor sets the seed for RANDOM_NUMBER to a processor-dependent value.
The RANDOM_NUMBER generator uses two separate congruential generators together to produce a period of approximately 10**18, and produces real pseudorandom results with a uniform distribution in (0,1). It accepts two integer seeds, the first of which is reduced to the range [1, 2147483562]. The second seed is reduced to the range [1, 2147483398]. This means that the generator effectively uses two 31-bit seeds.
For more information on the algorithm, see the following:
Communications of the ACM vol 31 num 6 June 1988, titled: Efficient and Portable Combined Random Number Generators by Pierre L'ecuyer.
Springer-Verlag New York, N. Y. 2nd ed. 1987, titled: A Guide to Simulation by Bratley, P., Fox, B. L., and Schrage, L. E.
В общем, некая неслучайность присутствует. И при решении некоторых задач это может быть неприятно.
И второй нюанс: если нам нужна выборка большого объема, то мы точно не будем работать в Excell. А значит и обращаться к его ГСЧ не сможем (технически это возможно, но скорость будет несоразмерна задаче). А вот какие ГСЧ используются сейчас в стандартных библиотеках, это вопрос. В моем случае (компиляторы Интел, 2008 год) плюсы и фортран к одному и тому же ГСЧ обращались. Как сейчас - не знаю (компилятор пока не могу обновить), но шанс столкнуться с подобным багом вполне реален. На что собственно и намекает довольно свежая хабровская статья уважаемого @red-cat-fat, которую я упомянул в первом комменте
а как же импорто-замещение?
Функция «СЛМАССИВ» доступна в Excel 365 и Excel 2021
Вы очень красиво функции преобразовали в фото, для удобства чтения это отлично, но в тексте вы не разу не упомянули злосчастную вышенаписанную функцию, что возможно не позволит поисковым системам спроводить до вашей статьи потенциально заинтересованного человека.
from scipy import stats # И все будет в порядкеКогда-то давным-давно, когда виндовс была 98, а трава зеленее сам начинал с экселя, теперь вообще не понимаю, как в нем можно работать.
Спасибо за ностальжи :)
А вообще есть распределения которые, как мне кажется сгенерить в экселе не получится, например устойчивое распределение Леви. Хотя, учитывая что в нем как-то DooM запускают, то наверняка это возможно. Но это точно будет очень медленная генерация.
Моя борьба с ДЕТЕРМИНАЦИЕЙ (отпугивает хейтеров) , были и более экзотические варианты
=ABS(СЛЧИС()-СЛУЧМЕЖДУ(0;1))
=ABS(ОСТАТ(СЛЧИС()+СЛЧИС();1)-СЛУЧМЕЖДУ(0;1))
=ABS(ОСТАТ(СЛЧИС()+СЛЧИС()+СЛЧИС();1)-СЛУЧМЕЖДУ(0;1))
=ABS(ОСТАТ(СЛЧИС()+СЛЧИС()+СЛЧИС()+СЛЧИС();1)-СЛУЧМЕЖДУ(0;1))
=ABS(ОСТАТ(СЛЧИС()+СЛЧИС()+СЛЧИС()+СЛЧИС()+СЛЧИС();1)-СЛУЧМЕЖДУ(0;1))
Как в Excel сгенерировать случайную величину произвольного распределения