Microsoft Excel

Microsoft Excel

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

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

Ход работы:

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

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

Задача 1.

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

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

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

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

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

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

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

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

= $F$4*E5

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

Для этого:

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

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

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

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

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

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

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

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

СОРТИРОВКА

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

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

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

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

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

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

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

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

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

·       Выделить диапазон А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 – малиновым цветом заливки, белым цветом шрифта.

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

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

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

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

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

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