使用DAYS()函数拆分拨款金额时存在1天误差的技术问题
2026-7-7
按年份拆分拨款金额时的1天误差问题
我制作了一个使用DAYS()函数按年份拆分拨款金额的电子表格，但随机出现1天误差——即使添加额外天数也无法解决。
下方表格中，测试总计不等于100000的行即为错误行：
| 起始日期 | 结束日期 | 拨款金额 | 每日金额 | 测试总计 | 2021 | 2022 | 2023 | 2024 | 2025 | 2026 | 2027 | 2028 |
|---|---|---|---|---|---|---|---|---|---|---|---|---|
| 6/8/2021 | 11/6/2028 | 100000 | 36.9276218611521 | 100000 | 7644.01772526 | 13478.5819793 | 13478.5819793 | 13478.5819793 | 13478.5819793 | 13478.5819793 | 13478.5819793 | 11484.4903988 |
| 04/05/2023 | 12/12/2028 | 100000 | 48.1231953801732 | 100000 | 0 | 0 | 13041.385948 | 17564.9663138 | 17564.9663138 | 17564.9663138 | 17564.9663138 | 16698.7487969 |
| 04/06/2024 | 12/13/2026 | 100000 | 101.936799184506 | 100101.936799185 | 0 | 0 | 0 | 27522.9357798 | 37206.9317023 | 35372.069317 | 0 | 0 |
| 04/20/2021 | 04/21/2023 | 100000 | 136.798905608755 | 100136.798905609 | 35020.5198358 | 49931.6005472 | 15184.6785226 | 0 | 0 | 0 | 0 | 0 |
| 02/12/2023 | 03/12/2024 | 100000 | 253.807106598985 | 100253.807106599 | 0 | 0 | 81979.6954315 | 18274.1116751 | 0 | 0 | 0 | 0 |
| 02/12/2021 | 02/12/2022 | 100000 | 273.972602739726 | 100273.97260274 | 88493.1506849 | 11780.8219178 | 0 | 0 | 0 | 0 | 0 | 0 |
| 02/12/2024 | 02/12/2025 | 100000 | 273.224043715847 | 100273.224043716 | 0 | 0 | 0 | 88524.5901639 | 11748.6338798 | 0 | 0 | 0 |
| 01/01/2023 | 12/31/2023 | 100000 | 274.725274725275 | 100000 | 0 | 0 | 100000 | 0 | 0 | 0 | 0 | 0 |
以下是我使用的公式（已保存为命名函数）：
=IF(AND(YEAR(givendate) >= YEAR(startdate), YEAR(givendate) <= YEAR(enddate)), IF(YEAR(enddate) = YEAR(startdate), total, IF(YEAR(startdate) = YEAR(givendate), ((DAYS(givendate,startdate) + 1) * (total/ DAYS(enddate,startdate))), IF((YEAR(enddate) = YEAR(givendate)), (DAYS(enddate,(DATE(YEAR(givendate),1,1))) + 1) * (total/ DAYS(enddate,startdate)), 365 * (total/ DAYS(enddate,startdate))))),0)
我尝试在公式中加入TODAY()函数来解决该问题，修正了部分拨款的误差，但仍有其他拨款存在问题。
下方表格中，测试总计不等于100000的行即为错误行：
| 起始日期 | 结束日期 | 拨款金额 | 每日金额 | 测试总计 | 2021 | 2022 | 2023 | 2024 | 2025 | 2026 | 2027 | 2028 |
|---|---|---|---|---|---|---|---|---|---|---|---|---|
| 6/8/2021 | 11/6/2028 | 100000 | 36.9276218611521 | 99963.0723781389 | 7607.0901034 | 13478.5819793 | 13478.5819793 | 13478.5819793 | 13478.5819793 | 13478.5819793 | 13478.5819793 | 11484.4903988 |
| 04/05/2023 | 12/12/2028 | 100000 | 48.1231953801732 | 100000 | 0 | 0 | 13041.385948 | 17564.9663138 | 17564.9663138 | 17564.9663138 | 17564.9663138 | 16698.7487969 |
| 04/20/2021 | 04/21/2023 | 100000 | 136.798905608755 | 100000. | 34883.7209302 | 49931.6005472 | 15184.6785226 | 0 | 0 | 0 | 0 | 0 |
| 02/12/2023 | 03/12/2024 | 100000 | 253.807106598985 | 100253.807106599 | 0 | 0 | 81979.6954315 | 18274.1116751 | 0 | 0 | 0 | 0 |
| 02/12/2021 | 02/12/2022 | 100000 | 273.972602739726 | 100000 | 88219.1780822 | 11780.8219178 | 0 | 0 | 0 | 0 | 0 | 0 |
| 02/12/2024 | 02/12/2025 | 100000 | 273.224043715847 | 100000. | 0 | 0 | 0 | 88251.3661202 | 11748.6338798 | 0 | 0 | 0 |
| 01/01/2023 | 12/31/2023 | 100000 | 274.725274725275 | 100000 | 0 | 0 | 100000 | 0 | 0 | 0 | 0 | 0 |
以下是修改后的公式：
=IF(AND(YEAR(givendate) >= YEAR(startdate), YEAR(givendate) <= YEAR(enddate)), IF(YEAR(enddate) = YEAR(startdate), total, IF((YEAR(startdate) = YEAR(givendate)), (IF(YEAR(TODAY()) = YEAR(givendate), (DAYS(givendate,startdate) + 1) * (total/ DAYS(enddate,startdate)), DAYS(givendate,startdate) * (total/ DAYS(enddate,startdate)))), IF((YEAR(enddate) = YEAR(givendate)), (DAYS(enddate,(DATE(YEAR(givendate),1,1))) + 1) * (total/ DAYS(enddate,startdate)), 365 * (total/ DAYS(enddate,startdate))))),0)
内容的提问来源于stack exchange，提问作者GhostlyT
相关产品推荐
相关产品推荐
