excel中最常用的10个函数(Excel函数的冷门技巧)(1)

Excel函数的冷门技巧,很多人不知道,学会告别加班




excel中最常用的10个函数(Excel函数的冷门技巧)(2)

  1. 如何分离姓名和电话号码?
  2. 巧用SUBSTITUTE函数进行文本字符串的替换!
  3. 长相相似但功能相反的两个函数——CODE和CHAR!
  4. 怎样将带有小数点的小写数字转化为大写?


excel中最常用的10个函数(Excel函数的冷门技巧)(3)




怎样分离姓名和电话号码

如图3-10-17所示,要把A列内容拆分到B列和C列。这个问题可以用第1章第10招介绍的快速填充一秒钟搞定,如果A列内容变了,B列和C列要自动变化就得用公式与函数了。


excel中最常用的10个函数(Excel函数的冷门技巧)(4)

图3-10-17

如果A列姓名都是2个汉字,电话号码都是11位固定长度,我们可以通过分列实现,第一步选择固定宽度,第二步建立分列线,直接下一步就可以实现分开,如图3-10-18所示。

上如例子姓名长度不固定,要拆分姓名和电话号码可以用公式与函数实现,汉字是双字节,而字母和数字是单字节。

而在Excel函数中有一类是带B的函数LENB,LEFTB,RIGHTB,MIDB,SEARCHB,它们可以区分单双字节,所以我们就可以利用带B的函数来解决这个问题。


excel中最常用的10个函数(Excel函数的冷门技巧)(5)

图3-10-18

B2公式:=LEFTB(A2,SEARCHB("?",A2)-1)

C2公式:=MIDB(A2,SEARCHB("?",A2),11)

公式说明:

SEARCHB是在一个字符串中查找特定字符位置的函数,而且可以区分单双字节,它和FIND的区别是可以使用通配符。

公式中的?就是表示任意一个单字节的字符,属通配符,不是真的查找问号。MIDB是按字节数截取。一个汉字算两个字节,字母和数字分别算一个。

也可以用下面的公式来实现,LEN函数返回字符数(char),LENB返回字节数(byte),2个函数返回值相减就得到汉字的个数,再用LEFT和RIGHT函数截取姓名和电话号码。

D2公式:=LEFT(A2,LENB(A2)-LEN(A2))

E2公式:=RIGHT(A2,LEN(A2)-(LENB(A2)-LEN(A2)))

如图3-10-19所示,公式说明:

LENB(A2)-LEN(A2)得到姓名的字符数,姓名在左边,用LEFT函数截取姓名。LEN(A2)-(LENB(A2)-LEN(A2))A2单元格内容总长度减去姓名的字符数就得到电话号码的字符数,电话号码在右边,再用RIGHT函数截取电话号码。


excel中最常用的10个函数(Excel函数的冷门技巧)(6)

图3-10-19

文本字符串的替换——SUBSTITUTE函数

SUBSTITUTE函数在文本字符串中用new_text替代old_text。如果需要在某一文本字符串中替换指定的文本,请使用函数SUBSTITUTE;如果需要在某一文本字符串中替换指定位置处的任意文本,请使用函数REPLACE。

语法:

SUBSTITUTE(text,old_text,new_text,[instance_num])

如果指定了instance_num,则只有满足要求的old_text被替换;

如果缺省则将用new_text替换text中出现的所有old_text。

例如,A1单元格内容为腾迅,公式=SUBSTITUTE(A1,"迅","讯")返回结果腾讯。

长相相似但功能相反的两个函数——CODE和CHAR函数

在计算机数据表示中,每一个字符都有对应的ASCII码与其对应,我们可以借助CHAR函数将ASCII码转换成对应的字符,该函数在编程及循环操作时常见。

反过来,如果要将字符转换为对应的ASCII码,则借助函数CODE。下面就一起来了解一下这两个函数的使用方法。

首先来看一下CHAR函数的语法:

CHAR(number)。

number:代表用于转换的ASCII码字符代码,使用的是当前计算机字符集中的字符。

在A2单元格中输入“65”,然后在B2单元格中输入公式“=CHAR(A2)”即可生成大写字母A,将A列ASCII码依次增加,同时对B列进行公式的复制,就会发现自动生成后续字母。在C列输入97到122,D列输入公式“=CHAR(C2)”,向下复制公式,可以自动生成小写字母,如图3-10-20所示。


excel中最常用的10个函数(Excel函数的冷门技巧)(7)

图3-10-20

如果需要输入圆圈内带数字的字符,输入数字代码,借助函数CHAR就可以得到,如图3-10-21所示。


excel中最常用的10个函数(Excel函数的冷门技巧)(8)

图3-10-21

再看看汉字,比如,我的姓名对应的数字代码如图3-10-22所示。


excel中最常用的10个函数(Excel函数的冷门技巧)(9)

图3-10-22

几个数字就代表一个汉字,是不是感觉很神奇?这就是计算机强大的记忆功能。

再看看CODE函数,这个函数的功能是用于返回与字符相对应的字符编码,如图3-10-23和图3-10-24所示。


excel中最常用的10个函数(Excel函数的冷门技巧)(10)

图3-10-23


excel中最常用的10个函数(Excel函数的冷门技巧)(11)

图3-10-24

怎样将带有小数点的小写数字转化为大写

Excel中要将人民币小写金额转换成大写格式,将自定义格式类型中的“G/通用格式”改为“G/通用格式"元"”来实现。

但在转换小数时却出现了问题,比如123.45元只能转换为“壹佰贰拾叁.肆伍”。那怎么解决这一先天不足呢?

我们可以利用公式和函数解决,A1单元格是小写数字,我们在B1单元格输入公式=TEXT(INT(A1),"[DBNum2]G/通用格式")&"元"&TEXT(MOD(A1,1)*100,"[DBNum2]0角0分")就可以将A1单元格的小写数字转换为大写数字,如图3-10-27所示。


excel中最常用的10个函数(Excel函数的冷门技巧)(12)

图3-10-27

公式里用到了4个函数,TEXT,INT,DBNum2,MOD,我们来一一解释这4个函数的功能。TEXT函数是将数值转换为指定数字格式表示的文本,语法是:TEXT(数值,指定格式的文本)。

[DBNum2]是格式函数,小写数字转中文大写。

例如,公式=TEXT(123,"[DBNum2]")返回的结果是壹佰贰拾叁。

INT函数是将任意实数向下取整为最接近的整数。

例如,INT(123.56)返回结果是123。

MOD函数是两数相除的余数,语法是:MOD(被除数,除数),例如,MOD(25,2)返回结果是1。了解了函数的功能,我们再来看看公式前面一部分=TEXT(INT(A1),"[DBNum2]G/通用格式"),是将小数点前面的整数部分转换为大写,后面一部分TEXT(MOD(A1,1)*100,"[DBNum2]0角0分")是将小数点后面的小数部分放大100倍后再转换为大写。

,