Добавьте промежуточные итоги, определив Стоимость по определенному виду товара.
По горизонтали - по центру | По вертикали – по центру | Контрольный пример | Контрольный пример |
17. Сохраните рабочую книгу
Формулировка задания: MS Excel. Создание сводных и консолидированных таблиц
Цель работы: Научиться создавать сводные и консолидированные таблицы, которые являются мощным аналитическим инструментом, позволяющим преобразовывать информацию из полей списков Microsoft Excel в том виде, которые будет удобен пользователю.
Контрольный пример
- Запустите Microsoft Excel.
- Откройте книгу с контрольным примером.
- Добавьте в книгу новый рабочий лист и переименуйте его в лист с именем «Список».
- Скопируйте ранее созданную таблицу с листа «Сортировка» и отмените все условия сортировки, возвращая таблице первоначальный вид.
Создание сводной таблицы
-
Выделите любую ячейку в списке, на основе которого вы хотите создать сводную таблицу.
Рисунок Ошибка! Закладка не определена. 43 - Таблица исходных данных с листа «Список»
- Выберите в пункте меню Данные команду Сводная таблица.
- Microsoft Excel запустит Мастер сводных таблиц и диаграмм – шаг 1 из 3. На данном шаге построения сводной таблицы указывается тип источника данных и вид создаваемого отчета. Установите переключатели согласно приведенному ниже рисунку и нажмите кнопку Далее.
Рисунок 144 - Мастер сводных таблиц и диаграмм – шаг 1 из 3
-
Далее во втором окне Мастер сводных таблиц и диаграмм, вам требуется указать диапазон, в котором находятся исходные данные. Если вы правильно выполнили пункт 5 контрольного задания, выделили одну ячейку в списке, то мастер правильно заполнит это окно диалога, нажмите кнопку Далее.
Рисунок 145 - Мастер сводных таблиц и диаграмм – шаг 2 из 3
- В последнем окне диалога, укажите место, где вы хотите расположить сводную таблицу, в нашем примере это новый лист, смотрите ниже приведенный пример, после нажмите кнопку Готово.
Рисунок 146 - Мастер сводных таблиц и диаграмм – шаг 3 из 3
- После нажатия кнопки Готово Microsoft Excel отобразит в новом рабочем листе пустой макет таблицы и выведет на экран панель инструментов Сводные таблицы с кнопками для каждого поля из источника данных. Для создания сводной таблицы, перетащите кнопки полей с панели инструментов в область макета:
- - в поле страницы поля Код заказа и Код фирмы;
- - в поле столбцов поле Вид продукции;
- - в поле строк поле Наименование товара;
- - в область данных поле Цена заказа.
Рисунок 147 – Построение сводной таблицы
- После построения сводной таблицы с помощью мастера она должна иметь следующий вид:

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

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

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

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

Рисунок 152 – Окно диалога Консолидация
- В поле Ссылка введите или укажите с помощью мыши последовательно следующие исходные ссылки:
· · Январь!$A$2:$B$11
· · Февраль!$A$2:$B$11
· · Март!$A$2:$B$11
· · Апрель!$A$2:$B$11
Ввод в это поле производится в следующем порядке:
· · вводим данные за январь в поле Ссылка;
· · нажимаем кнопку Добавить. Данные будут перенесены с область Список данных;
· · повторить выполнение выше описанных действий для остальных данных.

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

Рисунок 154 – Диапазон В2:В11 в листе Консолидация содержит максимальные значения соответствующих ячеек в четырех исходных листах.
- Сохраните файл рабочей книги.
Дата добавления: 2015-10-28; просмотров: 50 | Нарушение авторских прав
mybiblioteka.su - 2015-2025 год. (0.007 сек.)