Таким образом, у меня на трех Листах рабочей книги SESSION.XLT. появился Шаблон. (Перед вводом данных в конкретный лист рабочей книги снимаю выделение со всех остальных листов). В ячейках D3 каждого листа ввожу текст с названием соответствующей дисциплины: математика, физика, история.
Для подсчета количества различных оценок в группе необходимо добавить столбцы для каждого вида оценки: столбец F для пятерок (ячейкаF5), столбец G для четверок (ячейкаG5), столбец H для троек (ячейка H5), столбец I для двоек (ячейка I5), столбец J для неявок (ячейка J5). В данные ячейки вписываю соответствующие оценки. Форматирую По центру (аналогично форматированию ячеек в Шаблоне). Выделяю вновь созданные столбцы, на панели инструментов щелкаю кнопку Границы и выбираю разделительные линии таблицы (внешние и внутренние).
В ячейку E6 ввожу текст с соответствующей фамилией экзаменатора - Enter. Произвожу автозаполнение ячеек столбца E: делаю ячейку E6 активной, устанавливаю курсор в нижний правый угол ячейки так, чтобы указатель мыши приобрел изображение креста и, нажав левую кнопку мыши, протягиваю курсор на требуемый размер. Произвожу подобную операцию на всех листах Экзаменационной ведомости.
В столбце D каждой ведомости, напротив фамилий студентов проставляю оценки (количество 5,4,3,2, неявок согласно заданию).
В столбец В ввожу названия итогового количества полученных оценок в группе: В45 – отлично, В46 – хорошо, В47 – удовлетворительно, В48 – неудовлетворительно, В49 – неявки, В50 – ИТОГО.
Составление и ввод формул.
Кроме текста и числа ячейка может содержать формулу, т.е. ее содержимое будет вычисляться с учетом значений других ячеек. При вводе формулы строку надо начать с символа =(равно). Ввод формулы заканчивается нажатием клавиши Enter. Программа Excel содержит большое количество разнообразных математических, статистических, логических и многих других функций. Остается только задать значения их аргументов, делается все это с помощью мастера функций. Вызвать мастер функций можно щелчком по кнопке панели инструментов Стандартная. Другой вариант – опция Функция пункта меню Вставка. Предварительно необходимо сделать активной ячейку, в которой должна располагаться формула с нужной функцией. На экране откроется окно мастера функций. Сначала (это первый шаг) в поле Категория выбрать группу, к которой относится искомая функция, а в поле Функция указать ее имя. Если имя функции ничего не говорит, то в нижней части окна выводится подсказка о выполняемых данной функцией действиях (?). Если этой информации недостаточно дополнительно можно щелкнуть по кнопке Помощник. После того как формула выбрана, необходимо щелкнуть по кнопке ОК: таким образом можно перейти к следующему шагу. Отказаться от ввода функции можно щелчком по кнопке Отмена.
Второй шаг мастера функций состоит из задания аргументов. Для этого используется палитра формул. Здесь можно воспользоваться несколькими способами. Допустимо просто вводить значения и адреса ячеек в открывающихся полях, причем ввод каждого нового аргумента следует заканчивать нажатием клавиши Tab bли щелчком по следующему полю ввода. После того как будут введены все аргументы, необходимо нажать клавишу Enter или щелкнуть по кнопке ОК.
Второй вариант задания аргументов функции – щелкать мышью по ячейкам, содержащим эти самые аргументы (при необходимости можно выделить группы ячеек). Их адреса будут появляться в текущих полях ввода палитры формул. Если палитра мешает, то можно щелкнут по кнопке в конце поля ввода, и она будет свернута. Чтобы развернуть свернутую палитру, достаточно повторного щелчка по кнопке в конце поля ввода.
Функция может быть составляющей частью сложной формулы, как, прочем, и в одной формуле может использоваться несколько функций.
Итак, произведу ввод формулы в ячейку F6:
- устанавливаю курсор в данную ячейку;
- выбираю мышью на панели инструментов кнопку Мастера функций;
- в первом диалоговом окне выбираю вид функции:
- категория: логические.
- имя функции: ЕСЛИ.
- ОК.
На экране появилась палитра формул.
- В первой строке записываю выражение – D6=5.
- во второй строке– 1 (значение, если истина),
- в третьей строке – 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)
Чтобы не вводить данные формулы в строку каждого студента подобным образом, формулы можно скопировать. Для этого я выделяю ячейки F6 G6 H6 I6 J6, на панели инструментов щелкаю по кнопке Копировать, выделяю все строки и столбце, куда мне надо вставить формулы и нажимаю кнопку Вставить на панели инструментов. Все выделенные ячейки автоматически заполняются формулами. В столбцах F, G, H, I, J появляются нолики и единички согласно соответствующей оценке студента. Копирую формулы во все ведомости.
Несколько иначе произведу подсчет суммарного количества полученных оценок определенного вида. Устанавливаю указатель мыши в ячейку подсчета оценок «хорошо» (D46).Вызываю Мастера функции и выбираю следующие параметры:
- категория функции – математические;
- имя функции – СУММ;
- ОК.
На экране появляется палитра формул. Щелкаю по кнопке в конце поля ввода, палитра сворачивается. Делаю активной ячейку G 6 и не отпуская клавишу мыши протягиваю курсор по столбцу G до ячейки G43. Разворачиваю свернутую палитру повторным щелчком по кнопке в конце поля ввода. Проверяю правильность написанной автоматически формулы в первой строке палитры, и правильность суммы. Если все верно (ошибка может быть только в том случае, если не правильно были выделены необходимые для подсчета ячейки), нажимаю ОК. В ячейке D46 появляется сумма отличных оценок. Либо формула =СУММ(G 6:G43). Вижу ли я на экране формулу или полученное число зависит от параметров пункта меню Сервис. Сервис – Параметры – вкладка Вид – флажок строка формул (увижу формулы), если флажок не стоит – увижу число.
Аналогичным образом подсчитываю количество остальных оценок.
Подсчет общего числа (ИТОГО) всех полученных оценок можно подсчитать этим же способом, а можно и иначе, а именно:
- устанавливаю курсор в ячейке, которая находится под ячейками, где подсчитывались суммы по всем видам оценок (D50);
- щелкаю по кнопке «∑» (автосумма);
- выделяю ячейки подсчета суммы по всем видам оценок (D45:D49);
- Enter.
В ячейке D50 появляется общее число оценок (38), либо формула - =СУММ(D45:D49).
Копирую все формулы в другие ведомости. (Копирование произвожу обычным способом, который был описан выше).
Начисление стипендии.
1. Открываю Лист «Стипендия». В ячейку В1 ввожу текст Стипендия, в ячейку В2 – минимум, С2 – стипендии, D2 – 150 (размер минимальной стипендии). Столбцы А(с порядковыми номерами студентов) и В (с фамилиями студентов) копирую с файла «Шаблон». Добавляю дополнительные столбцы - Средний бал, Количество сданных экзаменов и Стипендия.
2. Нередко в формулах приходится использовать данные с другого листа рабочей книги. В этом случае нужно явно указать имя листа рабочей книги, потом поставить восклицательный знак и уже затем указать адрес ячейки с этого листа.
Если приходится переименовывать лист рабочей книги с данными уже после того, как была задана формула, то эта формула будет автоматически скорректирована в соответствии с новым именем листа.
Для вычисления среднего бала студента необходимо ввести формулу подсчета среднего бала в ячейку С6. Делаю это следующим образом:
- устанавливаю указатель мыши в ячейку С6;
- щелкаю кнопку Мастер функций и выбираю в диалоговом окне параметры:
- категория функций: статистическая;
- имя функции: СРЗНАЧ;
- ОК.
На экране появляется палитра формул:
- Устанавливаю курсор в первой строке. Щелкаю по кнопке в конце поля ввода, палитра сворачивается. Щелкаю по ярлычку листа Экзамен 1 и выбираю ячейку D6 с оценкой первого студента по первому экзамену. Разворачиваю свернутую палитру повторным щелчком по кнопке в конце поля ввода.
- Перевожу курсор во вторую строку, щелкаю по ярлычку листа Экзамен 1(2) и выбираю ячейку D6 с оценкой первого студента по второму экзамену;
- Устанавливаю курсор в третьей строке, щелкаю по ярлычку листа Экзамен 1(3) и выбираю ячейку D6 с оценкой первого студента по третьему экзамену;
- Проверяю правильность написанной формулы (правильность выбранных ячеек для подсчета среднего бала). При отсутствии ошибок - ОК.
В ячейке С6 появится значение среднего бала первого студента, рассчитанного по формуле =СРЗНАЧ(΄Экзамен1΄!D6;΄Экзамен 1(2)΄!D6;΄Экзамен 1(3)΄! D6).
Копирую формулу по всем ячейкам столбца С: делаю ячейку С6 активной, устанавливаю курсор в нижний правый угол ячейки так, чтобы указатель мыши приобрел изображение креста и, нажав левую кнопку мыши, протягиваю курсор на требуемый размер (до последнего студента - С43). Ячейки автоматически заполняются формулой. В ячейках появляются значения среднего бала каждого студента. В некоторых ячейках средний бал имеет несколько десятичных цифр. Так как мне достаточно только одного десятичного знака, то я могу уменьшить разрядность (предварительно установив курсор в нужную ячейку) при помощи кнопки на панели инструментов Форматирование в Excel «Уменьшить разрядность».
3. Для подсчета количества сданных каждым студентом экзаменов с учетом неявок ввожу формулу в ячейку D6, для составления которой так же пользуюсь Мастером функций.
- устанавливаю указатель мыши в ячейку D6;
- щелкаю кнопку Мастер функций и выбираю в диалоговом окне параметры:
- категория функций: статистическая;
- имя функции: СЧЕТ;
- ОК.