ПРАКТИЧЕСКОЕ ЗАНЯТИЕ N 12

ТЕМА:   Создание электронной книги. Относительная и абсолютная адресации в MS Excel.

ЦЕЛЬ: Научиться применению относительной и абсолютной адресаций при расчетах. Сортировка, условное форматирование и копирование созданных таблиц.

ВРЕМЯ НА ВЫПОЛНЕНИЕ: 2 часа.

МЕСТО ВЫПОЛНЕНИЯ РАБОТЫ  -  лаборатория «Информационных технологий»

ДИДАКТИЧЕСКОЕ И МЕТОДИЧЕСКОЕ ОБЕСПЕЧЕНИЕ: Персональный компьютер. Электронные методические указания.

 

1. Внеурочная подготовка

1.1. Подготовить титульный лист отчета и разделы основной части, включающие тему, цель работы.

1.2. Повторить способы создания формул в электронных таблицах.

 

2. Работа в кабинете

Работа с формулами в Excel. Ссылки в таблице Excel.

Формулы — это выражения, с помощью которых можно выполнять вычисления, манипулировать содержимым других ячеек, проверять условия и пр.

Формула всегда начинается со знака равенства (=).

В Excel обычно задан режим показа результатов вычислений по формулам, но можно задать и режим показа формул (вкладка Формулы -Показать формулы в группе Зависимости формул).

Формулы в системе Excel могут состоять из постоянных значений(числа, текст, даты/время), символов математических операций (+, -, *, /), круглых скобок для изменения порядка действий (система знает приоритет вычислений в математических формулах, принятый в математике),функций разного типа, ссылки на ячейки и имена ячеек.

Часто при вычислениях одинаковую операцию нужно выполнить с рядом данных. Простейший пример показан на рисунке:

Как видно из рисунка, одинаковую операцию сложения нужно выполнить с парами чисел колонок А и В в строках 1-5, затем с парами чисел колонок В и С.

Для организации подобных вычислений используется одна из глав- особенностей электронных таблиц - изменение относительной адресации при копировании формул. Формула в колонке С была написана один раз в строке 1, затем за маркер копирования/автозаполнения в правом

нижнем углу скопирована в остальные строки путем расширения выборана строки 2-5, затем получившаяся выделенная колонка была скопирована в столбец D

SNAGHTML209d774

 

Как копируем?

Выделяем ячейку, содержащую формулу (С1).

Ставим курсор на ее правый нижний угол. Видим, что в углу появился крестик.

Нажимаем на него левой клавишей мыши и, не отпуская ее, протягиваем до ячейки.

Отпускаем клавишу

 

Как видно из примера, при копировании формул ссылки изменяются:

при копировании в низ формулы строки 1 =А1+В1, для строки 2 формула будет иметь вид =А2+В2 и т. д., при копировании вправо - формула для столбца С - =А1+В1, для столбца D - =В1+С1. Такие ссылки (A1, B1, С1) называются относительными.

Если при копировании формулы ссылка в ней или ее часть (обозначение столбца или строки) не должны изменяться, используется абсолютная и смешанная адресация. Для обозначения такого адреса используют знак доллар $ перед неизменяющимся элементом (изменять тип адресации следует нажатием клавиши F4):

1) $А$1 при копировании не будут изменяться обозначение столбца  строки;

2) $А1 при копировании не будет изменяться обозначение столбца;

3) А$1 при копировании не будет изменяться обозначение строки.

Пример использования абсолютной и относительной адресации показан на рисунке :

 

Задание.

Создать таблицы ведомости начисления заработной платы за два месяца на разных листах электронной книги, произвести расчеты, форматирование, сортировку.

Порядок работы

1. Запустите Microsoft Excel и создайте новую электронную книгу.

2. На первом листе создайте таблицу расчета заработной платы по образцу:

Введите исходные данные — Табельный номер, ФИО и Оклад, % Премии =27%, % Удержания = 13 %.

При расчете Премии используется формула

Премия = Оклад * % Премии,

 в ячейке D5 наберите формулу = $D$4 * С5 (ячейка D4 используется в виде абсолютной адресации) и скопируйте автозаполнением.

Рекомендации. Для удобства работы и формирования навыков работы с абсолютным видом адресации рекомендуется при оформлении констант окрашивать ячейку цветом, отличным от цвета расчетной таблицы. Тогда при вводе формул в расчетную окрашенная ячейка (т.е. ячейка с константой) будет вам напоминанием, что следует установить абсолютную адресацию (набором символов $ с клавиатуры или нажатием клавиши [F4]).

Формула для расчета «Всего начислено»:

Всего начислено = Оклад + Премия.

При расчете Удержания используется формула

Удержание = Всего начислено * % Удержания,

 для этого в ячейке F5 наберите формулу = $F$4 * Е5 и скопируйте автозаполнением.

Формула для расчета столбца «К выдаче»:

К выдаче = Всего начислено — Удержания.

3. Рассчитайте итоги по столбцам, а также максимальный, минимальный и средний доходы поданным колонки «К выдаче» (Формулы —Библиотека функций — Статистические функции).

4. Переименуйте ярлычок Листа 1, присвоив ему имя «Зарплата октябрь». Для этого дважды щелкните мышью по ярлычку с названием листа и наберите новое имя. Можно воспользоваться командой Переименовать контекстного меню ярлычка, вызываемого щелчком правой кнопкой мыши по ярлычку с названием листа

Краткая справка. Каждая рабочая книга Excel может содержать до 255 рабочих листов. Это позволяет, используя несколько листов, создавать понятные и четко структурированные документы, вместо того, чтобы хранить большие последовательные наборы данных на одном листе.

5. Скопируйте содержимое листа «Зарплата октябрь» на новый лист с помощью команды Переместить/Скопировать контекстного меню ярлычка с названием листа. Не забудьте для копирования поставить галочку в окошке Создавать копию

Краткая справка. Перемешать и копировать листы можно, перетаскивая их корешки (для копирования удерживайте нажатой клавишу [Ctrl]).

6. Присвойте скопированному листу название «Зарплата ноябрь». Исправьте название месяца в названии таблицы. Измените значение премии на 32%. Убедитесь, что программа произвела пересчет формул.

7. Между колонками «Премия» и «Всего начислено» вставьте новую колонку «Доплата» (Вставка/Столбец) и рассчитайте значение доплаты по  формуле

Доплата = Оклад * % Доплаты.

Скопируйте формулу автозаполнением.

 Значение доплаты примите равным 8 %.

8. Измените формулу для расчета значений колонки «Всего начислено»:

Всего начислено = Оклад + Премия + Доплата.

10. Проведите сортировку по фамилиям в алфавитном порядке по возрастанию

Ø выделите фрагмент таблицы с 5 по 18 строки);

Ø выполните команду Данные/Сортировка, Сортировать по — Столбец В

Таблицу сохранить в своей папке на диске с именем Расчет зарплаты.

 

Самостоятельное задание.

 

Составить следующую таблицу расчета платы за обучение в колледже в зависимости от курса доллара:

 

 

А

В

С

1

 

 

 

2

Курс доллара

       21500

 

3

 

 

 

4

 

 

 

5

                   Плата  за обучение

 

6

Курс

Стоимость

обучения, $

Стоимость

обучения, руб.

7

1 курс

         900

 

8

2 курс

         850

 

9

3 курс

         800

 

10

4 курс

         750

 

 

Расчеты произвести по формуле:

Стоимость обучения, руб  = Стоимость обучения,$ × Курс доллара

Присвоить рабочему листу, на котором расположена таблица, имя Обучение.

3. СОДЕРЖАНИЕ ОТЧЕТА

3.1. Номер практического занятия, тема и цель работы.

3.2. Макет созданных электронных таблиц с формулами и результатами расчета.

3.2. Протокол выполненных действий.