Выбранный для просмотра документ Практикум № 8 «MS Excel 2003. Статистические функции».doc
Практикум № 8
«MS Excel 2003. Статистические функции»
Выполнив задания этой темы, вы научитесь:
Задание 1. Рассчитать
количество прожитых дней.
Технология работы:
Задание 2. Возраст учащихся. По заданному списку учащихся и даты их
рождения. Определить, кто родился раньше (позже), определить кто самый старший
(младший).
Технология работы:
Получите от преподавателя файл Возраст. По локальной сети: Откройте папку Мое сетевое окружение–Соседние компьютеры–Great– Электронные_таблицы, найдите файл Возраст. Скопируйте его любым известным вам способом.
Самостоятельная работа:
Задача. Произведите необходимые расчеты роста учеников в разных единицах
измерения.
Задание 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 – числовой, два знака после запятой. Таким образом, задача полностью решена. На рисунке представлены результаты решения для заданного набора данных.
Самостоятельная работа:
С использованием электронной
таблицы произвести обработку данных помощью статистических функций.
1. Даны сведения об учащихся класса, включающие оценки в течение одного месяца.
Подсчитайте количество пятерок, четверок, двоек и троек, найдите средний балл
каждого ученика и средний балл всей группы. Создайте диаграмму, иллюстрирующую
процентное соотношение оценок в группе.
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 процентов. Посчитать штраф
для каждой организации, средний штраф, общее количество денег, которые банк
собирается получить дополнительно. Определить средний штраф бюджетных
организаций.
Рабочие листы
к вашим урокам
Скачать
Рабочие листы
к вашим урокам
Скачать
Выполнив задания этой темы, вы научитесь: Технологии создания табличного документа; Присваивать тип к используемым данным; Созданию формулы и правилам изменения ссылок в них; Использовать встроенные статистических функции Excel 2003 для расчетов. Задание 1. Рассчитать количество прожитых дней.Технология работы: Запустить приложение Excel 2003. В ячейку A1 ввести дату своего рождения (число, месяц, год – 20.12.81). Зафиксируйте ввод данных. Просмотреть различные форматы представления даты (Формат – Формат ячейки – Число – Числовые форматы - Дата). Перевести дату в тип ЧЧ.ММ.ГГГГ. Пример, 14.03.2001 Рассмотрите несколько типов форматов даты в ячейке А1. В ячейку A2 ввести сегодняшнюю дату. В ячейке A3 вычислить количество прожитых дней по формуле =A2-A1. Результат может оказаться представленным в виде даты, тогда его следует перевести в числовой тип. (Формат – Формат ячейки – Число – Числовые форматы – Числовой – число знаков после запятой – 0). Задание 2. Возраст учащихся. По заданному списку учащихся и даты их рождения. Определить, кто родился раньше (позже), определить кто самый старший (младший).Технология работы: Получите от преподавателя файл Возраст. По локальной сети: Откройте папку Мое сетевое окружение–Соседние компьютеры–Great– Электронные_таблицы, найдите файл Возраст. Скопируйте его любым известным вам способом. Откройте свою папку (с номером вашей группы\класса). Вставьте в нее скопированный ранее файл. Рассчитаем возраст учащихся. Чтобы рассчитать возраст необходимо с помощью функции СЕГОДНЯ выделить сегодняшнюю текущую дату из нее вычитается дата рождения учащегося, далее из получившейся даты с помощью функции ГОД выделяется из даты лишь год. Из полученного числа вычтем 1900 – века и получим возраст учащегося. В ячейку D3 записать формулу =ГОД(СЕГОДНЯ()-С3)-1900. Результат может оказаться представленным в виде даты, тогда его следует перевести в числовой тип. (Формат – Формат ячейки – Число – Числовые форматы – Числовой – число знаков после запятой – 0). Определим самый ранний день рождения. В ячейку C22 записать формулу =МИН(C3:C21); Определим самого младшего учащегося. В ячейку D22 записать формулу =МИН(D3:D21); Определим самый поздний день рождения. В ячейку C23 записать формулу =МАКС(C3:C21); Определим самого старшего учащегося. В ячейку D23 записать формулу =МАКС(D3:D21). Самостоятельная работа: Задача. Произведите необходимые расчеты роста учеников в разных единицах измерения. Задание 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 – числовой, два знака после запятой. Таким образом, задача полностью решена. На рисунке представлены результаты решения для заданного набора данных.Самостоятельная работа: С использованием электронной таблицы произвести обработку данных помощью статистических функций. 1. Даны сведения об учащихся класса, включающие оценки в течение одного месяца. Подсчитайте количество пятерок, четверок, двоек и троек, найдите средний балл каждого ученика и средний балл всей группы. Создайте диаграмму, иллюстрирующую процентное соотношение оценок в группе. 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 процентов. Посчитать штраф для каждой организации, средний штраф, общее количество денег, которые банк собирается получить дополнительно. Определить средний штраф бюджетных организаций.
6 653 497 материалов в базе
Настоящий материал опубликован пользователем Елсукова Галина Александровна. Инфоурок является информационным посредником и предоставляет пользователям возможность размещать на сайте методические материалы. Всю ответственность за опубликованные материалы, содержащиеся в них сведения, а также за соблюдение авторских прав несут пользователи, загрузившие материал на сайт
Если Вы считаете, что материал нарушает авторские права либо по каким-то другим причинам должен быть удален с сайта, Вы можете оставить жалобу на материал.
Удалить материал
Ваша скидка на курсы
40%
Курс повышения квалификации
36 ч. — 144 ч.
Курс повышения квалификации
72 ч. — 180 ч.
Курс профессиональной переподготовки
600 ч.
Мини-курс
2 ч.
Оставьте свой комментарий
Авторизуйтесь, чтобы задавать вопросы.