文本中金额提取方法

发表于 更新于 分类于 ， 阅读次数： 评论数：
一种在excel出现lambda之前，在文本中以特定字符结尾的数字提取的方法。基于此，以特定字符开头也类似

背景

A1单元格有这样一段文字，需要提取出里面的金额并进行求和

基本养老保险基金收入1883亿元、基本医疗保险基金收入836亿元、失业保险基金收入36亿元、工伤保险基金收入36亿元

那么可以用这个公式做到

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
=SUM(
    IFERROR(
        --MID(
            TEXT(
                MID(
                    A1,
                    IF(
                        MID(A1, ROW(1:60), 1) = "亿",
                        ROW(1:60) - COLUMN(A:I),
                        0
                    ),
                    COLUMN(A:I)
                ),
                
            ),
            2,
            MAX(COLUMN(A:I))
        ),
        0
    )
)

实际计算结果与答案一致

解析

该公式的提取思路大致如下：

  1. 先找到数字尾部的特定字符
  2. 从特定字符开始往前进行字符串截取，这里要一直截取到数字前的文本部分，保证金额被完整截取
  3. 将所有数值替换掉，因为这里面存在不完整的金额数据。替换后剩下的内容构成为“一个其它字符+完整金额”、“两个其它字符+完整金额”…
  4. 对上一步所有结果进行第一位的字符剔除，这样保证了得到得数值都是完整的金额而且不重复
  5. 对结果数据进行数值转化，将报错的内容替换为0，这样就得到了数值类型的完整金额数据
  6. 对金额进行对应运算（求和、平均等）即可

需要注意的是这个公式只能提取以特定字符结尾的数字。公式中用到了几个我不熟悉的知识点。第一个是text()第二参为空可以将纯数字构成的字符串置空。第二个是矩阵，row(1:60)或生成1~60的一列数据，column(a:i)或生成1~60的一行数据，当对它们进行运算时会生成$60×9$的矩阵。

为了方便理解，使用LET对原本公式进行拆分，拆分后的公式如下：

1
2
3
4
5
6
7
8
9
10
11
12
13
=LET(
    rag, A1,
    row, ROW(1:60),
    col, COLUMN(A:I),
    a_1, MID(rag, row, 1),
    a_2, IF(a_1 = "亿", row - col, 0),
    a_3, MID(rag, a_2, col),
    a_4, TEXT(a_3, ),
    a_5, MID(a_4, 2, MAX(col)),
    a_6, IFERROR(--a_5, 0),
    a_7, SUM(a_6),
    a_7
)

其中rag指要提取内容所在的单元格，row的长度应该至少等于最后一个特定字符在字符串中的位置，col的长度需要大于要提取金额的位数(包含小数点)。基于上面内容，现在可以来逐步分析公式是如何得到最终结果：

  • a_1：对要提取的内容单元格进行逐字符拆分，并排成一列

  • a_2：判断拆分后的字符是否为特定的字符“亿”，如果是则在这一列逐个向前计算字符的位置作为起始位置，逐个向前是因为特定字符“亿”为结尾字符；如果不是则都设置为0

  • a_3：截取根据a_2计算出的位置和当前的列号进行字符串截取，满足条件行的第一列会得到“亿”前一个字符、第二列会得到“亿”前两个字符……不满足条件的则会报错

  • a_4：将截取后的字符中纯数字构成的字符串替换为空，这是因为逐个递增截取得到的纯数字中会包含不完整的金额数字，而数字和非数字混合构成的字符串一定包含完整的金额

  • a_5：对混合字符串进行舍去左边以为字符的方式进行截取，得到唯一一个完整金额字符串。因为col的长度保证了a_4中存在一位字符和完整金额构成的字符串，而逐个递增截取的方式保证如果存在则只有一个

  • a_6：使用--运算符将字符串转换数值类型，使用iferror()将报错统一设置为0，这样就是实现了提取

  • a_7：对a_6运用相应的统计公式即可