Задания и порядок их выполнения. Задание 1. Создайте таблицу, содержащую сведения о возрасте осужденных по ст

Задание 1. Создайте таблицу, содержащую сведения о возрасте осужденных по ст. 105 ч. 1 УК.

Порядок выполнения:

1. Запустите табличный процессор Excel: Пуск ñ Программы ñ Microsoft Office ñ Microsoft Office Excel 2010.

2. В ячейку А1 введите наименование таблицы: Сведения об осужденных по статье 105 УК; в ячейку А2 – Порядковый номер; в ячейку В2 – Возраст, лет.

3. Выделите блок ячеек А2:В2 и выполните команду контекстного меню Формат ячеек (). Во вкладке «Выравнивание» установите параметры: «по горизонтали» – по центру; «по вертикали» – по центру, а также флажок в строке «переносить по словам».

4. В ячейки А3 и А4 введите 1 и 2, соответственно. Используя автозаполнение (выделите ячейки А3. А4 ñ ухватите курсором правый нижний угол ячейки А4 ñ не отпуская левую кнопку мыши протяните курсор до ячейки А42), столбец «Порядковый номер» дополните числами от 3 до 40.

5. Используя генератор случайных чисел, заполните данные в столбце «Возраст». Для этого:

5.1. Нажмите кнопку Анализ данных ()в группе Анализ на вкладке Данные [4].

5.2. Из списка инструментов в раскрывшемся окне «Анализ данных» выберите инструмент «Генерация случайных чисел» и нажмите кнопку ОК. Откроется одноименное окно диалога.

5.3. В окне «Генерация случайных чисел» задайте параметры, приведенные на рис. 10.28, и нажмите кнопку ОК.

Рис. 10.27. Окно Генерация случайных чисел

5.4. В ячейку В3 введите формулу =ОКРУГЛВВЕРХ(D3;0) и скопируйте её в ячейки В4:В42.

6. Обратите внимание, что в ячейках В3:В42 появятся данные о возрасте, сформированные случайным образом. Эта совокупность данных и есть выборка объема N = 40. Если упорядочить (отсортировав) строки таблицы по мере возрастания значения возраста, то получим упорядоченную выборку, называемую вариационным рядом.

7. Выделите блок ячеек А2:В42 и нажмите кнопку Все границы () на вкладке Главная (группа Шрифт). Ячейки таблицы выделятся обрамлением из одной линии.

8. Сохраните рабочую книгу в личной папке под именем Статистика.

Задание 2. Создайте интервальную таблицу частот полученной выборки.

Порядок выполнения:

1. Введите данные и оформите таблицу по образцу, представленному на рис. 10.28.

2. Подсчитайте число осуждённых, возраст которых принадлежит выделенным интервалам (от 18 до 25 лет, от 25 до 30 лет, …, от 60 до 70 лет). Для этого:

Рис. 10.28. Исходный вид интервальной таблицы частот

2.1. Проведите предварительные расчёты:

1) в ячейках G8, H8, …, K8 подсчитайте число осуждённых, возраст которых совпадает с границами интервалов, т.е. 25, 30, …, 60 лет, соответственно: введите в ячейку G8 формулу[5] =СЧЁТЕСЛИ($B$3:$B$42;25), скопируйте её в ячейки H8, …, K8 и строке ввода данных замените значение 25 на 30, 40, 50 и 60, соответственно.

2) в ячейку G9 введите формулу для подсчёта число осуждённых в возрасте до 24 лет:

=СЧЁТЕСЛИ($B$3:$B$42;"<25");

3) в ячейку Н9 введите формулу для подсчёта число осуждённых в возрасте от 26 до 29 лет:

=СЧЁТЕСЛИ($B$3:$B$42;"<30")-СЧЁТЕСЛИ($B$3:$B$42;"<26");

4) в ячейку I9 введите формулу для подсчёта число осуждённых в возрасте от 31 до 39 лет:

=СЧЁТЕСЛИ($B$3:$B$42;"<40")-СЧЁТЕСЛИ($B$3:$B$42;"<31");

5) в ячейку J9 введите формулу для подсчёта число осуждённых в возрасте от 41 до 49 лет:

=СЧЁТЕСЛИ($B$3:$B$42;"<50")-СЧЁТЕСЛИ($B$3:$B$42;"<41");

6) в ячейку K9 введите формулу для подсчёта число осуждённых в возрасте от 51 до 59 лет:

=СЧЁТЕСЛИ($B$3:$B$42;"<60")-СЧЁТЕСЛИ($B$3:$B$42;"<51");

7) в ячейку L9 введите формулу для подсчёта число осуждённых в возрасте от 61 до 70 лет:

=СЧЁТЕСЛИ($B$3:$B$42;"<71")-СЧЁТЕСЛИ($B$3:$B$42;"<61").

2.2. Выполните окончательный подсчёт числа осуждённых по возрастным интервалам:

1) в ячейку G3 введите формулу

=G9+ОКРУГЛВВЕРХ(G8/2;0);

2) в ячейку Н3 введите формулу

=H9+G8-ОКРУГЛВВЕРХ(G8/2;0)+ОКРУГЛВВЕРХ(H8/2;0)

и скопируйте её в ячейки I3, J3, K3 и L3.

2.3. В ячейку М3 поместите результат суммирования блока ячеек G3:L3. Убедитесь, что он составляет 40.

3. В ячейках G4:L4 подсчитайте относительную частоту числа осуждённых в каждом из возрастных интервалов. Для этого в ячейку G4 введите формулу

=G3/$M$3

и скопируйте её в ячейки Н4:L4.

4. В ячейку М4 поместите результат суммирования ячеек G4:L4. Убедитесь, что он составляет 1.

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

1) в ячейку G5 формулу

=G4/7

2) в ячейку Н5 формулу

=Н4/5

3) в ячейку I5 формулу

=I4/10

и скопировать её в ячейки J5:L5.

6. В ячейку М5 поместите результат суммирования блока ячеек G5:L5. Убедитесь, что он составляет 0,10357.

7. В ячейках G6:L6 подсчитайте накопленную частоту числа осуждённых в каждом из возрастных интервалов. Для этого введите:

1) в ячейку G6 формулу

=G4

2) в ячейку Н6 формулу

=H4+G6

и скопируйте её в ячейки I6:L6.

8. Убедитесь, что в ячейке L5 отобразится число 1.

Задание 3. Постройте гистограмму и полигон плотности распределения числа осуждённых, а также график кумуляты (накопленной частоты), исходя из возрастных интервалов возраста осужденных.

Порядок выполнения:

1. Создайте гистограмму плотности распределения числа осуждённых по возрастным интервалам. Для этого:

1.1. Выделите блок ячеек G2:L5.

1.2. На вкладке Вставка в группе Диаграммы выберите мышью Гистограмма () ñ Гистограмма с группировкой ().

1.3. Подхватите диаграмму и сдвиньте её вниз на свободное место.

1.4. Выделите ступеньки синего цвета – ряда 1 (щелчком курсора по любой из них) и нажмите клавишу Del. Ряд 1 будет удалён из диаграммы.

1.5. Выделите ступеньки красного цвета – ряда 2 (щелчком курсора по любой из них) и нажмите клавишу Del. Ряд 2 будет удалён из диаграммы.

1.6. В группе Макеты диаграммам установите Макет 8 () и введите названия:

в поле название диаграммы - Гистограмма плотности возраста осуждённых;

в поле название оси (по вертикали) – Плотность;

в поле название оси (по горизонтали) – Возраст.

1.7. Установите размер шрифта названий осей и горизонтальной оси (категорий) равным 12 пт (щёлкнуть по названию и на вкладке Главная ñ группа Шрифт ñ окно Размер шрифта установить 12).

1.8. Щёлкните курсором внутри гистограммы и вызовите контекстное меню. В верхней части выпавшего меню щёлкните по кнопке Заливка фигуры () и установите параметр Нет заливки; щёлкните по кнопке Контур фигуры () и установите параметры: цвет темы Чёрный; толщина 1,5 пт.

Сравните результат с диаграммой, представленной на рис. 10.29.

2. Создайте полигон плотности распределения числа осуждённых по возрастным интервалам. Для этого:

Рис.10.29. Гистограмма плотности возраста осуждённых

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

Рис.10.30. Таблица для построения полигона

2.2. В ячейках Q3:X3 подсчитайте плотность распределения числа осуждённых для каждого возрастных значений осуждённых. Для этого в указанные ячейки необходимо введите следующие формулы:

1) в ячейку Q3 =G5/2

2) в ячейку R3 =G4/7

3) в ячейку S3 =H4/5

4) в ячейку T3 =I4/10 и скопируйте её в ячейки U3:W3;

5) в ячейку X3 =L5/2.

2.3. Выделите блок ячеек Q3:Х3.

2.4. На вкладке Вставка в группе Диаграммы выберите мышью График () ñ График ().

2.5. Подхватите диаграмму и сдвиньте её вниз на свободное место.

2.6. В группе Макеты диаграммам установите Макет 10 () и введите названия:

в поле название диаграммы - Полигон возраста осуждённых;

в поле название оси (по вертикали) – Плотность;

в поле название оси (по горизонтали) – Возраст.

2.7. Щёлкните в область графика, вызовите контекстное меню и выберите команду Выбрать данные… В правой части окна «Выбор источника данных» нажмите кнопку Изменить (). В окне «Подписи оси» укажите блок ячеек Q2:X2 и нажмите кнопку ОК.

2.8. Установите размер шрифта названий осей и горизонтальной оси (категорий) равным 12 пт (щёлкнуть по названию и на вкладке Главная ñ группа Шрифт ñ окно Размер шрифта установить 12).

2.9. Щёлкните по легенде и нажмите клавишу Del. Удалится надпись «Ряд 1».

2.10. Щёлкните курсором по графику и вызовите контекстное меню. В верхней части выпавшего меню щёлкните по кнопке Контур фигуры () и установите параметры: цвет темы Чёрный; толщина 1,5 пт. Сравните результат с графиком, представленным на рис. 10.31.

Рис.10.31. Полигон возраста осуждённых

3. Создайте график кумуляты числа осуждённых по возрастным интервалам. Для этого:

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

Рис.10.32. Таблица для построения кумуляты

3.2. В ячейку G29 введите значение 0, а в ячейку N29 – значение 1.

3.3. В ячейках Н29:М29 подсчитайте наколенную частоту числа осуждённых для каждого возрастных значений осуждённых. Для этого в указанные ячейки необходимо введите следующие формулы:

1) в ячейку Н29 =G4

2) в ячейку R3 =Н4+G6 и скопируйте её в ячейки I29:M29.

3.4. Выделите блок ячеек G29:N29.

3.5. На вкладке Вставка в группе Диаграммы выберите мышью График () ñ График ().

3.6. Подхватите диаграмму и сдвиньте её вниз на свободное место.

3.7. В группе Макеты диаграммам установите Макет 10 () и введите названия:

в поле название диаграммы - Кумулята возраста осуждённых;

в поле название оси (по вертикали) – Накопленная частота;

в поле название оси (по горизонтали) – Возраст.

3.8. Щёлкните в область графика, вызовите контекстное меню и выберите команду Выбрать данные… В правой части окна «Выбор источника данных» нажмите кнопку Изменить (). В окне «Подписи оси» укажите блок ячеек G28:N28 и нажмите кнопку ОК.

3.9. Установите размер шрифта названий осей и горизонтальной оси (категорий) равным 12 пт (щёлкнуть по названию и на вкладке Главная ñ группа Шрифт ñ окно Размер шрифта установить 12).

3.10. Щёлкните по легенде и нажмите клавишу Del. Удалится надпись «Ряд 1».

3.11. Щёлкните курсором по графику и вызовите контекстное меню. В верхней части выпавшего меню щёлкните по кнопке Контур фигуры () и установите параметры: цвет темы Чёрный; толщина 1,5 пт.

3.12. Щёлкните курсором по горизонтальной оси (категорий) и вызовите контекстное меню. В ниспадающем меню выберите команду Формат оси … ().

3.13. В окне «Формат оси» для параметра «Положение оси:» установите значение (флажок) по делениям.

3.14. Сравните результат с графиком, представленным на рис. 10.33.

Рис.10.33. Кумулята возраста осуждённых

Контрольные вопросы

1. Каково содержание понятий «выборка» и «генеральная совокупность»?

2. К какой категории функций принадлежат функции для проведения статистической обработки данных? Каков порядок их выбора?

3. Каков порядок доступа к инструментам надстройки Пакет анализа?

4. Какие виды дисперсионного анализа позволяет выполнить Пакет анализа? В чём их отличие?

[1] Вызывается через контекстное меню или нажатием кнопки () в группе Выравнивание.

[2] Форму данных можно использовать для добавления, поиска, изменения и удаления строк в диапазоне или таблице.

[3] Для включения в «Пакет анализа» функций Visual Basic для приложений (VBA) аналогичным образом можно загрузить надстройку «Пакет анализа VBA».

[4] Если кнопка Анализ данных недоступна, выполните действия, указанные в разделе «Теоретические сведения».

[5] Функция СЧЁТЕСЛИ() принадлежит категории «Статистические».