大家好，Excel 比我厉害！非常感谢你们提供的任何帮助。我会尽量简短地讲：
我有一张包含三列的表格。月份（1、2、3 等）、销售人员姓名和销售额。每行代表销售人员当月的销售额。
它的排列方式如下：前 1000 行全部为 Jan/1，接下来的 1000 行全部为 Feb/2，等等。
我需要获取过去 3 个月每个销售人员的平均销售金额。但是——如果过去 3 个月中有一个是空白的，我仍然需要 3 个月的平均值。
因此，我需要公式来识别例如空白的 9 月结果，然后获取下一个最新结果（8 月、7 月，无论是什么），因此它始终平均该销售人员最近 3 个月的结果，而不仅仅是 1 个或 2 个结果。
目前我有一个非常简单的数据透视表。它对我来说效果很好，直到我意识到这个问题 :(
有没有什么方法（无论是否使用数据透视表）可以做到这一点？有超过 1000 名销售人员，因此手动操作不可行。由于某种原因，我有很多人一个月都没有数据，所以我需要围绕这个问题进行计算。
感谢您的帮助！如果我可以添加其他内容或内容不清楚，请告诉我。
答案1
我不知道有什么好办法可以平均最近三个非空白月份的数据，但这里有一个非常这是一种黑客式的方法。这可能会引发一些更好的方法来实现它。
在原始数据中创建一个表格，然后按月份（降序）和人员排序。
接下来，筛选“销售额”列并取消选择空白。然后将这些内容复制到另一个区域并粘贴。
然后创建一个“排名”列。单元格 D2 的公式，对每个人的每个月（最近的是 1，等等）进行排名。
=IF(B2=B1,D1+1,1)
然后在另一个区域内显示不同的人员（F 列），以及他们最近 3 个月的占位符列（标记为 1、2 和 3 的列）。
G2细胞公式：
=SUMIFS($C:$C,$B:$B,$F2,$D:$D,G$1)
将其拖到第 3 列标题上并向下移动。最后，创建平均值公式。
答案2
采用不同的方法，保持原始数据不变，不重新排序、过滤或复制，在数据列表中添加了 3 个辅助列，如下所示。
柱子排是一个简单的索引系列，从 1 开始，列人2是相同的人但使用 IF 函数在零行中插入空字符串销售量。计数提供计数，通过人，非零月份销售量。
计数与包含每个销售人员一行的结果列表交互，如下所示。
在结果列表中，列月份只是统计每个销售人员的数据列表中销售额不为零的月份数。单元格 I2 中的公式为
=COUNTIFS(A$2:A$21,H2,$C$2:$C$21,">0")
M1行显示排数据列表中每个销售人员的第一个月销售额不为零的值。单元格 J2 中的公式为
=MATCH(H2,$E$2:$E$21,0)
列月1，月2和第3个月列中的月份数字是计数（数据列表）对应于用于计算 3 个月平均值的第一、第二和第三个月，而销售1，销售2和销售3是这 3 个月的销售价值。平均的是计算出的 3 个月平均值。
K2、L2 和 M2 单元格的公式分别为
=I2-1
=K2+1
=L2+1
N2 单元格中的公式为
=SUMIFS($C$2:$C$21,$E$2:$E$21,$H2,$F$2:$F$21,K2)
并依赖于计数数据列表的列（范围$F$2:$F$21，见下文）。单元格 N2 被复制到单元格 O2 和 P2。
范围 I2:Q2 可以复制到结果列表的所有后续行。
数据列表-列计数
单元格 F2 中的公式是
=IF(C2>0,1,0)
这会将 0 或 1 放入单元格中。0 表示单元格 E2 中的人在单元格显示的月份中没有销售B2，而 1 表示这是此人的第一个销售月份。
单元格 F3 中的公式要复杂得多，并且
=IF(LEN(E3)=0,0,IF(VLOOKUP(E3,H$2:J$3,3,FALSE)=D3,1,1+MAXIFS(F$2:F2,E$2:E2,E3)))
如果该行对应于销售量为零。
否则，销售量不为零，并且触发第二个 IF。在第二个 IF 中人在结果表中查找单元格 E3 中的 M1行值（数据列表中对应于第一个月销售额的行号）匹配排单元格 D3 中的值，这意味着第 3 行对应于单元格 E3 中标识的人员的第一行非零销售额。
如果M1行和排值不同，数据表中的第 3 行对应于单元格 E3 中人员的后续销售月份（第一个月之后）。在这种情况下，月份编号是通过将数据列表前几行单元格 E3 中销售人员的 F 列最大值加 1 得到的。MAXIFS 函数用于确定此最大值。
单元格 F3 中的公式被复制到数据列表的后续行。公式中相对和绝对寻址的适当使用确保 MAXIFS 函数在副本中使用适当的单元格范围。
笔记
- 使用 MAXIFS 需要 Excel 2019 或更高版本
- 假设 (i) 数据按时间顺序排列，且 (ii) 每个销售人员和月份组合最多有一行非零的销售数据。
- 图中，蓝色字体表示公式，黑色字体表示静态值。