使用AFE(Advanced Formula Environment)计算折旧

效果

自定义好函数后，只要调用函数，选原始数据的表作为参数 1，一个日期值作为参数 2，便可以直接计算出各项折旧相关的数据，包括当月折旧，年初时的折旧数，本年的折旧，累计折旧，并生成对应的表格。如下图：

代码

CalcDepre = LAMBDA(data, date,
    LET(
        inputDate, CHOOSECOLS(data, 4), //购入月份数的数组
        allMonthCount, CHOOSECOLS(data, 5), //使用年限对应月份数的数组
        rowCount, ROWS(data), //表格的行数
        
        //使用makearray计算出4种月份差异数
        monthCounts, MAKEARRAY(
            rowCount,
            1,
            LAMBDA(r, c,
                LET(
                    _startOfYear, DATE(YEAR(date) - 1, 12, 1),
                    _allMonthCount, INDEX(allMonthCount, r, c),
                    _inputDate, INDEX(inputDate, r, c),
                    //未考虑过大与负数，计算的当前月距离购入月的月份差异数。
                    _mc, YEAR(date) * 12 + MONTH(date) - YEAR(_inputDate) * 12 - MONTH(_inputDate),
                    _monthCount, MIN(MAX(0, _mc), _allMonthCount), //考虑后的差异数，最小为0，最大为折旧年限对应的月数
                    //未考虑过大与负数，计算的前一年12月距离购入月的月份差异数。
                    _smc, YEAR(_startOfYear) * 12 + MONTH(_startOfYear) - YEAR(_inputDate) * 12 -
                        MONTH(_inputDate),
                    _startMonthCount, MIN(MAX(0, _smc), _allMonthCount), //考虑后的差异数，最小为0，最大为折旧年限对应的月数
                    TEXTJOIN(",", 0, _mc, _monthCount, _smc, _startMonthCount)
                )
            )
        ),
        
        //使用MAP组出上面计算好的4列值
        mc, MAP(monthCounts, LAMBDA(x, INDEX(TEXTSPLIT(x, ","), 1, 1))),
        monthCount, MAP(monthCounts, LAMBDA(x, INDEX(TEXTSPLIT(x, ","), 1, 2))),
        smc, MAP(monthCounts, LAMBDA(x, INDEX(TEXTSPLIT(x, ","), 1, 3))),
        startMonthCount, MAP(monthCounts, LAMBDA(x, INDEX(TEXTSPLIT(x, ","), 1, 4))),
        monthDepre, (CHOOSECOLS(data, 6) - CHOOSECOLS(data, 7)) / allMonthCount, //每个月的折旧额
        startDepre, monthDepre * startMonthCount,
        
        //当月晚于购入月，且当前月早于提完折旧月的情况，月折旧就是当月的折旧
        currentDepre_, IF(N(mc) <= allMonthCount, monthDepre, 0),
        currentDepre, IF(N(mc) > 0, currentDepre_, 0),//注意使用N转成数值，本人在这坑了蹲子很久
        //注意：currentDepre,IF( and(mc<=allMonthCount,mc>0),monthDepre,0)，这种写法是得不到
        //正确结果的，因为and函数不是每次取一行执行判断选择monthDepre还是0，而是一次把所有结果算完，
        //大多数情况下，结果都只会是False

        accuDepre, monthDepre * monthCount, //累计折旧
        yearDepre, accuDepre - startDepre, //当年的折旧
        head, {"当月折旧", "年初折旧", "本年折旧", "累计折旧"},
        body, HSTACK(currentDepre_, startDepre, yearDepre, accuDepre),
        VSTACK(head, body)//拼装成表格
    )
);

总结

胜在使用起来会十分简洁。
一般的作法，会产生大量的工作表。其实里面大多数数都是重复。

这种作法，只有一页表，改一下年月就可以看不同年月的折旧情况。

其实，我也很困惑，写了很久。

文件下载

示例文件下载：
链接: https://pan.baidu.com/s/1C7uQ9zssEk560wrtCpy0Kg?pwd=1234
提取码: 1234