Электронная таблица Microsoft Excel
Оценка 4.6

Электронная таблица Microsoft Excel

Оценка 4.6
doc
08.05.2020
Электронная таблица Microsoft Excel
8. Табличный процессор Microsoft Excel.doc

Тема: Создание электронной книги. Относительная и абсолютная адресация в 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 (два рабочих листа)


Тема: Создание электронной книги

Тема: Создание электронной книги

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

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

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

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

Заполните открывшееся окно как показано на рисунке ниже, в

Заполните открывшееся окно как показано на рисунке ниже, в

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

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

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

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