Тема 3. Анализ данных с помощью мастера сводных таблиц
Задание 25. Создание сводной таблицы
Наиболее подходящим средством обработки данных в Excel является команда «Сводная таблица». В результате ее выполнения создается настраиваемая таблица для организации полей на листе в новых сочетаниях. Методика сводных таблиц позволяет превратить строки в столбцы и наоборот.
При создании сводной таблицы с помощью Мастера вы можете задать нужные поля, организацию таблицы (ее макет) и тип выполняемых вычислений. После построения таблицы можно изменить ее расположение. Именно возможность изменения ориентации таблицы, например транспортирование заголовков столбцов в заголовки строк и наоборот, дала сводной таблице ее название. В оригинале сводная таблица называется Pivot Table (Pivot — вращаться).
Сводная таблица дает возможность получать оперативные справки, например: по торговле с одним клиентом или одним товаром, по торговле с одним клиентом одним товаром, в определенном временном периоде, выполнять математические операции.
Сводная таблица создается с помощью Мастера сводных таблиц, который следит за вводом информации, помогает производить необходимые вычисления и форматировать таблицу.
Проделайте следующее:
перейдите в четвертый рабочий лист и присвойте ему имя «Анализ»;
вернитесь в рабочий лист с анализируемой таблицей. В меню «Данные» выберите команду «Сводная таблица».
Откроется диалоговое окно Мастера сводных таблиц, состоящее их 4 шагов.
В первом диалоговом окне выберите в списке или базе данных Microsoft Excel и нажмите кнопку «Далее».
Во втором окне укажите диапазон ячеек, которые будут использованы в сводной таблице. Для этого поместите курсор в поле «Диапазон» и выделите блок ячеек А2 — L18. Нажмите кнопку «Далее».
Третье окно — самое важное. В нем определяется структура сводной таблицы (рис. 3).
В центре окна «Область-сведения», которая разделена на поля: «Строка», «Столбец», «Данные», «Страница».
Справа от «Области-сведения» отображаются все имена полей, используемые в списке заказов.
Продумайте структуру своей сводной таблицы, определите, какую информацию нужно проанализировать.
Например, проследите динамику сбыта отдельных товаров по месяцам.
Для этого:
в область «Страница» перетащите поле «Месяц»;
в область «Строка» перетащите поле «Наименование товара»;
в область «Данные» поместите поле, по которому будет подсчитана сумма. Перетащите в эту область поле «Получено». В области данных появится кнопка «Сумма» по полю «Получено». То есть предлагается операция суммирования.
Если вы хотите задать выполнение другой операции, выполните двойной щелчок по полю «Сумма» по полю «Получено» в области «Данные» и в диалоговом окне «Вычисление» поля сводной таблицы можно увидеть перечень операций и выбрать нужную.
Рис. 3. Диалоговое окно 3-го шага Мастера сводных таблиц
Нажмите кнопку «Далее».
В четвертом (последнем) окне диалога Мастера сводных таблиц подтвердите предложенные параметры или задайте свои.
Нажмите на клавишу «Готово».
Перед вами итоговые данные по продаже товаров за все месяцы.
По полю, помещенному в область «Страницы», выполняется фильтрация. Так как в область «Страницы» было помещено поле «Месяц», это вам позволит фильтровать данные для конкретного месяца.
Чтобы просмотреть данные за февраль, раскройте список рядом с заголовком «Месяц». В раскрывшемся списке выберите февраль.
Просмотрите данные за март.
Покажите итоговые данные, выбрав в списке «Все».
Задание 26. Редактирование сводной таблицы
Вместе со сводной таблицей на экране появляется и панель инструментов «Запрос» и «Сводная таблица», позволяющая провести операции редактирования. Первая кнопка «Мастер сводных таблиц» позволяет быстро перенестись в третье диалоговое окно Мастера сводных таблиц (если панели инструментов нет, в меню «Вид» выберите команду «Панель инструментов», установите флажок «Запрос» и «Сводная таблица»).
Сводная таблица позволяет экспериментировать.
Внимание! Курсор всегда должен находиться в области таблицы!
Задача ставится в зависимости от того, какой документ необходимо вывести на печать.
Для закрепления теоретической информации, выполните следующие задачи:
1. Покажите итоговые данные по каждому магазину за каждый месяц.
Для этого щелкните по первой кнопке панели инструментов «Запрос» и «Сводная таблица». В области третьего окна Мастера сводных таблиц перенесите поля:
в область «Столбец» — «Месяц»;
в область «Страница» — «Клиент»;
в область «Данные» — «Получено».
Так как в область «Страница» помещено поле «Клиент», фильтрация происходит по полю «Клиент». Щелкните по кнопке раскрывающегося списка «Клиент» и увидите список клиентов. В таблице показываются итоговые данные для выбранного клиента.
Условие задачи такое же как в предыдущей. Но поместите поле «Клиент» не в область «Страница», а в область «Строка». Просмотрите сводную таблицу и определите разницу при перемещении поля в области «Страница» и «Строка».
Определите, сколько холодильников купил магазин № 8. Для этого в третьем окне Мастера сводных таблиц в области «Данные» сделайте двойной щелчок по полю «Сумма» и по полю «Уплачено».
Откроется диалоговое окно «Вычисление для сводной таблицы». В списочном поле выберите «Количество значений».
В раскрывающихся списках готовой таблицы выберите «Клиент» и «Товар».
4. Сколько мебельных гарнитуров для спальни куплено магазином № 48 и когда?
Отредактируйте сводную таблицу, добавив в область «Страница» поле «Наименование товара». В готовой таблице — два поля, по которым происходит фильтрация. Чтобы просмотреть содержимое раскрывающихся списков, увеличьте колонку В. В область столбца поместите «Дату». В раскрывающихся списках выберите «Магазин № 48» и «Мебельный гарнитур для спальни». Вы увидите, что мебельный гарнитур для спальни магазину № 48 продавался дважды.
5. Нужна информация по каждому товару: кто покупал, сколько, по какой цене, на какую сумму. Чтобы получить данную информацию, переместите:
в область «Строка» — поля: «Наименование», «Клиент», «Количество», «Цена»;
в область «Данные» — поле «Сумма» по полю «Уплачено».
6. Нужна информация по каждому клиенту: что он купил, по какой цене, на какую сумму.
Чтобы получить данную информацию, переместите поля:
в область «Строка» — «Наименование», «Количество», «Цена»;
в область «Данные» — «Сумма» по полю «Уплачено»;
в область «Страница» — «Клиент».
В раскрывающемся списке «Клиент» рассматриваются данные на каждого клиента.
7. Постройте несколько сводных таблиц самостоятельно.