Excel技术问题：如何由行列列表生成单元格数组并计算最大值？

核心需求 #

基于动态生成的逗号分隔列标识（如F,S,AE,AQ,BC,BO,CA）和行号列表（如10,21,32,43,54,65,76），在单个单元格中生成交叉单元格数组并计算最大值，支持行列数量动态变化。

Excel 365/2021 版本解决方案 #

直接使用TEXTSPLIT拆分文本列表，结合INDEX生成交叉引用数组，最终用MAX取最大值：

=MAX(TOCOL(INDEX(数据源工作表!$A:$ZZ, --TEXTSPLIT(行号列表单元格, ","), MATCH(TEXTSPLIT(列标识列表单元格, ","), 数据源工作表!$1:$1, 0))))

参数说明 #

• TEXTSPLIT(列标识列表单元格, ",")：将逗号分隔的列标识文本拆分为单个列名的数组
• MATCH(..., 数据源工作表!$1:$1, 0)：匹配每个列名在数据源表首行的对应列号
• --TEXTSPLIT(行号列表单元格, ",")：拆分行号文本并通过--转换为数值型行号数组，避免文本类型导致的计算错误
• INDEX(数据源工作表!$A:$ZZ, 行号数组, 列号数组)：生成所有交叉单元格的二维引用数组
• TOCOL(...)：将二维数组转换为一维数组，适配MAX函数的计算逻辑
• MAX(...)：计算所有交叉单元格的最大值

旧版Excel（无TEXTSPLIT）解决方案 #

用FILTERXML替代TEXTSPLIT完成文本拆分：

=MAX(TOCOL(INDEX(数据源工作表!$A:$ZZ, --FILTERXML("<a><b>"&SUBSTITUTE(行号列表单元格, ",", "</b><b>")&"</b></a>", "//b"), MATCH(FILTERXML("<a><b>"&SUBSTITUTE(列标识列表单元格, ",", "</b><b>")&"</b></a>", "//b"), 数据源工作表!$1:$1, 0))))

关键逻辑 #

FILTERXML通过构造简易XML结构，将逗号分隔的文本拆分为数组；--负责将文本行号转为数值，彻底解决文本行号无法参与引用的问题。

注意事项 #

1. 替换公式中的数据源工作表为实际数据所在的工作表名称
2. 行号列表单元格和列标识列表单元格替换为存储对应动态生成列表的单元格引用（即你原先生成行列列表的公式所在单元格）
3. 公式支持行列数量动态变化，无需手动调整数组范围

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