我们都知道当数据过多的时候,我们制作Excel图表就会显得非常的复杂,图表上面的内容就会特别多。Excel老玩家就会想到用切片器制作动态可变化的图表来显示。今天我们就来学习一下一个比......
2023-01-08
某些财务软件导出的科目余额表中是“1122.01.898偷懒的技术“这样的格式,需要将代码和名称分离,或者遇到不规范的数据,如人名与手机号“龙逸凡18X12345678”,需要将数字和汉字分离为二列。
解决方案
如果科目代码长度一致,或者代码和名字中有某个固定分隔符,则可使用“分列”功能来分离科目代码和名称,如果没有,则需要使用下面的公式:
取科目代码=LEFT(A2,2*LEN(A2)-LENB(A2))
取科目名称=RIGHT(A2,LENB(A2)-LEN(A2))
这个公式不太完善,当名称中有字母或数字时,则公式结果会出错,比如B16、B17单元格,这种情况下提取科目代码需使用下面的数组公式:
=LEFT(A2,MIN(IF(MID(A2,ROW($1:$99),1)>=”a”,ROW($1:$99),99))-1)
输入完后需要同时按Ctrl+Shift+Enter三键输入
如果电脑上安装了Excel2013以后的版本,也可使用快速填充,具体操作详见下面的操作演示:
知识点解释
第一个公式中的Len函数:计算字符数。LENB函数:计算字节数,一个汉字为二个字节。所以可以用LENB-LEN计算字符串中汉字的个数。由于公式是根据汉字个数来分离数字和汉字,所以,当名称中包含字母或数字,以及数字和汉字相互夹杂时,公式提取的结果就不符合要求。
第二个公式的设计思路是逐个截取字符串中的每一个字符,判断其是否为字母或汉字,即是否为”a”之后的字符(在Excel里,如按升序排列,则字母在数字后,汉字在字母后,也就是1→9→a→z→吖(ā)→酢(zuò),这也是它们的大小顺序)。
公式使用ROW($1:$99)生成1到99的常量数组,写成99只是为了保证大于等于字符串的长度,根据实际情况也可改成50,30等。
MID(A2,ROW($1:$99),1)是依次截止A2单元格的第1个,第2个。。。第99个。截取出的字符如果是字母或汉字,则给其字符所在位置的顺序号,否则,给它的顺序号是99。再用MIN函数来提取第一个字母、汉字的位置。最后用LEFT来截取左边的数字和字符组成的代码。
同理,如果要提取科目名称,用下列公式即可(别忘了最后用三键输入):
=RIGHT(A2,LEN(A2)-MIN(IF(MID(A2,ROW($1:$99),1)>=”a”,ROW($1:$99),99))+1)
当然,使用上述公司还得有个前提,即避免分录名称的第一个字为数字,比如“1小组”、“2车间”等等,而应采用“一小组”、“二车间”的命名方式。
相关文章
我们都知道当数据过多的时候,我们制作Excel图表就会显得非常的复杂,图表上面的内容就会特别多。Excel老玩家就会想到用切片器制作动态可变化的图表来显示。今天我们就来学习一下一个比......
2023-01-08
在工作中,可能许多朋友都会碰到一个情况,那就是工作簿和工作表数据的合并操作。如何将上百个工作簿快速合并到一个表格中,许多朋友可能会觉得不可思议。今天我们就来教大家学习一......
2023-01-08
今天在这里为你分享5个Excel文本函数,这些拆分和组合函数,你一定会用上的。①LEFT函数公式:=LEFT(A2,1)在Excel表格中,需要想要拆分汉字,想从哪里开始就从那哪里开始。首先选定单元格......
2023-01-08
相信大家也和我一样,才开始看到Excel可以当做翻译软件的时候会很好奇,这究竟是怎样做到的?其实,这个方法并不是很难,它是由一个函数公式而制作出来的,好了,首先我们一起来看看成......
2023-01-08
函数可以说是所用快捷方法中最为简单的一种方法,为什么很多人认为函数用起来很难了?主要是因为它拥有很长的函数公式,记不住。其实不管是学Excel函数,还是学习其他的一些快捷方法......
2023-01-08