☰

在 Excel 中计算每个组的平均年增长率

tag-icon • tag-icon microsoft-excel worksheet-function microsoft-excel-2010
在 Excel 中计算每个组的平均年增长率

我有一个 Excel 文件，其中的列如下，显示了客户的年度营业额数据（实际文件有数千名客户）：


  customer   year   turnover  
 ---------- ------ ---------- 
       124   2018       8912  
       124   2019        324  
       124   2020       1235  
       533   2019      32498  
       533   2020      34599  
       272   2018        123  
       272   2019        534  
       272   2020        475

我想计算每个客户营业额的平均年增长率，得到如下所示的结果（增长率是虚构的，与实际增长率不符）：


  customer   average_annual_growth_rate  
 ---------- ---------------------------- 
       124   -23%                        
       533   12%                         
       272   35%

我认为我可以这样做，即使用源数据生成一个数据透视表，创建两列营业额，然后更改第二列以显示与之前的百分比差异。但是我无法用这种方式计算每个客户的平均年利率。数据透视表如下所示：

枢

过去有人做过类似的事情吗？创建一个平均年增长率，其中年数因客户而异？

你能帮忙吗？谢谢！

答案1

如果我误解了你的问题，我认为你离解决方案已经很近了。创建数据透视表后，插入客户作为行，营业额百分比作为值。然后单击营业额百分比按钮并选择“值字段设置”

图片:

在打开的窗口中，选择“平均值”，然后单击“确定”。数据透视表上将显示每个客户的平均营业额百分比。

在此处输入图片描述

答案2

这解决了这个问题：

在此处输入图片描述

怎么运行的：

  • 要计算年增长率，单元格 D16 中的公式为：

    =(C16-C15)/C15

  • 单元格 D24 中的公式计算出总体增长率，是一项可选练习，您可以跳过它。

    =AVERAGE(D16:D22)

  • 在单元格 F15 中输入此数组（CSE）公式，获取唯一的客户列表。

     {=IF(INDEX(A15:A22,MATCH(0,COUNTIF($F$14:F14,A15:A22),0))=0,"",INDEX(A15:A22,MATCH(0,COUNTIF($F$14:F14,A15:A22),0)))}

  • 单元格 G15 中的数组（CSE）公式可查找客户的 AAGR：

    {=IF(ISERROR(AVERAGE(IF($A$15:$A$22=$F15,$D$15:$D$22))),"",AVERAGE(IF($A$15:$A$22=$F15,$D$15:$D$22)))}

注意：

  • 您还可能会发现多年来的 AAGR。
  • 完成配方Ctrl+Shift+Enter。
  • 根据需要调整公式中的单元格引用。

相关内容