Сводная таблица в excel 2003 как сделать

Корпорация "Центр"

Иллюстрированный самоучитель по Microsoft Office 2003

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

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

Если обычные таблицы могут быть только двумерными, то сводные таблицы многомерны, что позволяет избежать дублирования данных. Создавая сводную таблицу, пользователь указывает, какие Поля и какие элементы должны быть представлены в ней. Например, если у вас есть списки товаров, которые продаются в различных магазинах, то названия товаров будут образовывать поля, а их конкретное количество в каждом магазине – элементы. Данные по магазинам, которые расположены в разных городах, можно расположить на отдельных листах. Сводная таблица поможет вам проанализировать суммарную продажу конкретных товаров по неделям, месяцам, кварталам, избавит от необходимости просматривать все имеющиеся списки. В сводную таблицу можно включать промежуточные и итоговые суммы, расчетные поля.

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

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

В качестве примера рассмотрим создание сводной таблицы, позволяющей на основе таблиц с исходными данными выполнить анализ продажи определенных товаров в различных городах России. В книге, приведенной на рис. 18.16, показана продажа нескольких моделей автомобилей: Волга, Жигули, Ока в разных городах России: в Москве, Саратове и Туле. Каждый город показан на отдельном листе. Предполагается, что сводные таблицы составляются по четырем месяцам: январь, февраль, март и апрель. Таблицы отформатированы с использованием команды Автоформат (AutoFormat) в меню Формат (Format). Выбран образец с подписью Простой (Simple).

Рис. 18.16. Исходный список для составления сводной таблицы

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

Положением переключателя в группе Создать таблицу на основе данных, находящихся: (Create Pivot table from data in:) установите переключатель в положение: в нескольких диапазонах консолидации (Multiple consolidation ranges), так как источники данных для создания сводной таблицы, расположены на разных листе Excel.

Назначение других положений переключателя:

  • в списке или базе данных Microsoft Office Excel (Microsoft Office Excel list or Database) – позволяет создать отчет сводной таблицы или сводной диаграммы по данным строк или столбцов с подписями, расположенных на одном листе Excel;
  • во внешнем источнике данных (External data source) – позволяет создать отчет сводной таблицы или сводной диаграммы по данным внешнего файла или базы данных, например, Microsoft Access, SQL Server, Paradox;.
  • в нескольких диапазонах консолидации – позволяет создать отчет сводной таблицы или сводной диаграммы по данным строк или столбцов с подписями, расположенных в нескольких диапазонах консолидации;
  • в другой сводной таблице или сводной диаграмме (Another PivotTable report or Pivot Chart report) – используется для создания отчета сводной таблицы или сводной диаграммы по данным другого отчета сводной таблицы в активной книге.

Создание сводной таблицы в 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 – Сводная таблица "Итоговые суммы заказов"

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

Корпорация "Центр"

Ссылка на основную публикацию