Задание №7. Консолидация данных

Задание Имеется коллектив, состоящий из 10 сотрудников фирмы " XXX". Ежемесячный расчет дохода каждого сотрудника производится пропорционально коэффициенту трудового участия (КТУ) как произведение отработанных дней на квалификационный коэффициент сотрудника.

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

Для выполнения задания №7 потребуется 6 рабочих листов Excel. Образцы результатов представлены на рисунках 2.24, 2.25, 2.26, 2.27, 2.29, 2.30.

Порядок выполнения задания.

7.1 Открыть книгу Excel с заданиями и добавить листы, если в этом есть необходимость. Сделайте активным чистый лист книги Excel.

7.2 Заполнить рабочий лист данными по образцу рис. 2.24 — бланк для проведения расчетов.

Действия:

· Общая сумма дохода фирмы 100000 руб. вводится по схеме: в ячейку СЗ ввести число 100000, а в ячейку D3 —текст " руб.".

Рис. 2. 24 Первоначальный вид таблицы к задаче №6 по Excel

7.3 Скопировать полученную таблицу на чистый рабочий лист и переименовать скопированный лист.

7.4 Произвести расчёты в таблице по графам КТУ и Начислено, затем в строке ИТОГО.

Расчётные формулы для граф:

КТУ_i=РД_i*КК_i; (5)

НАЧ_i=ОСД*КТУ_i/ (6),

Где: i -индекс сотрудника, i=1, 2, 3, …, 10

КТУ_i -коэффициент трудового участия i-го сотрудника;

РД_i -количество рабочих дней i-го сотрудника;

КК_i -квалификационный коэффициент i-го сотрудника;

НАЧ_i -начисленный доход i-го сотрудника;

ОСД -общая сумма дохода фирмы за месяц.

Образец результатов расчетов доходов сотрудников за октябрь представлен на рис. 2.25.

Рис. 2. 25 Рабочий лист расчётов за октябрь к задаче № 6 по Excel

Действия:

• В ячейке D5 ввести формулу =В5*С5. Размножить до ячейки D14.

Появится результат расчета графы КТУ по формуле (5);

• Подсчитать итоговую сумму в ячейке В15 и D15, используя значок автосуммы.

• В ячейке Е5 ввести формулу = $C$3*D5/$D$15. Заполнить диапазон ячеек Е6: Е14 этой формулой. Появится результат расчета графы Начислено по формуле (6).

• В ячейке Е15 подсчитатьсумму. Появится число 100000 —итоговая сумма дохода фирмы " XXX" за октябрь месяц (рис. 2.25). Табличные расчеты по алгоритму (5) — (6) за октябрь месяц закончены.