Лабораторная работа 9
Тема: Расчетные операции в MS Excel работа с формулами. Абсолютная и относительная адресация ячеек.
Цель: Научиться производить расчеты в электронных таблицах с использованием формул.Изучить организацию расчетов с относительной и абсолютной адресацией данных в таблицах MS Excel.
Теоретические сведения
РАБОТА С ФОРМУЛАМИ. АБСОЛЮТНАЯ И ОТНОСИТЕЛЬНАЯ АДРЕСАЦИЯ ЯЧЕЕК.
Вычисления в таблицах выполняются с помощью формул. Формула может содержать числовые константы, ссылки на ячейки и функции Excel, соединённые знаками математических операций, операций сравнения, операций конкатенации. Формула, введённая в ячейку, должна начинаться со знака равенства.
После нажатия клавиши Enter в ячейке появится результат вычисления. При выделении ячейки, содержащей формулу, она появляется в строке редактирования. Формула может состоять из математических операторов, функций, значений, ссылок на ячейку. В формулах в качестве аргументов могут использоваться как числа, так и адреса ячеек. Причём по ряду причин использование в качестве аргументов адресов ячеек предпочтительнее.
Результатом выполнения формулы есть некоторое новое значение, содержащееся в ячейке, где находится формула. В формулах используются известные арифметические операторы:
сложение (+), умножение (*), вычитание (-), деление (/), процент (%), возведение в степень (^), а также операторы сравнения: равно (=), больше (>), меньше (<), не равно (<>), больше или равно (=>), меньше или равно (<=).
Порядок вычислений определяется обычными математическими законами.
Примеры формул
| Формула | Описание |
| =В1+В2 | Складывает содержимое ячеек В1 и В2 |
| =А1^(1/3) | Возводит в степень (1/3) содержимое ячейки А1 |
| =СУММ(А1:А5) | Возвращает сумму значений из диапазона А1:А5 |
Константы – текстовые или числовые значения, которые вводятся в ячейку и не могут изменяться во время вычислений.
Ошибки, возвращаемые формулами
| Ошибка | Пояснения |
| #ДЕЛ/0! | Функция содержит деление на ноль |
| #ИМЯ? | В функции используется неопределенное либо некорректное имя ячейки (Excel не может найти путь к ячейке) |
| #ЗНАЧ! | Функция содержит недопустимый тип аргумента, например, пытается произвести арифметические действия не над числами, а над текстом |
| #ССЫЛ! | Функция ссылается на несуществующую ячейку или интервал ячеек (возможно, они были удалены) |
| #ЧИСЛО! | Функция содержит некорректную математическую операцию, нарушение математических правил, например, корень из отрицательного числа |
| #Н/Д | Н/Д – является сокращением термина Неопределённые Данные. Помогает предотвратить использование ссылки на пустую ячейку |
Ссылка – это адрес ячейки, используемый в формуле. Когда формула использует в качестве аргументов адреса ячеек, говорят, что она ссылается на эти ячейки. Ссылка – это формализованное обращение к другой ячейке.
Ссылка на ячейку или группу ячеек – способ, которым указывается конкретная ячейка или несколько ячеек. Ссылка на отдельную ячейку – ее координаты. Значение пустой ячейки равно нулю. Ссылки на ячейки бывают двух типов: абсолютные и относительные ссылки.
При вводе в формулу ссылки на ячейку или диапазон ячеек можно не набирать эту ссылку с клавиатуры, а выбрать нужную ячейку или диапазон ячеек мышью методом выделения.
Часто требуется, чтобы какая-то часть формулы не изменялась при копировании. Например, когда несколько формул должны ссылаться на одну и ту же ячейку. В этом случае необходимо выполнить действия по защите формулы от изменения при копировании. Защита сводится к защите тех адресов ячеек используемых в формуле, которые должны оставаться постоянными.
Для защиты адреса ячейки необходимо установить знаки $ перед обозначением столбца и перед обозначением строки, образующими данную ячейку.
| А1 | Относительная ссылка |
| $A$1 | Абсолютная ссылка |
Комбинация предыдущих типов (например: F$7 или $F7) – смешанные ссылки. Смешанные ссылки представляют собой комбинацию абсолютных и относительных ссылок. Например, $A1 – смешанная ссылка. В смешанной ссылке знаком $ защищается строка или столбец.
Относительные ссылки – это ссылки, которые при копировании формулы изменяются автоматически в соответствии с относительным расположением исходной ячейки и создаваемой копией.
Абсолютные ссылки – это ссылки, которые при копировании не изменяются ($H$5).
Смешанные ссылки – это ссылки, которые сочетают в себе и относительную и абсолютную адресацию ($H5, H$5).
По умолчанию ссылки на ячейку в формулах рассматриваются как относительные.
Для обращения к группе ячеек используются специальные символы:
·: (двоеточие) – формирует обращение к блоку ячеек. Через двоеточие указывается левая верхняя и правая нижняя ячейки блока. Например. С4: D6 – обращение к ячейкам С4, С5, С6, D4, D5, D6.
·; (точка с запятой) – обозначает объединение ячеек. Например, D2:D4; D6:D8 – обращение к ячейкам D2, D3, D4, D6, D7, D8. Если значения в ячейках, на которые есть ссылки в формулах, меняются, то результат изменится автоматически.
Имя – это легко запоминающийся идентификатор, который можно использовать для ссылки на ячейку, группу ячеек, значение или формулу. Создать имя для ячейки можно контекстное меню Вставить/Имя диапазона... Использование имен обеспечивает следующие преимущества:
· формулы, использующие имена, легче воспринимаются и запоминаются, чем формулы, использующие ссылки на ячейки.
· при изменении структуры рабочего листа достаточно обновить ссылки лишь в одном месте (в определении имен), и все формулы, использующие эти имена, будут использовать корректные ссылки. После того как имя определено, оно может использоваться в любом месте рабочей книги. Доступ ко всем именам из любого рабочего листа можно получить с помощью окна имени в левой части строки формул.
Индивидуальные задания
Краткая справка. Переключение режимов просмотра формул и просмотра значений формул на листе - Нажмите CTRL + ` (апостроф). Или на ленте - вкладка Формулы, группа Зависимости формул, кнопка Показать формулы.
Задание 1.
Создать таблицу расчета рентабельности продукции потребительского общества по кварталам. Константы вводить в расчетные формулы в виде абсолютной адресации. (Рис.1)
Рис.1 Исходные данные для решения Задания 1.