![#1](https://linux22.com/image/1614782/%231.png)
(首先:如果你能想出一个更好的标题,我们会很欢迎)
我有以下情况:为全年设定了预算。除非已知,否则这应该平均分摊到所有月份。但您应该能够手动输入您已经知道花费(或没有花费)的月份的值。问题是,当我尝试这样做时,它总是出现循环引用(无法修复它,因为 excel 找不到确切的引用)。
我尝试使用以下公式来计算余数除以剩余月份:
=($B3-SUMPRODUCT(--NOT(ISFORMULA($C3:$N3));$C3:$N3))/(12-SUMPRODUCT(--NOT(ISFORMULA($C3:$N3))))
B3 是全年预算总额(最左边),C3 至 N3 是月份(1 月至 12 月;最右边是预算)。右侧的白色单元格对本题不重要
答案1
我现在自己想出了几个解决方案(这离不开您的帮助,非常感谢),它们都有一些缺点和优点。
#1
此解决方案不需要额外的单元格,但缺点是您只能按顺序(从左到右)填写单元格,如果不这样做,您将得到错误的解决方案。
单元格 E3 的公式如下=IF(ISFORMULA(D3);D3;($B3-IFERROR(SUM(OFFSET($C3;0;0;1;(MONTH(E$2)-1)));0))/(13-MONTH(E$2)))
,引用应该在图像中可见。
#2
此解决方案需要第二个辅助行。
图像
顶行的公式为 (示例单元格:E7) =MAX(0;($B7-SUMIFS($C7:$N7;$C8:$N8;0))/(SUM($C8:$N8)))
,而底行仅包含以下内容:--ISFORMULA(C7)
,C7 是对顶部单元格 (C6) 的引用。此解决方案运行良好,但由于需要第二行,因此使用起来很烦人。
#3
此解决方案与 #2 类似,但不需要第二行。相反,将“辅助行”放在另一张表上(如 @gns100 所建议的那样)。
该表仅告诉您第一张表上的相同单元格是否是公式或通过--ISFORMULA(Sheet1!C7)
(对于单元格 C7)。
那么第一张表中对应的公式将是:=MAX(0;($B13-SUMIFS($C13:$N13;Sheet2!$C13:$N13;0))/(SUM(Sheet2!$C13:$N13)))
缺点当然是需要另一张纸。