我在一家非营利组织工作，我们正尝试开展一个项目，我们给人们寄明信片，庆祝他们为我们付出多少年。
我在 Windows 桌面上使用 Office 365 中的 Excel。我对 Excel 的了解程度可能介于中级和高级之间。这是我在这里的第一篇文章，所以我希望我做得正确。
以下是我正在处理的一小部分数据样本：
| ID | 礼物 5 | 礼物 4 | 礼物 3 | 礼物 2 | 礼物 1 | 期望值 |
|---|---|---|---|---|---|---|
| 0063532 | 2020 年 2 月 12 日 | 2020 年 2 月 12 日 | 2020 年 2 月 12 日 | 2019 年 12 月 23 日 | 2019 年 12 月 20 日 | 1 |
| 0038837 | 2020 年 2 月 12 日 | 2017 年 10 月 3 日 | 2016 年 12 月 1 日 | 2015 年 11 月 20 日 | 2014 年 9 月 18 日 | 1 |
| 0971229 | 2020 年 2 月 7 日 | 2019 年 6 月 30 日 | 2019 年 5 月 31 日 | 2019 年 3 月 18 日 | 2019 年 3 月 5 日 | 2 |
| 0113996 | 2020 年 2 月 12 日 | 2019 年 2 月 12 日 | 2016 年 11 月 29 日 | 2002 年 7 月 19 日 | 2 | |
| 0974727 | 2020 年 2 月 20 日 | 2020 年 2 月 20 日 | 2019 年 9 月 23 日 | 2018 年 10 月 2 日 | 2017 年 12 月 13 日 | 3 |
| 0176357 | 2020 年 2 月 12 日 | 2018 年 10 月 16 日 | 2018 年 6 月 13 日 | 2015 年 9 月 14 日 | 2014 年 10 月 29 日 | 3 |
| 0031280 | 2020 年 2 月 12 日 | 2019 年 2 月 4 日 | 2018 年 1 月 23 日 | 2017 年 1 月 25 日 | 2016 年 2 月 29 日 | 5 |
他们最近的捐赠必须是在过去 12 个月内，而且年份必须是连续的，所以如果他们在 2020 财年、2019 财年、2017 财年、2016 财年和 2015 财年捐赠，那么他们已经连续捐赠了 2 年，因为他们跳过了 2018 财年。就像我说的，最近的捐赠应该是在过去 12 个月内，所以如果有人在 2016 财年、2015 财年、2014 财年、2013 财年和 2012 财年捐赠，那么解决方案应该返回零，因为即使他们有 5 年的持续捐赠，他们也已经中断了连续捐赠。
另一个需要注意的是，我们希望以财政年度而不是日历年为基础。我们的财政年度从 7 月 1 日开始，到 6 月 30 日结束。
上面我提供了一些示例数据，但实际上我们有大约 25,000 名选民，其中一些人已经捐赠了 35 多年，通常每年捐赠多次。这里讨论的实际工作表有许多列捐赠日期（每份记录的礼物都有一列）。ID 列是每个捐赠者的唯一标识符。最右边的列是我希望解决方案返回的值，它不是工作表上实际存在的值。
到目前为止，我尝试实施一种解决方案，即我为每个财政年度创建新的列，然后搜索所有赠礼日期以查找发生在该财政年度的赠礼。如果有这样的赠礼，它会将日期放在列中，这样那里就会有某种标记。然后我得到了最后一行，从最左边的财政年度开始，开始计数，只要遇到包含值的单元格就会继续计数。
在给定的财政年度中搜索礼物的示例公式（我还有另一张表，其中包含每个财政年度的开始和结束日期，这就是所参考的内容）：
=IFNA(IFS(AND(B2<'fiscal year dates'!$B$2,B2>'fiscal year dates'!$A$2),B2,AND(C2<'fiscal year dates'!$B$2,C2>'fiscal year dates'!$A$2),C2,AND(D2<'fiscal year dates'!$B$2,D2>'fiscal year dates'!$A$2),D2,AND(E2<'fiscal year dates'!$B$2,E2>'fiscal year dates'!$A$2),E2,AND(F2<'fiscal year dates'!$B$2,F2>'fiscal year dates'!$A$2),F2),"")
计算最近连续财政年度数的示例公式：
=IF(IFERROR((MATCH(TRUE,INDEX(G2:K2="",0),0)-1),5)=0,"",IFERROR((MATCH(TRUE,INDEX(G2:K2="",0),0)-1),5))
此解决方案在 Excel 中的形式如下：
| ID | 礼物 5 | 礼物 4 | 礼物 3 | 礼物 2 | 礼物 1 | 2020财年 | 2019财年 | 2018 财年 | 2017 财年 | 2016 财年 | 连续的 |
|---|---|---|---|---|---|---|---|---|---|---|---|
| 0063532 | 2020 年 2 月 12 日 | 2020 年 2 月 12 日 | 2020 年 2 月 12 日 | 2019 年 12 月 23 日 | 2019 年 12 月 20 日 | 2020 年 2 月 12 日 | 1 | ||||
| 0038837 | 2020 年 2 月 12 日 | 2017 年 10 月 3 日 | 2016 年 12 月 1 日 | 2015 年 11 月 20 日 | 2014 年 9 月 18 日 | 2020 年 2 月 12 日 | 2017 年 10 月 3 日 | 2016 年 12 月 1 日 | 2015 年 11 月 20 日 | 1 | |
| 0971229 | 2020 年 2 月 7 日 | 2019 年 6 月 30 日 | 2019 年 5 月 31 日 | 2019 年 3 月 18 日 | 2019 年 3 月 5 日 | 2020 年 2 月 7 日 | 2019 年 5 月 31 日 | 2 | |||
| 0113996 | 2020 年 2 月 12 日 | 2019 年 2 月 12 日 | 2016 年 11 月 29 日 | 2002 年 7 月 19 日 | 2020 年 2 月 12 日 | 2019 年 2 月 12 日 | 2016 年 11 月 29 日 | 2 | |||
| 0974727 | 2020 年 2 月 20 日 | 2020 年 2 月 20 日 | 2019 年 9 月 23 日 | 2018 年 10 月 2 日 | 2017 年 12 月 13 日 | 2020 年 2 月 20 日 | 2018 年 10 月 2 日 | 2017 年 12 月 13 日 | 3 | ||
| 0176357 | 2020 年 2 月 12 日 | 2018 年 10 月 16 日 | 2018 年 6 月 13 日 | 2015 年 9 月 14 日 | 2014 年 10 月 29 日 | 2020 年 2 月 12 日 | 2018 年 10 月 16 日 | 2018 年 6 月 13 日 | 2015 年 9 月 14 日 | 3 | |
| 0031280 | 2020 年 2 月 12 日 | 2019 年 2 月 4 日 | 2018 年 1 月 23 日 | 2017 年 1 月 25 日 | 2016 年 2 月 29 日 | 2020 年 2 月 12 日 | 2019 年 2 月 4 日 | 2018 年 1 月 23 日 | 2017 年 1 月 25 日 | 2016 年 2 月 29 日 | 5 |
这个解决方案有效，我只担心如何在我的整个数据集上实施它，因为我将有 35 个财政年度列和数百个礼品日期列。我还担心每个月都要重复这个过程。做一次可能还好，但每个月都把它应用到这个报告中来检查周年纪念日就不太合理了。
我觉得这应该是 excel 能够做到的事情，但我不知道如何有效地做到这一点。请告诉我你的想法！我愿意接受任何类型的解决方案。几个月来，它一直让我抓狂。
答案1
我也在 Reddit 上发布了此帖，那里有人回答了我的问题。以下是答案的链接：https://www.reddit.com/r/excel/comments/l74rsa/calculating_consecutive_fiscal_years_giving/