Для таблицы 3.

Лабораторная работа 3. Связывание рабочих листов.

Цель:

1. Закрепить навыки работы с листами электронной книги;

2. Овладеть навыками составления табличных формул;

3. Научиться связывать данные с разных листов электронной книги.

Данные

В отделе кадров завода хранятся цеховые списки (таблица 1) и сведения об отработанных днях работников завода (таблица 2).

Таблица 1

Таблица 2

Таблица 3

Требуется:

1. Разместить таблицы 1 и 2 на различных листах.

2. Переименовать листы соответственно в «Кадры» и «Рабочие дни».

3. Составить итоговый отчет (таблица 4) на третьем листе. Переименовать данный лист в “Расчет 3”.

4. Выполнить задания.

Заполнение таблиц

Для таблицы 1.

1. Колонки A, B, C, D заполняются произвольно (4-5 записей).

2. Колонка А имеет такой числовой формат, при котором недостающие до 5 цифр числа дополняются нулями.

3. Колонки C и D имеют указанный в примере формат даты.

4. Колонка «Стаж работы» вычисляется по следующей формуле:

текущий год – год от «Даты поступления на завод». Для этого воспользоваться функциями ГОД и СЕГОДНЯ.

4. Колонка «Оклад» задается по следующей формуле:

случайное число от 2000 до 5000. При вычислении использовать функцию ОКРУГЛ.

Для таблицы 2.

1. Колонка A – копия колонки A таблицы 1.

2. Колонки B, C, D заполняются числами 28 или 22.

3. Колонка E высчитывается по формуле: «Количество рабочих дней (из них пропущено по больничному)» + «Количество рабочих дней (из них пропущено по другим причинам)».

4. Поле «Количество отработанных дней» вычисляется по формуле: «Количество рабочих дней (в месяце)» – «Количество рабочих дней (из них пропущено всего)».

Для таблицы 3.

1. Колонка A – копия колонки A таблицы 1.

2. Колонка B переносится из колонки B таблицы 1. При перенесении воспользоваться функцией ЕСЛИ.

3. Колонка «Повременно» вычисляется по формуле:

(«Количество отработанных дней» / «Количество рабочих дней (в месяце)»)*«Оклад»

4. Колонка «Районный коэффициент» вычисляется по формуле:

30% от «Оклада» + 10% от «Оклада» за каждые 2 полных года работы. При вычислении использовать функцию ОКРУГЛВНИЗ.

5. Премия начисляется в размере оклада, если человек не пропустил ни одного рабочего дня (смотри таблицу 2).

6. Колонка «Подоходный налог» вычисляется по формуле:

(«Повременно» + «Районный коэффициент» + «Премия») * 0,13.

7. Колонка «Пенсионный взнос» высчитывается по формуле:

(«Повременно» + «Районный коэффициент» + «Премия») * 0,01.

8. Колонка «Профсоюзный взнос» высчитывается по формуле:

(«Повременно» + «Районный коэффициент» + «Премия») * 0,015.

9. Колонка «Итого на руки» высчитывается по формуле:

(«Повременно» + «Районный коэффициент» + «Премия» ) – («Подоходный налог» + «Пенсионный взнос» + «Профсоюзный взнос» )

10. Для колонок "Подоходный налог", "Профсоюзный взнос", "Итого на руки" использовать числовой формат с округлением до двух знаков после запятой.

Задания(выполняются на листе «Расчет 3»)

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

1. Подсчитать двумя способами сколько людей проболели более 10 дней. Для расчета по 1-му способу использовать функцию СЧЁТЕСЛИ, для расчета по 2-му способу – табличный вид формулы и функции ЕСЛИ, СУММ.

2. Подсчитать двумя способами, сколько людей имеют оклад более 1000 р. Для расчета по 1-му способу использовать функцию СЧЁТЕСЛИ, для расчета по 2-му способу – табличный вид формулы и функции ЕСЛИ, СУММ.

3. Подсчитать количество людей пенсионного возраста, т.е. тех, кому за 60 лет. Для расчета использовать табличный вид формулы и функции ЕСЛИ, СУММ, ГОД.

4. Найти максимальный и минимальный размер оклада. Для расчета использовать функции МАКС и МИН.

5. Найти сколько людей получают самые большие оклады, т.е. отличие их окладов от максимального оклада не более чем на 10% от максимальной величины оклада. Для расчета использовать табличный вид формулы и функции ЕСЛИ, СУММ.

6. Подсчитать сколько человек имеют больничный лист и получили на руки (колонка "Итого на руки") от 1000 до 20000 рублей. Для этого ввести отдельную строку внизу таблицы и озаглавить ее. Для расчета использовать табличный вид формулы и функции ЕСЛИ, СУММ.

7. Посчитать сумму выплаченной премии, которую получили работники за 20 и более дней работы и районным коэффициентом более 3000 р. Для расчета использовать табличный вид формулы и функции ЕСЛИ, СУММ.

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

1. Чем отличается табличная формула от простой?

2. Что вычисляют функции ТДАТА, ДАТАЗНАЧ, ВРЕМЯЗНАЧ, ВРЕМЯ? Что может являться значением их параметров?

3. Какие функции округления существуют в EXCEL?

4. Какие команды необходимо использовать, чтобы добавить новый лист в книгу или убрать ненужный лист из книги?

5. Перечислите основные операции с листами.

Лабораторная работа 4. Работа с категорией «Ссылки массивы»

Цель:

1. Овладеть навыками составления формул с использованием функций ВПР, ПОИСКПОЗ и ЕНД;

2. Научиться работать с мастером функций.

Данные

В отделе кадров завода хранятся цеховые списки (таблица 1) и сведения об отработанных днях работников завода (таблица 2). Кроме этого есть данные о смене, в которую работал данный работник (таблица 4) и справочник по положенным надбавкам за работу в вечернее время (таблица 5).

Таблица 4

Таблица 5

Таблица 6