Добавьте промежуточные итоги, определив Стоимость по определенному виду товара.

17. Сохраните рабочую книгу

Формулировка задания: MS Excel. Создание сводных и консолидированных таблиц

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

Контрольный пример

1. Запустите Microsoft Excel.
2. Откройте книгу с контрольным примером.
3. Добавьте в книгу новый рабочий лист и переименуйте его в лист с именем «Список».
4. Скопируйте ранее созданную таблицу с листа «Сортировка» и отмените все условия сортировки, возвращая таблице первоначальный вид.

Создание сводной таблицы

1. 

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

Рисунок Ошибка! Закладка не определена. 43 - Таблица исходных данных с листа «Список»

1. Выберите в пункте меню Данные команду Сводная таблица.
2. Microsoft Excel запустит Мастер сводных таблиц и диаграмм – шаг 1 из 3. На данном шаге построения сводной таблицы указывается тип источника данных и вид создаваемого отчета. Установите переключатели согласно приведенному ниже рисунку и нажмите кнопку Далее.

Рисунок 144 - Мастер сводных таблиц и диаграмм – шаг 1 из 3

1. 
   Далее во втором окне Мастер сводных таблиц и диаграмм, вам требуется указать диапазон, в котором находятся исходные данные. Если вы правильно выполнили пункт 5 контрольного задания, выделили одну ячейку в списке, то мастер правильно заполнит это окно диалога, нажмите кнопку Далее.

Рисунок 145 - Мастер сводных таблиц и диаграмм – шаг 2 из 3

1. В последнем окне диалога, укажите место, где вы хотите расположить сводную таблицу, в нашем примере это новый лист, смотрите ниже приведенный пример, после нажмите кнопку Готово.

Рисунок 146 - Мастер сводных таблиц и диаграмм – шаг 3 из 3

1. После нажатия кнопки Готово Microsoft Excel отобразит в новом рабочем листе пустой макет таблицы и выведет на экран панель инструментов Сводные таблицы с кнопками для каждого поля из источника данных. Для создания сводной таблицы, перетащите кнопки полей с панели инструментов в область макета:

- - в поле страницы поля Код заказа и Код фирмы;

- - в поле столбцов поле Вид продукции;

- - в поле строк поле Наименование товара;

- - в область данных поле Цена заказа.

Рисунок 147 – Построение сводной таблицы

1. После построения сводной таблицы с помощью мастера она должна иметь следующий вид:

Рисунок 148 – Сводная таблица

1. Переименуйте лист, на котором была создана сводная таблица в лист с соответствующим именем.
2. Выделите любую ячейку списка сводной таблицы, на одноименной панели инструментов выберите пункт Сводная таблица, из предложенного списка выберите команду Параметры таблицы.
3. В появившемся диалоговом окне Параметры сводной таблицы, уберите флажок в разделе Формат напротив поля Общая сумма по строкам и нажмите кнопку ОК.
4. В поле Код фирмы из списка выберите фирму под номером 4 и нажмите кнопку ОК.
5. После всех преобразований вы должны получить таблицу, приведенную ниже, по которой можно сделать следующие выводы: фирма под номеров 4 приобрела товар по следующим наименованиям, шоколадные конфеты «Орион» и «Школьная» на сумму 9300 тыс. руб. и карамель «Слами» на сумму 7800 тыс. руб.

Рисунок 149 – Итоговая сводная таблица

Создание консолидированной таблицы

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

Рисунок 150 – Таблица с исходными данными

1. Переименуйте лист с созданной таблицей в лист с именем «Январь».
2. Создайте три копии листа «Январь».
3. На второй, третьей и четвертой копии листа измените значения в столбцах Количество и Цена за единицу товара, таким образом, чтобы они отличались друг от друга по разным позициям товаров.
4. Переименуйте данные листы соответственно в листы с названиями «Февраль», «Март», «Апрель».
5. На новом листе рабочей книге произведите предварительные действия перед консолидацией данных. Скопируйте с любого листа месяца заголовки столбцов Наименование товара и Количество, сделайте активной ячейку А2.

Рисунок 151 – Подготовка листа Консолидация

1. Переименуйте данный лист в лист с названием «Консолидация».
2. В пункте меню Данные выберите команду Консолидация и заполните окно диалога Консолидация. Выберите функцию Максимум в раскрывающемся списке Функция. В данном примере в области Использовать в качестве имен установите флажок в поле Значение левого столбца, для консолидации по строкам.

Рисунок 152 – Окно диалога Консолидация

1. В поле Ссылка введите или укажите с помощью мыши последовательно следующие исходные ссылки:

· · Январь!$A$2:$B$11

· · Февраль!$A$2:$B$11

· · Март!$A$2:$B$11

· · Апрель!$A$2:$B$11

Ввод в это поле производится в следующем порядке:

· · вводим данные за январь в поле Ссылка;

· · нажимаем кнопку Добавить. Данные будут перенесены с область Список данных;

· · повторить выполнение выше описанных действий для остальных данных.

Рисунок 153 – Команда Консолидация использует ссылки из Списка диапазонов для вычисления консолидированных максимальных значений.

1. Нажмите кнопку ОК. Microsoft Excel вычислит максимальные исходные значения и поместит их в итоговый лист, представленные на рисунке 12.

Рисунок 154 – Диапазон В2:В11 в листе Консолидация содержит максимальные значения соответствующих ячеек в четырех исходных листах.

1. Сохраните файл рабочей книги.

Дата добавления: 2015-10-28