如何使用MAP()、BYCOL() 或BYROW() 来返回多个数组？

编程语言 2026-07-10

我有一个表示项目之间相互作用的矩阵。每一行和每一列都对应一个与另一项互动的项目（也就是说，"列1" 与 "行4" 代表项目1与项目4之间的互动；"列3"、"行3" 则表示项目3与其自身的互动，这种情况也会发生）。每个互动都有一个数值，因此存在强互动和弱互动。见下图：

项目	1	2	3	4
1	0.11	0.21	0.34	0.43
2	0.12	0.23	0.33	0.42
3	0.13	0.22	0.31	0.41
4	0.14	0.24	0.32	0.44

我的问题如下：我想要一个新表格，使得每一列的交互按强到弱排序，并以对应的项目编号标注。因此“列1”应为 {1; 2; 3; 4}，“列2”应为 {1; 3; 2; 4}，“列3”应为 {3; 4; 2; 1}，而“列4”应为 {3; 2; 1; 4}。

在Excel中，我尝试了类似这样的表达式：

=MAP(items_cols;
     LAMBDA(x; TAKE(SORTBY(items_rows;
                           CHOOSECOLS(data_values;
                                      MATCH(x; items_cols)
                                      )
                           ); 
                    5)
            )
     )

其中 items_rows 是包含项目名称的列，items_cols 是包含项目名称的行，而 data_values 是表格的其余部分（即中间部分）。我用 TAKE() 来获取前5个，因为我的项目大约有50个左右。

使用 MAP() 和 BYCOL() 无法工作，因为这些函数不能输出数组（或者类似的问题）。我在某种程度上通过使用 TEXTJOIN() 绕过了这个问题，使 MAP() 返回形如 "1_2_3_4" 的字符串数组，而不是数组的数组。不过现在我得将字符串拆分以访问结果，在这一点上，我又得再次使用 MAP()，并再次遇到同样的问题。

我已经用辅助表格解决了这个问题（如本例所示），但每次需要进行这种排序时为大约50个左右的项目建立辅助表格工作量太大。这就是为什么我想把它自动化，并在单个 LET() 参数中完成。

那么，是否有一种简洁干净的方式来获得这些结果？

解决方案

Sort by column via array reshaping (broadcast and flatten):

=WRAPCOLS(
    SORTBY(
        TOCOL(IF({1}, items_rows, data_values)),
        TOCOL(IF({1}, SEQUENCE(, COLUMNS(data_values)), data_values)), 1,
        TOCOL(data_values), 1
    ),
    ROWS(data_values)
)

IF 用于通过把一个数组对象 {1} 传递给它的 logical_test 参数，在 data_values 数组中对 items_rows 向量进行广播（重复）。由于1 被解释为TRUE，因此只返回 value_if_true 参数。
同样的过程也应用于水平向量的列索引数字。(注： SEQUENCE(, COLUMNS(data_values)) 作为一种广义方法使用，但如果它已经包含连续的列索引数字，例如 {1,2,3,4,...}，也可以用 items_cols 替代。)
TOCOL 通过把它们的值发送到单一列（竖向向量）来扁平化每个数组。
SORTBY 然后对得到的 items_rows 值向量进行排序，先按列索引号升序排序，再按 data_values 升序排序。
最后，WRAPCOLS 将原表/数组的维度恢复。

Sort by column via recursive bisection (a.k.a. divide and conquer):

=LET(
    fn, LAMBDA(me,arr,
        IF(
            COLUMNS(arr) = 1,
            SORTBY(items_rows, arr),
            HSTACK(me(me, TAKE(arr, , COLUMNS(arr) / 2)), me(me, DROP(arr, , COLUMNS(arr) / 2)))
        )
    ),
    fn(fn, data_values)
)

此方法通过递归地把 data_values 数组对半分割来创建一个函数的二叉树，直到到达每个叶节点为止，从而推迟对 SORTBY(items_rows, arr) 的求值，直到只剩下1列为止，并有效绕过在标准Lambda递归中通常会被施加的操作数栈限制。

为了在 LET 语句的作用域内使函数成为递归，需要一个不动点组合子（me），在定义好函数名之后将函数传递给自身。

简单来说，me 被用作函数占位符，因为此时 fn 还不存在（它仍在被定义），因此它还不能自我调用。

在 fn 定义之后，me 通过 fn(fn,...) 变为 fn。这是一种Lambda注入的形式，即同一个函数被再次应用于后续的所有迭代。

Excel