时间戳

首页 > 解决方案 > 如何通过循环遍历excel中的列表来总结过去3个月？

excel - 如何通过循环遍历excel中的列表来总结过去3个月？

问题描述

我在excel中有一个每月1到12的值列表。当季度在 12 月结束时，它应该对季度 10 月、11 月和 12 月的值求和，即第 10、11 和 12 月。当前使用的函数是偏移函数 =SUM(OFFSET(INDEX($F$999:$F$1008,MATCH(MONTH($B1013),$E$999:$E$1008)),0,0,-3,1))。当起始位置为 Mar 时，该公式适用，六月、九月和十二月。但是，假设季度结束月份是一月。那么使用上述函数只会对一月的值求和，因为上面没有两个月前的值。

期望的结果：如果季度在 1 月结束，那么它应该循环遍历列表并总结 11 月、12 月和 1 月。目标是执行敏感性分析，因为季度结束月份可以是任何月份，并且基于季度结束月份它将总结该季度的所有值。

该列表如下所示，因此如果该季度在 2 月结束，则应将 (12 月) 10% + (1 月) 3% + (2 月) 6% = 19%

Jan 1   3%
Feb 2   6%
Mar 3   2%
Apr 4   9%
May 5   5%
Jun 6   8%
Jul 7   3.2%
Aug 8   12%
Sep 9   6.5%
Oct 10  5.6%
Nov 11  8%
Dec 12  10%

标签： excelvba

解决方案

诀窍是生成一个与所需月份匹配的数字序列。

请注意，在您的表中，月份数 = 表中的索引。

将生成所需序列的公式，例如，如果您的季度结束月份是一月（例如月份数 1）=>{11,12,1}是

  Office 365:  =SUM(INDEX(monthly,MOD(SEQUENCE(3, ,F2+12-2)-1,12)+1,3))
  
  older versions:  =MOD(ROW(INDEX($A:$A,1):INDEX($A:$A,3))+F2+12-4,12)+1

其中F2包含结束季度的月份数。

所以对于 O365：

=SUM(INDEX(monthly,MOD(SEQUENCE(3, ,F2+12-2)-1,12)+1,3))

  or      :  =SUM(XLOOKUP(MOD(SEQUENCE(3, ,F2+12-2)-1,12)+1,monthly[monthNum],monthly[perCent]))

monthly你的桌子的名字在哪里

在此处输入图像描述

对于早期版本，请尝试：

=SUM(INDEX(monthly,MOD(ROW(INDEX($A:$A,1):INDEX($A:$A,3))+F2+12-4,12)+1,3))

甚至：

=SUM(INDEX(monthly,N(IF(1,MOD(ROW(INDEX($A:$A,1):INDEX($A:$A,3))+F2+12-4,12)))+1,3))

在某些早期版本的 Excel 中，您可能需要通过按住+的同时点击来“确认”这个数组公式。如果您正确执行此操作，Excel 将在公式周围放置大括号，如公式栏中所示ctrlshiftenter{...}

推荐阅读