Создание сводной таблицы в MS Excel ХР(2003)

10. Создать новый лист (пункт меню Вставка). Переименовать Лист4 в лист с названием "Форма заказов". В пункте меню Данные выбрать команду Сводная таблица. Откроется Мастер сводных таблиц.

10.1. На шаге 1 Мастераустановить переключатель Вид создаваемого отчета в положение Сводная таблица.

10.2. На шаге 2 Мастера указать диапазон, содержащий исходные данные. Для этого следует нажать кнопку Обзор, открыть лист Список заказов и выделить таблицу "Список фирм-заказчиков" (рис.1.11). В диапазоне не допускаются имена столбцов в объединенных ячейках.

Рисунок 1.11 – Шаг 2 Мастера сводных таблиц и диаграмм

10.3. На шаге 3 Мастера, выбрать место размещения таблицы на листе Форма заказа и нажать кнопку Макет (рис.1.12). Откроется диалоговое окно Мастер сводных таблиц и диаграмм-макет(рис.1.13).

Рисунок 1.12 – Шаг 3 Мастера сводных таблиц и диаграмм

Рисунок 1.13 – Макет сводной таблицы

10.4. В правой части диалогового окна макета имеется список названий полей (столбцов выбранной таблицы). Следует выполнить следующие операции (рис.1.13):

– в область с надписью "Строка" последовательно перетащить поля "Код фирмы", "Наименование фирмы", "Код товара", "Наименование товара", "Количество";

– в область с надписью "Страница" перетащить поля "Код заказа" и "Дата заказа";

– в область с надписью "Данные" перетащить поле "Сумма, руб", данные из этого поля будут автоматически суммироваться;

– область с надписью "Столбец" в данном примере не использовались.

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

10.5. Создав нужный макет, нажать кнопку OK, а затем кнопку Готово (рис.1.14).

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

10.6. Для фильтрации записей в сводной таблице следует раскрыть поле со списком и выбрать нужное значение (рис.1.16).

Рисунок 1.16 – Фильтрация сводной таблицы по Коду заказа

В сводной таблице каждое поле можно использовать для фильтрации данных. Для фильтрации данных с кодом заказа 22 следует раскрыть поле со списком в строке "Код заказа", выбрать значение 22 ® ОК. Для фильтрации данных по наименованию фирмы раскрыть список поля "Наименование фирмы", выбрать значение АО "Проект М" ® ОК.

10.7. Как правило, автоматически созданная сводная таблица содержит промежуточные итоги, которые загромождают таблицу. Например, итоги по полям "Код фирмы", "Код товара" и др. (рис.1.14).

Чтобы убрать строки с промежуточными итогами надо последовательно два раза щелкнуть по имени каждого поля(кроме поля Итого). В диалоговом окне Вычисление поля сводной таблицыкаждого поля следует отметить переключатель Итоги®Нет®ОК (рис. 1.15).

Рисунок 1.15 – Диалоговое окно Вычисление поля сводной таблицы

Диалоговое окно Вычисление поля сводной таблицы можно использовать для изменения имени поля. Так для переименования поля Дата, в поле с именем "Дата выполнения заказа" следует ввести новое имя в соответствующее поле диалогового окна.

10.8. Для завершения создания сводной таблицы по образцу (рис.1.2) выбрать на панели инструментов Сводная таблица кнопку Формат отчета. В открывшимся диалоговом окне Автоформат среди образцов оформления выбрать образец с подписью Нет. Выше таблицы добавить надпись ООО "Строймастер", а ниже таблицы надписи "Принял" и "Дата". В итоге получим сводную таблицу в виде, показанном на рисунке 1.17.

11. Для создания сводной таблицы "Итоговые суммы заказов" вставить в рабочую книгу новый лист. Переименовать Лист 5 в лист с названием Форма заказов. Выбрать команду Сводная таблица в пункте меню Данные и повторить операции 10.1-10.8. Для сводной таблице в диалоговом окне Мастер сводных таблиц и диаграмм – макет для области "Строка" выбрать поля "Код товара" и "Наименование фирмы", а для области "Данные" поле "Сумма, руб.". Убрать для выбранных полей промежуточные итоги. Для фильтрации выбрать из списка в поле "Наименование фирмы" запись ОАО "Привет". Общий итог рассчитывается автоматически. Результат выполнения показан на рисунке 1.18.

Рисунок 1.17 – Сводная таблица "Форма заказа" заказа №22 в виде бланка

Рисунок 1.18 – Сводная таблица "Итоговые суммы заказов"

При изменении данных в исходных таблицах, данные в сводных таблицах также изменяются.