我有一个使用“sumif”函数并计算日期范围内的月份的电子表格；
=SUMIFS(金额;DPIF;">=1/1/2021";DPIF;"<=31/1/2021")
此公式重复十二次（每个月），随着 2021 年到 2022 年的变化，我现在必须手动更改所有十二个公式中的日期（年份）范围。
我尝试仅更改年份（公式中的 2021）以引用另一个单元格，但 Excel 不再将其视为日期。
我怎样才能使我的电子表格在下一年仍然可用？
答案1
方法 1
不要在SUMIFS函数中对开始/结束日期进行硬编码，而是使用 Excel 公式生成它们，然后引用包含这些计算日期值的单元格。DATE和EOMONTH函数的使用方式如下面的屏幕截图所示。
一月份的公式SUMIFS可以改为：
=SUMIFS(Amount;DPIF;">="&Sheet1!B3;DPIF;"<="&Sheet1!C3)
对于一月份，单元格Sheet1!B3包含该月份的开始日期和Sheet1!C3结束日期。将这些单元格引用更改为您放置计算日期值的位置。对于二月份，开始/结束日期单元格为Sheet1!B4和Sheet1!C4，对于三月份，开始/结束日期单元格为Sheet1!B5和Sheet1!C5，等等。如果您的 12 个月SUMIFS公式排列为 12 行 1 列的范围，您将能够将一月份公式复制到接下来的 11 个月。
公式中的 & 符号分别将比较运算符与引用单元格中的值连接起来，因此在改变后的公式中">="&Sheet1!B3实际上相当于">=01/01/2024"因为Sheet1!B3传递的日期值为 01/01/2024。
要更改年份，只需更改相应单元格中的年份值（Sheet1!B1屏幕截图中的单元格）。您的 12 个月SUMIFS公式将全部自动引用新年的开始/结束日期，无需编辑公式。
这种方法还可以管理闰年二月的月底日期（如单元格中的屏幕截图所示C4）。
方法 2
另一种方法是使用以下方法将DPIF日期值转换为月份数值：在现有Amount/DPIF范围中添加额外的范围MONTH充当
=MONTH(date)
其中date代表范围内的日期单元格DPIF。
调用这些月度值MPIF意味着您可以使用以下公式
=SUMIFS(Amount;MPIF;1)
=SUMIFS(Amount;MPIF;2)
...
=SUMIFS(Amount;MPIF;12)
完全不用担心年份。
这种按月计算的方法假设您的数据范围中没有多个年份。如果有，它将汇总所有一月份、所有二月份等的数据，这可能不是您想要的。
在这种情况下，解决方案的改进是YPIF使用YEAR函数来获取这些值。然后，您可以向公式添加第二个条件SUMIFS以包括这些年份值，例如
=SUMIFS(Amount;MPIF;1;YPIF;2024)
当然，这意味着您每年仍需要修改 12 个公式，尽管编辑内容比您目前的编辑内容略微简单。为了避免这种情况，请将年份值放在其自己的单元格中，并使用以下公式引用它：
=SUMIFS(Amount;MPIF;1;YPIF;Sheet1!$B$1)
方法 3
不是一个推荐，但为了完整性，请将您的公式更改为
=SUMIFS(Amount;DPIF;">=01/01/"&Sheet1!$B$1;DPIF;"<=31/01/"&Sheet1!$B$1)
=SUMIFS(Amount;DPIF;">=01/02/"&Sheet1!$B$1;DPIF;"<=28/02/"&Sheet1!$B$1)
ETC
这涉及一次性编辑工作，该工作将适用于 2021 年、2022 年和 2023 年，但将从 2024 年 2 月的月度总数中省略 2024 年 2 月 29 日，如果不改回来，可能会在 2025 年造成问题。
答案2
- 将两个完整的日期（不仅仅是年份）分别放在一个单独的单元格中。然后在公式中，用对单元格的引用替换日期值
- 要一次更改所有公式，请在工作表中运行“全部替换”
- 明年，仅更改两个单独单元格中的日期值