Excel常用技巧(三)——函数_exce用if条件返回值带入到计算式后结果不对

Excel常用函数

》本文转载自：ExcelHome，http://www.excelhome.net/lesson/article/excel/1897.html
》并对以上链接进行稍做整理

1、IF函数条件判断

IF函数是最常用的判断类函数之一，能完成非此即彼的判断。
如下图，考核得分的标准为9分，要判断B列的考核成绩是否合格。
编写：=IF(B4>=9,"合格","不合格")

IF，相当于普通话的“如果”，常规用法是：

• =IF(判断的条件,符合条件时的结果,不符合条件时的结果)

---

2、多条件判断

如下图所示，如果部门为生产、岗位为主操 有高温补助。在D列使用公式：
编写：=IF(AND(B2="生产",C2="主操"),"有","无")

AND函数对两个条件判断，如果同时符合，IF函数返回“有”，否则为无。

---

3、条件求和

如下图所示，使用SUMIF函数计算一班的总成绩：
编写：=SUMIF(D2:D5,F2,C2:C5)

SUMIF用法是：

• =SUMIF(条件区域,指定的求和条件,求和的区域)

用通俗的话描述就是：
如果D2:D5区域的班级等于F2单元格的“一班”，就对C2:C5单元格对应的区域求和。

---

4、多条件求和

如下图所示，要统计部门为生产,并且岗位为主操的补助总额。
编写：=SUMIFS(D2:D9,B2:B9,F2,C2:C9,G2)

SUMIFS用法是：

• =SUMIFS(求和的区域，条件区域1,指定的求和条件1,条件区域2,指定的求和条件2,……)

---

5、条件计数

如下图，要统计指定店铺的业务笔数。也就是统计B列中有多少个指定的店铺名称。
编写：=COUNTIF(B2:B12,E3)

COUNTIF函数统计条件区域中，符合指定条件的单元格个数。常规用法为：

• =COUNTIF(条件区域,指定条件)

---

6、多条件计数

要求：统计统计部门为生产,并且岗位为主操的人数
编写：=COUNTIFS(B2:B9,F2,C2:C9,G2)

COUNTIFS函数统计条件区域中，符合多个指定条件的单元格个数。常规用法为：

• =COUNTIFS(条件区域1,指定条件1,条件区域2,指定条件2……)

---

7、条件查找

VLOOKUP函数一直是大众情人般的存在，函数的语法为：

• =VLOOKUP(要找谁,在哪儿找,返回第几列的内容,精确找还是近似找)

如下图，要查询F3单元格中的员工姓名是什么职务。
编写：=VLOOKUP(F3,B2:D6,2,0)

使用该函数时，需要注意以下几点：

1. 第1个参数，选择需要查找的值【注：查找值必须位于查询区域(紫色部分)中的第一列】
2. 第2个参数，选择需要查找的区域【注：查找值必须位于查询区域(紫色部分)中的第一列】
3. 第3参数中的列号，不能理解为工作表中实际的列号，而是指定返回值在查找范围中的第几列（即：部门那一列(第2列)）
4. 第4参数一般用0（或FASLE）以 精确匹配 方式进行查找
5. 如果查找值与数据区域关键字的数据类型不一致，会返回错误值 “#N/A”

---

8、多条件查找

如下图所示，要求查询部门为生产,并且岗位为部长的姓名。
编写：=LOOKUP(1,0/((B2:B9=F2)*(C2:C9=G2)),A2:A9)

LOOKUP函数多条件查询写法为：

• =LOOKUP(1,0/((条件区域1=条件1)*(条件区域2=条件2)),查询区域)
• 函数中的1和0是固定的
• 万能套用公式：=lookup(1,0/(查找值=查找列),结果列)

---

9、合并多个单元格内容

要连接合并多个单元格中的内容，可以使用&符号完成。如下图，要合并A列的姓名和B列的电话号码，可以编写：=A2&B$1&B2

---

10、合并带格式的单元格内容

合并带有格式的内容时，Excel默认按常规格式进行合并，但是如果是日期、时间或是其他有格式的数值，结果就会让人大失所望了：

如何才能正确连接出需要的字符串呢？其实很简单，C2公式为：=A2&TEXT(B2,"y年m月d日")

首先使用TEXT函数，把B列的日期变成具有特定样式的字符串，然后再与A列的姓名连接，就变成了最终需要的样式。

---

11、文本替换——替换部分电话号码

如下图所示，要将手机号码的中间四位换成星号。编写：=SUBSTITUTE(B2,MID(B2,4,4),"****",1)

SUBSTITUTE函数的用法是：

• SUBSTITUTE(要替换的区域,旧文本,新文本,[替换第几个])

1. 先使用MID函数取得B列号码中的中间4位；
2. 再用 “ ***** ” 替换掉这部分内容；
3. 最后一个参数使用1，表示只替换第一次出现的内容。

比如第九行的电话号码是13801010101，最后四位和中间四位相同，如果不指定1，就会全部替换掉了。

---

12、文本提取

》有三个方法：LEFT()、RIGHT()、MID()

① LEFT函数：
LEFT英文翻译成中文是“左边”的意思，顾名思义是用来提取字符串左边的几个字符。
LEFT函数有两个参数，其构造是：=LEFT（想要提取的字符串，返回左边的几个字符）

• 如下图操作：（以身份证为例）
  

② RIGHT函数：
RIGHT英文翻译成中文是“右边”的意思，顾名思义是用来提取字符串右边的几个字符。
RIGHT跟LEFT一样只有两个参数，其构造是：=RIGHT（想要提取的字符串，返回右边的几个字符）

• 如下图操作：（以身份证为例）
  

③ MID函数：
MID英文翻译成中文是“中间的”的意思，顾名思义是用来提取字符串中间的几个字符。
MID函数有三个参数，其构造是：=MID（想要提取的字符串，从字符串的第几位开始，向右返回几个字符）

---

13、提取混合内容中的姓名

如下图，要从A列姓名电话中提取出姓名，除了使用高版本的自动填充功能，还可以使用公式完成：=LEFT(A2,LENB(A2)-LEN(A2))

LENB函数将每个汉字（双字节字符）的字符数按2计数，LEN函数则对所有的字符都按1计数。因此“LENB(A2)-LEN(A2)” 返回的结果就是文本字符串中的汉字个数。
LEFT函数从文本字符串的第一个字符开始，返回指定个数的字符，最终提取出员工姓名。【RIGHT函数则相反，MID函数则从中间取】

---

14、根据身份证号码提取出生年月

》若出现身份证号码为科学计数法格式，那么就先删除，再将该列设置单元格格式为：文本，即可。
编写：=1*TEXT(MID(B2,7,8),"0-00-00")

①首先使用MID函数从B2单元格的第7位开始，提取出表示出生年月的8个字符，结果为：
“19780215”
②再使用TEXT函数将字符串转换为日期样式：
“1978-02-15”
③然后通过*1计算，将其转换为真正的日期。最后设置为日期格式即可。

---

15、数据查找 / 数据验证

以下图数据为例，需要从J列中，查找是否存在 “数据分析”的字段

》该案例用到了 FIND()函数、ISNUMBER()函数

FIND()函数的用法：

• FIND( 要查找的文本，文本所在的单元格，从第几个字符开始查找[可选，省略默认为1，从第一个开始查找])
• 它返回的是数值格式，即字段在查找区域的哪个位置

ISNUMBER()函数的用法：

• ISNUMBER( VALUE )
• 判断引用的参数或指定单元格中的值是否为数字，正确返回TRUE，否则返回FALSE

案例讲解：
》若FIND函数返回数值，则ISNUMBER函数将该数值转换为TRUE，表示在查找区域中找到该字段
》若FIND函数返回不是数值，则ISNUMBER函数将该值转换为FALSE，表示在查找区域中未找到该字段
》通常，FIND函数和ISNUMBER函数结合使用，准确判断字段是否存在

---

16、屏蔽函数公式返回的错误值

在使用函数公式过程中，经常会返回一些诸如 #N/A、#NAME? 之类的错误值，要屏蔽这些错误值其实很简单，只需在原公式外侧加上一个IFERROR函数就好。
IFERROR函数的用法为：

• =IFERROR(原公式,出现错误时要返回的内容)

如果公式正确，就返回原有计算结果，如果公式返回的是错误值，就返回用户指定的显示内容。

---

17、四舍五入函数

ROUND函数这个想必大家经常用到吧，就是对数值按指定的位数四舍五入。比如：=ROUND(8/9,3)
就是将8/9的计算结果四舍五入到三位小数，结果为0.889

---

18、提取日期时间中的日期值

要从日期时间数据中提取出日期，可以使用以下公式：=INT(A2)
要继续提取时间，只需要做个减法，就ok了：

---

19、生成随机数

RANDBETWEEN能够在指定范围内生成一组随机数据
函数的用法是：

• =RANDBETWEEN(数字下限,数字上限)

比如以下公式，就是生成60到100之间的随机数：=RANDBETWEEN(60,100)

---

20、带单位的数值相加

如下图所示，计算 100g+2000g+300g 的值：

用left()函数结合len()函数，获取到数值结果，接着用sum(--)函数对结果进行求和计算，再到sum()后面加上：&"g" 给定单位，最后按 ‘ctrl+shift+enter’，求得最终结果。

---

21、计算文本算式

如下图，要计算单元格中的文本算式，先单击第一个要输入公式的单元格
定义名称 ：计算
引用位置：=EVALUATE(C2)
然后在单元格中输入公式：=计算

---

22、比较大小写的单词是否相同

如果在A1和A2单元格中分别输入大小写的单词，使用以下公式判断时，Excel会默认二者是相同的：=A2=B2

如需区别大小写，可以使用公式：=EXACT(A2,B2)
EXACT()函数 区分大小写，但忽略格式上的差异

---

23、$符号的运用

简单讲解：

》$表示静态引用（也叫绝对引用）的意思。即固定引用的单元格的行或列。键盘上按F4可以调出该符号

》一个单元格地址由两部分组成：一个是字母（代表列数），一个是数字（代表行数）。

• 字母横着拉会变，竖向拉，不管前面加不加$都不会变。
• 同样，数字竖向拉会变，横着拉不管加不加$都不会变。

》相对引用和绝对引用的精髓就是：$在谁前面，谁不动。

以乘法表为例，如下二图所示：

》如C2单元格，设置为：=C$1*$A2。在A2前加$符号，那么往右延长，该A2固定不变。即行值不变。
》如B3单元格，设置为：=B$1*$A3。在B1中间加$符号，那么往下延长，该B1固定不变。即列值不变。