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

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

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

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

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

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

• Нажмите стрелку в строке ярлыков.

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

Нажмите стрелку в строке ярлыков.

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

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

• Сортировать от А до Я.
• Сортировка от Z до A.
• Дополнительные параметры сортировки.

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

Таким же образом вы можете отсортировать поле в столбце — Месяц, нажав на стрелку в метках столбцов.

Сортировка по подытогам

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

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

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

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

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

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

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

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

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

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

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

• Нажмите Сортировка в раскрывающемся списке.

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

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

Нажмите Сортировка в раскрывающемся списке.

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

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

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

• Щелкните правой кнопкой мыши по любому из промежуточных итогов в итоговой строке.
• Выберите Sort из выпадающего списка.
• Выберите Sort Largest to Smallest из второго выпадающего списка.

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

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

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

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

• Нажмите на стрелку в строке ярлыков.

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

Нажмите на стрелку в строке ярлыков.

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

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

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

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

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

• Нажмите Значения в выбранном столбце: под Сортировать по.

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

Нажмите Значения в выбранном столбце: под Сортировать по.

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

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

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

• Откроется диалоговое окно Sort (Region). Выберите По убыванию (от Z до A) по: в разделе Параметры сортировки.

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

Откроется диалоговое окно Sort (Region). Выберите По убыванию (от Z до A) по: в разделе Параметры сортировки.

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

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

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

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

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

Поместите курсор на стрелку в метках строк или метках столбцов.

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

• Нажмите стрелку в строке ярлыков.

• Выберите Region в поле Select Field из выпадающего списка.

• Нажмите Дополнительные параметры сортировки . Откроется диалоговое окно Sort (Region).

• Выберите Вручную (вы можете перетаскивать объекты, чтобы изменить их порядок).

• Нажмите ОК.

Нажмите стрелку в строке ярлыков.

Выберите Region в поле Select Field из выпадающего списка.

Нажмите Дополнительные параметры сортировки . Откроется диалоговое окно Sort (Region).

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

Нажмите ОК.

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

Нажмите на восток и перетащите его наверх. При перетаскивании на восток горизонтальная зеленая полоса появляется на всем протяжении ряда строк.

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

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

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

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

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

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

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

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

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

• Нажмите стрелку в строке ярлыков.

• Выберите Region в поле Select Field.

• Нажмите Дополнительные параметры сортировки. Откроется диалоговое окно Sort (Region).

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

Нажмите стрелку в строке ярлыков.

Выберите Region в поле Select Field.

Нажмите Дополнительные параметры сортировки. Откроется диалоговое окно Sort (Region).

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

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

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

• Снимите флажок — сортировать автоматически при каждом обновлении отчета.

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

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

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

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

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

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

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

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

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

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

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

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