Генерация случайных чисел

Часто требуется смоделировать случайный процесс с известным законом распределения вероятностей. Тогда необходимо получить случайные числа в соответствии с этим законом распределения. Инструмент Генерация случайных чисел из Пакета анализа выдает случайные числа для основных законов распределения: равномерного, биномиального, нормального, дискретного и т.д.

Excel содержит функцию СЛЧИС для генерации равномерно распределенных случайных чисел в диапазоне [0,1] и функцию СЛУЧМЕЖДУ для генерации случайных чисел в произвольно заданном диапазоне значений.

В качестве примера проверим качество инструмента Генерация случайных чисел для нормального распределения. Для этого сгенерируем случайные числа с характеристиками из предыдущего примера, построим график плотности вероятностей и визуально сравним рисунки.

Перейдите на чистый лист Excel и вызовите инструмент Генерация случайных чисел через меню Сервис Анализ данных…. Введите параметры, как показано на рисунке.

Число переменных определяет число столбцов при выводе случайных чисел. В поле Распределение устанавливается нужный тип распределения. Параметры для каждого типа распределения различны. Выходной интервал должен содержать ссылку на ячейку, с которой будут в столбец выводиться случайные числа.

Нажмите ОК – случайные числа разместятся в ячейках А1:А30. В ячейку В1 введите функцию =НОРМРАСП(A1;15;1,5;0) и размножьте ее до ячейки В30. Далее по диапазону В1:В30 постройте график – Вы получите разбросанный график плотности вероятностей. Теперь необходимо отсортировать диапазон А1:А30 в порядке возрастания: выделите диапазон А1:А30, вызовите пункт меню Данные Сортировка и сортировать в пределах указанного выделения. По окончании сортировки график примет следующий вид.

Визуальное сравнение с эталонным графиком показывает достаточное сходство: при большем числе случайных чисел кривая будет стремиться к нормальной. На практике закон распределения вероятностей для данных, полученных в результате наблюдения, редко известен заранее. Его устанавливают с помощью статистических методов и процедур.

Основы статистического анализа

Статистический анализ охватывает методы описания и представления статистических данных (описательная статистика) и методы обработки этих данных (аналитическая статистика) с целью изучения, формулирования выводов, принятия решений и прогнозирования.

Статистический анализ строится на большом объеме данных, сплошном и полном охвате всех событий, называемой генеральной совокупностью. Часто генеральная совокупность слишком многочисленна или малодоступна, поэтому для исследования из нее делают выборки (выборочная совокупность), по которым судят обо всей генеральной совокупности. Для наилучшего представления информации о генеральной совокупности выборка должна быть представительной (репрезентативной). Иногда лучшим способом получения представительной выборки является многократный случайный отбор данных или повторение опыта. Если генеральная совокупность доступна, то для получения представительной выборки можно воспользоваться инструментом Выборка из Пакета анализа Excel. На основе полученной выборки приблизительно устанавливают выборочный закон (выборочную функцию) распределения и другие характеристики случайной величины.

Статистическая вероятность или статистическая частота есть отношение число успешных исходов m к общему числу испытаний n (m/n). Статистическая частота события стремится к теоретической вероятности p при большом числе испытаний. Выборочная функция распределения также стремится к теоретической функции распределения F(x) при больших n. Для построения выборочных функций распределения в Excel используется функция ЧАСТОТА и инструмент Гистограмма из Пакета анализа.

Случайные выборки значений из генеральной совокупности всех событий имеют числовые статистические характеристики.

Среднее арифметическое случайных значений (СРЗНАЧ).

Медиана есть число, которое является серединой множества чисел, т.е. половина чисел больше медианы, а половина меньше; вычисляется функцией МЕДИАНА.

Мода есть наиболее часто встречающееся значение; вычисляется функцией МОДА.

Среднее гармоническое есть величина обратная среднему арифметическому обратных величин (СРГАРМ).

Среднее геометрическое используется для вычисления средних темпов роста и есть корень n-ой степени из произведения n положительных значений (СРГЕОМ).

Дисперсия – функция ДИСП.

Стандартное отклонение – функция СТАНДОТКЛОН.

Эксцесс характеризует степень остроконечности (>0) или сглаженности (<0) «хвостов» распределения, т.е. частоты появления удаленных от среднего значений (ЭКСЦЕСС).

Асимметрия характеризует степень несимметричности распределения относительно среднего вправо (>0) и влево (<0), вычисляется функцией СКОС. Подробнее см. справку по F1, введя для поиска имя функции.

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

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

При обработке случайных выборок, кроме получения статистических характеристик, обычно решаются следующие задачи:

1. Определение степени достоверности выборки, отнесение или не отнесение событий выборки к некоторой статистической совокупности. Определяется с помощью доверительных интервалов – интервалов, в который события попадают с заданной доверительной вероятностью р=1–α, где α - уровень значимости – максимальное значение вероятности, при котором появление события практически невозможно. Достаточным обычно считается α =0.05 – ей соответствует доверительная вероятность 0.95. Для повышения надежности статистических выводов берут α =0.01, чему соответствует доверительная вероятность 0.99. Вычисление границ доверительного интервала в Excel осуществляется функцией ДОВЕРИТ.

2. Определение меры соответствия выборки какому-либо теоретическому распределению. Выполняется с использованием критериев согласия, в частности ХИ-квадрат – функция ХИ2ТЕСТ в Excel. Ориентировочная оценка может быть выполнена с помощью построения графиков и визуального сравнения расхождений и совпадений выборочного и теоретического распределений.

3. Выявление различий между выборками выполняется с использованием критериев различия, в частности t-критерия Стьюдента (функция ТТЕСТ) и критерия Фишера (функция ФТЕСТ). Можно использовать инструменты из Пакета анализа Excel: Двухвыборочный t-тест с различными дисперсиям, и Двухвыборочный F-тест для дисперсий, а также Парный двухвыборочный t-тест для средних и Двухвыборочный t-тест с одинаковыми дисперсиями.

4. Оценка влияния на выборки одного, двух или более факторов – однофакторный, двухфакторный и т.д. дисперсионный анализ. Инструменты Excel: Однофакторный дисперсионный анализ, Двухфакторный дисперсионный анализ с повторениями и Двухфакторный дисперсионный анализ без повторений.

5. Выявление степени связи между выборками (переменными) - корреляционный анализ. В качестве меры связи двух случайных величин используют коэффициент корреляции R. Если R=0 – зависимости нет, R>0 – зависимость прямо пропорциональная, R<0 – зависимость обратно пропорциональная. В Excel используется функция КОРРЕЛ и инструмент Корреляция.

6. Установление формы зависимости (уравнения регрессии) между выборкой (случайной переменной Y) и одной или несколькими независимыми переменными величинами – регрессионный анализ, с целью оценки достоверности принятой математической модели статистическим данным. Инструменты регрессионного анализа были рассмотрены ранее.

Наиболее распространенными пакетами статистического анализа и прогнозирования являются Statistica, Statgraphics, NCSS, SPSS, Project Expert (финансовое планирование). Извеcтны также пакеты SAS, SYSTAT, SigmaStat, SigmaPlot, ESB Stats, MVSP, Chameleon Statistics, Leo Statistic, Simca-P и другие. Перспективным инструментом решения трудноформализуемых задач прогнозирования, статистического и регрессионного анализа являются пакеты, построенные по технологии обучающихся нейронных сетей, в частности пакет STATISTICA Neural Network. Известны применения нейрокомпьютеров (CNAPS PC/128), имитаторов нейронных сетей (Qnet for WIndows) для прогнозирования финансовой деятельности и пр.