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

Question

Я хочу рассчитать среднюю прибыль / убыток в сводной таблице Excel. Допустим, исходные данные находятся в таблице Excel и выглядят так

Date   Net income
1/1    5
1/1    -3
1/1    7
1/2    -10
1/2    4

Мое решение состоит в том, чтобы добавить 2 дополнительных столбца с формулой: IF([@Net income]>=0,[@Net income],0) и IF([@Net income]<0,[@Net income],0)

Date  Net income   Profit    Loss
1/1   5            5         0
1/1   -3           0         -3
1/1   7            7         0
1/2   -10          0         -10
1/2   4            4         0

Затем в производную сводную таблицу я могу добавить 2 столбца со средним значением столбца "Прибыль" и "Убыток" из исходной таблицы.

Pivot table    Avg Profit     Avg Loss
1/1            4 (=12/3)      -1 (=-1/3)
1/2            2 (=4/2)       -5 (=-10/2)

Мне было интересно, есть ли лучший способ, поэтому мне не нужно добавлять 2 дополнительных столбца "Прибыль" и "Убыток". Можно ли в этом случае использовать рассчитанные поля / элементы?

Answer 1

К сожалению, опция вычисляемых полей для сводных таблиц будет иметь дело только со всей полнотой данных; Я имею в виду, что вы не можете фильтровать данные по значению после того, как создали сводную таблицу. Так что averageif и if функции должны иметь дело с field а не с cells , в противном случае это было бы возможно с вычисляемым полем.

Вы можете сократить эти 2 столбца до одного с помощью формулы столбца флага C2=if(B2< 0, "Loss", "Gain") а затем использовать это в качестве фильтра в сводной таблице.

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

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