☰

查找最后 2 个值并计算变化百分比

tag-icon • tag-icon microsoft-excel vlookup array
查找最后 2 个值并计算变化百分比

我需要计算最后 2 个单元格（月份）的百分比变化。最后 2 个单元格每月都会发生变化，并添加了新数据。我需要公式来查找行中的最后 2 个单元格（具有值），并计算变化。我尝试了以下公式，但输出错误 #Value！

=(-INDEX(C48:O48,MATCH(TRUE,C48:O48<>0,0))+LOOKUP(2,1/(C48:O48>0),C48:O48))/INDEX(C48:O48, MATCH(TRUE,C48:O48<>0,0))

答案1

这是围绕使用LET() 函数并非所有版本的 Excel 都支持此功能。您可以在没有此功能的情况下执行此操作，但公式会变得非常长，并且更难理解。

如果您的数据中没有任何间隙，并且我们可以指望倒数第二个值就在最后一个值的左边，那么公式可以是这样的：

=LET(monthRange,C48:O48,cBeg,MIN(COLUMN(monthRange)),cEnd,LOOKUP(2,1/(monthRange<>""),COLUMN(monthRange)),lastValue,INDEX(monthRange,cEnd-cBeg+1),nextToLastValue,INDEX(monthRange,cEnd-cBeg),(lastValue-nextToLastValue)/nextToLastValue)

如果您可能有空白，并且您不想研究倒数第二个非空白和最后一个非空白之间的区别，则公式会变得更长，但它是最强大的解决方案：

=LET(monthRange,C48:O48,cBeg,MIN(COLUMN(monthRange)),cEnd,LOOKUP(2,1/(monthRange<>""),COLUMN(monthRange)),allButLastRange,OFFSET(monthRange,0,0,1,cEnd-cBeg),cNextToEnd,LOOKUP(2,1/(allButLastRange<>""),COLUMN(allButLastRange)),lastValue,INDEX(monthRange,cEnd-cBeg+1),nextToLastValue,INDEX(monthRange,cNextToEnd-cBeg+1),(lastValue-nextToLastValue)/nextToLastValue)

这些公式看起来非常令人生畏，但 LET() 的妙处在于你可以将它们分解成块，这样就更容易理解了。格式如下，variable name, value只是最后一项只是value返回的一项。一次只看一个较长的公式：

  • monthRange,C48:O48
    • 定义monthRange为我们关心的范围。
    • 我们可以完全跳过这一步并直接引用范围，但这 1）有助于使公式的其余部分更容易阅读，2）如果我们关心的范围发生变化，可以使公式更容易更新。
  • cBeg,MIN(COLUMN(monthRange))
    • 查找范围内的第一列。
    • 这在 INDEX() 函数后面是需要的。
  • cEnd,LOOKUP(2,1/(monthRange<>""),COLUMN(monthRange))
    • 查找范围内最后一个非空单元格的列号。
    • 直接取自捷星。
  • allButLastRange,OFFSET(monthRange,0,0,1,cEnd-cBeg)
    • 定义从第一个范围的开始位置开始并在最后一个值的左侧结束的范围。
    • 如果您可以指望倒数第二个值始终位于最后一个值的左边，那么这就没有必要了。
  • cNextToEnd,LOOKUP(2,1/(allButLastRange<>""),COLUMN(allButLastRange))
    • 查找新的、较小的范围内的最后一个非空单元格的列号。
    • 再次强调，除非您的数据中有空白，否则不需要这样做。
  • lastValue,INDEX(monthRange,cEnd-cBeg+1)
    • 提取最后一个数据的值。
  • nextToLastValue,INDEX(monthRange,cNextToEnd-cBeg+1)
    • 提取倒数第​​二个数据的值。
    • 如果没有空白的话，情况会略有不同。cNextToEnd-cBeg+1就变成了cEnd-cBeg。
  • (lastValue-nextToLastValue)/nextToLastValue
    • 进行数学运算以返回您想要的百分比。您可以根据希望的数学运算方式更改此处的条件。
    • 我将其写为比上个月增加的百分比。

如果您不太关心可读性或更新的简易性，并且您知道数据中不会有空白，那么您可以将其变得更短：

=LET(cEnd,LOOKUP(2,1/(C48:O48<>""),COLUMN(C48:O48)),(INDEX(48:48,cEnd)-INDEX(48:48,cEnd-1))/INDEX(48:48,cEnd-1))

答案2

尝试：

=LOOKUP(2,1/ISNUMBER($C1:$AA1),$C1:$AA1)/LOOKUP(2,1/ISNUMBER($C1:$AA1),$B1:$Z1)-1

请注意，result_vector在第二个函数中偏移了一列，LOOKUP以便检测倒数第二个条目。

相关内容