如何在Google Sheets中计算五列数据集的最频繁数值配对？

能否从包含五列的数据集的数值组合中计算出最频繁出现的数值配对？我可以通过Excel宏实现该功能，想了解Google Sheets中是否有简便解决方案。

样本数据 #

B1  B2  B3  B4  B5
6   22  28  32  36
7   10  17  31  35
8   33  38  40  42
10  17  36  40  41
8   10  17  36  54
9   30  32  51  55
1   4   16  26  35
12  28  30  40  43
42  45  47  49  52
10  17  30  31  47
10  17  33  51  58
4   10  17  30  32
2   35  36  37  43
6   10  17  38  55
3   10  17  25  32

预期结果 #

Value1  Value2  Frequency
10  17  8
10  31  2
17  31  2
10  36  2
17  36  2
30  32  2
10  30  2
17  30  2
10  32  2
17  32  2

注：每行代表一个数据集，数值配对无需相邻。

Google Sheets 解决方案 #

无需宏操作，用数组公式就能直接生成结果，步骤如下：

1. 在空白单元格中输入以下公式（将A2:E16替换为你的实际数据范围）：

=LET(
    data, A2:E16,
    pairs, BYROW(data, LAMBDA(row, LET(
        sorted_nums, SORT(FILTER(row, row<>"")),
        num_count, COUNTA(sorted_nums),
        pair_count, COMBIN(num_count, 2),
        MAKEARRAY(pair_count, 2, LAMBDA(r,c, INDEX(sorted_nums, IF(c=1, r, num_count - r + 1 + IF(r>COMBIN(num_count-1,1), num_count - (r - COMBIN(num_count-1,1)), 0))))
    ))),
    flattened, FLATTEN(pairs),
    paired_rows, ARRAYFORMULA(IF(ISODD(SEQUENCE(ROWS(flattened))), flattened&"|", "")),
    combined_pairs, TEXTJOIN(";", TRUE, FILTER(JOIN("", paired_rows), JOIN("", paired_rows)<>"")),
    split_data, SPLIT(combined_pairs, "|;"),
    final_result, QUERY(SPLIT(split_data, "|"), "SELECT Col1, Col2, COUNT(Col1) WHERE Col1 IS NOT NULL GROUP BY Col1, Col2 ORDER BY COUNT(Col1) DESC LABEL COUNT(Col1) 'Frequency'")
)

2. 按下回车后，公式会自动生成按频率降序排列的数值配对统计结果。

公式说明 #

• LET：定义中间变量，简化公式结构，提升可读性。
• BYROW + LAMBDA：逐行处理数据，先对每行数值排序，再生成所有不重复的数值配对（确保配对按升序排列，避免17 10和10 17被统计为不同项）。
• COMBIN：计算每行可生成的配对数量（5个数值对应10组配对）。
• MAKEARRAY：构建每行的配对数组。
• FLATTEN + TEXTJOIN + SPLIT：将所有配对整理成标准二维表格格式。
• QUERY：统计每个配对的出现次数，排序并添加表头。

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