Лабораторная работа № 2.
Тема: Создание и использование формул в MS Excel 2007. Применение абсолютной и относительной ссылки.
Оборудование:ПК,программаM S Excel 2007.
Ввод формулы в ячейки рабочего листа табличного процессора Microsoft Excel начинается с введения знака = (равно)
При вводе и редактировании формул используются следующие операторы:
1) + сложение;
2) - вычитание;
3) * умножение;
4) / деление;
5) ^ возведение в степень.
Дробные числа записываются через – [,]
При выполнении вычислений в Excel выполняются следующие правила: в первую очередь вычисляются выражения внутри круглых скобок, умножение и деление выполняются раньше сложения и вычитания, операторы с одинаковым приоритетом выполняются слева направо.
Набор формулы можно осуществлять 2 способами
· С помощью мыши (курсором мыши левой кнопкой выделяются те ячейки, адреса которых нужно записать в формуле).
· Адреса ячеек, которые используются в формуле, набираются с клавиатуры. Важно помнить, что набор формул в этом случае должен выполняться только тогда, когда клавиатура переключена на латинский алфавит.
Ввод формулы завершается нажатием клавиши Enter.
Редактирование формулы иногда лучше выполнять в строке формул.
Чтобы войти в режим редактирования ячейки, нужно, находясь в данной ячейке нажать левой кнопкой мыши 2 раза или нажать на клавишу F2. Мигающий курсор в ячейке означает, что включен режим редактирования ячейки. Чтобы сохранить внесенные изменения в ячейку нажмите на клавишу Enter. Если изменения сохранять не нужно, то нажмите на клавишу Esc. В этом случае ячейка останется без изменений.
В формулах электронной таблицы применяются два типа адресов ячеек:
· Абсолютный адрес
· Относительный адрес
Абсолютный адрес – неизменяемый при копировании формулы адрес. Например, $А$1, знак $ фиксирует букву столбца и цифру строки. Ввод абсолютного адреса начинается с ввода обычного адреса (относительного), а затем нажимаем [F4] для вставки знаков $.
Относительный адрес – адрес, который при копировании формул корректируется, т.е изменяется Относительный адрес не устанавливается. Все ячейки по умолчанию имеют относительный адрес (например, В4, А5, С13, т.е. без знака доллара.). Из относительного адреса устанавливается абсолютный адрес.
Таким образом, если возникает необходимость копирования формулы при условии, что соответствующие координаты какой-либо входящей в нее ячейки не должны изменяться, то в этом случае необходимо изменить относительные координаты ячейки на абсолютные. Это достигается подстановкой перед соответствующей координатой ячейки знака $ (с помощью нажатия функциональной клавиши F4), например A$3, $B5, $D$8.В первом случае при копировании формулы в другие ячейки неизменным останется номер строки (3), во 2-м случае неизменным будет номер колонки (В), а в 3-м случае при копировании формулы неизменными останутся координаты всей ячейки (D8).
Задание 1. Откройте файл excel-1.xlsх. Сохраните файл, присвоив ему новое имя excel-2.xlsх. Для этого выполните команду Кнопка «Office»/ Сохранить как. Удалите лист Исходные данные.
Задание 2. Используя данные на листе «Расчетная таблица» рассчитайте значение функции Zn=Xn2+2*Yn-3,354 при заданных десяти значениях Х и У, т.е. N=10.
Рекомендации к выполнению задания
При вычислении данной функции необходимо использовать относительные и абсолютные ссылки. (Об относительной и абсолютной адресации см выше в рамке.)
Значение Xn для 5-й строки находится в ячейке В5, в данном случае это относительная ссылка. Это обусловлено тем, что для 6-й строки значение Xn будет находиться в ячейке В6 и т.д. Т.е. значения Xn зависят от значений n и являются переменными. Таким образом, при наборе формулы по отношению к аргументу Xn необходимо применять относительные ссылки. Аналогичный подход в данной ситуации необходим и в отношении аргумента Yn.
Что же касается числа 3,354, то эта величина является постоянной (константой) и не зависит от значения n. Поэтому целесообразно в отношении данного числа использовать абсолютную ссылку - $E$3. Абсолютная ссылка устанавливается в режиме редактирования при нажатии на клавишу F4. В этом случае должны появиться знаки $, которые фиксируют адрес ячейки. В дальнейшем, при копировании формулы из ячейки D5 в остальные ячейки столбца (D6:D14), эта ссылка останется неизменной.
Откройте лист Расчетная таблица и установите курсор на ячейку D5. Далее наберите знак =, начав тем самым ввод формулы.
После ввода знака = используя один из перечисленных способов ввода
формулы, введите следующую формулу: =B5^2+2*C5-$E$3
где В5, С5 – относительные адреса ячеек. В них находятся значения Х и У для 5-ой строки. А ячейка Е3 имеет абсолютный адрес, где находится значение 0,344.
Набрав формулу, нажмите клавишу Enter. Если формула набрана правильно, то в ячейке D5 должен отобразиться результат расчета -0,344.
Внимание! Если в ячейку введена формула, то в самой ячейке после нажатия клавиши ввода (Enter) будет виден результат, а в строке ввода сама формула, по которой получен этот результат.
Внимание! Формула водится только в первую ячейку данного столбца. Остальные ячейки заполняются через копирование первой ячейки.
Далее необходимо скопировать формулу из ячейки D5 в ячейки D6:D14. Для этого находясь в активном режиме ячейки D5 нажмите на правую кнопку мыши. В открывшемся контекстном меню выберите пункт - Копировать. Затем выделите блок ячеек D6:D14 и нажмите клавишу Enter. Формула будет скопирована во все ячейки расчетной таблицы.
Замечание. Знак «двоеточие» между адресами означает диапазон ячеек. Например, А2:А7 означает группу ячеек от А2 до А7.
Замечание. Если в определенной ячейке имеется формула, то при установке указателя мыши на эту ячейку в строке формул отобразится содержимое ячейки (формула).
Задание 3. Вставьте новый лист. Присвойте ему имя «Расчет платежей ». Создайте и оформите таблицу расчета платежей по образцу, представленному на рисунке 5. Рассчитайте сумму платежа, за израсходованную электроэнергию за год, используя абсолютные и относительные ссылки на ячейки при создании формул. Столбец D должен реагировать на изменения тарифа.
Рисунок 5 Образец таблицы