Excel日常应用:函数和快捷键

在科研工作中，和数据打交道是难免的！临床数据，基因组学数据，调查问卷数据……面对大量的实验数据，如何快速的将自己想要的结果筛选出来呢？

现在的科研处处需要数据来证明，与表格打交道已经成为科研工作者的日常，熟练的运用Excel成了科研工作者的必备能力，了解并掌握 Excel 的技巧，达成[一目了然、零失误、高效率]的目标，是建立以数字思考与效率沟通的必备模式。

Excel函数共包含13类，分别是兼容函数、多维数据集函数、数据库函数、日期与时间函数、工程函数、财务函数、信息函数、逻辑函数、查询和引用函数、数学和三角函数、统计函数、文本函数以及用户自定义函数，总数超过400种，那么如何在快速找到实用合适的函数呢？

我机构整理了一些常用函数并进行应用举例，供大家参考。

常用函数

•按条件查找：VLOOKUP(vlookup_value,table_array,col_index_num,range_lookup)

•删除字符串中多余空格：TRIM(text)

•统计重复内容的数量：COUNTIF(range，criteria)

•对满足条件的单元格求和：SUMIF(range，criteria，sum_range)

•多条件统计重复内容的数量:

COUNTIFS(criteria_range1,criteria1,criteria_range2,criteria2,…)

•对满足多条件的单元格求和：SUMIFS(sum_range,criteria_range1,criteria1,[criteria_range2,criteria2],…)

•条件判断：IF(logical_test,value_if_true,value_if_false)

•取绝对值:ABS(number)

•向下取整:INT(number)

•按指定位数进行四舍五入：ROUND(number,num_digits)

•得到字符串左部指定个数：LEFT(text,n)

•得到字符串左部指定个数：RIGHT(text,n)

•得到字符串指定位置与个数：MID(text,start_num,num_chars)

•日期计算DATEDIF:(DATEDIF(start_date,end_date,unit))

•把公式产生的错误值显示为空：IFERROR(value, value_if_error)

不知道使用什么函数怎么办？

公式-插入函数-搜索函数

常见错误形式及说明

常用函数应用举例Vlookup函数

用途：数据查找、表格核对、表格合并

用法：=vlookup（查找的值,查找区域,返回值所在列数,精确OR模糊查找）

【例1】如下图所示，根据姓名查找销售量。

=VLOOKUP(H3,B3:C9,2,0)

TRIM函数

用途：移除字符串两侧的空白字符或其他预定义字符

用法：=TRIM（数据）

Sumif和Countif函数

用途：按条件求和，按条件计数

用法：

=Sumif(判断区域,条件，求和区域)

=Counif(判断区域,条件）

【例2】统计相应地区的销量

公式:=SUMIF(E3:E9,H3,C3:C9)

【例3】统计相应性别的人数

公式:=COUNTIF(D3:D9,H3)

Sumifs和Countifs函数

用途：多条件求和、多条件计数，数据分类汇总利器

用法：

=Sumifs(求和区域,判断区域1，条件1，判断区域2，条件2…..）

=Countifs(判断区域1，条件1，判断区域2，条件2…..）

【例4】计算公司销售量大于等于50的销售之和

公式=SUMIFS(D3:D9,C3:C9,H3,D3:D9,”>=50”)

【例5】计算公司指定地区性别销售人员的人数

公式=COUNTIFS(D3:D9,C3:C9,H3,E3:E9,I3)

IF函数

用途：根据条件进行判断

用法：=IF(判断条件,条件成立返回的值,条件不成立返回的值）

【例6】判断销售量是否达标。

公式：=IF(D3>=60,”达标”,”不达标”)

说明：若有多个条件，同时成立用AND,任一个成立用OR函数。

ABS函数、Round函数、INT函数

用途：数值取绝对值四舍五入和取整函数

用法：

取绝对值=ABS（数值）

四舍五入=Round(数值,保留的小数位数）

取整=INT(数值)

【例7】A1的值为-256.235，要求

取绝对值=ABS(A1)

取整=INT(A1)

保留两位小数=Round(A1.2)

Left、Right和Mid函数

用途：字符串的截取

用法：

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

=Right(字符串,从右边截取的位数)

=Mid(字符串,从第几位开始截,截多少个字符）

【例8】根据F3的值分别从左、右和中间截取

公式=LEFT(F3,4)=MID(F3,5,4)=RIGHT(F3,9)

Datedif函数

用途：日期的间隔计算。

用法：

=Datedif(开始日期,结束日期.”y”)间隔的年数

=Datedif(开始日期,结束日期.”M”)间隔的月份

=Datedif(开始日期,结束日期.”D”)间隔的天数

【例9】A1是开始日期（2011-12-1），B1是结束日期(2013-6-10)。计算：

相隔多少天？=datedif(A1,B1,”d”)结果：557

相隔多少月?=datedif(A1,B1,”m”)结果：18

相隔多少年?=datedif(A1,B1,”Y”)结果：1

不考虑年相隔多少月？=datedif(A1,B1,”Ym”)结果：6

不考虑年相隔多少天？=datedif(A1,B1,”YD”)结果：192

不考虑年月相隔多少天？=datedif(A1,B1,”MD”)结果：9

IFERROR函数

用途：把公式返回的错误值转换为指定的值。如果没有返回错误值则正常返回结果

用法：=IFERROR(公式表达式,错误值转换后的值）

【例10】当VLOOKUP公式出错时显示空白

=IFERROR(VLOOKUP(H3,B3:C92,0),””)

常用快捷键

•定位：Ctrl+G

•选择连续单元格：CTRL+SHIFT+CTRL+SHIFT+方向键

•完成单元格输入并在选定区域中下移：TENER

•完成单元格输入并在选定区域中平移：TAB

•用当前输入项填充选定的单元格区域：CTRL+ENTER

•向下填充：CTRL+D

•向右填充：CTRL+R

•重复最后一次操作：F4或CTRL+Y

•将公式作为数组输入：CTRL+SHIFT+ENTER

•撤消最后一次操作：CTRL+Z

•显示公式：CTRL+~