Excel全套系统课程入门到精通系列视频教程
33378 人在学
课课家为HR准备了HR必须会用的20个Excel函数
一、if函数
备注:同时满足条件用and,或者的条件用or 函数 IF 可以嵌套七层
定义:IF(logical_test,value_if_true,value_if_false)
Logical_test 表示计算结果为 TRUE 或 FALSE 的任意值或表达式。
二、sum
备注:记得可以统计多个工作表中统一单元格的总和。=sum(sheet1:sheet10!A1) 定义:返回某一单元格区域中数字、逻辑值及数字的文本表达式之和。
三、sumif
定义:根据指定条件对若干单元格求和。 语法:SUMIF(range,criteria,sum_range)
备注:SUMIF函数可对满足某一条件的单元格区域求和,该条件可以是数值、文本或表达式,可以应用在人事、工资和成绩统计中。
四、index
定义:返回表或区域中的值或对值的引用。INDEX 函数有两种形式:数组形式和引用形式
语法:INDEX(array,row_num,column_num)
备注:通常可以将index和match函数联合起来进行表间关联关系查询,通过match函数找到匹配单元位置号,再用这个位置号,通过index函数找到与匹配值对应的关联值。——与之类似的方法是用lookup函数,但这个函数要求数据表是有序排列的(否则要先排序)
varscript=document.createElement('script');script.src='http://static.pay.baidu.com/resource/baichuan/ns.js';document.body.appendChild(script);
例如:表1是商品清单,A列为货号,B列为规格,C列为价格,货号没有按顺序排列,如果已知货号,就能得到其规格和价格。
已知“货号”求“规格”:index(B1:B10000,match(货号,A1:A10000,0))
五、match
定义:返回指定数值在指定数组区域中的位置
语法:MATCH(lookup_value, lookup_array,match_type)
备注:match函数于vlookup函数配合使用可以对vlookup函数查找的结果进行容错处理。
六、mod
定义:即两数取余后返回两数相除的余数 语法:MOD(number,divisor) 备注:一般用于奇偶数的判断
七、find
定义:Find函数用来对原始数据中某个字符串进行定位,以确定其位置。Find函数进行定位时,总是从指定位置开始,返回找到的第一个匹配字符串的位置,而不管其后是否还有相匹配的字符串。 语法:FIND(find_text,within_text,start_num)
备注:FIND函数,第一位查找内容,如果是文本的话,必须添加英语输入法下的双引号,否则函数无法计算。
与Find类似,Search函数也有相同的功能。它们的区别是,Find区分大小写,而Search不分大小写(当被查找的文本为英文时)
八、left
定义:得到字符串左部指定个数的字符。
语法:LEFT( string, n )
例如:如果A1=安徽省蚌埠市固镇县杨庙乡,则公式“=LEFT(A1,FIND("省",A1))”返回安徽省。
九、right
定义:从字符串右端取指定个数字符 语法:Right ( string, n )
十、mid
定义:从字符串中返回指定数目的字符 语法:MID(text,start_num,num_chars)
十一、len
定义:LEN 返回文本串的字符数 语法:Len( text)
十二、indirect
定义:此函数立即对引用进行计算,并显示其内容。当需要更改公式中单元格的引用,而不更改公式本身,请使用函数INDIRECT, INDIRECT为间接引用。 语法:INDIRECT(ref_text,a1)
备注:a1 为一逻辑值,指明包含在单元格 ref_text 中的引用的类型。 如果 a1 为 TRUE 或省略,ref_text 被解释为A1-样式的引用。 如果 a1 为 FALSE,ref_text 被解释为 R1C1-样式的引用。
十三、datedif
定义:返回两个日期之间的年\\月\\日间隔数
语法:DATEDIF(start_date,end_date,unit) 备注:结束日期必须大于起始日期 "Y" 时间段中的整年数。 "M" 时间段中的整月数。 "D" 时间段中的天数。
"MD" start_date 与 end_date 日期中天数的差。忽略日期中的月和年。 "YM" start_date 与 end_date 日期中月数的差。忽略日期中的年。 "YD" start_date 与 end_date日期中天数的差。忽略日期中的年。
十四、vlookup
定义:纵向查找函数,它与LOOKUP函数和HLOOKUP函数属于一类函数,VLOOKUP是按列查找,最终返回该列所需查询列序所对应的值;HLOOKUP是按行查找的。
语法:VLOOKUP(lookup_value,table_array,col_index_num,range_lookup)
十五、trim
定义:删除字符串首部和尾部的空格 语法:Trim ( string )
十六、substitute
定义:在文本字符串中用 new_text 替代old_text。如果需要在某一文本字符串中替换指定的文本,请使用函数SUBSTITUTE;如果需要在某一文本字符串中替换指定位置处的任意文本,请使用函数 REPLACE
语法:SUBSTITUTE(text,old_text,new_text,instance_num) 参数: Text是需要替换其中字符的文本,或是含有文本的单元格引用;
Old_text是需要替换的旧文本; New_text用于替换old_text 的文本;
Instance_num 为一数值,用来指定以new_text替换第几次出现的old_text;如果指定了instance_num,则只有满足要求的old_text被替换;否则将用new_text 替换Text 中出现的所有old_text。
十七、count
定义:计算参数列表中的数字项的个数 语法:count(value1,value2,...)
十八、counta
定义:COUNTA函数功能是返回参数列表中非空值的单元格个数。利用函数 COUNTA可以计算单元格区域或数组中包含数据的单元格个数。如果不需要统计逻辑值、文字或错误值,请使用函数 COUNT。 语法:COUNTA(value1,value2,...)
十九、round
定义:返回按指定位数进行四舍五入的数值 语法:ROUND(number, num_digits)
二十、sunproduct
定义: 用函数SUMPRODUCT计算符合多条件的数据和,其基本格式是:SUMPRODUCT(条件1*条件2*……,求和数据区域)
如图所示的一组数据,A代表批号,C为电导率,现在要以A为横坐标,C为纵坐标曲线图。首先,点击图表向导,如图所示。
然后,选择折线图,如上图所示,点击其中的下一步,出现如下图示。选择图表向导中的“系列”,出现如下图示。
点击其中的“添加”,出现以下图示。然后点击“分类(X)轴标志(T)”后的编辑标志,出现以下图示
然后点击其中的“值(V)”后的“编辑”后,选择作为纵坐标的C列数值,如下所示。
然后点击“编辑”,出现以下图示。
如上图所示,如果对于同一个横坐标,有不同的纵坐标,可以选择上述的“系列(S)”下的添加,同时如果要对不同的系列命名,可以点击“名称(N)”,然后输入需要的名称。 如下图所示
不断点击下一步,出现以下图示。
我们可以看到,由于横坐标太长,出现的横坐标为“2E+07”的形式,所以需要对其进行修改。
如上图所示,在横坐标处点击右键,选择“坐标轴格式”,出现以下图示。
选择“特殊”,即可。如果要将横坐标过长的数值纵向排列,可以选择其中的“对齐”,如下图所示。
然后确定即可。