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

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


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


Ход работы:


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


Задача 1.

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


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




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

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

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

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

= $F$4*E5

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

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

  1. Рассчитайте итоги по столбцам, а также минимальный, максимальный и средний доходы.
  2. Переименуйте Лист 1  в – Зарплата октябрь.
  3. Скопируйте содержимое листа «Зарплата октябрь» на новый лист из контекстного меню на ярлыке листа.
  4. Присвоить скопированному листу имя Зарплата ноябрь.
  5. Измените значение Премии на 32 %. Убедитесь, что программа произвела пересчет формул.
  6. Между колонками Премия и Всего начислено вставьте новую колонку Доплата.
  7. Значение доплаты примите равным 5 %.
  8. Рассчитайте значение доплаты для всех сотрудников по формуле:  Оклад * % Доплаты.
  9. Измените формулу для расчета значений колонки Всего начислено:

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

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

  1. Перейдите на лист – Ведомость за октябрь
  2. Зададим условное форматирование для чисел в столбце К выдаче по следующим условиям:
  • значений  меньше 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. Выполните сортировку по табельному номеру в порядке убывания
  2. Сделать примечание на любые 3 ячейки.
  3. Сделать условное форматирование оклада и премии за ноябрь месяц:
  • до 2000 р. – желтым цветом заливки, синим цветом шрифта;
  • от 2000 до 5000 – зеленым цветом шрифта;
  • от 5000 до 6000 – белый цвет шрифта, зеленый цвет заливки;
  • от 6000 до 8000 – красный цвет шрифта;
  • от 8000 до 10000 – розовый цвет заливки, черный цвет шрифта;
  • свыше 10000 – малиновым цветом заливки, белым цветом шрифта.
  1. Построить круговую диаграмму начисленной суммы к выдаче всех сотрудников за ноябрь месяц.
  2. Защитите лист от изменений, установите пароль
  3. Проверьте защиту. Убедитесь в неизменяемости данных.
  4. Снимите защиту с листа.


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

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

Назад
Вперед