基于具有特定阈值的可变百分比带的单元格值计算公式

Question

我有一个带有两个选项卡的谷歌单张 --一个包含百分“带”值，另一个包含表中的数据，其中包括新条目的行和右边的列，这些行根据输入类型存储运行总计。运行总计取决于同类型和同月期间的行项。这一切都如预期的那样起作用。

我需要根据行条目数量/单元格(列I )计算列H中的值，该值引用该条目类型AA:AF和月份的运行总数，然后使用相关的预定义百分比“带”值(选项卡R1)。

通过使用SWITCH和VLOOKUP函数，当一个条目只跨越一个“波段”级别时，我已经成功地实现了这个工作。

列I中的当前公式使用的方法不再起作用，因为百分比带现在比以前更接近了，单个条目可以在多个带上获取该条目的运行总值(而不像以前那样仅接受前一个带)。

在示例表中，单元格H6包含9,900个测试值，因为这将该行AB6的运行总数从该类型的前一次运行总数( 6,413 )增加到16,313，并且跨越4个百分比范围：

A级: 0-7,500 - 5%

B级: 7,500-10,000 - 7.5%

C级: 10,000-15,000 - 15%

D级: 15,000 - 25,0000 - 17.5%

我的原始公式首先使用开关检查entry Type，然后使用VLOOKUP匹配最高的“带”值，然后使用IF检查上一次运行的总数是否小于最高匹配的“带”值，并在需要时计算和添加差异。

我已经尝试了如何计算多个带交叉时的相同结果(例如)，但我无法找到一种方法来构造公式，以便它可以使用匹配的带率、以前的运行总计和新的运行总计值在列中普遍应用。

是否有一种数学方法来做到这一点，或者这需要多个嵌套的IF语句等，或者另一种方法会更好吗？

Answer 1

我通过修改此页上的公式来解决这个问题。改变乐队的布局是很好的第一步。

现在，列I从当前运行的总数(与A列中的类型匹配)中计算值，并以相同的方式减去计算的值，但使用上一次运行总计来给出适用于H列中同一行中新输入的值的金额。我还要做更多的测试，但相当肯定它是正确工作的。任何其他的想法，请随时建议！

临时工作表：FwRvkHyYw/编辑?usp=共享