Excel中根据矩阵数值自动生成含行列标签的跨表条目列表方法咨询

首先提前做基础配置：

• 存放矩阵的工作表命名为「矩阵表」，A列为Y轴条目名称，第1行为X轴条目名称，数值填充区域为B2:Z100（可根据实际矩阵大小修改）
• 输出结果的工作表命名为「结果表」，表头第一行依次填写：数值、Y轴条目、X轴条目

方案1：Excel 365/2021 动态数组公式方案（无代码、全自动） #

直接在结果表的A2单元格输入以下公式，结果会自动溢出填充，不需要手动下拉，矩阵内容更新后结果会自动同步：

=LET(
    矩阵区域, 矩阵表!B2:Z100,
    Y轴标题, 矩阵表!A2:A100,
    X轴标题, 矩阵表!B1:Z1,
    非空序号, TOCOL(IF(矩阵区域<>"", SEQUENCE(ROWS(矩阵区域),COLUMNS(矩阵区域)), NA()), 2),
    对应行号, INT((非空序号-1)/COLUMNS(矩阵区域))+1,
    对应列号, MOD(非空序号-1, COLUMNS(矩阵区域))+1,
    HSTACK(
        INDEX(矩阵区域, 对应行号, 对应列号),
        INDEX(Y轴标题, 对应行号),
        INDEX(X轴标题, 对应列号)
    )
)

公式遍历逻辑完全符合要求：从上到下、从左到右扫描矩阵，跳过空单元格，按遍历顺序生成条目。

方案2：低版本Excel（2019及更早）VBA方案（触发式自动更新） #

如果使用不支持动态数组的Excel版本，可通过VBA实现自动更新：

1. 打开「矩阵表」，按Alt+F11调出VBA编辑器
2. 在左侧项目列表中双击「矩阵表」名称，在弹出的代码编辑窗口粘贴以下代码：

Private Sub Worksheet_Change(ByVal Target As Range)
    Dim 矩阵范围 As Range, 单元格 As Range
    Dim 结果表 As Worksheet
    Dim 下一空行 As Long
    ' 可根据实际矩阵大小修改此处范围
    Set 矩阵范围 = Me.Range("B2:Z100")
    Set 结果表 = ThisWorkbook.Sheets("结果表")
    
    ' 仅处理矩阵区域的内容修改
    If Not Intersect(Target, 矩阵范围) Is Nothing Then
        Application.EnableEvents = False
        ' 清空原有结果，保留表头
        结果表.Range("A2:C" & 结果表.Rows.Count).ClearContents
        ' 按从上到下、从左到右顺序遍历矩阵
        For Each 单元格 In 矩阵范围
            If 单元格.Value <> "" Then
                下一空行 = 结果表.Cells(结果表.Rows.Count, "A").End(xlUp).Row + 1
                ' 写入对应字段
                结果表.Cells(下一空行, "A") = 单元格.Value
                结果表.Cells(下一空行, "B") = Me.Cells(单元格.Row, "A")
                结果表.Cells(下一空行, "C") = Me.Cells(1, 单元格.Column)
            End If
        Next
        Application.EnableEvents = True
    End If
End Sub

3. 保存文件为Excel启用宏的工作簿(*.xlsm)格式即可，后续只要修改矩阵表的内容，结果表会自动同步更新。

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