excel中最常用的10个函数（Excel函数的冷门技巧）

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

---

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

---

怎样分离姓名和电话号码

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

图3-10-17

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

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

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

图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函数截取电话号码。

图3-10-19

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

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

语法：

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

• text为需要替换其中字符的文本或对含有文本的单元格的引用。
• old_text为需要替换的旧文本。
• new_text用于替换old_text的文本。
• instance_num为一数值，用来指定以new_text替换第几次出现的old_text。

如果指定了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所示。

图3-10-20

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

图3-10-21

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

图3-10-22

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

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

图3-10-23

图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所示。

图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倍后再转换为大写。

,