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

У меня есть следующая таблица Excel:

     A              B              C              D    
1   Product        Category         Sale        Margin
2   Product A       Apparel          500          45%
3   Product A       Apparel          400          30%
4   Product A       Shoes            600          55%
5   Product B       Apparel          300          20%
6   Product C       Beauty           100          40%
7   Product C       Shoes            200          65%
8   Product D       Apparel          450          25%
9   Product D       Beauty           700          50%
10  Product D       Beauty           250          35%

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

   Profit = Sale * Margin

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

            Sum of Profit
Product A     1.950
Product B        60
Product C       315
Product D     1.540

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

              Sum of Profit
Product A        1.950
Product D        1.540
Product C          315
Product B           60

Похоже, что для вычисляемого поля параметры сортировки в PivotTable-Tools недоступны.

У вас есть еще идеи, как я могу добиться этой сортировки?

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

Источник

3 ответа

Решение

Щелкните правой кнопкой мыши по ячейке в Вычисляемом поле -> Сортировать -> Сортировать по возрастанию по убыванию.

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

Sub SortCalculatedField()
Dim ws As Worksheet
Dim pt As PivotTable

Set ws = Sheets("Sheet1")   'This is the sheet which contains Pivot Table
Set pt = ws.PivotTables(1)
pt.PivotFields("Product").AutoSort xlDescending, "Sum of Profit", pt.PivotColumnAxis.PivotLines(1), 1
End Sub
Источник

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

Источник

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

Затем добавьте меру (нажмите на ось, после чего должна появиться вкладка «Power Pivot» > «Добавить меры»). Вам нужно будет добавить формулу (аналогично тому, как вы создавали вычисляемый показатель). После этого вы сможете отсортировать свою меру/вычисленную меру. Если у вас есть столбцы, обязательно сделайте это перед добавлением столбцов.

Источник
Другие вопросы по тегам