提取存储在对角线元素中的值，并将 rowid 作为 Excel 中的输入

Extract values stored in diagonal elements with the rowid as an input in Excel

提问人：vp_050 提问时间：10/31/2023 最后编辑：Mayukh Bhattacharyavp_050 更新时间：11/1/2023 访问量：113

问：

假设我们有以下矩阵，我们希望通过提取存储在对角线单元格中的值来填充与 中的值匹配的 ROW 标签和 COLUMN Headers。C2C1

矩阵：

1 2 3 4 5
1 2416 6136 13519 5298 1333
2 459 2416 5225 12043 3292
3 785 1276 1319 3188 14798
4 605 816 689 1539 4529
5 205 221 152 301 459

期望的结果

C1  C2
2   2416
5   459

我尝试使用和功能，但无法获得所需的结果。MATCH()INDEX()

注意：如果矩阵以表格格式存储在另一张纸中，我想使用函数来读取值。INDIRECT()

Excel 矩阵 索引 excel-公式 匹配

评论

2赞 Ike 10/31/2023
你说的“rowid 的对角线单元格”是什么意思。2416是如何计算的？
1赞 P.b 10/31/2023
您不需要 INDIRECT 链接到其他工作表。只需在范围前面添加工作表名称：如果确实需要使用间接，请更好地显示要如何使用它。细胞输入？'Sheet1'!B2:F6
1赞 P.b 10/31/2023
对于表，您可以直接引用表名称，而不是工作表。无论如何，您应该发布更多详细信息，说明您期望它如何使用哪些细节。对于一个糟糕的问题，你有一个完美的解决方案。
2赞 P.b 11/1/2023
如果您希望它涉及 INDIRECT，请解释您如何看待这一点。单元格输入？举两个范围 （/tables） 示例，以及您期望不同输入在输出上的行为方式。就像你展示的&和他们的预期输出一样。除了间接部分，你的问题会很好。C1C2
1赞 P.b 11/1/2023
如果您分享您的尝试，也会有所帮助。我们可以从那里看到范围或表引用。

答：

1赞 Mayukh Bhattacharya 10/31/2023 #1

尝试使用INDEX() & MATCH()

enter image description here

• 细胞中使用的配方I2

=INDEX($B$2:$F$6,MATCH(H2,$A$2:$A$6,0),MATCH(H2,$B$1:$F$1,0))

或SUM()

enter image description here

• 细胞中使用的配方I2

=SUM((H2=$B$1:$F$1)*(H2=$A$2:$A$6)*$B$2:$F$6)

或者，使用FILTER()

enter image description here

• 细胞中使用的配方I2

=FILTER(FILTER($B$2:$F$6,H2=$A$2:$A$6),H2=$B$1:$F$1)

或者，使用MAP()

enter image description here

• 细胞中使用的配方I2

=MAP(H2:H3,LAMBDA(x,FILTER(FILTER(B2:F6,x=A2:A6),x=B1:F1)))

或

• 细胞中使用的配方I2

=XLOOKUP(H2,$B$1:$F$1,FILTER($B$2:$F$6,H2=$A$2:$A$6))

enter image description here

基于OP评论的假设：

这个公式： =XLOOKUP（H2，$B$1：$F$1，FILTER（$B$2：$F$6，H2=$A$2：$A$6）） 如果矩阵存储在另一个工作表中并且我们使用间接函数来读取它，则其他公式似乎不起作用。

由于我不确定您使用该函数的原因，因为它不清楚并且 OP 似乎没有正确解释，因此我尝试了几种我在下面评论的方法，只是假设如果您使用引用 SheetTab，那么您可以尝试。INDIRECT()INDIRECT()

enter image description here

• 细胞中使用的配方B2

=LET(
     α, INDIRECT(C1&"!A1:F6"),
     φ, DROP(TAKE(α,,1),1),
     δ, DROP(TAKE(α,1),,1),
     Ω, DROP(α,1,1),
     MAP(A2:A3,LAMBDA(m,FILTER(FILTER(Ω,m=φ),m=δ))))

此外，我清楚地看到的不是必需的，但是是的，如果您有多个工作表并想将工作表切换为不同的输出，那么它是必需的，但是在现代 excel 中，在俱乐部中，您可以组合一组数据，然后对数据进行操作。但仍然不清楚。INDIRECT()

一种方式：

enter image description here

@P.b 先生建议的另一种方式，

enter image description here

如果其他工作表中的数据在Structured References

enter image description here

最终编辑和更新：

enter image description here

  • Sheet1--> 是存储数据的位置。
  • Structured References--> 数据在 akaStructured ReferencesTables
  • OP，想专门使用函数。INDIRECT()

enter image description here

• 细胞中使用的配方B2

=LET(
     α, INDIRECT(C1),
     φ, DROP(TAKE(α,,1),1),
     δ, DROP(TAKE(α,1),,1),
     Ω, DROP(α,1,1),
     MAP(A2:A3,LAMBDA(m,FILTER(FILTER(Ω,m=φ),m=δ/1))))

或

•在C2

=LET(
     α, INDIRECT(C1),
     φ, DROP(α,1,1),
     δ, TOROW(IF(φ,DROP(TAKE(α,1),,1)))/1,
     Ω, TOROW(IF(φ,DROP(TAKE(α,,1),1))),
     MMULT((δ=A2:A3)*(Ω=A2:A3),TOCOL(φ)))

笔记：由于标题是数字，而它转换为文本格式，因此它被除以使其成为数字 -->Structured References1δ/1

上面发布的所有解决方案都可以在这里找到：Excel

enter image description here

评论

0赞 vp_050 10/31/2023
这个公式： =XLOOKUP（H2，$B$1：$F$1，FILTER（$B$2：$F$6，H2=$A$2：$A$6）） 如果矩阵存储在另一个工作表中并且我们使用间接函数来读取它，则其他公式似乎不起作用。
1赞 Mayukh Bhattacharya 10/31/2023
@vp_050抱歉，您的帖子中没有提到它，我试图提供所有可能性。在您的帖子中没有提到您正在使用功能阅读其他工作表。INDIRECT()
0赞 vp_050 10/31/2023
我同意并为此道歉。我现在在这个问题上做了一个记录
1赞 Mayukh Bhattacharya 10/31/2023
@vp_050我刚刚尝试过这种方式并且它有效，Yu 可以确认吗？ 即使这样也在起作用=FILTER(FILTER(INDIRECT("Sheet1!"&"B2:F6"),INDIRECT("Sheet1!"&"A2:A6")=A2),INDIRECT("Sheet1!"&"B1:F1")=A2)=INDEX(INDIRECT("Sheet1!"&"B2:F6"),MATCH(A2,INDIRECT("Sheet1!"&"A2:A6"),0),MATCH(A2,INDIRECT("Sheet1!"&"B1:F1"),0))
2赞 P.b 10/31/2023
@vp_050要单独检索各种工作表的值或所有工作表的总和？在后一种情况下，您可能不需要 INDIRECT。

上一个：Excel VBA 匹配具有 2 个变量的行

下一个：当每个 arm 返回带有类型参数的类型的不同组合时，如何减少 2 个嵌套匹配语句