Excel函数应用与提高.ppt

Excel函数应用与提高Microsoft Office 培训课程内容•Excel函数的应用方式和目的•如何发现Excel中的函数•函数在Excel中的书写位置•Excel函数的应用场景•自定义Excel函数2024/8/29第2页函数在Excel中的作用•简化公式•实现其他方法无法实现的计算•提高编辑数据的速度•实现判断功能2024/8/29第3页Excel中的函数类型•财务函数•日期和时间函数•数学函数•统计函数•查找和引用函数•数据库函数•文本和数据函数•逻辑函数•信息函数•工程函数2024/8/29第4页函数在那里…2024/8/29第5页在使用函数时应了解…•单元格的引用问题Ø相对地址引用-----=A1+A2+A6Ø绝对地址引用-----=$A$1+$A$2+$A$6Ø混合地址引用-----=A$1+$A2+A$6Ø三维地址引用-----=Sheet1!A1+A2•运算符的优先级问题2024/8/29第6页在Excel中插入函数的方法•手工输入函数Ø在录入时可以访问到该函数的帮助ØExcel可以自动补充函数末尾的括号Ø使用Ctrl+Shift+A可以看到函数的参数说明•插入函数对话框Ø可以根据需求查找函数Ø将给出函数的参数对话框2024/8/29第7页如何在函数中输入参数•不带参数----rand()•使用单元格引用作为参数---sum(a1,a3)•使用名称作为参数---sum(sales)•使用数值作为参数---left(a1,3)•使用表达式作为参数---sqrt(a1+a2)•使用函数作为参数---sin(radians(b1))•使用数组作为参数---or(a1={1,2,3})2024/8/29第8页函数出现在Excel中的位置•单元格中•条件格式对话框中•数据有效性的验证对话框中•图表的选项当中2024/8/29第9页Excel函数应用实例1•统计某部门的加班费ØSumif(range,criteria,sum_range)Ø根据指定条件对若干单元格求和 ØRange    为用于条件判断的单元格区域。

ØCriteria    为确定哪些单元格将被相加求和的条件，其形式可以为数字、表达式或文本例如，条件可以表示为 32、"32"、">32" 或 "apples"ØSum_range    是需要求和的实际单元格2024/8/29第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)。

2024/8/29第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,则精确匹配 2024/8/29第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,则精确匹配•实例：自动生产员工档案表•实例：自动划分成绩区间2024/8/29第13页Excel函数应用实例3•计算员工的年龄Ø使用相关的日期函数---Year函数、Month函数、Today函数Ø找不到的函数ØDateDif(start_date,end_date,code)Østart_date为起始时间Øend_date为结束时间ØCode为返回值的类型，有三种类型ØY-----返回整年数ØM-----返回整月数ØD-----返回天数2024/8/29第14页Excel函数应用实例4•统计单元格区域中的唯一值ØCountIF（range,criteria ）Ø计算区域中满足给定条件的单元格的个数Ørange为需要计算其中满足条件的单元格数目的单元格区域。

Øcriteria为确定哪些单元格将被计算在内的条件，其形式可以为数字、表达式或文本Ø可以将其应用在条件格式中Ø可以将其应用在数据有效性的验证中2024/8/29第15页Excel函数应用实例5•数据的引用Ø indirect()Ø返回由文本字符串指定的引用当需要更改公式中单元格的引用，而不改变公式本身，请使用此函数Ø实例：动态2级列表2024/8/29第16页Excel函数应用实例5Øindex(reference,row_num,column_num,area_num)Ø返回指定的行与列交叉处的单元格引用如果引用由不连续的选定区域组成，可以选择某一连续区域ØReference为所引用的区域块，可以选定多个不连续的区域ØRow_num为reference区域的某一行序号，函数从该行返回一个引用ØColumn_num为reference区域的某一列序号，函数从该列返回一个引用ØArea_num为reference区域中的某一区域，函数从该区域返回一个引用Ø实例：动态返回查询数据 2024/8/29第17页Excel函数应用实例6•文本处理函数ØLen(text) 返回文本字符串中的字符个数。

ØLeft(text,num_chars)返回文本字符串中的第一个或前几个字符 ØRight(text,num_chars)从字符串的最后一个字符开始返回指定个数的字符ØMid(text,start,end)ØConcatenate(text1,text2,…)将多个文本字符串合并成一个2024/8/29第18页Excel函数应用实例6•逻辑判断函数ØIf()执行真假值判断，根据逻辑计算的真假值，返回不同结果可以使用函数 IF 对数值和公式进行条件检测ØAnd()检测是否所有参数均为true,如果是则返回true，否则返回false.ØOr()如果任一参数值为true,即返回true；只有所以参数值为false时才返回false•实例：根据身份证号码自动填写个人信息2024/8/29第19页Excel函数应用实例7•综合实例：自动打印工资条Ø使用的新函数ØRow()函数ØMod()函数ØInt()函数ØColmun()函数Ø使用宏2024/8/29第20页Excel函数应用实例8•自定义Excel函数Ø如何编写自定义函数工具—宏—Visual Basic 编辑器—插入—模块Ø如何应用自定义函数Ø实例：人民币数据大小写转换2024/8/29第21页在Excel中可能出现的错误值•#DIV/0!Ø不能够除以0，为了避免这样的错误可以使用IF函数•#N/AØ数据无效或没有定义•#NAME?Ø包含了没有定义的范围或单元格•#NULL!Ø当指定并不相交的两个区域的交点时，出现这种错误•#REF!Ø无效的单元格引用•#VALUE!Ø数据类型错误，或在输入函数时出现输入错误2024/8/29第22页保护Excel中的函数•设置单元格的属性选中要撤销保护的单元格—单击鼠标右键—设置单元格格式—保护•开启工作表保护  单击工具—保护—保护工作表•设置工作表保护选项2024/8/29第23页谢谢大家！。