PAYATHON 2026

Excel 的 MAP()、BYCOL() 或 BYROW() 如何返回多个数组？

数据阿明

可以用一个 LET() 公式完成：通过 REDUCE() 逐列计算排名，再用 HSTACK() 横向合并结果，得到可以直接溢出的二维数组。不需要辅助表，也不必先用 TEXTJOIN() 转成字符串。

先确定排序方向

题目给出的四列预期结果都对应数值升序。例如，第 1 列的数值依次为 0.11、0.12、0.13、0.14，对应项目编号 {1; 2; 3; 4}。

下面的公式默认使用 sortOrder;1，以匹配示例。如果业务含义是“数值越大，交互越强”，应改成 sortOrder;-1，按数值降序提取前 5 项。仅凭现有描述，无法确定数值大小与交互强弱的实际关系。

使用 REDUCE 和 HSTACK 合并每列排名

沿用题目中的命名区域，在空白单元格输入：

=LET(
    topK;MIN(5;ROWS(items_rows));
    sortOrder;1;
    result;REDUCE(
        "";
        SEQUENCE(COLUMNS(data_values));
        LAMBDA(acc;colNum;
            HSTACK(
                acc;
                TAKE(
                    SORTBY(
                        items_rows;
                        CHOOSECOLS(data_values;colNum);
                        sortOrder
                    );
                    topK
                )
            )
        )
    );
    DROP(result;;1)
)

这里假设 items_rows 是单列项目名称，行数与 data_values 一致，且项目名称的顺序与数值区域的行顺序对应。

公式按以下步骤计算：

  1. SEQUENCE(COLUMNS(data_values)) 生成需要处理的列序号。
  2. CHOOSECOLS(data_values;colNum) 取出当前列的交互数值。
  3. SORTBY() 根据该列数值对 items_rows 排序，返回项目名称或编号。
  4. TAKE() 保留前 topK 项。
  5. HSTACK() 将当前列的排名接到累计结果右侧。
  6. DROP(result;;1) 删除初始 "" 产生的占位列。

相关函数语法可参见 Microsoft 的 REDUCE 文档和 HSTACK 文档。

公式直接按 data_values 的列位置处理，不需要通过 items_cols 查找列号。输出列顺序与 data_values 相同。

对于题目中的四行数据，MIN(5;ROWS(items_rows)) 得到 4。按数值升序逐列排序，结果与题目列出的预期结果一致：

排名项目 1 列项目 2 列项目 3 列项目 4 列
11133
22342
33221
44414

为什么直接使用 MAP、BYCOL 或 BYROW 会受限

这些函数可以返回结果数组，但在题述用法中，每次 LAMBDA 调用都需要返回一整列排名。外层函数不会自动将这些列展开并拼接成二维区域。

以 BYCOL() 为例，其常规计算方式是每列生成一个值，例如最大值或总和。Microsoft 的 BYCOL 文档要求内部 LAMBDA 返回单个值，否则会出现 #CALC!。

TEXTJOIN() 可以让每次计算返回一个文本值，从而绕过这一返回限制，但项目编号也会变成需要再次解析的字符串。这里可以直接拼接数组。

另一种写法：MAKEARRAY 逐个生成结果单元格

也可以先确定输出区域的行列数，再让每次 LAMBDA 只返回一个项目编号：

=LET(
    topK;MIN(5;ROWS(items_rows));
    sortOrder;1;
    MAKEARRAY(
        topK;
        COLUMNS(data_values);
        LAMBDA(rowNum;colNum;
            INDEX(
                SORTBY(
                    items_rows;
                    CHOOSECOLS(data_values;colNum);
                    sortOrder
                );
                rowNum;
                1
            )
        )
    )
)

rowNum 表示要取第几名，colNum 表示处理原矩阵的哪一列，INDEX() 从排序结果中取出一个编号。MAKEARRAY 按指定的行列尺寸，为每个位置计算一个结果。对于逐列排序，前面的 REDUCE() 写法更直接地表达了“生成一列，再拼接一列”的过程。

使用时需要注意什么

  • 当前 Excel 环境需要支持所选公式中的函数。出现 #NAME? 时，应检查函数支持情况和命名区域是否存在。
  • 示例沿用原问题的分号参数分隔符。如果本地 Excel 使用逗号，请相应替换。
  • data_values 应只包含参与排序的数值，不包含标题或合计行；空白、文本和错误值需要先按业务规则处理。
  • 公式保留了项目与自身的交互，没有排除对角线数据。
  • 如果数值相同且需要明确的先后顺序，可以增加第二排序键。例如按项目编号升序打破并列：SORTBY(items_rows;CHOOSECOLS(data_values;colNum);sortOrder;items_rows;1)。
  • 如果继续使用原来的列名查找方式，应写成 MATCH(x;items_cols;0)，明确进行精确匹配，避免默认近似匹配带来的列定位问题。
  • 在普通工作表的空白区域输入公式，并为结果保留足够的溢出空间。

备注：内容仅供参考。