Excel金额数据拆分:财务必会技能-记帐或支票填写

Excel金额数据拆分:财务必会技能-记帐或支票填写
2024年01月26日 10:35 部落窝教育

编按:财会人员在记帐或支票填写等工作中,经常会涉及到将金额数据拆分,并分列填写在对应的单元格今天就来给大家讲讲,如何用函数公式来快速处理这个问题。

使用Excel处理这些数据,如果一个一个数字填写不但非常麻烦,更可能会出错,如果可以实现金额数据自动分列,就好了。

方法一:用空格补足位数然后用MID从需要位置逐个取1位数据

效果如动态图所示。

这个例子中的公式为:=IF(LEN($A2*100)=10,MID($A2*100,COLUMN(A1),1),MID(REPT(" ",10-LEN("¥"&$A2*100))&"¥"&$A2*100,COLUMN(A1),1))

下面就为大家解读一下这个公式的原理,方便根据自己的情况修改套用。

首先来了解一个整数进行拆分的公式:=MID($A2,COLUMN(A1),1)

这个公式很好懂的,就是MID函数的基础用法,对A列的数字进行提取,第三参数填1表示每次提取一个数字,关键是第二参数使用COLUMN(A1),就可以实现开始提取的位置随着公式右拉变成1、2、3……

当数字有小数点时,如果公式不加修改就会得到这样的结果。

小数点也会占一个位置,这不是我们需要的结果。

有个简单的办法可以避免这个情况,那就是对A列的数字扩大100倍,公式修改为=MID($A2*100,COLUMN(A1),1)

这样做的好处有两个,第一是把小数点给去掉了,第二是用0补充缺少的角或分数据。

现在离最终结果只剩一步,就是实现靠右对齐的效果,将空白的单元格调整到最左边。

这就要在数字前面添加空格来补齐位置才行,也是整个公式中最有技术含量的部分。

难点在于确定到底补几个空格,这需要两个信息:最大是几位数,实际是几位数,二者之差就是需要补齐的空格数。

本例中从分到千万最大位数是10,也就是B:K的列数。

至于实际有几位数就很容易判断,直接用LEN($A2*100)就能数出来。

所以10- LEN($A2*100)就是需要补空格的个数。

知道要补什么和补几个,就可以使用REPT这个函数来实现。

所以补空格的公式为REPT(" ",10-LEN($A2*100)),再用&把$A2*100连接起来,MID要提取的数字才算是构造完成。

又因为财会需要在不到10位的数字前加货币符号¥,满10位数字则不加货币符号¥,所以公式嵌套IF函数:

=IF(LEN($A2*100)=10,MID($A2*100,COLUMN(A1),1),MID(REPT(" ",10-LEN("¥"&$A2*100))&"¥"&$A2*100,COLUMN(A1),1))

注:公式中的数字10根据实际最大位数来修改。如果最大位数是11,则公式中所有10都变成11。

方法二:先用RIGHT获取不同位数的数字然后用LEFT提取第一位

动态效果如下:

所用的公式=LEFT(RIGHT(" ¥"&$A2*100,11-COLUMN(A1)),1)。

注意¥前方有一个空格。

公式解析:

假设用"¥"&$A2*100(¥前方没有空格)刚好能组成10位数字,则从B2开始到K2,用RIGHT逐个提取10、9……1个数,得到如下:

再用LEFT提取每个数据的第一位,就得到分列效果:

可是当"¥"&$A2*100(¥前方没有空格)组成的数字不够10位,同样的方法就会出现多个¥符号:

怎么去掉重复的¥?在¥前加空格,即用" ¥"&$A2*100(¥前方有空格)组建数据,重复的¥符号就被空格取代:

第二个方法比第一个方法更简洁,推荐使用。

版权申明:

本文作者老菜鸟;同时部落窝教育享有专有使用权。若需转载请联系部落窝教育。

财经自媒体联盟更多自媒体作者

新浪首页 语音播报 相关新闻 返回顶部