Практическая работа №12
Тема. Абсолютный адрес в MS Excel.
Цель. Приобрести и закрепить практические навыки по применению абсолютной адресации при расчёте электронной таблицы.
Задание 1. Создать и заполнить таблицу расчёта доходов, показанную на рисунке.
Теоретические сведения
Ссылка в редакторе Excel однозначно определяет ячейку таблицы или группу ячеек рабочего листа. Ссылки указывают на то, в каких ячейках находятся значения, которые нужно применить в качестве операндов формулы. В формуле при помощи ссылок можно использовать данные, находящиеся в различных местах рабочего листа. Кроме того, можно использовать значение одной и той же ячейки в нескольких формулах.
При помощи ссылок можно также ссылаться на ячейки, находящиеся на других листах рабочей книги или в другой рабочей книге, либо даже на данные другого приложения.
После того как формула введена в ячейку, эту формулу можно перенести, скопировать или распространить на блок ячеек. Копирование и перемещение ячеек с формулами выполняется так же, как и копирование и перемещение ячеек с данными.
При перемещении формулы из одной ячейки в другую ссылки не изменяются, в то время как при копировании они автоматически изменяются.
В случае, если ссылки автоматически корректируются при копировании формулы из одной ячейки в другую, они называются относительными. По умолчанию в формулах используются именно они.
Например, если в ячейке А3 была записана формула =А1*А2, то при копировании содержимого АЗ в ячейки ВЗ и СЗ новые формулы с обновленными ссылками примут следующий вид: = В1*В2, =С1*С2 (рис. а).
Кроме относительных ссылок, в редакторе Excel часто используются абсолютные ссылки, где кроме названия столбца и номера строки используется специальный символ «$», который фиксирует часть ссылки (столбец, строку) и оставляет ее неизменной при копировании формулы с такой ссылкой в другую ячейку. Обычно абсолютные ссылки указывают на ячейки, в которых содержатся константы, используемые при вычислениях.
| 
|
| a | б |
Рис. Использование ссылок а – относительных; б – абсолютных
Например, если необходимо зафиксировать в формуле =А1*В1 значение ячейки А1 (рис. б), которое не должно изменяться в случае копирования данной формулы, то абсолютная ссылка на эту ячейку будет иметь следующий вид: $А$1. Таким образом, при копировании формулы из ячейки В2 в ячейку С2 формула примет вид =$А$1*С1.
Если требуется зафиксировать в ссылке только строку или только столбец, в котором находится используемая ячейка, в частности столбец А или строку 1, то ссылка примет вид $А1 или А$1 соответственно.
Изменить тип ссылки можно следующим образом:
1. Выделить ячейку с формулой.
2. В строке формул выделить ссылку, которую нужно изменить.
3. Нажатием клавиши F4 выбрать требуемый тип ссылки.
Последовательность изменения типов ссылок для ячейки А1 при использовании клавиши F4 такая:
· $А$1 - абсолютная ссылка (фиксированная ячейка);
· А$ 1 - изменяемый столбец и неизменяемая строка;
· $А1 - неизменяемый столбец и изменяемая строка;
· А1 - относительная ссылка.
В формулах можно использовать ссылки на ячейки как текущего листа, так и других листов рабочей книги. Например, ссылка на ячейку А1, расположенную на листе с именем Лист1, будет выглядеть так: Лист1!А1.
Формулы со ссылками могут быть получены двумя способами:
· путем непосредственного ввода ссылок с клавиатуры (вводятся латинскими буквами), что часто используется при редактировании формул;
· щелчком мыши по ячейкам, значения которых принимают участие в вычислениях.
Второй способ включает в себя следующие действия:
1. В режиме редактирования установить курсор в ту часть формулы, куда необходимо вставить ссылку, но обязательно после математического оператора или скобки.
2. Чтобы при построении формулы создать ссылку на данные текущего листа, необходимо выполнить щелчок мышью по ячейке с ними или выделить требуемый диапазон ячеек. Если данные расположены на другом рабочем листе, сначала выполнить переход на нужный лист щелчком мыши по его ярлыку внизу экрана, а затем указать ячейки с данными. Аналогично можно сослаться и на данные, содержащиеся в другой книге.
3. Нажать клавишу Enter.
Порядок выполнения работы
| A | B | C | D | E | |
| Распределение доходов в зависимости от КТУ | |||||
| Общий доход | |||||
| Фамилия | Время, ч | Квалификационнй разряд | КТУ | Сумма к выдаче | |
| Сотрудник 1 | |||||
| Итого |
Алгоритм выполнения задания.
1. Записать исходные значения таблицы, указанные на рисунке.
2. Заполнить графу Фамилия значениями Сотрудник 1÷10, используя операцию Автозаполнение.
3. Рассчитать графу КТУ как произведение времени, затраченного сотрудником, на его квалификационный разряд (формула =В4*С4).
4. Подсчитать значение Итого с помощью операции Автосумма.
5. Графа Сумма к выдаче рассчитывается как произведение общего дохода на отношение КТУ данного сотрудника к итоговому КТУ (формула =В2*D4/D14).
6. При выполнении операции Автозаполнение в графе Сумма к выдаче появляются ошибки #ЗНАЧ! и #ДЕЛ/0!. Это происходит из-зи того, что при применении формулы происходит изменение адресов в ней, например, в ячейке Е5 формула содержит адреса = В3*D5/D15.
7. Для правильного расчёта необходимо зафиксировать адреса В2 и D14, для этого:
Выделить ячейку Е4.
В строке формул отображается формула из этой ячейки, щёлкнуть по адресу В2 в этой формуле, нажать клавишу F4, у обозначения адреса появятся значки $B$4, щёлкнуть по обозначению адреса D14, нажать клавишу F4, у обозначения адреса появятся значки $D$14.
Выполнить заново операцию Автозаполнение для графы Сумма к выдаче (вместе с ячейкой Итого).
В ячейке Итого должна получиться сумма, равная Общему доходу.
Присвоить денежным величинам обозначение в рублях, для этого выделить ячейку В2, щёлкнуть кнопку Денежный формат на панели инструментов Форматирование или выполнить команду Правой кнопкой мыши/Формат Ячеек/Выравнивание.
8. Денежный, установить в поле Обозначение тип р.
9. Для проверки возможности автоматического перерасчёта таблицы заменить значения Квалификационного разряда, Времени, затраченного некоторыми сотрудниками, а также величины Общего дохода, например на 25000 р.
10. Установить для графы Сумма к выдаче отображение с двумя десятичными разрядами, для этого выделить диапазон ячеек Е4:Е14, щёлкнуть на кнопке Увеличить разрядность на панели инструментов Форматирование или выполнить команду Правой кнопкой мыши/Формат Ячеек/Выравнивание.
/Денежный, установить в поле Число десятичных знаков число 2.
Задание 2. Создать и заполнить таблицу расчёта стоимости, показанную на рисунке.
| A | B | C | D | E | |
| Стоимость программного обеспечения | |||||
| Наименование | Стоимость, $ | Стоимость, р. | Стоимость, Евро | Доля в общей стоимости, % | |
| OC Windows | |||||
| Пакет MS Office | |||||
| Редактор Corel Draw | |||||
| Графический ускоритель 3D | |||||
| Бухгалтерия 1С | |||||
| Антивирус DR Web | |||||
| Итого | |||||
| Курс валюты (к рублю) |
Алгоритм выполнения задания.
1. Записать исходные текстовые и числовые данные.
2. Рассчитать графу Стоимость, р., используя курс доллара как абсолютный адрес.
3. Рассчитать графу Стоимость, Евро, используя курс доллара и курс Евро как абсолютные адреса.
4. Рассчитать графу Доля в общей стоимости, используя итоговую Стоимость, р. как абсолютный адрес.
5. Преобразовать числовые значения в графе Доля в общей стоимости в процентные значения (Правой кнопкой мыши/Формат Ячеек/Число …):
Выделить числовые значения этой графы.
Щёлкнуть по кнопке Процентный формат.
Установить отображение процентов с одним десятичным знаком, используя кнопки Увеличить или Уменьшить разрядность.
Контрольные вопросы
1. Для чего используются абсолютные и относительные адреса ячеек?
2. В чём смысл правил автоматической настройки формул при выполнении операций копирования и перемещения?