Аргументы (Аргумент. Значение, предоставляющее информацию для действия, события, метода, свойства, функции или процедуры.) функции ВПР описаны ниже.
Искомое_значение Значение, которое должно быть найдено в первом столбце таблицы или диапазона. Аргумент искомое_значение может быть значением или ссылкой. Если искомое_значение меньше, чем наименьшее значение в первом столбце аргумента таблица, функция ВПР возвращает значение ошибки #Н/Д.
Таблица Диапазон ячеек, содержащий данные. Можно использовать ссылку на диапазон (например, A2:D8) или имя диапазона. Значения в первом столбце аргумента таблица — это значения, в которых выполняется поиск аргумента искомое_значение. Эти значения могут быть текстовыми, числовыми или логическими. Текстовые значения в нижнем и верхнем регистре считаются эквивалентными.
Номер_столбца Номер столбца в аргументе таблица, из которого возвращается совпадающее значение. Если номер_столбца равен 1, то возвращается значение из первого столбца аргумента таблица; если номер_столбца равен 2, — значение из второго столбца аргумента таблица и т. д.
Рис.4.1.
Решение приведено на рис.4.2.
Рис.4.2.
Все остальные расчеты аналогичны примерам 1 и 2.
Пример 5.
Пусть стрелок стреляет в мишень, координаты центра которой совпадает с началом координат. Мишень имеет три зоны - три концентрических окружности с радиусами равными 1, 2 и 3. Провести имитацию стрельбы, полагая, что точка прицеливания это центр мишени, а точка попадания определяется двумя непрерывными СВ X и φ. Первая величина определяет расстояние, на которое удалена точка попадания от точки прицеливания, вторая величина φ равна углу между направлением оси ОХ и направлением на точку попадания.
y
r
При этом будем полагать, что эта величина X имеет нормальное распределение с параметрами m ,σ , т.е. X~N(m ,σ). Расстояние от точки прицеливания до точки попадания равно . Будем считать, что m=0 и σ =1, т.е. величина X имеет стандартное нормальное распределение.
Будем также полагать, что величина φ имеет равномерное распределение в интервале
Построить имитационную модель, моделирующую процесс многократной стрельбы. Вычислить количество попаданий в каждую из зон мишени, их долю от общего количества повторений. Найти среднее значение величины X (расстояние с учетом знака!) попаданий и средний разброс относительно среднего.
Построить интервальный вариационный ряд.
Для этого разбить диапазон экспериментальных значений на nint=20 частей.
Построить график.
Расчет провести в MS Excel, повторив имитацию не менее 500 раз. Сравнить полученные результаты с соответствующими теоретическими характеристиками.
Решение:
Координаты точки попадания определяется равенствами
Для имитации значения воспользуемся функцией «СЛЧИС()». Поскольку СЛЧИС() вычисляет значения равномерно распределенной случайной величины в интервале [0;1]. то величина , равная имеет равномерное распределение в интервале .
Как известно, что СВ Х имеет нормальное распределение, если ее плотность вероятности имеет вид:
, (2.13)
где
m и σ параметры распределения, удовлетворяющие соотношениям .
Генерация нормально распределенной СВ в MS Excel можно осуществить с помощью инструмента «Генерация случайных чисел» который находится в надстройке (модуль) «Пакет анализа». Надстройка (модуль) «Пакет анализа» MS Excel предназначен для выполнения базовых операций статистического анализа. Полученные с его помощью результаты не обновляются при изменении исходных данных, поэтому после их изменения для обновления результатов требуется снова выполнить соответствующую команду.
Для активизации команд выполните команду меню Сервис →Анализ данных.Если эта команда недоступна, загрузите Пакет анализа(Сервис→ Надстройки).
Рис.5.2.
Вставить все рисунки и таблицы из файла 5.xls!
Задание (повышенной трудности!) сгенерировать Нормально распр СВ с помощью (http://ru.wikipedia.org/wiki/%D0%9D%D0%BE%D1%80%D0%BC%D0%B0%D0%BB%D1%8C%D0%BD%D0%BE%D0%B5_%D1%80%D0%B0%D1%81%D0%BF%D1%80%D0%B5%D0%B4%D0%B5%D0%BB%D0%B5%D0%BD%D0%B8%D0%B5)
, преобразование Бокса — Мюллера
центральную предельную теорему.
на центральной предельной теореме. Именно, если сложить много независимых одинаково распределённых величин с конечной дисперсией, то сумма будет распределенапримерно нормально. Например, если сложить 12 независимых базовых случайных величин, получится грубое приближение стандартного нормального распределения. Тем не менее, с увеличением слагаемых распределение суммы стремится к нормальному.
Использование точных методов предпочтительно, поскольку у них практически нет недостатков. В частности, преобразование Бокса — Мюллера является точным, быстрым и простым для реализации методом генерации.
При этом будем полагать, что эта величина X имеет нормальное распределение с параметрами m ,σ , т.е. X~N(m ,σ). Расстояние от точки прицеливания до точки попадания равно 
. Будем считать, что m=0 и σ =1, т.е. величина X имеет стандартное нормальное распределение.
воспользуемся функцией «СЛЧИС()». Поскольку СЛЧИС() вычисляет значения равномерно распределенной случайной величины в интервале [0;1]. то величина 
имеет равномерное распределение в интервале 
, (2.13)
.
