Лабораторная№7 по Excel 2010

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

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

Необходимо рассчитать доход сотрудников за три месяца на трех рабочих листах

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

Для выполнения задания №7 потребуется 6 рабочих листов Excel. Образцы результатов представлены на рисунках 2.71, 2.72, 2.73, 2.74, 2.77, 2.78.

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

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

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

Действия:

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

100000, а в ячейку D3 —текст "руб.".

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

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

1

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

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

КТУi=РДi*ККi	(5)
10
НАЧi=ОСД*КТУi/ КТУi	(6)

i 1

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

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

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

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

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

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

Действия:

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

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

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

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

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

за октябрь месяц закончены.

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

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

2

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

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

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

7.7Для выполнения Консолидации данных с листов Октябрь, Ноябрь и Декабрь

создать чистый лист книги и переименовать стандартное имя листа на имя 4-квартал.

7.8Скопировать с любого листа диапазон с данными А1:Е3 на лист 4-квартал.

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

Действия:

3

• Установить курсор в ячейку А4 листа с		именем 4-квартал. Выбрать команду
	консолидация: Данные ► Работа с		данными ►Консолидация. Появится
диалоговое окно Консолидация (рис. 2.75).			В поле окна Функция установить —

Сумма.

•В поле окна Ссылка щелкнуть на кнопке с красной стрелкой. Окно Свернется до размеров поля Консолидация — Ссылка. Щелкнуть на ярлычке листа Октябрь и

выделить диапазон ячеек А5:В15. Щелкнуть по кнопке поля Консолидация — Ссылка. Появится окно Консолидация. Щелкнуть по кнопке Добавить. Ссылка на выделенный диапазон появится в поле Список диапазонов.

•Действуя по аналогии добавить в Список диапазонов диапазоныА5:В15 листов с именами Ноябрь и Декабрь.

Флажки Подписи верхней строки и Значение левого столбца в окне Консолидация

должны быть установлены.

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

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

• Щелкнуть на кнопке ОК.

Результат консолидации представлен на рис. 2.77.

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

Действия:

•Установить курсор в ячейку С4 листа с именем 4-квартал. Выбрать команду консолидация: Данные ► Работа с данными ►Консолидация. В поле окна Функция

установить — Среднее.

4

•Из Списка диапазонов удалить установленные диапазоны.

•В Список диапазонов добавить диапазон ячеек С5:D15 за все месяцы.

Установить флажок Подписи верхней строки.

Снять флажки Значение левого столбца и Создавать связи с исходными данными.

Рис. 2.76 Диалоговое окно консолидации данных рабочих листов: среднее значение Квалификационного коэффициента и

КТУ сотрудников за 4-й квартал

7.11 Произвести консолидацию для ячейки С3 листов Октябрь, Ноябрь и Декабрь, с

использованием формулы трехмерной ссылки (3-D).

Действия:

•Выбрать команду Формулы ►Определенные имена► Присвоить имя. Появится окно Создание имени. В поле окна Имя ввести ОДС (аббревиатура слов Общий доход сотрудников). В поле Диапазон ввести формулу

=СУММ(Октябрь:Декабрь!$С$3)

(ярлычки листов: Октябрь, Ноябрь и Декабрь должны располагаться рядом и в

последовательности указанной в формуле).

• Щелкнуть по кнопке ОК.

5

•Установить курсор в ячейку СЗ листа с именем 4-квартал.

•Выбрать команду Формулы ►Определенные имена► Использовать в формуле. В

появившемся окне списка имен выделить имя ОДС, затем нажать клавишу <Enter>. В

ячейке СЗ на листе 4-квартал появится результат консолидации (рис. 2.77).

Рис. 2.77 Таблица результатов консолидации

7.12 Произвести консолидацию листов Октябрь, Ноябрь и Декабрь, используя имена

диапазона ячеек Е5:E15.

Действия:

•Сделать активным лист Октябрь. Выделить диапазона ячеек Е5:E15. Вызвать команду

Формулы ►Определенные имена ► Присвоить имя. В поле окна Имя ввести

Начислено10. ОК.

•По аналогии, присвоить имена Начислено11 и Начислено12 диапазону ячеек D5:E15

листов Ноябрь и Декабрь.

•Сделать активным лист 4-квартал. Установить курсор в ячейку D5 (ячейка начала консолидации блока ячеек D5:E15). Выбрать команду главного меню Данные►Работа

сданными ►Консолидация.

•В появившемся окне выбрать функцию консолидации — Сумма. Очистить список диапазонов. Установить курсор в поле окна Ссылка, затем выбрать команду

Формулы ►Определенные имена ► Использовать в формуле. В выпадающем списке имен выделить имя Начислено10 и нажать клавишу <Enter>, затем по кнопке

Добавить. В поле Список диапазонов появится имя добавленного диапазона ячеек.

•По аналогии, добавить в это окно имена Начислено11 и Начислено12.

Установить флажок Подписи верхней строки.

Снять флажки Значение левого столбца и Создавать связи с исходными данными.

6

• Щелкнуть по кнопке ОК.

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

4-й квартал (рис.2.77).

7.13Отформатируйте таблицу.

7.14Оформить многооконный режим работы в EXCEL с четырьмя рабочими листами по образцу рис. 2.78.

Многооконный режим работы в Excel позволяет создать команда Вид ► Окно ►Новое.

Для получения 4-х рабочих листов на экране эту команду следует повторить три раза.

После этого использовать команду Вид ► Окно ► Упорядочить все. В диалоговом окне

Расположение окон установить переключатель на пункте Рядом. Чтобы увидеть больше информации в окнах, следует использовать команды вкладки Вид, группы Масштаб,

затем уменьшить масштаб в окнах экрана.

Рис. 2.78 Многооконный режим рабочего экрана в Excel

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

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

7