На листе Консолидация произведите консолидацию данных, расположенных на листах Филиал 1иФилиал 2

Лабораторная работа № 6.

Обобщение данных.

Структуризация и консолидация.

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

1.Запустите Excel и откройте Ваш файлФамилия5-диаграммы.

2.Увеличьте высоту строки 8 на листе Отчет Продаж в 2 раза. В названиях столбцов E, F, G, укажите перенос по словам.

Для этого выполните действия:

- выделите ячейки E8, F8, G8;

- выполните командыФормат®Ячейки на вкладке Выравнивание выберите Переносить по словам;

- щелкните OK.

3.Уменьшите ширину этих столбцов таким образом, чтобы их названия располагались в две строки.

4.Выполните группировку данных в строках 10, 11, 12 используя метод группировки вручную.

Для этого выполните действия:

- выделите строки 10, 11, 12;

- выполните командыДанные®Группа и структура®Группировать.

Строки сгруппируются, при этом в таблице появилось два новых типа элементов управления:

1. кнопки с цифрами, расположенные в левом верхнем углу окна документа;

2. кнопки со знаками «-» и «+», расположенные слева от номеров строк.

5.Скройте сгруппированные строки, нажав на кнопку “-”.

Аналогично сгруппируйте данные в строках 15, 16, 17, 18.

7.Разгруппируйте сгруппированные строки.

Для этого выполните действия:

- выделите строки 10, 11, 12;

- выполните командыДанные®Группа и структура® Разгруппировать.

Аналогично разгруппируйте строки 15, 16, 17, 18.

Скопируйте лист Отчет продаж и переименуйте его на Структуризация.

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

Для этого выполните действия:

- выделите ячейку внутри списка данных;

- выполните командыДанные®Группа и структура® Создание структуры.

Слева от номеров строк и выше заголовков столбцов должны появиться кнопки двухуровневого вложения структуры.

10.Упростите вид таблицы, скрыв все сгруппированные строки и столбцы.

Скопируйте лист Список товаров и переименуйте его на Итоги.

На листе Итоги произведите группировку данных по полю Товар, используя метод группировки данных с подведением промежуточных итогов по полю Общая сумма.

Для этого выполните действия:

- отсортируйте список товаров по возрастанию по двум полям Категория и Товар одновременно;

- выполните командыДанные® Итоги;

- в открывшемся диалоговом окне Промежуточные итоги в окне При каждом изменении в выберите Товар;

- в окне Операция выберите Сумма;

- в окне Добавить итоги по выберите Общая сумма;

- установите флажок опции Итоги под данными;

- щелкните OK.

13.Удалите полученные итоги.

Для этого выполните действия:

- выделите ячейку внутри списка данных;

- выполните командыДанные® Итоги;

- в открывшемся диалоговом окне Промежуточные итоги щелкните на кнопке Убрать все.

14.Произведите группировку данных по полю Категория и с подведением промежуточных итогов по полю Количество.

Вставьте три новых листа и именами Филиал 1, Филиал 2, Консолидация.

Используя Специальную вставку для копирования формул в ячейках, скопируйте содержимое листа Отчет продаж на листы Филиал 1иФилиал 2.

Внесите изменения в данные столбцов Январь, Февраль, Март листа Филиал 2.

18.В ячейки Филиал 1!А1 иФилиал 2!А1введите соответственно Филиал 1и Филиал 2.

К ячейкам Филиал 1!А1 иФилиал 2!А1примените шрифтовое оформление.

На листе Консолидация произведите консолидацию данных, расположенных на листах Филиал 1иФилиал 2.

Для этого выполните действия:

- выделите ячейку Консолидация!В4;

- выполните командыДанные® Консолидация;

- в открывшемся диалоговом окне Консолидация в окне Функция выберите Сумма;

- в окне Ссылка с помощью мыши, перейдя на лист Филиал 1,укажите адреса ячеек, подлежащие консолидации Филиал 1!А8:D13;

- щелкните на кнопке Добавить;

- перейдите на лист Филиал 2 и выделите аналогичные ячейки Филиал 2!А8:D13 (обычно они выделяются автоматически);

- установите флажки в окнах Использовать в качестве имен и Создавать связи с исходными данными;

- щелкните OK.

На листе Консолидация должна отобразиться консолидированная таблица. Наложите на нее сетку.

21.В ячейку В2 добавьте название таблицы Суммарные значения. Примените шрифтовое оформление.

22.Произведите консолидацию тех же данных, используя функцию Среднееиз окна Функций, предварительно удалите занесенные ранее данные в окне Список диапазонов. Результат расположите начиная с ячейки Консолидация!К4. Наложите сетку.

23.В ячейку К2 добавьте называние таблицы Средние значения. Примените шрифтовое оформление.