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

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

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

Для того, чтобы выполнить сортировку числовых данных сводной таблицы внутри каждой категории (наименований строк), необходимо:

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

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

Параметры сортировки – «По убыванию» и направление – «Сверху вниз».

Если материал Вам понравился или даже пригодился, Вы можете поблагодарить автора, переведя определенную сумму по кнопке ниже:

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

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

При сортировке данных в сводной таблице следует учитывать следующее:

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

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

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

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

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

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

В приведенном ниже примере данные в столбце Транспорт отсортированы от наименьшего до самого большого.

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

Установка собственных параметров сортировки

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

1. Щелкните ячейку в строке или столбце, которые требуется отсортировать.
2. Щелкните стрелку рядом с полем Метки строк или Метки столбцов.
3. В разделе Сортировка выберите по возрастанию или убыванию и выберите один из параметров в списке Сортировка. (Эти параметры зависят от выбранных вами на шагах 1 и 2.)
4. В разделе Фильтр выберите другие возможные условия. Например, если вы хотите просмотреть данные только за март, в списке По метке выберите Равно , а затем введите March в появившемся текстовом поле. Чтобы включить в вычисления только определенные данные, выберите или снимите флажки проверка в поле Фильтр. Чтобы отменить выбор, нажмите кнопку Очистить фильтр.
5. Щелкните за пределами диалогового окна настраиваемая сортировка, чтобы закрыть его.

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

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

Перед сортировкой

При сортировке данных учитывайте приведенные ниже сведения.

• Порядок сортировки зависит от выбранного языка. Убедитесь в том, что на панели управления в разделе «Региональные параметры» или «Язык и региональные стандарты» задан правильный языковой стандарт. Сведения о том, как изменить языковой стандарт, см. в справке Windows.
• Данные могут содержать начальные пробелы, которые влияют на результаты сортировки. Для получения оптимальных результатов следует удалить такие пробелы перед сортировкой.
• Нельзя сортировать текстовые значения с учетом регистра символов.
• Нельзя сортировать данные по определенному признаку форматирования, такому как цвет ячеек или шрифта. Также нельзя сортировать данные по индикаторам условного форматирования, например наборы значков.

Сортировка меток строк или столбцов

1. В сводной таблице щелкните маленькую стрелку рядом со списком Названия строк или Названия столбцов.
2. Щелкните поле в строке или столбце, которые требуется отсортировать.
3. Щелкните стрелку в списке Названия строк или Названия столбцов, а затем выберите нужный параметр.

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

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

Сортировка по столбцу, который не имеет кнопки со стрелкой

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

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

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

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

Установка собственных параметров сортировки

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

1. Щелкните ячейку в строке или столбце, которые требуется отсортировать.
2. Щелкните стрелку на вкладке Метки строк или Метки столбцов, а затем выберите Дополнительные параметры сортировки.

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

• Чтобы изменить порядок элементов перетаскиванием, щелкните Вручную. Наведите указатель мыши на границу элемента, чтобы увидеть четырехконечную стрелку, затем перетащите ее.
• Элементы, которые отображаются в области значений списка полей сводной таблицы, нельзя перетаскивать.
• Щелкните По возрастанию (от А до Я) по полю или По убыванию (от Я до А) по полю и выберите поле для сортировки.
• Чтобы увидеть другие параметры сортировки, нажмите щелкните на пункте Дополнительные параметры, затем в диалоговом окне Дополнительные параметры сортировки выберите подходящий вариант:

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

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

Примечание: Сортировка по настраиваемым спискам не сохраняется после обновления данных в сводной таблице.

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

Перед сортировкой

При сортировке данных учитывайте приведенные ниже сведения.

• Порядок сортировки зависит от выбранного языка. Убедитесь в том, что на панели управления в разделе «Региональные параметры» или «Язык и региональные стандарты» задан правильный языковой стандарт. Сведения о том, как изменить языковой стандарт, см. в справке Windows.
• Данные могут содержать начальные пробелы, которые влияют на результаты сортировки. Для получения оптимальных результатов следует удалить такие пробелы перед сортировкой.
• Нельзя сортировать текстовые значения с учетом регистра символов.
• Нельзя сортировать данные по определенному признаку форматирования, такому как цвет ячеек или шрифта. Также нельзя сортировать данные по индикаторам условного форматирования, например наборы значков.

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

1. Щелкните ячейку в строке или столбце, которые требуется отсортировать.
2. Щелкните стрелку на списке Названия строк или Названия столбцов, а затем выберите нужный параметр сортировки.

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

Сортировка меток строк или столбцов вручную

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

• Наведите указатель мыши на границу элемента, пока не увидите курсор перемещения, а затем перетащите его.

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

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

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

1. Щелкните стрелку на списке Названия строк и выберите пункт Сортировка по значению.

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

Перед сортировкой

При сортировке данных учитывайте приведенные ниже сведения.

• Порядок сортировки зависит от выбранного языка. Убедитесь в том, что на панели управления в разделе «Региональные параметры» или «Язык и региональные стандарты» задан правильный языковой стандарт. Сведения о том, как изменить языковой стандарт, см. в справке Windows.
• Данные могут содержать начальные пробелы, которые влияют на результаты сортировки. Для получения оптимальных результатов следует удалить такие пробелы перед сортировкой.
• Нельзя сортировать текстовые значения с учетом регистра символов.
• Нельзя сортировать данные по определенному признаку форматирования, такому как цвет ячеек или шрифта. Также нельзя сортировать данные по индикаторам условного форматирования, например наборы значков.

Сортировка меток строк или столбцов

1. В сводной таблице щелкните маленькую стрелку рядом со списком Названия строк или Названия столбцов.
2. Щелкните поле в строке или столбце, которые требуется отсортировать.
3. Щелкните стрелку в списке Названия строк или Названия столбцов, а затем выберите нужный параметр.

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

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

Сортировка по столбцу, который не имеет кнопки со стрелкой

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

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

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

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

См. также

• Создание сводной таблицы для анализа данных на листе
• Создание сводной таблицы для анализа внешних данных
• Создание сводной таблицы для анализа данных в нескольких таблицах
• Фильтрация данных в сводной таблице
• Группировка и отмена группировки данных в отчете сводной таблицы
• Применение условного форматирования в сводной таблице

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

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

Рис. 1. Сортировка по полю Заказчик: (а) по умолчанию – от А до Я; (б) в порядке уменьшения дохода; (в) порядок сортировки по полю Заказчик не изменился при добавлении поля Сектор

Скачать заметку в формате Word или pdf, примеры в формате Excel

Сортировка заказчиков в порядке убывания дохода

Чтобы отсортировать строки сводной таблицы в порядке убывания дохода, выберите любую ячейку столбца Сумма по полю Доход, например, Е4 (но не заголовок), и щелкните на значке ЯА, находящемся на вкладке Данные (рис. 2). Подобная сортировка напоминает стандартную, но это лишь внешнее сходство. При выполнении сортировки сводной таблицы Excel создает правило, которое будет работать и после внесения дополнительных изменений в сводную таблицу.

Рис. 2. Создание правила сортировки в порядке уменьшения дохода

На примере сводной таблицы, находящейся в столбцах G:I (рис. 1в), видно, что произойдет после добавления нового внешнего поля строки Сектор. Сводная таблица продолжает сортировать данные в порядке убывания дохода внутри каждого сектора. Например, в секторе Производство на первом месте находится компания General Motors с доходом 750 163 доллара. За ней следует компания Ford с доходом 622 794 доллара. Если даже удалить поле Заказчик из сводной таблицы, выполнить дополнительные настройки и вернуть это поле обратно, но уже в область столбцов, Excel запомнит сортировку заказчиков в порядке уменьшения дохода.

Чтобы в сводной таблице, находящейся в столбцах G:I (рис. 1в), секторы также были отсортированы в порядке убывания дохода, можно пойти одним из трех способов:

• Выделите ячейку G4, щелкните правой кнопкой мыши и выберите Свернуть всёполе, чтобы скрыть все элементы, которые относятся к заказчику. После того как на экране будут отображаться лишь одни секторы, выделите ячейку I4 и щелкните на значке ЯА на вкладке Данные для выполнения сортировки по убыванию. Таким образом, будет создано правило сортировки для поля Сектор. Повторно выделите ячейку G4, щелкните правой кнопкой мыши и выберите Развернуть всёполе.
• Временно удалите поле Заказчик из сводной таблицы, отсортируйте таблицу по убыванию дохода (методом, который был описан для рис. 2), а потом вновь верните поле Заказчик.
• Воспользуйтесь возможностями команды Дополнительныепараметрысортировки (я пользуюсь именно этим методом). Чтобы вызвать команду: (а) выделите ячейку G4, щелкните правой кнопкой мыши и выберите Сортировка → Дополнительныепараметрысортировки (рис. 3) или (б) кликните на значке треугольника в поле Сектор, а затем выберите пункт Дополнительныепараметрысортировки (рис. 4). В обоих случаях откроется окно Сортировка (рис. 5). Установите переключатель в положение по убыванию и выберите строку Сумма по полю Доход.

Рис. 3. Вызов команды Дополнительные параметры сортировки правой кнопкой мыши

Рис. 4. Вызов команды Дополнительные параметры сортировки с помощью меню Сортировка и фильтры поля Сектор

Рис. 5. Настройка параметров в окне Сектор

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

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

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

Чтобы выполнить такую сортировку:

1. Раскройте список Заказчик, находящийся в ячейке А4.
2. Выберите параметр Дополнительные параметры сортировки.
3. В диалоговом окне Сортировка (Заказчик) щелкните на кнопке Дополнительно…
4. В диалоговом окне Дополнительные параметры сортировки (Заказчик) выберите раздел Порядок сортировки и установите переключатель Значения в выделенном столбце.
5. Щелкните в поле ссылки, а затем выберите ячейку С5. Обратите внимание на то, что нужно щелкнуть в одной из ячеек значений Устройство, поскольку на заголовке Устройство в ячейке С4 щелкнуть невозможно.
6. Чтобы завершить установку параметров дважды кликните ОK.

Не пугайтесь, описание этого пошагового алгоритма приведено, скорее, в обучающих целях. Начиная с Excel 2013 сортировка данных сводной таблицы существенно упростилась. Теперь кнопки ЯА и АЯ на вкладке Данные используют интеллектуальные алгоритмы сортировки. При попытке выполнить сортировку с помощью этих кнопок программа попытается предугадать намерения пользователя, основываясь на том, какая ячейка была выделена перед нажатием кнопки сортировки (рис. 7):

• А1, С1, D1, Е1, F1, F2, А30, F30 – не доступны
• А2:А29 – расположит по алфавиту имена заказчиков в столбце А
• В1, В2, С2, D2, E2 – расположит по алфавиту названия товаров в строке 2
• В30, С30, D30, E30 – расположит по убыванию (возрастанию) суммы дохода в строке 30
• по возрастанию (убыванию) продаж В3:В29 – модулей, С3:С29 – устройств, D3:D29 – деталей, Е3:Е29 – препаратов, F3:F29 – итого.

Рис. 7. Интеллектуальные возможности сортировки данных сводной таблицы

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

Обратите внимание на то, что в диалоговом окне Сортировка (см. рис. 5) можно вручную определить правила сортировки данных. Но сортировка сводной таблицы вручную также выполняется другим, весьма необычным способом. В отчете сводной таблицы на рис. 8а показана последовательность категорий товаров, отсортированных в алфавитном порядке: Деталь, Модуль, Препарат и Устройство. Обратите внимание на то, что объем проданных товаров, относящихся к категории Деталь, не наибольший. И вряд ли стоит эту категорию отображать первой. Установите указатель мыши в ячейке Е4 и введите слово Деталь. Стоит лишь нажать клавишу Enter, как Excel определит, что вы решили переместить колонку Деталь в последний столбец таблицы. Все числовые значения, относящиеся к этой категории товаров, переместятся из столбца В в столбец Е. Значения, относящиеся к другим категориям товаров, сместятся влево. Подобное поведение выглядит нелогичным и присуще лишь сводным таблицам Excel. Обычный набор данных Excel переупорядочить таким образом не удастся. На рис. 8б показана сводная таблица после перемещения заголовка нового столбца в ячейку Е4.

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

Любители мыши могут просто перетаскивать заголовки требуемых колонок (или отдельные строки). Щелкните в области заголовка столбца и удерживайте указатель мыши над границей диапазона выделенных ячеек до тех пор, пока он не приобретет вид четырехнаправленной стрелки. Начинайте перетаскивать ячейку в выбранное место; появится указатель в виде жирной линии и засечками. Как только вы отпустите кнопку мыши, числовые значения тут же переместятся в новую колонку. Учтите, что при использовании ручной сортировки товары, добавляемые в источник данных, добавляются в конец списка. Это связано с тем, что программа Excel не знает, куда именно нужно добавить новый регион.

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

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

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

1. В свободной от данных области рабочего листа введите названия категорий товаров в последовательности, которая соответствует создаваемому пользовательскому списку. В каждой ячейке вводите по одному названию, а названия располагайте в одном столбце (рис. 9).
2. Выделите полученный список названий категорий товаров (ячейки А10:А13).
3. Выберите вкладку ленты Файл и в нижней части панели навигации, отображенной в окне слева, щелкните на кнопке Параметры для открытия диалогового окна Параметры Excel.
4. Выберите категорию Дополнительно, перейдите в раздел Общие и щелкните на кнопке Изменить списки.
5. В диалоговом окне Списки адрес диапазона, содержащего предварительно выделенный список названий, отображается в поле Импорт списка из ячеек (рис. 10). Щелкните на кнопке Импорт, чтобы сформировать новый список категорий товаров на основе указанных данных. Новый список добавляется в нижнюю часть области Списки.
6. Щелкните на кнопке ОК, чтобы закрыть диалоговое окно Списки. Щелкните еще раз на кнопке ОК для закрытия диалогового окна Параметры Excel.

Рис. 9. Заготовка для создания пользовательского списка

Рис. 10. Окно Списки

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

Рис. 11. Теперь все сводные таблицы будут сортироваться в соответствии с новым списком

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

1. Раскройте список поля Товар и выберите параметр Дополнительные параметры сортировки.
2. В диалоговом окне Сортировка (Товар) выберите кнопку по возрастанию (от А до Я) по полю, а в раскрывающемся списке выберите Товар.
3. Щелкните на кнопке Дополнительно…
4. В диалоговом окне Дополнительные параметры сортировки (Товар) отмените установку флажка Автосортировка.
5. Раскройте список Сортировка по первому ключу и выберите список, включающий названия категорий товара (рис. 12).
6. Дважды щелкните на кнопке ОК.

Рис. 12. Выбор сортировки в соответствии с пользовательским списком

[1] Заметка написана на основе книги Билл Джелен, Майкл Александер. Сводные таблицы в Microsoft Excel 2013. Глава 4.