EXCEL 函数

1.求平均

AVERAGE(参数1，参数2，、、、)，参数可以是数字，可以引用单元格，可以应用单元格区域，可以是公式函数计算的值。最多255个参数。

AVERAGEA的参数除了数字，单元格区域，还可以是文本的逻辑值。

逻辑值true代表1，false代表0；文本代表0

AVERAGEAIF满足一定条件求平均值。AVERAGEAIF(单元格区域，条件（可以是数字，文本，公式，单元格），实际计算平均的单元格区域)

AVERAGEAIFS满足多条件求平均值。AVERAGEAIFS(实际计算平均的单元格区域，条件判断区域，条件（可以是数字，文本，公式，单元格），条件判断区域，条件、、、)

注意：条件为文本时要加双引号

2.逻辑函数

1、AND，判断多个条件成立

例：AND(E2>=60,F2>=60)

2、OR，判断多个条件至少有一个成立

例：OR(E2>=60,F2>=60)

3、NOT，对逻辑值求反

例：NOT(E2>=60)

注：逻辑返回true与false。

IF，根据条件判断，然后返回指定内容

IF（条件，如果为真时返回的值，如果为假时返回的值）

例：

IF（E2>=60,“及格”，“不及格”）

注意：返回值为文本时加双引号，双引号必须为半角输入。

IF（AND(E2>=60,F2>=60),“及格”，“不及格”）

TRUE与FALSE返回参数的逻辑值，可以直接使用，一般配合其他函数使用。也可以直接用于运算。

用于运算。TRUE代表的数值是1，FALSE代表的数值是0，可以数值运算的，如：

TRUE*1=1,FALSE*1=0

3.求和

1、求和SUM

2、根据条件求和,SUMIF(条件判断区域，条件，实际求和区域)

例：

SUMIF(B2:B4,">=60",C2:C4)

3、根据多条件求和,SUMIFS(实际求和区域，条件判断区域1，条件1，条件判断区域2，条件2，、、、)

例：

SUMIFS(D2:D4,B2:B4,">=60",C2:C4,">=60")

先元素相乘，后对积求和。

SUMPRODUCT(数组参数1，数组参数2，、、、)

例：

SUMPRODUCT(B2:B9，C2:C9)

TRUE和FALSE返回参数的逻辑值，TRUE代表的数值是1，FALSE代表的数值是0，可以数值运算的

例：条件求和

SUMPRODUCT((B2:B9=2)*C2:C9)

累计求和(对角线)

函数N，将不是数值形式的值转换为数值，文本会被转换成0，这样就免去了表头不能计算的烦恼了。

带单位的数字求和

a=1+12+3+8 ctrl+e自动填充 cttl+h替换a为空

4.舍入

2.4.1四舍五入ROUND

对数值进行四舍五入，ROUND(需要四舍五入的数值，保留几位小数)

例：

ROUND(B2,2) ROUND(B2,0) ROUND(B2,-2)

大于0，在小数点右边四舍五入，等于0，四舍五入到整数，小于0，小数点左边四舍五入。

2.4.2向下舍入到整数INT与数字截断TRUNC

1.INT:对数值进行向下舍入，保留整数

例：

INT(B2)

2.TRUNC:数字截取，TRUNC(数据，保留的小数位)

例：

TRUNC(B2) TRUNC(B2,1) TRUNC(B2,2)

2.4.3向上舍入ROUNDUP和向下舍入ROUNDDOWN

1、向上舍入，向前一位进1 ，ROUNDUP(数据，保留小数位）

例：

ROUNDUP(B2,2) ROUNDUP(B2,0) ROUNDUP(B2,-1)

大于0，在小数点右边向上舍入，等于0，向上舍入到整数，小于0，小数点左边向上舍入。

2、向下舍入，舍去不进1 ，ROUNDUP(数据，保留小数位）

例：

ROUNDDOWN(B2,2) ROUNDDOWN(B2,0) ROUNDDOWN(B2,-1)

大于0，在小数点右边向上舍入，等于0，向上舍入到整数，小于0，小数点左边向上舍入。

2.4.4按照参数的倍数向下、向上舍入floor、ceiling

5.统计

2.5.1 统计含有数字的单元格个数COUNT

统计含有数字的单元格个数，COUNT（数据参数1，数据参数2，、、、）

例：

COUNT(B2:B4,C2:C4)

COUNT(C2:C4)

2.5.2 统计非空白单元格个数COUNTA

统计非空单元格个数，COUNTA（数据参数1，数据参数2，、、、）最多30个数据参数。

例：

COUNTA(B2:B4,C2:C4)

COUNTA(C2:C4)

2.5.3 统计空白单元格个数COUNTBLANK

统计空单元格个数，COUNTBLANK（数据参数1，数据参数2，、、、）最多30个数据参数。

例：

COUNTBLANK(B2:B4,C2:C4)

COUNTBLANK(C2:C4)

2.5.4 根据条件统计单元格个数COUNTIF

根据条件统计单元格个数，COUNTIF（统计区域，条件）

例：

COUNTIF(B2:C4,"871")

2.5.5 统计满足多个条件的单元格个数COUNTIFS

统计满足多个条件的单元格个数COUNTIFS，COUNTIFS（条件区域1,条件1,条件区域1,条件1,、、、）

例：

COUNTIFS(B2:C14,">871",B2:C14,"<900")

6.最值

2.6.1求最大值MAX

MAX求最大值

MAX(参数1，参数2，、、、)

例：

MAX(D1:D21)

MAX(IF(C2:C21=K1,E2:E21))

MAX(IF(@C2:C21=K1,E2:E21))

Ctrl+Shift+Enter组合键，根据条件求最大值 (数组)

2.6.2求最小值MIN

MIN求最小值

MIN(参数1，参数2，、、、)

例：

MIN(D1:D21)

MIN(IF(C2:C21=K1,E2:E21))

Ctrl+Shift+Enter组合键，根据条件求最大值

2.6.3求第k个最大值LARGE()

返回一组数据中的第K个最大值。

LARGE（单元格区域或数组，k）

注意：两个参数都不能为空，0<k<=数据个数。当k不是整数时，函数在计算时将会掐尾取整。

2.6.4求第k个最大值LARGE()

返回一组数据中的第K个最大值。

LARGE（单元格区域或数组，k）

注意：两个参数都不能为空，0<k<=数据个数。当k不是整数时，函数在计算时将会掐尾取整。

7.行列号

2.7.1返回行号ROW

1、ROW:返回行号

ROW(单元格或区域)

例：

ROW() ROW(B2)

ROW(B2:B4) Ctrl+Shift+Enter组合键，使用数组公式。

生成序号：

“P-”&ROW()-2

生成重复序列号：

"P-"&INT((ROW(D1)-1)/2)+1

注意：分母是几重复几次

对合并单元格编号

=MAX(F$1:F1)+1

2.7.2返回行数ROWS

1、ROWS:返回行数

ROWS(单元格区域)

例：

ROWS(B2:B9)

2.7.3返回列号COLUMN

1、COLUMN:返回列号

COLUMN(单元格或区域)

例：

COLUMN() COLUMN(B2)

COLUMN(B:D) Ctrl+Shift+Enter组合键，使用数组公式。

2.7.4返回列数COLUMNS

1、COLUMNS:返回列数

COLUMNS(单元格区域)

例：

COLUMNS(B2:G9)

2.7.5 SUBTOTAL

返回一个数据列表或数据库的分类汇总。

=Subtotal(功能代码，数值区域)。

8.商余

2.8.1计算除法的余数MOD

MOD(被除数，除数)

例:

MOD(3/2)

注：如果除法为0，则MOD返回#DIV/0!

2.8.1计算除法商的整数部分QUOTIENT

QUOTIENT被除数，除数)

例:

QUOTIENT(3/2)

注：如果除数为0，则QUOTIENT返回#DIV/0!

9.VLOOKUP函数

2.9.1垂直查找VLOOKUP，精准与模糊查找

1、VLOOKUP中V表示方向垂直查找。用途：在表格数组的首列查找值，然后返回当前所在行中其他值。

VLOOKUP(1查找的值，2查找区域，3返回值所在的列，4查找精度)

第四个参数决定是精准查找还是模糊查找。0或FALSE表示精准查找，1或TRUE表示模糊查找。第四部分省略时进行模糊查找。

VLOOKUP("P-1",B2:C7,2,FALSE)

2.9.2VLOOKUP精确查找学生信息

1、VLOOKUP与COLUMN

VLOOKUP(1查找的值，2查找区域，3返回值所在的列，4查找精度)

例：

=VLOOKUP($L4,$C:$K,COLUMN(I2),FALSE)

第四个参数决定是精准查找还是模糊查找。0或FALSE表示精准查找，1或TRUE表示模糊查找。第四部分省略时进行模糊查找。

查找值可以是值也可以是单元格引用。

表格内直接查找快捷键：Ctrl+F

2.9.3VLOOKUP跨表查询

1、VLOOKUP与COLUMN

VLOOKUP(1查找的值，2查找区域，3返回值所在的列，4查找精度)

例：

=VLOOKUP(B3,员工信息表！B:R,COLUMN(R1-1),FALSE)

第四个参数决定是精准查找还是模糊查找。0或FALSE表示精准查找，1或TRUE表示模糊查找。第四部分省略时进行模糊查找。

查找值可以是值也可以是单元格引用。

表格内直接查找快捷键：Ctrl+F

2.9.4VLOOKUP模糊查找代替IF函数嵌套成绩分级

1、VLOOKUP(1查找的值，2查找区域，3返回值所在的列，4查找精度)

IF(J3<60,"不及格",IF(J3<80,"及格",IF(J3<90,"良好",IF((J3<90,"良好"))))

=VLOOKUP(J3,$B$3:$C$6,2,TURE)

第四个参数决定是精准查找还是模糊查找。0或FALSE表示精准查找，1或TRUE表示模糊查找。第四部分省略时进行模糊查找。

查找值可以是值也可以是单元格引用。

查找函数家族还有LOOKUP，HLOOKUP函数。

多表协同工作-链接公式

1、链接公式结构

同一工作簿：

=工作名称！单元格地址

不同工作簿

=‘工作簿存储地址[工作簿名称]工作名称！’单元格地址

2、引用其他工作簿的内容默认的是绝对引用。

10.随机数

2.10.1生成随机数RAND

返回[0,1]之间的平均分别随机数RAND()

2.10.1生成两个数之间的随机数RANDBETWEEN

返回两个数之间的随机数.

RANDBETWEEN(最小值，最大值)

11.文本

2.11.1字符串替换SUBSTITUTE

将字符串中的部分字符串以新字符串替换SUBSTITUTE。

SUBSTITUTE(需要替换字符的字符串或单元格, 要被替换的字符串, 用于替换旧字符串的新字符串,指定要将第几个旧字符串替换为新字符串)

注意：大小写不匹配，不替换。

2.11.2字符串替换REPLACE

将字符串中的部分字符串以新字符串替换REPLACE。

REPLACE（要替换的字符串，开始位置，替换个数，新的文本）

注意：第四个参数是文本，要加上引号。

2.11.3返回指定个数的字符LEFT()

中文本字符串中的第一个字符开始返回指定个数的字符： LEFT （）

例：

LEFT （截取的单元格内容，从左截取位数）

2.11.4返回指定个数的字符RIGHT()

中文本字符串中的最后一个字符开始返回指定个数的字符：RTGHT（）

例：

RIGHT（截取的单元格内容，从右截取位数）

2.11.5返回指定个数的字符MID()

中文本字符串中的指定字符开始返回指定个数的字符MID（）

例：

MID（截取的单元格内容，从第几位截取，截取几位数）

12.时间

2.12.1常见的时间日期函数

1、=TODAY()：显示当前日期：2017/6/28

2、=NOW()：显示当前日期和时间：2017/6/28 17:25

3、=YEAR("2017/6/28")：显示日期对应的年份：2017

4、=MONTH("2017/6/28")：显示日期对应的月份：6

5、=DAY("2017/6/28")：显示日期对应的天：28

6、=WEEKDAY(2017/6/28,2)：显示日期对应的星期：3，表示星期三；第二个参数为2表示数字1-7对应的是星期一到星期日

7、=EDATE("2017/6/28",1)：显示日期后一个月的日期：2017/7/28；如果第二个参数为-1，表示日期前一个月的日期

8、=EOMONTH("2017/6/28",0)：显示日期该月最后一天的日期：2017/6/30

9、=WORKDAY("2017/3/1",30）：显示日期经过30个 工作日后对应的日期：2017/4/12

10、=NETWORKDAYS("2017/1/1","2017/4/1")：显示两个日期之间的工作日：65

2.12.2 被隐藏的DATEDIF函数

1、=DATEDIF(A1,B1,"y")

显示两个日期之间相隔的年数：1

2、=DATEDIF(A1,B1,"m")

显示两个日期之间相隔的月数：13

3、=DATEDIF(A1,B1,"d")

显示两个日期之间相隔的天数：423

4、=DATEDIF(A1,B1,"ym")

忽略年，两个日期相隔的月数：1

5、=DATEDIF(A1,B1,"yd")

忽略年，两个日期相隔的天数：58

6、=DATEDIF(A1,B1,"md")

忽略年和月，两个日期相隔的天数：27