«MS
Excel. Статистические функции»
Выполнив задания этой темы, вы научитесь:
· Технологии
создания табличного документа;
· Присваивать
тип к используемым данным;
· Созданию
формулы и правилам изменения ссылок в них;
· Использовать
встроенные статистических функции Excel для расчетов.
Задание 1. Рассчитать
количество прожитых дней.
Технология работы:
1. Запустить
приложение Excel.
2. В
ячейку A1 ввести дату своего рождения (число, месяц, год – 20.12.97).
Зафиксируйте ввод данных.
3. Просмотреть
различные форматы представления даты (Главная – Формат ячейки – Другие
числовые форматы - Дата). Перевести дату в типЧЧ.ММ.ГГГГ. Пример,
14.03.2001
4. Рассмотрите
несколько типов форматов даты в ячейке А1.
5. В
ячейку A2 ввести сегодняшнюю дату.
6. В
ячейке A3 вычислить количество прожитых дней по формуле. Результат может
оказаться представленным в виде даты, тогда его следует перевести в числовой
тип.
Задание
2. Возраст
учащихся. По заданному списку учащихся и даты их рождения. Определить, кто
родился раньше (позже), определить кто самый старший (младший).
Технология работы:
1. Получите файл
Возраст.
2. Рассчитаем
возраст учащихся. Чтобы рассчитать возраст необходимо с помощью функции СЕГОДНЯ выделить
сегодняшнюю текущую дату из нее вычитается дата рождения учащегося, далее из
получившейся даты с помощью функции ГОД выделяется из даты лишь год. Из
полученного числа вычтем 1900 – века и получим возраст учащегося. В ячейку D3
записать формулу =ГОД(СЕГОДНЯ()-С3)-1900. Результат может
оказаться представленным в виде даты, тогда его следует перевести в числовой
тип.
3. Определим самый
ранний день рождения. В ячейку C22 записать формулу =МИН(C3:C21);
4. Определим самого
младшего учащегося. В ячейку D22 записать формулу =МИН(D3:D21);
5. Определим самый
поздний день рождения. В ячейку C23 записать формулу =МАКС(C3:C21);
6. Определим самого
старшего учащегося. В ячейку D23 записать формулу =МАКС(D3:D21).
Самостоятельная работа:
Задача. Произведите
необходимые расчеты роста учеников в разных единицах измерения.
«MS Excel. Статистические
функции» Часть II.
Задание
3. С использованием электронной таблицы произвести обработку
данных с помощью статистических функций. Даны сведения об учащихся класса,
включающие средний балл за четверть, возраст (год рождения) и пол. Определить
средний балл мальчиков, долю отличниц среди девочек и разницу среднего балла
учащихся разного возраста.
Решение:
Заполним таблицу исходными данными и проведем необходимые расчеты.
В таблице используются
дополнительные колонки, которые необходимы для ответа на вопросы, поставленные
в задаче — возраст ученика и является ли учащийся отличником
и девочкой одновременно.
Для расчета возраста использована следующая формула (на примере ячейки G4):
=ЦЕЛОЕ((СЕГОДНЯ()-E4)/365,25)
Прокомментируем
ее. Из сегодняшней даты вычитается дата рождения ученика. Таким образом,
получаем полное число дней, прошедших с рождения ученика. Разделив это
количество на 365,25 (реальное количество дней в году, 0,25 дня для обычного
года компенсируется високосным годом), получаем полное количество лет ученика;
наконец, выделив целую часть, — возраст ученика.
Является
ли девочка отличницей, определяется формулой (на примере ячейки H4):
=ЕСЛИ(И(D4=5;F4="ж");1;0)
Приступим
к основным расчетам.
Прежде всего требуется определить средний балл девочек. Согласно определению,
необходимо разделить суммарный балл девочек на их количество. Для этих целей
можно воспользоваться соответствующими функциями табличного процессора.
=СУММЕСЛИ(F4:F15;"ж";D4:D15)/СЧЁТЕСЛИ(F4:F15;"ж")
Функция СУММЕСЛИ позволяет просуммировать значения
только в тех ячейках диапазона, которые отвечают заданному критерию (в нашем
случае ребенок является мальчиком). Функция СЧЁТЕСЛИ подсчитывает количество
значений, удовлетворяющих заданному критерию. Таким образом и получаем
требуемое.
Для подсчета доли отличниц среди всех девочек отнесем количество
девочек-отличниц к общему количеству девочек (здесь и воспользуемся
набором значений из одной из вспомогательных колонок):
=СУММ(H4:H15)/СЧЁТЕСЛИ(F4:F15;"ж")
Наконец, определим отличие средних баллов
разновозрастных детей (воспользуемся в расчетах вспомогательной колонкой Возраст):
=ABS(СУММЕСЛИ(G4:G15;15;D4:D15)/СЧЁТЕСЛИ(G4:G15;15)-
СУММЕСЛИ(G4:G15;16;D4:D15)/СЧЁТЕСЛИ(G4:G15;16))
Обратите внимание на то, что формат данных в ячейках
G18:G20 – числовой, два знака после запятой. Таким образом, задача полностью
решена. На рисунке представлены результаты решения для заданного набора данных.
Самостоятельная
работа
«MS Excel. Статистические функции»
1 вариант.
С использованием электронной таблицы
произвести обработку данных с помощью статистических функций.
1. Даны сведения об учащихся класса (10 человек), включающие оценки в течение
одного месяца по математике.
Подсчитайте количество пятерок, четверок, двоек и троек, найдите средний балл
каждого ученика и средний балл всей группы. Создайте диаграмму,
иллюстрирующую процентное соотношение оценок в группе.
2 вариант.
Четверо друзей путешествуют на трех
видах транспорта: поезде, самолете и пароходе. Николай проплыл 150 км на
пароходе, проехал 140 км на поезде и пролетел 1100 км на самолете. Василий
проплыл на пароходе 200 км, проехал на поезде 220 км и пролетел на самолете
1160 км. Анатолий пролетел на самолете 1200 км, проехал поездом 110 км и
проплыл на пароходе 125 км. Мария проехала на поезде 130 км, пролетела на
самолете 1500 км и проплыла на пароходе 160 км.
Построить на основе вышеперечисленных данных электронную таблицу.
- Добавить к таблице столбец, в
котором будет отображаться общее количество километров, которое проехал
каждый из ребят.
- Вычислить общее количество
километров, которое ребята проехали на поезде, пролетели на самолете и
проплыли на пароходе (на каждом виде транспорта по отдельности).
- Вычислить суммарное количество
километров всех друзей.
- Определить максимальное и
минимальное количество километров, пройденных друзьями по всем видам
транспорта.
- Определить среднее количество
километров по всем видам транспорта.
3 вариант.
Создайте таблицу “Озера Европы”,
используя следующие данные по площади (кв. км) и наибольшей глубине (м):
Ладожское 17 700 и 225; Онежское 9510 и 110; Каспийское море
371 000 и 995; Венерн 5550 и 100; Чудское с Псковским 3560 и 14; Балатон
591 и 11; Женевское 581 и 310; Веттерн 1900 и 119; Боденское 538 и 252;
Меларен 1140 и 64. Определите самое большое и самое маленькое по площади
озеро, самое глубокое и самое мелкое озеро.
4 вариант.
Создайте таблицу “Реки Европы”,
используя следующие данные длины (км) и площади бассейна (тыс. кв. км):
Волга 3688 и 1350; Дунай 2850 и 817; Рейн 1330 и 224; Эльба 1150 и 148; Висла
1090 и 198; Луара 1020 и 120; Урал 2530 и 220; Дон 1870 и 422; Сена 780 и 79;
Темза 340 и 15. Определите самую длинную и самую короткую реку, подсчитайте
суммарную площадь бассейнов рек, среднюю протяженность рек европейской части
России.
5 вариант.
В банке производится учет
своевременности выплат кредитов, выданных нескольким организациям. Известна
сумма кредита и сумма, уже выплаченная организацией. Для должников
установлены штрафные санкции: если фирма выплатила кредит более
чем на 70 процентов, то штраф составит 10 процентов от суммы задолженности, в
противном случае штраф составит 15 процентов. Посчитать штраф для каждой
организации, средний штраф, общее количество денег, которые банк собирается
получить дополнительно. Определить средний штраф бюджетных организаций.
|
Оставьте свой комментарий
Авторизуйтесь, чтобы задавать вопросы.