我们在Excel中有如下所示的数据:
| A | B | C |
—————————————————————————————————————————
1 | Amount | % Complete 1 | % Complete 2 |
2 | $ 1,000 | 25% | 50% |
3 | $ 600 | 50% | 100% |
4 | $ 2,500 | 75% | 100% |
每个订单项都是我们同意支付的任务的成本,每个"完成百分比"列是截至特定日期的完成百分比。 我们汇总截至该日期的欠款金额以根据百分比支付,这意味着对于第一个"完成百分比"列,我们可以使用数组公式计算欠款金额{=SUM(A2:A4*B2:B4)}
.
但是,后续列只需支付当前应付金额与之前已支付金额之间的差额。
使事情进一步复杂化:
- 如果没有更改,数据将为空。 例如,如果在第一次检查时完成了 25%,而在第二次检查时仍完成了 25%,则第二次检查列中的相应单元格将为空。
- 百分比可能会下降,导致要支付的金额为负数。 例如,如果第一次检查完成了50%,但进度倒退,那么第二次检查可能为25%,这意味着钱应该退还给我们。
因此,对于第一列之后的"完成百分比"列,我需要当前列之前"完成百分比"列中同一行中的最后一个非空白单元格。使用此解释,我能够创建一个公式:
=$A2*(C2-LOOKUP(2,1/($B2:B2<>""),$B2:B2))
这将计算特定单元格的应付金额,因此在上面的示例中,它将产生 250 美元:1,000 美元 * (50% - 25%(。当您继续操作时,它将继续工作,并将处理查找最后一个非空白列。
我想在数组公式中使用上述公式,但不知道该怎么做。
因此,这就引出了一个问题:当一个公式中已经有一个范围(即上面公式中的$B2:B2
(时,是否可以在数组公式中使用它,如果是,如何? 在普通公式中,您可以将单元格引用(例如,A1
(隐藏到范围(例如,A:A
(中,但是是否有某种语法可以在范围内使用范围?
也很乐意考虑完全不同的方法来解决此问题,但是由于此电子表格的使用方式,不幸的是我们不能使用VBA。
为了回答您帖子的最后一部分,我假设您将定期更新和计算进度和付款。在这种情况下,您需要做的就是将当前期间与上一周期进行比较。如果是这种情况,以这样的形式使用 IF(( 函数应该可以达到目的。
=IF(Compl2>Compl1,Compl2*Rate,(Compl2*Rate-Compl1*Rate))