# 工作学习中必备的15个Excel常用函数，帮你节省99%的时间！

**URL:** <https://www.shiqidu.com/t/topic/152>\
**Category:** 资源分享\
**Tags:** Excel\
**Created:** [2017 年3 月 4 日 18:48 UTC](https://www.shiqidu.com/t/topic/152 "2017-03-04T18:48:17Z")\
**Posts on this page:** 1\
**Page:** 1

<div class="post-metadata">

作者： ![shiqidu\_editor](https://www.shiqidu.com/user_avatar/www.shiqidu.com/shiqidu_editor/32/528_2.png) [@shiqidu\_editor](https://www.shiqidu.com/u/shiqidu_editor)\
发布日期： [2017 年3 月 4 日 18:48 UTC](https://www.shiqidu.com/t/topic/152/1 "2017-03-04T18:48:17Z")

</div>

Excel作为工作学习中使用频率很高的软件，使用得当能节省我们很多时间，这些技巧你都知道吗？

### 1、日期与时间函数

**（1）TODAY、NOW、YEAR、MONTH、DAY、WEEKDAY函数**

这是一组非常简单但是又最为常用的日期函数，通常与其他嵌套使用，实现更多样化的结果。

![](https://discourse.shiqidu.com/uploads_nj/article/2017-03-05/58bb032d67ff9.png)

**案例：**

> 一般情况下，男60岁退休，女55岁退休（政策有变，先不予考虑）。我们可以根据员工的出生日期和性别来确定退休日期。  
> 公式=DATE(YEAR(C12)+(B12=“男”)\*5+55,MONTH(C12),DAY(C12))

![](https://discourse.shiqidu.com/uploads_nj/article/2017-03-05/58bb036fdd337.png)

**（2）DATEIF函数**

DATEDIF函数是Excel的一个隐藏函数，不在插入函数的函数库里，在Excel帮助文件里也没有它的身影，是非常重要的日期函数。

![](https://discourse.shiqidu.com/uploads_nj/article/2017-03-05/58bb039c94790.png)

**案例：**

> 现有两个日期，通过DATEIF函数计算两个日期之间的天数、月数和年数，计算结果及结果说明如下。

![](https://discourse.shiqidu.com/uploads_nj/article/2017-03-05/58bb03d3adfb4.png)

### 2、统计分析函数

**（3）COUNT/COUNTA/COUNTBLANK函数**

COUNT函数是最常见的统计函数，主要用于统计单元格区域或数组中数值的个数，文本、空白单元格、逻辑值或错误值无法用COUNT函数来统计。  
COUNTA函数可以返回非空文本的单元格个数，COUNTBLANK函数可以计算指定单元格区域中空白单元格的个数。

![](https://discourse.shiqidu.com/uploads_nj/article/2017-03-05/58bb04074325d.png)

**案例：**

> 现有4组数据，需要将每组数据的最后一个数字提取出来。  
> 公式=INDEX(B19:F19,COUNTA(B19:F19))

![](https://discourse.shiqidu.com/uploads_nj/article/2017-03-05/58bb0439a9601.png)

**（4）LARGE和SMALL函数**

LARGE和SMALL函数代表第几大的数和第几小的数，常与其他函数组合使用。需要注意的是这两个函数对于数据表中相同的数会顺序排位返回相应的值。

![](https://discourse.shiqidu.com/uploads_nj/article/2017-03-05/58bb0462d4b36.png)

**案例：**

> 现在有班级成绩，要求80分以上的升序排列，80分以下的不显示。  
> 公式=IFERROR(SMALL(IF($B$17:$B$28\>80,$B$17:$B$28,“”),ROW(A1)),“”)

![](https://discourse.shiqidu.com/uploads_nj/article/2017-03-05/58bb048b004e1.png)

**（5）MAX和MIN函数**

LARGE和SMALL函数是第几大，第几小，而MAX和MIN函数是极值。

![](https://discourse.shiqidu.com/uploads_nj/article/2017-03-05/58bb04bdb6b93.png)

**案例：**

> 有一个100万的项目招标，报价分为100分，评分条件如下：总价等于价格平均值的，得基本分80分；总价低于价格平均值的，每低2%，加2分，最多100分；不含税总价高于评标基准值的，每高2%，扣4分，最低60分。  
> 最高100分，最低60分公式=MAX(MIN(D2,100),60)

![](https://discourse.shiqidu.com/uploads_nj/article/2017-03-05/58bb04ef01a57.png)

**（6）COUNTIF/SUMIF/AVERAGEIF函数**

COUNTIF/SUMIF/AVERAGEIF函数是对指定区域中符合指定条件的单元格计数/求和/求平均值。

![](https://discourse.shiqidu.com/uploads_nj/article/2017-03-05/58bb05108ffb0.png)

![](https://discourse.shiqidu.com/uploads_nj/article/2017-03-05/58bb052649fa2.png)

**案例：**

> 现有一公司销售数据，需计算销售员“杨过”在2016年的总体表现。

![](https://discourse.shiqidu.com/uploads_nj/article/2017-03-05/58bb054ec8fd8.png)

**（7）COUNTIFS/SUMIFS/AVERAGEIFS函数**

COUNTIFS/SUMIFS/AVERAGEIFS函数主要为计算多个区域中满足给定条件的单元格的个数/和/平均值。

![](https://discourse.shiqidu.com/uploads_nj/article/2017-03-05/58bb057b5e84d.png)

![](https://discourse.shiqidu.com/uploads_nj/article/2017-03-05/58bb0586edc74.png)

**案例：**

> 现有一公司销售数据，需计算销售员“杨过”在2016年第四季度的总体表现。

![](https://discourse.shiqidu.com/uploads_nj/article/2017-03-05/58bb05c2e25ad.png)

### 3、查找引用函数

查找是数据分析处理的一项重要工作，而正确地引用数据则是准确查找的关键。Excel提供有丰富的函数满足我们对数据查找与引用的需求。

**（8）VLOOKUP函数**

VLOOKUP函数在Excel函数江湖中的地位无人能及，有很多人就用VLOOKUP函数+数据透视表横扫职场。

![](https://discourse.shiqidu.com/uploads_nj/article/2017-03-05/58bb05fa6aba8.png)

**案例：**

> 现在一组学生成绩，需提取部分学生的成绩。

![](https://discourse.shiqidu.com/uploads_nj/article/2017-03-05/58bb07317a7c1.png)

函数公式解读：

![](https://discourse.shiqidu.com/uploads_nj/article/2017-03-05/58bb07db55eb6.png)

**（9）INDEX+MATCH函数**

在Excel中，MATCH函数可以用于查找数据的位置，而INDEX函数则可以根据查找到的位置返回实际的单元格引用，因此，在实际工作中这两个函数完美组合可以解决很多问题。

![](https://discourse.shiqidu.com/uploads_nj/article/2017-03-05/58bb080202a01.png)

**案例：**

> 现在一组学生成绩，需根据选定姓名和学科查找成绩。  
> 公式=INDEX($A$1:$D$15,MATCH($F2,$A$1:$A$15,0),MATCH($G2,$A$1:$D$1,0))

![](https://discourse.shiqidu.com/uploads_nj/article/2017-03-05/58bb082dd4506.png)

### 4、逻辑信息函数

**（10）AND/OR函数**

逻辑判断是指一个有具体意义并能判断真假的陈述语句，常用的逻辑关系有三种，即“与”、“或”、“非”，在Excel中分别用AND函数、OR函数、NOT函数表示。我们工作中最常用的就是AND函数和OR函数。

![](https://discourse.shiqidu.com/uploads_nj/article/2017-03-05/58bb0864344d7.png)

**案例：**

> 公司计划组织员工免费参加培训，条件是25岁以下的女性或者40岁以下的男性，现有公司人员信息，需判断哪些符合，哪些不符合？  
> 公式=IF(OR(AND(B3=“女”,C3\<=25),AND(B3=“男”,C3\<=40)),“符合”,“不符合”)

![](https://discourse.shiqidu.com/uploads_nj/article/2017-03-05/58bb088e32827.png)

**（11）ISNUMBER/ISERROR/IFERROR函数**

`ISNUMBER（value）`，如果VALUE为数字，将返回TRUE，否则将返回FALSE。

`ISERROR（expression）`，其中expression可以是任何有效表达式，用来判断公式计算是否出错，如果出错，则返回TURE，如果正确，则返回FALSE。通常与IF配套使用。

`IFERROR(value, value_if_error)`，检查参数是否为错误值，如果是错误值，则返回第二参数，不过不是错误值，则保留第一个参数结果。

（注意：07版及以上，出现了IFERROR，可以替代ISERROR的使用）

![](https://discourse.shiqidu.com/uploads_nj/article/2017-03-05/58bb08d7bd637.png)

**案例：**

> 现有一公司入职年限及岗位工资表，需要根据入职年限匹配岗位工资。  
> 公式=IFERROR(VLOOKUP(D12,$A$10:$B$22,2,0),“查询错误”)

![](https://discourse.shiqidu.com/uploads_nj/article/2017-03-05/58bb08fd41bd2.png)

### 5、文本处理函数

**（12）FIND/SEARCH函数**

FIND和SEARCH都是查找函数，两者的相同点是查找特定字符在字符串中起始位置编号的函数，不同点是对于英文字母大小写的区分以及是否支持使用通配符查找。

![](https://discourse.shiqidu.com/uploads_nj/article/2017-03-05/58bb0930b5217.png)

**案例：**

> 现有一组公司名称数据，现在需要判断公司名称中是否包含“科技”两个字。  
> 公式=IF(ISNUMBER(FIND($B$12,A13)),“包含”,“不包含”)

![](https://discourse.shiqidu.com/uploads_nj/article/2017-03-05/58bb098351854.png)

**（13）MID/LEFT/RIGHT函数**

MID/LEFT/RIGHT三个字符串截取函数通常与其他函数组合使用，用处非常广泛。

![](https://discourse.shiqidu.com/uploads_nj/article/2017-03-05/58bb09af9d787.png)

**案例：**

> 现有一组身份证号码，需根据身份证号码提取“出生日期”属性。  
> 公式=–TEXT((LEN(A2)=15)\*19&MID(A2,7,6+(LEN(A2)=18)\*2),“0-00-00”)

![](https://discourse.shiqidu.com/uploads_nj/article/2017-03-05/58bb09e617cb4.png)

**（14）SUBSTITUTE函数**

这是一个强大但是被低估了甚至很少有人使用的函数。

![](https://discourse.shiqidu.com/uploads_nj/article/2017-03-05/58bb0a0b8fdf1.png)

**案例：**

> 现有一组数据，需提取每个单元格最后一个字符。  
> 公式=MID(A2,FIND(“Z”,SUBSTITUTE(A2,“→”,“Z”,LEN(A2)-LEN(SUBSTITUTE(A2,“→”,“”))))+1,1)

![](https://discourse.shiqidu.com/uploads_nj/article/2017-03-05/58bb0a3a1bd76.png)

### 6、数学计算函数

**（15）INT和MOD函数**

MOD函数用于计算两数相除的余数，INT函数用于舍去数值的小数部分保留整数部分。是非常简单的两个函数，但是在与其他函数嵌套的过程中发挥着非常重要的作用。

**案例1：制作黑白相间的棋盘**

在“条件格式”-“新建规则”-“使用公式确定要设置格式的单元格”-“为符合此公式的值设置格式”对话框中输入公式：  
`=or(and(mod(row(),2)=1,mod(column(),2)=1),and(mod(row(),2)=0,mod(column(),2)=0))`

![](https://discourse.shiqidu.com/uploads_nj/article/2017-03-05/58bb0a909dcc2.png)

**案例2：**

**现有一组身份证号码，需根据身份证号码提取“性别”属性。**

公式=`IF(MOD(MID(A2,15,3),2)=1,"男","女")`

![](https://discourse.shiqidu.com/uploads_nj/article/2017-03-05/58bb0ac5a23ea.png)

> 出自简书 [http://www.jianshu.com/p/c38eea94cc2b，Excel真是太强大了！](http://www.jianshu.com/p/c38eea94cc2b%EF%BC%8CExcel%E7%9C%9F%E6%98%AF%E5%A4%AA%E5%BC%BA%E5%A4%A7%E4%BA%86%EF%BC%81)