Составление табеля учета часов работы сотрудников отдела за месяц

2.1. Перейдите на Лист3 (или создайте его) в файле Фамилия_4_n.xls и переименуйте его на Сентябрь.

2.2. Впишите в ячейку С2 текст "Табель выхода на работу сотрудников за сентябрь 2015 года".

2.3. Составьте каркас таблицы табеля такого вида:

2.4. Заполните значениями столбцы Должность и Ставка.

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

2.6. Создайте верхний колонтитул, в который впишите слева свою фамилию, справа – свою группу.

2.7. Заполните в произвольном порядке ячейки Е6:АН10 данными, используя следующие значения:

8 – количество отработанных часов в день для целой ставки;

4 – количество отработанных часов в день для 0,5 ставки;

в – выходной день (суббота, воскресенье, праздничные);

б – больничный;

к – командировка;

о – отпуск.

2.8. Добавьте в конце месяца столбцы для:

- определения общего числа часов работы каждого сотрудника;

- определения количества рабочих дней каждого сотрудника;

- определения количества дней на больничном каждого сотрудника;

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

2.9. Измените разметку страницы таким образом, чтобы вся информация на ней занимала только одну страницу с альбомной ориентацией.

2.10. Вычислите суммарное количество часов в столбце АI.

2.11. Вычислите количество рабочих дней в столбце АJ с помощью функции СЧЕТ, которая подсчитывает количество числовых ячеек указанного диапазона.

2.12. Вычислите количество больничных дней в столбце АК, воспользовавшись функцией СЧЕТЕСЛИ, которая подсчитывает количество ячеек, удовлетворяющих условию.

2.13. Аналогично предыдущему пункту вычислите количество командировочных дней в столбце АL.

2.14. Скопируйте лист Сентябрь два раза и переименуйте созданные листы на Октябрь и Ноябрь. Сохраните изменения в файле Фамилия_4_n.xls.

2.15. Внесите изменения в данные ячеек Е6:АН10 на листах Октябрь и Ноябрь и измените название месяцев в тексте ячейки С2.

2.16. Вставьте на листе Октябрь столбец для 31-го числа и внесите изменения диапазонов в формулы.

2.17. Создайте новый лист Оплата, на котором сформируйте таблицу вида:

2.18. Вычислите в столбце G стоимость одного часа для каждого сотрудника как значение оклада, разделенное на произведение трех значений: 1) среднее количество рабочих дней в месяце (22 дня при пятидневной форме), 2) количество рабочих часов в дне (8 часов) и 3) размер ставки. Так, в ячейке G3 формула будет иметь вид: =F3/(22*8*D3).

2.19. Вычислите в столбце Н стоимость одного рабочего дня каждого сотрудника как значение оклада, разделенное на количество рабочих дней в месяце.

2.20. Вычислите в столбце I оплату за отработанные часы в сентябре, перемножив значение стоимости одного часа работника на реальное количество отработанных часов в этом месяце.

2.21. Вычислите в столбце J оплату за больничные в сентябре по правилу:

– если стаж менее 5 лет, выплата составит 60% среднего заработка;

– если стаж от 5 до 8 лет, выплата составит 80% среднего заработка;

– если стаж более 8 лет, выплата составит 100% среднего заработка.

Т. е., надо проверить стаж на принадлежность нескольким диапазонам и возникает необходимость использования вложенной функции ЕСЛИ.

Запишите эту формулу в протокол.

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

2.23. Вычислите в ячейке L3 суммарное значение ячеек I3:K3 и выполните автозаполнение на ячейки L4:L7.

2.24. Повторите вычисления в пп. 2.20 – 2.23 для октября и ноября.

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

2.26. Сохраните файл Фамилия_4_n.xls и закройте его.

Группировка листов

3.1. Создайте в своей папке файл (книгу) Имя_4_n.xls.

3.2. Создайте в этом файле новые листы до общего количества – 7. Переименуйте листы на: Январь, Февраль, Март, Апрель, Май, Июнь, Полугодие.

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

При заполнении таблицы выполните следующее:

– в ячейках В2:Е2 создайте формулы вычисления суммарных значений в соответствующих столбцах, а в ячейках Е4:Е5 – формулы вычисления суммарных значений в соответствующих строках;

– в ячейке В5 введите формулу =В41*0% и выполните автозаполнение формулы на ячейки С5:D5;

– для ячеек В2:Е2 и В4:Е5 задайте формат Денежный (Число десятичных знаков – 2, Обозначение – Тенге).

3.4. Разгруппируйте листы и измените названия месяцев в заголовке первой строки для листов Февраль – Полугодие.

3.5. Заполните произвольными числовыми данными ячейки В4:D4 на листах Январь – Июнь. Сохраните изменения в файле Имя_4_n.xls.

3.6. На листе Полугодие очистите ячейки В4:D5 и введите в ячейку В4 формулу вычисления суммы ячеек В4 во всех листах диапазона Январь – Июнь:

=СУММ(Январь:Июнь!B4)

Выполните автозаполнение этой формулы на ячейки В4:D5.

3.7. На листе Полугодие ниже существующей таблицы создайте еще одну, в которой сформируйте вычисления итогов по всем месяцам.

3.8. Сохраните файл Имя_4_n.xls и закройте его.

3.9. Устно проработайте ответы на все контрольные вопросы и письменно ответьте на контрольный вопрос, соответствующий номеру Вашего варианта.__

Контрольные вопросы

1) Как переименовать лист в книге MS Excel и изменить цвет ярлычка?

2) Как скопировать/переместить лист в пределах одной книги:

– с помощью мыши;

– используя команду меню?

3) Как переименовать ячейку MS Excel?

4) Чем отличаются формулы =СУММ(Лист1!А1:А5) и =СУММ(Лист1!А1/А5)? Что они будут вычислять?

5) В чем состоит разница между относительными ссылками на ячейки и ссылками с использованием имен ячеек?

6) Что будут вычислять формулы: 1) =СУММ(Лист1:Лист3!B4); 2) =СУММ(Лист1!B4:С4); 3) =СУММ(Лист1:Лист3!B4:С4)?

7) Как ввести одну и ту же формулу в несколько ячеек одновременно?

8) Каково назначение примечаний и как их вставлять (и удалять) в таблицу?

9) Чем отличаются формулы =СУММ(Лист1!А1:А5) и =СУММ(Лист1!А1;А5)? Что они будут вычислять?

10) Какие действия можно выполнять с рабочими листами?

11) С какой целью ячейкам присваивают собственные имена?

12) В каких случаях в ссылках на ячейку присутствует восклицательный знак? Какую роль он там играет?

13) Как присвоить ячейке или диапазону ячеек имя?

14) Как удалить ненужный лист в документе?

15) Как добавить в документе еще один лист?

16) Какое максимальное количество листов может быть в рабочей книге?

17) Как зафиксировать столбец на экране?

18) Опишите, как скрыть лист в книге MS Excel.

19) Как отобразить скрытый столбец на листе в книге MS Excel?

20) Приведите примеры формул с четырьмя различными статистическими функциями, известными Вам по выполненным лабораторным работам.

21) Может ли функция ЕСЛИ быть вложенной в другую функцию? Какое максимальное количество уровней вложенности функции?

22) Что будет вычислять формула =Лист1!A1*B1 ?

23) Как и с какой целью группируются листы?

24) В чем разница использования клавиш [Shift] и [Ctrl] при группировке листов?

25) Объясните допущенные в формулах ошибки: 1) =СУММ(Лист1B4:С4); 2) =СУММ(Лист1:Лист3:B4); 3) =Лист1!(B4+С4)?

26) Как выполнить разметку страницы таким образом, чтобы вся информация на ней занимала только одну страницу?

27) Является ли признаком ошибки в формуле появление сообщения #ДЕЛ/0! В ячейке, содержащей эту формулу?

28) Что обозначает сообщение об ошибке #######? Как ее исправить?

29) Что обозначает сообщение об ошибке #ЗНАЧ!? Как ее исправить?

30) Как подсчитать количество числовых ячеек в диапазоне?__

ПРАКТИЧЕСКАЯ РАБОТА №5