MS Excel. Статистические функции

Практическая работа

Тема: MS Excel. Статистические функции

Цель работы: научиться присваивать тип к используемым данным, использовать встроенные статистические функции ТП для расчетов.

Задание 1.  Рассчитать количество прожитых дней.
Технология работы:

1.      Запустить приложение Excel.

2.      В ячейку A1 ввести дату своего рождения (число, месяц, год – 20.12.97). Зафиксируйте ввод данных.

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

4.      Рассмотрите несколько типов форматов даты в ячейке А1.

5.      В ячейку A2 ввести сегодняшнюю дату.

6.      В ячейке A3 вычислить количество прожитых дней по формуле. Результат может оказаться представленным в виде даты, тогда его следует перевести в числовой тип.

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

Технология работы:

1.    Получите файл Возраст. По локальной сети: Откройте папку Сетевое окружение–Boss–Общие документы– 9 класс, найдите файл Возраст. Скопируйте его любым известным вам способом или скачайте с этой страницы внизу приложения.

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

3.    Определим самый ранний день рождения. В ячейку C22 записать формулу =МИН(C3:C21);

4.    Определим самого младшего учащегося. В ячейку D22 записать формулу =МИН(D3:D21);

5.    Определим самый поздний день рождения. В ячейку C23 записать формулу =МАКС(C3:C21);

6.    Определим самого старшего учащегося. В ячейку 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 – числовой, два знака после запятой. Таким образом, задача полностью решена. На рисунке представлены результаты решения для заданного набора данных.

Сделайте вывод о проделанной работе

Скачано с www.znanio.ru