Обработка статистики

Задание 5.1. Определение состава абитуриентов по стажу работы

1. Откройте файл-заготовку vedomosty.xlsx .

2. В свободной области таблицы D47:D48 создайте заголовки: Со стажем, После школы.

3. В ячейках Е47:Е48 при помощи статистической функции СЧЕТЕСЛИ подсчитайте соответствующие заголовкам значения по столбцу Стаж работы. Эта функция исследует указанный диапазон (столбец Стаж работы) и подсчитывает в нем количество ячеек, удовлетворяющих заданному условию: =0 — для только окончивших школу и >0 — для абитуриентов со стажем.

Для абитуриентов со стажем формула будет выглядеть так:

=СЧЁТЕСЛИ(E6:E46;">0") (стат.  функции)   

 или  в OpenOffice      =COUNTIF(E6:E46;">0")  (математические ф-ии) 

 

Задание 5.2. Определение среднего балла

1. В ячейке F47 напечатайте заголовок: Средний балл.

2. В ячейке  F48 при помощи статистической функции СРЗНАЧ (Average  для ООО)  подсчитайте средний балл по всем абитуриентам. Вы получите усредненную оценку уровня подготовки.

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

 

Задание 5.3. Определение регионального состава абитуриентов

1. В свободной области таблицы С49:С52 создайте заголовки Регион, Санкт-Петербург, Ленобласть, Другие регионы.

2. Рядом, в ячейках D50:D52, при помощи статистической функции СЧЕТЕСЛИ выполните расчеты количества абитуриентов по регионам. Например, формула для подсчета абитуриентов из Санкт-Петербурга будет выглядеть следующим образом:

=СЧЁТЕСЛИ(D6:D46;"СПб")

 для ООО -       =COUNTIF(D6:D46;"СПб") 

В этой формуле исследуется столбец Место жительства и подсчитывается количество ячеек, в которых указано значение СПб.

3. Формулы для Ленинградской области и других регионов составьте самостоятельно.

4. Постройте   круговую  диаграмму  по  рассчитанным   данным.

Рис. Доля иногородних поступающих

 

Задание 5.4. Определение состава абитуриентов по виду вступительных испытаний

1.    Состав абитуриентов по виду вступительных экзаменов (экзамен, олимпиада, собеседование) оформите самостоятельно в ячейках F49:G52 по аналогии с заданием 3. Используйте данные столбца Вид испытаний.

2.    Постройте  круговую диаграмму  на основании  полученных данных.    

 

 

 

Анализ результатов статистической обработки данных

Задание 5.5. Определение количества поступающих по направлениям обучения

1. Количество поступающих по направлениям обучения (экономика, техника, информационные технологии) подсчитайте самостоятельно в ячейках Н49:I52.

2. Подберите самостоятельно тип диаграммы для полученных данных и постройте ее.

 

Задание 5.6. Исследование возраста абитуриентов

1. В столбце 06:045 подсчитайте возраст каждого абитуриента. Используйте для этого формулу:

=(СЕГОДНЯ()-B6)/365
{для OOO =(TODAY()-B6)/365 }

2. Придумайте самостоятельно и проверьте свою формулу подсчета возраста (количества полных лет).

 Используйте функции раздела Дата и время. Помните, что Microsoft Excel хранит даты как ряд последовательных номеров, что позволяет выполнять над ними вычисления.
По умолчанию 1 января 1900 года соответствует числу 1. Каждый следующий день — число, на единицу большее предыдущего.

3. В свободной области таблицы в ячейках 049:052 создайте заголовки: Возрастные группы; До 20; От 20 до 21; Старше 21.

4. Рядом, в ячейках Р50 и Р52 подсчитайте количество поступающих до 20 лет и старше 21 лет.

5. Количество поступающих по возрастной группе от 20 до 21 лет подсчитайте в ячейке P51 по формуле:

=СЧЁТ(O6:O45)-P50-P52
{для ООО =COUNT(O6:O46)-P47-P49}

Функция СЧЁТ(интервал) используется для получения количества числовых ячеек в указанном интервале ячеек. В данной формуле эта функция считает общее количество абитуриентов, из которого вычитается число абитуриентов с возрастом менее 20 и более 21 лет.

6. Подберите тип диаграммы и постройте ее по рассчитанным данным.

Задание 5.7 Исследование популярности различных направлений обучения среди юношей и девушек

1. В свободной области таблицы, в столбцах I..N, создайте заголовки, как показано в табл. 5.1.

Таблица 5.1. Шапка таблицы для исследования популярности направлений обучения

Юноши

Девушки

Эк

Тех

ИТ

Эк

Тех

ИТ







2.  В первом столбце обозначенной заголовками области пометьте единицей юношей, поступающих на обучению по направлению «Экономика». Это можно сделать по следующей формуле:

=ЕСЛИ(И(С6="муж";Н6="экономика");1;0)
{для OOO: =IF(AND(С6="муж";Н6="экономика");1;0)}

3.  Скопируйте формулу в остальные строки этого столбца с помощью автозаполнения.

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

5.  Просуммируйте содержимое каждого из шести столбцов. Результаты разместите в ячейках I47:N47. Что показывают полученные суммы?

6.  По   шести   полученным   значениям   постройте   диаграмму.

 

Рис.  Поступление юношей и девушек по направлениям обучения

Задание 5.8. Формирование списков абитуриентов, зачисленных в вуз по выбранным направлениям обучения

1.  Скопируйте на листе 1 и поочередно перенесите копию на лист 2 столбцы Фамилия ИО, Количество баллов и Направление образования (данные вместе с заголовками).

2.  Замените в столбце оценок записи «медалист» на число 16. Балл 16 выше максимально возможного балла по экзаменам. Это дает медалистам приоритетное право на зачисление по сравнению с общим потоком. Балл по олимпиадам может быть выше, но олимпиадные задания имеют повышенный уровень сложности, поэтому приоритет олимпиады выше.

3.  Выделите содержимое всех трех столбцов вместе с заголовками и выполните сортировку (команда меню Данные Сортировка):

4. Выделите в каждом направлении цветом фона количество абитуриентов, соответствующее количеству мест в бюджетных группах. Это и есть списки абитуриентов, зачисленных в вуз.

Может получиться так, что непосредственно за списком зачисленных окажутся абитуриенты, набравшие такое же количество баллов. Каждый подобный случай разбирается экзаменационной комиссией. Например, в техническом направлении Балков М. М. и Майкова С.К. имеют равное количество баллов. Скорее всего, в список попадет первый, так как он житель Санкт-Петербурга и у него не будет проблем с жильем. При прочих равных условиях важным может оказаться средний балл аттестата, форма экзамена и даже пол абитуриента. В спорных ситуациях все может решить живое общение в ходе собеседования.

Задание 5.9.  Письменный отчет по работе

1.  В текстовом документе объясните, как вы понимаете построенные в процессе работы диаграммы.

2.  Перенесите списки с результатами конкурсных испытаний в текстовый документ, оформив их соответствующим образом.

3.  Сохраните отчет в учебной папке.

4.  По требованию учителя предъявите отчет.

Контрольные вопросы и задания

1.  Приведите примеры массивов данных.

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

3.  Какие статистические функции вам известны?

4.  Для   чего   используется   функция   СЧЁТЕСЛИ(<диапазон>;<условие>)? Что обозначают аргументы этой функции?

5.  Для чего используется функция СЧЁТ(<диапазон>)?