Как сделать теорию вероятности в эксель

Обновлено: 08.07.2024

Прежде чем мы углубимся в различные методы производства случайных чисел в Excel, давайте определимся, что они собой представляют. Говоря простым языком, случайные данные – это последовательность цифр, букв или других символов, в которой отсутствуют какой-либо шаблон или закономерность.

Создание случайных чисел в Excel – основы.

Случайные числа - это без всякой закономерности выбранная последовательность чисел. Но в жизни практически всегда применяются псевдослучайные числа. Их получают по заранее определенному сложному алгоритму. Для постороннего человека они выглядят случайными, но все же их можно предсказать, если узнать этот сложный алгоритм. Псевдослучайные числа применяются во всех компьютерных программах.

Хотя функции случайных чисел Excel удовлетворяют всем стандартным тестам на случайность, они все же не выдают истинные случайные числа. Но это не значит, что Excel для этих целей не годится. Псевдослучайные числа, создаваемые функциями Excel, прекрасно подходят для многих целей.

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

Как и большинство компьютерных программ, генератор случайных чисел Excel производит псевдослучайные числа с помощью некоторых математических формул. Для вас это означает, что теоретически эти числа, создаваемые Excel, предсказуемы. Но только при условии, что кто-то знает все детали алгоритма. Это причина того, что это никогда не было задокументировано и вряд ли когда-либо будет сделано.

Итак, как можно создавать случайные числа в Excel?

Функции Excel СЛЧИС и СЛУЧМЕЖДУ (RAND и RANDBETWEEN в английской версии) возвращают псевдослучайные числа из равномерного распределения, также известного как прямоугольное распределение, где существует равная вероятность для всех значений, которые величина может принимать. Хороший пример равномерного распределения - бросок одной игральной кости. Итогом такой жеребьевки являются шесть возможных значений (1, 2, 3, 4, 5, 6), и каждое из них имеет одинаковую вероятность.
Функции Excel СЛЧИС и СЛУЧМЕЖДУ, по слухам, инициализируются из системного времени компьютера. Технически начальное число является отправной точкой для создания последовательности чисел. И каждый раз, когда вызывается случайная функция Excel, используется новое начальное число, которое возвращает уникальную последовательность. Другими словами, при использовании генератора случайных чисел в Excel вы не можете получить повторяемую комбинацию ни с помощью функции СЛЧИС и СЛУЧМЕЖДУ, ни с помощью VBA, ни какими-либо другими способами.
В ранних версиях Excel, до Excel 2003, алгоритм случайных чисел имел относительно небольшой диапазон (менее 1 миллиона неповторяющихся последовательностей чисел) и не прошел несколько стандартных тестов на случайность для длинных последовательностей. По этой причине, если кто-то все еще работает со старой версией Excel, вам лучше не использовать функцию СЛЧИС с большими имитационными моделями.

Это довольно длинное техническое введение закончено, и мы переходим к более практическим и более полезным вещам.

Функция случайного числа — СЛЧИС.

Функция СЛЧИС в Excel - одна из двух функций, специально разработанных для случайных чисел. Она возвращает произвольное десятичное число (действительное число) от 0 до 1.

СЛЧИС – это непостоянная функция, означающая, что новое случайное число возникает каждый раз при вычислении рабочего листа. А это происходит часто — когда вы выполняете какое-либо действие, например, редактируете формулу (не обязательно СЛЧИС, даже любую другую формулу на листе), редактируете ячейку или вводите новые данные.

Функция СЛЧИС доступна во всех версиях Excel, начиная с самых ранних.

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

А теперь давайте составим несколько формул СЛЧИС для создания случайных чисел в соответствии с вашими условиями.

Ограничение верхней границы диапазона случайных чисел.

Чтобы создать массив от нуля до любого значения N, вы умножаете функцию СЛЧИС на N:

Например, чтобы создать последовательность чисел, больше или равных 0, но меньше 50, используйте следующую формулу:

Примечание. Значение верхней границы никогда не включается в возвращаемую последовательность. Например, если вы хотите получить случайные числа от 0 до 10, включая 10, правильная формула будет =СЛЧИС()*11.

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

Чтобы создать случайное число между любыми двумя указанными вами величинами, используйте следующую формулу СЛЧИС:

Где A - значение нижней границы (наименьшее число), а B - значение верхней границы (наибольшее).

Например, чтобы записать на ваш лист случайные числа от 10 до 50, вы можете использовать следующую формулу:

Примечание. Эта формула никогда не вернет число, равное максимальному значению указанного диапазона (то есть, B).

Случайные целые числа.

Чтобы функция Excel СЛЧИС выдавала целые числа, возьмите любую из вышеупомянутых формул и оберните ее в функцию ЦЕЛОЕ().

Чтобы создать случайные целые числа от 0 до 50:

Чтобы получить их в интервале от 10 до 50:

А теперь рассмотрим более новую функцию — СЛУЧМЕЖДУ().

Функция Excel СЛУЧМЕЖДУ — как получить случайные целые числа в указанном диапазоне

СЛУЧМЕЖДУ - еще одна функция, предоставляемая Excel для случайных чисел. Она возвращает их в указанном вами диапазоне:

Как и СЛЧИС, СЛУЧМЕЖДУ в Excel – это непостоянная функция, которая возвращает новый результат каждый раз, когда ваша электронная таблица пересчитывается. А происходит это при любом изменении на листе.

Например, чтобы решить ту же задачу, которую мы рассматривали чуть выше: получить случайные целые числа от 10 до 50 (включая 10 и 50), используйте следующую формулу:

Функция СЛУЧМЕЖДУ в Excel может создавать как положительные, так и отрицательные числа. Например, чтобы получить список целых чисел от -10 до 10, введите следующую формулу:

Функция СЛУЧМЕЖДУ доступна в Excel 2019 - 2007. В более ранних версиях вы можете использовать формулу СЛЧИС, рассмотренную нами выше.

Далее в этом руководстве вы найдете еще несколько примеров формул, демонстрирующих, как использовать функцию СЛУЧМЕЖДУ для записи случайных значений, отличных от целых.

Как создавать случайные числа с нужным количеством десятичных знаков.

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

Например, чтобы получить список чисел с одним десятичным знаком, вы умножаете нижнее и верхнее значения на 10, а затем делите возвращаемое значение на 10:

СЛУЧМЕЖДУ( нижнее значение * 10; верхнее значение * 10) / 10

Следующая формула СЛУЧМЕЖДУ() возвращает случайные десятичные числа от 1 до 50:

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

Аналогичным образом, чтобы выдать случайные числа от 1 до 50 с двумя десятичными знаками, вы умножаете аргументы функции СЛУЧМЕЖДУ на 100, а затем также делите результат на 100:

=СЛУЧМЕЖДУ(1*100; 50*100) / 100

Как получить случайные даты в Excel

Как вы знаете, даты в Excel представляют собой числа. Поэтому рассматриваемые нами подходы вполне применимы и к ним.

Чтобы вернуть список произвольных дат в каком-то временном интервале, используйте функцию СЛУЧМЕЖДУ в сочетании с ДАТАЗНАЧ:

СЛУЧМЕЖДУ(ДАТАЗНАЧ( дата начала ), ДАТАЗНАЧ( дата окончания ))

Например, чтобы получить список дат с 1 июня 2015 года по 30 июня 2015 года включительно, введите следующую формулу в свой рабочий лист:

В качестве альтернативы вы можете использовать функцию ДАТА:

Не забудьте применить формат даты, и вы получите список случайных дат, подобный этому:

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

Как вставить случайное время в Excel

Учитывая, что во внутренней системе Excel время хранится как десятичное число от 0 до 1, вы можете использовать стандартную функцию Excel СЛЧИС для вставки случайных действительных чисел, а затем просто применить формат времени к этим ячейкам:

Чтобы вернуть случайное время в соответствии с вашими критериями, требуются более сложные формулы, как показано ниже.

Случайное время в указанном интервале.

Чтобы вставить случайное время между любыми двумя указанными вами значениями времени, используйте функцию ВРЕМЯ() или ВРЕМЗНАЧ() вместе с СЛЧИС():

ВРЕМЯ( время начала ) + СЛЧИС() * (ВРЕМЯ( время начала ) - ВРЕМЯ( время окончания ))

ВРЕМЗНАЧ ( время начала ) + СЛЧИС () * (ВРЕМЗНАЧ ( время начала ) - ВРЕМЗНАЧ ( время окончания ))

Например, чтобы вставить время между 6:00 и 20:30, вы можете использовать любую из следующих формул:

=ВРЕМЯ(6;0;0) + СЛЧИС() * (ВРЕМЯ(20;30;0) - ВРЕМЯ(6;0;0))

=ВРЕМЗНАЧ("6:00:00") + СЛЧИС() * (ВРЕМЗНАЧ("20:30:00") - ВРЕМЗНАЧ("6:00:00"))

Случайные дата и время.

Чтобы создать список случайных дат и времени, используйте комбинации функций СЛУЧМЕЖДУ и ДАТАЗНАЧ:

СЛУЧМЕЖДУ(ДАТАЗНАЧ( начальная дата) ; ДАТАЗНАЧ( конечная дата )) + СЛУЧМЕЖДУ(ВРЕМЗНАЧ( время начала ) * 10000; ВРЕМЗНАЧ( время окончания ) * 10000) / 10000

Предположим, вы хотите вставить произвольные даты между 1 июня 2021 года и 31 августа того же года со временем между 8:30 и 17:00. Следующая формула подойдет для вас:

=СЛУЧМЕЖДУ(ДАТАЗНАЧ("1-июн-2021"); ДАТАЗНАЧ("31-авг-2021")) + СЛУЧМЕЖДУ(ВРЕМЗНАЧ("8:30") * 10000; ВРЕМЗНАЧ("17:00") * 10000) / 10000

Вы также можете указать дату и время, используя функции ДАТА и ВРЕМЯ соответственно:

=СЛУЧМЕЖДУ(ДАТА(2021;6;1); ДАТА(2021;8;31)) + СЛУЧМЕЖДУ(ВРЕМЯ(8;30;0) * 10000; ВРЕМЯ(17;0;0) * 10000) / 10000

Выбирайте тот вариант, который больше подходит для вашей задачи.

Как получить случайные буквы в Excel

Чтобы вернуть случайную букву, требуется комбинация трех разных функций:

Где A - первый символ, а Я - последний символ в диапазоне букв, который вы хотите использовать (в алфавитном порядке).

В приведенной выше формуле:

КОДСИМВ() возвращает коды ANSI для указанных букв.
СЛУЧМЕЖДУ() принимает код, возвращаемый функцией КОДСИМВ, как нижнее и верхнее значения диапазона.
СИМВОЛ() преобразует коды ANSI, возвращаемые СЛУЧМЕЖДУ, в соответствующие буквы.

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

Если кто-то помнит таблицу кодов символов ANSI, ничто не мешает вам передать коды букв непосредственно в функцию СЛУЧМЕЖДУ.

Например, чтобы получить заглавные буквы между A (код ANSI 192) и Я (код ANSI 223), вы пишете:

Чтобы получить строчные буквы от а (код ANSI 224) до я (код ANSI 255), используйте следующую формулу:

192-223 — прописные буквы А-Я
224-255 — строчные буквы а-я

Создание текстовых строк и паролей в Excel

Чтобы создать произвольную текстовую строку в Excel, вам просто нужно объединить несколько функций СИМВОЛ и СЛУЧМЕЖДУ.

Например, чтобы получить список паролей, состоящих из 4 символов, вы можете использовать формулу, подобную этой:

Чтобы сделать запись более компактной, я ввел коды ANSI непосредственно в формулу. Четыре функции возвращают следующие случайные значения:

СЛУЧМЕЖДУ(0;9) — возвращает числа от 0 до 9.
СИМВОЛ(СЛУЧМЕЖДУ(65;90)) —прописные буквы от A до Z.
СИМВОЛ(СЛУЧМЕЖДУ(97; 122)) — получаем строчные буквы от a до z.
СИМВОЛ(СЛУЧМЕЖДУ(33;47)) — добавляем специальные символы.

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

Если вы ищете расширенный генератор случайных паролей в Excel, способный создавать текстовые строки любой длины и шаблона, вы можете проверить возможности Advanced Random Generator для тестовых строк.

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

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

Как предотвратить постоянный пересчет формул СЛЧИС и СЛУЧМЕЖДУ.

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

Чтобы функции СЛЧИС или СЛУЧМЕЖДУ не пересчитывались в одной определённой ячейке, выберите её, переключитесь на строку формул и нажмите клавишу F9 , чтобы заменить формулу ее значением.
Чтобы предотвратить пересчет, используйте инструмент Специальная вставка. Выделите все ячейки с формулой, нажмите Ctrl + C , чтобы скопировать их в буфер обмена. Затем щелкните правой кнопкой мыши выбранный диапазон и выберите Специальная вставка >Значения. Или же можете нажать Shift + F10 а потом V , что также позволит вставить ранее скопированные значения вместо формул.
Можно использовать специальный инструмент преобразования формул в значения, который является составной частью надстройки Ultimate Suite.

Как создать уникальные случайные числа в Excel

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

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

Также вы можете воспользоваться специальным генератором случайных значений в Excel, в котором есть опция получения случайных цифровых и текстовых значений. Более подробно читайте о нем по этой ссылке.

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

Расширенный генератор случайных чисел для Excel - Теперь, когда вы знаете, как использовать случайные функции в Excel, позвольте мне продемонстрировать вам более быстрый, простой и не требующий формул способ создания списка случайных чисел, дат или текстовых строк…

Распределение вероятностей – одно из центральных понятий теории вероятности и математической статистики. Определение распределения вероятности равносильно заданию вероятностей всех СВ, описывающих некоторое случайное событие. Распределение вероятностей некоторой СВ, возможные значения которой x ₁, x ₂, … x_n образуют выборку, задается указанием этих значений и соответствующих им вероятностей p ₁, p ₂,… p_n . ( p_n должны быть положительны и в сумме давать единицу).

В данной лабораторной работе будут рассмотрены и построены с помощью MS Excel наиболее распространенные распределения вероятности: биномиальное и нормальное.

1 Биномиальное распределение

П римером практического использования биномиального распределения может являться контроль качества партии фармакологического препарата. Здесь требуется подсчитать число изделий (упаковок), не соответствующих требованиям. Все причины, влияющие на качество препарата, принимаются одинаково вероятными и не зависящими друг от друга. Сплошная проверка качества в этой ситуации не возможна, поскольку изделие, прошедшее испытание, не подлежит дальнейшему использованию. Поэтому для контроля из партии наудачу выбирают определенное количество образцов изделий ( n ). Эти образцы всестороннее проверяют и регистрируют число бракованных изделий ( k ). Теоретически число бракованных изделий может быть любым, от 0 до n .

В Excel функция БИНОМРАСП применяется для вычисления вероятности в задачах с фиксированным числом тестов или испытаний, когда результатом любого испытания может быть только успех или неудача.

Функция использует следующие параметры:

БИНОМРАСП (число_успехов; число_испытаний; вероятностъ_успеха; интегральная) , где

число_успехов — это количество успешных испытаний;

число_испытаний — это число независимых испытаний (число успехов и число испытаний должны быть целыми числами);

вероятность_ успеха — это вероятность успеха каждого испытания;

интегральный — это логическое значение, определяющее форму функции.

Если данный параметр имеет значение ИСТИНА (=1), то считается интегральная функция распределения (вероятность того, что число успешных испытаний не менее значения число_ успехов);

если этот параметр имеет значение ЛОЖЬ (=0), то вычисляется значение функции плотности распределения (вероятность того, что число успешных испытаний в точности равно значению аргумента число_ успехов).

Пример 1. Какова вероятность того, что трое из четырех новорожденных будут мальчиками?

1. Устанавливаем табличный курсор в свободную ячейку, например в А1. Здесь должно оказаться значение искомой вероятности.

2. Для получения значения вероятности воспользуемся специальной функцией: нажимаем на панели инструментов кнопку Вставка функции ( fx ) .

3. В появившемся диалоговом окне Мастер функций - шаг 1 из 2 слева в поле Категория указаны виды функций. Выбираем Статистическая. Справа в поле Функция выбираем функцию БИНОМРАСП и нажимаем на кнопку ОК.

Появляется диалоговое окно функции. В поле Число_ s вводим с клавиатуры количество успешных испытаний (3). В поле Испытания вводим с клавиатуры общее количество испытаний (4). В рабочее поле Вероятность_ s вводим с клавиатуры вероятность успеха в отдельном испытании (0,5). В поле Интегральный вводим с клавиатуры вид функции распределения — интегральная или весовая (0). Нажимаем на кнопку ОК.

В ячейке А1 появляется искомое значение вероятности р = 0,25. Ровно 3 мальчика из 4 новорожденных могут появиться с вероятностью 0,25.

Если изменить формулировку условия задачи и выяснить вероятность того, что появится не более трех мальчиков, то в этом случае в рабочее поле Интегральный вводим 1 (вид функции распределения интегральный). Вероятность этого события будет равна 0,9375.

Задания для самостоятельной работы

2 . Нормальное распределение

Нормальное распределение - это совокупность объектов, в кото рой крайние значения некоторого признака — наименьшее и наибольшее — появ ляются редко; чем ближе значение признака к математическому ожиданию, тем чаще оно встречается. Например, распределение студентов по их весу приближается к нормальному распределению. Это распределение имеет очень широкий круг приложений в статистике, включая проверку гипотез.

Диаграмма нормального распределения симметрична относительно точки а (математического ожидания). Медиана нормального распределения равна тоже а. При этом в точке а функция f(x) достигает своего максимума, который равен

В Excel для вычисления значений нормального распределения используются функция НОРМРАСП, которая вычисляет значения вероятности нормальной функции распределения для указанного среднего и стандартного отклонения.

Функция имеет параметры:

НОРМРАСП (х; среднее; стандартное_откл; интегральная) , где:

х — значения выборки, для которых строится распределение;

среднее — среднее арифметическое выборки;

стандартное_откл — стандартное отклонение распределения;

интегральный — логическое значение, определяющее форму функции. Если интегральная имеет значение ИСТИНА(1), то функция НОРМРАСП возвращает интегральную функцию распределения; если это аргумент имеет значение ЛОЖЬ (0), то вычисляет значение функция плотности распределения.

Если среднее = 0 и стандартное_откл = 1, то функция НОРМРАСП возвращает стандартное нормальное распределение.

Пример 2 . Построить график нормальной функции распределения f ( x ) при x , меняющемся от 19,8 до 28,8 с шагом 0,5, a =24,3 и

1. В ячейку А1 вводим символ случайной величины х, а в ячейку B 1 — символ функции плотности вероятности — f ( x ) .

2. Вводим в диапазон А2:А21 значения х от 19,8 до 28,8 с шагом 0,5. Для этого воспользуемся маркером автозаполнения: в ячейку А2 вводим левую границу диапазона (19,8), в ячейку A3 левую границу плюс шаг (20,3). Выделяем блок А2:А3. Затем за правый нижний угол протягиваем мышью до ячейки А21 (при нажатой левой кнопке мыши).

3. Устанавливаем табличный курсор в ячейку В2 и для получения значения вероятности воспользуемся специальной функцией — нажимаем на панели инструментов кнопку Вставка функции ( fx ) . В появившемся диалоговом окне Мастер функций - шаг 1 из 2 слева в поле Категория указаны виды функций. Выбираем Статистическая. Справа в поле Функция выбираем функцию НОРМРАСП. Нажимаем на кнопку ОК.

4. Появляется диалоговое окно НОРМРАСП. В рабочее поле X вводим адрес ячейки А2 щелчком мыши на этой ячейке. В рабочее поле Среднее вводим с клавиатуры значение математического ожидания (24,3). В рабочее поле Стандартное_откл вводим с клавиатуры значение среднеквадратического отклонения (1,5). В рабочее поле Интегральная вводим с клавиатуры вид функции распределения (0). Нажимаем на кнопку ОК.

5. В ячейке В2 появляется вероятность р = 0,002955. Указателем мыши за правый нижний угол табличного курсора протягиванием (при нажатой левой кнопке мыши) из ячейки В2 до В21 копируем функцию НОРМРАСП в диапазон В3:В21.

6. По полученным данным строим искомую диаграмму нормальной функции распределения. Щелчком указателя мыши на кнопке на панели инструментов вызываем Мастер диаграмм. В появившемся диалоговом окне выбираем тип диаграммы График, вид — левый верхний. После нажатия кнопки Далее указываем диапазон данных — В1:В21 (с помощью мыши). Проверяем, положение переключателя Ряды в: столбцах. Выбираем закладку Ряд и с помощью мыши вводим диапазон подписей оси X: А2:А21. Нажав на кнопку Далее, вводим названия осей Х и У и нажимаем на кнопку Готово.

Рис. 1 График нормальной функции распределения

Получен приближенный график нормальной функции плотности распределения (см. рис.1).

Задания для самостоятельной работы

1. Построить график нормальной функции плотности распределения f ( x ) при x , меняющемся от 20 до 40 с шагом 1 при

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

Еще одним аспектом использования законов распределения вероятностей являет ся генерация случайных величин. Бывают ситуации, когда необходимо получить последовательность случайных чисел. Это, в частности, требуется для моделирования объектов, имеющих случайную природу, по известному распределению вероятно стей.

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

В MS Excel для генерации СВ используются функции из категории Математические :

СЛЧИС () – выводит на экран равномерно распределенные случайные числа больше или равные 0 и меньшие 1;

СЛУЧМЕЖДУ (ниж_граница; верх_граница) – выводит на экран случайное число, лежащее между про извольными заданными значениями.

В случае использования процедуры Генерация случайных чисел из пакета Анализа необходимо заполнить следующие поля:

- число переменных вводится число столбцов значений, которые необходимо разместить в выходном диапазоне. Если это число не введено, то все столбцы в выходном диапазоне будут заполнены;

- число случайных чисел вводится число случайных значений, которое необ ходимо вывести для каждой переменной, если число случайных чисел не будет введе но, то все строки выходного диапазона будут заполнены;

- в поле распределение необходимо выбрать тип распределения, которое следует использовать для генерации случайных переменных:

1. равномерное - характеризуется вер x ней и нижней границами. Переменные из влекаются с одной и той же вероятностью для всех значений интервала.

2. нормальное — характеризуется средним значением и стандартным отклонени ем. Обычно для этого распределения используют среднее значе ние 0 и стандартное отклонение 1.

3. биномиальное — характеризуется вероятностью успеха (величина р) для неко торого числа попыток. Например, можно сгенерировать случайные двухальтер нативные переменные по числу попыток, сумма которых будет биномиальной случайной переменной;

4. дискретное — характеризуется значением СВ и соответствующим ему интервалом вероятности, диапазон должен состоять из двух столбцов: левого, содержаще го значения, и правого, содержащего вероятности, связанные со значением в дан ной строке. Сумма вероятностей должна быть равна 1;

5. распределения Бернулли, Пуассона и Модельное.

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

Пример 3. Повар столовой может готовить 4 различных первых блюда (уха, щи, борщ, грибной суп). Необходимо составить меню на месяц, так чтобы первые блюда чередовались в случайном порядке.

1. Пронумеруем первые блюда по порядку: 1 — уха, 2 — щи, 3 — борщ, 4 — грибной суп. Введем числа 1-4 в диапазон А2:А5 рабочей таблицы.

2. Укажем желаемую вероятность появления каждого первого блюда. Пусть все блюда будут равновероятны (р=1/4). Вводим число 0,25 в диапазон В2:В5.

4. Указываем выходной диапазон и нажимаем ОК. В столбце С появляются случайные числа: 1, 2, 3, 4.

Задание для самостоятельной работы

1. Сформировать выборку из 10 случайных чисел, лежащих в диапазоне от 0 до 1.

2. Сформировать выборку из 20 случайных чисел, лежащих в диапазоне от 5 до 20.

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

4. Составить расписание внеклассных мероприятий на неделю для случайного проведения: семинаров, интеллектуальных игр, КВН и спец. курса.

5. Составить расписание на месяц для случайной демонстрации на телевидении одного из четырех рекламных роликов турфирмы. Причем вероятность появления рекламного ролика №1 должна быть в два раза выше, чем остальных рекламных роликов.

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

В докладе ЮНЕСКО " Высшее образование в XXI веке: подходы и практические меры" (1998г.) отмечалось, что "в сфере высшего образования наблюдается теснейшее сближение, если не общность проблем, тенденций, задач и целей, заставляющихзабыватьо национальных и региональныхразличиях и специфике«[2]. Сложившиеся в настоящее время социально-экономическиеи социально-культурные условиятребуют подготовки специалиста, конкурентоспособного на рынке труда.

Для успешного функционированияспециалиста в высокотехнологическом обществе необходимо постоянное пополнениебагажа знаний, умений и навыков.Непрерывное образование- необходимость и требование современнойнаучно-технической цивилизации.

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

К настоящему времени исследователи пришлик единому мнению, что информационные технологии должны разрабатываться с учетом классических дидактических требований: принципа научности, доступности и посильной трудности, систематичности и последовательности, прочности усвоения, наглядности, связи теории с практикой, сознательности и активности (самостоятельности), принципа коллективного характера обучения и учета индивидуальных особенностейобучающихся, однако применительно к новым информационным технологиямониимеют свою специфику [3].

ПЭВМ наиболее полно удовлетворяет дидактическим требованиям и позволяет управлять процессом обучения, максимально адаптировать его киндивидуальнымособенностям обучаемого. Знания, полученные при компьютерном обучении, выступают в познавательной деятельностив качестве средстварешения профессиональных задачдеятельности специалиста.

Также исследователи выделяют возможные направлениявключениякомпьютера в процесс учебно-познавательной деятельности обучаемых: диагностика, обучающий режим, отработка умений и навыковпри решении задачпосле изучения темы, моделирование сложных процессов, графическая иллюстрация изучаемого материала, работа с базами данных [4].

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

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

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

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

Рассмотрим использование Excel при изученииразличных видовраспределений дискретных и непрерывных случайных величин.

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

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

Рассмотрим задание набиномиальное распределение:

Задание 1. Построить с помощью программы Excel , многоугольникбиномиального распределения для следующих параметров:

Используетсястатистическая функция БИНОМРАСПР:

Изменяя параметры распределения, проследить как изменяетсяконтур многоугольника распределения.

Задание 2. Работа уличного агента по приглашению потенциальных покупателей тайм-шер считаетсяудовлетворительной, если по его приглашению за день на презентацию придет более 10 покупателей. Считая, что вероятность того, что лицо, к которому агент обратится с предложением, с вероятностью 0,1 придет на презентацию, вычислить вероятность того, что работаагента будет признана удовлетворительной, если агент обратится с предложением к 40 прохожим.

Для задачи необходимо составление компьютерной модели, выполнение громоздкихрасчетовс помощью функции БИНОМРАСПР.

Для закрепленияраспределения Пуассона можно предложить задание 3.

Задание 3. Устройство состоит из 1000 элементов, работающих независимо один от другого. Вероятность отказа любого элемента в течение времени Т равно 0,002. Найти вероятность того, что за время Т откажут ровно k элементов. Построить график распределения вероятности k =0;1;2;3;4;5;6;7.

Используется функция ПУАССОН:

Рис.2Многоугольник распределения Пуассона ( l =2)

После построения графика целесообразно поставить вопрос : от какого параметра зависит распределение Пуассона?

Задание 4 и 5 на гипергеометрическое распределение, выполнение расчетов и построение графика.

Задание 4. В лотерее "Спортлото 6 из 45 «денежные призы получают участники, угадавшие 3, 4, 5 и 6 видов спорта из отобранныхслучайно 6 видов из 45. Найти закон распределения случайной величины Х- числа угаданных видов спорта среди случайно отобранных шести. Какова вероятность получения денежного приза? Найти математическое ожидание и дисперсию случайной величины Х.

В задании 4строитсямодель, через функцию ГИПЕРГЕОМЕТвыполняются громоздкие вычисления длярядаэтого распределения.

Задание 5. Построить графики гипергеометрического распределения для следующих значений параметров:

Нормальный закон распределенияприменяется взаданиях 6 и 7.

З адание 6.Построить кривую Гаусса для:

а) а=2; s =2; б )а =2; s =1; в)а=2; s =0,5. Сделать выводы по графикам. Найти площадь под каждой кривой Гаусса.

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

Задание 7. Полагая, что рост мужчин определенной возрастнойгруппы есть нормально распределеннаяслучайная величина Х с параметрами а=173, , найти:

а) выражение плотности вероятности и функции распределения случайной величины Х;

b ) доли костюмов 4-го роста (176- ) и 3-го роста (170-176см), которыенужно предусмотреть в общем объеме производства для данной возрастной группы;

с) квантиль и 10 % -ную точку случайной величины Х.

В задании 7 необходимо построитьмодель нормального распределения, вероятность попадания в интервал,применить правиланахождения квантиля .

Задание 8. Непрерывная случайная величина Храспределенапо показательному закону, заданному при плотностьюраспределения ; при x f ( x )=0. Найти вероятность того, что в результате испытания Х попадает в интервал (0,13; 0,7 ) . Построить кривую распределения играфик функциираспределения .

Рис. 3График плотности экспоненциального распределения ( l =3)

Рис.4График интегральной функции экспоненциального распределения ( l =3)

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

Исследователи считают, что применение информационных технологийсущественным образомпреобразует мыслительную деятельность человека [6]. Формируется не только логическое, но икритическое мышление — качества, необходимыедля выработкинового стилямышления, приэтом повышаетсяобщий уровень интеллектуальной деятельности. .

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

3. Сережкина А.Е., Садыкова В.А. Обучение в новой информационной среде: психолого-педагогические особенности//Высшее образование сегодня. — 2004. -.№ 1. — с.54-59.

4. Бекбаева З. Роль и функции средств информационной технологии в активизации самостоятельной учебно-познавательной деятельности учащихся.// Поиск, серия ест . н аук. −2001. -№ 6, — с.113-119.

5. Кремер Н.Ш. Теория вероятностейи математическая статистика: Учебник для вузов. — М.: ЮНИТИ-ДАНА, 2006.-573 с.

6. Тихомиров О.К. Бабанин Л.Н. ЭВМи новые проблемы психологии. М.: Изд-во Моск.ун-та, 1986.

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

Развивающие: развитие познавательной активности, творческих способностей учащихся, умения сопоставлять, анализировать, развить навыки ввода данных в ячейки с использованием автозаполнения и вычисления итоговых значений с помощью статистических функций табличного процессора MS Excel;

Воспитательные: способствовать воспитанию у учащихся аккуратности, усидчивости, навыков учебного труда, интереса к предмету.

Организационный момент.

Актуализация опорных знаний. Опрос.

Как определить среднее арифметическое ряда?
Что называется модой ряда?
Как определить размах ряда?
Что называется медианой ряда?
Что такое адрес ячейки и из чего он состоит?
Чем абсолютный адрес отличается от относительного?
Что такое автозаполнение и как им пользоваться?
Что такое функция Excel?

Решить задачу:

Администрация школы решила проверить математическую подготовку учащихся 8 класса. С этой целью был составлен тест, содержащий 9 заданий. Работу выполняли 40 учащихся школы. При проверке каждой работы учитель отмечал число верно выполненных заданий. В результате был составлен такой ряд чисел:

Определить сколько заданий в среднем выполнил каждый ученик верно?
Найти разницу в числе верно выполненных заданий между учащимися.
Чему равна мода и медиана данного ряда?

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

Чтобы ответить на вопросы задачи необходимо упорядочить ряд данных, а при нахождении статистических характеристик ряда нам помогут на доске записанные статистические функции Excel, которые используются для нахождения среднего арифметического, размаха, моды и медианы рядов чисел:

=МЕДИАНА(аргументы) – медиана ряда;

=СРЗНАЧ(аргументы) – среднее арифметическое ряда;

=МОДА(аргументы) – мода ряда;

=МАКС(аргументы)-МИН(аргументы) – размах ряда.

Закрепление материала

Учащиеся получают листки с задачами, рассаживаются за компьютеры и решают их с помощью Excel. Для решения задач необходим файл (Приложение1)

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

б) сколько денег получает ежемесячно “средний” человек из этой группы (найдите медиану этих данных)?

в) какой заработок наиболее распространен у членов этой группы (найдите моду этих данных)?

Среднее выборки или выборочное среднее (sample average, mean) представляет собой среднее арифметическое всех значений выборки.

В MS EXCEL для вычисления среднего выборки можно использовать функцию СРЗНАЧ() . В качестве аргументов функции нужно указать ссылку на диапазон, содержащий значения выборки.

Примечание: О вычислении доверительных интервалов при оценке математического ожидания можно прочитать, например, в статье Доверительный интервал для оценки среднего (дисперсия известна) в MS EXCEL.

Некоторые свойства среднего арифметического:

Сумма всех отклонений от среднего значения равна 0:

Если к каждому из значений x_i прибавить одну и туже константу с, то среднее арифметическое увеличится на такую же константу;
Если каждое из значений x_i умножить на одну и туже константу с, то среднее арифметическое умножится на такую же константу.

Математическое ожидание

Примечание: В англоязычной литературе имеется множество терминов для обозначения математического ожидания: expectation, mathematical expectation, EV (Expected Value), average, mean value, mean, E[X] или first moment M[X].

Если случайная величина имеет дискретное распределение, то математическое ожидание вычисляется по формуле:

где x_i – значение, которое может принимать случайная величина, а р(x_i) – вероятность, что случайная величина примет это значение.

Если случайная величина имеет непрерывное распределение, то математическое ожидание вычисляется по формуле:

где р(x) – плотность вероятности (именно плотность вероятности, а не вероятность, как в дискретном случае).

Для каждого распределения, из представленных в MS EXCEL, Математическое ожидание можно вычислить аналитически, как функцию от параметров распределения (см. соответствующие статьи про распределения). Например, для Биномиального распределения среднее значение равно произведению его параметров: n*p (см. файл примера ).

Функция СРОТКЛ в Excel используется для анализа числового ряда, передаваемого в качестве аргумента, и возвращает число, соответствующее среднему значению, рассчитанному для модулей отклонений относительно среднего арифметического для исследуемого ряда.

Примеры методов анализа числовых рядов в Excel

Смысл данной функции становится предельно ясен после рассмотрения примера. Допустим, на протяжении суток каждые 3 часа фиксировались показатели температуры воздуха. Был получен следующий ряд значений: 16, 14, 17, 21, 25, 26, 22, 18. С помощью функции СРЗНАЧ можно определить среднее значение температуры – 19,88 (округлим до 20).

Для определения отклонения каждого значения от среднего необходимо вычесть из него полученное среднее значение. Например, для первого замера температуры это будет равно 16-20=-4. Получаем ряд значений: -4, -6, -3, 1, 5, 6, 2, -2. Поскольку СРОТКЛ по определению работает с модулями отклонений, итоговый ряд значений имеет вид: 4, 6, 3, 1, 5, 6, 2, 2. Теперь нужно получить среднее значение для данного ряда с помощью функции СРЗНАЧ – примерно 3,63. Именно таков алгоритм работы рассматриваемой функции.

Пример 1. Имеются два ряда значений, представляющих собой результаты наблюдений одного и того же физического явления, сделанные в ходе двух различных экспериментов. Определить, среднее отклонение от среднего значения результатов для какого эксперимента является максимальным?

Вид таблицы данных:

Используем следующую формулу:

Сравниваем результаты, возвращаемые функцией СРОТКЛ для первого и второго ряда чисел с использованием функции ЕСЛИ, возвращаем соответствующий результат.

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

Формула расчета линейного коэффициента вариации в Excel

Пример 2. Студенты сдали экзамены по различным предметам. Определить число студентов, которые удовлетворяют следующему критерию успеваемости – линейный коэффициент вариации оценок не превышает 15%.

Вид таблицы данных:

Линейный коэффициент вариации определяется как отношение среднего отклонения к среднему значению. Для расчета используем следующую формулу:

Растянем ее вниз по столбцу и получим следующие значения:

Для определения числа неуспешных студентов по указанному критерию используем функцию:

Правила использования функции СРОТКЛ в Excel

Функция имеет следующий синтаксис:

=СРОТКЛ( число1 ;[число2];. )

1. Вычислить математическое ожидание:

1) Пуск > Все программы > Microsoft Office > Microsoft Excel

2. Вычислить дисперсию:

Вводим =, далее – fx, “Статистические” – “ДИСП”, выделить числовые данные нашей исходной таблицы.

3. Среднее квадратичесое отклонение (не смещённое):

Вводим =, далее – fx, “Статистические” – “СТАНДТОТКЛОН”, выделить числовые данные нашей исходной таблицы.

4. Среднее квадратическое отклонение (смещённое):

Вводим =, далее – fx, “Статистические” – “СТАНДТОТКЛОН”, выделить числовые данные нашей исходной таблицы.

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

Читайте также: