 |
Составление и ввод формул.
|
|
|
|
Кроме текста и числа ячейка может содержать формулу, т.е. ее содержимое будет вычисляться с учетом значений других ячеек. При вводе формулы строку надо начать с символа =(равно). Ввод формулы заканчивается нажатием клавиши 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;
- щелкаю кнопку Мастер функций и выбираю в диалоговом окне параметры:
- категория функций: статистическая;
- имя функции: СЧЕТ;
- ОК.
На экране появляется палитра формул:
- Устанавливаю курсор в первой строке. Щелкаю по ярлычку листа Экзамен 1 и выбираю ячейку D6 с оценкой первого студента по первому экзамену.
- Перевожу курсор во вторую строку, щелкаю по ярлычку листа Экзамен 1(2) и выбираю ячейку D6 с оценкой первого студента по второму экзамену;
- Устанавливаю курсор в третьей строке, щелкаю по ярлычку листа Экзамен 1(3) и выбираю ячейку D6 с оценкой первого студента по третьему экзамену;
- Проверяю правильность написанной формулы (правильность выбранных ячеек для подсчета среднего бала). При отсутствии ошибок - ОК.
В ячейке D6 появится число, соответствующее числу сданных экзаменов первым студентом, посчитанное по формуле =СЧЕТ(΄Экзамен1΄!D6;΄Экзамен 1(2)΄!D6;΄Экзамен 1(3)΄! D6). Копирую формулу по всем ячейкам столбца D: делаю ячейку D6 активной, устанавливаю курсор в нижний правый угол ячейки так, чтобы указатель мыши приобрел изображение креста и, нажав левую кнопку мыши, протягиваю курсор на требуемый размер (до последнего студента – D43). Ячейки автоматически заполняются формулой и, напротив каждой фамилии студента появляется число сданных экзаменов (с учетом неявок).
4. В большинстве случаев в формулах используются относительные адреса ячеек, которые автоматически корректируются при заполнении ячеек формулами.
Но иногда необходимо, чтобы при заполнении формулами адреса некоторых ячеек оставались неизменными. В этом случае должен использоваться абсолютный адрес ячейки. Для указания, что адрес ячейки не должен изменяться, его надо привести с символом доллара ($):
$А $11 – указан абсолютный адрес (при заполнении он не будет изменяться);
А11 – указан относительный адрес (при заполнении он начнет изменяться);
$А11 – указан смешанный адрес (при заполнении имя столбца изменяться не будет, а имя строки будет корректироваться);
А $ 11 – указан смешанный адрес (при заполнении имя строки изменяться не будет, а имя столбца начнет корректироваться).
Указать абсолютный адрес можно двумя способами: просто введя символы доллара, или щелкнув в Строке формул на адресе нужной ячейки и несколько раз нажав F4 до получения необходимого результата.
В одной формуле могут использоваться как относительные, так и абсолютные адреса ячеек.
Абсолютные и относительные адреса ячеек по-разному ведут себя при копировании или переносе формул через буфер обмена и с помощью мыши. Относительные адреса при этом изменяются так, чтобы сохранить относительное расположение аргументов по отношению к ячейке с формулой, а абсолютные адреса остаются неизменными (т.е. ссылаются на те же ячейки).
|
|
|
С учетом вышесказанного и используя предыдущий опыт ввода формул, ввожу формулу для вычисления размера стипендии студента в ячейку Е6:
=ЕСЛИ(И(С6>=4,5;D6=3);$D$2*1,5;ЕСЛИ(И(C6>=4;D6=3;$D$2;0)).
Копирую данную формулу в остальные ячейки столбца Е (до 43 строки). Происходит автоматическое начисление стипендии каждому студенту, в зависимости от его успеваемости.
Построение диаграммы.
|
|
|
