Excel 查找表并根据行差异匹配值

Excel 查找表并根据行差异匹配值

我正在构建一个每日仪表板,我需要将每日摄入量乘以一个特定值,该值在每日摄入量超过特定等级数字时会发生变化。我目前面临的挑战是,当某一天达到等级数量时,我无法乘以正确的等级乘数。这意味着较高等级乘数将分摊到当天的所有每日摄入量,而不是当天最终达到较高等级的差额。任何帮助都将非常有帮助!谢谢。

提供了一个工作示例:

1)我有一张包含乘数(投资)以及起始和终止层的表格。

投资表

2) 11 月 12 日的入学人数达到上限。如果你把这些数字加起来,就会发现 11 月 11 日是 464,而 11 月 12 日是 506,这意味着 11 月 12 日的 42 名入学学生中,有 11 名应该乘以 150 美元,而不是 200 美元,而我目前的公式并不会这样计算

入学层

3) 将摄入量乘以上表中“第二层”的数值=IF(E6="",$V$3*$D6,$V$4*D6)

投资层

答案1

这是修改后的答案。下表显示了正确计算的 11 月 12 日的投资。

在此处输入图片描述

F2中的公式为:

=IF(SUM(D$2:E2)>475,IF(E1="",50*(SUM(D$2:D2)-475)+150*D2,200*D2),150*D2)

大部分内容都很简单。如果 D 列的累计总和 (SUM(D$2:Dnn) 小于 475,则投资额为 150*Dnn。如果累计总和大于 475E列上一行不为空,则投资额为200*Dnn。

如果累计和大于 475,且 E 列的上一行空白(11月12日),则摄入量的一部分乘以200,剩余部分乘以150。

收入中要乘以 200 的部分是累计金额超过 475 的部分 - 这只是累计金额减去 475。因此,投资的这一部分是 。D200*(SUM(C$2:Cnn)-475)栏中的金额余数是 Dnn 减去超出的金额或Dnn-(SUM(C$2:Cnn)-475),这是要乘以 150 的部分。将累计金额表示为“Sum”,则总投资为:

200*(Sum - 475) + 150*(Dnn-(Sum-475))

这个表达式可以在公式中使用,但可以简化。简化后得到:

200*Sum - 200*475 + 150*Dnn - 150*Sum + 150*475
50*Sum - 50*475 + 150*Dnn
50*(Sum-475) + 150*Dnn

这是内部 IF() 语句中的“如果为真则为值”:

50*(SUM(D$2:D2)-475)+150*D2

我希望这会有所帮助,并且请原谅我上述评论中的错误开头。

相关内容