Задание № 2. Технология работы с формулами на примере подсчета количества разных оценок в группе в экзаменационной ведомости.
В созданной в предыдущем задании 1 рабочей книге с экзаменационной ведомостью (см. рис. 3.1), хранящейся в файле с именем Session, рассчитайте:
количество оценок (отлично, хорошо, удовлетворительно, неудовлетворительно), неявок, полученных в данной группе;
общее количество полученных оценок.
Для этого потребуется разработать алгоритм, в соответствии с которым будет производиться расчет. Предлагается следующий алгоритм.
1. Ввести дополнительное количество столбцов, по одному на каждый вид оценки (всего 5 столбцов).
2. В каждую ячейку столбца ввести формулу. Суть формулы состоит в том, что напротив фамилии студента в ячейке соответствующего вспомогательного столбца вид полученной им оценки отмечается как в остальных ячейках этой строки в других дополнительных столбцах будет стоять 0. Таким образом, полученная оценка в каждом столбце будет отмечаться по следующему условию:
в столбце пятерок - если студент получил 5, то отображается 1, иначе - 0;
в столбце четверок - если студент получил 4, то отображается 1, иначе - 0;
в столбце троек - если студент получил 3, то отображается 1, иначе - 0;
в столбце двоек - если студент получил 2, то отображается 1, иначе - 0;
в столбце неявок - если не явился на экзамен, то отображается 1, иначе - 0.
Пример. Студент Снегирев получил оценку 5, тогда в ячейке столбца, в котором фиксируются пятерки, должна стоять 1, а в остальных ячейках данной строки во вспомогательных столбцах, где отмечаются остальные оценки, будут стоять нули.
3. В нижней части таблицы ввести формулы подсчета суммарного количества полученных оценок определенного вида и общее количество оценок.
4. Сверить полученные общий вид таблицы, результаты и структуры формул с тем, что показано на рис. 3.2(в режиме отображения значений) и на рис. 3.3 (в режиме показа формул).
5. Скопировать несколько раз (по числу экзаменов в сессию) этот шаблон на другие листы и провести коррекцию оценок по каждому предмету.
Внимание (При выполнении задания 2 постоянно сравнивайте ваши результаты на экране с изображением на рис.3.2)
Рис. 3.2. Электронная таблица Экзаменационная ведомость в режиме отображения значений
-
A
|
B
|
C
|
D
|
E
|
F
|
G
|
H
|
I
|
J
|
ЭКЗАМЕНАЦИОННАЯ ВЕДОМОСТЬ
|
|
|
|
|
|
|
|
|
|
Группа№
|
|
Дисциплина
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
№ п/п
|
ФИО
|
№ зачетной
книжки
|
Оценка
|
Подпись
экзаменатора
|
5
|
4
|
3
|
2
|
Неявки
|
1
|
Снегирев А.П.
|
|
5
|
|
=ЕСЛИ(D6=5;1;0)
|
=ЕСЛИ(D6=4;1;0)
|
=ЕСЛИ(D6=3;1;0)
|
=ЕСЛИ(D6=2;1;0)
|
=ЕСЛИ(D6=«н/я»;1;0)
|
2
|
Орлов К.Н.
|
|
5
|
|
= ЕСЛИ (D7= 5;1;0)
|
= ЕСЛИ (D7=4;1;0)
|
= ЕСЛИ (D7=3;1;0)
|
= ЕСЛИ (D7=2;1;0)
|
=ЕСЛИ(D7=«н/я» ;1;0)
|
3
|
Воробьева В П.
|
|
4
|
|
=ЕСЛИ(D8= 5;1;0)
|
=ЕСЛИ(D8=
4;1;0)
|
=ЕСЛИ(D8=
3;1;0)
|
=ЕСЛИ(D8= 2;1;0)
|
=ЕСЛИ(D8=«н/я»;1;0)
|
4
|
Голубкина О.Л.
|
|
3
|
|
=ЕСЛИ(D9= 5;1;0)
|
=ЕСЛИ(D9=4;1;0)
|
=ЕСЛИ(D9=3;1;0)
|
=ЕСЛИ(D9= 2;1;0)
|
=ЕСЛИ(D9=«н/я»;1;0)
|
5
|
Дятлов В.А.
|
|
2
|
|
=ЕСЛИ(D10= 5;1;0)
|
=ЕСЛИ(D10= ;1;0)
|
=ЕСЛИ(D10= 3;1;0)
|
=ЕСЛИ(D10=2;1;0)
|
=ЕСЛИ(D10=«н/я»;1;0)
|
6
|
Кукушкин М.И.
|
|
5
|
|
=ЕСЛИ(D11= 5;1;0)
|
=ЕСЛИ(D11= 4;1;0)
|
=ЕСЛИ(D11=3;1;0)
|
=ЕСЛИ(D11=2;1;0)
|
=ЕСЛИ(D11= «н/я»;1;0)
|
7
|
Иванов А.Т.
|
|
4
|
|
=ЕСЛИ(D12= 5;1;0)
|
=ЕСЛИ(D12=4;1;0)
|
=ЕСЛИ(D12=3;1;0)
|
=ЕСЛИ(D12=2;1;0)
|
=ЕСЛИ(D12= «н/я»;1;0)
|
8
|
…
|
|
|
|
|
|
|
|
|
Отлично
|
|
= СУММ (ОТЛИЧНО)
|
|
|
|
|
|
Хорошо
|
|
=СУММ (ХОРОШО)
|
|
|
|
|
|
Удовлетворительно
|
=СУММ (УДОВЛЕТ ВОРИТЕЛЬНО)
|
|
|
|
|
|
|
Неудовлетворительно
|
=СУММ(НЕУДОВЛЕТВОРИТЕЛЬНО)
|
|
|
|
|
|
|
Неявка
|
|
=СУММ(НЕЯВКИ)
|
|
|
|
|
|
|
итого
|
|
|
|
|
|
|
|
|
Риc. 3.3 Электронная таблица Экзаменационная ведомость в режиме отображения формул
Методика выполнения работы
1. Загрузите с жесткого диска рабочую книгу с именем Session:
выполните команду Файл, Открыть;
в диалоговом окне установите следующие параметры:
Папка: имя вашего каталога
Имя файла: Session
Тип файла: Книга Microsoft Excel
|
2. Проделайте подготовительную работу, вводя названия (5, 4, 3, 2, неявки) соответственно в ячейки F5, G5, H5, I5, J5 вспомогательных столбцов (см. рис.3.3).
3. В эти столбцы F - J введите вспомогательные формулы (см. ниже). Суть формулы состоит в том, что вид оценки фиксируется напротив фамилии студента в ячейке соответствующего вспомогательного столбца как 1.
Пример. Студент Снегирев получил оценку 5, тогда в ячейке F6 должна стоять 1, а в остальных вспомогательных столбцах G - J в данной строке - 0.
Для ввода исходных формул воспользуйтесь Мастером функций. Рассмотрим эту тех-
нологию на примере ввода формулы в ячейку F6:
установитекурсор в ячейку F6 и выберите мышью на панели инструментов кнопку Мастера функции
в 1-м диалоговом окне выберите вид функции
Категория - логические
Имя функции -ЕСЛИ
|
щелкните по кнопке <0K>;
во 2-м диалоговом окне, устанавливая курсор в каждой строке, введите соответствующие операнды логической функции:
Логическое выражение
|
D6=5
|
Значение, если истина,
|
1
|
Значение, если ложно,
|
0
|
щелкните по кнопке <ОК>
Примечание. Для ввода адреса ячейки в строку наберите его сами или щелкните в ячейке D6 правой кнопкой мыши.
4. С помощью Мастера функциивведите формулы аналогичным способом в остальные ячейки данной строки. В результате в ячейках F6 - J6 должно быть:
Адрес ячейки
|
Формула
|
F6
|
ЕСЛИ(D6=5;1;0)
|
G6
|
ЕСЛИ(D6=4;1;0)
|
H6
|
ЕСЛИ(D6=3;1;0)
|
I6
|
ЕСЛИ(D6=2;1;0)
|
J6
|
ЕСЛИ(D6="н/я";1;О)
|
5. Скопируйте эти формулы во все остальные ячейки дополнительных столбцов:
выделите блок ячеек F6: J6;
установите курсор в правый нижний угол выделенного блока и после появления черного крестика, нажав правую кнопку мыши, протащите ее до конца таблицы Экзаменационная ведомость;
выберите в контекстном меню команду Заполнить значения.
6. Определите имена блоков ячеек по каждому дополнительному столбцу. Рассмотрите это на примере дополнительного столбца F:
выделите все значения дополнительного столбца, например F6: адрес ячейки в столбце, в которой находится последнее значение;
введите команду Формулы, Присвоить Имя;
в диалоговом окне в строке Имя введите слово ОТЛИЧНО:
щелкните по кнопке <Ок>;
проводя аналогичные действия с остальными столбцами, вы создадите еще несколько имен блоков ячеек: ХОРОШО, УДОВЛЕТВОРИТЕЛЬНО, НЕУДОВЛЕТВОРИТЕЛЬНО, НЕЯВКА.
7. Выделите столбцы F - J целиком и сделайте их скрьггыми:
установите курсор на названии столбцов и выделите столбцы F-J;
введите команду Главная, Ячейки, Формат, Скрыть или отобразить, Скрыть столбцы.
8. Введите формулу подсчета суммарного количества полученных оценок определенного вида, используя имена блоков ячеек с помощью Мастера функций. Покажем это на примере подсчета количества отличных оценок:
установите указатель мыши в ячейку С13 подсчета количества отличных оценок;
введите команду Формулы, Вставить функцию;
в диалоговом окне Мастер функций выберите: Категория -Математические, функция - СУММ; щелкните по кнопке <ОК>;
в следующем диалоговом окне в строке Число 1 установите курсор и введите команду Вставка, Имя, Вставить;
в появившемся диалоговом окне выделите имя блока ячеек Отлично, щелкните по кнопке <ОК>;
повторите аналогичные действия для подсчета количества других оценок в ячейках С14-С17.
9. Подсчитайте общее количество (ИТОГО) всех полученных оценок другим способом (см. рис.3.2):
установите курсор в пустой ячейке С18 (рядом с ИТОГО). Эта ячейка должна обязательно находиться под ячейками, где подсчитывались суммы по всем видам оценок;
щелкните по кнопке <∑>;
выделите блок ячеек, где подсчитывались суммы по всем видам оценок, и нажмите клавишу .
10. Переименуйте текущий лист:
установите курсор на имени текущего листа и вызовите контекстное меню;
выберите параметр Переименовать и введите новое имя, например Экзамен 1.
11. Скопируйте несколько раз текущий лист Экзамен 1:
установите курсор на имени текущего листа и вызовите контекстное меню;
выберите параметр Переместить/Скопировать, поставьте флажок Создавать копию и параметр Переместить в конец, нажмите <ОК>. Обратите внимание на автоматическое наименование ярлыков новых листов.
12. Сохраните рабочую книгу с экзаменационными ведомостями:-
выполните команду Файл, Сохранить как;
в диалоговом окне установите следующие параметры:
Папка: имя вашего каталога
Имя файла:Session
Тип файла: Книга Microsoft Excel
|
13. Закройте рабочую книгу командой Файл, Закрыть.
|
Скачать 244.49 Kb.