Excel中常用函数简介课件.ppt

Excel中常用函数简介,2022年11月27日星期日,全国计算机等级考试Office常用函数,1.求和函数SUM(),打开表格后，我们将语文成绩一栏的数据求和。在B列的B6单元格输入公式：=SUM(B2:B5)，公式输入完成后按回车键即可。,2022年11月27日星期日,全国计算机等级考试Office常用函数,SUM(B2:B5)这个公式的意思是什么呢？我们要把语文成绩一列全部求和，数值是从B2开始的，那么我们就从B2开始选择，到B5数值就结束了，那么我们就从B2一直选到B5就可以了。简单的说就是数值从哪个单元格开始，就从哪个单元格开始选，从哪个单元格结束，就从开始的单元格一直选到数值结束的单元格就可以了。,2022年11月27日星期日,全国计算机等级考试Office常用函数,其实还有一个更简单的方法：那就是在菜单栏的选项里有一个“公式”按钮。,2022年11月27日星期日,全国计算机等级考试Office常用函数,单击公式按钮，会发现在下方就有一个自动求和的按钮。,2022年11月27日星期日,全国计算机等级考试Office常用函数,这时我们先将鼠标单击B6单元格，然后单击自动求和按钮会出现如图所示，按后按回车键即可。,2022年11月27日星期日,全国计算机等级考试Office常用函数,2.条件求和函数sumif(),sumif函数语法sumif(range,criteria,sum_range)range必须项，表示条件范围criteria必须项，表示条件sum_range可选项，表示求和范围,2022年11月27日星期日,全国计算机等级考试Office常用函数,求数学成绩超过95分的成绩之和,2022年11月27日星期日,全国计算机等级考试Office常用函数,如图所示单元格中输=SUMIF(D2:D8,“=95”)，看到没有第三个参数，表示无可选项，意思是求D2到D8区域内，大于等于95的数值之和,2022年11月27日星期日,全国计算机等级考试Office常用函数,结果是194，与表格中对应的数学成绩之和一致。,2022年11月27日星期日,全国计算机等级考试Office常用函数,再举一例看到，求数学成绩超过95分的同学的总分之和。,2022年11月27日星期日,全国计算机等级考试Office常用函数,在单元格中输入公式=SUMIF(D2:D8,=95,F2:F8)这个公式含可选项，表示求D2到D8单元格中数值超过95分对应的F2到F8的数值之和,2022年11月27日星期日,全国计算机等级考试Office常用函数,看图，结果完全无误,2022年11月27日星期日,全国计算机等级考试Office常用函数,3.多条件求和函数SUMIFS（）,sumifs函数语法sumifs(sum_range,criteria_range1,criteria1,riteria_range2,criteria2.)sum_range是我们要求和的范围criteria_range1是条件的范围criteria1是条件后面的条件范围和条件可以增加。,2022年11月27日星期日,全国计算机等级考试Office常用函数,下面这张成绩单为例，演示sumifs函数用法，先求男生的语文成绩之和,2022年11月27日星期日,全国计算机等级考试Office常用函数,在G2单元格输入公式=SUMIFS(C2:C8,B2:B8,男),2022年11月27日星期日,全国计算机等级考试Office常用函数,得到结果是228，我们看图中男生成绩得分之和与公式得到的结果一致。,2022年11月27日星期日,全国计算机等级考试Office常用函数,再求语文和数学得分都大于等于90分的学生总分之和,2022年11月27日星期日,全国计算机等级考试Office常用函数,在G4单元格输入公式=SUMIFS(F2:F8,C2:C8,=90,D2:D8,=90),2022年11月27日星期日,全国计算机等级考试Office常用函数,看到图中语文和数学都大于等于90分的学生只有一个同学，他的总分就是247分，与公式求得的结果完全一致。,2022年11月27日星期日,全国计算机等级考试Office常用函数,4.垂直查询函数VLOOKUP（）,VLOOKUP(lookup_value, table_array, col_index_num, range_lookup)。VLOOKUP(查找值，查找范围，查找列数，精确匹配或者近似匹配）,2022年11月27日星期日,全国计算机等级考试Office常用函数,现有如下手机的每日销售毛数据（图左），A分销商需要提供四个型号的销售数据（图右）,2022年11月27日星期日,全国计算机等级考试Office常用函数,第一步：选中要输入数据的单元格，=VLOOKUP(H3,$A$3:$F$19,5,FALSE)如图,2022年11月27日星期日,全国计算机等级考试Office常用函数,参数解释：H3为我们想查找的型号，即iphone5。为什么要写H3，而不是直接写iphone5，因为方便公式进行拖拽填充，以及保证准确性。,2022年11月27日星期日,全国计算机等级考试Office常用函数,参数解释：$A$3:$F$19为我们需要在此范围内做查找，为什么要加上绝对引用呢，因为下面的ip4和剩余的查找都引用这个区域，即我们的数据源，加上了绝对引用后，就可以对公式进行直接的拖拽。,2022年11月27日星期日,全国计算机等级考试Office常用函数,参数解释：5 从我们的数据源第一列起，我们要查询的7/31号的销量在我引用的第一列（即型号列）后面的第五列。注意这里的列数是从引用范围的第一列做为1，而不是以A列作为第一列，万万注意此处。,2022年11月27日星期日,全国计算机等级考试Office常用函数,参数解释：false 在开始已经强调，我们再日常生活中，大部分都使用的是精确的匹配到我想要查询的值，千万不要使用true，使用true的结果就是给你带来意想不到的结果，老老实实的使用false作为精确匹配。,2022年11月27日星期日,全国计算机等级考试Office常用函数,然后回车后，我们便可得到iphone5在7/30的销量。其他的只需要拖拽下即可获得完整的数据报告。如图，并且我们简单检验下，查看后各个值均相等。,2022年11月27日星期日,全国计算机等级考试Office常用函数,四舍五入函数ROUND（）,round函数用来对数值进行四舍五入。语法：ROUND(number, num_digits)其中number表示需要进行四舍五入的数值或单元格内容。num_digits表示需要取多少位的参数。num_digits0时，表示取小数点后对应位数的四舍五入数值。num_digits=0时，表示则将数字四舍五入到最接近的整数。num_digits 0时，表示对小数点左侧前几位进行四舍五入。,2022年11月27日星期日,全国计算机等级考试Office常用函数,以15.5627这个数值为例，求它四舍五入得到的值。因为15.5627在A19单元格内，所有后面公式中number都选择A19。,2022年11月27日星期日,全国计算机等级考试Office常用函数,在B20单元格内输入公式=ROUND(A19,2),2022年11月27日星期日,全国计算机等级考试Office常用函数,取小数点后两位得到的结果是15.76。在B22单元格内输入公式=ROUND(A19,0),2022年11月27日星期日,全国计算机等级考试Office常用函数,取最接近的整数得到的结果是18。在B24单元格内输入=ROUND(A19,-1),2022年11月27日星期日,全国计算机等级考试Office常用函数,所有的结果都通过round函数计算得到。大家应该都明白了吧，可以通过公式的第二个参数来求不同应用的四舍五入值。,2022年11月27日星期日,全国计算机等级考试Office常用函数,ROUNDDOWN（）,输入的第二个参数为0，表示将A列的数据四舍五入到最接近的整数。例如11.588向下舍入后为11.58,而11.599向下舍入后为11.59。 不管第三位是多少都舍掉。,2022年11月27日星期日,全国计算机等级考试Office常用函数,2022年11月27日星期日,全国计算机等级考试Office常用函数,在C列添加新的计算列，还是用ROUNDDOWN函数，第二个参数输入为0，表示将A列的数据向下舍入为最接近的整数。,2022年11月27日星期日,全国计算机等级考试Office常用函数,在D列添加新的计算列，还是用ROUNDDOWN函数，第二个参数输入为-1，表示将A列的数据向下舍入为最接近的整数。这表示“向小数点右侧舍入”，这个计算方法在数学中叫做圆整。相当于圆整到“十位”。,2022年11月27日星期日,全国计算机等级考试Office常用函数,条件平均值函数averageif（）,=averageif(range, criteria, average_range)=Averageif（条件区，条件，平均值区域）参数Range表示：条件区第二个参数条件所在的范围。参数Criteria表示：条件是用来定义计算平均值的单元格。 （形式可以是数字、表达式、单元格引用或文本的条件。用来定义将计算平均值的单元格。 例如，条件可以是数字10、表达式12、文本上海发货平台 或 C2。）参数Average_range：平均值区域参与计算平均值的单元格。（这参数可以省略，当条件区和平均值区域一致时）,2022年11月27日星期日,全国计算机等级考试Office常用函数,2022年11月27日星期日,全国计算机等级考试Office常用函数,我们在“中国城市平均参加人数”后面单元格输入公式“=AVERAGEIF(A2:A10,中国,C2:C10)”,2022年11月27日星期日,全国计算机等级考试Office常用函数,AVERAGEIF“(range,criteria,average_range)”中的range表示条件的范围，本例中条件的范围是“国家”列，如图中的“1”显示范围：,2022年11月27日星期日,全国计算机等级考试Office常用函数,“criteria”表示条件，本例中条件为“国家是中国”，用”中国”表示，如图中的箭头所指部分：,2022年11月27日星期日,全国计算机等级考试Office常用函数,“average_range”表示所需求平均值的数据，本例中为“参加人数”列，用“C2:C10”表示。如图中的“2”部分,2022年11月27日星期日,全国计算机等级考试Office常用函数,AVERAGEIF函数我们已经说明清楚了，最后，如果我们还要其他国家及城市的参加人数的平均值，我们把格式往下拉，然后把“中国”改成“日本”或者“韩国”即可。如图,2022年11月27日星期日,全国计算机等级考试Office常用函数,排位函数Rank（）,rank函数语法形式：rank(number,ref,order)number 为需要求排名的那个数值或者单元格名称（单元格内必须为数字）ref 为排名的参照数值区域order的值为0和1，默认不用输入，得到的就是从大到小的排名，若是想求倒数第几，order的值请使用1。,2022年11月27日星期日,全国计算机等级考试Office常用函数,小学校某年级有3个班，各班的成绩分别在3个工作表中，需要统计出各班级的排名和年级排名。,2022年11月27日星期日,全国计算机等级考试Office常用函数,进行各班级内部排名，以1班为例，在H2单元格（班级名次第一个单元格）输入公式：=rank(F2,$F$2:$F$14)，回车后将公式填充至最后一个非空单元格。2班和3班的排名公式和1班一样。,2022年11月27日星期日,全国计算机等级考试Office常用函数,年级排名（也就是3个工作表内进行排名）还以1班为例，在I2单元格（年级名次第一个单元格）输入公式：=RANK(F2,1班:3班!$F$2:$F$14)，回车即得到f2单元格在整个年级的排名公式解释：跨表时，按住shift件单击工作表标签“1班”和“3班”即可选中相对应的的3个工作表，然后再用鼠标拖动选择排序区域（本例为：F2:F14）。回车后，将公式填充至本列最后一个非空单元格。然后将公式复制到工作表“2班”和“3班”即可。这样即进行了整个年级的综合排名,2022年11月27日星期日,全国计算机等级考试Office常用函数,2022年11月27日星期日,全国计算机等级考试Office常用函数,如何利用excel从身份证号中提取信息,使用excel提供的提取字符串函数，能够快速准确地提取相关信息。使用的主要函数为CONCATENATE、MID、MOD、以及RIGHT、LEFT。,2022年11月27日星期日,全国计算机等级考试Office常用函数,CONCATENATE（text1，text2，）将多个文本字符串联合并为一个文本字符串，可用“&”运算符号代替。MID（text，start-num，num-chars）从左往右返回文本字符串从指定位置开始的指定长度的字符。MOD（number，divisor）返回两数相除所得余数，计算结果的正负号与除数相同，number为被除数，divisor为除数。RIGHT（text，num-chars）从右往左提取指定长度字符。LEFT（num-chars，text）从左往右提取指定长度字符。,2022年11月27日星期日,全国计算机等级考试Office常用函数,中国公民身份证18位，16位为省份地区信息码，714位为出生日期码，1517位为顺序码，第18位为校验码。可根据顺序码的最后一位（即身份证的第17位判定性别，偶数为女，奇数为男）,2022年11月27日星期日,全国计算机等级考试Office常用函数,提取出生日期：以下图为例，在D4单元格输入下列公式“=MID(E4,7,4)&-&MID(E4,11,2)&-&MID(E4,13,2)”或者，输入公式“=CONCATENATE（MID(E4,7,4),-,MID(E4,11,2), -,MID(E4,13,2)）”,按“enter”键结束。下拉填充。,2022年11月27日星期日,全国计算机等级考试Office常用函数,提取性别信息：在单元格C4中输入公式“=IF(MOD(RIGHT(LEFT(E4,17),2),男,女)”，按“enter”键，使用自动填充功能将公式复制到单元格C11。,2022年11月27日星期日,全国计算机等级考试Office常用函数,