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

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

Фильтрация

Получившаяся таблица – двумерная, т.к. показывает данные в двух разрезах: ФИО сотрудников и время (годы). Можно добавить еще одно измерение, например, посмотреть продажу в разрезе получателей заказов.

Для того чтобы добавить поле Получатель в окно Список полей, вернитесь в окно Конструктора запросов (нажмите кнопку Вид).

В окне бланка конструктора запросов из таблицы Заказы добавьте поле НазваниеПолучателя и вернитесь в режим Сводная Таблица.

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

Появится окно со списком всех получателей, которым были отгружены товары. Слева в каждой строке стоит флажок.

Сбросьте флажок Все в первой строке, после чего будут сброшены все флажки. Установите флажок только у одного получателя, например Blondel pere et fils, и нажмите кнопку . Данные в сводной таблице изменятся, а под наименованием фильтра появилось имя выбранного получателя.

Рисунок 120 – Установка фильтра в сводной таблице

Окно со списком получателей позволяет сделать множественный выбор, то есть выбрать сразу несколько получателей. Так, чтобы добавить еще одного получателя, например Bon app', снова щелкните по стрелке справа от названия фильтра Получатели и в появившемся окне со списком всех получателей установите флажок у Bon app' и нажмите .

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

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

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

Другой способ фильтрации данных в таблице – по первому столбцу. Сначала удалите все установленные фильтры, нажав на панели инструментов кнопку Автофильтр. Затем выделите столбец Сотрудники, щелкнув на его заголовке. На панели инструментов нажмите кнопку Показать верхние и нижние элементы. Затем выберите в списке элемент Показать только верхний элемент и далее 5. В результате будут отобраны только пять сотрудников с наибольшим значением в столбце общие итоги. В заголовке столбца ФИО появился значок фильтра.

Рисунок 120 – Установка фильтра по максимальному объему продаж

АВС-анализ

Покажем список товаров, доля продаж которых составляет 80 % от общего объема. Сначала удалите все установленные фильтры, нажав на панели инструментов кнопку Автофильтр.

Для того чтобы добавить поле Марка в окно Список полей, вернитесь в окно Конструктора запросов (нажмите кнопку Вид).

В окне бланка конструктора запросов нажмите на панели инструментов кнопку и в появившемся диалогом окне Добавление таблицы выберите таблицу Товары и нажмите кнопку . Добавьте в бланк запроса из таблицы Товары поле Марка. Так как поле Цена присутствует в двух таблицах, следует указать, что значение поля Цена в вычисляемом поле будет браться из таблицы Заказано

Сумма: [Заказано]![Цена]*[Количество]

Теперь вернитесь в режим Сводная Таблица.

Удалите столбец Сотрудники: щелкните на его заголовке и нажмите клавишу Delete. Найдите в окне Список полей сводной таблицы поле Марка и перетащите его мышью в область заголовков строк – вертикальную область у левого края формы.

На панели инструментов на-жмите кнопку Показать верх-ние и нижние элементы. Затем выберите в списке элемент Пока-зать только верхний элемент и далее Другое...

Появится окно Свойства с раскрытой вкладкой Фильтр и группировка. Установите в поле со списком Отображать значение первые. В поле Элементы введите 80 и отметьте флажок %.

Вы увидите список из 37 товаров (из 77 наименований), отгрузка которых составляет 80,5 % от общего объема (11074406,20 / 13755956,30).

Рисунок 121 – АВС-анализ

Снимается фильтр той же кнопкой, но выбрать в списке нужно команду Показать все.

Детализация

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

Щелкните на маркере, например, 1996 г. Появятся три столбца, в которых будут отображаться данные за кварталы и один итоговый столбец за год.

Щелкните маркер развертывания в столбце Кв4, и таблица развернется по месяцам. Чтобы раскрыть данные сразу по всем кварталам, выделите заголовок Кварталы (он находится рядом с заголовком Годы). При этом окажутся выделенными заголовки сразу всех кварталов 1996 г. Теперь на панели инструментов нажмите кнопку Развернуть. Обратите внимание, что кроме данных по каждому месяцу выводятся также промежуточные итоги за квартал и за год.

Чтобы снова свернуть таблицу, щелкните на заголовке Годы. Будут выделены все заголовки, содержащие год. Нажмите кнопку Свернуть слева от кнопки Развернуть.

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

Снова сверните строку (сокройте детальные данные), щелкнув на кнопке в той же строке.

Аналогично можно развернуть любой столбец. Щелкните на значке в заголовке 1996 г. Будут показаны детальные данные этого столбца (все продажи за 1996 г.) для всех товаров.

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

Формы