提问人：vp_050 提问时间：8/10/2023 最后编辑：Mayukh Bhattacharyavp_050 更新时间：8/10/2023 访问量：87
在 excel 中查找矩阵中前 3 个值的对应行 ID 和列 ID
Find the corresponding row ID and column ID of top 3 values in a matrix in excel
问：
我有以下矩阵。
我想接收的输出是。
我已经实现了查找前 3 个值的功能。我尝试在一个公式中实现和函数以接收上述输出，但没有成功。我们如何在excel中获取上述输出？LARGE()INDEX()MATCH()
答：
4赞
Mayukh Bhattacharya
8/10/2023
#1
请尝试使用以下公式：
• 细胞中使用的配方H1
=LET(
a,TOCOL(B2:F6),
b,TOCOL(IFNA(A2:A6,SEQUENCE(,ROWS(B2:F6)))),
c,TOCOL(IFNA(B1:F1,SEQUENCE(ROWS(B2:F6)))),
d,TAKE(SORT(HSTACK(a,b,c),1,-1),3),
VSTACK({"Rank","Value","RowID","ColID"},
HSTACK(SEQUENCE(ROWS(d)),d)))
这是另一个替代公式，但字节数也更多，它使用辅助函数LAMBDA( )BYROW( )
=LET(
a,A1:F6,
b,TAKE(SORT(TOCOL(DROP(a,1,1)),,-1),3),
c,BYROW(b,LAMBDA(m,CONCAT(REPT(DROP(a,1,-5)&"|"&TAKE(a,1,-5),m=DROP(a,1,1))))),
d,HSTACK(SEQUENCE(ROWS(b)),b,TEXTBEFORE(c,"|"),TEXTAFTER(c,"|"))+0,
VSTACK({"Rank","Value","RowID","ColID"},d))
比前面的字节少一点：
=LET(
a,A2:A6,
b,B1:F1,
c,B2:F6,
d,TAKE(SORT(TOCOL(c),,-1),3),
e,SEQUENCE(ROWS(d)),
HSTACK(VSTACK("Rank",e),REDUCE({"Value","RowID","ColID"},d,LAMBDA(x,y,
VSTACK(x,HSTACK(y,CONCAT(REPT(a,y=c)),CONCAT(REPT(b,y=c)))+0)))))
评论
0赞
vp_050
8/10/2023
在这种情况下，我们如何排除对角线元素？
0赞
Mayukh Bhattacharya
8/10/2023
你@vp_050对此提出问题？
2赞
JvdV
8/10/2023
#2
这里有一个替代方法：
公式：H1
=VSTACK({"Rank","Value","RowID","ColID"},HSTACK({1;2;3},TAKE(SORT(--TEXTSPLIT(TEXTAFTER("|"&TOCOL(B2:F6&"|"&A2:A6&"|"&B1:F1),"|",{1,2,3}),"|"),,-1),3)))
评论
0赞
vp_050
8/25/2023
如果任何单元格是空白的或有 NA，我们如何忽略上述公式中的这些单元格？
评论
73RowID53