ПРАКТИЧЕСКАЯ РАБОТА 18
АБСОЛЮТНАЯ И ОТНОСИТЕЛЬНАЯ АДРЕСАЦИЯ В MS EXCEL.
Цель работы:
ü приобрести навыки работы в среде MS Excel;
ü ознакомиться с определением абсолютного, относительного и смешанного адресов ячеек;
ü научиться применять принципы относительной и абсолютной адресации;
ü научиться применять правила формирования выражений для вычислений в электронных таблицах;
ü научиться использовать различные виды адресации при расчетах с помощью математических формул.
Обеспечение работы:
ü ПК с установленным программный обеспечением (MS Excel);
ü методические указания к выполнению работы (электронный вариант).
Порядок выполнения работы:
1. Ознакомиться с теоретическим материалом данных методических указаний;
2. Выполнить задание, которое приведено в разделе II. Порядок выполнения работы;
3. Ответить на контрольные вопросы, сделать выводы.
4. Оформить отчет.
Содержание отчета:
ü тема, цель и порядок выполнения работы;
ü привести выполненное задание;
ü привести ответы на поставленные вопросы в заданиях и скриншоты экрана;
ü ответы на контрольные вопросы;
ü выводы.
Теоретические положения
I. ССЫЛКА. ВИДЫССЫЛОК
Адрес ячейки, используемый в формуле, называют ссылкой на ячейку. При вычислениях вместо ссылки в формулу подставляется значение ячейки. Формула может ссылаться на смежные и/или несмежные диапазоны ячеек.
Ссылки бывают относительные, абсолютные и смешанные.
ü Относительная ссылка имеет вид A1.
ü Абсолютная ссылка имеет вид $A$1.
ü Смешанная ссылка имеет вид $A1 (фиксируем столбец) или A$1 (фиксируем строку).
Если Вы скопируете формулу, содержащую относительную ссылку, в другую ячейку, то формула изменится (изменятся адреса связанных с формулой ячеек). Причем Excel сделает это автоматически, не надо заботиться о корректировке формул после копирования ячеек. Это удобно и в том случае, если Вы заполняете ячейки с помощью автозаполнения. Вам достаточно ввести формулу в одну ячейку, а затем протянуть за маркер автозаполнения, после чего во всех ячейках появятся скорректированные формулы (Вы это делали в предыдущей работе).
Однако может возникнуть ситуация, когда ссылка на ячейку меняться не должна (например, несколько формул используют цену, которая постоянна для определенного вида товара). В этом случае необходимо использовать абсолютную ссылку, зафиксировав столбец и/или строку знаком $. Например, если ссылка выглядит так: =$B$1, то при автозаполнении все ячейки будут содержать формулу =$B$1.
При копировании формулы со смешанными ссылками, будет изменяться только незакрепленная часть адреса (строка или столбец).
При копировании или перемещении формулы в другое место таблицы необходимо организовать управление формированием адресов исходных данных. Поэтому в электронной таблице при написании формул наряду с веденным ранее понятием ссылки используются понятия абсолютной и относительной ссылок (адресаций).
Относительная ссылка указывает на ячейку, основываясь на ее положении относительно ячейки, в которой находится формула, например, "на две строки выше".
Относительная ссылка - это изменяющийся при копировании и перемещении формулы адрес ячейки, содержащий исходное данное. изменение адреса происходит по правилу относительной ориентации клетки с исходной формулой и клеток с операндам (данными).
Абсолютная ссылка используется для указания на ячейку ее фиксированное положение на листе, например, "ячейка находится в столбце А строки 2".
Абсолютная ссылка - это не изменяющийся при копировании и перемещении формулы адрес ячейки, содержащих исходное данное.
II. ПОРЯДОК ВЫПОЛНЕНИЯ РАБОТЫ
Задание 1. Создание ведомости начисления заработной платы.
Создать таблицы ведомости начисления заработной платы за два месяца на разных листах электронной книги, произвести расчеты, форматирование, сортировку и защиту данных. Исходные данные представлены на рис. 4.1, результаты работы – на рис. 4.2 и 4.4.
1. Запустите редактор электронных таблиц Microsoft Excel и создайте новую электронную книгу.
2. Создайте таблицу расчета заработной платы по образцу (рис. 4.1).
Введите исходные данные – Табельный номер, ФИО и Оклад, % Премии = 27 %, % Удержания = 13 %.
Выделите отдельные ячейки для значений % Премии (D4) и % Удержания (F4).
3. Произведите расчеты во всех столбцах таблицы. При расчете Премии используется формула
Премия = Оклад * % Премии.
В ячейке D5 наберите формулу
=$D$4*C5
(ячейка D4 используется в виде абсолютной адресации). Скопируйте набранную формулу вниз по столбцу автозаполнением. Краткая справка. Для удобства работы и формирования навыков работы с абсолютным видом адресации рекомендуется при оформлении констант окрашивать ячейку цветом, отличным от цвета расчетной таблицы. Тогда при вводе формул в расчетную ячейку окрашенная ячейка с константой будет вам напоминанием, что следует установить абсолютную адресацию (набором символа $ с клавиатуры или нажатием клавиши [F4]).
Формула для расчета «Всего начислено»:
Всего начислено = Оклад + Премия.
При расчете Удержания используется формула:
Удержания = Всего начислено * % Удержаний.
Для этого в ячейке F5 наберите формулу:
=$F$4*E5.
Формула для расчета столбца «К выдаче»:
К выдаче = Всего начислено – Удержания.
Рис. 4.1 - Исходные данные
4. Рассчитайте итоги по столбцам, а также максимальный, минимальный и средний доходы по данным колонки «К выдаче» (Вставка/Функция/категория – Статистические функции).
5. Переименуйте ярлычок Листа 1, присвоив ему имя «Зарплата октябрь». Для этого дважды щелкните мышью по ярлычку и наберите новое имя. Можно воспользоваться командой Переименовать контекстного меню ярлычка, вызываемого правой кнопкой мыши. Результаты работы представлены на рис. 4.2.
Рис. 4.2 - Итоговый вид таблицы расчета заработной платы за октябрь
Краткая справка. Каждая рабочая книга Excel может содержать до 255 рабочих листов. Это позволяет, используя несколько листов, создавать понятные и четко структурированные документы, вместо того чтобы хранить большие последовательные наборы данных на одном листе.
6. Скопируйте содержимое листа «Зарплата октябрь» на новый лист (Правка/Переместить/Скопировать лист). Можно воспользоваться командой Переместить/Скопировать контекстного меню ярлычка. Не забудьте для копирования поставить галочку в окне Создавать копию.
Краткая справка. Перемещать и копировать листы можно, перетаскивая их корешки (для копирования удерживайте нажатой клавишу [Ctrl]).
7. Присвойте скопированному листу название «Зарплата ноябрь». Исправьте название месяца в названии таблицы. Измените значение Премии на 32 %. Убедитесь, что программа произвела пересчет формул.
8. Между колонками «Премия» и «Всего начислено» вставьте новую колонку «Доплата» (Вставка/Столбец) и рассчитайте значение доплаты по формуле:
Доплата = Оклад * % Доплаты.
Значение доплаты примите равным 5 %.
9. Измените формулу для расчета значений колонки «Всего начислено»:
Всего начислено = Оклад + Премия + Доплата.
10. Проведите условное форматирование значений колонки «К выдаче». Установите формат вывода значений между 7000 и 10000 – зеленым цветом шрифта, меньше 7000 – красным, больше или равно 10000 – синим цветом шрифта (Формат/Условное форматирование) (рис. 4.3).
Рис. 4.3 - Условное форматирование данных
11. Проведите сортировку по фамилиям в алфавитном порядке по возрастанию (выделите фрагмент таблицы с 5 по 18 строки без итогов – выберите меню Данные/Сортировка, сортировать по – Столбец В).
12. Поставьте к ячейке D3 комментарии «Премия пропорциональна окладу» (Вставка/Примечание); при этом в правом верхнем углу ячейки появится красная точка, которая свидетельствует о наличии примечания. Конечный вид таблицы расчетной платы за ноябрь приведен на рис. 4.4.
Рис. 4.4 - Конечный вид таблицы расчета зарплаты за ноябрь
13. Защитите лист «Зарплата ноябрь» от изменений (Сервис/Защита/Защитить лист). Задайте пароль на лист, сделайте подтверждение пароля. Убедитесь, что лист защищен и удаление данных невозможно. Снимите защиту листа (Сервис/Защита/Снять защиту листа).
14. Сохраните созданную электронную книгу под именем «Зарплата» в своей папке.
Контрольные вопросы
1. Раскрыть суть понятий относительная и абсолютная адресация;
2. Алгоритм ввода ссылки на другой лист Книги;
3. Алгоритм ввода ссылки на другую Рабочую книгу;
4. Формулы Excel;
5. Перечислите последовательность выполнения команды Автосумма.
Литература
1. Гарнаев А.Ю. Использование MS Excel и VBA в экономике и финансах. - СПб.: БХВ - Санкт-Петербург, 1999.- 336 с., ил.
2. Информатика. Серия «Учебники, учебные пособия». И57 // Под ред. П.П. Беленького. – Ростов н/Д: Феникс, 2002. 448с.