编号等差分组的EXCEL函数实现

发表于 更新于 分类于 ， 阅读次数： 评论数：

假设我们有这样一组数据在表中存储为如下：

1
2
3
={
"sno","score";1,1;2,0;3,2;4,0;5,0;6,3;7,3;8,2;9,0;10,0;11,5;12,4;13,4;14,2;15,2;16,5;17,0;18,2;19,3;20,1
}


现在许要将数据1-2，3-4···两两一组进行对分数求平均，显示在对应数据后面。假设上面的数据放在Sheet4的A1单元格，我们可以通过下面的代码进行获取

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
=LET(
    g_num, 2,
    t_sno, LAMBDA(rag, INDEX(Sheet4!A1:A21, ROW(rag), 1)),
    c_arr, LAMBDA(sno, g_num,
        FILTER(
            Sheet4!B1:B21,
            (sno <= Sheet4!A1:A21) * (sno + g_num > Sheet4!A1:A21)
        )
    ),
    c_fun, LAMBDA(c_arr, AVERAGE(c_arr)),
    avg_arr, MAP(
        Sheet4!B1:B21,
        LAMBDA(x,
            IFS(
                ROW(x) = 1,
                "avg_score",
                MOD(t_sno(x), g_num) = 1,
                c_fun(c_arr(t_sno(x), g_num)),
                1 = 1,
                c_fun(
                    c_arr(
                        (INT((t_sno(x) - 1) / g_num) * g_num + 1),
                        g_num
                    )
                )
            )
        )
    ),
    ans_arr, HSTACK(Sheet4!A1:B21, avg_arr),
    ans_arr
)


这段代码可以分为定义工具、计算分组数据、拼接为结果数据集三部分。第一部分的各个内容如下：
g_num指一组的数据为几条；
t_sno(rag)为根据传入的单元格返回对应的编号；
c_arr(sno, g_num)为根据传入的编号和个数返回需要计算的数据数组，返回的规则为大于等于sno小于sno+g_num所对应的所有数据
c_fun(c_arr)为实际的计算函数，接受的c_arr是需要需要计算的数据

计算分组数据的逻辑是使用MAP()遍历每个需要计算的单元格，并为上面的函数传入计算中需要的参数。其中如果当前计算的单元格不是本组中第一个单元格时，则需要传入本组第一个单元格的编号，对应计算逻辑为(INT((t_sno(x) - 1) / g_num) * g_num + 1)
最后将计算得到的数组与原数据源通过HSTACK()拼接即可