Задание № 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)
![](66360_html_4747ce36.png)
Рис. 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. Закройте рабочую книгу командой Файл, Закрыть.
0k> |