Урок 51
Практикум
Статистическая обработка массива данных и построение диаграмм

Выполнив практическую работу, вы научитесь:

- обрабатывать числовые данные при помощи математических и статистических функций;
- использовать логические функции для обработки данных;
- строить различные виды диаграмм по расчетным данным.

Постановка задачи — обработа результатов вступительных экзаменов

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

Этому и посвящен предагаемый практикум. В нем в качестве примера рассматривается задача статистической обработки итогов вступительных экзаменов в высшее учебное заведение.

При поступлении в любое учебное заведение требуется предоставить приемной комиссии целый пакет документов. В качестве исходных данных для практической работы будет использована сводная ведомость, сформированная на основе представленных документов и результатов вступительных испытаний (рис. 5.1). Эти данные намеренно упрощены и носят учебный характер.

Чтобы не загромождать практическую работу кодами различных специальностей, все они условно разделены на три направления:

✔ экономика (подготовка экономистов, менеджеров);
✔ техника (подготовка инженерного состава); 
✔ информационные технологии (подготовка специалистов в области прикладной информатики и информационных технологий).

Рис. 5.1. Сводная ведомость абитуриентов

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

Под вступительными испытаниями подразумеваются три формы: собеседование — для абитуриентов, имеющих золотые медали, система предметных олимпиад конкретного вуза — для жителей города, например Санкт-Петербурга, и экзамены в общем потоке — для всех желающих.

Данные о контингенте и ходе вступительных экзаменов обрабатываются ежегодно. На основании статистических данных за несколько лет формируется политика вуза: открываются новые специальности, закрываются специальности, на которые нет спроса на рынке специалистов, создаются новые места в общежитиях для проживания иногородних студентов, и т. п. Статистика нужна и для улучшения организации приема в вуз.

Накопление статистики

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

1. Откройте файл-заготовку Ведомость.xls.

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

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

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

=СЧЁТЕСЛИ(Е6:Е45;">0").

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

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

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

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

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

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

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

=СЧЁТЕСЛИ(06:045;"СПбп).

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

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

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

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

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

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

Рис. 5.3. Деление поступающих по видам приемных испытаний

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