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

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

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

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

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

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

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

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

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

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

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

Сортировка по промежуточным итогам

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

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

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

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

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

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

Как вы можете заметить, на юге самый высокий объем заказов, а на Севере самый низкий.

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

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

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

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

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

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

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

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

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

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

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

  • Щелкните по значениям в выбранном столбце: в разделе Сортировать по.
  • В поле под этим введите B5.

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

  • Отсортируйте регион по сумме суммы заказа в порядке возрастания, используя значения в этом столбце: Январь. Нажмите OK.
  • Откроется диалоговое окно Сортировка (регион). Выберите по убыванию (от Z до A) по: в разделе Параметры сортировки.

В разделе «Сводка» текущий порядок сортировки указан следующим образом −

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

Как вы можете заметить, в январе месяце на West приходится наибольшее количество заказов, а на North — наименьшее.

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

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

Наведите курсор на стрелку Стрелка вниз в метках строк или столбцов.

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

  • Щелкните стрелку Стрелка вниз в метках строк.
  • Выберите регион в поле Выбора поля из выпадающего списка.
  • Выберите Дополнительные параметры сортировки. Появится диалоговое окно Сортировка (регион).
  • Выберите Руководство (вы можете перетаскивать элементы, чтобы изменить их порядок).
  • Нажмите OK.

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

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

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

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

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

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

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

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

  • Щелкните стрелку Стрелка вниз в метках строк.
  • Выберите регион в поле Выбора поля.
  • Щелкните Дополнительные параметры сортировки. Появится диалоговое окно Сортировка (регион).
  • Нажмите кнопку Дополнительные параметры.

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

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

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

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

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

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

Вы можете создавать собственные списки на вкладке ФАЙЛ на ленте. ФАЙЛ → Параметры. В диалоговом окне Параметры Excel нажмите дополнительно и перейдите к общим. Вы найдете кнопку Редактировать пользовательские списки рядом с разделом Создавать списки для использования в последовательностях сортировки и заполнения.

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

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

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

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

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

Обучаю основам работы с офисными программами.

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