Порядок выполнения I этапа работы

1. Откройте файл «1.xlsx». На листе «Таблицы для заполнения» находятся заготовки таблиц для отчёта, на листе «Образцы готовых таблиц» представлен образец отчёта с готовыми таблицами и графиками.

2. В столбец введите числа выборки x полученного варианта работы.

3. В ячейке под столбцом рассчитайте сумму числовых значений столбца:

a. выделите рамкой ячейку, предназначенную для записи суммы;

b. нажмите комбинацию клавиш «Alt+=» и убедитесь, что мерцающей рамкой правильно выделен диапазон суммируемых ячеек;

c. нажмите клавишу «Enter».

4. В специально предназначенной для этого ячейке рассчитайте медиану выборки:

a. включите Мастер функций щелчком по кнопке f_x;

b. включите категорию «Статистические»;

c. выберите функцию «МЕДИАНА» (если вы использовали эту функцию недавно, её можно найти в категории «10 недавно использовавшихся»);

d. щёлкните ОК;

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

f. щёлкните ОК.

5. Аналогично в соответствующих ячейках рассчитайте моду выборки (функция «МОДА») и среднее арифметическое значение (функция «СРЗНАЧ»). Эти функции также можно найти в категории «Статистические» или «10 недавно использовавшихся», в качестве аргумента выбирайте диапазон ячеек со значениями выборки.

6. Заполните столбец «» таблицы «Расчёт основных статистических характеристик выборки X»:

a. выделите рамкой первую ячейку столбца;

b. наберите в ячейку формулу вида «=B4-$C$20», где B4 – адрес первой ячейки столбца «x_i», $C$20 – абсолютная ссылка на ячейку С20, в которой в нашем примере находится вычисленное значение среднего арифметического значения:

i. наберите символ «=» (знак равенства);

ii. щёлкните мышкой по ячейке В4;

iii. наберите символ «–» (минус);

iv. щёлкните мышкой по ячейке С20, после чего нажмите на клавиатуре клавишу F4, чтобы сделать ссылку в формуле абсолютной (она не будет меняться при копировании ячейки);

v. нажмите клавишу «Enter»;

c. используя автозаполнение (или копирование) заполните по образцу первой остальные ячейки столбца.

7. Заполните столбец «» таблицы «Расчёт основных статистических характеристик выборки X»:

a. выделите рамкой первую ячейку столбца;

b. наберите в ячейку формулу вида «=C4^2» для возведения в квадрат значений предыдущего столбца:

i. наберите символ «=» (знак равенства);

ii. щёлкните мышкой по ячейке С4 (первая ячейка предыдущего столбца);

iii. наберите символ «^» (при английской раскладке клавиатуры комбинация клавиш «Shift+6») и цифру 2;

iv. нажмите клавишу «Enter»;

c. используя автозаполнение (или копирование) заполните по образцу первой остальные ячейки столбца.

8. В ячейке под столбцом рассчитайте сумму числовых значений столбца аналогично пункту 3.

9. В специально предназначенных ячейках рассчитайте дисперсию (функция «ДИСП») и среднее квадратическое (стандартное) отклонение (функция «СТАНДОТКЛОН») аналогично пункту 4. Эти функции также можно найти в категории «Статистические» или «10 недавно использовавшихся», в качестве аргумента выбирайте диапазон ячеек со значениями выборки.

10. В соответствующей ячейке рассчитайте стандартную ошибку среднего арифметического, для чего в ячейку введите формулу вида «=C29/КОРЕНЬ(СЧЁТ(B4:B13))», где в ячейке С29 находится среднее квадратическое отклонение, а в диапазоне ячеек B4:B13 – значения выборки. Как и в предыдущих случаях, при наборе формулы ссылки на ячейки лучше вводить не вручную, а щелчком мышкой по соответствующей ячейке или для выделения диапазона ячеек путём перетаскивания мышки от первой до последней ячейки диапазона, остальные символы формулы в данном случае проще будет вводить вручную. Формулы в таблицах Excel вводятся без пробелов!

11. В соответствующей ячейке рассчитайте коэффициент вариации, для чего в ячейку введите формулу вида «=C29/C20». Если после нажатия клавиши «Enter» результат будет показан не в процентах, ячейке необходимо присвоить процентный формат с двумя десятичными знаками:

a. щёлкните по ячейке правой кнопкой мышки и выберите пункт меню «Формат ячеек…»;

b. в открывшемся диалоговом окне «Формат ячеек» на вкладке «Число» выберите в списке «Числовые форматы» «Процентный», на счётчике «Число десятичных знаков» установите значение «2» (рис. 3);

c. щёлкните ОК.

 

 

Рисунок 3 – Диалоговое окно с необходимыми установками числового формата ячейки со значением коэффициента вариации

12. Для оценки степени разброса выборки в ячейку рядом с надписью «разброс» введите логическую формулу следующего вида: «=ЕСЛИ(C37<0,1;"малый";ЕСЛИ(C37>0,2;"большой";"средний"))», где в ячейке С37 находится значение коэффициента вариации. Данная формула будет способствовать автоматическому принятию решения о степени разброса выборки. Если значение коэффициента вариации окажется меньше 0,1 (10%), в ячейке появится надпись «малый», при значениях больше 0,2 (20%) будет написано «большой», во всех остальных случаях разброс будет оценен как «средний». Напоминание: ссылки на ячейку вводите щелчком по ячейке, формулу вводите без пробелов.

13. В соответствующей ячейке рассчитайте размах варьирования путём ввода в ячейку формулы вида «=МАКС(B4:B13)-МИН(B4:B13)», где B4:B13 ­ диапазон ячеек, содержащий значения выборки.

14. Для построения гистограммы и полигона распределения необходимо заполнить таблицу «Вариационный ряд результатов измерений». Перед заполнением таблицы рассчитайте шаг интервала в соответствующей ячейке, для чего в ячейку введите формулу вида «=C41/4». В ячейке С41 находится рассчитанное значение размаха варьирования. При объёме выборки n = 10 рекомендуемое количество интервалов k = 4 (см. табл. 3).

15. Заполните таблицу «Вариационный ряд результатов измерений»:

a. в группе столбцов «Границы интервала» нижнюю границу (левая ячейка) интервала №1 определите как минимальное значение выборки: «=МИН(B4:B13)» (данную формулу можно записать, воспользовавшись мастером функций, функция «МИН», возвращающая минимальное значение диапазона ячеек, находится в категории «Статистические» или «10 недавно использовавшихся»);

b. для нахождения верхней границы (правая ячейка) интервала №1 к значению нижней границы прибавьте шаг интервала: =G4+$H$11, где ячейка G4 соответствует нижней границе интервала, а в ячейке H11 содержится значение шага интервала; для автоматизации расчёта ссылку на шаг интервала лучше сделать абсолютной ($H$11) так, как это вы делали, выполняя пункт 6 подпункт b.iv.;

c. нижняя граница (левая ячейка) интервала №2 будет равна верхней границе (правая ячейка) интервала №1, для чего в упомянутую ячейку интервала №2 введите формулу вида «=H4», где в ячейке H4 содержится значение верхней границы интервала №1;

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

e. частоты интервалов (количество значений выборки, оказавшихся в границах интервала) удобнее подсчитать самостоятельно и записать в ячейку соответствующего интервала; при этом минимальное значение обязательно войдёт в интервал №1, максимальное – в интервал №4, остальные значения, оказавшиеся на границе между интервалами, следует включать в меньший интервал; таким образом, сумма частот всех интервалов должна быть равна объёму выборки (в нашем примере n = 10).

16. Постройте гистограмму распределения, используя данные таблицы «Вариационный ряд результатов измерений»:

a. выделите столбец «Частота» таблицы с заголовком так, как это показано на рисунке 4;

 

 

Рисунок 4 – Выделенные ячейки для построения гистограммы и полигона распределения

 

b. на вкладке «Вставка» в группе «Диаграммы» выберите команду «Гистограмма»;

c. в появившемся меню в разделе «Гистограмма» выберите «Гистограмма с группировкой»;

d. для настройки параметров в появившейся гистограмме в области диаграммы (белая область по краям диаграммы) щёлкните правой кнопкой мышки;

e. в появившемся контекстном меню выберите пункт «Выбрать данные…»;

f. в появившемся диалоговом окне «Выбор источника данных» в области «Подписи горизонтальной оси (категории)» нажмите кнопку «Изменить»;

g. появится диалоговое окно «Подписи оси», при этом выделите мышкой в таблице «Вариационный ряд результатов измерений» диапазон ячеек, в которых указаны значения границ интервалов (рис. 5);

h. нажмите кнопку ОК в окне «Подписи оси»;

i. нажмите кнопку ОК в окне «Выбор источника данных»;

j. для ликвидации зазора между столбиками гистограммы щёлкните правой кнопкой мышки по одному из столбиков, в контекстном меню выберите пункт «Формат ряда данных…»;

 

 

Рисунок 5 – Пример выделения ячеек, содержащих информацию для подписей оси категорий

 

k. в появившемся диалоговом окне «Формат ряда данных» бегунок «Боковой зазор» перетащите в крайнее левое положение «Без зазора»;

l. нажмите в окне «Формат ряда данных» кнопку «Закрыть»;

m. переименуйте диаграмму, для чего щёлкните по заголовку («Частота»), двойным щелчком выделите текст и наберите новый текст заголовка «Гистограмма».

17. Перетащите построенную гистограмму вправо от таблицы «Вариационный ряд результатов измерений».

18. Постройте полигон распределения, используя данные таблицы «Вариационный ряд результатов измерений»:

a. выделите столбец «Частота» таблицы с заголовком так, как это показано на рисунке 4;

b. на вкладке «Вставка» в группе «Диаграммы» выберите команду «График»;

c. в появившемся меню в разделе «График» выберите «График с маркерами»;

d. для настройки параметров в появившемся графике в области диаграммы (белая область по краям диаграммы) щёлкните правой кнопкой мышки;

e. в появившемся контекстном меню выберите пункт «Выбрать данные…»;

f. в появившемся диалоговом окне «Выбор источника данных» в области «Подписи горизонтальной оси (категории)» нажмите кнопку «Изменить»;

g. появится диалоговое окно «Подписи оси», при этом выделите мышкой в таблице «Вариационный ряд результатов измерений» диапазон ячеек, в которых указаны значения границ интервалов (рис. 5);

h. нажмите кнопку ОК в окне «Подписи оси»;

i. нажмите кнопку ОК в окне «Выбор источника данных»;

j. переименуйте диаграмму, для чего щёлкните по заголовку («Частота»), двойным щелчком выделите текст и наберите новый текст заголовка «Полигон».

19. Перетащите построенный полигон распределения и расположите его снизу от гистограммы распределения.

20. Сохраните файл «1.xlsx».

Вопросы для самопроверки:

1. Предмет математической статистики.

2. Этапы статистического обследования.

3. Дискретные и непрерывные ряды.

4. Выборочная и генеральная совокупности. Объем выборки.

5. Что называют ранжированием ряда?

6. Полигон и гистограмма распределения.

7. Основные статистические характеристики центра ряда.

8. Характеристики вариации.

9. Понятие репрезентативности выборочных показателей, ошибки репрезентативности.

Основы теории корреляции