JS宏上手实例——账龄分析表

#WPS技巧达人 #JS宏 #账龄分析表

wils

Lv.2潜力创作者

前两天论坛里发起投票，发现只有少数朋友愿意尝试JS宏

正好弄了个例子，尽量列出JS宏最常见的套路，方便大家上手

今天的题目是，生成应收账款的账龄分析表。

上年的表是这样：

客户	本年增加	本年减少	1年以内	2年以内	3年以内	4年以内	5年以内	5年以上
A	0	0	100	101	102	103	104	105
B	0	0	101	102	103	104	105	106
C	0	0	102	103	104	105	106	107
D	0	0	103	104	105	106	107	108
E	0	0	104	105	106	107	108	109
F	0	0	105	106	107	108	109	110
G	0	0	106	107	108	109	110	111
H	0	0	107	108	109	110	111	112
I	0	0	108	109	110	111	112	113
J	0	0	109	110	111	112	113	114

今年的数据是这样：

客户	本年增加	本年减少
F	600	100
G	700	200
H	800	300
J	1000	500
A	100	600
B	200	700
C	300	800
D	400	900

“本年增加”要作为新的“1年以内”

去年的账龄要整体右移1年

“本年减少”要先抵扣账龄最长的，因为先进先出

规则很简单，要据此生成新的账龄表

第一步获取上年数据

    const 上年工作簿 = Workbooks.Open(`${ThisWorkbook.Path}/2024应收账款.xlsx`, false, true)
    
    const 上年明细表 = 上年工作簿.Sheets("明细表")
    const 上年数组 = 上年明细表.Range(`A2:I${上年明细表.Range(`A${上年明细表.Rows.Count}`).End(xlUp).Row}`).Value2
    const 上年字典 = {}
    for (const i of 上年数组) {
        上年字典[i[0]] = i.slice(3)
    }
    
    上年工作簿.Close()

整体的套路是，打开xlsx，获取“明细表”，将数据存入字典，关闭xlsx：

Open第三个参数是只读打开，可以加快速度

End(xlUp).Row相当于按ctrl加方向键，是为了找到A列里有数据的最后一行行号

因为今年的客户名称和去年的顺序不同，需要查询，而这种类似vlookup、filter的操作一般都用字典，所以将“上年数组”里的数据以“上年字典”的形式重新存了一份

第二步获取今年数据

    const 今年明细表 = ActiveWorkbook.Sheets("明细表")
    const 今年数组 = 今年明细表.Range(`A2:C${今年明细表.Range(`A${今年明细表.Rows.Count}`).End(xlUp).Row}`).Value2

和第一步一样，获取“今年数组”即可

第三步逐行处理数据

    const 今年账龄数组 = []
    for (const i of 今年数组) {
        const 单行账龄 = 上年字典[i[0]]
        单行账龄.unshift(i[1])
        单行账龄[单行账龄.length - 2] += 单行账龄[单行账龄.length - 1]
        单行账龄.pop()
        
        let j = 单行账龄.length - 1
        while (i[2] > 0) {
            if (j === 0 || 单行账龄[j] > i[2]) {
                单行账龄[j] -= i[2]
                i[2] = 0
            }
            else {
                i[2] -= 单行账龄[j]
                单行账龄[j] = 0
            }
            j--
        }
        
        今年账龄数组.push(单行账龄)
    }
    今年明细表.Range(`D2`).Resize(今年账龄数组.length, 今年账龄数组[0].length).Value2 = 今年账龄数组

第一行新建空白数组

最后一行写入结果

中间是个for循环，逐行处理上年数据，并生成结果，处理的过程是