Как создать экзаменационную ведомость в excel
Цель итоговой работы – применить полученные знания и навыки для решения сложной задачи: создания экзаменационных ведомостей для студенческой группы, а также ведомости расчета стипендии студентов. Выполнение работы предполагает расположение и форматирование данных по заданному образцу.
Исходными данными для расчета стипендии являются оценки, полученные студентами на трех экзаменах. На их основе строятся формулы автоматического расчета статистических показателей группы на каждом экзамене: количество отличных, хороших, удовлетворительных и неудовлетворительных оценок, а также неявок.
Для каждого студента группы рассчитывается средний балл и количество сданных им экзаменов, по результатам трех экзаменов рассчитывается и назначается обычная или повышенная стипендия (или никакой). Базовая стипендия является настраиваемым параметром системы. Заканчивается работа построением диаграммы по полученным результатам.
1. Начало работы
1.1. Создайте новую рабочую книгу и назовите ее Итоговая_работа. Рабочая книга должна состоять из пяти листов с названиями: Экзамен_1, Экзамен_2, Экзамен_3, Стипендия и Диаграмма.
1.2. Каждый лист оформляется отдельно. Сначала будет предложен образец листа. После общего вида листа дается список, где точно указано, какие данные в какие ячейки листа следует помещать. Далее в задании приводятся указания по форматированию ячеек. Описываются произвольно вводимые пользователем данные, в образце эти данные заключены в скобки. Наконец, задается действие формул, которые нужно ввести в соответствующие ячейки таблицы, помеченные в образце как (Ф-ла) или (Формула).
2. Построение экзаменационной ведомости
2.1. Образец листа Экзамен_1:
Группа № (номер группы)
Дисциплина: (наименование)
2.2. Расположение данных в ячейках таблицы указано в следующем перечне. Если указан блок ячеек для одного данного, то эти ячейки нужно объединить. Если перечислено несколько данных через запятую и столько же ячеек через запятую, то каждое данное записывается в соответствующую ячейку.
ЭКЗАМЕНАЦИОННАЯ ВЕДОМОСТЬ
Группа № (номер группы)
Дисциплина: (наименование)
А6, А7, А8, А9, А10, А11, А12
(Фамилии, инициалы студентов)
В6, В7, В8, В9, В10, В11, В12
(Формулы индикаторов оценок)
(Формулы количества оценок каждого вида)
(Формула общего количества оценок)
2.3. Формат данных на листе Экзамен_1: шрифт – Arial 12 пт. Жирным шрифтом набираются только слова: ЭКЗАМЕНАЦИОННАЯ ВЕДОМОСТЬ, Группа, Дисциплина. Выравнивание данных в ячейках – как показано в образце.
2.4. С помощью опции Формат ячейки – Граница необходимо разделить вертикальными линиями столбцы с номерами, фамилиями студентов, номерами зачеток, оценками и подписями, как это делается в настоящей экзаменационной ведомости. Также нужно разделить горизонтальными линиями строки ведомости.
2.5. Заполняются пользователем:
а) в 3 строке - № его группы, наименование одной из дисциплин, которую преподают на 1 курсе института;
б) в строках 6 – 12 столбца В - фамилии и инициалы студентов данной группы;
в) в строках 6 – 12 столбца С – произвольные шестизначные номера зачетных книжек;
г) в строках 6 – 12 столбца D – произвольные оценки от 2 до 5 и неявки. Должны присутствовать все варианты оценок от 2 до 5 и неявки, которые обозначаются н/я.
2.6. Формулы блока F6:J12 – это индикаторы оценок каждого студента. Если студент получил за этот экзамен 5, то в строке с его фамилией в столбце F (столбец индикаторов пятерок) должна появиться единица. Если студент получил другую оценку или не явился, то в строке с его фамилией в столбце F должен стоять число ноль.
Эта задача может быть реализована с помощью логических функций ЕСЛИ, первая из которых строится в ячейке F6. Логический аргумент – это проверка совпадения содержимого ячейки D6 с оценкой данного студента с числом 5. В случае совпадения формула должна выдавать значение 1, иначе – 0.
Построив формулу индикатора пятерок для первого студента в ячейке F6, скопируйте ее в блок F7:F12.
Аналогично строятся формулы для индикаторов четверок. Разница в том, что первый индикатор четверок строится в я в ячейке G6, а оценка студента из ячейки D6 сравнивается с числом 4. Построенная формула индикатора четверок копируется на весь блок G7:G12
Для индикаторов троек и двоек формулы строятся аналогично.
Формула для индикатора неявок отличается от остальных индикаторов одной особенностью: в логическом аргументе функции ЕСЛИ содержимое ячейки D6 сравнивается не с числом, а с текстом «н/я» (текст в формуле должен быть заключен в кавычки, а в ячейках столбца D – нет).
Проверяя работу формул данного блока, убедитесь, что для каждого студента один и только один индикатор оценки равен единице, а остальные нулю. Измените в столбце D оценки студентов и проверьте работу индикаторов.
2.7. Формулы блока С14:С18 рассчитывают количество отличных, хороших, удовлетворительных, неудовлетворительных оценок и неявок. Сосчитать их, имея индикаторы оценок каждого студента, просто. Например, количество отличных оценок – это сумма индикаторов пятерок, иными словами сумма по блоку F6:F12. Аналогично рассчитывается количество других оценок и неявок.
2.8. Напротив слова ИТОГО по формуле рассчитывается общее количество оценок и неявок (сумма по блоку С14:С18), что, естественно, должно совпасть с количеством студентов в списке.
2.9. Проверьте работу всех формул, изменяя оценки студентов в столбце D.
2.10. Для придания листу вида, похожего на обычную экзаменационную ведомость, скройте столбцы с F по J. Убедитесь, что формулы продолжают работать и в скрытых столбцах.
Задание № 3. Подготовьте для каждой группы ведомость (рис. 3.4) назначения студентов на стипендию по результатам экзаменационной сессии. При выполнении данного задания потребуется произвести расчеты по более сложным, чем в предыдущем задании 2, формулам.
ВЕДОМОСТЬ НАЗНАЧЕНИЯ НА СТИПЕНДИЮ
Группа №________
Рис. 3.4. Форма стипендиальной ведомости
Ознакомьтесь с алгоритмом действий по технологии выполнения данного задания:
1. Загрузите экзаменационную ведомость.
2. На новом листе создайте ведомость стипендии (см. рис.3.4) и скопируйте в нее список группы из экзаменационной ведомости, отображенный на рис.3.9.
3. Вычислите средний балл по результатам сдачи экзаменов по каждому студенту.
если средний балл не менее 4,5, выплачивается 50%-ная надбавка к минимальной стипендии;
если средний балл от 3 (включительно) до 4,5, выплачивается минимальная стипендия;
если средний балл меньше 3, стипендия не выплачивается.
5. Подсчитайте сумму стипендиального фонда для всей группы.
6. Сверьте полученные общий вид таблицы, результаты и структуры формул с тем, что отображено на рис.3.5 и 3.6.
Рис. 3.5. Электронная таблица Ведомость назначения на стипендию в режиме отображения значений
Методика выполнения работы
выполните команду Файл, Открыть;
в диалоговом окне установите следующие параметры:
3. Оформите название и шапку ведомости назначения на стипендию согласно рис. 5.
Для этого введите название таблицы - ВЕДОМОСТЬ НАЗНАЧЕНИЯ НА СТИПЕН-
ДИЮ Группа № и названия столбцов - № п/п; Фамилия, имя, отчество; Стипендия,
задайте шрифт и тип выделения - полужирный.
4. Укажите размер минимальной стипендии в ячейке D3;
5. Вставьте два дополнительных столбца перед столбцом Стипендия и введите их названия — Средний балл и Кол-во сданных экзаменов. Сверьте полученное изображение электронной таблицы с рис. 3.5. Скорректируйте расхождение.
введите команду Формулы, Вставить функциюи выберите в диалоговом окне параметры:
Категория: Статистические
Имя: СРЗНАЧ
щелкните по кнопке , появится панель ввода аргументов функции СРЗНАЧ;
установите курсор в 1-й строке (имя Число 1) панели ввода аргументов функции, щелкните на названии листа Экзамен 1 и выберите ячейку D6 с оценкой конкретного студента по первому экзамену;
установите курсор во 2-й строке (имя Число 2), щелкните на названии листа Экзамен 1(2) и выберите ячейку D6 с оценкой того же студента по второму экзамену;
установите курсор в 3-й строке (имя Число 3), щелкните на названии листа Экзамен 1(3) и выберите ячейку D6 с оценкой того же студента по второму экзамену;
щелкните по кнопке ;
в ячейке С6 появится значение, рассчитанное по формуле:
установите курсор в ячейке С6;
наведите указатель мыши на правый нижний угол этой ячейки, добившись появления черного крестика;
нажмите левую кнопку мыши и протащите ее до конца этого столбца;
просмотрите все формулы этого столбца, устанавливая курсор в каждой ячейке.
установите курсор в ячейке D6;
введите команду Формулы, Вставить функциюи выберите в диалоговом окне параметры:
щелкните по кнопке , появится панель ввода аргументов функции СЧЕТ;
установите курсор в 1-й строке (имя Значение 1) панели ввода аргументов функции, щелкните на названии листа Экзамен 1 и выберите ячейку D6 с оценкой конкретного студента по первому экзамену;
установите курсор во 2-й строке (имя Значение 2), щелкните на названии листаЭкзамен 1(2) и выберите ячейку D6 с оценкой того же студента по второму экза-
мену;
установите курсор в 3-й строке (имя Значение 3), щелкните на названии листа Экзамен 1(3) и выберите ячейку D6 с оценкой того же студента по второму экзамену;
щелкните по кнопке ;
в ячейке D6 появится значение, рассчитанное по формуле
9. Скопируйте формулу по всем ячейкам столбца D аналогично п.7.
10. Введите формулу для вычисления размера стипендии студента в ячейку Е6. Эта формула должна иметь следующий вид: =4,5;D6=3); =3;D6=3);$D$3;0)'>$D$3*1,5 ;ЕСЛИ(И(C6>=3;_D_6=3);$D$3;0))__Внимание!'>=ЕСЛИ(И(С6>=4,5;D6=3);$D$3*1,5;ЕСЛИ(И(C6>=3; D6=3);$D$3;0))
Внимание!
1. В структуре формулы имеются вложенные функции И(. ), ЕСЛИ(. ). Для ввода этих функций надо воспользоваться
кнопкой вызова функции, находящейся в строке
ввода под панелями.
2. При наборе формулы автоматически расставляются круглые скобки и разделительный символ - точка с запятой.
3. В процессе набора формулы постоянно сравнивайте ее с выражением, которое приведено в этом пункте выше.
4. В числах для отделения целой части от дробной используется либо точка, либо запятая, что зависит от установок Excel.
5. Если после ввода формулы появится синтаксическая ошибка,
то следует проверить количество скобок, наличие разделите-
ля (точки с запятой), заменить в числе точку на запятую или
наоборот.
установите курсор в ячейке Е6;
введите команду Формулы, Вставить функцию и выберите в диалоговом окне параметры:
щелкните по кнопке , появится панель ввода аргументов функции ЕСЛИ;
курсор будет находиться в 1-й строке (имя — Логическое выражение) панели ввода аргументов функции;
нажмите кнопку вызова функции в строке ввода, выберите категорию Другие функции и функцию И, нажмите кнопку ;
появится второе окно ввода аргументов функции И, курсор автоматически будет установлен в строке Логическое значение1;
щелкните в ячейке С6, где показан средний балл этого студента, и наберите с клавиатуры условие >=4,5. В результате в этой строке должно быть выражение С6>=4,5
установите курсор на второй строке Логическое значение 2 и аналогично сформируйте выражение, которое указывает необходимое количество сданных экзаменов (в данном примере - это число 3) D6=3
щелкните по кнопке . В результате в строке ввода должно появиться выражение =ЕСЛИ(И(С6>=4,5;D6=3)
щелкните мышью на строке ввода, появится первое окно ввода аргументов для функции ЕСЛИ;
установите курсор во 2-й строке (Значение_ если_ истина), щелкните в ячейке D3 и нажмите клавишу . Появится символ $ перед именем столбца и номером строки. Введите выражение *1,5. В результате в этой строке будет выражение $D$3*1,5
установите курсор в 3-й строке ( Значение_ если_ ложь) и по аналогичной технологии введите оставшуюся часть формулы ЕСЛИ(И(С6>=3;D6=3);$D$3;0)
после окончания формирования формулы нажмите кнопку .
вводите другие оценки в экзаменационные ведомости;
измените минимальный размер стипендии.
8. Закройте рабочую книгу командой Файл, Закрыть.
Работа 3. Построение, редактирование и форматирование диаграмм
Задание № 1. Для таблицы на рис.3.7 постройте два вида диаграмм - внедренную на лист с исходными данными и на отдельном листе.
Для этого вам необходимо выполнить следующие действия:
создать рабочую книгу;
сохранить рабочую книгу;
переименовать Лист1 на Успеваемость.
Создать таблицу в соответствии с рис.3.7 и вычислить средний балл по факультету по каждому предмету.
Рис. 3.7. Таблица успеваемости к заданию 1
3. Построить внедренную диаграмму, оформив согласно рис.3.8.
4. Построить диаграмму другого типа и разместить ее на отдельном листе.
Задание №1 Создание и заполнение таблицы Excel данными и формулами.
1. Создать в среде EXCEL электронную таблицу Экзаменационная Ведомость и заполнить ее данными. Отформатировать согласно образцу.
ввести дополнительное количество столбцов, по одному на каждый вид оценки
(5 столбцов);
в первые ячейки столбцов ввести вспомогательные формулы с помощью Мастера функций;
скопировать эти формулы во все остальные ячейки дополнительных столбцов;
под таблицей (в выделенные ячейки) ввести формулы подсчета суммарного количества полученных оценок определенного вида с помощью функции СУММ;
подсчитать общее количество (ИТОГО) всех полученных оценок другим способом – Автосумма;
сделать дополнительные столбцы скрытыми;
провести коррекцию оценок и оценить полученные результаты;
переименовать текущий лист на Экзамен1, сохранить рабочую книгу под именем Задание №1.
4. На новом листе Экзамен3 создать третий вариант Экзаменационной ведомости. Провести коррекцию оценок. Сохранить документ.
1. На новом листе Стипендия создать Ведомость назначения на стипендию и скопировать на нее список группы из Экзаменационной ведомости.
2. В отдельной ячейке задать размер минимальной стипендии – 1100 руб.
3. Вычислить средний балл на основе трех Экзаменационных ведомостей по каждому студенту:
например: =СРЗНАЧ('Экзамен1'!D6;'Экзамен2'!D6;'Экзамен3'!D6)
4. Подсчитать количество сданных каждым студентом экзаменов с учетом неявок:
например: =СЧЕТ('Экзамен1'!D6;'Экзамен2'!D6;'Экзамен3'!D6)
если средний балл равен 5 – выплачивается 50% надбавка к минимальной стипендии;
если средний балл от 4 до 5 – выплачивается минимальная стипендия;
если средний балл меньше 4 – стипендия не выплачивается.
6. Подсчитать сумму стипендиального фонда для всей группы.
7. Применить условное форматирование Ведомости (заливка ячеек и начертание шрифта)
для трех столбцов: средний балл, количество сданных экзаменов, стипендия.
8. Скрыть формулы, установить защиту на неизменяемые ячейки (снять защиту с ячеек с фамилиями студентов и минимальным размером стипендии), лист, книгу.
9. Сохранить и защитить документ (установить пароль на открытие документа).
10. На новом листе рассчитать начисление стипендии по следующему условию:
В1- ЭКЗАМЕНАЦИОННАЯ ВЕДОМОСТЬ; А3-Группа №; С3-Дисциплина; А5-№ п/п; В5-Фамилия, имя, отчество; С5-№ зачетной книжки; D5-оценка; Е5-Подпись экзаменатора (записи вводим полностью, без всяких сокращений).
Выделите блок ячеек А1– Е1, щелкните правой кнопкой мыши по выделению, для вызова контекстного меню. В нем выберете команду Формат ячеек. На вкладке Выравнивание выберете опции: по горизонтали: по центру выделения; по вертикали: по верхнему краю. На вкладыше Шрифт нажмите кнопку размер и выберете 14пт и сделайте его полужирным.
3) Отформатируйте шапку таблицы.
Выделите блок ячеек А3:J5, где располагается шапка таблицы. Нажмите правую кнопку мыши на выделенном блоке ячеек и выберите команду Формат ячеек. На вкладке Выравнивание задайте параметры: по горизонтали: по значению; по вертикали: по верхнему краю; переносить по словам: поставить флажок, нажмите кнопку OK.
4)Установите нужную ширину столбцов.
Для этого установите указатель мыши к правой черте клетки с указателем имени столбца, например В, так, чтобы указатель изменил свое изображение на ↔. Нажмите левую кнопку мыши и, удерживая ее, протащите мышь так, чтобы добиться нужной ширины столбца или строки. Проделайте тоже самое с остальными столбцами А, С и т.д.
5) Заполните ячейки столбца В данными о студентах учебной группы, приблизительно 10-15 строк. (имя отчество вводить только инициалы).
6)Присвойте каждому студенту порядковый номер.
Введите в ячейку А6 число 1. Установите курсор мыши в нижний правый угол ячейки А6, так, чтобы он приобрел изображение креста и, нажав правую кнопку мыши, протяните курсор на требуемое количество ячеек, отпустив мышку выберите команду локального меню Заполнить.
7)После списка студентов в нижней части таблицы введите в ячейки столбцов А текст итоговых строк: Отлично, Хорошо, Удовлетворительно, Неудовлетворительно, Неявка, Итого.
8)Объедините две соседние ячейки,
где расположены итоговые надписи, покажем это на примере объединения двух ячеек, где расположена надпись Отлично. Выделите две ячейки: ячейку Отлично и ячейку находящуюся справа от нее. Нажмите правую клавишу мыши на выделении и выберите команду Формат ячеек. На вкладке Выравнивание установите флажок Объединение ячеек и нажмите кнопку Ok. Проведите аналогичные действия с остальными итоговыми ячейками;
9) Заполните номера зачеток.
Введите номер первой зачетки – 34544. Поставьте курсор мыши в нижний правый угол ячейки и добейтесь появления креста, нажмите правую кнопку мыши и не отпуская ее протяните курсор на нужное количество ячеек, которых требуется заполнить, выберите в появившемся меню Прогрессия, там в разделе тип выберите арифметическая прогрессия, а шаг равный 8.
10) Заполните графу оценки вводя следующие оценки 5, 4, 3, 2, неявка.
11) Введите дополнительное кол-во столбцов, для подсчета кол-ва оценок, на каждый вид оценки, всего пять: в ячейке F5 – написать ОТЛИЧНО, в G5 – ХОРОШО, в H5 – УДОВЛЕТВ., в I5 – НЕУДОВЛ., в J5 – НЕЯВКА.
12) В каждую ячейку дополнительного столбца ввести формулу,
Ее суть: например если студент Снегирев получил 5, то в ячейке F6 отображается 1, а в остальных вспомогательных столбцах G-J, в данной строке появится 0.
В ячейке F6 напишите =ЕСЛИ(D6=5;1;0); в ячейке G6 =ЕСЛИ(D6=4;1;0); в ячейке H6 =ЕСЛИ(D6=3;1;0); в ячейке I6 =ЕСЛИ(D6=2;1;0); в ячейке J6 =ЕСЛИ(D6="неявка";1;0);
13) Скопируйте эти формулы во все остальные ячейки дополнительных столбцов.
Для этого выделите блок ячеек F6:J6. Установите курсор в нижний угол выделенного блока и после появления черного крестика, нажав правую кнопку мыши, перетащите ее до конца таблицы. Выберете в контекстном меню команду Заполнить значения.
14)Определите имена блоков ячеек по каждому дополнительному столбцу.
Рассмотрим на примере дополнительного столбца F. Выделите все значения столбца F (только числа, без названия столбцов). Выберите команду Вставка, Имя, Присвоить. В диалоговом окне в строке Имя введите слово ОТЛИЧНО и щелкните кнопкой Добавить. Проведите аналогичные действия с остальными столбцами, создайте еще несколько имен блоков ячеек: ХОРОШО, УДОВЛЕТВОРИТЕЛЬНО, НЕУДОВЛЕТВОРИТЕЛЬНО, НЕЯВКА.
15) Сделайте столбец F-J скрытым. Выделите столбцы F-J и выполните команду Формат, Столбец, Скрыть.
16) Подсчет кол-ва оценок.
Установите курсор в ячейку ряда С, напротив ОТЛИЧНО. Выполните команду Вставка, Функция и в появившемся окне выберите: Категория Математические, функция Сумм. В следующем окне в строке Число1 установите курсор и выполните Вставка, Имя, Вставить. В появившемся окне выберете имя Отлично. Повторите аналогичные действия для подсчета кол-ва других оценок в ячейках С14-С17.
Подсчитайте кол-во самостоятельно ИТОГО (количество студентов явившихся на экзамен).
Составьте табель успеваемости студентов по предметам (высшая математика, физика, информатика, физкультура, русский язык).
Задание №1 Создание и заполнение таблицы Excel данными и формулами.
Самойлов Владимир Васильевич
1. Создать в среде EXCEL электронную таблицу Экзаменационная Ведомость и заполнить ее данными. Отформатировать согласно образцу.
2. Рассчитать количество полученных оценок по видам и в целом, по следующему алгоритму:
ввести дополнительное количество столбцов, по одному на каждый вид оценки (5 столбцов);
в первые ячейки столбцов ввести вспомогательные формулы с помощью Мастера функций;
например: для столбца "5" - =ЕСЛИ(D6=5;1;0)
для столбца "неявки" - =ЕСЛИ(D6=”н/я”;1;0)
скопировать эти формулы во все остальные ячейки дополнительных столбцов;
под таблицей (в выделенные ячейки) ввести формулы подсчета суммарного количества полученных оценок определенного вида с помощью функции СУММ;
подсчитать общее количество (ИТОГО) всех полученных оценок другим способом – Автосумма;
сделать дополнительные столбцы скрытыми;
провести коррекцию оценок и оценить полученные результаты;
переименовать текущий лист на Экзамен1, сохранить рабочую книгу под именем Задание №1.
3. Создать копию листа Экзамен1 и переименовать его в Экзамен2. Удалить подсчет суммарного количества полученных оценок по видам. Применить другой способ подсчета полученных оценок с помощью функции СЧЕТЕСЛИ.
4. На новом листе Экзамен3 создать третий вариант Экзаменационной ведомости. Провести коррекцию оценок. Сохранить документ.
ВЕДОМОСТЬ НАЗНАЧЕНИЯ НА СТИПЕНДИЮ
Группа №__________
Минимальный размер стипендии:
Фамилия, Имя, Отчество
Средний балл
Количество сданных экзаменов
ИТОГО стипендиальный фонд по группе:
1. На новом листе Стипендия создать Ведомость назначения на стипендию и скопировать на нее список группы из Экзаменационной ведомости.
2. В отдельной ячейке задать размер минимальной стипендии – 1100 руб.
3. Вычислить средний балл на основе трех Экзаменационных ведомостей по каждому студенту:
например: =СРЗНАЧ('Экзамен1'!D6;'Экзамен2'!D6;'Экзамен3'!D6)
4. Подсчитать количество сданных каждым студентом экзаменов с учетом неявок:
например: =СЧЕТ('Экзамен1'!D6;'Экзамен2'!D6;'Экзамен3'!D6)
5. Используя минимальное значение стипендии и учитывая, что сданы все 3 экзамена, ввести формулы начисления стипендии по условию:
если средний балл равен 5 – выплачивается 50% надбавка к минимальной стипендии;
если средний балл от 4 до 5 – выплачивается минимальная стипендия;
если средний балл меньше 4 – стипендия не выплачивается.
например: =ЕСЛИ(И(C7=5;D7=3);$E$4*1,5;ЕСЛИ(И(C7>=4;D7=3);$E$4;0))
6. Подсчитать сумму стипендиального фонда для всей группы.
7. Применить условное форматирование Ведомости (заливка ячеек и начертание шрифта)
для трех столбцов: средний балл, количество сданных экзаменов, стипендия.
8. Скрыть формулы, установить защиту на неизменяемые ячейки (снять защиту с ячеек с фамилиями студентов и минимальным размером стипендии), лист, книгу.
9. Сохранить и защитить документ (установить пароль на открытие документа).
10. На новом листе рассчитать начисление стипендии по следующему условию:
минимальная стипендия выплачивается при сданных экзаменах не ниже на "хорошо"
Тут вы можете оставить комментарий к выбранному абзацу или сообщить об ошибке.
Читайте также: