从值表中创建值列表，并排除某些值。

Question

我有以下Excel电子表格：

    A            B       C        D        E              F                 G    
1                 Q1     Q2       Q3      Q4        Positive values
2   Asset 1      -50     85      -90      70             85 
3   Asset 2      -28    -80      -45      60             70         
4   Asset 3      -30     50       55     -10             60      
5   Asset 4      -20      5      -80     -15              :  
6   Asset 5       35    -30       27     -98              :
7                    

在单元格A1:E6中，我有不同的资产，其性能与季度Q1-Q4不同。

在Column F中，我使用来自这里的公式创建了资产所有正性能的列表

Column F = {LARGE(IF($B$2:$E$6>0,B$2:$E$6),ROW(B1:E1))}

到目前为止，这一切都很好。

但是，现在我想从Column F中的列表中排除某些值。例如，我不希望该数字85或数字70出现在列表中。因此，我试图将公式修改为：

Column F = {LARGE(IF(AND($B$2:$E$6>0,$B$2:$E$6<>85,$B$2:$E$6<>70),B$2:$E$6),ROW(B1:E1))}

然而，现在我得到的结果是0。

你知道能解决这个问题的公式吗？

Answer 1

你走在正确的道路上。不用使用AND，嵌套的IF可以工作。您需要为要测试的每个新条件创建一个新的IF条件。因此，如果要排除85和70，则需要添加两个额外的if语句。

F栏的公式是：

={LARGE(IF($B$2:$E$6>0,IF($B$2:$E$6<>85,IF($B$2:$E$6<>70,B$2:$E$6))),ROW(B1:E1))}

您可以在这里阅读公式处理的方式：最小若多准则

Answer 2

• 创建一个0的数组，其中0等效于一个负数或排除值。

($B$2:$E$6>0)*($B$2:$E$6<>85)*($B$2:$E$6<>70)

• 1/(…)会将其转换为1和错误。
• 乘以原始值以创建原始值和错误的数组。

 =1/(($B$2:$E$6>0)*($B$2:$E$6<>85)*($B$2:$E$6<>70)) * B2:E6

• 使用聚合函数获得所需的结果。

=AGGREGATE(14,6,1/(($B$2:$E$6>0)*($B$2:$E$6<>85)*($B$2:$E$6<>70))*$B$2:$E$6,ROWS($1:1))

填上第二、第三等最大的

Answer 3

如果您有F列中的数据，并且希望将不是70或85的所有值提取到G列中，那么在G2中输入：

=IFERROR(INDEX(F$2:F$9999, AGGREGATE(15, 6, ROW($1:$999)/((F$2:F$9999<>85)*(F$2:F$9999<>70)), ROW(1:1))),"")

并向下复制：

​

​

注意，这种方法不需要使用数组公式。