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

Тема: Создание сводной ведомости, использование встроенных функций табличного редактора.

Цель работы: Научиться работать с элементами управления формами и создать связанные таблицы «Склад» и «Касса», используя элементы управления и формулы для организации работы интернет магазина.

Количество часов на выполнение работы

Продолжительность выполнения данной практической работы составляет 2 академических часа.

Оборудование

Аппаратное обеспечение: персональный компьютер любой конфигурации.

Программное обеспечение:

• операционная система Windows 7 / 8 / 8.1 / 10.

• Microsoft Office 7 / 10 / 13 / 16.

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

Необходимо составить таблицу «Электронный журнал», где вручную должны быть заполнены поля: «Номер», «ФИО», «Оценка/день/месяц».

1. Разграничение по цветам.

Было реализовано разграничение оценок по цветам. Это было сделано благодаря условному форматированию, где было создано несколько правил для отдельных оценок, что можно было заменить одним правилом. Путём выделения области форматирования, были созданы данные правила.

1. Расчёт количества оценок.

Данный раздел был сформирован с использованием формулы подсчёта «СЧЁТЕСЛИ», которая позволила выделить необходимые оценки и записать их в специально отведённые поля. Формулы работает по принципу считать значение если оно соответствует критерию поиска, который можно задать номером ячейки или вручную, но при указании ячейки необходимо её зафиксировать символом «$», чтобы при счёте выделение не сползало и значения считались верно. В данном случае мы фиксируем у диапазона счёта столбцы, а у условия строку, так как нам будет необходимо разом захватить несколько ячеек в строке.

1. Выставление не аттестаций.

В данном поле расчёт формулы построен на условии и суммировании чисел. То есть по формуле «=ЕСЛИ(СУММ(O4:S4)

1. Раздел «Примечание».

Данный раздел работает по тому же принципу, что и выставление не аттестаций. То есть используется условие. Условие определяет, если количество пропусков больше двух с половиной, то учащийся не посещает, но если же меньше, то учащийся посещает занятия исправно. Формула: «=ЕСЛИ(T4СЧЁТ(O$3:T$3)/2;"ОТСУТСТВУЕТ";"ПОСЕЩАЕТ")».

Для большего удобства была составлена таблица на туже тематику «Ведомость». Была составлена таблица, в которой располагаются оценки учащихся за первый семестр по предметам и оценки группы по предметам за тот же семестр, также приведены средние баллы для каждого учащегося и группы по предмету. В таблице также присутствует посещаемость, итоги, общие и средние значения.

1. Раздел «Посещаемость».

Для этого раздела использовалась формула с ссылкой на другой лист в это же книге. Формула: «=СЧЁТЕСЛИ('Журнал успеваемости'!F4:N4;"нбу")». Данная формула ссылается на другой лист с диапазоном ячеек и считает количество клеток с необходимыми данными.

1. Раздел «Итого» по учащимся.

В данном разделе используется та же формула, что использовалась в предыдущей работе для вывода количества оценок по соответствию данных в ячейки. Формула: «=СЧЁТЕСЛИ($D8:$N8;S$7)». Также в подразделе «Средний балл» рассчитывает средний балл учащихся по предметам. Формула написана с учётом наличия в строках «н/а». Формула: «=СУММПРОИЗВ(D8:N8)/11».

1. Раздел «Среднее значение».

В данном разделе рассчитывается среднее значение за общее количество оценок. Формула: «=СРЗНАЧ(S8:S17)».

1. Раздел «Общее значение».

Для раздела «Посещаемость» был сделан раздел «Общее значение», который по формуле рассчитывает общее количество оценок. Формула: «=СУММ(O8:O17)».

1. Раздел «Итоги» по предметам.

Данный раздел предусматривает расчёт количества оценок по предметам со всей группы, а также средний бал по предметам с учетом не аттестаций. Формула среднего балла: «=СУММПРОИЗВ(D8:D17)/10». Формула количества оценок: «=СЧЁТЕСЛИ(D$8:D$17;$C18)».

1. Раздел «Успеваемость».

В этом разделе описывается расчёт среднего балла по группе, %качества, %успеваемости. То есть количество успевающих студентов и количество студентов с баллом ниже «5». Также рассчитывается общий балл группы. Формула среднего балла группы: «=ОКРУГЛВНИЗ(СУММПРОИЗВ(D8:N17)/10/11;2)». Формула процента успеваемости: «=СРЗНАЧ(AA8:AA17)» - рассчитывается по отдельным столбцам, в которые по условию выписываются значения «1» и «0» в зависимости от оценки студента. Формула процента качества: «=СРЗНАЧ(Z8:Z17)» - работает по тому же принципу.

1. Диаграммы.

Для построения диаграмм необходимо выделить необходимый диапазон ячеек и в меню вставка выбрать тип диаграммы, который необходим. На основе данных было построено два диаграммы.

Рисунок 177 - Диаграмма, созданная в Excel (2).

Также есть другой способ создания диаграмм. При помощи инструмента «Сводная диаграмма», который также предлагает выбрать диапазон ячеек, содержащих значения.

1. Условное форматирование.

В данной функции был рассмотрен ещё один способ выделения ячеек. Был рассмотрен градиентный способ выделения ячеек.

Контрольные вопросы

1. Показать созданную таблицу

2. Продемонстрировать работу формул