Вероятность и стастистика

Задание 1.

Откройте лист «Задание 1». В ячейках А2–А101 содержатся данные о напряжении в электросети, полученные при помощи бытового вольтметра в течение дня в отдельные моменты (всего 100 измерений). Выполните следующие пункты.

1. Средние значения (4 балла).

1.1. Найдите меры центральной тенденции массива.

Для этого в ячейки E1–E4 впишите формулы для среднего арифметического (СРЗНАЧ (англ. AVERAGE)), медианы (МЕДИАНА (англ. MEDIAN)), наименьшего (МИН (MIN)) и наибольшего (МАКС (MAX)) значений и укажите (выделите) соответствующий диапазон ячеек в скобках.

2. Группировка и нахождение частот (15 баллов).

2.1. В ячейках Н2–Н22 содержатся значения напряжения от 220 (минимум) до 240 (максимум) вольт. Нужно найти, сколько раз вольтметр зафиксировал каждое из этих значений — заполнить ячейки I2–I22. Для этого воспользуйтесь функцией СЧЁТЕСЛИ (англ. COUNTIF). Синтаксис функции в Excel: =СЧЁТЕСЛИ(диапазон; число) (6 баллов).

2.2. Найдите относительные частоты каждого события в процентах — заполните ячейки J2–J22. Используйте для вычислений соответствующую формулу (5 баллов).

2.3 В ячейках I23–I24 вычислите, сколько значений больше и меньше номинального значения напряжения (230 B), а в ячейках J23–J24 найдите относительную частоту этих величин (4 балла).

3. Диаграмма (8 баллов).

Постройте столбчатую диаграмму для массива частот (ячейки J2–J22). По горизонтальной оси выберите значения напряжения, а по вертикальной — соответствующие относительные частоты. Подпишите оси.

4. Вывод (16 баллов).

По следующему плану постройте вывод и запишите его в ячейке H47.

По форме построенной гистограммы сделайте вывод о характере изменчивости напряжения в сети (4 балла).
Сравните относительную частоту значений напряжения, которые больше и меньше номинального. О чём говорит результат (4 балла)?
Насколько сильно среднее значение и медиана отличаются от номинального значения напряжения (230 В)? Можно ли сказать, что изменчивость является случайной, или есть систематические отклонения (4 балла)?
Объясните, с чем, по вашему мнению, могут быть связаны отклонения напряжения в сети (4 балла).


Задание 2.

Откройте лист «Данные для заданий». В ячейках A2–A293 содержатся результаты измерений диаметра арахиса в шоколаде. Измерения выполнялись с помощью штангенциркуля с точностью до 0,1 мм.

Скопируйте этот массив данных на лист «Задание 2» в ячейки В2–В293. В ячейках Е1–Е5 автоматически появятся меры центральной тенденции, вычисленные для данного диапазона значений (2 балла).

Проанализируйте гистограмму в зависимости от шага группировки. Для этого в ячейку Е6 запишите сначала значение 0,2. В ячейке Е7 появится количество интервалов, а сами интервалы появятся в столбцах G — I. В столбце K появится массив частот. Слева появится гистограмма.

Проделайте то же самое, изменив значение шага группировки в ячейке Е6 на 0,6 и 1. Оставьте в ячейке E6 более подходящий шаг группировки (1 балл).

Вопросы.

В ячейке М25 : V35 ответьте на следующие вопросы.

А. Какой из трёх шагов группировки, по вашему мнению, подходит лучше всего для описания характера изменения размеров конфеток? Ответ обоснуйте (5 баллов).

Б. Что, по вашему мнению, влияет на изменчивость размеров конфеток (6 баллов)?

В. Найдите с помощью Excel относительную частоту событий «диаметр конфеток равен 19 мм» и «диаметр конфеток равен 18 мм» (6 баллов).

Г. Будем считать, что номинальный диаметр арахиса в шоколаде равен 19 мм. Можно ли на основании наших измерений считать, что при покупке упаковки этих конфеток шансы событий «достать конфетку с диаметром больше номинального» и «достать конфетку с диаметром меньше номинального» примерно равны? Ответ обоснуйте (8 баллов).