Excel 如何拆解金额?

支票或收据中都会有图 7.48 所示形式的金额拆解过程,将一个多位数的金额按值的大小拆分到对应的多个单元格中,是否有办法一次性将图中所有金额拆解完成呢?

图7.48 待拆解的金额表

解题步骤

如果金额都统一长度,那么利用分列工具就可以一次性拆解所有金额,本例中金额长短不一,因此需要使用公式来完成,具体操作步骤如下。

1.选择B2单元格,然后输入以下公式:

2.将B2的公式向右填充到L2。

3.将B2:L2的公式向下填充到第7行,此时所有金额都会自动拆解到B2:L7区域中,效果如图7.49所示。

图7.49 金额拆解结果

知识扩展

1.函数 COLUMN 用于生成指定单元格的列号,当参数包含多个单元格时,仅生成第一个单元格的列号,例如:

=COLUMN(A1)——结果为1,A1单元格的列号为1;

=COLUMN(G22)——结果为7,G列的列号为7;

=COLUMN(F5:H8)——结果为6,参数包含12个单元格,但只生成F5的列号6,如果参与数组运算,则会生成12个列号,不过本例中的公式不是数组公式

本例中COLUMN的参数是相对引用,因此当公式向右填充到L列时,它生成的值是1~11的自然数序列。

2.RIGHT函数用于提取指定字符串右边若干位的字符,它的语法如下:

RIGHT(text,[num_chars])

其中第一参数代表字符串,第二参数代表提取长度,当忽略此参数时则调用默认值1。

=RIGHT(B1)——当B1单元格的值是“中国人”时,公式返回“人”;

=RIGHT(B1,3)——当B1单元格的值是“中国人”时,公式返回“中国人”;

=RIGHT(B1,20)——当B1单元格的值是“中国人”时,公式返回“中国人”,第二参数20大大超过了第一参数的字符长度,此时会自动忽略超过总长度的部分。

3.LEFT函数和RIGHT函数的功能相反,它用于提取指定字符串左边若干位的字符。参数含义和函数用法与RIGHT一致。

4.本例中的金额有0位、1位或2位小数,从带有小数点的数值中提取字符会把小数点也提取出来,而右边的拆解区域未保留小数点位置,因此公式的第一步就是将目标数值乘以100从而去掉小数点。然后在目标数值前添加“¥”,其中右边的“¥”用于防错,将它放置在金额的左方,打印出来后可以防止他人在金额前面手工添加数值,从而影响金额的正确性。而¥前面的空格则是占位符,将它提取出来放置在¥前面的单元格中,仅仅占位,没有实质用处。假设没有空格,那么公式的拆解结果区域中会产生多个¥。

5.公式“=LEFT(RIGHT("¥"&$A2*100,12-COLUMN(A1)))”的整体含义是:首先对金额去除小数点,并在前面添加“¥”,然后利用Right函数分别提取左边1位、2位、3位、4位……11位字符,最后用Left函数从Right产生的结果中提取左边一位字符。

当金额是164257.45时,从“¥16425745”右边分别提取1位、2位、3位、4位……11位字符,产生的结果为“¥16425745”、“¥16425745”、“¥16425745”、“16425745”、“6425745”、“425745”、“25745”、“5745”、“745”、“45”、“5”。此时仔细观察这一组数据,它的最左边一位字符就是题目中所需要的拆解结果,因此公式的最外层使用了Left函数提取最左边的字符。

发布者

Excel22

专为职场萌新准备的免费全面的Excel入门及提高学习网站,也可作为Excel老司机杂耍各种函数的宝典 —— Excel22.com 网址超好记

发表评论

邮箱地址不会被公开。 必填项已用*标注