Excel函数应用与提高.pptx
2023/2/21第1页课程内容课程内容Excel函数的应用方式和目的如何发现Excel中的函数函数在Excel中的书写位置Excel函数的应用场景自定义Excel函数第1页/共24页2023/2/21第2页函数在函数在Excel中的作用中的作用简化公式实现其他方法无法实现的计算提高编辑数据的速度实现判断功能第2页/共24页2023/2/21第3页Excel中的函数类型中的函数类型财务函数日期和时间函数数学函数统计函数查找和引用函数数据库函数文本和数据函数逻辑函数信息函数工程函数第3页/共24页2023/2/21第4页函数在那里函数在那里第4页/共24页2023/2/21第5页在使用函数时应了解在使用函数时应了解单元格的引用问题相对地址引用-=A1+A2+A6绝对地址引用-=$A$1+$A$2+$A$6混合地址引用-=A$1+$A2+A$6三维地址引用-=Sheet1!A1+A2运算符的优先级问题第5页/共24页2023/2/21第6页在在Excel中插入函数的方法中插入函数的方法手工输入函数在录入时可以访问到该函数的帮助Excel可以自动补充函数末尾的括号使用Ctrl+Shift+A可以看到函数的参数说明插入函数对话框可以根据需求查找函数将给出函数的参数对话框第6页/共24页2023/2/21第7页如何在函数中输入参数如何在函数中输入参数不带参数-rand()使用单元格引用作为参数-sum(a1,a3)使用名称作为参数-sum(sales)使用数值作为参数-left(a1,3)使用表达式作为参数-sqrt(a1+a2)使用函数作为参数-sin(radians(b1)使用数组作为参数-or(a1=1,2,3)第7页/共24页2023/2/21第8页函数出现在函数出现在Excel中的位置中的位置单元格中条件格式对话框中数据有效性的验证对话框中图表的选项当中第8页/共24页2023/2/21第9页Excel函数应用实例函数应用实例1统计某部门的加班费Sumif(range,criteria,sum_range)根据指定条件对若干单元格求和Range为用于条件判断的单元格区域。Criteria为确定哪些单元格将被相加求和的条件，其形式可以为数字、表达式或文本。例如，条件可以表示为32、32、32或apples。Sum_range是需要求和的实际单元格。第9页/共24页2023/2/21第10页Excel函数应用实例函数应用实例2查找函数Lookup(lookup_value,array)函数lookup是在数组的第一行或第一列中查找指定数值，然后返回最后一行或最后一列中相同位置处的数值Lookup_value为函数lookup在数组中所要查找的数值。Lookup_value可以为数字、文本、逻辑值或包含数值的名称或引用。如果函数lookup找不到lookup_value，则使用数组中小于或等于lookup_value的最大数值。如果lookup_value小于第一行或第一列（取决于数组的维数）的最小值，函数lookup返回错误值#N/A。Array为包含文本、数字或逻辑值的单元格区域，它的值用于与lookup_value进行比较。lookup(“C”,“a”,“b”,“c”,“d”;1,2,3,4)在数组的第一行中查找“C”，并返回同一列中最后一行的值(3)。第10页/共24页2023/2/21第11页Excel函数应用实例函数应用实例2查找函数VLookup(lookup_value,table_array,col_index_num,range_lookup)在表格或数值数组的首列查找指定的数值，并由此返回表格或数组当前行中指定列处的数值。Lookup_value为需要在数组第一列中查找的数值。Lookup_value可以为数值、引用或文本字符串。Table_array为需要在其中查找数据的数据表。可以使用对区域或区域名称的引用，例如数据库或列表。Col_index_num为table_array中待返回的匹配值的列序号。Range_look为一逻辑值，如果参数为true或省略，则近似匹配，也就是说如果找不到准确的结果则返回小于lookup_value的最大值。如果参数为false,则精确匹配。第11页/共24页2023/2/21第12页Excel函数应用实例函数应用实例2HLookup(lookup_value,table_array,row_index_num,range_lookup)在表格或数值数组的首行查找指定的数值，并由此返回表格或数组当前列中指定行处的数值。Lookup_value为需要在数据表第一行中进行查找的数值。Lookup_value可以为数值、引用或文本字符串。Table_array为需要在其中查找数据的数据表。可以使用对区域或区域名称的引用。Row_index_num为为table_array中待返回的匹配值的行序号。Range_look为一逻辑值，如果参数为true或省略，则近似匹配，也就是说如果找不到准确的结果则返回小于lookup_value的最大值。如果参数为false,则精确匹配。实例：自动生产员工档案表实例：自动划分成绩区间第12页/共24页2023/2/21第13页Excel函数应用实例函数应用实例3计算员工的年龄使用相关的日期函数-Year函数、Month函数、Today函数找不到的函数DateDif(start_date,end_date,code)start_date为起始时间end_date为结束时间Code为返回值的类型，有三种类型Y-返回整年数M-返回整月数D-返回天数第13页/共24页2023/2/21第14页Excel函数应用实例函数应用实例4统计单元格区域中的唯一值CountIF（range,criteria）计算区域中满足给定条件的单元格的个数。range为需要计算其中满足条件的单元格数目的单元格区域。criteria为确定哪些单元格将被计算在内的条件，其形式可以为数字、表达式或文本。可以将其应用在条件格式中可以将其应用在数据有效性的验证中第14页/共24页2023/2/21第15页Excel函数应用实例函数应用实例5数据的引用indirect()返回由文本字符串指定的引用。当需要更改公式中单元格的引用，而不改变公式本身，请使用此函数。实例：动态2级列表第15页/共24页2023/2/21第16页Excel函数应用实例函数应用实例5index(reference,row_num,column_num,area_num)返回指定的行与列交叉处的单元格引用。如果引用由不连续的选定区域组成，可以选择某一连续区域。Reference为所引用的区域块，可以选定多个不连续的区域。Row_num为reference区域的某一行序号，函数从该行返回一个引用。Column_num为reference区域的某一列序号，函数从该列返回一个引用。Area_num为reference区域中的某一区域，函数从该区域返回一个引用。实例：动态返回查询数据第16页/共24页2023/2/21第17页Excel函数应用实例函数应用实例6文本处理函数Len(text)返回文本字符串中的字符个数。Left(text,num_chars)返回文本字符串中的第一个或前几个字符。Right(text,num_chars)从字符串的最后一个字符开始返回指定个数的字符。Mid(text,start,end)Concatenate(text1,text2,)将多个文本字符串合并成一个。第17页/共24页2023/2/21第18页Excel函数应用实例函数应用实例6逻辑判断函数If()执行真假值判断，根据逻辑计算的真假值，返回不同结果。可以使用函数IF对数值和公式进行条件检测。And()检测是否所有参数均为true,如果是则返回true，否则返回false.Or()如果任一参数值为true,即返回true；只有所以参数值为false时才返回false。实例：根据身份证号码自动填写个人信息第18页/共24页2023/2/21第19页Excel函数应用实例函数应用实例7综合实例：自动打印工资条使用的新函数Row()函数Mod()函数Int()函数Colmun()函数使用宏第19页/共24页2023/2/21第20页Excel函数应用实例函数应用实例8自定义Excel函数如何编写自定义函数工具宏VisualBasic编辑器插入模块如何应用自定义函数实例：人民币数据大小写转换第20页/共24页2023/2/21第21页在在Excel中可能出现的错误值中可能出现的错误值#DIV/0!不能够除以0，为了避免这样的错误可以使用IF函数#N/A数据无效或没有定义#NAME?包含了没有定义的范围或单元格#NULL!当指定并不相交的两个区域的交点时，出现这种错误#REF!无效的单元格引用#VALUE!数据类型错误，或在输入函数时出现输入错误第21页/共24页2023/2/21第22页保护保护Excel中的函数中的函数设置单元格的属性选中要撤销保护的单元格单击鼠标右键设置单元格格式保护开启工作表保护单击工具保护保护工作表设置工作表保护选项第22页/共24页谢谢大家！第23页/共24页2023/2/21第24页感谢您的观看！第24页/共24页
