Задание_№7_Excel

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

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

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

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

1. Заполнить рабочий лист данными по образцу рис. 1.

Рис. 1. Первоначальный вид таблицы

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

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

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

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

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

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

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

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

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

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

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

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

Рис. 2. Рабочий лист расчётов за октябрь

4. Полученную таблицу скопировать два раза на свободные листы и переименовать стандартные имена листов, соответственно, на имена— Ноябрь и Декабрь.

5. Изменить данные (название месяца в заголовке таблицы, рабочие дни, общую сумму дохода) на листах Ноябрь, Декабрь по образцам рисунков 3 и 4. Произойдет автоматический пересчет результатов по алгоритму, выполненному для таблицы листа с именем Октябрь.

Рис. 3 Рабочий лист расчетов за ноябрь

Рис. 4 Рабочий лист расчетов за декабрь

6. Для выполнения Консолидации за 4-й квартал, скопировать первоначальный вариант таблицы (с данными рис. 1) на чистый лист книги и переименовать стандартное имя листа на имя 4-квартал.

7. Произвести консолидацию листов Октябрь, Ноябрь и Декабрь по физическому расположению диапазона ячеек для графы Рабочие дни.

Действия:

• В диалоговом окне Консолидация в поле окна Функция установить — Сумма.

• В поле окна Ссылка ввести диапазон ячеек с данными Рабочие дни за Октябрь месяц. Добавить его в Список диапазонов. Ссылка на выделенный диапазон появится в поле Список диапазонов.

• Действуя по аналогии добавить в Список диапазонов диапазоны данных за Ноябрь и Декабрь.

Флажок Создавать связи с исходными данными в окне Консолидация должен отсутствовать.

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

Диалоговое окно консолидации данных рабочих листов: сумма рабочих дней сотрудников за 4-й квартал

8. Произвести консолидацию для ячейки С3 листов Октябрь, Ноябрь и Декабрь, с использованием формулы трехмерной ссылки (3-D).

Действия:

• В окне Присвоение имени в поле окна ИМЯ ввести ОДС (аббревиатура слов Общий доход сотрудников). В поле окна Формула ввести формулу =СУММ(Октябрь:Декабрь!$С$3)

(ярлычки листов: Октябрь, Ноябрь и Декабрь должны располагаться рядом и в последовательности указанной в формуле). Добавить созданное ИМЯ.

• В ячейку С3 вставить созданное ИМЯ и произвести консолидацию.

9. Произвести консолидацию листов Октябрь, Ноябрь и Декабрь, используя имена диапазона ячеек D5:E15.

10. Оформить многооконный режим работы в EXCEL с четырьмя рабочими листами

Самостоятельная работа.

Самостоятельная работа заключается в консолидации по типу функции — среднее, для расчета среднего дохода сотрудников фирмы за квартал с применением трехмерной ссылки (3-D) для диапазона ячеек Е5:Е15. Результат консолидации поместить в соседнем столбце F5:F15. Оформить <шапку> графы этого столбца "Средний доход".

5