EXCEL常用函数

（演示的数据来源是我们专业某年级某次的成绩,名字已经高度马赛克。）

 

if函数（=IF(判断条件,条件成立返回的值,条件不成立返回的值））

1.对高代成绩做评价，60以上为及格，60分一下为不及格。

=IF(D3>=60,"及格","不及格")

2.对高代成绩做评价，80以上为优秀，60以上为合格，60分一下为不及格。（嵌套）

=IF(D2>=80,"优秀",IF(D2>=60,"合格","不及格"))

3.如果数分高代有一门成绩大于90，即数学学得很好。（or）

=IF(OR(D2>=90,E2>=90),"数学很好","数学一般")

4.如果数分高代成绩都大于90，即数学学得很好。（and）

=IF(AND(D2>=90,E2>=90),"数学很好","数学一般")

Round函数/INT函数（数值四舍五入和取整函数）

四舍五入为几位小数：=round(number,digit)

vlookup函数（数据查找、表格核对、表格合并）不能反向查找

[=vlookup（查找的值，查找区域，找到后返回其右侧对应的第几列数据，精确还是模糊查找）]

查询JS的数分成绩？

=VLOOKUP("JS",C1:J24,3,FALSE)

多次查找，不希望查找范围改变：

=VLOOKUP("JS",$C$1:$J$24,3,FALSE)

注意：你查找的东西必须在查找区域的第一列！

---

同样的，HLOOKUP函数是按照行查找的。

例如：

=HLOOKUP("数学分析",A1:J24,5,FALSE)     ->72

Sumif和Countif函数（按条件求和，按条件计数）

Sumifs和Countifs函数（多条件求和，多条件计数）

=SUMIF(D43:D47,"男",E43:E47)

=SUMIFS(E43:E47,C43:C47,1,D43:D47,"男")

1.C++实验良好的人有多少？

=COUNTIF(G:G,"良好")

2.C++实验良好并且MATLAB大于80分的人有多少？

=COUNTIFS(G:G,"良好",H:H,">80")

Left、Right和Mid函数（字符串的截取）

[=Left(字符串,从左边截取的位数)]

 

Datedif函数（日期的间隔计算）

最后一个参数是年、月、日

最值计算函数（max\min\large\small）

large第几大值

查找出数分成绩的第三名？

=LARGE(E:E,3)

IFERROR函数（把公式返回的错误值转换为特定的值。如果没有返回错误值则正常返回结果）

比如说除数为0的时候，该单元格填写空字符。=IFERROR(E11/F11," ")

INDEX+MATCH函数（可以反向查找！）

（=INDEX(区域,match(查找的值，一行或一列,0) )）

（返回指定内容所在的位置）match的用法：（最后一个参数，1小于，-1大于，0精确匹配）返回的是第几行/列

=MATCH(要找的值,区域,0)

（根据指定位置查询到位置所对应的数据）index的用法：

=INDEX(区域,第几行,第几列)

两个一起用就可以进行反向查找啦！

例：找出高代考了零分的人？

=INDEX(C:C,MATCH(0,D:D,0))

解释：用match先找到这个考了零分的人的位置，然后用index找到这个人的名字

第一个参数是要找的人所在的列（即姓名列），第二个参数是match，match的第一个参数是0,表示考了零分，第二个参数是高代成绩列，第三个参数是精确查找

 

FREQUENCY函数（频率统计）

FREQUENCY(data_array, bins_array)

Data_array  需要判断的数组或者数据区域

bins_array   输出结果数据的分割点

=FREQUENCY(H2:H24,L6:L9)

选中要填充的地方输入公式后按Ctrl+shift+enter键！

=FREQUENCY(H2:H24,{80,100})

会输出三个数组，一个是小于等于80的，一个是大于80小于等于100的，一个是大于100的。

AVERAGEIFS函数（按多条件统计平均值。）

[=AVERAGEIFS(D:D,A:A,"数学1901",G:G,"优秀")]

求解数学1901班并且C++实验课优秀的人的高代平均分

=AVERAGEIFS(D:D,A:A,"数学1901",G:G,"优秀")

第一个参数是要求解平均值的范围，后面的都是条件内容。

SUMPRODUCT函数（给定的几组数组中，将数组间对应的元素相乘，并返回乘积之和）

比如说把两列对应相乘求和。

=SUMPRODUCT(H39:H40,I39:I40)

SUBSTITUTE函数

[Substitute(要替换部分字符的文本, 被替换文本，替换文本，[被替换文本序号])]

=SUBSTITUTE(E42,RIGHT(E42,3),"******")

解释：E42（174627387）是需要替换的文本，RIGHT(E42,3)是被替换的部分，即右边数三个，第三个参数是要替换成什么。

最后的结果：174627******

 

----END----

（没想到我也有学习excel的一天……之前看着统计专业的舍友学习高级EXCEL课程还觉得表格这玩意儿有啥好学的，后来我发现，这玩意好像还真挺麻烦orz