Сводные таблицы Excel - Краткое руководство

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

Основные функции сводной таблицы следующие:

• Создать сводную таблицу очень просто и быстро

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

• Получение подходящего представления для ваших данных по мере их понимания.

• Возможность создавать отчеты на лету.

• Создание нескольких отчетов из одной сводной таблицы за считанные секунды.

• Предоставление интерактивных отчетов для синхронизации с аудиторией.

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

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

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

Вы узнаете, как создать сводную таблицу из диапазона данных или таблицы Excel, в главе - Создание сводной таблицы из таблицы или диапазона.

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

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

Макет сводной таблицы - поля и области

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

Вы узнаете о полях и областях сводной таблицы в главах - Поля сводной таблицы и области сводной таблицы.

Изучение данных с помощью сводной таблицы

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

У вас будет обзор этих параметров в главе - Исследование данных с помощью сводной таблицы.

Подведение итогов

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

Вы узнаете, как применять типы вычислений к сводной таблице в главе «Подведение итогов по различным типам вычислений».

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

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

Вы узнаете о различных способах обновления данных в главе «Обновление сводной таблицы».

Отчеты сводной таблицы

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

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

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

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

Если вы не уверены, какой макет сводной таблицы лучше всего подходит для ваших данных, вы можете использовать команду Excel «Рекомендуемые сводные таблицы», чтобы просмотреть сводные таблицы, настроенные для ваших данных, и выбрать тот, который вам нравится.

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

Рассмотрим следующий диапазон данных, который содержит данные о продажах для каждого продавца в каждом регионе и в январе, феврале и марте:

Чтобы создать сводную таблицу из этого диапазона данных, выполните следующие действия:

• Убедитесь, что первая строка имеет заголовки. Вам нужны заголовки, потому что они будут именами полей в вашей сводной таблице.

• Назовите диапазон данных как SalesData_Range.

• Щелкните диапазон данных - SalesData_Range.

• Щелкните вкладку ВСТАВИТЬ на ленте.

Щелкните Сводная таблица в группе Таблицы. ВCreate PivotTable появится диалоговое окно.

В диалоговом окне Создание сводной таблицы в разделе Choose the data that you want to analyze, вы можете выбрать таблицу или диапазон из текущей книги или использовать внешний источник данных.

Когда вы создаете сводную таблицу из диапазона данных, выберите в диалоговом окне следующее:

• Выбрать Select a table or range.

• В поле Таблица / диапазон введите имя диапазона - SalesData_Range.

• Выберите «Новый лист» в разделе «Выберите, где вы хотите разместить отчет сводной таблицы» и нажмите «ОК».

Вы можете выбрать анализ нескольких таблиц, добавив этот диапазон данных в модель данных. Вы можете узнать, как анализировать несколько таблиц, использовать модель данных и как использовать внешний источник данных для создания сводной таблицы, в учебнике Excel PowerPivot.

В вашу книгу будет вставлен новый лист. Новый рабочий лист содержит пустую сводную таблицу. Назовите рабочий лист Range-PivotTable.

Как видите, PivotTable FieldsСписок отображается в правой части листа, содержащий имена заголовков столбцов в диапазоне данных. Далее на ленте появляются Инструменты сводной таблицы - АНАЛИЗ и ДИЗАЙН.

Добавление полей в сводную таблицу

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

Предположим, вы хотите просуммировать сумму заказа по продажам за январь, февраль и март. Вы можете сделать это за несколько простых шагов, а именно:

• Щелкните поле «Продавец» в списке полей сводной таблицы и перетащите его в область СТРОК.

• Щелкните поле «Месяц» в списке полей сводной таблицы и перетащите его в область ROWS.

• Щелкните Сумму заказа и перетащите ее в область area ЗНАЧЕНИЯ.

Ваша первая сводная таблица готова, как показано ниже.

Обратите внимание, что в сводной таблице появляются два столбца, один из которых содержит выбранные вами ярлыки строк, т. Е. «Продавец» и «Месяц», а второй - «Сумма суммы заказа». В дополнение к сумме суммы заказа по месяцам для каждого продавца вы также получите промежуточные итоги, представляющие общие продажи этого человека. Если вы прокрутите лист вниз, вы найдете последнюю строку как «Общая сумма», представляющая общий объем продаж.

Вы узнаете больше о создании сводных таблиц в соответствии с потребностями по мере прохождения этого руководства.

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

Рассмотрим следующую таблицу Excel, которая содержит те же данные о продажах, что и в предыдущем разделе:

Таблица Excel по своей сути будет иметь имя, а столбцы будут иметь заголовки, что является требованием для создания сводной таблицы. Предположим, что имя таблицы - SalesData_Table.

Чтобы создать сводную таблицу из этой таблицы Excel, выполните следующие действия:

• Щелкните по таблице - SalesData_Table.

• Щелкните вкладку ВСТАВИТЬ на ленте.

• Щелкните Сводная таблица в группе Таблицы. ВCreate PivotTable появится диалоговое окно.

• Щелкните Выбрать таблицу или диапазон.

• В поле Таблица / диапазон введите имя таблицы - SalesData_Table.

• Выберите Новый лист под Choose where you want the PivotTable report to be placed. Щелкните ОК.

В вашу книгу будет вставлен новый лист. Новый рабочий лист содержит пустую сводную таблицу. Назовите рабочий лист - Таблица-Сводная таблица. Рабочий лист Table-PivotTable похож на тот, который вы получили в случае диапазона данных в предыдущем разделе.

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

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

Если вы не знакомы со сводными таблицами Excel или не знаете, какие поля приведут к созданию содержательного отчета, вы можете использовать команду «Рекомендуемые сводные таблицы» в Excel. Рекомендуемые сводные таблицы предоставляют вам все возможные отчеты с вашими данными вместе с соответствующим макетом. Другими словами, отображаемые параметры будут сводными таблицами, настроенными для ваших данных.

Чтобы создать сводную таблицу из таблицы Excel SalesData-Table с использованием рекомендованных сводных таблиц, выполните следующие действия:

• Щелкните таблицу SalesData-Table.

• Щелкните вкладку ВСТАВИТЬ.

• Щелкните Рекомендуемые сводные таблицы в группе Таблицы. Откроется диалоговое окно "Рекомендуемые сводные таблицы".

В диалоговом окне «Рекомендуемые сводные таблицы» будут отображаться возможные настраиваемые сводные таблицы, соответствующие вашим данным.

• Щелкните каждый из параметров сводной таблицы, чтобы просмотреть предварительный просмотр справа.

• Щелкните сводную таблицу - Сумма суммы заказа по продавцу и месяцу и нажмите ОК.

Вы получите предварительный просмотр на правой стороне.

Выбранная сводная таблица появится на новом листе в вашей книге.

Вы можете видеть, что выбраны поля сводной таблицы - продавец, регион, сумма заказа и месяц. Из них регион и продавец находятся в области СТРОК, Месяц - в области СТОЛБЦЫ, а сумма суммы заказа - в области ЗНАЧЕНИЯ.

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

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

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

Внизу панели задач вы найдете опцию - «Отложить обновление макета» с кнопкой «ОБНОВИТЬ» рядом с ней.

• По умолчанию он не выбран, и любые изменения, которые вы вносите в выбор полей или в параметры макета, мгновенно отражаются в сводной таблице.

• Если вы выберете это, изменения в вашем выборе не будут обновлены, пока вы не нажмете на UPDATE кнопка.

В этой главе вы узнаете подробности о полях. В следующей главе вы узнаете подробности об областях.

Панель задач полей сводной таблицы

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

• Щелкните вкладку АНАЛИЗ в разделе ИНСТРУМЕНТЫ Сводной таблицы на ленте.
• Убедитесь, что список полей выбран (т. Е. Выделен) в группе Показать.
• Если список полей не выбран, щелкните его.

Панель задач «Поля сводной таблицы» будет отображаться в правой части окна с заголовком «Поля сводной таблицы».

Перемещение области задач полей сводной таблицы

Справа от заголовка Поля сводной таблицы на панели задач сводной таблицы вы найдете кнопку

. Это представляет собой параметры панели задач. Щелкните кнопку . Параметры панели задач - «Переместить», «Размер» и «Закрыть» отображаются в раскрывающемся списке.

Вы можете переместить панель задач сводных таблиц в любое место окна следующим образом:

• Щелкните Переместить в раскрывающемся списке.

  
  На панели задач появляется кнопка.

• Щелкните

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

Вы можете разместить панель задач в левой части окна, как показано ниже.

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

Вы можете изменить размер панели задач сводных таблиц, то есть увеличить / уменьшить длину и / или ширину панели задач следующим образом:

• Щелкните Параметры панели задач -

  
  справа от заголовка - Поля сводной таблицы.

• Щелкните Размер в раскрывающемся списке.

• Используйте символ ⇔ для увеличения / уменьшения ширины панели задач.

• Используйте символ ⇕ для увеличения / уменьшения ширины панели задач.

В области ∑ ЗНАЧЕНИЯ, чтобы сделать сумму суммы заказа полностью видимой, вы можете изменить размер панели задач, как показано ниже.

Поля сводной таблицы

Список полей сводной таблицы включает все таблицы, связанные с вашей книгой, и соответствующие поля. Именно выбрав поля в списке полей сводной таблицы, вы создадите сводную таблицу.

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

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

Над списком полей вы найдете действие Выбрать поля для добавления в отчет. Справа вы найдете кнопку,

которая представляет Инструменты.

• Щелкните на кнопке Tools.

В раскрывающемся списке вы найдете следующее -

• Пять различных вариантов макета для полей и областей.

• Два варианта сортировки полей в списке полей -

  • Отсортируйте от А до Я.

  • Сортировать в порядке источников данных.

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

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

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

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

Доступны четыре области сводной таблицы -

• ROWS.
• COLUMNS.
• FILTERS.
• ∑ ЦЕННОСТИ (читать как обобщающие значения).

Сообщение - Drag fields between areas below появляется над областями.

С помощью областей сводной таблицы вы можете выбрать -

• Какие поля отображать в виде строк (область ROWS).
• Какие поля отображать в виде столбцов (область COLUMNS).
• Как суммировать ваши данные (∑ область ЦЕННОСТИ).
• Фильтры для любого из полей (область ФИЛЬТРЫ).

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

РЯДЫ

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

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

Например, рассмотрим таблицу данных о продажах.

• Перетащите поле Продавец в область ROWS.
• Перетащите поле Месяц в область СТРОК.

Ваша сводная таблица отображается с одним столбцом, содержащим ярлыки строк - продавец и месяц, а в последней строке - общий итог, как показано ниже.

КОЛОННЫ

Вы можете перетаскивать поля в область КОЛОННЫ.

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

Перетащите поле «Регион» в область «КОЛОННЫ». Ваша сводная таблица отображается с первым столбцом, содержащим метки строк - продавец и месяц, следующие четыре столбца содержат метки столбцов - регион и последний столбец общей суммы, как указано ниже.

• Перетащите поле Месяц из СТРОК в КОЛОННЫ.

• Перетащите поле «Регион» из КОЛОННЫ в СТРОКИ. Макет сводной таблицы изменится, как показано ниже.

Вы можете видеть, что сейчас есть только пять столбцов - первый столбец с метками строк, три столбца с метками столбцов и последний столбец с общим итогом.

Количество строк и столбцов зависит от количества значений в этих полях.

∑ ЦЕННОСТИ

Основное использование сводной таблицы - суммирование значений. Следовательно, поместив поля, по которым вы хотите суммировать данные, в∑ VALUES области, вы попадете в сводную таблицу.

• Перетащите поле Сумма заказа на ∑ VALUES.

• Перетащите поле «Регион» над полем «Продавец» в области ROWS. Этот шаг предназначен для изменения порядка размещения. Вы узнаете о вложении в главе - Вложение в сводной таблице этого учебного пособия.

Как видите, данные суммируются по регионам, продавцам и месяцам. У вас есть промежуточные итоги по каждому региону по месяцам. У вас также есть общие итоги по месяцам в области общих итогов в строке Grand Total в столбце Grand Total.

ФИЛЬТРЫ

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

Перетащите поле Region из области ROWS в область FILTERS. Область фильтра будет размещена над сводной таблицей. Если у вас нет пустых строк над сводной таблицей, сводная таблица сдвигается вниз, вставляя строки над сводной таблицей для фильтра.

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

• Щелкните стрелку справа от фильтра.
• Установите флажок - Выбрать несколько элементов.

Флажки появятся для всех параметров в раскрывающемся списке. По умолчанию все флажки отмечены.

• Установите флажки - Север и Юг.
• Снимите остальные поля. Щелкните ОК.

Сводная таблица изменяется для отражения отфильтрованных данных.

Вы можете заметить, что фильтр отображает (Несколько элементов). Поэтому, когда кто-то смотрит на сводную таблицу, не сразу видно, какие значения фильтруются.

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

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

С помощью сводной таблицы вы можете сделать следующее:

• Отсортируйте данные.
• Отфильтруйте данные.
• Вложите поля сводной таблицы.
• Разверните и сверните поля.
• Сгруппировать и разгруппировать значения полей.

Сортировка и фильтрация данных

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

Вы можете фильтровать данные в сводной таблице, чтобы сосредоточиться на некоторых конкретных данных. В сводной таблице есть несколько параметров фильтрации, которые вы узнаете в главе «Фильтрация данных в сводной таблице» этого руководства. Вы можете использовать слайсеры для фильтрации, о чем вы узнаете в главе - Фильтрация с использованием слайсеров в этом руководстве.

Вложенные, раскрывающиеся и сворачивающиеся поля

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

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

Группировка и разгруппировка значений полей

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

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

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

Сортировка по полям

Вы можете отсортировать данные в приведенной выше сводной таблице по полям, которые находятся в строках или столбцах - регион, продавец и месяц.

Чтобы отсортировать сводную таблицу с полем Продавец, выполните следующие действия:

• Щелкните стрелку

  
  в метках строк.

• В раскрывающемся списке выберите «Продавец» в поле «Выбрать поле».

Отображаются следующие параметры сортировки -

• Отсортируйте от А до Я.
• Отсортируйте от Я до А.
• Дополнительные параметры сортировки.

Кроме того, поле «Продавец» по умолчанию отсортировано в порядке возрастания. НажмитеSort Z to A. Поле «Продавец» будет отсортировано по убыванию.

Таким же образом можно отсортировать поле в столбце - Месяц, щелкнув стрелку

в метках столбцов.

Сортировка по промежуточным итогам

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

Вы можете видеть, что стрелки

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

• Щелкните правой кнопкой мыши промежуточный итог любого из продавцов в столбце «Общая сумма».

• Выбрать Sort из раскрывающегося списка.

• Появится еще один раскрывающийся список с параметрами сортировки - Сортировать от наименьшего к наибольшему, Сортировка от наибольшего к наименьшему и Дополнительные параметры сортировки. Выберите Сортировать от большего к меньшему.

Промежуточные итоги в столбце «Общий итог» отсортированы от самого высокого до самого низкого значений в каждом регионе.

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

• Щелкните правой кнопкой мыши промежуточный итог любого из регионов в столбце «Общий итог».

• Щелкните Сортировать в раскрывающемся списке.

• Во втором раскрывающемся списке щелкните Сортировать от максимального к наименьшему. Сводная таблица будет отсортирована по промежуточным итогам по регионам.

Как вы можете заметить, у Юга самая высокая сумма заказа, а у Севера самая низкая.

Вы также можете отсортировать сводную таблицу по общей сумме за месяц следующим образом:

• Щелкните правой кнопкой мыши любой промежуточный итог в строке общего итога.
• В раскрывающемся списке выберите Сортировать.
• Во втором раскрывающемся списке выберите Сортировать от максимального к наименьшему.

Сводная таблица будет отсортирована по общей сумме за месяц.

Вы можете заметить, что в феврале самая высокая сумма заказа, а в марте самая низкая.

Дополнительные параметры сортировки

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

• Щелкните стрелку

  
  в ярлыках строк.

• В раскрывающемся списке выберите Дополнительные параметры сортировки. ВSort (Region) появится диалоговое окно.

Как вы можете заметить, в разделе «Сводка» текущий порядок сортировки указан как область сортировки в порядке возрастания. По возрастанию (от А до Я) выбрано в разделе Параметры сортировки. В поле нижеRegion отображается.

• Щелкните поле, содержащее Регион.
• Щелкните Сумма суммы заказа.

Щелкните значок More Optionsкнопка. ВMore Sort Options (Region) появится диалоговое окно.

Как видите, в разделе «Сортировать по» выбрано «Общая сумма». В разделе Сводка текущий порядок сортировки отображается какSort Region by Sum of Order Amount в порядке возрастания.

• Нажмите Values in selected column: в разделе Сортировать по.

• В поле ниже введите B5.

Как вы можете заметить, в разделе «Сводка» текущий порядок сортировки выглядит следующим образом:

• Сортировать регион по Sum of Order Amountв порядке возрастания с использованием значений в этом столбце: Январь. Щелкните ОК.

• Откроется диалоговое окно «Сортировка (регион)». Выберите По убыванию (от Я до А) по: в разделе Параметры сортировки.

В разделе «Сводка» текущий порядок сортировки выглядит следующим образом:

Сортировка региона по сумме суммы заказа в порядке убывания, используя значения в этом столбце: январь. Щелкните ОК. Сводная таблица будет отсортирована по регионам с использованием значений за январь.

Как вы можете заметить, в январе наибольшее количество заказов имеет Запад, а наименьшее - Север.

Сортировка данных вручную

В сводной таблице данные автоматически сортируются по выбранному вами параметру сортировки. Это называется автосортировкой.

Поместите курсор на стрелку

в ярлыках строк или столбцов.

Отобразится автосортировка, показывающая текущий порядок сортировки для каждого из полей сводной таблицы. Теперь предположим, что вы хотите отсортировать поле «Регион» в порядке - Восток, Запад, Север и Юг. Вы можете сделать это вручную следующим образом -

• Щелкните стрелку

  
  в ярлыках строк.

• Выберите регион в поле «Выбрать поле» из раскрывающегося списка.

• Нажмите More Sort Options. Откроется диалоговое окно «Сортировка (регион)».

• Выберите «Вручную» (вы можете перетаскивать элементы, чтобы переставить их).

• Щелкните ОК.

В разделе «Сводка» текущий порядок сортировки задается перетаскиванием элементов в поле «Регион» для отображения их в любом порядке.

Нажмите на восток и перетащите его наверх. Во время перетаскивания на восток через всю строку перемещается горизонтальная зеленая полоса.

Повторите перетаскивание с другими элементами поля «Регион», пока не получите требуемое расположение.

Вы можете наблюдать следующее -

• Элементы вложенного поля - Продавец также перемещаются вместе с соответствующим элементом поля «Регион». Кроме того, значения в других столбцах также изменились.

• Если вы поместите курсор на стрелку

  
  в ярлыках строк или столбцов, появится автосортировка, показывающая текущий порядок сортировки только полей «Продавец» и «Месяц». Поскольку вы отсортировали поле «Регион» вручную, оно не будет отображаться в автосортировке.

Note- Вы не можете использовать это ручное перетаскивание элементов поля, которое находится в области ЗНАЧЕНИЯ списка полей сводной таблицы. Таким образом, вы не можете перетащить значения суммы заказа в эту сводную таблицу.

Настройка параметров сортировки

В предыдущем разделе вы узнали, как установить параметр сортировки для поля вручную. У вас есть еще несколько параметров сортировки, которые вы можете установить следующим образом:

• Щелкните стрелку

  
  в ярлыках строк.

• Выберите регион в поле выбора поля.

• Щелкните Дополнительные параметры сортировки. Откроется диалоговое окно «Сортировка (регион)».

• Щелкните кнопку Дополнительные параметры.

Появится диалоговое окно «Дополнительные параметры сортировки (регион)». В этом диалоговом окне вы можете установить дополнительные параметры сортировки.

В разделе «Автосортировка» вы можете установить или снять флажок «Сортировка автоматически при каждом обновлении отчета», чтобы разрешить или остановить автоматическую сортировку при обновлении данных сводной таблицы.

• Снимаем галочку - Sort automatically каждый раз, когда отчет обновляется.

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

• Установите флажок под первым ключевым порядком сортировки.

Как видите, настраиваемые списки дня недели и месяца года представлены в раскрывающемся списке. Вы можете использовать любой из них, или вы можете использовать свой собственный список, такой как High, Medium, Low, или список размеров S, M, L, XL, которые не расположены в алфавитном порядке.

Вы можете создавать собственные списки на вкладке ФАЙЛ на ленте. ФАЙЛ → Параметры. В диалоговом окне «Параметры Excel» нажмите «Дополнительно» и перейдите к «Общие». Вы найдете кнопку «Редактировать настраиваемые списки» рядом с полем «Создать списки» для использования в последовательностях сортировки и заполнения.

Обратите внимание, что заказной порядок сортировки списка не сохраняется при обновлении (обновлении) данных в сводной таблице.

В разделе Сортировать по вы можете нажать Grand Total or Valuesв выбранных столбцах для сортировки по этим значениям. Этот параметр недоступен, если вы устанавливаете сортировку вручную.

Что следует учитывать при сортировке сводных таблиц

Когда вы сортируете данные в сводной таблице, помните следующее:

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

• Вы не можете сортировать текстовые записи с учетом регистра.

• Вы не можете сортировать данные по определенному формату, например по цвету ячеек или шрифта.

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

Возможно, вам придется провести углубленный анализ подмножества данных сводной таблицы. Это может быть связано с тем, что у вас большие данные, и вы должны сосредоточиться на меньшей части данных, или, независимо от размера данных, вам необходимо сосредоточиться на определенных конкретных данных. Вы можете фильтровать данные в сводной таблице на основе подмножества значений одного или нескольких полей. Есть несколько способов сделать это:

• Фильтрация с помощью слайсеров.
• Фильтрация с использованием фильтров отчетов.
• Фильтрация данных вручную.
• Фильтрация с использованием фильтров меток.
• Фильтрация с использованием фильтров значений.
• Фильтрация с использованием фильтров даты.
• Фильтрация с использованием Top 10 Filter.
• Фильтрация с использованием шкалы времени.

Вы научитесь фильтровать данные с помощью слайсеров в следующей главе. Вы поймете фильтрацию с помощью других методов, упомянутых выше в этой главе.

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

Фильтры отчетов

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

Перетащите область из строк в фильтры в областях сводной таблицы.

Фильтр с меткой «Регион» отображается над сводной таблицей (если у вас нет пустых строк над сводной таблицей, сводная таблица сдвигается вниз, чтобы освободить место для фильтра.

Вы заметите, что

• Значения продавца отображаются в строках.

• Значения месяца отображаются в столбцах.

• Вверху отображается фильтр региона, по умолчанию выбранный как ВСЕ.

• Суммарное значение - сумма суммы заказа.

  • Сумма суммы заказа по продавцу отображается в столбце Общая сумма.

  • Сумма суммы заказа по месяцам отображается в строке Общая сумма.

• Щелкните стрелку в поле справа от области фильтра.

Появится раскрывающийся список со значениями поля Регион. Установите флажокSelect Multiple Items.

По умолчанию все флажки отмечены. Снимите флажок (All). Все флажки будут сняты.

Затем установите флажки - Юг и Запад и нажмите ОК.

Будут обобщены данные только по Южному и Западному регионам.

В ячейке рядом с областью фильтра отображается - (Несколько элементов), указывающее, что вы выбрали более одного элемента. Однако сколько элементов и / или какие элементы не известно из отображаемого отчета. В таком случае использование слайсеров является лучшим вариантом для фильтрации.

Ручная фильтрация

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

в ячейке «Ярлыки строк» ​​или «Ярлыки столбцов».

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

Щелкните стрелку

в ячейке "Ярлыки столбцов".

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

• Снимите флажок (Выбрать все) в верхней части списка значений.

• Установите флажки рядом со значениями, которые вы хотите отображать в сводной таблице, в данном случае за февраль, и нажмите OK.

В сводной таблице отображаются только те значения, которые связаны с выбранным значением поля «Месяц» - февраль. Вы можете заметить, что стрелка фильтрации изменится на значок,

показывая, что фильтр применен. Поместите курсор на значок.

Вы можете заметить, что отображается, указывая, что ручной фильтр применен к полю «Месяц».

Если вы хотите изменить значение выбора фильтра, сделайте следующее:

• Щелкните

  
  значок.

• Установите / снимите флажки напротив значений.

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

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

• Щелкните стрелку

  
  в ячейке "Ярлыки строк".

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

• В раскрывающемся списке выберите «Продавец». Будет отображен список значений поля - Продавец.

• Снимите отметку (Выбрать все) и отметьте Уолтерс, Крис.

• Щелкните ОК.

В сводной таблице отображаются только те значения, которые связаны с выбранным значением поля «Месяц» - февраль и значением поля «Продавец» - Уолтерс, Крис.

Стрелка фильтрации для меток строк также изменится на значок,

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

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

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

Фильтрация по тексту

Если у вас есть поля, содержащие текст, вы можете отфильтровать сводную таблицу по тексту, если соответствующая метка поля является текстовой. Например, рассмотрим следующие данные сотрудников.

Данные содержат подробную информацию о сотрудниках - EmployeeID, Title, BirthDate, MaritalStatus, Gender и HireDate. Кроме того, данные также содержат уровень руководителя сотрудника (уровни 0–4).

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

Возможно, вы захотите узнать, скольким сотрудникам со словом «Менеджер» в названии подчиняются подчиненные. Поскольку заголовок ярлыка является текстовым, вы можете применить фильтр ярлыка к полю заголовка следующим образом:

• Щелкните стрелку

  
  в ячейке "Ярлыки строк".

• Выберите заголовок в поле «Выбрать поле» из раскрывающегося списка.

• Щелкните Фильтры меток.

• Щелкните Содержит во втором раскрывающемся списке.

Откроется диалоговое окно «Фильтр меток (заголовок)». Type Manager в поле рядом с Contains. Щелкните ОК.

Сводная таблица будет отфильтрована по значениям заголовка, содержащим «Менеджер».

• Щелкните

  
  значок.

Вы можете видеть, что

отображается следующее:

• Фильтр меток применяется к полю - Заголовок и
• Что такое примененный фильтр меток.

Фильтрация по значениям

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

• Щелкните стрелку

  
  в ячейке "Ярлыки строк".

• Выбрать Title в поле Выбрать поле из раскрывающегося списка.

• Щелкните Фильтры значений.

• Выберите «Больше или равно» во втором раскрывающемся списке.

Откроется диалоговое окно «Фильтр значений (заголовок)». Введите 25 в поле справа.

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

Фильтрация по датам

Возможно, вы захотите отобразить данные всех сотрудников, нанятых в 2015-15 финансовом году. Вы можете использовать фильтры данных для того же:

• Включите поле HireDate в сводную таблицу. Теперь вам не нужны данные менеджера, поэтому удалите поле ManagerLevel из сводной таблицы.

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

• Щелкните стрелку

  
  в ячейке «Ярлыки строк».

• Выберите HireDate в поле «Выбрать поле» из раскрывающегося списка.

• Щелкните Фильтры даты.

• Seelct Between из второго раскрывающегося списка.

Откроется диалоговое окно Фильтр даты (HireDate). Введите 01.04.2014 и 31.03.2015 в двух полях Дата. Щелкните ОК.

Сводная таблица будет фильтроваться для отображения только данных с HireDate от 1 - ^го апреля 2014 года и 31 - ^го марта 2015 года.

Вы можете сгруппировать даты по кварталам следующим образом -

• Щелкните правой кнопкой мыши любую дату. ВGrouping появится диалоговое окно.

• Введите 01.04.2014 в поле Начиная с. Установите флажок.

• Введите 31.03.2015 в поле Окончание в. Установите флажок.

• Щелкните Кварталы в поле под By.

Даты будут сгруппированы по кварталам в сводной таблице. Вы можете сделать таблицу более компактной, перетащив поле HireDate из области ROWS в область COLUMNS.

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

Фильтрация с использованием фильтра Top 10

Вы можете использовать фильтр Top 10 для отображения нескольких верхних или нескольких нижних значений поля в сводной таблице.

• Щелкните стрелку

  
  в ячейке «Ярлыки строк».

• Щелкните Фильтры значений.

• Щелкните Top 10 во втором раскрывающемся списке.

Появится диалоговое окно Top 10 Filter (Title).

• В первом поле нажмите «Сверху» (вы также можете выбрать «Снизу»).

• Во втором поле введите число, скажем, 7.

• В третьем поле у ​​вас есть три варианта фильтрации.

  • Нажмите «Элементы» для фильтрации по количеству элементов.

  • Нажмите «Процент», чтобы отфильтровать по проценту.

  • Щелкните Сумма, чтобы отфильтровать по сумме.

• Поскольку у вас есть количество EmployeeID, щелкните Items.

• В четвертом поле щелкните поле Count of EmployeeID.

• Щелкните ОК.

Семь верхних значений по количеству EmployeeID будут отображаться в сводной таблице.

Как вы можете заметить, наибольшее количество сотрудников в финансовом году было нанято техниками-производителями, и большинство из них приходилось на первый квартал.

Фильтрация с использованием шкалы времени

Если ваша сводная таблица имеет поле даты, вы можете фильтровать сводную таблицу с помощью временной шкалы.

Создайте сводную таблицу из данных сотрудников, которые вы использовали ранее, и добавьте данные в модель данных в диалоговом окне «Создание сводной таблицы».

• Перетащите поле Заголовок в область СТРОК.

• Перетащите поле EmployeeID в область ЗНАЧЕНИЯ и выберите Подсчет для расчета.

• Щелкните сводную таблицу.

• Щелкните вкладку ВСТАВИТЬ.

• Щелкните Временная шкала в группе Фильтры. Откроется диалоговое окно «Вставить временную шкалу».

• Установите флажок HireDate.
• Щелкните ОК. Временная шкала появится на листе.
• Инструменты временной шкалы появятся на ленте.

Как видите, все периоды - в месяцах отображаются на временной шкале.

• Нажмите на стрелку рядом с - МЕСЯЦЫ.

• В раскрывающемся списке выберите ЧЕТВЕРТИ. Отображение временной шкалы изменится на Все периоды - в кварталах.

• Щелкните на 2014 Q1.

• Удерживая нажатой клавишу Shift, перетащите на четвертый квартал 2014 года. Период временной шкалы выбран с 1 по 4 квартал 2014 года.

• Сводная таблица фильтруется по этому периоду временной шкалы.

Очистка фильтров

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

Очистка всех фильтров в сводной таблице

Вы можете очистить все фильтры, установленные в сводной таблице, за один раз следующим образом:

• Щелкните вкладку ГЛАВНАЯ на ленте.
• Щелкните Сортировка и фильтр в группе редактирования.
• В раскрывающемся списке выберите Очистить.

Очистка фильтра метки, даты или значения

Чтобы очистить фильтр метки, даты или значения, выполните следующие действия:

• Щелкните значок в метках строк или столбцов.

• Щелкните

  
  <имя поля>, для которого вы хотите очистить фильтр, в поле «Выбрать поле» в раскрывающемся списке.

• Щелкните Очистить фильтр из <имя файла>, который появляется в раскрывающемся списке.

• Щелкните ОК. Конкретный фильтр будет очищен.

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

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

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

Вставка слайсеров

Предположим, вы хотите отфильтровать эту сводную таблицу на основе полей - «Регион» и «Месяц».

• Нажмите на АНАЛИЗ в разделе ИНСТРУМЕНТЫ ПОВОРОТНОГО СТОЛА на ленте.

• Нажмите «Вставить слайсер» в группе «Фильтр». Откроется диалоговое окно «Вставить срезы». Он содержит все поля из вашей таблицы данных.

• Установите флажки «Регион» и «Месяц».

• Щелкните ОК.

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

Фильтрация с помощью слайсеров

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

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

• Щелкните Юг в слайсере для региона. Только Юг будет выделен в слайсере - Регион.

• Удерживая нажатой клавишу Ctrl, нажмите Запад в слайсере для региона.

• Щелкните февраль в слайсере для месяца.

• Удерживая нажатой клавишу Ctrl, нажмите Март в слайсере для месяца.

Выбранные элементы в слайсерах выделяются. Отобразится сводная таблица со сводными значениями для выбранных элементов.

Чтобы добавить / удалить значения поля из фильтра, удерживайте нажатой клавишу Ctrl и нажимайте эти кнопки в срезе поля.

Очистка фильтра в слайсере

Чтобы очистить фильтр в слайсере, нажмите

в правом верхнем углу слайсера.

Удаление слайсера

Предположим, вы хотите удалить срез для поля «Регион».

• Щелкните правой кнопкой мыши слайсер - регион.
• Щелкните Удалить «Регион» в раскрывающемся списке.

Инструменты для слайсера

После того, как вы вставите слайсер, инструменты слайсера появятся на ленте с вкладкой ОПЦИИ. Чтобы просмотреть инструменты слайсера, щелкните слайсер.

Как вы можете заметить, на вкладке Инструменты слайсера - ОПЦИЯ у вас есть несколько вариантов для изменения внешнего вида слайсера, которые включают:

• Заголовок слайсера
• Настройки слайсера
• Отчет о подключениях
• Панель выбора

Заголовок слайсера

Вы можете найти поле Slicer Caption в группе Slicer. Заголовок слайсера - это заголовок, который отображается на слайсере. По умолчанию,Slicer Caption это имя поля, которое оно представляет.

• Щелкните слайсер для региона.
• Щелкните вкладку ОПЦИИ на ленте.

Группа слайсеров на ленте, в поле "Заголовок слайсера", область отображается как заголовок слайсера. Это имя поля, для которого вставлен срез. Вы можете изменить заголовок слайсера следующим образом -

• Щелкните поле «Заголовок слайсера» в группе слайсеров на ленте.

• Удалить регион. Поле очищено.

• Введите Местоположение в поле и нажмите Enter. Заголовок слайсера изменится на «Местоположение» и будет отражен в заголовке слайсера.

Note- Вы изменили только заголовок слайсера, то есть заголовок. Имя поля, которое представляет срез - Регион остается как есть.

Настройки слайсера

Вы можете использовать настройки слайсера, чтобы изменить имя слайсера, изменить заголовок слайсера, выбрать, отображать ли заголовок слайсера или нет, и установить параметры сортировки и фильтрации для элементов -

• Нажмите на слайсер - Местоположение.

• Щелкните вкладку ОПЦИИ на ленте. Вы можете найти настройки слайсера в группе слайсеров на ленте. Вы также можете найти настройки слайсера в раскрывающемся списке, щелкнув правой кнопкой мыши слайсер.

• Щелкните Настройки слайсера. Откроется диалоговое окно «Параметры слайсера».

Как вы можете заметить, для слайсера исправлено следующее:

• Название источника.
• Имя для использования в формулах.

Вы можете изменить следующее для слайсера -

• Name.
• Заголовок - Заголовок.
• Отобразить заголовок.
• Параметры сортировки и фильтрации для элементов, отображаемых на срезе.

Отчет о подключениях

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

• Сводные таблицы создаются с использованием тех же данных.

• Одна сводная таблица была скопирована и вставлена ​​как дополнительная сводная таблица.

• Несколько сводных таблиц создаются на отдельных листах с функцией Показать страницы фильтров отчета.

Рассмотрим следующие сводные таблицы, созданные из одних и тех же данных:

• Назовите верхнюю сводную таблицу PivotTable-Top, а нижнюю - PivotTable-Bottom.
• Щелкните верхнюю сводную таблицу.
• Вставьте слайсер для поля «Регион».
• Выберите Восток и Север на слайсере.

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

• Щелкните на слайсере - Регион. ИНСТРУМЕНТЫ SLICER появятся на ленте.
• Щелкните вкладку ОПЦИИ на ленте.

Вы найдете Report Connections в группе Slicer на ленте. Вы также можете найти подключения к отчету в раскрывающемся списке, щелкнув правой кнопкой мыши срез.

Нажмите Report Connections в группе слайсеров.

В Report Connectionsпоявится диалоговое окно. Флажок Сводная таблица-верх установлен, а остальные флажки сняты. Также установите флажок «Сводная таблица - снизу» и нажмите «ОК».

Нижняя сводная таблица будет отфильтрована по выбранным элементам - Восток и Север.

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

Панель выбора

Вы можете выключать и включать отображение срезов на листе с помощью области выбора.

• Нажмите на слайсер - Местоположение.

• Щелкните вкладку ОПЦИИ на ленте.

• Щелкните область выделения в группе «Упорядочить» на ленте. Панель выбора появляется в правой части окна.

Как вы можете заметить, имена всех срезов перечислены на панели выбора. Справа от имен вы можете найти символ видимости,

означающий, что срез отображается на листе.

Щелкните

символ месяца. Символ изменится на символ , указывая на то, что слайсер скрыт (не виден).

Как видите, слайсер - Месяц не отображается на листе. Однако помните, что вы не удаляли слайсер для месяца, а только что скрыли его.

• Щелкните

  
  символ месяца.

• Символ

  
  изменится на символ
  
  , показывая, что слайсер теперь виден.

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

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

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

В этой главе вы поймете порядок вложения полей и то, как вы можете изменить порядок вложения.

Порядок вложенности полей

Рассмотрим пример данных о продажах, где вы разместили поля в следующем порядке:

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

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

Как видите, значения второго поля в порядке вложенности встроены под каждое из значений первого поля.

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

Изменение порядка размещения

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

Щелкните поле «Продавец» в области СТРОК и перетащите его под полем «Регион». Таким образом, вы изменили порядок размещения на - Сначала регион и затем продавец, как показано ниже:

Результирующая сводная таблица будет такой, как показано ниже -

Вы можете ясно видеть, что Макет с порядком размещения - Регион, а затем Продавец дает лучший и компактный отчет, чем отчет с порядком размещения - Продавец и затем Регион.

Если продавец представляет более одной области, и вам необходимо суммировать продажи по продавцу, то предыдущий макет был бы лучшим вариантом.

На рабочем листе, содержащем сводную таблицу, лента будет содержать инструменты сводной таблицы с вкладками АНАЛИЗ и ДИЗАЙН. На вкладке АНАЛИЗ есть несколько команд, которые позволят вам исследовать данные в сводной таблице. Команды вкладки ДИЗАЙН будут полезны для структурирования сводной таблицы с различными параметрами отчета и параметрами стиля.

В этой главе вы изучите команды ANALYZE. Вы изучите команды ДИЗАЙН в главе - Эстетические отчеты со сводными таблицами.

Команды ANALYZE

Команды на вкладке «Лента АНАЛИЗА» включают следующие:

• Расширение и сворачивание поля.
• Группировка и разгруппировка значений полей.
• Настройки активного поля.
• Параметры сводной таблицы.

Расширение и сворачивание поля

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

Рассмотрим следующую сводную таблицу, в которой поле «Продавец» вложено в поле «Регион».

Щелкните

символ слева от Востока. Элемент к востоку от области поля будет свернут.

Как видите, остальные элементы - север, юг и запад области Региона - не свернуты. Если вы хотите свернуть какой-либо из них, повторите шаги, которые вы сделали для Востока.

• Нажмите на

  
  символ слева от Востока. Пункт Восток поля Регион будет расширяться.

Если вы хотите свернуть все элементы поля сразу, сделайте следующее:

• Щелкните любой из элементов поля - Регион.
• Щелкните вкладку АНАЛИЗ на ленте.
• Щелкните Свернуть поле в группе Активное поле.

Все элементы поля Регион будут свернуты.

Если вы хотите развернуть все элементы поля одновременно, сделайте следующее:

• Нажмите на любой из элементов поля - Регион.
• Щелкните вкладку АНАЛИЗ на ленте.
• Щелкните Развернуть поле в группе Активное поле.

Все элементы поля Регион будут расширены.

Группировка и разгруппировка значений полей

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

• Выберите элементы «Восток» и «Север» поля «Регион» в сводной таблице вместе с вложенными элементами поля «Продавец».

• Щелкните вкладку АНАЛИЗ на ленте.

• Нажмите Выбор группы в группе - Группа.

Элементы - Восток и Север будут сгруппированы под именем Group1. Кроме того, создается новый Юг, в который вложен Юг, и создается новый Запад, в который вложен Запад.

Вы также можете заметить, что новое поле - Region2 - добавлено в список полей сводной таблицы, который появляется в области ROWS.

• Выберите элементы «Юг» и «Запад» поля «Регион 2» в сводной таблице, а также вложенные элементы поля «Регион» и «Продавец».

• Щелкните вкладку АНАЛИЗ на ленте.

• Нажмите Выбор группы в группе - Группа.

Пункты - Юг и Запад поля Регион будут сгруппированы под названием Группа2.

Чтобы разгруппировать группу, сделайте следующее -

• Щелкните имя группы.
• Щелкните вкладку АНАЛИЗ.
• Нажмите Разгруппировать в группе - Группа.

Группировка по полю даты

Рассмотрим следующую сводную таблицу, в которой у вас есть данные о сотрудниках, суммированные по Count of EmployeeID, по найму и должности.

Предположим, вы хотите сгруппировать эти данные по полю HireDate, которое является полем даты, по годам и кварталам.

• Щелкните элемент даты в сводной таблице.
• Щелкните вкладку АНАЛИЗ на ленте.
• Нажмите Group Field в группе - Group.

Появится диалоговое окно "Группировка".

• Установите даты для - Начало и Окончание в.

• Выберите кварталы и годы в поле под. Чтобы выбрать / отменить выбор нескольких элементов, удерживайте нажатой клавишу Ctrl.

• Щелкните ОК.

Значения поля HireDate будут сгруппированы в кварталы, вложенные в годы.

Если вы хотите разгруппировать эту группу, вы можете сделать, как показано ранее, щелкнув Ungroup в группе - Группа на ленте.

Настройки поля активного значения

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

Предположим, вы хотите установить параметры для поля «Регион».

• Нажмите на Восток. На ленте в группе «Активное поле» в поле «Активное поле» будет отображаться «Регион».

• Нажмите на Field Settings. Откроется диалоговое окно "Параметры поля".

Вы можете установить свои предпочтения для поля - Регион.

Параметры сводной таблицы

Вы можете установить параметры сводной таблицы в соответствии со своими предпочтениями.

• Щелкните сводную таблицу.
• Щелкните вкладку АНАЛИЗ.
• Щелкните Параметры в группе сводной таблицы.

В PivotTable Optionsпоявится диалоговое окно. Вы можете установить свои предпочтения в диалоговом окне.

Вы можете подвести итоги сводной таблицы, поместив поле в область ∑ ЗНАЧЕНИЯ на панели задач «Поля сводной таблицы». По умолчанию Excel принимает суммирование как сумму значений поля в области ∑ ЗНАЧЕНИЯ. Однако у вас есть другие типы вычислений, такие как Count, Average, Max, Min и т. Д.

В этой главе вы узнаете, как установить тип расчета в зависимости от того, как вы хотите суммировать данные в сводной таблице.

Сумма

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

Как вы можете заметить, когда вы перетаскиваете поле «Сумма заказа» в область ∑ ЗНАЧЕНИЯ, оно отображается как сумма суммы заказа, указывая на то, что расчет принимается как сумма. В сводной таблице в верхнем левом углу отображается сумма суммы заказа. Кроме того, столбец «Общий итог» и строка «Общий итог» отображаются для промежуточных итогов по полям в строках и столбцах соответственно.

Настройки поля значений

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

• Щелкните Сумма суммы заказа в области ∑ ЗНАЧЕНИЯ.
• В раскрывающемся списке выберите Параметры поля значений.

Откроется диалоговое окно "Параметры поля значений".

Имя источника - это поле, а настраиваемое имя - это сумма поля. Тип расчета - Сумма. Щелкните значокShow Values As таб.

В поле Показать значения как, No Calculationотображается. Щелкните значокShow Values Asкоробка. Вы можете найти несколько способов показать свои общие значения.

% от общей суммы

Вы можете отобразить значения в сводной таблице как% от общего итога.

• В поле Пользовательское имя введите% от общей суммы.
• Щелкните поле Показать значения как.
• Нажмите на% от общей суммы в раскрывающемся списке. Щелкните ОК.

Сводная таблица суммирует значения как% от общей суммы.

Как вы можете заметить, сумма суммы заказа в верхнем левом углу сводной таблицы и в области ∑ ЗНАЧЕНИЯ на панели полей сводной таблицы изменяется на новое настраиваемое имя -% от общей суммы.

• Щелкните заголовок столбца «Общая сумма».

• Введите% от общей суммы в строке формул. Заголовки столбца и строки изменятся на% от общей суммы.

% от общей суммы столбца

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

• Щелкните Сумма суммы заказа в области ∑ ЗНАЧЕНИЯ.

• В раскрывающемся списке выберите Параметры поля значений. Откроется диалоговое окно "Параметры поля значений".

• В поле Пользовательское имя введите% от общей суммы за месяц.

• Щелкните поле Показать значения как.

• Выберите% от общей суммы столбца из раскрывающегося списка.

• Щелкните ОК.

В сводной таблице значения суммируются как% от итогового значения столбца. В столбцах «Месяц» вы найдете значения как% от общей суммы за конкретный месяц.

• Щелкните заголовок столбца «Общая сумма».

• Введите% от итога по столбцу в строке формул. Заголовки столбца и строки изменятся на% от общей суммы столбца.

% от итога строки

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

Считать

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

• Снимите флажок Сумма заказа.

• Перетащите учетную запись в область ∑ ЗНАЧЕНИЯ. Сумма счета будет отображаться в области ∑ ЗНАЧЕНИЯ.

• Щелкните Сумма счета.

• В раскрывающемся списке выберите Параметры поля значений. Откроется диалоговое окно "Параметры поля значений".

• В поле Суммировать значение по полю выберите Подсчитать. Пользовательское имя изменится на Количество учетных записей.

• Щелкните ОК.

Счетчик учетной записи будет отображаться, как показано ниже -

В среднем

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

• Отмените выбор учетной записи.

• Перетащите Сумму заказа в область ∑ ЗНАЧЕНИЯ. Сумма суммы заказа будет отображаться в области ∑ ЗНАЧЕНИЯ.

• Щелкните Сумма суммы заказа.

• В раскрывающемся списке щелкните Параметры поля значений. Откроется диалоговое окно "Параметры поля значений".

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

• Щелкните ОК.

Среднее значение будет отображаться, как показано ниже -

Вы должны установить числовой формат значений в сводной таблице, чтобы сделать ее более презентабельной.

• Щелкните Среднее значение суммы заказа в области ∑ ЗНАЧЕНИЯ.

• В раскрывающемся списке щелкните Параметры поля значений. Откроется диалоговое окно "Параметры поля значений".

• Нажмите кнопку числового формата.

Откроется диалоговое окно «Формат ячеек».

• Щелкните номер под категорией.
• Введите 2 в поле Десятичные разряды и нажмите ОК.

Значения сводной таблицы будут отформатированы в числа с двумя десятичными знаками.

• Щелкните заголовок столбца «Общая сумма».

• Введите Средняя сумма заказа в строку формул. Заголовки столбца и строки изменятся на «Средняя сумма заказа».

Максимум

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

• Щелкните Сумма суммы заказа.

• В раскрывающемся списке выберите Параметры поля значений. Откроется диалоговое окно "Параметры поля значений".

• В поле Суммировать значение по полю щелкните Макс. Пользовательское имя изменится на Максимальную сумму заказа.

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

• Щелкните заголовок столбца «Общая сумма».

• Введите Максимальная сумма заказа в строке формул. Заголовки столбца и строки изменятся на максимальную сумму заказа.

Мин.

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

• Щелкните Сумма суммы заказа.

• В раскрывающемся списке щелкните Параметры поля значений. Откроется диалоговое окно "Параметры поля значений".

• В поле Суммировать значение по полю щелкните Min. Пользовательское имя изменится на Минимальную сумму заказа.

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

• Щелкните заголовок столбца «Общая сумма».

• Введите минимальную сумму заказа в строке формул. Заголовки столбца и строки изменятся на минимальную сумму заказа.

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

В этой главе вы узнаете о различных способах обновления макета и / или обновления данных в сводной таблице.

Обновление макета сводной таблицы

Вы можете решить, будет ли ваша сводная таблица обновляться всякий раз, когда вы вносите изменения в макет, или она должна обновляться отдельным триггером.

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

Отметьте опцию - Defer Layout Update.

Кнопка ОБНОВЛЕНИЕ рядом с ним будет активирована. Если вы внесете какие-либо изменения в области сводной таблицы, изменения будут отражены только после того, как вы нажмете кнопку ОБНОВИТЬ.

Обновление данных сводной таблицы

Когда данные сводной таблицы изменяются в ее источнике, то же самое можно отразить в сводной таблице, обновив ее.

• Щелкните сводную таблицу.
• Щелкните вкладку АНАЛИЗ на ленте.
• Щелкните Обновить в группе данных.

Есть разные варианты обновления данных в раскрывающемся списке -

• Refresh - Получить последние данные из источника, подключенного к активной ячейке.

• Refresh All - Чтобы получить последние данные, обновив все источники в книге.

• Connection Properties - Чтобы установить свойства обновления для подключений к книге.

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

Вы можете изменить диапазон исходных данных сводной таблицы. Например, вы можете расширить исходные данные, чтобы включить большее количество строк данных.

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

• Щелкните сводную таблицу. PIVOTTABLE TOOLS появляются на ленте.

• Щелкните вкладку АНАЛИЗ.

• Щелкните Изменить источник данных в группе данных.

В раскрывающемся списке выберите «Изменить источник данных».

Появится диалоговое окно «Изменить источник данных сводной таблицы», и текущий источник данных будет выделен.

Выберите таблицу или диапазон, который вы хотите включить, в поле «Таблица / диапазон» в разделе «Выберите таблицу или диапазон». Щелкните ОК.

Источник данных для сводной таблицы будет изменен на выбранную таблицу / диапазон данных.

Переход на внешний источник данных

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

• Щелкните сводную таблицу.

• Щелкните вкладку АНАЛИЗ на ленте.

• Нажмите Change Data Sourceв группе Данные. ВChange PivotTable Data Source появится диалоговое окно.

• Щелкните значок Choose Connection кнопка.

В Existing Connections появится диалоговое окно.

• Выберите все подключения в поле «Показать». Отобразятся все подключения в вашей книге.

• Щелкните значок Browse for More кнопка.

В Select Data Source появится окно.

• Щелкните кнопку «Новый источник».
• Выполните шаги мастера подключения к данным.

Если ваш источник данных находится в другой книге Excel, выполните следующие действия:

• Щелкните поле Имя файла.
• Выберите имя файла книги.

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

Вы можете удалить сводную таблицу следующим образом -

• Щелкните сводную таблицу.
• Щелкните вкладку АНАЛИЗ на ленте.
• Нажмите Выбрать в группе Действия.

Выбрать Entire PivotTableиз раскрывающегося списка. Будет выбрана вся сводная таблица.

Нажмите клавишу удаления. Сводная таблица будет удалена.

Если сводная таблица находится на отдельном листе, вы также можете удалить сводную таблицу, удалив весь рабочий лист.

Щелкните правой кнопкой мыши вкладку рабочего листа и выберите Delete из раскрывающегося списка.

Весь рабочий лист вместе со сводной таблицей удаляется.

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

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

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

Иерархии

Вы узнали, как вкладывать поля для формирования иерархии, в главе «Вложение в сводную таблицу» этого руководства. Вы также узнали, как группировать / разгруппировать данные в сводной таблице в главе «Использование инструментов сводной таблицы». Мы рассмотрим несколько примеров, чтобы показать вам, как создавать интерактивные отчеты сводной таблицы с иерархиями.

Если у вас есть встроенная структура для полей в ваших данных, например, Год-Квартал-Месяц, вложение полей для формирования иерархии позволит вам быстро развернуть / свернуть поля для просмотра сводных значений на требуемом уровне.

Например, предположим, что у вас есть данные о продажах за 2015–16 финансовый год для регионов - Востока, Севера, Юга и Запада, как показано ниже.

Создайте сводную таблицу, как показано ниже.

Как видите, это комплексный способ представления данных с использованием вложенных полей в виде иерархии. Если вы хотите отображать результаты только на уровне кварталов, вы можете быстро свернуть поле «Квартал».

Предположим, у вас есть поле даты в ваших данных, как показано ниже.

В таком случае вы можете сгруппировать данные по полю даты следующим образом:

Создайте сводную таблицу.

Как видите, в этой сводной таблице неудобно выделять важные данные.

• Сгруппируйте сводную таблицу по полю даты. (Вы узнали о группировке в главе - Исследование данных с помощью инструментов сводных таблиц в этом руководстве).

• Поместите поле «Продавец» в область «Фильтры».

• Отфильтруйте метки столбца по Восточному региону.

Фильтр отчетов

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

• Убедитесь, что у вас есть поле «Продавец» в области «Фильтры».
• Щелкните сводную таблицу.
• Щелкните вкладку АНАЛИЗ на ленте.
• Щелкните стрелку рядом с параметром в группе сводной таблицы.
• В раскрывающемся списке выберите Показать страницы фильтра отчета.

В Show Report Filter Pagesпоявится диалоговое окно. Выберите поле Продавец и нажмите ОК.

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

Рабочий лист будет назван по значению поля, которое отображается на вкладке рабочего листа.

Слайсеры

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

• Щелкните сводную таблицу.

• Щелкните вкладку АНАЛИЗ.

• Нажмите «Вставить слайсер» в группе «Фильтр».

• Нажмите Order Date, Quarters and Yearsв диалоговом окне "Вставить срезы". Будут созданы три слайсера - дата заказа, кварталы и год.

• Отрегулируйте размеры срезов, добавив больше столбцов для кнопок на срезах.

• Также создайте срезы для полей «Продавец» и «Регион».

• Выберите стили слайсера, чтобы поля даты были сгруппированы по одному цвету, а два других поля имели разные цвета.

• Снимите флажок Линии сетки.

Как видите, у вас есть не только интерактивный отчет, но и интересный, понятный.

Временная шкала в сводной таблице

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

• Создайте сводную таблицу с продавцом в области ROWS и регионом в области COLUMNS.
• Вставьте временную шкалу для поля Дата заказа.
• Отфильтруйте временную шкалу, чтобы отобразить данные за 5 месяцев, с ноября 2015 года по март 2016 года.

ДИЗАЙН Команды

В PIVOTTABLE TOOLS - DESIGN Команды на ленте предоставляют вам параметры для форматирования сводной таблицы, включая следующие:

• Layout
• Параметры стиля сводной таблицы
• Стили сводной таблицы

Макет

Вы можете иметь макет сводной таблицы в соответствии с вашими предпочтениями для следующего:

• Subtotals
• Общие итоги
• Макет отчета
• Пустые строки

PivotTable Layout – Subtotals

У вас есть возможность отображать Subtotalsили нет. По умолчанию промежуточные итоги отображаются в верхней части группы.

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

• Щелкните сводную таблицу.
• Щелкните вкладку ДИЗАЙН на ленте.
• Щелкните Промежуточные итоги в группе Параметры макета.
• Щелкните Показать все промежуточные итоги внизу группы.

Теперь промежуточные итоги появятся внизу каждой группы.

Если вам не нужно сообщать промежуточные итоги, вы можете выбрать - Не показывать промежуточные итоги.

Общие итоги

Вы можете выбрать отображение общих итогов или нет. У вас есть четыре возможных комбинации -

• Отключено для строк и столбцов
• Включено для строк и столбцов
• Включено только для строк
• Включено только для столбцов

По умолчанию это вторая комбинация - Вкл для строк и столбцов.

Макет отчета

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

• Компактная форма.
• Наброски Форма.
• Табличная форма.

Вы также можете выбрать, повторять ли все метки элементов или нет, если они повторяются несколько раз.

Макет отчета по умолчанию - это компактная форма, с которой вы знакомы.

Compact Form

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

Нажмите на Show в контурной форме.

Нажмите Show в табличной форме.

Рассмотрим следующий макет сводной таблицы, в котором поле «Месяц» вложено в поле «Регион» -

Как видите, метки месяца повторяются, и это значение по умолчанию.

Щелкните "Не повторять метки элементов". Метки месяца будут отображаться только один раз, а сводная таблица выглядит ясной.

Пустые строки

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

Нажмите Insert Blank Line after Each Item.

Параметры стиля сводной таблицы

У вас есть следующие параметры стиля сводной таблицы -

• Заголовки строк
• Заголовки столбцов
• Полосатые ряды
• Чередующиеся столбцы

По умолчанию установлены флажки для заголовков строк и столбцов. Эти параметры предназначены для отображения специального форматирования для первой строки и первого столбца соответственно. Установите флажокBanded Rows.

Установите флажок Чередующиеся столбцы.

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

Вы можете выбрать несколько стилей сводной таблицы. Выберите тот, который подходит для вашего отчета. Например, если вы выберете Pivot Style Dark 5, вы получите следующий стиль для сводной таблицы.

Условное форматирование в сводной таблице

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

Сводные диаграммы

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

• Щелкните сводную таблицу.
• Щелкните вкладку АНАЛИЗ на ленте.
• Щелкните Сводная диаграмма.

Откроется диалоговое окно «Вставить диаграмму».

Щелкните столбец на левой панели и выберите столбец с накоплением. Щелкните ОК.

Отображается столбчатая диаграмма с накоплением.

• Щелкните Месяц на сводной диаграмме.
• Отфильтруйте до февраля и нажмите ОК.

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