Для получения случайных чисел в Microsoft Excel используется функция CЛЧИС( ), не имеющая аргументов и дающая в качестве значения случайное число на интервале от 0 до 1 (распределение равномерное). Следует обратить внимание, что все случайные значения обновляются при внесении любых изменений в любые ячейки таблицы.
Порядок выполнения работы:
1. Заполнить случайными значениями, означающими время, через которое появится каждый из автобусов, три последовательных столбца. Это время вычисляется по формуле Т=СЛЧИС( )*DТ, где DТ – интервал следования автобуса. Для анализа следует набрать не менее тысячи значений (рекомендуется 5000) в каждом столбце. Интервал следования каждого автобуса следует предварительно записать в некоторую ячейку, чтобы, меняя его значение, можно было легко наблюдать за изменением конечного результата.
2. В следующем столбце вычислить время появления ближайшего автобуса. Для этого можно использовать функцию МИН( ). В качестве ее аргумента указывается набор ячеек, среди которых эта функция находит минимальное значение.
3. Используя функцию СРЗНАЧ( ), вычислить среднее значение времени ожидания автобуса.
4. Вычислить вероятности того, что автобус появится через 0-1мин, 1-2мин,…, 9-10мин. Для этого:
А) Набрать столбец условий, записанных в виде “>0”, “>1”, … “>9”, “>10”
Б) в соседнем столбце с помощью функции СЧЕТЕСЛИ( ) подсчитать число случаев, когда автобус приехал позже, чем через 0, 1, 2, … минуты.
В) В соседнем столбце вычислить число случаев, когда автобус прибыл в интервале 0-1мин, 1-2мин, 2-3мин, … , 9-10мин. Это можно сделать, вычислив разность между соседними значениями предыдущего столбца. Разделив эти разности на полное число испытаний (записанное в ячейке, в которой подсчитано число случаев при условии “>0”), получим вероятность прихода автобуса в выбранный интервал времени.
5. Построить точечную диаграмму P1(T), отображающую зависимость от времени ожидания вероятности того, что автобус придет в течение определенной минуты. Для этого предварительно набрать столбец значений T = 0.5, 1.5, 2.5, … , 9.5 . Это будут середины интервалов времени, т. е. значения, откладываемые по оси Х.
6. Построить точечную диаграмму P2(T), отображающую вероятность того, что пассажир будет ждать автобуса больше, чем Т минут (предварительно нужно набрать столбец значений Т от 0 до 10).
7. Меняя интервалы следования автобусов, наблюдать за изменением графиков и величины среднего времени ожидания.
Получение и использование в моделировании случайных чисел,
имеющих нормальное распределение.
В ходе работы требуется средствами электронных таблиц Microsoft Excel получить ряд случайных чисел, имеющих нормальное распределение. Параметры этого распределения – среднее значение и стандартное отклонение - должны быть заданы в отдельных ячейках, чтобы, меняя значения этих параметров, можно было увидеть их влияние на функцию распределения. Изменение вида распределения следует наблюдать по графику, на котором для каждого целого значения (например, на интервале [-10,20] ) отмечено число таких значений, полученных в результате округления анализируемой случайной величины. Во второй части работы моделируется попадание 10 выстрелов в мишень. Предполагается, что центр попадания выстрелов смещен в точку мишени (X0,Y0), а отклонения от этой точки распределены нормально со средним значением, равным нулю (т. е., в плюс и в минус равновероятно). Угол, определяющий направление, в котором произошло отклонение, распределен равномерно от 0 до 360о.
Порядок выполнения работы.
1. Ввести в выбранные ячейки значения среднего и стандартного отклонения.
2. С помощью функции СЛЧИС( ) вычислить столбец равномерно распределенных случайных чисел (Для получения сравнительно гладкой кривой взять не менее 1000 чисел).
3. Вычислить с помощью функции НОРМОБР( ) столбец случайных чисел, имеющих нормальное распределение. Первым аргументом функции НОРМОБР( ) указывается равномерно распределенное случайное число (адрес ячейки, в которой оно записано), вторым и третьим аргументом, соответственно, указываются среднее и стандартное отклонение требуемого распределения.
4. Построить график распределения полученных случайных чисел. Для этого:
А) С помощью функции ОКРУГЛ( ) набрать столбец округленных случайных чисел (так, чтобы оставалась только целая часть). Первым аргументом функции ОКРУГЛ( ) указывается округляемое число (ячейка), вторым аргументом – число оставляемых десятичных знаков (нужно взять 0 ).
Б) Набрать столбец целых чисел от –10 до 20.
В) С помощью функции СЧЕТЕСЛИ( ) набрать столбец, в котором для каждого из значений (от –10 до 20) определено, сколько раз оно встречается среди округленных случайных чисел.
Г) Изобразить полученную зависимость на точечной диаграмме. По оси Х откладывается значение, по оси Y – сколько раз оно встречается.
5. Меняя значения среднего и стандартного отклонения, наблюдать изменение графика распределения.
6. Ввести в некоторые ячейки значения X0 и Y0 – центр попаданий выстрелов в мишень.
7. Набрать столбец номера выстрела (от 1 до 30).
8. Набрать столбец R величины отклонения. В этот столбец можно перенести нормально распределенные случайные числа, полученные в п.3. При этом следует в качестве среднего указать 0.
9. Вычислить, умножая СЛЧИС( ) на 2p, угол a отклонения в радианах (значение p дает функция ПИ( ) ).
10. Вычислить, используя R и a, координаты Х и Y попадания в мишень. Для этого использовать формулы: Х=X0+R×cos(a) и Y=Y0+R×sin(a)
11. Построить диаграмму Y(X). Выбрать в качестве маркера круг, отмечающий место попадания.
12. Меняя X0, Y0 и стандартное отклонение, наблюдать изменение результата моделирования.
Определение параметров линейной зависимости методом наименьших квадратов.
В ходе работы моделируется проведение некоторого естественнонаучного эксперимента. Предположим, что существует величина Y, которая, согласно какому-либо закону физики или др. науки линейно зависит от величины Х (назовем этот закон «теоретической» зависимостью). Прибор, производя измерения, дает результат с некоторой погрешностью, в результате чего полученные экспериментальные точки не лежат строго на одной прямой. По этим экспериментальным точкам можно подобрать «экспериментальную» прямую методом наименьших квадратов, однако она не будет совпадать с «теоретической». Цель данной работы: проводя моделирование, наглядно продемонстрировать, как величина погрешностей эксперимента влияет на отличие «экспериментальной» прямой от «теоретической».
Строго говоря, погрешности естественнонаучного эксперимента чаще всего имеют нормальное распределение. В этой работе для упрощения её выполнения предполагается, что погрешности распределены равномерно. Это упрощение не оказывает существенного влияния на наглядность результата моделирования.
Порядок выполнения работы:
1) Набрать столбец значений величины Х, меняющейся от 30 до 150 с шагом 15
2) Набрать столбец «теоретических» значений Yтеор(X), вычисленных как результат строгой линейной зависимости Yтеор(X)=0,5X+50
3) В соседний столбец записать «экспериментальные» значения, вычисленные как сумма теоретических значений и погрешностей эксперимента, смоделированных при помощи функции СЛЧИС( ). Функция СЛЧИС( ) принимает значения на интервале от 0 до 1, поэтому для получения величины погрешностей, моделирующей реальный эксперимент, из неё нужно вычесть 0,5, после чего умножить на некоторый коэффициент (например, K=40). Таким образом, формула будет выглядеть как Yэксп(Х)= Yтеор(X)+K*(СЛЧИС( )-0,5). Коэффициент К следует записать в отдельную ячейку электронной таблицы, чтобы, меняя значение К, можно было видеть влияние погрешностей на результат.
4) Вычислить методом наименьших квадратов параметры А и В линейной зависимости Y(X)=A*X+B, вычисленной на основании экспериментальных точек. Для этого используйте функции НАКЛОН( ) (коэффициент A)и ОТРЕЗОК( ) (коэффициент В). Обе эти функции в качестве аргументов используют наборы ячеек, в которых записаны значения Х и Y.
5) Вычислить столбец значений, рассчитанных как Yнаим. кв.(Х)=A*X+B с использованием полученных коэффициентов.
6) Построить график (точечную диаграмму), на котором должны присутствовать Yэксп(Х), Yнаим. кв.(Х) и Yтеор(X). Экспериментальные точки изобразить в виде точек (только маркеры без линии), а теоретический и вычисленный методом наименьших квадратов графики – в виде прямых линий различного цвета без маркеров.
7) Меняя значение коэффициента К, влияющего на величину погрешностей, наблюдать за различием теоретической прямой и прямой, рассчитанной методом наименьших квадратов.
Проверка существования зависимости успеваемости от посещаемости.
В двух текстовых файлах, имя и размещение которых следует уточнить у преподавателя, записаны, соответственно, данные об успеваемости и посещаемости уроков в 10-х и в 11-х классах в виде семи столбцов. Первые 6 столбцов в каждом из файлов содержат оценки за полугодие по алгебре, физике, биологии, литературе, русскому языку и английскому языку (предметы подписаны сверху). В последнем столбце приведено число уроков, пропущенных соответствующим учеником за полугодие.
Порядок выполнение работы:
1. Собрать в одной таблице Excel данные одновременно по 10-м и по 11-м классам, открыв соответствующие текстовые файлы и скопировав из них через буфер обмена данные в созданную перед этим пустую таблицу. Внесение каких-либо изменений в исходные файлы не допускается!
2. Вычислить для каждого из учеников среднюю оценку по всем предметам, используя явную запись формулы или вставку функции СРЗНАЧ ( ). Эти данные следует поместить в свободном столбце, находящемся справа от столбцов данных.
3. Вычислить коэффициент корреляции посещаемости и средней оценки по всем предметам. Для этого используется функция КОРРЕЛ( ). В качестве ее аргументов указываются столбцы (диапазоны ячеек), содержащие число пропущенных уроков и среднюю оценку.
4. Сделать вывод о присутствии или отсутствии зависимости. Для этого следует использовать таблицу значимости результата в зависимости от полученной величины коэффициента корреляции и количества экспериментальных точек. В данной работе мы имеем большое количество точек, выходящее за рамки имеющейся таблицы значимости, поэтому для анализа нужно использовать экстраполяцию по графику:
А) Перенести данные из таблицы в электронную таблицу Excel
Б) Построить графики зависимости величины значимости от числа экспериментальных точек для различных значений коэффициента корреляции.
В) Для удобства экстраполяции выбрать по оси ординат логарифмическую шкалу. Также следует выбрать удобные пределы шкалы по осям Х и Y.
Г) Определить по графику искомую величину значимости, мысленно продолжая соответствующие кривые в нужную область.
5. Вычисляя аналогично п.3 коэффициенты корреляции числа пропущенных уроков с данными из других столбцов, сделать вывод о том, по каким дисциплинам зависимость наиболее сильная, а по каким – наиболее слабая.
6. Построить двумерную диаграмму, по оси х которой отложить число пропущенных уроков, а по оси у – среднюю оценку ученика.
7. Вычислить методом наименьших квадратов, на сколько баллов уменьшается средняя оценка у ученика, пропустившего 100 уроков. Поскольку предполагается линейная зависимость вида у=Ах+В, то коэффициент А вычисляется при помощи вызова функции НАКЛОН( ), имеющей в качестве аргументов столбец Y и столбец Х, а коэффициент В – при помощи вызова функции ОТРЕЗОК( ), имеющей те же аргументы.
8. На график зависимости успеваемости от посещаемости нанести прямую линию, вычисленную из столбца х по формуле у=Ах+В, где в качестве А и В использованы коэффициенты, полученные в п.7.
9. Проверить, как изменятся коэффициенты корреляции, если исключить из рассмотрения тех учеников, которые пропустили более 100 уроков. Самый легкий способ проверки - найти и удалить соответствующие строчки данных. Более грамотный путь – использовать логическую функцию ЕСЛИ().
Приложения.
Оценки по математике, полученные абитуриентами на мехмате МГУ в 2004г
Через запятую указаны оценка по десятибалльной системе и номер выполняемого варианта.
8,6
2,5
2,5
6,6
8,8
2,7
2,6
2,8
8,6
8,7
8,6
8,8
8,7
9,7
7,6
2,8
2,6
8,5
6,8
6,5
2,7
6,7
6,5
6,8
7,6
6,5
7,8
6,6
8,6
2,7
2,7
7,8
7,5
8,5
9,7
8,6
9,8
6,5
8,8
6,8
2,8
6,6
9,7
2,6
7,7
2,8
2,7
8,5
9,6
6,5
2,6
2,8
7,8
6,6
7,7
2,5
2,7
6,8
7,5
2,7
8,5
2,6
7,7
6,8
6,7
6,5
8,7
2,8
6,7
6,5
8,5
7,8
7,7
6,6
7,5
6,5
8,6
6,6
9,8
2,6
2,5
2,8
6,7
2,6
2,6
7,6
6,7
2,5
2,6
9,6
2,6
9,6
2,7
7,6
2,6
6,8
2,6
9,8
2,6
8,7
6,5
2,5
7,7
2,7
7,7
6,7
8,7
6,5
8,8
6,6
8,6
7,5
2,7
8,6
6,5
2,7
8,7
6,6
2,7
6,8
8,5
2,6
8,7
2,6
6,7
8,6
2,5
2,8
9,5
7,5
8,6
2,8
6,8
8,8
6,7
6,7
2,8
8,8
2,8
2,8
6,7
2,5
6,6
2,7
2,5
7,5
2,7
7,8
2,5
2,8
7,5
6,6
2,6
2,8
8,7
7,5
8,5
Оценки за полугодие и число уроков, пропущенных учащимися 10-х и 11-х классов.
Алгебра | Биология | Русский язык | Английский язык | Литература | Физика | Пропущено уроков |
4 | 5 | 5 | 3 | 5 | 4 | 0 |
3 | 5 | 4 | 4 | 4 | 4 | 36 |
3 | 4 | 3 | 4 | 3 | 3 | 11 |
4 | 5 | 4 | 5 | 5 | 4 | 14 |
5 | 5 | 5 | 5 | 5 | 5 | 0 |
4 | 4 | 4 | 2 | 3 | 4 | 155 |
3 | 5 | 4 | 4 | 4 | 4 | 93 |
4 | 5 | 4 | 5 | 4 | 4 | 36 |
3 | 5 | 4 | 4 | 4 | 3 | 15 |
4 | 5 | 4 | 4 | 4 | 5 | 15 |
4 | 5 | 4 | 4 | 4 | 4 | 24 |
4 | 5 | 5 | 4 | 4 | 4 | 122 |
3 | 5 | 4 | 3 | 3 | 4 | 171 |
4 | 5 | 4 | 3 | 4 | 4 | 0 |
4 | 5 | 4 | 4 | 4 | 3 | 122 |
4 | 4 | 4 | 5 | 5 | 5 | 78 |
3 | 4 | 4 | 4 | 5 | 4 | 86 |
3 | 5 | 4 | 4 | 5 | 4 | 93 |
3 | 4 | 4 | 3 | 3 | 3 | 60 |
4 | 4 | 4 | 4 | 4 | 4 | 29 |
3 | 5 | 4 | 4 | 3 | 5 | 14 |
4 | 5 | 5 | 4 | 4 | 4 | 126 |
4 | 5 | 4 | 4 | 4 | 4 | 70 |
3 | 5 | 3 | 3 | 4 | 4 | 49 |
4 | 5 | 4 | 4 | 4 | 4 | 14 |
4 | 5 | 4 | 4 | 5 | 4 | 14 |
5 | 4 | 4 | 4 | 4 | 4 | 10 |
3 | 5 | 4 | 4 | 4 | 4 | 127 |
3 | 4 | 4 | 3 | 4 | 3 | 114 |
4 | 5 | 4 | 5 | 5 | 4 | 35 |
3 | 4 | 4 | 3 | 3 | 4 | 21 |
5 | 5 | 5 | 5 | 5 | 5 | 63 |
3 | 5 | 4 | 3 | 4 | 4 | 36 |
4 | 5 | 4 | 4 | 5 | 4 | 85 |
4 | 5 | 4 | 4 | 5 | 5 | 0 |
3 | 3 | 4 | 3 | 3 | 3 | 77 |
3 | 4 | 3 | 3 | 4 | 4 | 27 |
5 | 5 | 4 | 4 | 4 | 5 | 15 |
5 | 5 | 5 | 5 | 5 | 5 | 28 |
4 | 5 | 4 | 4 | 5 | 4 | 112 |
3 | 5 | 3 | 3 | 3 | 4 | 176 |
5 | 5 | 4 | 5 | 5 | 4 | 18 |
4 | 5 | 4 | 4 | 4 | 4 | 37 |
4 | 5 | 4 | 4 | 5 | 4 | 21 |
4 | 5 | 4 | 5 | 4 | 4 | 105 |
4 | 5 | 4 | 4 | 4 | 4 | 0 |
4 | 5 | 4 | 5 | 4 | 4 | 150 |
3 | 5 | 3 | 4 | 3 | 4 | 91 |
4 | 5 | 4 | 4 | 5 | 4 | 77 |
3 | 4 | 4 | 3 | 3 | 4 | 21 |
5 | 5 | 4 | 5 | 5 | 5 | 0 |
4 | 5 | 4 | 5 | 5 | 4 | 0 |
3 | 3 | 3 | 3 | 3 | 3 | 1 |
3 | 3 | 5 | 5 | 5 | 4 | 154 |
4 | 5 | 4 | 4 | 5 | 4 | 15 |
4 | 4 | 3 | 4 | 5 | 4 | 7 |
3 | 4 | 3 | 4 | 4 | 4 | 89 |
3 | 5 | 4 | 5 | 5 | 4 | 88 |
3 | 3 | 2 | 3 | 4 | 3 | 51 |
3 | 4 | 2 | 3 | 4 | 4 | 14 |
3 | 5 | 3 | 4 | 4 | 3 | 95 |
4 | 5 | 4 | 4 | 4 | 3 | 36 |
3 | 4 | 2 | 3 | 4 | 3 | 21 |
3 | 3 | 2 | 3 | 3 | 3 | 77 |
3 | 3 | 2 | 3 | 3 | 3 | 121 |
3 | 3 | 2 | 3 | 3 | 3 | 45 |
4 | 5 | 2 | 3 | 3 | 3 | 30 |
3 | 4 | 3 | 3 | 3 | 4 | 23 |
5 | 4 | 4 | 4 | 4 | 5 | 16 |
5 | 5 | 4 | 5 | 5 | 4 | 123 |
3 | 4 | 4 | 4 | 4 | 3 | 24 |
3 | 5 | 4 | 5 | 4 | 4 | 4 |
3 | 4 | 3 | 3 | 3 | 3 | 10 |
5 | 5 | 4 | 4 | 4 | 4 | 2 |
4 | 5 | 4 | 4 | 4 | 3 | 14 |
4 | 5 | 4 | 3 | 4 | 4 | 57 |
4 | 5 | 4 | 4 | 5 | 4 | 0 |
3 | 5 | 4 | 4 | 4 | 4 | 76 |
4 | 5 | 4 | 4 | 4 | 4 | 42 |
3 | 5 | 4 | 4 | 4 | 3 | 57 |
3 | 5 | 4 | 4 | 3 | 3 | 35 |
4 | 5 | 4 | 5 | 5 | 4 | 8 |
4 | 4 | 4 | 4 | 4 | 4 | 18 |
4 | 5 | 4 | 4 | 4 | 4 | 16 |
3 | 4 | 4 | 4 | 4 | 4 | 21 |
3 | 5 | 4 | 3 | 4 | 4 | 83 |
3 | 5 | 4 | 3 | 4 | 3 | 24 |
4 | 4 | 4 | 5 | 5 | 4 | 32 |
4 | 4 | 3 | 3 | 4 | 4 | 10 |
3 | 4 | 4 | 4 | 4 | 3 | 16 |
|
Из за большого объема этот материал размещен на нескольких страницах:
1 2 3 4 |
