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

Обновлено: 21.11.2024

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

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

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

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

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

Примеры

У нас есть данные, согласно которым отдел проверки качества помечает продукт как "приемлемый" для использования и "НЕ ОК" и "СРЕДНИЙ" для использования для определенных идентификаторов продукта. Мы построим сводную таблицу для данных, а затем найдем наибольшее число каждой пропорции.

Рассмотрите следующие данные,

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

На новом рабочем листе, куда нас ведет ExcelНовый рабочий лист, куда нас ведет ExcelЧтобы добавить новый рабочий лист, перейдите в нижнюю часть экрана и нажмите кнопку «+». Вы также можете перейти к «Главной», «Вставить», а затем «Вставить лист», чтобы добавить новый лист. Подробнее, мы можем увидеть раздел полей, который мы обсуждали ранее. Перетащите условие в поля строк и идентификатор продукта в значения.

Слева мы видим, что отчет создан для сводной таблицы.

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

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

Мы видим, что наши данные отсортированы в порядке возрастания.

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

Посмотрите на данные ниже,

Что нужно помнить

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

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

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

В приведенном ниже примере данные на уровне категории (шлем, дорожная сумка) отсортированы по алфавиту от А до Я.

Чтобы просмотреть общие итоги по продуктам — от наибольшего к наименьшему, — выберите любое число в столбце «Общий итог» и выполните сортировку по нему.

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

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

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

Нажмите поле в строке или столбце, которые нужно отсортировать.

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

В диалоговом окне "Сортировка" выберите нужный тип сортировки:

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

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

Нажмите По возрастанию (от А до Я) или По убыванию (от А до Я), а затем выберите поле, которое нужно отсортировать.

Чтобы получить дополнительные параметры, нажмите "Дополнительные параметры", а затем выберите нужный параметр в диалоговом окне "Дополнительные параметры сортировки":

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

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

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

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

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

Совет. Нажмите Порядок источника данных, чтобы вернуть элементы в исходный порядок. Этот параметр доступен только для исходных данных интерактивной аналитической обработки (OLAP).

Вот быстрый способ сортировки данных по строкам или столбцам:

Нажмите поле в строке или столбце, которые нужно отсортировать.

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

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

Чтобы отсортировать данные в порядке возрастания или убывания, нажмите «Сортировать по возрастанию» или «Сортировать по убыванию».

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

Выполните следующие действия:

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

Если щелкнуть стрелку "Ярлыки столбцов", сначала выберите поле, которое нужно отсортировать, а затем нужный вариант сортировки.

В поле "Сортировать по значению" в разделе "Выбрать значение" выберите значение для сортировки.

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

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

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

по их ярлыкам, от A до Z или от Z до A
по одному из полей "Значение" в порядке от наибольшего к наименьшему или от наименьшего к наибольшему.

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

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

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

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

Нажмите на любой элемент сводки в поле сводки, чтобы выбрать эту ячейку.
На вкладке "Данные" ленты Excel нажмите команду A-Z или команду Z-A.

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

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

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

Нажмите на значение любого сводного элемента, чтобы выбрать эту ячейку
На вкладке "Данные" ленты Excel нажмите команду A-Z или команду Z-A.

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

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

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

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

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

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

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

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

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

Новые элементы сводной таблицы не по порядку

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

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

Новые элементы сводной таблицы Excel не по порядку

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

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

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

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

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

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

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

При сортировке поля "Товар" его параметр сортировки изменяется с "Вручную" на "Сортировка по возрастанию" или "Сортировка по убыванию".

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

Видео: сортировка фильтра отчета сводной таблицы

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

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

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

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

Новые элементы в конце списка

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

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

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

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

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

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

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

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

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

Чтобы отсортировать столбец сводной таблицы:

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

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

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

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

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

Выберите вариант сортировки

Чтобы отсортировать строку сводной таблицы:

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

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

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

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

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

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

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

в алфавитном порядке по названиям строк, от A до Z или от Z до A.
по числовому значению - от наибольшего к наименьшему или от наименьшего к наибольшему

Однако в Excel вы также можете сортировать элементы на основе пользовательского списка с настраиваемым порядком сортировки.

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

В Excel есть несколько встроенных настраиваемых списков, которые вы можете использовать:

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

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

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

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

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

включите этот параметр и разрешите Excel использовать эти настраиваемые списки в качестве порядка сортировки вместо сортировки от А до Я.
отключить этот параметр и запретить сортировку Excel на основе пользовательского списка

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

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

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

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

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

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

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

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

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

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

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

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

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

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

Первые 2 макроса сортируются по определенному полю "Значение" — "Сумма итогов".
Следующие 2 макроса сортируются по выбранному полю "Значение". Есть версии для
Сводные таблицы Normaland/или модели данных

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

Чтобы отсортировать данные в сводной таблице, выберите любую ячейку и щелкните ее правой кнопкой мыши, чтобы найти параметр "Сортировка". В списке «Сортировка» у вас будет два варианта: «Сортировать от меньшего к большему», а другой — «Сортировать от большего к меньшему».

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

Сортировка по значениям с помощью параметра «Сортировка и фильтрация»

Excel имеет встроенную функцию сортировки и фильтрации, которая работает как для обычной таблицы, так и для сводной таблицы. Для этого выберите любую ячейку сводной таблицы, а затем щелкните параметр «Сортировка и фильтр» в разделе «Редактирование» на вкладке «Главная». Там вы увидите две опции: «Сортировать от наименьшего к наибольшему» и «Сортировать от наибольшего к наименьшему». Нажав кнопку Сортировать от наибольшего к наименьшему, вы увидите, что ваша таблица отсортирована от наибольшего к наименьшему значению.

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

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

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

После этого нажмите на фильтры значений и выберите вариант «Больше чем».

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

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

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

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

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

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

После этого вы увидите внезапные изменения в таблице. Сортировка происходит в строке. Мы выбрали строку Electric Kettle, и там самое низкое значение было 700, что является значением продаж за февраль для Electric Kettle. После сортировки первым будет число 700, так как это наименьшее число в строке. Мы увидим, что столбец «Продажи за февраль» теперь находится на первом месте из-за сортировки от наименьшего к наибольшему в строке «Электрический чайник».

Заключение

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

Читайте также: