Pivot table: Pivot Tables in Excel — Easy Excel Tutorial
Создание первой сводной таблицы
Исходные данные
Сейчас на простом примере мы научимся создавать сводные таблицы и познакомимся с некоторыми их возможностями.
Для примера в качестве исходных данных возьмем ведомость основных средств компании и определим стоимость позиций по каждому филиалу с помощью сводной таблицы.
Перед созданием сводной таблицы убедитесь, что в исходной таблице отсутствуют пустые заголовки. Это требуется потому что каждый столбец таблицы становиться полем сводной таблицы, по которому можно собрать данные.
Советую также преобразовывать исходный диапазон данных в таблицу (Главная — Форматировать как таблицу). Тогда при добавлении или удалении строк и столбцов не придется менять ссылку на этот диапазон в сводном отчете.
Рекомендуемые сводные таблицы
Пользователи Excel 2013 могут выбрать сводную таблицу из макета на основе рекомендаций, которые предлагает Excel. Вот как это делается:
Выберите любую ячейку исходной таблицы.
-
Перейдите на вкладку ленты Вставка [Insert] и найдите группу Таблицы [Table].
-
Выберите команду Рекомендуемые сводные таблицы [Recommended PivotTables].
-
Выберите подходящую таблицу в списке слева. После выборы в окне справа вы увидите макет будущей таблицы.
Самостоятельное создание сводной таблицы с помощью конструктора
Для тех, у кого нет Excel 2013, или же если нужной сводной таблицы нет в предложенных, можно создать ее с нуля с помощью конструктора. Для этого:
Выберите любую ячейку исходной таблицы.
-
Перейдите на вкладку ленты Вставка [Insert] и найдите группу Таблицы [Table].
-
Выберите команду Сводная таблица [PivotTable].
-
В диалоговом окне Создание сводной таблицы [Create PivotTable] убедитесь в правильности диапазона данных, на основе которого будет строиться отчет. Если диапазон некорректный, его нужно поменять в поле Таблица и диапазон [Table/Range].
Важно! При выделении диапазона убедитесь, что выбрана таблица с заголовками, а так же что выбранный диапазон не включает строку итогов.
Так же укажите, где необходимо разместить отчет сводной таблицы: на новом листе или на уже существующий.
Советую размещать каждый отчет сводной таблицы на отдельном листе. Это поможет избежать ошибок в случае если будут добавлять данные.
-
После формирования сводной таблицы в выбранном месте появиться область с ее названием. По умолчанию отчет называется СводнаяТаблица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. Концепция Времени и Денег
Для получения автоматического финансового анализа выберите колонку времени и денег. Например, сумма за текущий и базовый периоды вместе с диаграммами будут созданы в один клик.
Шаг 3. Получите анализ в 1 клик
В таблице показаны значения суммы, количества и тенденции по сумме по каждой ячейке. Ниже дана диаграмма продаж за последнюю неделю.
Кликайте по ячейкам и колонкам
Кликните по ячейке или колонке, чтобы получить детализацию данных и график именно по этому параметру. Пример показывает анализ продаж сыра.
Настройки показателей
Выберите те показатели, которые нужны именно Вам. Для каждой части интерфейса программы можно назначить свой список показателей.
Автоматическая группировка данных
Все ячейки отсортированы по сумме, поэтому наиболее важные находятся вверху, а менее важные – ниже или в группе «Другие».
Настройка группы «Другие»
Помещайте ячейки в «Другие» и извлекайте их оттуда, перемещая сплиттер вверх-вниз. Изменения отобразятся как в таблице, так и на графике.
Показатели
Программа рассчитывает для каждой ячейки все наиболее популярные показатели: сумму, количество, среднее, долю – отдельно для каждого периода.
Круговая диаграмма распределения
Кликните по названию колонки, чтобы увидеть диаграмму долей либо по сумме, либо по количеству.
Множественный график
Сравнивайте динамику по нескольким категориям при помощи наложения графиков.
Вкладка «Быстрая сводная таблица» и диаграмма сравнения периодов
Нажмите на вкладку «Быстрая сводная таблица», чтобы получить таблицу показателей, которую Вы сможете скопировать в MS Excel. Ниже располагается диаграмма сравнения продаж за текущую неделю с аналогичными днями предыдущей недели.
Перерасчёт таблицы для г. Москва
Установите фильтр и изучайте влияние отдельных параметров в пределах фильтра. Диаграмма показывает изменение доли продаж белого хлеба по неделям только для г. Москва (фильтр).
Автоматическое разбиение цен и других численных показателей на кластеры
Анализируйте влияние цены или иных численных параметров, благодаря автоматическому разбиению их на группы.
Автоматический отчёт в Excel
Настраиваемый отчёт в Excel обеспечивает создание полного финансового отчёта автоматически. Каждый параметр анализируется на своём листе с диаграммами и сводными таблицами.
Гибкая настройка заголовков и данных
Приложение автоматически определяет заголовки и данные. Кроме того, Вы можете их настроить самостоятельно. Программа поддерживает объединенные ячейки как в заголовке, так и в данных.
Автоматическая загрузка приложения: 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 полей. Идентификатор заказа, продукт, категория, сумма, дата и страна.
Вставить сводную таблицу
Чтобы вставить сводную таблицу , выполните следующие шаги.
1. Щелкните любую ячейку в наборе данных.
2. На вкладке Вставка в группе Таблицы щелкните Сводная таблица.
Появится следующее диалоговое окно. Excel автоматически выбирает данные за вас.Местоположение по умолчанию для новой сводной таблицы — Новый рабочий лист.
3. Щелкните OK.
Перетащите поля
Появится панель полей сводной таблицы . Чтобы получить общий объем экспорта каждого продукта, перетащите следующие поля в разные области.
1. Поле продукта в области Строки.
2. Поле количества в области значений.
3.Поле Страна в область Фильтры.
Ниже представлена сводная таблица. Бананы — наш основной экспортный продукт. Вот насколько простыми могут быть сводные таблицы!
Сортировка
Чтобы банан находился в верхней части списка, отсортируйте сводную таблицу.
1. Щелкните любую ячейку в столбце «Сумма суммы».
2. Щелкните правой кнопкой мыши и выберите «Сортировка, сортировка от наибольшего к наименьшему».
Результат.
Фильтр
Поскольку мы добавили поле «Страна» в область «Фильтры», мы можем фильтровать эту сводную таблицу по стране. Например, какие товары мы больше всего экспортируем во Францию?
1. Щелкните раскрывающийся список фильтров и выберите Франция.
Результат. Яблоки — наш основной экспортный продукт во Францию.
Примечание. Вы можете использовать стандартный фильтр (треугольник рядом с ярлыками строк), чтобы отображать только количество определенных продуктов.
Изменить итоговый расчет
По умолчанию Excel суммирует ваши данные путем суммирования или подсчета элементов. Чтобы изменить тип расчета, который вы хотите использовать, выполните следующие шаги.
1. Щелкните любую ячейку в столбце «Сумма суммы».
2. Щелкните правой кнопкой мыши и выберите Параметры поля значений.
3. Выберите тип расчета, который вы хотите использовать.Например, нажмите «Подсчитать».
4. Щелкните OK.
Результат. 16 из 28 заказов во Францию были заказами Apple.
Двумерная сводная таблица
Если перетащить поле в область «Строки» и «Столбцы», можно создать двумерную сводную таблицу. Сначала вставьте сводную таблицу. Затем, чтобы получить общий объем экспорта каждого продукта в каждую страну, перетащите следующие поля в разные области.
1. Из поля Country в область Rows.
2. Перейдите в поле «Продукт» в область «Столбцы».
3. Поле количества в области значений.
4. Поле Категория в области Фильтры.
Ниже представлена двухмерная сводная таблица.
Чтобы легко сравнить эти числа, создайте сводную диаграмму и примените фильтр.Возможно, на данном этапе для вас это слишком далеко, но он показывает вам одну из многих других мощных функций сводных таблиц, которые может предложить Excel.
Сводная диаграмма
в Excel — Easy Excel Tutorial
Вставить сводную диаграмму | Сводная диаграмма фильтров | Изменить тип сводной диаграммы
Сводная диаграмма — это визуальное представление сводной таблицы в Excel . Сводные диаграммы и сводные таблицы связаны друг с другом.
Ниже представлена двухмерная сводная таблица. Вернитесь к сводным таблицам, чтобы узнать, как создать эту сводную таблицу.
Вставить сводную диаграмму
Чтобы вставить сводную диаграмму, выполните следующие действия.
1. Щелкните любую ячейку в сводной таблице.
2. На вкладке «Анализ» в группе «Инструменты» щелкните «Сводная диаграмма».
Появится диалоговое окно «Вставить диаграмму».
3. Щелкните OK.
Ниже представлена сводная диаграмма. Эта сводная диаграмма поразит и впечатлит вашего начальника.
Примечание: любые изменения, которые вы вносите в сводную диаграмму, немедленно отражаются в сводной таблице и наоборот.
Сводная диаграмма фильтров
Чтобы отфильтровать эту сводную диаграмму, выполните следующие действия.
1.Используйте стандартные фильтры (треугольники рядом с товаром и страной). Например, используйте фильтр «Страна», чтобы показать только общую сумму каждого продукта, экспортированного в США.
2. Снимите фильтр страны.
3. Поскольку мы добавили поле «Категория» в область «Фильтры», мы можем фильтровать эту сводную диаграмму (и сводную таблицу) по категориям. Например, используйте фильтр «Категория», чтобы отображать только овощи, экспортируемые в каждую страну.
Изменить тип сводной диаграммы
Вы можете в любой момент перейти на другой тип сводной диаграммы.
1. Выберите диаграмму.
2. На вкладке «Дизайн» в группе «Тип» щелкните «Изменить тип диаграммы».
3. Выберите «Пирог».
4. Щелкните OK.
Результат:
Примечание: круговые диаграммы всегда используют одну серию данных (в данном случае Beans).Чтобы получить сводную диаграмму страны, поменяйте местами данные по оси. Сначала выберите диаграмму. Затем на вкладке «Дизайн» в группе «Данные» нажмите «Переключить строку / столбец».
Работа со сводными таблицами в Excel на примерах
Пользователи создают сводные таблицы для анализа, обобщения и представления больших объемов данных. Этот инструмент Excel позволяет фильтровать и группировать информацию, а также отображать ее в различных аспектах (составлять отчет).
Источник данных сводной таблицы
включает таблицу с несколькими десятками и сотнями строк, несколько таблиц в одной книге, несколько файлов.Давайте пересмотрим порядок создания сводной таблицы: «ВСТАВИТЬ» — «Таблицы» — «Сводные таблицы».
В этой статье мы узнаем, как работать со сводными таблицами в Excel.
Взаимодействие с другими людьми
Как создать сводную таблицу из нескольких файлов
Первый шаг — перенос информации в Excel и преобразование ее в таблицы Excel. Если наши данные в Word, то переносим их в Excel и составляем таблицу по всем правилам Excel (даем заголовки столбцам, удаляем пустые строки и т. Д.).
Дальнейшая работа по созданию сводной таблицы из нескольких файлов будет зависеть от типа данных.Если вы работаете с однотипными данными (таблиц несколько, но заголовки одинаковые), вам поможет Конструктор сводных таблиц.
Мы просто создаем консолидированный отчет (отчет сводной таблицы) на основе данных в нескольких диапазонах консолидации.
Намного сложнее создать сводную таблицу на основе исходных таблиц, имеющих различную структуру. Например:
Первая таблица «входящие» представляет поступление товаров. Второй «торговый» показывает количество единиц, проданных в разных магазинах.Нам нужно объединить эти две таблицы в один отчет, чтобы проиллюстрировать остатки, продажи, выручку и т. Д.
Мастер сводных таблиц выдает ошибку с этими начальными параметрами, так как нарушается одно из основных условий консолидации (те же имена столбцов).
Но два заголовка в этих таблицах идентичны. Таким образом, мы можем объединить данные, а затем создать сводный отчет.
- Поместите курсор в целевую ячейку (куда будет перемещена таблица). Write = — перейти к листу с переданными данными — выбрать первую ячейку столбца, который копируется.Нажмите Ввод. «Умножьте» формулу, потянув ее за правый нижний угол ячейки.
- Остальные данные передаем по такому же принципу. В результате мы получаем одну сводную таблицу из двух таблиц.
- Теперь создайте отчет сводной таблицы. ВСТАВИТЬ — Сводные таблицы — укажите диапазон и расположение — ОК.
Открывается пустой сводный отчет с отображаемыми полями. Покажем, например, количество проданного товара.
Вы можете выводить различные параметры для анализа и перемещать поля.Но работа со сводными таблицами в Excel на этом не заканчивается: возможности этого инструмента широки.
Взаимодействие с другими людьми
Детализация информации в сводных таблицах
Из отчета (см. Выше) видно, что ВСЕГО продано 30 видеокарт. Чтобы узнать, какие данные использовались для получения этого значения, дважды щелкните число «30». Получить подробный отчет:
Как обновить данные в сводной таблице Excel?
Если вы измените какой-либо параметр в исходной таблице или добавите новую запись, эта информация не будет отображаться в отчете сводной таблицы.Эта ситуация нас не устраивает. Выбираем «Сводная таблица» — «АНАЛИЗ» — «Обновить» (ALT + F5). Курсор должен находиться в любой ячейке главного отчета.
Обновление данных:
Или щелкните правой кнопкой мыши — обновите.
Для настройки автоматического обновления сводной таблицы при изменении данных делаем следующее:
- Курсор находится в любом месте отчета. «ИНСТРУМЕНТ Сводных таблиц» — «АНАЛИЗ» — «Сводная таблица» — «Параметры».
- В открывшемся диалоге — Данные — Обновить данные при открытии файла — ОК.
Изменение структуры отчета
Добавить новые поля в сводную таблицу:
- Вставьте столбец «Продажи» на лист с исходными данными. Здесь мы отразим, какую выручку магазин получит от продажи товара. Воспользуемся формулой — цена (F2) * количество проданных единиц (I2).
- Перейти к листу с отчетом. «ИНСТРУМЕНТ Сводной таблицы» — «АНАЛИЗ» — «Изменить источник данных». Расширить круг информации, которая должна войти в сводную таблицу.
Если мы добавили столбцы внутри исходной таблицы, этого было достаточно для обновления сводной таблицы.
После изменения диапазона в сводке появилось поле «Продажи»
Как добавить вычисляемое поле в сводную таблицу?
Иногда данных в сводной таблице недостаточно для пользователя. Менять исходную информацию не имеет смысла. В таких ситуациях лучше добавить вычисляемое поле.
Это виртуальный столбец, созданный в результате вычислений.Может отображать средние значения, проценты, расхождения, то есть результаты разных формул. Данные вычисляемого поля взаимодействуют с данными сводной таблицы.
Инструкция по добавлению вычисляемого поля:
- Определите, какие функции будет выполнять цифровой столбец. Определите данные в сводной таблице, на которые должно ссылаться вычисляемое поле. Допустим, вам нужны остатки по группам товаров.
- «ИНСТРУМЕНТ Сводной таблицы» — «АНАЛИЗ» — «Поля, элементы и наборы» — «Вычисляемое поле».
- Введите название поля в открывшемся меню. Установите курсор в строку «Формула». Инструмент «Расчетное поле» не реагирует на диапазоны. Поэтому выбирать ячейки в сводной таблице бессмысленно. Выберите из предложенного списка категории, которые необходимы для расчета. Затем нажмите «Вставить поле». Дополните формулу необходимыми арифметическими операциями.
- Нажмите ОК. Появились остатки.
Группировка данных в консолидированном отчете
Например, посчитаем расходы на товары в разные годы: сколько денег было потрачено в 2012, 2013, 2014 и 2015 годах? Группировка по дате в сводной таблице Excel выполняется следующим образом.Например, сделаем простую сводку по дате доставки и цене.
Щелкните любую дату правой кнопкой мыши. Выберите команду «Группировать».
В открывшемся диалоге укажите параметры группировки. Дата начала и окончания диапазона отображаются автоматически. Выберите значение шага «Годы».
Вы получите количество заказов по годам.
Скачать рабочий пример
По такой же схеме можно сгруппировать данные в сводной таблице по другим параметрам.
101 Продвинутые советы и рекомендации по работе с сводными таблицами, которые необходимо знать
Сводные таблицы — это круто! Это одна из самых мощных функций Excel. , они позволяют быстро суммировать большие объемы данных за считанные секунды. Эта коллекция из удивительных советов и приемов поможет вам освоить сводные таблицы и стать ниндзя данных!
Вы узнаете все советы, которые используют профессионалы, так что приготовьтесь к очень-очень длинному посту!
Загрузите файл-пример с данными, использованными в этом посте, чтобы продолжить.
Видеоурок: 25 лучших советов и приемов для расширенных сводных таблиц
Ваши исходные данные должны быть в табличном формате
При использовании сводной таблицы исходные данные должны быть в табличном формате . Это означает, что ваши данные находятся в таблице со строками и столбцами.
- Первая строка должна содержать заголовки столбцов , которые описывают данные непосредственно под этим столбцом. В ваших данных не должно быть пустых заголовков столбцов.
- Каждая строка после заголовков столбцов должна относиться к ровно одной записи в ваших данных. Например, если ваша таблица содержит данные о клиентах, то каждая строка может содержать имя, почтовый адрес, почтовый индекс и адрес электронной почты только для одного клиента.
Используйте таблицу для исходных данных
При создании сводной таблицы обычно рекомендуется преобразовать данные в таблицу Excel . При добавлении новых строк или столбцов к исходным данным вам не нужно обновлять ссылку на диапазон в сводных таблицах, если ваши данные находятся в таблице.
Без таблицы ссылка на диапазон будет выглядеть примерно так, как указано выше. В этом примере, если бы мы добавляли данные после строки 51 или столбца I, наша сводная таблица не включала бы их в результаты.
Чтобы создать и назвать вашу таблицу.
- Выберите ваши данные.
- Перейдите на вкладку Insert и нажмите кнопку Table в разделе Tables или используйте сочетание клавиш Ctrl + T.
- Нажмите кнопку OK .
- С активной ячейкой внутри таблицы перейдите на вкладку Table Tools Design .
- Измените Имя таблицы в разделе Свойства и нажмите Введите .
Теперь при создании сводной таблицы можно ссылаться на нее с именем вместо диапазона. Когда вы добавляете данные в таблицу, вам не нужно обновлять диапазон в сводной таблице. Просто обновите его, и новые данные появятся в ваших результатах.
Изменить исходные данные
Хорошо, если вы по какой-то причине решите не использовать таблицу, вам придется обновлять диапазон при добавлении любых новых строк или столбцов за пределами исходного выбранного диапазона.
Выберите сводную таблицу, перейдите на вкладку Analyze и нажмите кнопку Изменить источник данных , затем выберите в меню Изменить источник данных . Обновите свой диапазон соответствующим образом в следующем всплывающем диалоговом окне Изменить источник данных сводной таблицы .
Отстыковать окно полей сводной таблицы
От
до открепите окно «Поля сводной таблицы». панель наведите указатель мыши на заголовок, пока он не превратится в четырехстороннюю стрелку, затем щелкните правой кнопкой мыши и перетащите в нужное место. Вы можете либо оставить его плавающим где-нибудь в электронной таблице, либо прикрепить его к левой стороне , перетащив его к самому левому краю.
Быстро закрепите окно полей сводной таблицы
Для быстрого закрепления окна «Поля сводной таблицы» наведите указатель мыши на заголовок, пока он не превратится в четырехстороннюю стрелку, затем дважды щелкните правой кнопкой мыши .Он будет пристыкован к последнему пристыкованному месту (справа или слева).
Скрыть или отобразить окно полей сводной таблицы
Вы можете получить больше места на экране, скрыв окно Поля сводной таблицы. Выберите ячейку в сводной таблице и затем перейдите на вкладку Analyze на ленте. Нажмите кнопку Список полей в разделе Показать , чтобы включить или выключить окно Поля сводной таблицы.
Вы также можете закрыть окно, используя значок X в правом верхнем углу .
Вы также можете отобразить или скрыть окно полей сводной таблицы, щелкнув правой кнопкой мыши в любом месте сводной таблицы, затем выберите Показать список полей или Скрыть список полей (в зависимости от текущего состояния окна полей сводной таблицы).
Изменить расположение полей сводной таблицы по умолчанию
Щелкните шестеренку со стрелкой вниз, чтобы изменить внешний вид окна Поля сводной таблицы по умолчанию.
Вы можете выбрать один из пяти доступных вариантов.
- Полевой участок и участок с накоплением
- Полевая секция и параллельная секция площадей
- Только полевая секция
- Только участки (2 на 2)
- Только участки (1 на 4)
Измените порядок сортировки списка полей
Список полей данных по умолчанию будет отображаться в том же порядке, что и исходные данные.Вы можете изменить это, чтобы отображать в алфавитном порядке (от A до Z), если хотите. Щелкните левой кнопкой мыши в меню параметров в окне Поля сводной таблицы, чтобы получить доступ к параметру.
Выберите в меню пункт Сортировать от А до Я . Теперь ваши поля будут отображаться в порядке убывания!
Перемещение, изменение размера и закрытие окна полей сводной таблицы
Щелкните правой кнопкой мыши маленькую стрелку , направленную вниз, справа от заголовка полей сводной таблицы, чтобы переместить , изменить размер или закрыть окно.
- Переместить — это позволит вам открепить окно и перемещать его по электронной таблице.
- Размер — позволяет регулировать ширину и высоту (в отстыкованном состоянии) окна.
- Закрыть — позволяет закрыть окно. Вы можете открыть его снова, выбрав вкладку Analyze > Список полей .
Сочетание клавиш мастера сводных таблиц и диаграмм
Используйте сочетание клавиш Alt + D + P, чтобы открыть PivotTable and PivotChart Wizard .Это поможет вам настроить сводную таблицу или сводную диаграмму, выбрать данные и место для новой сводной таблицы или диаграммы.
Создание сводной таблицы с помощью сочетания клавиш
Используйте сочетание клавиш Alt + N + V на ленте, чтобы быстро создать сводную таблицу.
Показать подробности за значением
Дважды щелкните правой кнопкой мыши на значении в сводной таблице, чтобы быстро просмотреть данные, лежащие в основе этого агрегированного значения.Будет создан новый лист только с данными, относящимися к этому значению.
Вы также можете получить доступ к этой функции, щелкнув правой кнопкой мыши на любом значении и выбрав Показать подробности .
Отключите отображение подробностей, чтобы избежать случайного двойного щелчка
Если возможность отображать подробные данные за результатом сводной таблицы вас не интересует, вы можете отключить эту функцию. Это означает, что вы можете избежать создания новых листов с битами данных в них из-за случайного двойного щелчка.
Выберите сводную таблицу и перейдите на вкладку Analyze на ленте. Нажмите кнопку Options в разделе PivotTable , чтобы открыть меню параметров.
В меню PivotTable Options перейдите на вкладку Data и снимите отметку , Enable show details , чтобы отключить эту функцию.
Заменить пустые ячейки
Эта сводная таблица содержит пустых ячеек , потому что наши исходные данные не содержат записей для этих комбинаций измерений.Например, нет данных для Артура Джеймса и Франции, поэтому пересечение строки Артура Джеймса и столбца Франции остается пустым. Мы можем изменить настройки, чтобы отображать что-то вроде ноль или какой-то текст с надписью « N / A » вместо пустого поля.
Щелкните левой кнопкой мыши в любом месте сводной таблицы, затем выберите Параметры сводной таблицы .
В меню параметров сводной таблицы
- Перейдите на вкладку Layout & Format .
- Установите флажок Для пустых ячеек покажите поле и введите значение, которое вы хотите отображать для пустых ячеек. В нашем примере мы заменим пустые ячейки на 0.
- Нажмите кнопку OK .
Теперь пустые ячейки заменены нулями .
Показать элементы без данных
В этом примере мы создали сводную таблицу с Customer Name и Product Sold в области Rows .Обратите внимание, что для каждого покупателя перечислены не все возможные продукты. Перечисляются только те, для которых у нас есть транзакция в наших данных. Мы можем изменить это так, чтобы мы видели все элементы, даже если данных нет.
Щелкните правой кнопкой мыши и выберите в меню Field Settings .
Сводные функции сводной таблицы Excel
В сводной таблице Excel значения отображаются как сумма или количество. Узнайте, почему это происходит, и узнайте, как перейти к другим функциям.Получите бесплатную рабочую книгу ниже.
Введение
Когда вы добавляете поле в область значений сводной таблицы, 11 различных
для подведения итогов доступны такие функции, как Sum, Count и Average.
данные.
Сводные функции в сводной таблице аналогичны рабочим листам.
функции с одинаковыми именами, с некоторыми отличиями, как указано в
следующие описания.
Изменение функции сводки
Когда вы добавляете числовое поле в область значений сводной таблицы,
Сумма или счет будет по умолчанию
сводная функция. Функцию по умолчанию изменить нельзя — она применяется в зависимости от содержимого поля:
- Если поле
содержит числа, Сумма будет по умолчанию - Если поле
содержит текст или пустые ячейки, Счетчик будет по умолчанию
После добавления поля в сводную таблицу, чтобы выбрать другую функцию сводки, выполните следующие действия.
эти шаги:
- Щелкните правой кнопкой мыши ячейку в поле «Значение», которое вы хотите изменить.
- Во всплывающем меню щелкните Суммировать значения по
- Щелкните функцию сводки, которую вы хотите использовать
Итоги и промежуточные итоги
Выбранная функция суммирования будет автоматически использоваться в промежуточных итогах
и всего по этому полю — человек. Вы можете выбрать другую функцию
для итогов — см. инструкцию
Вот.
Однако итоги рассчитываются на основе исходных данных, а не значений
отображается в сводной таблице.Например, если в поле используется сводка MAX
функция, а промежуточный итог показывает СРЕДНЕЕ, это будет среднее значение от
значения в исходных данных, а не среднее значение MAX. (Вычислять
Среднее из максимальных значений, вы можете использовать формулы вне сводной таблицы
table или создайте новую сводную таблицу на основе исходной.)
Изменить несколько полей одновременно
Чтобы изменить сразу несколько полей, вы можете использовать
макрос.Или, если вы купили копию моего PivotPower
Надстройка Premium, просто нажмите «Суммировать все» или выберите другую функцию
в раскрывающемся списке Суммировать все по, и все поля данных будут
изменен на выбранную функцию.
Функция суммы
Функция Sum сводной таблицы суммирует все базовые значения
для каждого элемента в поле. Результат такой же, как при использовании СУММ
функцию на листе, чтобы суммировать значения.Пустые ячейки и ячейки
с текстом игнорируются.
Когда вы добавляете числовое поле в область значений сводной таблицы,
Сумма будет итоговой функцией по умолчанию. (Примечание: если поле
содержит текст или пустые ячейки, Счетчик будет по умолчанию.)
На снимке экрана ниже вы можете увидеть исходные данные для небольшого
сводная таблица и общее количество, используя функцию SUM рабочего листа,
317.
С помощью сводной таблицы вы можете быстро увидеть общую сумму по каждому продукту.
что было продано, а общая сумма — 317 — соответствует таблице
общее.
Макрос функции суммы
Вместо того, чтобы вручную изменять каждое поле данных на функцию 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 в столбце «Кол-во» пуста.
Если вы хотите подсчитать заказ перьев и использовать поле Кол-во,
пустая ячейка (C7) не учитывается.Количество заказов на Ручки
будет отображаться как 1 (сводная таблица слева внизу).
Вместо этого вы можете добавить поле продукта в область значений и
в заказах на ручки будет отображаться счетчик до 2 (сводная таблица справа внизу).
Функция среднего
Функция Среднее значение суммирует все базовые значения в
Область значений, и она делится на количество значений.Результат
так же, как при использовании функции СРЕДНИЙ на листе для расчета
среднее (среднее) значений.
Пробелы и нули
Пустые ячейки и ячейки с текстом игнорируются при вычислении
средние значения сводной таблицы, но включены нулевые ячейки.
В показанном ниже источнике данных ячейка C7 пуста и не включается.
либо в среднем на листе (C12), либо в среднем по сводной таблице,
показано ниже.
Если вы отформатировали рабочий лист, чтобы скрыть нулевые значения, помните
что эти нулевые значения будут включены в средние, даже если
ячейки будут пустыми.
Отформатируйте результаты
При использовании сводной функции Среднее значение результаты будут
вероятно, показывают странную смесь десятичных знаков, как показано на
сводная таблица слева, на снимке экрана ниже.
Отформатируйте поле, чтобы иметь постоянное количество десятичных знаков (как
в сводной таблице справа внизу), поэтому числа легко
сравнить.
Макс.функция
Сводная функция Max показывает максимальное значение из
базовые значения в области значений. Результат такой же, как при использовании
функцию MAX на листе, чтобы вычислить максимум
ценности.
На снимке экрана ниже вы можете увидеть исходные данные для небольшого
сводная таблица и максимальное количество, используя функцию MAX рабочего листа,
составляет 97.
С помощью сводной таблицы вы можете быстро увидеть максимум для каждого продукта
который был продан, и общая сумма — 97, что соответствует листу
максимум.
Минимальная функция
Сводная функция Min показывает минимальное значение из
базовые значения в области значений.Результат такой же, как при использовании
функция MIN на листе, чтобы вычислить минимум
ценности.
На снимке экрана ниже вы можете увидеть исходные данные для небольшого
сводная таблица и минимальное количество, используя функцию MIN рабочего листа,
составляет 8.
С помощью сводной таблицы вы можете быстро увидеть минимум для каждого продукта
который был продан, и общее количество — 8, что соответствует листу
минимум.
И на листе, и в сводной таблице пустая ячейка игнорируется
при расчете минимальной суммы.
Функция продукта
Сводная функция Произведение показывает результат умножения
все базовые значения в области значений. Результат тот же
как использование функции ПРОДУКТ на листе для расчета продукта
ценностей.
Мне никогда не приходилось использовать сводную функцию Продукт в сводной таблице
table, и не могу представить ситуацию, в которой это было бы полезно. Однако,
он может пригодиться вам, и вот как он работает.
На снимке экрана ниже вы можете увидеть исходные данные сводной таблицы,
с ПРОДУКТОМ, рассчитанным для каждой группы товаров. Внизу
исходных данных — это общий расчет ПРОДУКТА.
Результаты функции Продукт могут быть очень большими числами
и по умолчанию используется формат научных чисел. Вы можете форматировать числа
как числовой формат, а не научный формат.
Примечание. Excel сохраняет и вычисляет только с 15 значащими цифрами.
точности, поэтому после 15-го символа вы увидите только нули.
Счетчик чисел Функция
Счетчик чисел Сводная функция подсчитывает все
числа в области значений.Результат такой же, как при использовании COUNT
функция на листе. Пустые ячейки, ошибки и текст не учитываются.
На снимке экрана ниже вы можете увидеть исходные данные для небольшого
сводная таблица и количество чисел в столбце Кол-во (столбец
C). В ячейке C4 значение 20 вводится как текст, поэтому эта ячейка не
посчитал.
Счетчик чисел и счетчик
В сводной таблице, показанной ниже, поле Qty было добавлено дважды.
в область Ценности.В столбце B итоговая функция — Count.
Числа , а общая сумма — 7.
В столбце C итоговая функция — Счетчик , которая включает
текста, поэтому общая сумма для этого столбца равна 8.
Функция StdDev и функция StdDevP
Как и функции рабочего листа STDEV.P и STDEV.S, StdDevp и
Сводные функции StdDev вычисляют стандартное отклонение для
базовые данные в области значений.Стандартное отклонение — это мера
насколько значения отличаются от среднего значения.
Функция сводки StdDevP должна использоваться, когда все
используется в расчете. Когда используется выборка данных, не
всего населения, затем используйте сводную функцию StdDev.
На снимке экрана ниже вы можете увидеть пример источника сводной таблицы
данные, а функция рабочего листа STDEV.P вычисляет стандартные
отклонение для каждого вида продукции.Для папок с файлами есть
большая разница между проданным количеством и стандартным отклонением
высокий — 44,5. Для бумаги разница в количестве намного меньше,
и стандартное отклонение низкое — 4,7.
Когда поле Qty добавлено в сводную таблицу, измените сводку
расчет в StdDevp.
На снимке экрана ниже видно, что стандартные отклонения
в сводной таблице те же, что были рассчитаны на
рабочий лист.
Примечание : Если количество элементов равно единице, # DIV / 0! отображается ошибка
при использовании итоговой функции StdDev, потому что вычитается один
от счета при вычислении стандартного отклонения.
Как рассчитывается стандартное отклонение
Для стандартного отклонения каждое число сравнивается со средним
номеров. Вы можете рассчитать стандартное отклонение по
лист, без использования СТАНДОТКЛОНА.Функция P.
- Найдите среднее значение чисел в данных сводной таблицы.
- Из каждого числа вычтите среднее значение.
- Возвести вычисленную разницу в квадрат для каждого числа
- Найдите среднее значение квадрата разности.
- Найдите квадратный корень из среднего.
Функция Var и функция Varp
Сводные функции Var и Varp работают как VAR.P и VAR.S
функции рабочего листа, чтобы вычислить дисперсию для базового
данные в области значений, а дисперсия — это мера того, насколько широко
значения отличаются от среднего значения.
Когда в расчетах используется вся генеральная совокупность, сводка VarP
функция используется. Для выборки данных, а не всего
Население, используйте функцию суммирования Var.
На снимке экрана ниже показан пример исходных данных сводной таблицы,
с помощью VAR.Функция рабочего листа P, вычисляющая дисперсию для каждого
Тип продукта. Для папок с файлами, для которых существует большая разница
между двумя величинами разница велика — 1980,25. За
Продажи бумаги, есть небольшая разница в количестве, и
дисперсия составляет всего 22,22.
Чтобы показать дисперсию, когда поле Qty добавлено в сводную таблицу,
измените итоговый расчет на Varp.
Как видите, отклонения, показанные в сводной таблице, одинаковы.
как те, которые были рассчитаны на листе.
Примечание : Если количество элементов равно единице, # DIV / 0! отображается ошибка
при использовании итоговой функции Var, потому что вычитается единица из
счет при вычислении дисперсии.
Как рассчитывается отклонение
Для дисперсии каждое число сравнивается со средним значением чисел.Вы можете рассчитать дисперсию на листе без VAR.P
функция.
- Найдите среднее значение чисел в данных сводной таблицы.
- Из каждого числа вычтите среднее значение.
- Возвести вычисленную разницу в квадрат для каждого числа
- Найдите среднее значение квадрата разности.
Отличный счет
В сводной таблице вы можете захотеть увидеть количество уникальных (отдельных) элементов в сегменте вместо общего количества .Например, если ручки и папки продаются в разных цветах, , сколько цветов было для каждого продукта?
В обычной сводной таблице нет встроенной функции «Уникальный счетчик», но расчет отдельного счетчика доступен только для сводных таблиц на основе OLAP , таких как Power Pivot, или сводных таблиц на основе модели данных . Чтобы узнать о других способах получения точного подсчета (уникального количества), см. Страницу «Подсчет уникальных элементов».
В этом видео показано, как получить точный счетчик с помощью Power Pivot, а образец файла находится в разделе загрузки.
ПРИМЕЧАНИЕ. Ниже видео описаны шаги для модели данных.
Пример данных
В этом примере исходные данные сводной таблицы — это таблица с информацией о продажах канцелярских товаров. В таблице 3 поля — Товар, Цвет и Количество. Вы можете загрузить образец файла OLAP сводной функции, чтобы просмотреть данные и сводную таблицу.
Сводная таблица — модель данных
Я создал сводную таблицу на основе исходных данных канцелярских товаров и добавил галочку к опции «Добавить эти данные в модель данных».
Это создает сводную таблицу на основе OLAP вместо обычной сводной таблицы.
Список цветов
В сводной таблице продукт и цвет находятся в области строк, а цвет также находится в области значений как количество цветов. Это показывает общее количество — сколько элементов каждого цвета находится в исходных данных.
В области «Строки» есть 5 уникальных цветов, перечисленных в разделе «Подшивки», и 3 уникальных цвета, перечисленных в разделе «Перья», но сводная таблица не дает нам этих уникальных значений.
Покажи отчетливый счет
Чтобы получить уникальное количество цветов для каждого продукта, выполните следующие действия:
- Щелкните правой кнопкой мыши одно из значений в поле «Количество цветов»
- Щелкните Параметры поля значений
- Прокрутите список «Суммировать значение поля по по» вниз и нажмите «Различное количество», затем нажмите «ОК».
Поле значения цвета изменится, и промежуточные итоги продукта покажут точное количество цветов, проданных для каждого типа продукта.
Если вам не нужно видеть названия цветов,
Ошибки в исходных данных
Если в исходных данных есть значения ошибок, отображается сводная таблица.
ошибка для этих данных, за исключением случаев, указанных ниже, для Count и Count
Nums.
В этом примере поле «Итого» содержит #VALUE! ошибка и # DIV / 0
ошибка, одна пустая ячейка (E7) и одна ячейка с текстом (E9).
Ошибки с подсчетом и подсчетом чисел
Эти две сводные функции подсчитывают ошибки или игнорируют их. В
ошибки не отображаются в итоговой сумме товаров.
- Подсчет чисел : пустые ячейки, ошибки и текст не учитываются.
- Счетчик : подсчитываются текст, числа и ошибки. Пустые ячейки
не засчитывается.
Ошибки с другими функциями сводки
Для всех остальных сводных функций, если в исходных данных есть ошибки.
поле:
- первая ошибка, обнаруженная в исходных данных, отображается в сводной таблице
- сумма не рассчитывается — отображается первая ошибка из исходных данных.
В данных # ЗНАЧЕНИЕ! это первая ошибка в списке, поэтому она появляется в
сводная таблица.
Однако, если вы отсортируете данные с самыми последними датами вверху, # DIV / 0! ошибка первая. Затем обновите сводную таблицу, и она покажет # DIV / 0! ошибка.
Итоги и промежуточные итоги
Если отображаются промежуточные итоги или итоги по строкам и столбцам, это влияет
итоги и промежуточные итоги отображают ошибку.
И хотя они не показывают ошибок в итоговых значениях товаров, функции Count и Count Numbers также будут отображать ошибки в
их общее количество, если выполняются оба этих условия:
- другие сводные функции включены в сводку
таблица, и эти поля содержат ошибки в данных - Поля Count и Count Number содержат ошибки в данных
Например, на снимке экрана ниже добавлено поле «Среднее значение для цены» и
это поле содержит # DIV / 0! ошибка.В итоге:
- Подсчет чисел и общие итоги подсчета показывают # ЗНАЧЕНИЕ! ошибка, потому что они основаны на поле Всего, которое содержит ошибки в данных
- Однако общий итог «Число дат» верен, поскольку поле «Дата» не содержит ошибок в данных.
Загрузить рабочие тетради
Инструменты для поворотного стола
Чтобы сэкономить время при создании, форматировании и изменении сводных таблиц, используйте инструменты в моей надстройке Pivot Power Premium.Всего за несколько кликов вы можете:
- скопируйте форматирование из одной сводной таблицы и примените его к другой сводной таблице.
- изменить все значения с Count на Sum
- удалить «Сумма» из всех заголовков.
и многое другое!
Дополнительные уроки
Слайсеры Value Group
Значения ошибок Excel
Группировка данных
Промежуточные итоги
Очистить старые элементы в сводной таблице
Примеры сортировки сводной таблицы Excel и видео
Как исправить проблемы сортировки сводной таблицы.Показывать новые элементы в правильном порядке, неправильный элемент вверху списка. Сортировать отчет Фильтровать элементы, сортировать по значениям, сортировать слева направо
Ограничения сортировки сводной таблицы
В области строки сводной таблицы элементы сводной таблицы можно сортировать одним из следующих способов:
- по этикеткам, A-Z или Z-A
- одним из полей значения, от наибольшего к наименьшему или от наименьшего к наибольшему.
Однако структура сводной таблицы накладывает ограничения на то, что будет отсортировано:
- Если нет поля сводки слева от поля, которое вы сортируете, все элементы сводки будут отсортированы вместе в том порядке, который вы выбрали.
- Если есть поля слева от поля, которое вы сортируете, элементы сводки будут отсортированы в каждом элементе следующего поля слева.
Сортировать по ярлыкам
Для быстрой сортировки элементов сводного поля по алфавиту:
- Щелкните любой элемент сводной таблицы в поле сводной таблицы, чтобы выбрать эту ячейку
- На вкладке «Данные» ленты Excel щелкните команду A – Z или команду Z – A
На снимке экрана ниже поле Продукт находится в области строки, а название продукта выбрано в ячейке A5.При нажатии кнопки Z-A названия продуктов сортируются в порядке убывания.
Сортировать по значению
Для быстрой сортировки элементов сводного поля по полю значения:
- Щелкните значение любого элемента сводной таблицы, чтобы выбрать эту ячейку
- На вкладке «Данные» ленты Excel щелкните команду A – Z или команду Z – A
На снимке экрана ниже поле «Продукт» находится в области строки, а значение в поле «Заказы» выбрано в ячейке C4.При нажатии кнопки A – Z значения заказов сортируются в порядке возрастания.
с поворотным полем влево
В приведенных выше примерах нет сводного поля слева от поля продукта. В этой ситуации все сводные элементы сортируются вместе, поэтому вы можете видеть верхнее и нижнее значения в целом.
Если есть сводное поле слева от поля, которое вы сортируете, сортировка ограничивается этим внешним полем.Ярлыки или значения сортируются только внутри каждого элемента внешнего поля — сортировка не происходит в целом.
На приведенном ниже снимке экрана поле «Продукт» находится в области строк, а поле «Категория» — слева от него как внешнее поле.
Количество продукта в ячейке C5 выбрано, и при нажатии кнопки A – Z значения Qty сортируются в порядке возрастания — в каждой категории .
Независимая сортировка
Внешнее поле «Категория» также может иметь отсортированные значения «Кол-во», независимо от значений «Кол-во продуктов».
На приведенном ниже снимке экрана количество категорий в ячейке C4 выбрано и отсортировано по Z-A (от наибольшего к наименьшему). Это перемещает файлы cookie в верхнюю часть списка, но продукты в каждой категории по-прежнему отсортированы от A до Z (от наименьшего к наибольшему)
Новые элементы сводной таблицы
Вышел из строя
После создания сводной таблицы в источник могут быть добавлены новые элементы.
данные. Когда вы обновляете сводную таблицу, эти новые элементы могут появиться
в раскрывающихся списках внизу списка.Иногда
новые элементы вообще не появляются, если применяется фильтр.
Посмотрите это видео, чтобы узнать, как решать проблемы с новинками в
сводная таблица.
.
Новые элементы сводной таблицы вышли из строя
Когда вы добавляете новые записи в исходные данные сводной таблицы и обновляете
в сводной таблице новые элементы могут появиться в раскрывающихся списках.
Иногда эти новые элементы появляются в конце списка вместо
из в алфавитном порядке.Например, на снимке экрана ниже папки
были только что добавлены к исходным данным этой сводной таблицы.
При обновлении сводной таблицы внизу появились подшивки.
списка продуктов, а не вверху.
Исправить порядок сортировки
Элементы в раскрывающихся списках сводной таблицы могут выйти из строя, если
Для поля сводки установлено значение Сортировка вручную (настройка по умолчанию).Когда поле
установлен для ручной сортировки, сводные элементы отображаются в алфавитном порядке
сначала, и вы можете перетащить элементы поворота в другое положение
в листе. Все новые элементы появятся в конце раскрывающегося списка.
список.
Выполните следующие действия, чтобы отсортировать поля в порядке возрастания:
- Щелкните правой кнопкой мыши ячейку в поле «Продукт». Например, щелкните правой кнопкой мыши
ячейка «Конверты». - Щелкните «Сортировка», а затем щелкните «Сортировка от А до Я».
Когда вы сортируете поле Product, его настройка сортировки меняется с Manual
по Сортировка по возрастанию или Сортировка по убыванию .
Это также сортирует раскрывающийся список и упрощает работу для пользователей.
чтобы найти нужные им предметы.
Видео: сортировка элементов фильтра отчета
После обновления сводной таблицы в отчете могут появиться новые элементы.
Выпадающие списки фильтров.Если новые элементы расположены не в алфавитном порядке,
вы можете выполнить шаги в коротком видео, чтобы отсортировать их.
Сортировка элементов фильтра отчета
Если вы добавляете новые записи в поля, которые находятся в отчете сводной таблицы
Фильтр, новые элементы могут появиться в конце списка, а не
из в алфавитном порядке.
Вы можете отсортировать элементы в алфавитном порядке вручную (как
описано ниже), или с программированием (получить отчет
макрос сортировки фильтра здесь).
В моей надстройке Pivot Power Premium также есть команда «Сортировать фильтры отчета».
Новые позиции в конце списка
В этом примере поле «Продукт» находится в области «Фильтр отчета»,
а Binders — это новый продукт в исходных данных. Когда сводная таблица была обновлена, подвязки появились внизу.
списка продуктов, а не вверху.
Исправить порядок сортировки фильтра отчетов
Если в поле задана сортировка вручную, новые элементы будут отображаться в
конец раскрывающегося списка.Вы не можете сортировать поля, которые находятся в
Область фильтров отчетов, поэтому временно переместите их в область строк.
Выполните следующие действия, чтобы отсортировать поле фильтра отчета в порядке возрастания:
- Перетащите поле «Продукт» в область «Строки» над любой другой строкой.
поля. - Щелкните правой кнопкой мыши ячейку в поле «Продукт». Например, щелкните правой кнопкой мыши
ячейка «Конверты». - Щелкните «Сортировка», а затем щелкните «Сортировка от А до Я».
- Перетащите поле «Продукт» обратно в область «Фильтр отчетов».
Когда вы сортируете поле Product, его настройка сортировки меняется с Manual
по Сортировка по возрастанию или Сортировка по убыванию .
Это также сортирует раскрывающийся список и упрощает работу для пользователей.
чтобы найти нужные им предметы.
Сортировка поля сводной таблицы слева направо
Обычно вы сортируете сводную таблицу по значениям в столбце, например
столбец «Общий итог».
Путем сортировки вы можете выделить самые высокие или самые низкие значения, перемещая
их в верхнюю часть сводной таблицы.
Для сортировки столбца сводной таблицы:
- Щелкните правой кнопкой мыши ячейку значения и выберите «Сортировать».
- Затем нажмите Сортировать от наименьшего к наибольшему или Сортировка от наибольшего к наименьшему.
Сортировка строки сводной таблицы
Вы также можете отсортировать строку сводной таблицы по ее значениям слева направо.Это перемещает наибольшее или наименьшее количество влево от оси поворота.
стол, чтобы вы могли сосредоточиться на них.
Например, в приведенной ниже сводной таблице столбцы городов
в алфавитном порядке, слева — Бостон.
Чтобы выделить город с самыми высокими продажами, вы можете отсортировать шоколад.
Строка чипа, поэтому продажи по городу отсортированы в порядке убывания.
Для сортировки строки сводной таблицы:
- В сводной таблице щелкните правой кнопкой мыши ячейку значения в
Чиповый ряд. - Щелкните «Сортировка», затем щелкните «Дополнительные параметры сортировки».
- В диалоговом окне «Сортировка по значению» в разделе «Параметры сортировки» выберите «Наибольший».
на самый маленький. - В разделе Направление сортировки выберите Слева направо. В разделе «Сводка»
вы можете увидеть описание настроек сортировки.
- Нажмите ОК, чтобы закрыть диалоговое окно.
После сортировки строки шоколадной крошки слева направо значения
в строке «Шоколадная крошка» отсортированы от наибольшего к наименьшему слева
направо.
Порядок столбцов «Город» изменился, и в Чикаго, где самый высокий
Продажа шоколадных чипсов, слева.
ПРИМЕЧАНИЕ : Строки для других товаров не могут быть в порядке убывания,
потому что порядок столбцов был установлен значениями в шоколаде
Чиповый ряд.
Сортировать сводное поле в произвольном порядке
В большинстве случаев вы будете сортировать сводную таблицу
- в алфавитном порядке на основе меток строк,
- или по стоимости.
Вы также можете создавать собственные списки, например названия отделов или городов,
и укажите уникальный порядок сортировки для этих списков.
Затем в сводной таблице вы можете использовать эти списки в качестве порядка сортировки,
вместо сортировки по алфавиту.
Чтобы создать собственный список, следуйте инструкциям здесь: Создать
Пользовательский список
Применение пользовательского порядка сортировки
После создания настраиваемого списка выполните следующие действия, чтобы применить настраиваемый список.
порядок сортировки по полю сводной таблицы:
- Обновите сводную таблицу.
- Если для поля, содержащего элементы из настраиваемого списка, установлено значение Автоматически
sort он должен измениться на порядок сортировки настраиваемого списка. - Если поле в настоящее время настроено для ручной сортировки, сортировка не выполняется.
в соответствии с заказным списком. Чтобы изменить его на автоматическую сортировку,
щелкните правой кнопкой мыши название города, выберите «Сортировка», а затем нажмите «Сортировка от А до Я».
Запрет сортировки пользовательского порядка в сводной таблице
В некоторых случаях может не потребоваться использование настраиваемого порядка сортировки списка.
в сводной таблице. Например, если продавца зовут Ян или Май,
это имя появится вверху списка перед другими именами, например
как Энн или Боб.
Чтобы пользовательские списки не имели приоритета при сортировке
сводная таблица, выполните следующие действия, чтобы изменить настройку:
- Щелкните правой кнопкой мыши ячейку в сводной таблице и выберите Параметры сводной таблицы.
- В диалоговом окне Параметры сводной таблицы щелкните Итоги и фильтры
таб. - В разделе «Сортировка» снимите флажок «Использовать настраиваемый
Списки при сортировке «
ПРИМЕЧАНИЕ. Этот параметр будет применяться ко всем полям в сводной таблице.
Сортировка сводных полей с помощью макроса
Следующий макрос изменит настройку Сортировки на По возрастанию (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 Концевой переводник
Скачать образец файла
- Чтобы проверить приемы этого руководства, вы можете скачать
файл примера сортировки сводной таблицы. Заархивированный файл имеет формат xlsx и НЕТ
макросы. - Чтобы протестировать сортировку на основе макроса значения , загрузите
файл «Сортировка по значениям». Заархивированный файл имеет формат xlsm и
содержит макросы с этой страницы. - Чтобы протестировать сортировку на основе макроса списка , вы можете загрузить
Сортировка по файлу списка. Заархивированный файл имеет формат xlsm и
содержит макросы.
Дополнительные уроки
Перемещение сводных элементов вручную
Очистить старые элементы в сводной таблице
Фильтры отчетов
.