Excel中，如何查找区域内满足条件的值？

Question

如图所示：B2:K25是数据区域，B$1:K$1是固定的，A列的单元格数据是要查找的，L列是查找的结果。
A2=56789在数据区域B2:K2出现的数据是5、7、5、6、6，其中最大数是7，对应的值是6，那么L2=6才是我要求的结果。即查找A列单元格内的每个数字在数据区域内出现最大数是哪个，并最大数对应值列出来。

Answer 1

原问题删除了？上午刚给你提供回答后，就找不到问题了。上午提交回答后，本来还算补充下的，却找不到了。

如果Excel版本是365或2019，公式比较简单如截图中的M列：

M2=TEXTJOIN(,,IF(B2:K2=MAX(ISNUMBER(FIND(B$1:K$1,A2))*B2:K2),B$1:K$1,""))

同时按Ctrl+Shift+Enter三键输入数组公式，下拉。

如果不版本低一些，公式也要复杂些，如截图中L列：

L2=IF(B2=MAX(B2:K2),0,"")&SUBSTITUTE(SUM(((B2:K2=MAX(ISNUMBER(FIND(B$1:K$1,A2))*B2:K2))*B$1:K$1)*10^(10-COLUMN(A:J))),0,)

同样数组公式。

上午的回答中，没有提供前一个公式，本来就是补充这个公式的。

追问

你好，我试用了两个公式，都出现同样的问题，前面几个没问题，后面就开始出现混乱。看截图明白了



追答

前面的公式忽视了一个小问题，补充一个条件即可：
L2=IF((B2=MAX(B2:K2*ISNUMBER(FIND(B$1:K$1,A2))))*(LEFT(A2)="0"),0,"")&SUBSTITUTE(SUM(((B2:K2=MAX(ISNUMBER(FIND(B$1:K$1,A2))*B2:K2))*B$1:K$1*(ISNUMBER(FIND(B$1:K$1,A2))))*10^(10-COLUMN(A:J))),0,)

M2=TEXTJOIN(,,IF(B2:K2=MAX(ISNUMBER(FIND(B$1:K$1,A2))*B2:K2)*ISNUMBER(FIND(B$1:K$1,A2)),B$1:K$1,""))

追问

非常感谢！问题得到解决了，现在我把公式用到我需要的地方是就出现问题了，我不明白公式

中的COLUMN(A:J)表示什么意思？

大神，帮我看下截图里的公式哪里出现问题了?

COLUMN(A:J)中的A:J可以用其它来代替吗？这样的话可以随意换转到其它表格里

追答

COLUMN(A:J)保持不变试试。
和你说下公式的大体思路，用第1行（你的实际数据是第3行）的0到9在A列（你的实际数据是E列）中把查找，找到的就是数字（ISNUMBER），这个ISNUMBER与该行中的数字相乘，这样第3行的数字在E列中有的，就得到数字，没有的就得到0，再用MAX求出最大数。然后该行数字等于这个最大数的（前面的回答只考虑到这一步）就是取得数字，实际还要加上一个条件，且第3行的数字在E列找到的（前面的回答忽视了这一点），两个条件都满足，就取该数字，否则取0，最后分别乘上10的N次方，N从右到左分别从0到9，这样做是为SUM做准备，把不同数位上的数字扩大到10的N次方。用SUM求和后，中间会有很多0（也正好是不取数的位置上），用SUBSTITUTE把这些0去掉之。前面的0单独用了公式进行处理，应该比较容易理解。

从上可知。10-COLUMN(A:J)，就是要得到9、8、7，直到0。所以你把公式搬到哪是10-COLUMN(A:J)，不要随位置变动。
不用10-COLUMN(A:J)当然可以的，就用{9，8，7，6，5，4，3，2，1，0}

追问

哇，讲解着非常详细，谢谢！如果我要在这基础上取最小的，是不是用MAX改MIN就可以了？如果选取第二大(前面是取最大的)的数值，该如何用公式表达？

追答

最小用：MIN，第二大用：LAGRE(数据,2)，应该是可以的，但要测试看是否会有意想不到的问题。
高版本建议用textjion公式，简练得多

追问

测试了，都显示空值。不能直接套用公式了，遇到这两问题，如何用公式来表示？

追答

第2大用large是没问题的：
=IF((B2=MAX(B2:K2*ISNUMBER(FIND(B$1:K$1,A2))))*(LEFT(A2)="0"),0,"")&SUBSTITUTE(SUM(((B2:K2=LARGE(ISNUMBER(FIND(B$1:K$1,A2))*B2:K2,2))*B$1:K$1*(ISNUMBER(FIND(B$1:K$1,A2))))*10^(10-COLUMN(A:J))),0,)
最小还有一个问题，就是原公式中，没找到的数据为0，所以最小会取0。公式思路可能得重新整理。
最小来了：
=IF((B2=MIN(B2:K2*ISNUMBER(FIND(B$1:K$1,A2))))*(LEFT(A2)="0"),0,"")&SUBSTITUTE(SUM(((B2:K2=MIN(IF(ISNUMBER(FIND(B$1:K$1,A2)),B2:K2,9^9)))*B$1:K$1*(ISNUMBER(FIND(B$1:K$1,A2))))*10^(10-COLUMN(A:J))),0,)

追问

在第2大的公式里，IF函数中的MAX不用更改吗？

追答

前面的处理0也要更改：
=IF((B2=LARGE(B2:K2*ISNUMBER(FIND(B$1:K$1,A2))),2)*(LEFT(A2)="0"),0,"")&SUBSTITUTE(SUM(((B2:K2=LARGE(ISNUMBER(FIND(B$1:K$1,A2))*B2:K2,2))*B$1:K$1*(ISNUMBER(FIND(B$1:K$1,A2))))*10^(10-COLUMN(A:J))),0,)

追问

显示公式存在错误

追答

发现第二的问题还比较多，需要重新编写公式。有时间再弄。

追问

是啊，问题比较复杂了

追答

第二大数，请自己测试下公式（不想再花大量时间来输入数据模拟）：
=IF((B2=LARGE(ISNUMBER(FIND(B$1:K$1,A2))*B2:K2*(MATCH(ISNUMBER(FIND(B$1:K$1,A2))*B2:K2,ISNUMBER(FIND(B$1:K$1,A2))*B2:K2,)=COLUMN(A:J)),2))*ISNUMBER(FIND(B$1,A2)),0,"")&SUBSTITUTE(SUM((ISNUMBER(FIND(B$1:K$1,A2))*B2:K2=LARGE(ISNUMBER(FIND(B$1:K$1,A2))*B2:K2*(MATCH(ISNUMBER(FIND(B$1:K$1,A2))*B2:K2,ISNUMBER(FIND(B$1:K$1,A2))*B2:K2,)=COLUMN(A:J)),2))*B$1:K$1*10^(10-COLUMN(A:J))),0,)

Answer 2

在数据区域中输入公式：=B2&countif($B$2:B2,B2)，下拉填充到对应单元格位置。 然后在查找区域输入函数：=IFERROR（VLOOKUP（E$2&ROW(A1),$A:$C,3,0),“”)，下拉填充到足够多的地方。E$2&ROW(A1)相当于将VLOOKUP函数的查询值加上了不同的序列。