Лабораторная работа № 11. Наименование: Обработка статистической информации средствами электронных таблиц

Наименование: Обработка статистической информации средствами электронных таблиц

1. Цель: Научиться использовать встроенные статистические функции для расчетов в электронных таблицах

2. Литература:

2.1 Анеликова Л.А. Лабораторные работы по Excel. – М.: СОЛОН – Пресс, 2006. – 128 с.

3. Содержание отчета:

3.1 Наименование и цель работы

3.2 Выполненное задание

3.3 Ответы на контрольные вопросы

3.4 Перенесите все полученные расчеты в отчет, вместе с формулами

4. Задание:

4.1 Рассчитать количество прожитых дней.

- в ячейку А1 введите дату своего рождения (число, месяц, год – 20.12.81). Зафиксируйте ввод данных;

- просмотрите различные форматы представления даты (Формат – Формат ячейка – Число – Числовые форматы – Дата). Перевести дату в тип ЧЧ.ММ.ГГГГ. Пример 14.03.2001;

- в ячейку А2 введите сегодняшнюю дату;

- в ячейки А3 вычислите количество прожитых дней по формуле = А2 – А1. Результат может оказаться представленным в виде даты, тогда его следует перевести в числовой тип. (Формат – Формат ячейки – Число – Числовые форматы - Числовой – число знаков после запятой – 0).

4.2 Возраст учащихся. По списку студентов своей группы и даты их рождения, определить, кто родился раньше (позже), определить кто самый старший (младший).

- Рассчитаем возраст учащихся. Чтобы рассчитать возраст необходимо с помощью функции СЕГОДНЯ выделить сегодняшнюю текущую дату из нее вычитается дата рождения учащегося, далее из получившейся даты с помощью функции ГОД выделяется из даты лишь год. Из полученного числа вычтем 1900 – века и получим возраст учащегося. В ячейку D3 записать формулу =ГОД(СЕГОДНЯ()-С3)-1900. Результат может оказаться представленным в виде даты, тогда его следует перевести в числовой тип. (Формат – Формат ячейки – Число – Числовые форматы – Числовой – число знаков после запятой – 0).

- Определим самый ранний день рождения, по формуле =МИН();

- Определим самого младшего учащегося.

- Определим самый поздний день рождения, по формуле =МАКС();

- Определим самого старшего учащегося.

4.3 Произведите необходимые расчеты роста учеников в разных единицах измерения.

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

Решение:
Заполним таблицу исходными данными и проведем необходимые расчеты. В таблицу будем заносить данные из журнала.

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

=ЦЕЛОЕ((СЕГОДНЯ()-E4)/365, 25)

Прокомментируем ее. Из сегодняшней даты вычитается дата рождения ученика. Таким образом, получаем полное число дней, прошедших с рождения ученика. Разделив это количество на 365, 25 (реальное количество дней в году, 0, 25 дня для обычного года компенсируется високосным годом), получаем полное количество лет ученика; наконец, выделив целую часть, — возраст ученика.
Является ли девочка отличницей, определяется формулой (на примере ячейки H4):

=ЕСЛИ(И(D4=5; F4=" ж"); 1; 0)

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

=СУММЕСЛИ(F4: Fn; " м"; D4: Dn)/СЧЁТЕСЛИ(F4: Fn; " м")

Функция СУММЕСЛИ позволяет просуммировать значения только в тех ячейках диапазона, которые отвечают заданному критерию (в нашем случае ребенок является мальчиком). Функция СЧЁТЕСЛИ подсчитывает количество значений, удовлетворяющих заданному критерию. Таким образом и получаем требуемое.
Для подсчета доли отличниц среди всех девочек отнесем количество девочек-отличниц к общему количеству девочек (здесь и воспользуемся набором значений из одной из вспомогательных колонок):

=СУММ(H4: Hn)/СЧЁТЕСЛИ(F4: Fn; " ж")

Наконец, определим отличие средних баллов разновозрастных детей (воспользуемся в расчетах вспомогательной колонкой Возраст):

=ABS(СУММЕСЛИ(G4: Gn; n1; D4: Dn)/СЧЁТЕСЛИ(G4: Gn; n1 )-
СУММЕСЛИ(G4: Gn; n2; D4: Dn)/СЧЁТЕСЛИ(G4: Gn; n2 ))

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

4.6 Четверо друзей путешествуют на трех видах транспорта: поезде, самолете и пароходе. Николай проплыл 150 км на пароходе, проехал 140 км на поезде и пролетел 1100 км на самолете. Василий проплыл на пароходе 200 км, проехал на поезде 220 км и пролетел на самолете 1160 км. Анатолий пролетел на самолете 1200 км, проехал поездом 110 км и проплыл на пароходе 125 км. Мария проехала на поезде 130 км, пролетела на самолете 1500 км и проплыла на пароходе 160 км.
Построить на основе вышеперечисленных данных электронную таблицу.

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

- вычислить общее количество километров, которое ребята проехали на поезде, пролетели на самолете и проплыли на пароходе (на каждом виде транспорта по отдельности).

- вычислить суммарное количество километров всех друзей.

- определить максимальное и минимальное количество километров, пройденных друзьями по всем видам транспорта.

- определить среднее количество километров по всем видам транспорта.

4.7 Создайте таблицу “Озера Европы”, используя следующие данные по площади (кв. км) и наибольшей глубине (м): Ладожское 17 700 и 225; Онежское 9510 и 110; Каспийское море 371 000 и 995; Венерн 5550 и 100; Чудское с Псковским 3560 и 14; Балатон 591 и 11; Женевское 581 и 310; Веттерн 1900 и 119; Боденское 538 и 252; Меларен 1140 и 64. Определите самое большое и самое маленькое по площади озеро, самое глубокое и самое мелкое озеро.
4.8 Создайте таблицу “Реки Европы”, используя следующие данные длины (км) и площади бассейна (тыс. кв. км): Волга 3688 и 1350; Дунай 2850 и 817; Рейн 1330 и 224; Эльба 1150 и 148; Висла 1090 и 198; Луара 1020 и 120; Урал 2530 и 220; Дон 1870 и 422; Сена 780 и 79; Темза 340 и 15. Определите самую длинную и самую короткую реку, подсчитайте суммарную площадь бассейнов рек, среднюю протяженность рек европейской части России.
5. Контрольные вопросы:

5.1 Что можно рассчитать с помощью статистических функций?

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

ПРИЛОЖЕНИЕ:

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

Основу статистического анализа составляет исследование совокупностей и выборок. Выборка представляет собой подмножество совокупности. В качестве примера выборки можно привести опросы общественного мнения. Исследуя выборки с помощью вычисления отклонений и отслеживания взаимосвязей с генеральной совокупностью, можно проследить, насколько репрезентативна выборка.