Google Sheets：如何获取共现矩阵中数值对应的行列标签

适用于Excel 365/2021（动态数组版本） #

直接用动态数组函数一步生成结果，无需辅助列：

假设你的矩阵结构为：

• 首行（A1:Z1）是列标签
• 首列（A1:A100）是行标签
• 数值区域为B2:Z100（共现次数）

复制以下公式到空白单元格（比如AA1），调整区域范围匹配你的数据：

=LET(
    列标签, A1:Z1,
    行标签, A1:A100,
    数值区, B2:Z100,
    行标签列表, TOCOL(INDEX(行标签, SEQUENCE(ROWS(数值区))), 2),
    列标签列表, TOCOL(INDEX(列标签, SEQUENCE(1, COLUMNS(数值区))), 2),
    标签配对, HSTACK(行标签列表, 列标签列表),
    数值列表, TOCOL(数值区, 2),
    筛选高值配对, FILTER(标签配对, 数值列表 >= 5),
    BYROW(筛选高值配对, LAMBDA(x, TEXTJOIN("&", TRUE, x)))
)

公式说明：

1. LET 定义变量，简化公式结构
2. TOCOL+INDEX 把行列标签分别转成与数值一一对应的一维列表
3. HSTACK 将行/列标签配对，形成「行标签+列标签」的二维数组
4. FILTER 筛选出共现次数≥5的标签对
5. BYROW+TEXTJOIN 把每对标签合并成A&D的格式

如果需要避免重复配对（比如同时出现A&D和D&A），可以修改FILTER的条件：

FILTER(标签配对, (数值列表 >= 5)*(行标签列表 <= 列标签列表))

适用于旧版Excel（无动态数组） #

用辅助列实现：

1. 辅助列1（AA列）：生成行标签，输入公式后下拉到覆盖所有数值行：

   =INDEX($A:$A, INT((ROW()-2)/COLUMNS($B:$Z))+2)
   

2. 辅助列2（AB列）：生成列标签，输入公式后下拉：

   =INDEX($1:$1, MOD(ROW()-2, COLUMNS($B:$Z))+2)
   

3. 辅助列3（AC列）：合并标签对：

   =AA2&"&"&AB2
   

4. 辅助列4（AD列）：提取对应共现次数：

   =INDEX($B:$Z, INT((ROW()-2)/COLUMNS($B:$Z))+2, MOD(ROW()-2, COLUMNS($B:$Z))+2)
   

5. 筛选AD列中≥5的行，AC列就是对应的标签对

内容的提问来源于stack exchange，提问作者Rob