高级办公自动化课件02.ppt

实用与应用 高效与专业绍兴文理学院王秀庆Wangxiuqing——高级办公自动化AOA第二讲 Excel高级应用与应用实例第二节 Excel函数与应用案例第三节 数据管理与分析第四节 数据的图示化表达第一节 Excel常用操作技巧第一节 Excel常用操作技巧教学内容3第一节 Excel常用操作技巧教学知识点数字输入技巧数据的有效性设置选择性粘贴公式和单元格的引用区域与名称数组应用工作表的安全4第一节 Excel常用操作技巧数据输入技巧快速输入大写的人民币值方法：“格式”“单元格”“数字”“特殊”输入负数快速输入有部分重复的数据（如学号）方法：选中单元格， “格式”“单元格”“数字 ”“自定义”例：”340822”@形式在不连续的单元格中输入相同的数据方法：利用Ctrl键，将不连续的单元格选中，在最后一个 选中的单元格中输入要显示的内容，按Ctrl+Enter5第一节 Excel常用操作技巧长串数字的输入（包括0001型数据）•输入以0开头的数字序列，单引号‘开始或单元格格式 设置为文本•超过15位的数据以科学计数法显示的问题，方法：单 元格格式为文本或’分数的输入方法：输入0，按空格后，输入分数当前日期：Ctrl+；6第一节 Excel常用操作技巧数据输入有效性设置（案例）限制单元格中数据的输入范围若数据输入不符合，显示出错信息鼠标在单元格上移动时显示提示信息让单元格提供下拉列表项进行选择输入7第一节 Excel常用操作技巧选择性粘贴（案例）行列转置数学运算（加、减、乘、除）方法：在空白单元格内输入要进行运算的数，按Ctrl+C， 选中要进行此运算的单元格，进行“选择性粘贴”p引用某些通过公式计算的数据8第一节 Excel常用操作技巧公式和运算符公式以=开始的表达式运算符n算术运算n文本运算（&）n引用运算•：区域运算符，如=Sum(A1:B3)，共6个单元格•，联合运算符，如=Sum(A1:B3,C1:D2)，共10个•空格交叉运算符，如=Sum(A1:D2 C1:E3)，共4个• ！ 三维引用运算符，如=Sheet1!B29第一节 Excel常用操作技巧单元格的引用相对引用以当前单元格的位置为基准，在复制公式时，当前单元 格改变时，单元格中的地址也随之发生变化例：A1绝对引用：例：$A$1混合引用：例：$A1，A$1【案例】制作乘法口诀表10第一节 Excel常用操作技巧三维引用：不同工作表之间的引用•同一工作簿中不同工作表之间的引用格式：工作表标签!单元格引用 如：Sheet1!B2•不同工作簿中工作表的引用格式：‘路径\[工作簿名.xls]工作表标签’!单元格引用如：’C:\[工资表.xls]sheet1’!A1思考：=Sum(sheet1:sheet4!A1)表示意思？【案例】三维引用11第一节 Excel常用操作技巧区域与名称单元格与区域 单元格本身就为一区域；多个单元格也是一个区域；一 张表就是一个大区域区域名称 例：“A1:C3”就是一个区域，问题是它的名称是什么？可以把“A1:C3”当成它的名称，并对它进行引用，例 “=SUM(A1:C3)”，但它不够形象12第一节 Excel常用操作技巧区域名称的定义方法•选中区域，直接在名称框中输入名称•“插入”—“名称”，选中区域，然后添加【案例】区域名称的应用13第一节 Excel常用操作技巧数组数组是单元的集合或是一组处理值的集合。

一个数组公式可 以占用一个或多个单元格区域数组公式与普通公式的区别•输入方法不同，普通公式按回车键（Enter），数组公 式按【Shift】+【Ctrl】+【Enter】组合键•在计算时，数组公式把多个数据做为一个整体进行计算 ，结果是返回多个数值，普通公式只是返回单个数值， 即使在引用时引用了多个数值，但实际返回的只是其中 的一个 【案例】了解数组公式14第一节 Excel常用操作技巧数组数组常量数组中的参数直接是数值本身，这样的数组称为数组常量，分为一维数组和二维数组•一维数组包括水平数组和垂直数组，水平数组中元素用逗号隔开，垂直数组中元素用分号隔开，如{10,20,30,40,50}，{100；200；300；400}•二维数组，用逗号将一行内的数据分开，用分号将隔行隔开15第一节 Excel常用操作技巧编辑数组一个数组中包含多个单元格，这些单元格形成一个整体， 数组中的单元格不能单独进行编辑，必须选取整个数组才 能进行相应的操作•数组选取：单击数组中的任一单元格”编辑“”定 位“”定位条件“”当前数组“•编辑数组：选择要编辑的数组，在编辑栏上单击鼠标， 表示数组公式的括号消失，对公式进行编辑，再按【 Shift】+【Ctrl】+【Enter】完成对数组公式的修改•删除数组：选定要删除的数组，按【Ctrl】+【Delete 】键或”编辑“”清除“16第一节 Excel常用操作技巧数组应用在工作表中有时使用函数不能直接得到期望的结果，数组 公式却能轻松解决。

【案例】数组应用17第一节 Excel常用操作技巧工作表的安全隐藏含有重要数据的行、列保护工作表隐藏数据计算的公式设置可编辑的区域（需输入密码才能在指定的区域输入数 据，其他区域不能输入）设置可编辑区域的权限（无需输入密码就可在指定的区域 输入数据）18教学内容第一节 Excel常用操作技巧第三节 数据管理与分析第四节 数据的图示化表达第二节 Excel函数与应用案例第二节 Excel函数与应用案例19第二节 Excel函数与应用案例教学知识点求和相关函数(Sum、SumIf)n文本函数(Mid、Concatenate、Replace和Exact)n统计函数（Count、CountIf、Max、Min、Rank）n条件函数与条件格式（If）n查询与引用函数(LookUp、HlookUp、VlookUp)n财务函数(PMT、IPMT、FV、PV和SLN)日期时间函数（Date、Year、Month、Day和Today）数据库函数20第二节 Excel函数与应用案例SumIf：按指定条件对若干单元格求和语法：=SumIf（Range，Criteria，Sum_range）说明：•Range：条件判断区域•Criteria：条件表达式•Sum_Range：需要求和的单元格区域例：=SUMIF(A2:A11,“A产品“,C2:C11)【案例】在房产销售表中统计各销售员的总销总额21第二节 Excel函数与应用案例文本函数Mid：从文本指定的位置起返回指定长度的文本语法：=Mid(text,start_num,num_chars)说明：•text：指定文本•start_num：起始位置•Num_chars：返回字符的个数22第二节 Excel函数与应用案例文本函数Concatenate：将多个文本连接在一起，显示在一个单元 格中语法：=Concatenate(text1, text2,…)23第二节 Excel函数与应用案例文本函数Substitute：替换文本中某个特定文本字符串语法：=Substitute(text1, old_text,new_text,instance_num)说明：•text1：原始文本•old_text：将被替换的字符串•new_text：替换old_text的新字符串•Instance_num：若文本中有多组old_texts，使用该参 数来指定被替换的字符串是文本中的第几组，若不指定 instance_num值，文本中所有的old_text都会被替换24第二节 Excel函数与应用案例文本函数Replace：将文本中某几位字符以新的字符替换语法： =Replace(old_text,start_num,num_chars,new_text)说明：•Old_text：原始文本•start_num：替换的起始位置•num_char：多少个字符被替换•new_text：用来替换的新字符串25第二节 Excel函数与应用案例文本函数Exact：比较两个文本字符串是否相同语法：=Replace(text1, text2)说明：相同返回True，不相同返回False此外，常用的文本函数还有Lower、Upper更改大小写【案例】文本函数的应用26第二节 Excel函数与应用案例If函数与条件格式条件函数语法:=If(Logical_Test,value_True,value_False)注意：条件函数与条件格式的区别条件函数的嵌套【案例】条件函数与条件格式的应用27第二节 Excel函数与应用案例统计函数Count：统计区域内数字的个数【案例】统计销售表中， “A产品”销售的纪录的条数=COUNT(IF(A2:A12=A2,C2:C12))统计A2:A12区域中值为A2，在C2:C12中的数字的个数说明：输完公式后，按Ctrl+shift+Enter键 ‘数组CountIf：统计区域内满足特定条件的单元格个数语法：=CountIf(Range,Criteria)【案例】按条件进行统计28第二节 Excel函数与应用案例Rank：返回一个数值在一批数中的位置语法：=Rank（number，range，order）说明：•Number：参加排序的数值•Range：排序的范围•Order：0为升序，非0为降序【案例】按年龄大小进行排名统计函数还有CountBlank、Max和Min等常用函数29第二节 Excel函数与应用案例查询与引用函数LookUp：根据条件查询，分为向量型和数组型n向量型：在单行或单列中查找指定的值，返回第二个单行或单列相同位置的值语法：=LookUp(value,lookup_vector,result_vector)说明：•value：表示要搜索的值•Lookup_vector：要搜索的值所在的区域•Result_vector：返回值所在区域【案例】根据员工的编号查询销售情况30第二节 Excel函数与应用案例LookUp：根据条件查询，分为向量型和数组型n数组型：在数组的第一行或第一列查找指定的值，返回数组最后一行或最后一列内同一位置的值语法：=look_up(lookup_value,array)【案例】根据员工的编号查询销售情况31第二节 Excel函数与应用案例Hlookup：在表格首行查找指定的值，返回表格指定行的同一列上的值语法：=Hlookup(value,array,index)说明：•value：表示在数据表的第一行中要查找的值•Array：查找的数据表•Index：表示返回数据表中第几行的值，如index=1表示返回第一行的值【案例】统计每个销售员的提成比例32第二节 Excel函数与应用案例VLookup：在表格或数组的首列查找指定的值，返回表 格当前行中其他列的值语法：=VLookup(value,array,col_index,True/False)说明：•Value：表示要查找的值•Array：表示查找的数据表•Col_index：返回数据表第几列值•True/False：表示返回近似值还是精确值【案例】填充商品的价格33第二节 Excel函数与应用案例财务函数PMT：基于固定利率及等额分期付款方式，返回贷款的每 期付款额语法：=PMT(rate,nper,pv,fv,type)说明：•Rate：贷款利率•nper：贷款的时间•Pv：贷款的本金•Fv：未来值（最后一次付款后的现金余额），可选•Type：付款时间是在期初（1）还是在期末（0）【案例】 34第二节 Excel函数与应用案例IPMT：用于固定利率及等额分期付款方式，返回贷款在某一给定期限内的利息金额语法： =IPMT(rate，per,nper,pv,fv,type)说明：per用于计算利息数额的期数，其值在1-nper之间【案例】某公司向银行贷款5万元，期限是5年，年利率为6%，请计算该公司每年的偿还金额和每年偿还的利息金额35第二节 Ex。