最近在学习零售规律，遇到excel中按固定天数画平均线，在python中比较好做，excel中一直不知道方法，网上找了好久也没有找到满意方法，今天做出来了，记录下，方便自己以后回忆，也方便大家有需求的，可以直接看。
假设有这么一数据:
数据集.png
做出曲线图是这样的：
支付金额曲线图.png
不太方便看出销售规律，加上平均线就方便了：
支付金额加平均线趋势.png
要做平均线当然要做平均值了。
我们正常的平均值很容易算：
=AVERAGE(B2:B8)
直接向下拉：
平均值1.png
后面的值都不一样，我们要的是固定天数一样的平均值，这显示不对，怎么办？在2和8上加dollar符号试试。
=AVERAGE(B$2:B$8)
平均值2.png
这次倒全是一样了，可是超过需求天数后的值也没变，这显示也是不对的。
由于我平时对python比较熟悉，心想excel肯定也有能够做固定循环的函数吧。我们需求是什么？求固定天数的平均值，假设是7天（一周），也就是每7天一个组，分别计算，手工计算到是快，就是数据多了，填写太麻烦了。
baidu了一下，找到了这样的公式：
=IF(MOD(COUNT($A$1:A1),7)=0,AVERAGE(OFFSET(A1,-6,0,7,1)),"")
里面有OFFSET引起了我的兴趣，由于我平时没用过，先研究下吧。
百度上是这么解释的:
OFFSET(参考单元格，移动的行数，移动的列数，所要引用的行数，所要引用的列数）
这个函数肯定是我们要用的，但是怎么确定让组内的平均值保持一致呢？
用OFFSET这个函数，也就是要保证每组开始的行数始终是这组开始的第一个值。也就是：
变量演示.png
这下清楚了，要得到函数所需的行数，就是要得到所在行的行号ROW()，用所在行的行数除以7取向上整数乘7再减去7就对了。
CEILING(ROW(B2)/7,1)*7-7
这个整个偏移函数就出来了：
=AVERAGE(OFFSET($B$1,CEILING(ROW(B2)/7,1)*7-7,,7))
C列平均值.png
最后再展示下加上平均值的效果：
支付金额加平均线趋势.png
希望能帮到大家！
如有更方便的办法，希望大家能够指出，不胜感激！
网友评论