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

Управление сводными таблицами в Excel

Сводная таблица – мощный инструмент Microsoft Excel. С ее помощью пользователь анализирует большие по объему диапазоны, подводит итоги всего в несколько кликов, выводит на экран только нужную в данный момент информацию.

Фильтр в сводной таблице Excel

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

Для примера возьмем следующую таблицу:

Создадим сводную таблицу: «Вставка» — «Сводная таблица». Поместим ее на новый лист.

Мы добавили в сводный отчет данные по поставщикам, количеству и стоимости.

Напомним, как выглядит диалоговое окно сводного отчета:

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

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

Например, среднее количество заказов по каждому поставщику:

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

Установим фильтр в сводном отчете:

1. В перечне полей для добавления в таблицу ставим галочку напротив заголовка «Склад».
2. Перетащим это поле в область «Фильтр отчета».
3. Таблица стала трехмерной – признак «Склад» оказался вверху.

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

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

Отфильтровать отчет можно также по значениям в первом столбце.

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

Немного преобразуем наш сводный отчет: уберем значение по «Поставщикам», добавим «Дату».

Сделаем таблицу более полезной – сгруппируем даты по кварталам. Для этого щелкнем правой кнопкой мыши по любой ячейке с датой. В выпадающем меню выберем «Группировать». Заполним параметры группировки:

После нажатия ОК сводная таблица приобретает следующий вид:

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

Значения в сводном отчете поменяются в соответствии с отсортированными данными:

Теперь выполним сортировку данных по дате. Правая кнопка мыши – «Сортировка». Можно выбрать способ сортировки и на этом остановиться. Но мы пойдем по другому пути. Нажмем «Дополнительные параметры сортировки». Откроется окно вида:

Установим параметры сортировки: «Дата по убыванию». Кликнем по кнопке «Дополнительно». Поставим галочку напротив «Автоматической сортировки при каждом обновлении отчета».

Теперь при появлении в сводной таблице новых дат программа Excel будет сортировать их по убыванию (от новых к старым):

Формулы в сводных таблицах Excel

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

1. Добавим в отчет заголовок «Поставщик». Заголовок «Стоимость» три раза перетащим в поле «Значения» — в сводную таблицу добавятся три одинаковых столбца.
2. Для первого столбца оставим значение «Сумма» для итогов. Для второго – «Среднее». Для третьего – «Количество».
3. Поменяем местами значения столбцов и значения строк. «Поставщик» — в названия столбцов. «Σ значения» — в названия строк.

Сводный отчет стал более удобным для восприятия:

Научимся прописывать формулы в сводной таблице. Щелкаем по любой ячейке отчета, чтобы активизировать инструмент «Работа со сводными таблицами». На вкладке «Параметры» выбираем «Формулы» — «Вычисляемое поле».

Жмем – открывается диалоговое окно. Вводим имя вычисляемого поля и формулу для нахождения значений.

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

Экспериментируйте: инструменты сводной таблицы – благодатная почва. Если что-то не получится, всегда можно удалить неудачный вариант и переделать.

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

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

Обратите внимание: При осуществлении сортировки данных стоит принимать во внимание указанные ниже данные:

• Выбранный язык воздействует на конечный порядок сортировки. Проверьте, выставлен ли в разделе “Язык и региональные стандарты” и “Региональные параметры” в панели управления соответствующий, корректный языковой стандарт. Детальную информацию об изменении языкового стандарта можно найти в справке Windows
• Финализированные данные могут вмещать в себя начальные пробелы, также оказывающие прямое воздействие на результаты сортировки. Чтобы получить оптимальные результаты, необходимо предварительно удалить такие пробелы прежде, чем приступать к сортировке данных
• Текстовые значения следует сортировать без учета регистра символов
• Сортировка по определенному признаку форматирования, например, по шрифту или цвету ячеек, также недопустима. В дополнение, не стоит сортировать данные по индикаторам условного форматирования (скажем, по набору значков).

В указанных ниже разделах можно найти данные о сортировке сводной диаграммы и сводной таблицы в классическом приложении Excel, входящим в состав офисного инструментария Office 2019 и более ранних изданий пакета.

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

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

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

1. Для сортировки данных в порядке убывания или возрастания, нажмите кнопки “Сортировка от Я до А” или “Сортировка от А до Я” соответственно.

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

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

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

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

Для просмотра общих итогов для продуктов, ранее отсортированных в порядке убывания, выберите любое число в колонке “ИТОГО” и выполните сортировку по его значению.

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

Задание собственных, персонализированных настроек сортировки

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

Сортировка вручную данных сводной таблицы Excel 2010

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

В отчете сводной таблицы на рис. 4.23 показана последовательность регионов по умолчанию. Эти регионы представлены в алфавитном порядке: Запад, Канада, Северо-Восток, Средний Запад,Юго-Восток и Юго-Запад. Если главный офис компании находится в Нью-Йорке, исторически устоявшиеся традиции будут требовать первостепенного вывода данных для региона Северо-Восток (перед данными из канадского, южного и северо-западного регионов). На первый взгляд может показаться, что нет эффективного способа отсортировать поле Регион в указанной последовательности. Порядок по возрастанию предполагает расположение данных для региона Северо-Восток первыми, но при этом данные для регионов Запад, Канада, Средний Запад, Юго-Восток и Юго-Запад не будут расположены в правильной последовательности, что нарушает принятый порядок расположения информации в отчетах компании.

Конечно, можно попытаться убедить руководство компании отказаться от устоявшихся традиций вывода отчетов в последовательности Северо-Восток, Юго-Восток, Средний Запад, Юго-Запад, Запад и Канада или даже изменить названия регионов, чтобы их порядок соответствовал историческим требованиям. Оба варианта малоприятны для руководства, а потому нереальны для выполнения. Однако компания Microsoft предлагает более простое решение возникшей проблемы.

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

Поместите указатель в ячейку А5 сводной таблицы и введите Северо-Восток. Программа предположит, что столбец Северо-Восток должен быть первым, а потому поместит его в строку 5. Объем продаж для региона Канада автоматически переместится в строку 11. Остальные регионы сместятся на последующие строки. В результате вы получите отчет, показанный на рис. 4.24, где в столбце А были введены новые заголовки полей.

Рис. 4.24. Введите слово Северо-Восток в ячейку А5, и последовательность отображения данных отчета изменится автоматически

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

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