☰

前 5 个绝对值

tag-icon • tag-icon microsoft-excel
前 5 个绝对值

我有一个数据集，我正在尝试创建一个公式来根据第二列提取前 5 个值。输出应该是第二列的前 5 个（省略 0）值。

在此处输入图片描述

谢谢你，Vitaliy

答案1

您可以使用以下任一方法。我没有看到早期版本的标签，因此我使用当前集合中的函数设置它们并利用SPILL功能。

=INDEX(SORTBY(FILTER(B1:B9,B1:B9<>0),ABS(FILTER(B1:B9,B1:B9<>0)),-1,FILTER(B1:B9,B1:B9<>0),-1),SEQUENCE(5),1)

非常简单。FILTER()删除 0。SORTBY()按两“列”排序以使用常规排序术语。第一列按源范围的绝对值降序排列。它将“5”和“-5”视为相同，因此将它们组合在一起，得到：{-5,5,3,-2,2,-1,-1,1}，因为“0”消失了，这是源范围中其余值的顺序，在每个“值点”（“-5”在“5”之前遇到，因此它们以 {-5,5} 的形式出现，依此类推）。

的第二个排序顺序部分SORTBY()将所有具有相同绝对值的值按其“自然”顺序排列。因此，5 的元素（其中两个）从 {-5,5} 变为 {5,-5}，再往下，三个 1 的元素从 {-1,-1,1} 变为 {1,-1,-1}，从而得到 {5,-5,3,2,-2,1,-1,-1}，作为SORTBY()传递给 `INDEX() 的最终数组。

SORTBY()的目的是按绝对值从大到小的顺序生成一个列表，然后按自然顺序排列它们。

INDEX()仅用于选择前五个元素，因为它们是绝对值最高的五个元素。其中唯一“棘手”的事情是使用从列表SEQUENCE()中生成所需的行数（元素） 。SORTBY()

请注意，公式会“判断”各个绝对值源元素：即使“-1”先出现并且它们的绝对值相同，它也会选择“1”而不是“-1”。我认为这是您的意图。而且，老实说，它给出了一个更好的公式……但是，您可能希望返回在源范围中显示较高的那个，而不是对它们进行排序，以便返回每个一开始为正的值。

以下可能是您可以使用的版本，其中LET()提供了“伸出援手”的功能：

=LET(ResultCount,  5,
     Values,  B1:B9,
     CleanValues,  FILTER(Values,Values<>0),

 INDEX(  SORTBY(CleanValues,  ABS(CleanValues),-1,  CleanValues,-1  ),  
         SEQUENCE(ResultCount),  1)
 )

除了在某些方面更容易（在其他方面更难）维护和更改其确切用途之外，它还将人们可能希望不时更改的两个值放在了最开始的位置，易于查找和编辑。只需对其中之一进行一次编辑，它就会反映到整个过程中。无需寻找您想要更改的值，也无需找到其中任何一个的最后一个实例即可进行更改。如果一个值出现了 30 次，您仍然只需要在一个容易找到的地方进行一次编辑。

答案2

找到前 5 个绝对值：

在此处输入图片描述

  • 单元格 M93 中的数组（CSE）公式：

    {=IF(ROW(A1)<=5,LARGE(K$93:K$103,ROWS($A$1:A1)),"")}

注意：

  • 完成配方Ctrl+Shift+Enter。
  • 以上公式If(Row(A1)<=5是可调整的。
  • 根据需要调整公式中的单元格引用。

相关内容