第七篇. Excel函数

一、IF函数

（一）IF函数的基本用法

函数语法：IF(logical_test,[value_if_true],[value_if_false])

IF(条件,返回符合条件的值,返回不符合条件误的值)

例：IF(E4="男","先生","女士")如果E4为男，那么输入先生，否则输入女士。

IF(ISERROR(D35/C35),0,D35/C35)

（二）IF函数的嵌套

作用：在IF函数里嵌套另一个IF函数，使结果可为两个以上。

例：IF(B2="理工","LG",IF(B2="文科","WK","CJ"))

（三）公式嵌套

本地+30分，本省+20分，外省+10分

=IF(G2="本地",H2+30,IF(G2="本省",H2+20,H2+10))

（四）AND函数与OR函数

AND函数表示且，

=IF(AND(A3="男",B3>=60),1000,0)。对于60岁以上（含）的男性员工给予1000元奖金

OR函数表示或，

=IF(OR(A12="男",B12>=60),1000,0)。对于60岁以上或40岁以下的员工给予1000元奖金

=IF(OR(AND(A20="男",B20>60),AND(A20="女",B20<40)),1000,0)对于60岁以上的男员工或40岁以下的女员工给予1000元奖金

二、Countif函数

（一）使用Countif函数

1、Count函数：计算单元格个数，函数计数，且只能数数字，不能数文本

2、Counta函数：返回参数列表中非空的单元格个数

3、countblank函数：计算指定单元格区域中空白单元格的个数

2、Countif函数语法=Countif(range,criteria)

3、Countif函数计算数值区间

4、Countif函数超过15位字符时，函数需要这样写：

=COUNTIF(A2:A3,A2&"*")（因为countif函数默认只数15位字符）

（二）常见应用示例

1、在数据区域中寻找重复数据

If（countif（，）=0，无，有）

2、在数据有效性中使用Countif函数

将D2:I20设置为禁止输入重复数据

3、在条件格式中使用Countif函数

借助条件格式，在条件格式中运用countif函数找重复值或不重复值。

同列不同列都可以查找并借助条格式进行标记

=COUNTIF($E$2:$E$14,E2&"*")>=2

（三）2007及以上版本中的Countifs函数

Countifs(ceiteria_range1,criterial1,[ceiteria_range21,criterial3]…)

=COUNTIFS(条件区域，条件，条件区域，条件，条件区域，条件···)

=COUNTIFS(D:D,I6,E:E,J6)

三、Sumif函数

1、Sumif函数语法

=sumif(range,criteria,[sum_range])

=SUMIF(E:E,H8,F:F)

第三参数的省略：当条件区域与加和区域相同时=SUMIF(F:F,">=500")

2、Sumif函数计算数值区间

3、Sumif函数超过15位字符时的错误

=SUMIF(A:A,F3&"*",B:B)

4、关于第三参数简写时的注意事项

小心错位

5、在多列中使用Sumif函数

6、使用辅助列处理多条件的Sumif

7、Sumifs函数

四、Vlookup函数（查找引用的函数）

1、Vlookup函数语法

VLOOKUP(lookup_value,table_array,col_index_num,[range_lookup])

只能查找255位以内的

2、vlookup中使用通配符

3、vlookup模糊查找

只会找小于等于自己的最大值

一般在数值找区间的时候，数值档次中应用

4、使用isna函数处理数字格式引起的错误

=IF(ISNA(VLOOKUP(--F20,$A$18:$C$22,3,0)),VLOOKUP(F20&"",$A$18:$C$22,3,0),VLOOKUP(--F20,$A$18:$C$22,3,0))

数字变文本：F20&""

文本变数字：--F20

5、Hlookup函数

五、Match（查找）与Index（引用）函数

1、函数语法

查找：MATCH(lookup_value,lookup_array,[match_type])返回查找值在查找区域为第几个

引用：INDEX(array,row_num,[column_num])

2、Match+Index与Vlookup函数比较

3、使用Match与Vlookup函数嵌套返回多列结果

4、认识column函数

Column（D1）返回该值的列数

5、使用函数引用照片

定义名称-照相机-引用照片

第一步：定义名称=INDEX($D$2:$D$5,MATCH($H$2,$A$2:$A$5,0))

第二步：点击照相机，插入空白图片

第三步：为空白照片写公式：=照片

六、日期函数

1.Year、Month、Day函数

返回年月日

2.Date函数

返回具体日期

3.Datedif函数

计算日期间隔

Datedif(开始日期，结束日期，”Y”)从结束日期到开始日期间隔的年份

Datedif(开始日期，结束日期，”m”)从结束日期到开始日期间隔的月份

Datedif(开始日期，结束日期，”d”)从结束日期到开始日期间隔的天数

Datedif(开始日期，结束日期，”ym”)除开年份从结束日期到开始日期间隔的月份

Datedif(开始日期，结束日期，”md”)除开月份从结束日期到开始日期间隔的天数

Datedif(开始日期，结束日期，”yd”)除开年份从结束日期到开始日期间隔的天数

=DATEDIF(B13,C13,"y")&"年"&DATEDIF(B13,C13,"ym")&"月"&DATEDIF(B13,C13,"md")&"天"

4、Weeknum

Weeknum（日期，以星期几为一周的第一天）返回一年中的周数。

Weekday

Weeknum（日期，以星期几为一周的第一天）返回星期几。

5.Text函数

=TEXT(B3,"aaaa")将指定数值转化为星期

=TEXT(B10,"0000-00-00")*1将指定数值转化为日期格式

假日期：20130616   转化为日期2013/6/16

七、简单文本函数

一、使用文本截取字符串

1、Left函数

2、Right函数

3、Mid函数

4、示例使用MOD函数与文本函数，提取身份证号性别信息

二、获取文本中的信息

1、Len与Lenb函数

2、Find函数

一个中文字代表两个字节，一个字符。一个英文字代表一个字节，一个字符。

三、关于身份证

1、通过身份证前六位判断地区

2、通过身份证计算出生年月日

3、通过身份证判断性别

4、身份证验证

八、数学函数与引用

一、认识函数

1、Round函数Roundup函数Rounddown函数Int函数

Round（数值，小数位）

2、Mod函数

3、Row函数与Column函数

二、函数应用实例

1、通过身份证号码判断性别

2、特殊的舍入方式--员工假期计算

3、基于位置规律的引用

九、其它函数

一、cell函数

CELL（info-type，reference）：返回所引用单元格的格式、位置或内容等信息

info-type：一个文本值，指定所需的单元格信息的类型；

reference若要其有关信息的单元格，如果省略 reference，则在 info_type 中指定的信息将返回给最后更改的单元格。

LARGE：函数用于返回数据集中的第K个最大值。

SMALL：函数用于返回数据集中的第K个最小值。

Len()：得到字符串的长度

max：返回一组数值中的最大值，忽略逻辑值及文本

maxa：返回一组数值中的最大值，不忽略逻辑值及文本

min：返回一组数值中的最小值，忽略逻辑值及文本

mina：返回一组数值中的最小值，不忽略逻辑值及文本

MODE和MODE.SNGL：功能一样，返回众数的第一个值

MODE.MULT以数组形式返回众数的所有值。比如{1，1，2，2，3，3，4}一组数