Работа с математическими, статистическим и логическими функциями

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

Тема: Работа с математическими, статистическим и логическими функциями.

Цель:  освоить навыки использования логических функций ЕСЛИ в MS Excel.

Порядок выполнения работы.

Откройте файл «Практическая работа 19», сохраните его под именем «ПР9_Фамилия_гр____».

Выполните задания.

Задание 1. Активируйте лист Если 1, выполните задание:

[pic 1]

Задание 2.  Активируйте лист Если  2, выполните задание:

[pic 2]

Задание 3.  Активируйте лист Если 3, выполните задание:

[pic 3]

Задание 4.  Активируйте лист Если 4, выполните задание:

[pic 4]

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

Алгоритм выполнения задания.

1. Добавьте новый лист, присвойте ему имя «Задание 5».
2. В ячейку А2 вставьте таблицу, предварительно воспользовавшись сайтом Convertio - https://convertio.co/ru/ocr/ . Файл с таблицей «Таблица для Задания5» возьмите  из папки с заданием. Перед распознаванием выберите формат MS Excel.
3. Заполните таблицу исходными данными и проведем необходимые расчеты. Обратите внимание на формат значений в ячейках "Средний балл" (числовой) и "Дата рождения" (дата) (рис. 1).

[pic 5]

Рисунок 1 - Сведения об учащихся в классе

1. В таблице используются дополнительные колонки, которые необходимы для ответа на вопросы, поставленные в задаче — возраст ученика и является ли учащийся отличником и девочкой одновременно.
2. В столбце Е проверьте правильность написания дат, проставьте все недостающие точки.
3. Рассчитайте возраст учащихся, по формуле (на примере ячейки G4): =ЦЕЛОЕ((СЕГОДНЯ()-E4)/365,25). Прокомментирую ее. Из сегодняшней даты вычитается дата рождения ученика. Таким образом, получаем полное число дней, прошедших с рождения ученика. Разделив это количество на 365,25 (реальное количество дней в году, 0,25 дня для обычного года компенсируется високосным годом), получаем полное количество лет ученика; наконец, выделив целую часть, — возраст ученика.
4. Определите является ли девочка отличницей, с помощью формулы (на примере ячейки H4): =ЕСЛИ(И(F4:F15="ж");"да";"нет").
5. Определите средний балл девочек. Согласно определению, необходимо разделить суммарный балл девочек на их количество. Для этих целей можно воспользоваться соответствующими функциями табличного процессора. =СУММЕСЛИ(F4:F15;"ж";D4:D15)/СЧЁТЕСЛИ(F4:F15;"ж") Функция СУММЕСЛИ позволяет просуммировать значения только в тех ячейках диапазона, которые отвечают заданному критерию (в нашем случае ребенок является мальчиком). Функция СЧЁТЕСЛИ подсчитывает количество значений, удовлетворяющих заданному критерию. Таким образом и получаем требуемое.
6. Подсчитайте доли отличниц среди всех девочек отнесем количество девочек-отличниц к общему количеству девочек (здесь и воспользуемся набором значений из одной из вспомогательных колонок): =СЧЁТЕСЛИ(H4:H15;"да")/СЧЁТЕСЛИ(F4:F15;"ж")
7. Определите отличие средних баллов разновозрастных детей (воспользуемся в расчетах вспомогательной колонкой Возраст): =ABS(СУММЕСЛИ(G4:G15;15;D4:D15)/СЧЁТЕСЛИ(G4:G15;15)- СУММЕСЛИ(G4:G15;16;D4:D15)/СЧЁТЕСЛИ(G4:G15;16)) Обратите внимание на то, что формат данных в ячейках G16:G18 – числовой, два знака после запятой. Таким образом, задача полностью решена. На рисунке 2 представлены результаты решения для заданного набора данных:

[pic 6]

Рисунок 2 - Результат расчетов

Задание 6. Необходимо выбрать значение прибыли за определенный месяц, год и по нужному банку. В итоге имеем 3 условия отбора. Сразу по трем столбцам функция ВПР искать не может. Поэтому нам нужно объединить их в один. И, поскольку поиск производится всегда в крайнем левом (первом) столбце, то нужно добавить его в нашу таблицу тоже слева.