Практическое задание

Практическое задание

1.  Откройте в Microsoft Excel документ Электронный журнал.xls.

2.  Проанализируйте данные и расчеты, представленные на Листе «6 А класс». Следующие задания выполняем по образцу.

3.  На Листе «6 Б класс» самостоятельно введите оценки учеников.

4.  На Листе «6 Б класс» в диапазоне ячеек B20:B29 произведите расчеты процента успеваемости, процента качества знаний, степени обученности учащихся (СОУ) и средний балл по предмету для 6 «Б» класса.

Для математического выражения качества знаний используем формулы:

1) % успеваемости = (кол-во "5" + кол-во "4" + кол-во "3") x 100% / общее количество оценок;

2) % качества знаний = (кол-во "5" + кол-во "4") х 100% / общее количество оценок;

3) степень обученности учащихся (СОУ):

СОУ = (кол-во "5" + кол-во "4"х0,64 + кол-во "3"х0,32 + кол-во "2"х0,14+ кол-во "1"х0,07) / общее количество оценок.

При этом если СОУ (степень обученности учащихся):

СОУ ≥ 64% - отличное качество знаний;

СОУ ≥ 54% - хорошее качество знаний;

СОУ ≥ 36% - удовлетворительное;

СОУ ≤ 20% - неудовлетворительное.

Примечание: неаттестованные ученики - н/а - учитываются в общем количестве учащихся, как получившие «2», если неаттестованы по неуважительной причине, и нет, если неаттестованны по уважительной причине.

Для расчетов используем функции MS Excel

·  Для того чтобы подсчитать количество оценок «5» в 6 «Б» классе нужно использовать формулу

=СЧЁТЕСЛИ(B6:U16;5)

Функция СЧЁТЕСЛИ подсчитывает количество ячеек в диапазоне, которые соответствуют одному указанному пользователем критерию.

Синтаксис функции: СЧЁТЕСЛИ(диапазон;критерий)

·  Для того чтобы подсчитать количество оценок «4», «3», «2» или «1» в 6 «Б» классе формулу формируется аналогично.

·  Для того чтобы найти общее количество оценок 6 «Б» класса нужно использовать формулу

=СУММ(B20:B24)

Функция СУММ вычисляет сумму всех чисел, указанных в качестве аргументов.

Синтаксис функции: СУММ(число1,[число2],...]) или

СУММ(диапазон чисел)

·  Для того чтобы найти % успеваемости 6 «Б» класса нужно использовать формулу

=СУММ(B20:B22)*100%/B25

·  Для того чтобы найти % качества знаний 6 «Б» класса нужно использовать формулу

=СУММ(B20:B21)*100%/B25

·  Для того чтобы найти степень обученности учащихся (СОУ) 6 «Б» класса нужно использовать формулу

=(B20+B21*0,64+B22*0,32+B23*0,14+B24*0,07)/B25

·  Для того чтобы определить характеристику качества обучения 6 «Б» класса нужно использовать формулу

=ЕСЛИ(B28>=64%;"отличное";ЕСЛИ(B28>=54%;"хорошее";ЕСЛИ(B28>=36%;"удовлетворительное";ЕСЛИ(B28<=20%;"неудовлетворительное"))))

Функция ЕСЛИ возвращает одно значение, если указанное условие дает в результате значение ИСТИНА, и другое значение, если условие дает в результате значение ЛОЖЬ.

Синтаксис функции:

ЕСЛИ(лог_выражение, [значение_если_истина], [значение_если_ложь])

Примечание: Для создания более сложных проверок в качестве аргументов «значение_если_истина» и «значение_если_ложь» можно использовать до 64 вложенных функций ЕСЛИ.

·  Для того чтобы найти средний бал по предмету 6 «Б» класса нужно использовать формулу

=СРЗНАЧ(B6:U16)

Функция СРЗНАЧ возвращает среднее значение (среднее арифметическое) аргументов.

Синтаксис функции: СРЗНАЧ(число1; [число2];...)

5.  Отформатируйте полученные результаты

·  Для того чтобы уменьшить разрядность числа нужно на вкладке Главная несколько раз щелкнуть по кнопке Уменьшить разрядность

·  Для того чтобы придать числу процентный формат нужно на вкладке Главная щелкнуть по кнопке Процентный формат

6.  Создайте сводную таблицу по 6 «А» и 6 «Б» классам, в которой получите среднее значение процента успеваемости, процента качества знаний, степени обученности учащихся (СОУ) и средний балл по предмету двух классов.

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

В нашей задаче сводная таблица будет построена по данным двух таблиц, в этом случае необходим другой инструмент Мастер сводных таблиц и диаграмм.

По умолчанию данный инструмент скрыт от пользователя, его можно вызвать, выполнив следующие действия:

·  Для этого щелкаем кнопку Настройки панели быстрого доступа и выбираем Другие команды.

·  В окне Настройка панели быстрого доступа находим инструмент Мастер сводных таблиц и диаграмм и добавляем его на панель быстрого доступа, щелкнув по кнопке Добавить.

·  После добавления кнопка Мастера сводных таблиц и диаграмм появится на панели быстрого доступа:

·  Для того чтобы решить нашу задачу и создать сводную таблицу по данным 6 «А» и 6 «Б» классов, в которой получить среднее значение процента успеваемости, процента качества знаний, степени обученности учащихся (СОУ) и средний балл по предмету двух классов нужно выполнить следующие действия:

-  Ставим курсор на листе «6 А класс» в ячейку A19 и нажимаем инструмент «Мастера». В открывшемся окне отмечаем, что создать таблицу хотим в Нескольких диапазонах консолидации. То есть нам нужно объединить несколько мест с нужной нам информацией. Вид создаваемого отчета –Сводная таблица и щелкаем по кнопке Далее.

-  Следующий этап – создание полей, в нашей случае их будет два – 6 А класс и 6 Б класс, выбираем Создать поля страницы и щелкаем кнопку Далее.

-  Прописываем диапазон данных, по которым будем формировать сводный отчет. Выделяем первый диапазон A19:B29 на листе «6 А класс» (данные выделяются обязательно вместе с шапкой) и щелкаем по кнопке Добавить.

-  Выбираем 1 переключатель - это первое поле сводного отчета, которому присваиваем имя «6 А класс».

-  Выделяем второй диапазон A19:B29 на листе «6 Б класс» и щелкаем по кнопке Добавить.

-  Выбираем 2 переключатель - это второе поле сводного отчета, которому присваиваем имя «6 Б класс».

-  Щелкаем по кнопке Далее.

-  Выбираем, где расположить сводную таблицу. На существующем листе или новом. Лучше выбрать новый лист, чтобы не было наложений и смещений.

-  Щелкаем по кнопке Готово.

7.  Отформатируйте полученную сводную таблицу:

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

-  Значения, которые мы видим в сводной таблице – это Сумма значений соответствующих данных двух таблиц «6 А класс» и «6 Б класс». Функция Сумма выполняется по умолчанию. В нашей задаче необходимо найти Среднее значение данных, следовательно, функцию необходимо изменить для этого в поле Значения конструктора Сводной таблицы выбираем команду Параметры полей значений…

-  В диалоговом окне Параметры поля значений выбираем нужную функцию Среднее и OK. Значения пересчитались.

-  Уберем из сводного отчета строки о количествах оценок. Для этого в поле Названия строк снимите флажки с ненужных полей

-  Наша задача решена, сводный отчет получен:

8.  Когда вы создавали сводную таблицу, то формировали два поля (или две страницы) данных.

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

9.  Свою работу сохраните под именем Неделя 2В. xlsx и пришлите преподавателю на проверку.