Практическая работа 2 по Excel

Практическая работа № 4

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

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

Ход работы:

1. Запустите табличный процессор Microsoft Excel.

1. Сохраните в своей папке Работа в Excel на диске D: рабочую книгу под именем Ведомость.xlsx

Задача 1.

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

1. Создайте таблицу расчета заработной платы по образцу

1. Произвести расчеты во всех столбцах таблицы.

Формулы для расчета:

• При расчете Премии используется формула: Оклад * %Премии, то есть в ячейке D5 наберите формулу = $D$4*C5, скопируйте формулу

• При расчете Всего начислено используется формула: Оклад + Премия

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

Всего начислено * %Удержания, для этого в ячейке F5 наберите формулу

= $F$4*E5

• При расчете К выдаче используется формула:

Всего начислено – Удержания.

1. Рассчитайте итоги по столбцам, а также минимальный, максимальный и средний доходы.

1. Переименуйте Лист 1  в – Зарплата октябрь.

1. Скопируйте содержимое листа «Зарплата октябрь» на новый лист из контекстного меню на ярлыке листа.

1. Присвоить скопированному листу имя Зарплата ноябрь.

1. Измените значение Премии на 32 %. Убедитесь, что программа произвела пересчет формул.

1. Между колонками Премия и Всего начислено вставьте новую колонку Доплата.

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

1. Рассчитайте значение доплаты для всех сотрудников по формуле:  Оклад * % Доплаты.

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

Оклад + Премия + Доплата

УСЛОВНОЕ ФОРМАТИРОВАНИЕ ЯЧЕЕК

1. Перейдите на лист – Ведомость за октябрь

1. Зададим условное форматирование для чисел в столбце К выдаче по следующим условиям:

• значений  меньше 5000 – выделить красным цветом шрифта

• значения между 5000 и 7000 – выделить белым цветом шрифта на красном фоне

• значения между 7000 и 10000 – зеленым цветом шрифта;

• значения большие или равно 10000 – синим цветом шрифта.

Для этого:

• Выделите числовой диапазон ячеек – К выдаче (G5:G18)

• Н а странице ленты Главная разверните кнопку Условное форматирование, Правило выделения ячеек, Меньше

• Заполните открывшееся окно как это показано на рисунке и нажмите ОК

• Чтобы задать второе условие дайте команду Условное форматирование, Правило выделения ячеек, Между

• Заполните открывшееся окно как показано на рисунке ниже, в Пользовательском формате задайте цвет шрифта – белый, цвет заливки – красный

• Самостоятельно задайте условное форматирование для оставшихся двух видов значений:

• значения между 7000 и 10000 – зеленым цветом шрифта;

• значения большие или равно 10000 – синим цветом шрифта.

СОРТИРОВКА

1. Проведите сортировку по табельному номеру в порядке возрастания. Для этого

• Выделите диапазон A5:G18

• На странице ленты Данные нажмите кнопку Сортировка

• З аполните диалоговое окно как на рисунке

1. А теперь выполним сортировку фамилий в алфавитном порядке возрастания. Для этого

• Выделите диапазон A5:G18

• На странице ленты Данные нажмите кнопку Сортировка

• З аполните диалоговое окно как на рисунке

1. Чтобы отсортировать, например значения для табельного номера не меняя остальные строки в таблице надо:

• Выделить диапазон А4:А18 (к сортируемому диапазону добавляется одна ячейка сверху – как шапка столбца)

• На странице ленты Данные нажмите кнопку 

• В открывшемся окне установите флажок Сортировать в пределах указанного выделения и нажмите кнопку ОК

КОММЕНТАРИИ К ЯЧЕЙКАМ

1. Для ячейки D4 внесем комментарий «Премия пропорционально окладу». Для этого:

• Сделайте активной ячейку D4,

• Дайте команду Рецензирование, Создать примечание

• В появившемся окне введите текст примечания – Премия пропорционально окладу

• При создании примечания в правом верхнем углу ячейки D3 появилась красная точка, которая свидетельствует о наличии примечания.

• Чтобы скрыть примечание нажмите на ссылку Показать или скрыть примечание

• При наведении указателя мыши а ячейку с красной точкой, примечание появляется как всплывающая подсказка.

• Команда Показать все примечания – скрывает (выводит) тексты всех примечаний

ЗАЩИТА РАБОЧЕГО ЛИСТА

1. Защитим рабочий лист - Зарплата октябрь от изменений. Для этого:

• Дайте команду командой Рецензирование, Защитить лист

• В строке Пароль для отключения защиты введите пароль (например, 12345), нажмите ОК

• Подтвердите пароль – 12345.

• Убедитесь, что лист защищен и невозможно ввести или удалить данные.

• Снимите защиту листа (Рецензирование, Снять защиту листа).

• Сохраните созданную вами электронную книгу Ведомость.xlsx

ЗАДАНИЯ ДЛЯ САМОСТОЯТЕЛЬНОГО ВЫПОЛНЕНИЯ:

Задание 1:

Выполнить в файле Ведомость.xlsx на рабочем листе Ведомость ноябрь:

1. Выполните сортировку по табельному номеру в порядке убывания

1. Сделать примечание на любые 3 ячейки.

1. Сделать условное форматирование оклада и премии за ноябрь месяц:

• до 2000 р. – желтым цветом заливки, синим цветом шрифта;

• от 2000 до 5000 – зеленым цветом шрифта;

• от 5000 до 6000 – белый цвет шрифта, зеленый цвет заливки;

• от 6000 до 8000 – красный цвет шрифта;

• от 8000 до 10000 – розовый цвет заливки, черный цвет шрифта;

• свыше 10000 – малиновым цветом заливки, белым цветом шрифта.

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

1. Защитите лист от изменений, установите пароль

1. Проверьте защиту. Убедитесь в неизменяемости данных.

1. Снимите защиту с листа.

Анализ результатов работы и формулировка выводов

В  отчете необходимо предоставить: в своей папке файл: Ведомость.xlsx (два рабочих листа)