Порядок выполнения работы

Практическая работа №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.

Порядок выполнения работы

Алгоритм выполнения задания.

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. Создать и заполнить таблицу расчёта стоимости, показанную на рисунке.

Алгоритм выполнения задания.

1. Записать исходные текстовые и числовые данные.

2. Рассчитать графу Стоимость, р., используя курс доллара как абсолютный адрес.

3. Рассчитать графу Стоимость, Евро, используя курс доллара и курс Евро как абсолютные адреса.

4. Рассчитать графу Доля в общей стоимости, используя итоговую Стоимость, р. как абсолютный адрес.

5. Преобразовать числовые значения в графе Доля в общей стоимости в процентные значения (Правой кнопкой мыши/Формат Ячеек/Число …):

Выделить числовые значения этой графы.

Щёлкнуть по кнопке Процентный формат.

Установить отображение процентов с одним десятичным знаком, используя кнопки Увеличить или Уменьшить разрядность.

Контрольные вопросы

1. Для чего используются абсолютные и относительные адреса ячеек?

2. В чём смысл правил автоматической настройки формул при выполнении операций копирования и перемещения?