Постановка задачи 1.

Дано:

Коммерческий банк в г. Москва имеет несколько филиалов в городах Тверь и Пенза. В головном отделении и во всех филиалах используются одинаковые виды вкладов. Филиалы присылают отчеты в головной офис о вкладах и выплатах в едином формате (книга Excel).

Требуется:

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

Решение.

1. Откройте новую рабочую книгу EXCEL. На одном из рабочих листов создайте таблицу с показателями деятельности одного из трех филиалов коммерческого банка (см. рис.1). Назовите рабочий лист с этой таблицей «Москва».

Рис. 1.

2. Сделайте две копии рабочего листа «Москва». Переименуйте рабочие листы (копии) в соответствии с названиями филиалов (например, Тверь, Пенза) и введите другие исходные данные в колонку «общая сумма вклада».

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

1. На основе итоговых данных по трем филиалам коммерческого банка постройте консолидирующую таблицу.

Порядок построения консолидирующей таблицы

1. Перейдите на новый рабочий лист, установите курсор в начало итоговой таблицы и задайте команду Данные à Консолидация

На экране появится диалоговое окно «Консолидация», см. рис. 2.

Рис. 2.

2. В поле «Функция» следует задать функцию «Сумма» (т.е. сложение консолидируемых данных);

3. Установите курсор в поле «ссылка» и укажите диапазон выбранных ячеек в качестве источника консолидации. Как это сделать? В данном случае, щелкните левой кнопкой мыши на ярлычке первого филиала банка (т.е. «Москва») и выделите нужный диапазон ячеек; Если итоговая таблица должна содержать в качестве заголовков, не участвующих в суммировании, верхнюю строку и левый столбец, то их необходимо включать в диапазон, а затем установить в окне соответствующие флажки.

4. Щелкните по кнопке «Добавить». В поле «Список диапазонов» появится запись, Например, Москва!$A$2-:$D$8;

5. Установите курсор в поле «ссылка» и щелкните на ярлычке второго филиала банка (т.е. «Тверь»). Щелкните по кнопке «Добавить». В поле «Список диапазонов» появится еще одна запись;

6. Так, последовательно укажите диапазоны консолидируемых данных всех филиалов банка;

7. Установите флажки в полях: «Использовать в качестве имен подписи верхней строки», «Использовать в качестве имен значения левого столбца», «Создавать связи с исходными данными».

Если Вы все сделали правильно, диалоговое окно «Консолидация» будет иметь вид, как показано на рис. 3.

Рис. 3.

8. Убедившись в правильности своих действий, щелкните по кнопке «ОК». На экране появится консолидирующая таблица, имеющая приблизительно вид, как показано на рис. 4;

Рис. 4.

9. Назовите рабочий лист с этой таблицей «Консолидация-Сумма».

Недостатки:

ü не сохраняется формат ячеек

ü не все заголовки переносятся в итоговую таблицу.

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

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

· Щелкните по кнопке 2, чтобы раскрыть всю таблицу;

· Щелкните по кнопке 1, чтобы свернуть всю таблицу;

· Щелкните по значку +, чтобы раскрыть соответствующую часть таблицы;

· Щелкните по значку -, чтобы свернуть соответствующую часть таблицы;

10. Постройте в той же рабочей книге «Консолидация» на других рабочих листах еще две консолидирующие таблицы, выбрав в качестве функций консолидации «максимум» и «минимум», назовите их соответственно - «Консолидация-Максимум» и «Консолидация-Минимум».

11. Для каждой консолидирующей таблицы постройте гистограмму, которая должна быть полностью оформлена (название гистограммы, наименование осей координат и пр.)

12. Оформите в Word отчет, в котором должны быть представлены все таблицы и диаграммы со связью (скопируйте таблицу откройте Word и задайте команду Правка/Специальная вставка, лист MS Excel и т.д.). В колонтитуле укажите № группы и свою фамилию

Дата добавления: 2015-07-20