Разное

Pivot table: Pivot Tables in Excel — Easy Excel Tutorial

Содержание

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

Исходные данные

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

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

Исходная таблица для создания сводного отчета

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

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

Рекомендуемые сводные таблицы

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

 

  1. Вкладка Вставка

    Выберите любую ячейку исходной таблицы.

  2. Перейдите на вкладку ленты Вставка [Insert] и найдите группу Таблицы [Table].

  3. Выберите команду Рекомендуемые сводные таблицы [Recommended PivotTables].

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

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

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

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

 

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

    Выберите любую ячейку исходной таблицы.

  2. Перейдите на вкладку ленты Вставка [Insert] и найдите группу Таблицы [Table].

  3. Выберите команду Сводная таблица [PivotTable].

  4. В диалоговом окне Создание сводной таблицы [Create PivotTable] убедитесь в правильности диапазона данных, на основе которого будет строиться отчет. Если диапазон некорректный, его нужно поменять в поле Таблица и диапазон [Table/Range].

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

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

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

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

  1. После формирования сводной таблицы в выбранном месте появиться область с ее названием. По умолчанию отчет называется СводнаяТаблица1 [PivotTable 1].  Для начала работы необходимо щелкнуть левой клавшей мыши по данной области. В результате в правой части листа откроется макет сводной таблицы.

Макет сводной таблицы состоит из списка полей сводной таблицы, в котором перечислены все заголовки исходной таблицы и четырех областей: ФИЛЬТРЫ, КОЛОННЫ, СТРОКИ и ЗНАЧЕНИЯ.

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

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

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

Данную операцию можно сделать еще 2 способами:


  • отметить флажок напротив поля Группа;
  • щелкнуть по полу Группа правой кнопкой мыши и выбрать Добавить в названия строк [Add to Row Labels].

После добавления поля вы увидите список всех групп, которые есть в исходной таблице:

 Добавление строк в сводную таблицу

Теперь осталось добавить сумму по полю Рыночная стоимость. Для этого перенесем поле Рыночная стоимость в область значений. Желаемая таблица получена.

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

Скачать

Как использовать сводную таблицу Excel для анализа состояния проекта — Трюки и приемы в Microsoft Excel

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

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

Рис. 1. Реестр рисков проекта Grant St. Move

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

Рис. 2. Результат преобразования в таблицу выделенного диапазона ячеек

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

Теперь, когда данные представлены в табличном формате, вы можете создать сводную таблицу, не забудьте купить клавиатуру для компьютера. Для начала установите табличный курсор в любой ячейке этой таблицы, затем щелкните на значке PivotTable (Сводная таблица) вкладки Insert (Вставка) и в появившемся меню выберите команду PivotTable (Сводная таблица).

Рис. 3. Значок PivotTable расположен в крайней части вкладки Insert (Вставка)

На экране появится диалоговое окно Create Pivot Table (Создание сводной таблицы), показанное на рис. 4. В этом диалоговом окне следует указать программе, на основе каких данных будет построена сводная таблица — рабочего листа текущей рабочей книги или внешних данных (например, SQL Server). Обратите внимание, что в поле Table/Range (Таблица или диапазон) мы оставили заданное по умолчанию значение — Table1 (Таблица1).

Рис. 4. Диалоговое окно Create Pivot Table (Создание сводной таблицы)

Щелкните на кнопке ОК. Программа немедленно создаст макет сводной таблицы па новом рабочем листе (рис. 5), в правой части которого расположена панель Pivot Table Field List (Список полей сводной таблицы). Обратите внимание, что в верхней части этой панели перечислены названия всех полей созданной нами таблицы реестра рисков.

Рис. 5. Рабочий лист, на котором расположен макет сводной таблицы и панель Pivot Table Field List (Список полей сводной таблицы)

Названия полей, перечисленные на панели Pivot Table Field List, представляют собой названия заголовков столбцов, взятые из нашей таблицы. Области макета сводной таблицы предназначены для различного отображения данных. Их можно представлять как некую трехаспектную палитру. Допустим, нам требуется узнать количество рисков по каждой категории. Например, сколько внешних рисков у нашего проекта? Начните с перетаскивания поля Risk Category (Категория риска), как показано на рис. 6, в область Drop Row Fields Here (Перетащите сюда поля строк). (Местоположение этой области показано на рис. 5).

Рис. 6. Результат перетаскивания поля Risk Category (Категория риска) в область Drop Row Fields Here (Перетащите сюда поля строк)

Как видите, название поля Risk Category (Категория риска) появилось в области Row Labels (Названия строк), которая расположена в нижней части панели Pivot Table Field List (Список полей сводной таблицы). Теперь перетащите поле Risk Name (Название риска) в область макета сводной таблицы Drop Data Items Here (Перетащите сюда элементы данных), как показано на рис. 7.

Рис. 7. Результат перетаскивания поля Risk Name (Название риска) в область Drop Data /terns Here (Перетащите сюда элементы данных)

Обратите внимание, что в нижней части созданной нами сводной таблицы программа Excel автоматически добавила строку с заголовком Grand Total (Общий итог), в которой отображено общее количество названий рисков по отдельным категориям. Теперь нетрудно заметить, что, например, категория «Связанные с решением кадровых вопросов» (поле Organizational) содержит два риска, категория «Технические» (поле Technical) — четыре и т.д. В последней строке — Grand Total (Общий итог) — указано общее количество рисков (11) по всем категориям.

Как вы, должно быть, заметили, в области Values (Значения), расположенной в нижней части панели Pivot Table Field List (Список полей сводной таблицы), появился элемент Count of Risk Name (Количество по полю Risk Name), т.е. суммарное количество рисков (см. рис. 7). Существует множество способов отображения, представления и подсчета данных в сводных таблицах Excel. Если хотите увидеть результаты и подсчеты, которые сделает для вас Excel, поэкспериментируйте с перемещением полей из списка панели Pivot Table Field List (Список полей сводной таблицы) в разные области макета сводной таблицы.

Сводная таблица с графиками в один клик

Сводная таблица. Автоматически.
Отчёты в удовольствие с инновациями NeoNeuro!

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

Шаг 1. Возьмите файл Excel или CSV

Возьмите Ваши данные в форматах MS Excel, csv или подключитесь к базе данных. Первая строка должна содержать заголовки, остальные строки — данные. Просто откройте их в NeoNeuro Сводная Таблица и сразу получите детальный анализ данных.

Шаг 2. Концепция Времени и Денег

Шаг 2. Концепция Времени и Денег

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

Шаг 3. Получите анализ в 1 клик

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

Кликайте по ячейкам и колонкам

Кликайте по ячейкам и колонкам

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

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

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

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

Автоматическая группировка данных

Автоматическая группировка данных

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

Настройка группы

Настройка группы «Другие»

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

Показатели

Показатели

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

Круговая диаграмма распределения

Круговая диаграмма распределения

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

Множественный график

Множественный график

Сравнивайте динамику по нескольким категориям при помощи наложения графиков.

Вкладка

Вкладка «Быстрая сводная таблица» и диаграмма сравнения периодов

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

Перерасчёт таблицы для г. Москва

Перерасчёт таблицы для г. Москва

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

Автоматическое разбиение цен и других численных показателей на кластеры

Автоматическое разбиение цен и других численных показателей на кластеры

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

Автоматический отчёт в Excel

Автоматический отчёт в Excel

Настраиваемый отчёт в Excel обеспечивает создание полного финансового отчёта автоматически. Каждый параметр анализируется на своём листе с диаграммами и сводными таблицами.

Гибкая настройка заголовков и данных

Гибкая настройка заголовков и данных

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

Автоматическая загрузка приложения: Dashboard

Автоматическая загрузка приложения: Dashboard

Сохраните параметры анализа в .npt file, и установите галочку в Настройки-Общие-Открывать последний файл автоматически во время старта приложения: и Вы получите удобный dashboard для ваших данных сразу после загрузки приложения.

Подключение к базе данных

Подключение к базе данных

Удобные настройки подключения к БД позволяют получить данные из различных баз данных, включая MS SQL, Firebird, MySQL, Access, Oracle, PostgreSQL и многих других.

Подключение к онлайн данным

Подключение к онлайн данным

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

Ваше преимущество с программой Neoneuro Сводная Таблица

    • Интуитивная аналитика в один клик благодаря инновационному интерфейсу «Полная Сводная Таблица».
    • Концепция «Время — деньги» позволяет анализировать финансовую информацию без дополнительных настроек.
    • Вывод важных показателей (суммы, тенденции, средние и др.) рядом с именем каждой ячейки делает анализ невероятно наглядным и удобным.
    • Автоматический поиск улучшений и ухудшений за сравниваемые периоды укажет успешные и проблемные места вашего бизнеса
    • Ячейка «Другие» для наименее важных ячеек (по умолчанию в неё попадают ячейки с <2.5% от общей суммы или количества) позволит сфокусироваться на наиболее актуальных вопросах и не позволит замусорить диаграммы и отчёты лишней информацией.
    • Диаграммы с детальными подсказками помогают изучать тренды движением мыши.
    • Обработка ошибочных значений даты и суммы, что очень актуально для данных из Excel и CSV. Пользователь может игнорировать ошибки, либо узнать номера строк и колонок с битыми данными.
    • Создание сводной таблицы для любой колонки со всеми показателями происходит всего в один клик благодаря инновации «Быстрая сводная таблица».
    • Настраиваемый отчёт в Excel обеспечивает базовый финансовый анализ автоматически. Каждый параметр выводится на отдельный лист с графиками и сводными таблицами по всем ячейкам и показателям.
    • Программа подключается ко всем популярным базам данных: MS SQL, Access, Oracle, MySQL, PostgreSQL и к другим. 

Перейти к таблице сравнения программ анализа финансовых данных Microsoft Excel и NeoNeuro Pivot Table

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

в Excel — Easy Excel Tutorial

Вставить сводную таблицу | Перетащите поля | Сортировать | Фильтр | Изменить итоговый расчет | Двумерная сводная таблица

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

Наш набор данных состоит из 213 записей и 6 полей. Идентификатор заказа, продукт, категория, сумма, дата и страна.

Pivot Table Data in Excel

Вставить сводную таблицу

Чтобы вставить сводную таблицу , выполните следующие шаги.

1. Щелкните любую ячейку в наборе данных.

2. На вкладке Вставка в группе Таблицы щелкните Сводная таблица.

Появится следующее диалоговое окно. Excel автоматически выбирает данные за вас.Местоположение по умолчанию для новой сводной таблицы — Новый рабочий лист.

3. Щелкните OK.

Create PivotTable Dialog Box

Перетащите поля

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

1. Поле продукта в области Строки.

2. Поле количества в области значений.

3.Поле Страна в область Фильтры.

Drag Fields to Areas

Ниже представлена ​​сводная таблица. Бананы — наш основной экспортный продукт. Вот насколько простыми могут быть сводные таблицы!

Pivot Table

Сортировка

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

1. Щелкните любую ячейку в столбце «Сумма суммы».

2. Щелкните правой кнопкой мыши и выберите «Сортировка, сортировка от наибольшего к наименьшему».

Sort Largest to Smallest

Результат.

Sorted Pivot Table

Фильтр

Поскольку мы добавили поле «Страна» в область «Фильтры», мы можем фильтровать эту сводную таблицу по стране. Например, какие товары мы больше всего экспортируем во Францию?

1. Щелкните раскрывающийся список фильтров и выберите Франция.

Результат. Яблоки — наш основной экспортный продукт во Францию.

Filtered Pivot Table

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

Изменить итоговый расчет

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

1. Щелкните любую ячейку в столбце «Сумма суммы».

2. Щелкните правой кнопкой мыши и выберите Параметры поля значений.

Value Field Settings

3. Выберите тип расчета, который вы хотите использовать.Например, нажмите «Подсчитать».

Summarize Value Field By

4. Щелкните OK.

Результат. 16 из 28 заказов во Францию ​​были заказами Apple.

Count

Двумерная сводная таблица

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

1. Из поля Country в область Rows.

2. Перейдите в поле «Продукт» в область «Столбцы».

3. Поле количества в области значений.

4. Поле Категория в области Фильтры.

Create Two-dimensional Pivot Table

Ниже представлена ​​двухмерная сводная таблица.

Two-dimensional Pivot Table in Excel

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

Pivot Chart

Сводная диаграмма

в Excel — Easy Excel Tutorial

Вставить сводную диаграмму | Сводная диаграмма фильтров | Изменить тип сводной диаграммы

Сводная диаграмма — это визуальное представление сводной таблицы в Excel . Сводные диаграммы и сводные таблицы связаны друг с другом.

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

Two-dimensional Pivot Table in Excel

Вставить сводную диаграмму

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

1. Щелкните любую ячейку в сводной таблице.

2. На вкладке «Анализ» в группе «Инструменты» щелкните «Сводная диаграмма».

Click PivotChart

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

3. Щелкните OK.

Ниже представлена ​​сводная диаграмма. Эта сводная диаграмма поразит и впечатлит вашего начальника.

Pivot Chart in Excel

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

Сводная диаграмма фильтров

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

1.Используйте стандартные фильтры (треугольники рядом с товаром и страной). Например, используйте фильтр «Страна», чтобы показать только общую сумму каждого продукта, экспортированного в США.

Standard Filter

2. Снимите фильтр страны.

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

Report Filter

Изменить тип сводной диаграммы

Вы можете в любой момент перейти на другой тип сводной диаграммы.

1. Выберите диаграмму.

2. На вкладке «Дизайн» в группе «Тип» щелкните «Изменить тип диаграммы».

Change Chart Type

3. Выберите «Пирог».

Choose Pie

4. Щелкните OK.

Результат:

New Pivot Chart

Примечание: круговые диаграммы всегда используют одну серию данных (в данном случае Beans).Чтобы получить сводную диаграмму страны, поменяйте местами данные по оси. Сначала выберите диаграмму. Затем на вкладке «Дизайн» в группе «Данные» нажмите «Переключить строку / столбец».

Работа со сводными таблицами в Excel на примерах

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

Источник данных сводной таблицы

включает таблицу с несколькими десятками и сотнями строк, несколько таблиц в одной книге, несколько файлов.Давайте пересмотрим порядок создания сводной таблицы: «ВСТАВИТЬ» — «Таблицы» — «Сводные таблицы».

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

Взаимодействие с другими людьми

Как создать сводную таблицу из нескольких файлов

Первый шаг — перенос информации в Excel и преобразование ее в таблицы Excel. Если наши данные в Word, то переносим их в Excel и составляем таблицу по всем правилам Excel (даем заголовки столбцам, удаляем пустые строки и т. Д.).

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

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

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

Tables for example.

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

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

Но два заголовка в этих таблицах идентичны. Таким образом, мы можем объединить данные, а затем создать сводный отчет.

  1. Поместите курсор в целевую ячейку (куда будет перемещена таблица). Write = — перейти к листу с переданными данными — выбрать первую ячейку столбца, который копируется.Нажмите Ввод. «Умножьте» формулу, потянув ее за правый нижний угол ячейки.
  2. Multiply formula.

  3. Остальные данные передаем по такому же принципу. В результате мы получаем одну сводную таблицу из двух таблиц.
  4. result.

  5. Теперь создайте отчет сводной таблицы. ВСТАВИТЬ — Сводные таблицы — укажите диапазон и расположение — ОК.

specify the range and location.

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

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

Взаимодействие с другими людьми

Детализация информации в сводных таблицах

Из отчета (см. Выше) видно, что ВСЕГО продано 30 видеокарт. Чтобы узнать, какие данные использовались для получения этого значения, дважды щелкните число «30». Получить подробный отчет:

detailed report.

Как обновить данные в сводной таблице Excel?

Если вы измените какой-либо параметр в исходной таблице или добавите новую запись, эта информация не будет отображаться в отчете сводной таблицы.Эта ситуация нас не устраивает. Выбираем «Сводная таблица» — «АНАЛИЗ» — «Обновить» (ALT + F5). Курсор должен находиться в любой ячейке главного отчета.

Обновление данных:

ANALYZE.

Или щелкните правой кнопкой мыши — обновите.

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

  1. Курсор находится в любом месте отчета. «ИНСТРУМЕНТ Сводных таблиц» — «АНАЛИЗ» — «Сводная таблица» — «Параметры».
  2. Options.

  3. В открывшемся диалоге — Данные — Обновить данные при открытии файла — ОК.

Refresh data.

Изменение структуры отчета

Добавить новые поля в сводную таблицу:

  1. Вставьте столбец «Продажи» на лист с исходными данными. Здесь мы отразим, какую выручку магазин получит от продажи товара. Воспользуемся формулой — цена (F2) * количество проданных единиц (I2).
  2. formula.

  3. Перейти к листу с отчетом. «ИНСТРУМЕНТ Сводной таблицы» — «АНАЛИЗ» — «Изменить источник данных». Расширить круг информации, которая должна войти в сводную таблицу.

Change Data Source.

Если мы добавили столбцы внутри исходной таблицы, этого было достаточно для обновления сводной таблицы.

После изменения диапазона в сводке появилось поле «Продажи»

Как добавить вычисляемое поле в сводную таблицу?

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

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

Инструкция по добавлению вычисляемого поля:

  1. Определите, какие функции будет выполнять цифровой столбец. Определите данные в сводной таблице, на которые должно ссылаться вычисляемое поле. Допустим, вам нужны остатки по группам товаров.
  2. balances.

  3. «ИНСТРУМЕНТ Сводной таблицы» — «АНАЛИЗ» — «Поля, элементы и наборы» — «Вычисляемое поле».
  4. Items and Sets.

  5. Введите название поля в открывшемся меню. Установите курсор в строку «Формула». Инструмент «Расчетное поле» не реагирует на диапазоны. Поэтому выбирать ячейки в сводной таблице бессмысленно. Выберите из предложенного списка категории, которые необходимы для расчета. Затем нажмите «Вставить поле». Дополните формулу необходимыми арифметическими операциями.
  6. Calculated Field.

  7. Нажмите ОК. Появились остатки.

Residues appeared.

Группировка данных в консолидированном отчете

Например, посчитаем расходы на товары в разные годы: сколько денег было потрачено в 2012, 2013, 2014 и 2015 годах? Группировка по дате в сводной таблице Excel выполняется следующим образом.Например, сделаем простую сводку по дате доставки и цене.

For example.

Щелкните любую дату правой кнопкой мыши. Выберите команду «Группировать».

Group.

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

Years.

Вы получите количество заказов по годам.

Скачать рабочий пример

Done.

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

101 Продвинутые советы и рекомендации по работе с сводными таблицами, которые необходимо знать

Сводные таблицы — это круто! Это одна из самых мощных функций Excel. , они позволяют быстро суммировать большие объемы данных за считанные секунды. Эта коллекция из удивительных советов и приемов поможет вам освоить сводные таблицы и стать ниндзя данных!

Вы узнаете все советы, которые используют профессионалы, так что приготовьтесь к очень-очень длинному посту!

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

Видеоурок: 25 лучших советов и приемов для расширенных сводных таблиц

Ваши исходные данные должны быть в табличном формате

Data-in-Tabular-Format 101 Advanced Pivot Table Tips And Tricks You Need To Know

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

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

Используйте таблицу для исходных данных

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

Create-Pivot-Table-Menu-Using-Range 101 Advanced Pivot Table Tips And Tricks You Need To Know

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

Чтобы создать и назвать вашу таблицу.

  1. Выберите ваши данные.
  2. Перейдите на вкладку Insert и нажмите кнопку Table в разделе Tables или используйте сочетание клавиш Ctrl + T.
  3. Нажмите кнопку OK .
  4. С активной ячейкой внутри таблицы перейдите на вкладку Table Tools Design .
  5. Измените Имя таблицы в разделе Свойства и нажмите Введите .

Create-Pivot-Table-Menu-Using-A-Table 101 Advanced Pivot Table Tips And Tricks You Need To Know

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

Изменить исходные данные

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

Change-Data-Source-for-a-Pivot-Table 101 Advanced Pivot Table Tips And Tricks You Need To Know

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

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

Undock-the-PivotTable-Field-window 101 Advanced Pivot Table Tips And Tricks You Need To Know

От

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

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

Quickly-Dock-The-PivotTable-Field-Window 101 Advanced Pivot Table Tips And Tricks You Need To Know

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

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

Show-Or-Hide-The-PivotTable-Field-Window 101 Advanced Pivot Table Tips And Tricks You Need To Know

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

Close-The-PivotTable-Fields-Window 101 Advanced Pivot Table Tips And Tricks You Need To Know

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

Show-or-Hide-Field-List-from-Right-Click 101 Advanced Pivot Table Tips And Tricks You Need To Know

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

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

Change-How-The-PivotTable-Fields-Window-Is-Arranged 101 Advanced Pivot Table Tips And Tricks You Need To Know

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

PivotTable-Fields-Window-Options 101 Advanced Pivot Table Tips And Tricks You Need To Know Вы можете выбрать один из пяти доступных вариантов.

  1. Полевой участок и участок с накоплением
  2. Полевая секция и параллельная секция площадей
  3. Только полевая секция
  4. Только участки (2 на 2)
  5. Только участки (1 на 4)

Измените порядок сортировки списка полей

PivotTable-Fields-Default-Sort-Order 101 Advanced Pivot Table Tips And Tricks You Need To Know

Список полей данных по умолчанию будет отображаться в том же порядке, что и исходные данные.Вы можете изменить это, чтобы отображать в алфавитном порядке (от A до Z), если хотите. Щелкните левой кнопкой мыши в меню параметров в окне Поля сводной таблицы, чтобы получить доступ к параметру.

PivotTable-Fields-Sort-A-to-Z 101 Advanced Pivot Table Tips And Tricks You Need To Know

Выберите в меню пункт Сортировать от А до Я . Теперь ваши поля будут отображаться в порядке убывания!

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

Move-Resize-Or-Close-The-PivotTable-Fields-Window 101 Advanced Pivot Table Tips And Tricks You Need To Know

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

  • Переместить — это позволит вам открепить окно и перемещать его по электронной таблице.
  • Размер — позволяет регулировать ширину и высоту (в отстыкованном состоянии) окна.
  • Закрыть — позволяет закрыть окно. Вы можете открыть его снова, выбрав вкладку Analyze > Список полей .

Сочетание клавиш мастера сводных таблиц и диаграмм

Open-PivotTable-And-PivotChart-Wizard-Keyboard-Shortcut 101 Advanced Pivot Table Tips And Tricks You Need To Know

Используйте сочетание клавиш Alt + D + P, чтобы открыть PivotTable and PivotChart Wizard .Это поможет вам настроить сводную таблицу или сводную диаграмму, выбрать данные и место для новой сводной таблицы или диаграммы.

Создание сводной таблицы с помощью сочетания клавиш

Create-Pivot-Table-Keyboard-Shortcut 101 Advanced Pivot Table Tips And Tricks You Need To Know

Используйте сочетание клавиш Alt + N + V на ленте, чтобы быстро создать сводную таблицу.

Показать подробности за значением

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

Right-Click-to-Show-Details 101 Advanced Pivot Table Tips And Tricks You Need To Know

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

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

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

Open-the-Pivot-Table-Options 101 Advanced Pivot Table Tips And Tricks You Need To Know

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

Pivot-Table-Options-Enable-Show-Details 101 Advanced Pivot Table Tips And Tricks You Need To Know

В меню PivotTable Options перейдите на вкладку Data и снимите отметку , Enable show details , чтобы отключить эту функцию.

Заменить пустые ячейки

Pivot-Table-With-Blank-Cells 101 Advanced Pivot Table Tips And Tricks You Need To Know

Эта сводная таблица содержит пустых ячеек , потому что наши исходные данные не содержат записей для этих комбинаций измерений.Например, нет данных для Артура Джеймса и Франции, поэтому пересечение строки Артура Джеймса и столбца Франции остается пустым. Мы можем изменить настройки, чтобы отображать что-то вроде ноль или какой-то текст с надписью « N / A » вместо пустого поля.

Replace-Empty-Cells-In-A-Pivot-Table-Step-1 101 Advanced Pivot Table Tips And Tricks You Need To Know

Щелкните левой кнопкой мыши в любом месте сводной таблицы, затем выберите Параметры сводной таблицы .

Replace-Empty-Cells-In-A-Pivot-Table-For-Empty-Cells-Show 101 Advanced Pivot Table Tips And Tricks You Need To Know

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

  1. Перейдите на вкладку Layout & Format .
  2. Установите флажок Для пустых ячеек покажите поле и введите значение, которое вы хотите отображать для пустых ячеек. В нашем примере мы заменим пустые ячейки на 0.
  3. Нажмите кнопку OK .

Empty-Cells-In-A-Pivot-Table-Replaced-With-Zero 101 Advanced Pivot Table Tips And Tricks You Need To Know

Теперь пустые ячейки заменены нулями .

Показать элементы без данных

Open-Field-Settings-to-Show-Items-with-No-Data 101 Advanced Pivot Table Tips And Tricks You Need To Know

В этом примере мы создали сводную таблицу с Customer Name и Product Sold в области Rows .Обратите внимание, что для каждого покупателя перечислены не все возможные продукты. Перечисляются только те, для которых у нас есть транзакция в наших данных. Мы можем изменить это так, чтобы мы видели все элементы, даже если данных нет.

Щелкните правой кнопкой мыши и выберите в меню Field Settings .

Field-Settings-Show-Items-with-No-Data 101 Advanced Pivot Table Tips And Tricks You Need To Know

Сводные функции сводной таблицы Excel

Contextures

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

Введение

Когда вы добавляете поле в область значений сводной таблицы, 11 различных
для подведения итогов доступны такие функции, как Sum, Count и Average.
данные.

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

pivot table summary functions

Изменение функции сводки

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

  • Если поле
    содержит числа, Сумма будет по умолчанию
  • Если поле
    содержит текст или пустые ячейки, Счетчик будет по умолчанию

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

  1. Щелкните правой кнопкой мыши ячейку в поле «Значение», которое вы хотите изменить.
  2. Во всплывающем меню щелкните Суммировать значения по
  3. Щелкните функцию сводки, которую вы хотите использовать

pivot table summary function select

Итоги и промежуточные итоги

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

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

Изменить несколько полей одновременно

Чтобы изменить сразу несколько полей, вы можете использовать
макрос.Или, если вы купили копию моего PivotPower
Надстройка Premium, просто нажмите «Суммировать все» или выберите другую функцию
в раскрывающемся списке Суммировать все по, и все поля данных будут
изменен на выбранную функцию.

pivot table summary functions

Функция суммы

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

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

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

pivot table source data

С помощью сводной таблицы вы можете быстро увидеть общую сумму по каждому продукту.
что было продано, а общая сумма — 317 — соответствует таблице
общее. go to top

pivot table sum

Макрос функции суммы

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

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

ПРИМЕЧАНИЕ : Если у вас есть копия моего PivotPower
Надстройка Premium, просто нажмите «Суммировать все» или выберите любую функцию в раскрывающемся списке «Заменить ВСЕ на».

 Sub SumAllValueFields ()
  Dim pt As PivotTable
  Dim pf As PivotField
  Dim ws как рабочий лист

  Установить ws = ActiveSheet
  Установите pt = ws.Сводные таблицы (1)
  Application.ScreenUpdating = False

    pt.ManualUpdate = True
    Для каждого pf в pt.DataFields
      pf.Function = xlSum
    Следующий пф
    pt.ManualUpdate = False

  Application.ScreenUpdating = True
  Установите pf = Nothing
  Установите pt = Nothing
  Установить ws = ничего
Концевой переводник 

Функция счета

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

Сводная таблица Количество функций :

Пустые ячейки не учитываются.

Выберите поле для подсчета

Если вы используете функцию Count в сводной таблице, будьте осторожны
какое поле вы используете для подсчета. Например, в сводной таблице
исходные данные, показанные ниже, ячейка C7 в столбце «Кол-во» пуста.

pivot table count

Если вы хотите подсчитать заказ перьев и использовать поле Кол-во,
пустая ячейка (C7) не учитывается.Количество заказов на Ручки
будет отображаться как 1 (сводная таблица слева внизу).

Вместо этого вы можете добавить поле продукта в область значений и
в заказах на ручки будет отображаться счетчик до 2 (сводная таблица справа внизу).
go to top

Функция среднего

Функция Среднее значение суммирует все базовые значения в
Область значений, и она делится на количество значений.Результат
так же, как при использовании функции СРЕДНИЙ на листе для расчета
среднее (среднее) значений.

Пробелы и нули

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

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

pivot table average data

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

Отформатируйте результаты

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

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

pivot table average format

Макс.функция

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

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

pivot table max data

С помощью сводной таблицы вы можете быстро увидеть максимум для каждого продукта
который был продан, и общая сумма — 97, что соответствует листу
максимум. go to top

pivot table max

Минимальная функция

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

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

pivot table min data

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

И на листе, и в сводной таблице пустая ячейка игнорируется
при расчете минимальной суммы. go to top

pivot table min

Функция продукта

Сводная функция Произведение показывает результат умножения
все базовые значения в области значений. Результат тот же
как использование функции ПРОДУКТ на листе для расчета продукта
ценностей.

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

На снимке экрана ниже вы можете увидеть исходные данные сводной таблицы,
с ПРОДУКТОМ, рассчитанным для каждой группы товаров. Внизу
исходных данных — это общий расчет ПРОДУКТА.

pivot table product data

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

Примечание. Excel сохраняет и вычисляет только с 15 значащими цифрами.
точности, поэтому после 15-го символа вы увидите только нули.

pivot table product

Счетчик чисел Функция

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

На снимке экрана ниже вы можете увидеть исходные данные для небольшого
сводная таблица и количество чисел в столбце Кол-во (столбец
C). В ячейке C4 значение 20 вводится как текст, поэтому эта ячейка не
посчитал.

pivot table count num data

Счетчик чисел и счетчик

В сводной таблице, показанной ниже, поле Qty было добавлено дважды.
в область Ценности.В столбце B итоговая функция — Count.
Числа
, а общая сумма — 7.

В столбце C итоговая функция — Счетчик , которая включает
текста, поэтому общая сумма для этого столбца равна 8. go to top

pivot table count num

Функция StdDev и функция StdDevP

Как и функции рабочего листа STDEV.P и STDEV.S, StdDevp и
Сводные функции StdDev вычисляют стандартное отклонение для
базовые данные в области значений.Стандартное отклонение — это мера
насколько значения отличаются от среднего значения.

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

На снимке экрана ниже вы можете увидеть пример источника сводной таблицы
данные, а функция рабочего листа STDEV.P вычисляет стандартные
отклонение для каждого вида продукции.Для папок с файлами есть
большая разница между проданным количеством и стандартным отклонением
высокий — 44,5. Для бумаги разница в количестве намного меньше,
и стандартное отклонение низкое — 4,7.

standard deviation is low

Когда поле Qty добавлено в сводную таблицу, измените сводку
расчет в StdDevp.

change the summary calculation to StdDevp

На снимке экрана ниже видно, что стандартные отклонения
в сводной таблице те же, что были рассчитаны на
рабочий лист.

standard deviations on worksheet

Примечание : Если количество элементов равно единице, # DIV / 0! отображается ошибка
при использовании итоговой функции StdDev, потому что вычитается один
от счета при вычислении стандартного отклонения.

Как рассчитывается стандартное отклонение

Для стандартного отклонения каждое число сравнивается со средним
номеров. Вы можете рассчитать стандартное отклонение по
лист, без использования СТАНДОТКЛОНА.Функция P.

  1. Найдите среднее значение чисел в данных сводной таблицы.
  2. Из каждого числа вычтите среднее значение.
  3. Возвести вычисленную разницу в квадрат для каждого числа
  4. Найдите среднее значение квадрата разности.
  5. Найдите квадратный корень из среднего.

calculate standard deviation

Функция Var и функция Varp

Сводные функции Var и Varp работают как VAR.P и VAR.S
функции рабочего листа, чтобы вычислить дисперсию для базового
данные в области значений, а дисперсия — это мера того, насколько широко
значения отличаются от среднего значения.

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

На снимке экрана ниже показан пример исходных данных сводной таблицы,
с помощью VAR.Функция рабочего листа P, вычисляющая дисперсию для каждого
Тип продукта. Для папок с файлами, для которых существует большая разница
между двумя величинами разница велика — 1980,25. За
Продажи бумаги, есть небольшая разница в количестве, и
дисперсия составляет всего 22,22.

VAR.P worksheet function

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

change the summary calculation to Varp

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

same as worksheet variance

Примечание : Если количество элементов равно единице, # DIV / 0! отображается ошибка
при использовании итоговой функции Var, потому что вычитается единица из
счет при вычислении дисперсии.

Как рассчитывается отклонение

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

  1. Найдите среднее значение чисел в данных сводной таблицы.
  2. Из каждого числа вычтите среднее значение.
  3. Возвести вычисленную разницу в квадрат для каждого числа
  4. Найдите среднее значение квадрата разности.

calculate variance

Отличный счет

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

В обычной сводной таблице нет встроенной функции «Уникальный счетчик», но расчет отдельного счетчика доступен только для сводных таблиц на основе OLAP , таких как Power Pivot, или сводных таблиц на основе модели данных . Чтобы узнать о других способах получения точного подсчета (уникального количества), см. Страницу «Подсчет уникальных элементов».

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

ПРИМЕЧАНИЕ. Ниже видео описаны шаги для модели данных.

Пример данных

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

source data table

Сводная таблица — модель данных

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

Это создает сводную таблицу на основе OLAP вместо обычной сводной таблицы.

Add this data to the Data Model

Список цветов

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

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

Покажи отчетливый счет

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

  • Щелкните правой кнопкой мыши одно из значений в поле «Количество цветов»
  • Щелкните Параметры поля значений

  • Прокрутите список «Суммировать значение поля по по» вниз и нажмите «Различное количество», затем нажмите «ОК».

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

Если вам не нужно видеть названия цветов,

Ошибки в исходных данных

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

В этом примере поле «Итого» содержит #VALUE! ошибка и # DIV / 0
ошибка, одна пустая ячейка (E7) и одна ячейка с текстом (E9).

Errors in Source Data

Ошибки с подсчетом и подсчетом чисел

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

  • Подсчет чисел : пустые ячейки, ошибки и текст не учитываются.
  • Счетчик : подсчитываются текст, числа и ошибки. Пустые ячейки
    не засчитывается.

Errors with Count and Count Numbers

Ошибки с другими функциями сводки

Для всех остальных сводных функций, если в исходных данных есть ошибки.
поле:

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

В данных # ЗНАЧЕНИЕ! это первая ошибка в списке, поэтому она появляется в
сводная таблица.

Errors with Other Summary Functions

Однако, если вы отсортируете данные с самыми последними датами вверху, # DIV / 0! ошибка первая. Затем обновите сводную таблицу, и она покажет # DIV / 0! ошибка.

Errors with Other Summary Functions

Итоги и промежуточные итоги

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

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

  • другие сводные функции включены в сводку
    таблица, и эти поля содержат ошибки в данных
  • Поля Count и Count Number содержат ошибки в данных

Например, на снимке экрана ниже добавлено поле «Среднее значение для цены» и
это поле содержит # DIV / 0! ошибка.В итоге:

  • Подсчет чисел и общие итоги подсчета показывают # ЗНАЧЕНИЕ! ошибка, потому что они основаны на поле Всего, которое содержит ошибки в данных
  • Однако общий итог «Число дат» верен, поскольку поле «Дата» не содержит ошибок в данных.

Totals and Subtotals

Загрузить рабочие тетради

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

Чтобы сэкономить время при создании, форматировании и изменении сводных таблиц, используйте инструменты в моей надстройке Pivot Power Premium.Всего за несколько кликов вы можете:

  • скопируйте форматирование из одной сводной таблицы и примените его к другой сводной таблице.
  • изменить все значения с Count на Sum
  • удалить «Сумма» из всех заголовков.

и многое другое!

Дополнительные уроки

Слайсеры Value Group

Значения ошибок Excel

Группировка данных

Промежуточные итоги

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

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

Contextures

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

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

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

  • по этикеткам, A-Z или Z-A
  • одним из полей значения, от наибольшего к наименьшему или от наименьшего к наибольшему.

Однако структура сводной таблицы накладывает ограничения на то, что будет отсортировано:

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

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

Для быстрой сортировки элементов сводного поля по алфавиту:

  • Щелкните любой элемент сводной таблицы в поле сводной таблицы, чтобы выбрать эту ячейку
  • На вкладке «Данные» ленты Excel щелкните команду A – Z или команду Z – A

На снимке экрана ниже поле Продукт находится в области строки, а название продукта выбрано в ячейке A5.При нажатии кнопки Z-A названия продуктов сортируются в порядке убывания.

Pivot Table Items

Сортировать по значению

Для быстрой сортировки элементов сводного поля по полю значения:

  • Щелкните значение любого элемента сводной таблицы, чтобы выбрать эту ячейку
  • На вкладке «Данные» ленты Excel щелкните команду A – Z или команду Z – A

На снимке экрана ниже поле «Продукт» находится в области строки, а значение в поле «Заказы» выбрано в ячейке C4.При нажатии кнопки A – Z значения заказов сортируются в порядке возрастания.

Pivot Table Items

с поворотным полем влево

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

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

На приведенном ниже снимке экрана поле «Продукт» находится в области строк, а поле «Категория» — слева от него как внешнее поле.

Количество продукта в ячейке C5 выбрано, и при нажатии кнопки A – Z значения Qty сортируются в порядке возрастания — в каждой категории .

Pivot Table Items

Независимая сортировка

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

На приведенном ниже снимке экрана количество категорий в ячейке C4 выбрано и отсортировано по Z-A (от наибольшего к наименьшему). Это перемещает файлы cookie в верхнюю часть списка, но продукты в каждой категории по-прежнему отсортированы от A до Z (от наименьшего к наибольшему)

Pivot Table Items

Новые элементы сводной таблицы
Вышел из строя

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

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

.

Новые элементы сводной таблицы вышли из строя

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

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

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

Pivot Table Items

Исправить порядок сортировки go to top

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

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

  1. Щелкните правой кнопкой мыши ячейку в поле «Продукт». Например, щелкните правой кнопкой мыши
    ячейка «Конверты».
  2. Щелкните «Сортировка», а затем щелкните «Сортировка от А до Я».

Sort A to Z

Когда вы сортируете поле Product, его настройка сортировки меняется с Manual
по Сортировка по возрастанию или Сортировка по убыванию .

Это также сортирует раскрывающийся список и упрощает работу для пользователей.
чтобы найти нужные им предметы. go to top

Видео: сортировка элементов фильтра отчета

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

Сортировка элементов фильтра отчета

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

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

В моей надстройке Pivot Power Premium также есть команда «Сортировать фильтры отчета».

Новые позиции в конце списка

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

Исправить порядок сортировки фильтра отчетов

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

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

  1. Перетащите поле «Продукт» в область «Строки» над любой другой строкой.
    поля.
  2. Щелкните правой кнопкой мыши ячейку в поле «Продукт». Например, щелкните правой кнопкой мыши
    ячейка «Конверты».
  3. Щелкните «Сортировка», а затем щелкните «Сортировка от А до Я».
  4. Перетащите поле «Продукт» обратно в область «Фильтр отчетов».

Sort A to Z

Когда вы сортируете поле Product, его настройка сортировки меняется с Manual
по Сортировка по возрастанию или Сортировка по убыванию .

Это также сортирует раскрывающийся список и упрощает работу для пользователей.
чтобы найти нужные им предметы. go to top

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

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

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

Для сортировки столбца сводной таблицы:

  1. Щелкните правой кнопкой мыши ячейку значения и выберите «Сортировать».
  2. Затем нажмите Сортировать от наименьшего к наибольшему или Сортировка от наибольшего к наименьшему.

Sort Z to A

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

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

Например, в приведенной ниже сводной таблице столбцы городов
в алфавитном порядке, слева — Бостон.

Rows Alphabetical

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

Для сортировки строки сводной таблицы:

  1. В сводной таблице щелкните правой кнопкой мыши ячейку значения в
    Чиповый ряд.
  2. Щелкните «Сортировка», затем щелкните «Дополнительные параметры сортировки».

More Sort Options

  1. В диалоговом окне «Сортировка по значению» в разделе «Параметры сортировки» выберите «Наибольший».
    на самый маленький.
  2. В разделе Направление сортировки выберите Слева направо. В разделе «Сводка»
    вы можете увидеть описание настроек сортировки.

Sort by Value

  1. Нажмите ОК, чтобы закрыть диалоговое окно.

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

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

pivot table row sorted

ПРИМЕЧАНИЕ : Строки для других товаров не могут быть в порядке убывания,
потому что порядок столбцов был установлен значениями в шоколаде
Чиповый ряд.

Сортировать сводное поле в произвольном порядке

В большинстве случаев вы будете сортировать сводную таблицу

  • в алфавитном порядке на основе меток строк,
  • или по стоимости.

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

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

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

Применение пользовательского порядка сортировки

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

  1. Обновите сводную таблицу.
  2. Если для поля, содержащего элементы из настраиваемого списка, установлено значение Автоматически
    sort он должен измениться на порядок сортировки настраиваемого списка.
  3. Если поле в настоящее время настроено для ручной сортировки, сортировка не выполняется.
    в соответствии с заказным списком. Чтобы изменить его на автоматическую сортировку,
    щелкните правой кнопкой мыши название города, выберите «Сортировка», а затем нажмите «Сортировка от А до Я».

Запрет сортировки пользовательского порядка в сводной таблице

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

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

  1. Щелкните правой кнопкой мыши ячейку в сводной таблице и выберите Параметры сводной таблицы.
  2. В диалоговом окне Параметры сводной таблицы щелкните Итоги и фильтры
    таб.
  3. В разделе «Сортировка» снимите флажок «Использовать настраиваемый
    Списки при сортировке «

ПРИМЕЧАНИЕ. Этот параметр будет применяться ко всем полям в сводной таблице.

Сортировка сводных полей с помощью макроса

Следующий макрос изменит настройку Сортировки на По возрастанию (A-Z),
для всех полей строк и столбцов, во всех сводных таблицах, на всех листах, в активных
рабочая тетрадь. Порядок сортировки основан на именах элементов, поэтому «Восток» будет стоять перед «Западом».

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

Sub AutoSortAZAllFields ()
Dim ws как рабочий лист
Dim pt As PivotTable
Dim pf As PivotField
    
При ошибке Возобновить Далее
Для каждой страницы в ActiveWorkbook.Рабочие листы
  Для каждой точки в ws.PivotTables
    Для каждого pf в pt.RowFields
      pf.AutoSort xlAscending, pf.SourceName
    Следующий пф
    Для каждого pf в pt.ColumnFields
      pf.AutoSort xlAscending, pf.SourceName
    Следующий пф
  Следующая пт
Следующий ws

Концевой переводник 

Сортировка сводных полей по значениям — макросы

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

Сортировать по определенному полю значения

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

  • Первый макрос сортирует от наибольшего к наименьшему (Z-A)
  • Второй макрос сортирует от наименьшего к наибольшему (A – Z)

ПРИМЕЧАНИЕ : Перед запуском макросов выберите любую ячейку в сводной таблице , которую вы хотите отсортировать.

 Sub SortAllRowFields_ZA ()
Dim pt As PivotTable
Dim pf As PivotField
Dim strVal как строка
strVal = "Итого"

При ошибке Возобновить Далее
Установите pt = ActiveCell.PivotTable
Если pt равно Nothing, выйдите из Sub

За каждый пф в пт.RowFields
  pf.AutoSort xlDescending, strVal
Следующий пф

Конец подписки
'===========================
Sub SortAllRowFields_AZ ()
Dim pt As PivotTable
Dim pf As PivotField
Dim strVal как строка
strVal = "Итого"

При ошибке Возобновить Далее
Установите pt = ActiveCell.PivotTable
Если pt равно Nothing, выйдите из Sub

Для каждого pf в pt.RowFields
  pf.AutoSort xlAscending, strVal
Следующий пф

Концевой переводник 

Сортировать по полю выбранного значения — нормальная точка поворота

Следующие 2 макроса сортируют все поля строк в выбранной сводной таблице на основе значений в выбранном поле значения.(Для сводных таблиц на основе модели данных перейдите к следующему разделу)

  • Первый макрос сортирует от наибольшего к наименьшему (Z-A)
  • Второй макрос сортирует от наименьшего к наибольшему (A – Z)

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

ПРИМЕЧАНИЕ : Перед запуском макроса выберите любую сумму в столбце «Значение» , по которой вы хотите выполнить сортировку.

 Sub SortAllRowFields_ZASelVal ()
'выберите поле Значение, которое
'сортировка будет основана на
Dim pt As PivotTable
Dim pf As PivotField
Dim df As PivotField
Dim strVal как строка

При ошибке Возобновить Далее
Установите pt = ActiveCell.Сводная таблица
Если pt равно Nothing, выйдите из Sub
Установите df = ActiveCell.PivotField

Если df.Orientation <> xlDataField Тогда
  MsgBox "Пожалуйста, выберите поле значений"
  Выход из подводной лодки
Еще
  strVal = df.Caption
Конец, если

Для каждого pf в pt.RowFields
  pf.AutoSort xlDescending, strVal
Следующий пф

MsgBox "Поля строк были отсортированы от Я до А" _
  & vbCrLf _
  & "на основе поля значения:" _
  & vbCrLf _
  & strVal

Конец подписки
'===========================
Sub SortAllRowFields_AZSelVal ()
'выберите поле Значение, которое
'сортировка будет основана на
Dim pt As PivotTable
Dim pf As PivotField
Dim df As PivotField
Dim strVal как строка

При ошибке Возобновить Далее
Установите pt = ActiveCell.Сводная таблица
Если pt равно Nothing, выйдите из Sub
Установите df = ActiveCell.PivotField

Если df.Orientation <> xlDataField Тогда
  MsgBox "Пожалуйста, выберите поле значений"
  Выход из подводной лодки
Еще
  strVal = df.Caption
Конец, если

Для каждого pf в pt.RowFields
  pf.AutoSort xlAscending, strVal
Следующий пф

MsgBox "Поля строк отсортированы от А до Я" _
  & vbCrLf _
  & "на основе поля значения:" _
  & vbCrLf _
  & strVal

Концевой переводник 

Сортировка по полю выбранного значения — Нормальный или DM

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

  • Первый макрос сортирует от наибольшего к наименьшему (Z-A)
  • Второй макрос сортирует от наименьшего к наибольшему (A – Z)

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

ПРИМЕЧАНИЕ : Перед запуском макроса выберите любую сумму в столбце «Значение» , по которой вы хотите выполнить сортировку.

 Sub SortAllRowFieldsDM_ZASelVal ()
'выберите поле Значение, которое
'сортировка будет основана на
'для обычных PT или PT
Dim pt As PivotTable
Dim pf As PivotField
Dim df As PivotField
Dim strVal как строка

При ошибке Возобновить Далее
Установите pt = ActiveCell.Сводная таблица
Если pt равно Nothing, выйдите из Sub
Установите df = ActiveCell.PivotField

Если df.Orientation <> xlDataField Тогда
  MsgBox "Пожалуйста, выберите поле значений"
  Выход из подводной лодки
Еще
  Если pt.PivotCache.OLAP = True, то
    strVal = df.Name
  Еще
    strVal = df.Caption
  Конец, если
Конец, если

Для каждого pf в pt.RowFields
  pf.AutoSort xlDescending, strVal
Следующий пф

MsgBox "Поля строк были отсортированы от Я до А" _
  & vbCrLf _
  & "на основе поля значения:" _
  & vbCrLf _
  & strVal

Конец подписки
'===========================
Sub SortAllRowFieldsDM_AZSelVal ()
'выберите поле Значение, которое
'сортировка будет основана на
'для обычных PT или PT
Dim pt As PivotTable
Dim pf As PivotField
Dim df As PivotField
Dim strVal как строка

При ошибке Возобновить Далее
Установите pt = ActiveCell.Сводная таблица
Если pt равно Nothing, выйдите из Sub
Установите df = ActiveCell.PivotField

Если df.Orientation <> xlDataField Тогда
  MsgBox "Пожалуйста, выберите поле значений"
  Выход из подводной лодки
Еще
  Если pt.PivotCache.OLAP = True, то
    strVal = df.Name
  Еще
    strVal = df.Caption
  Конец, если
Конец, если

Для каждого pf в pt.RowFields
  pf.AutoSort xlAscending, strVal
Следующий пф

MsgBox "Поля строк отсортированы от А до Я" _
  & vbCrLf _
  & "на основе поля значения:" _
  & vbCrLf _
  & strVal

Концевой переводник 

Сортировка сводных элементов на основе списка

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

  • Если список сортировки содержит элементы, которых нет в сводном поле, или
    не отображаются в сводной таблице, эти элементы будут проигнорированы.
  • Любые элементы, которых нет в списке сортировки, но находятся в сводном поле,
    опустится до конца отсортированных сводных элементов.
Sub SortPT ()
Dim rngSort As Range
Dim c As Диапазон
Dim pt As PivotTable
Dim pf As PivotField
Dim pi As PivotItem
Dim lCount As Long
При ошибке Возобновить Далее
Заявка.EnableEvents = False

Установить rngSort = Sheets ("Заказы") _
    .Range ("ProdList")
Установить pt = Worksheets ("OrdersPT") _
    .PivotTables ("ptOrders")
Установите pf = pt.PivotFields ("Продукт")
lCount = 1

pt.ManualUpdate = True
С pf
  .AutoSort xlManual, pf.SourceName
  Для каждого c в rngSort
    Установите pi = Nothing
    Установите pi = .PivotItems (c.Value)
      Если не пи, то ничто
        Если pi.Visible = True, то
          pi.Position = lCount
          lCount = lCount + 1
        Конец, если
      Конец, если
  Следующий c
Конец с

пт.ManualUpdate = False
pt.RefreshTable
Application.EnableEvents = True
Концевой переводник 

Скачать образец файла

  1. Чтобы проверить приемы этого руководства, вы можете скачать
    файл примера сортировки сводной таблицы. Заархивированный файл имеет формат xlsx и НЕТ
    макросы.
  2. Чтобы протестировать сортировку на основе макроса значения , загрузите
    файл «Сортировка по значениям». Заархивированный файл имеет формат xlsm и
    содержит макросы с этой страницы.
  3. Чтобы протестировать сортировку на основе макроса списка , вы можете загрузить
    Сортировка по файлу списка. Заархивированный файл имеет формат xlsm и
    содержит макросы.

Дополнительные уроки

Перемещение сводных элементов вручную

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

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

.

Добавить комментарий

Ваш адрес email не будет опубликован. Обязательные поля помечены *