excel电子表格处理软件 (1)

《excel电子表格处理软件 (1)》由会员分享，可在线阅读，更多相关《excel电子表格处理软件 (1)（57页珍藏版）》请在金锄头文库上搜索。

1、EXCEL高级应用与案例分析函数VBA分析图表操作EXCEL学习EXCEL高级应用与案例分析教学内容：第一部分：Excel常用操作技巧第二部分： Excel常用函数案例第三部分：数据管理与数据分析第四部分：图表操作第一部分 Excel常用操作技巧教学知识点p数字格式的设置p数据输入有效性设置p选择性粘贴p单元格的引用p数字格式的设置n快速输入大写的人民币值 方法：“格式”“单元格”“数字”“特殊”n快速输入大量负数 方法：选中单元格， “格式”“单元格”“数 字”“自定义”“类型（-0）”n快速输入有部分重复的数据（如学号） 方法：选中单元格， “格式”“单元格”“数 字”“自定义”。例：”2

2、00918”形式n在不连续的单元格中输入相同的数据 方法：利用Ctrl键，将不连续的单元格选中，在 最后一个选中的单元格中输入要显示的内容，按 Ctrl+Entern长串数字的输入（包括0001型数据） 输入以0开头，且可以运算的数（“自定义”）n分数的输入 方法：输入0，按空格后，输入分数n当前日期：Ctrl + ；p数据输入有效性设置若数据输入不符合，显示警告信息设置鼠标移动时显示提示信息让单元格提供下拉列表项进行选择输入p选择性粘贴n行列转置n数学运算（加、减、乘、除）方法：在空白单元格内输入要进行运算的数 ，按Ctrl+C，选中要进行此运算的单元格， 进行“选择性粘贴”p单元格的引用n

3、单元格运算 算术运算、文本运算（&）、引用运算（，：！）引用运算：区域运算符，如=Sum(A1:B3)，共6个单元格，联合运算符，如=Sum(A1:B3,C1:D2)，共10个空格交叉运算符，如=Sum(A1:D2 C1:E3)，共4个 ！ 三维引用运算符，如=Sheet1!B2n相对引用 以当前单元格的位置为基准，在复制公式时，当前单元格改变时，单元 格中的地址也随之发生变化 例：A1n绝对引用：例：$A$1n混合引用：例：$A1，A$1案例：制作一张乘法口诀表n三维引用：不同工作表之间的引用同一工作簿中不同工作表之间的引用格式：工作表标签!单元格引用 例：Sheet1！B2不同工作簿中工作

4、表的引用格式：路径工作簿名.xls工作表标签!单元格引用例：C:工资表.xlssheet1!A1分析：对sheet1至sheet4四个工作表中A1单元格的 数据求和=Sum(sheet1:sheet4!A1)教学知识点求和相关函数(Sum、SumIf)统计函数（Average、Count、CountIf、 Frequency、Max、Min、 Rank）文本函数(Mid、Replace、Concatenate、Text)逻辑函数与条件格式（If,And,Or,Not）日期时间函数（Date、Year、Month、Day和Today）查询与引用函数(VlookUp、HlookUp、LookUp)

5、数据库函数(Dget,Dcount,Dsum,Daverage,Dmax)财务函数(PMT、IPMT、FV、PV和SLN)第二部分 Excel常用函数案例SumIf：按指定条件对若干单元格求和语法：=SumIf（Range，Criteria，Sum_range）说明：Range：条件判断区域Criteria：条件表达式Sum_Range：需要求和的单元格区域文本函数Mid：从文本指定的位置起返回指定长度的文本语法：=Mid(text,start_num,num_chars)说明：text：指定文本start_num：起始位置Num_chars：返回字符的个数文本函数Replace：将文本中某几

6、位字符以新的字符替换语法：=Replace(old_text,start_num,num_chars,new_text)说明：Old_text：原始文本start_num：替换的起始位置num_char：多少个字符被替换new_text：用来替换的新字符串If函数与条件格式条件函数语法:=If(Logical_Test,value_True,value_False)注意：条件函数与条件格式的区别条件函数的嵌套统计函数Count：统计区域内数字的个数CountIf：统计区域内满足特定条件的单元格个数语法：=CountIf(Range,Criteria)Rank：返回一个数值在一批数中的位置语法：

7、=Rank（number，range，order）说明：Number：参加排序的数值Range：排序的范围Order：0为升序，非0为降序统计函数还有CountBlank、Max和Min等常用函数查询与引用函数LookUp：根据条件查询，分为向量型和数组型向量型：在单行或单列中查找指定的值，返回第二个单行或单列相同位置的值语法：=LookUp(value,lookup_vector,result_vector)说明：value：表示要搜索的值Lookup_vector：要搜索的值所在的区域Result_vector：返回值所在区域【案例】根据员工的编号查询销售情况LookUp：根据条件查询，分

8、为向量型和数组型数组型：在数组的第一行或第一列查找指定的值，返回数组最后一行或最后一列内同一位置的值语法：=look_up(lookup_value,array)【案例】根据员工的编号查询销售情况Hlookup：在表格首行查找指定的值，返回表格指定行的同一列上的值语法：=Hlookup(value,array,index)说明：value：表示在数据表的第一行中要查找的值Array：查找的数据表Index：表示返回数据表中第几行的值，如index=1表示返回第一行的值【案例】统计每个销售员的提成比例VLookup：在表格或数组的首列查找指定的值，返回表格 当前行中其他列的值语法：=VLooku

9、p(value,array,col_index,True/False)说明：Value：表示要查找的值Array：表示查找的数据表Col_index：返回数据表第几列值True/False：表示返回近似值还是精确值财务函数PMT：基于固定利率及等额分期付款方式，返回贷款的每 期付款额语法：=PMT(rate,nper,pv,fv,type)说明：Rate：贷款利率nper：贷款的时间Pv：贷款的本金Fv：未来值（最后一次付款后的现金余额），可选Type：付款时间是在期初（1）还是在期末（0）【案例】IPMT：用于固定利率及等额分期付款方式，返回贷款在某一给定期限内的利息金额语法： =IPMT(

10、rate，per,nper,pv,fv,type)说明：per用于计算利息数额的期数，其值在1-nper之间【案例】某公司向银行贷款5万元，期限是5年，年利率为6%，请计算该公司每年的偿还金额和每年偿还的利息金额FV：用于固定利率及等额分期付款方式，返回某项投资的未来值语法： =FV(rate,nper,pmt,fv,type)rate：各期利率nper：总投资期pmt：各期应支付的金额Pv：本金type：付款时间是在期初（1）还是在期末（0）【案例】：某人用于某项投资5万元，年利率6%，并在接下来的5年中每年再投资5000元，5年后应得到的金额是多少？PV：用于计算某项投资所需的金额语法：

11、=FV(rate,nper,pmt,pv,type)rate：贷款利率nper：总投资期pmt：各期应支付的金额fv：未来值type：付款时间是在期初（1）还是在期末（0）SLN：用于计算设备每日、每月、每年的折旧值语法： =SLN(cost,salvage,life)cost：资产原值salvage：资产在折旧末期的价值，即资产残值Life：资产的使用寿命【案例】某店铺拥有固定资产5万元，使用10年后的资产 残值为8000元，那么每天、每月、每年固定资产的折旧 值是多少？日期时间函数 Date：计算某一特定日期的系列编号 语法：=Date(year,month,day) 说明：year：指定

12、的年份month：月份day：月份中的第几天 注意：若输入的月份大于12，将从指定年份一月份开始往上累加， 如date(2008,14,2)，返回2009-2-2若输入的天数大于该月的最大天数，则从指定月数的第一天 往上累加，如date(2008,1,35)，返回2008-2-4日期时间函数Day：返回指定日期所对应月份中的第几天语法：=Day(serial_number)说明：Serial_number：指定的日期或数值例：Day（2008-1-1），返回值为1Day（39448），返回值为1，系统日期将1900-1-1对应于序列号1， 后面的日期都是相对于这个序列号进行累加拓展：Year、

13、Month函数日期时间函数TODAY函数：返回系统当前的日期语法：=TODay()数据库函数数据库函数的语法：=函数名(database,field,criteria）说明：database：数据清单field：指定函数所使用的数据列criteria：包含给定条件的单元格区域数据库函数的特点数据库函数均有上述三个参数数据库函数几乎都是以D开头，将字母D去掉，就是Excel中其他类型的函数。如DCountCount数据库函数Dcount：返回数据库中满足指定条件的记录字段中包含数 值单元格的个数DGet：用于从列表或数据库列中提取符合指定条件的单个 值DAverage：计算列表或数据库的列中满足

14、指定条件的数值 的平均值DMax：返回列表或数据库的列中满足指定条件的最大值DSum：返回列表或数据库的列中满足指定条件的数字之和第三部分 数据管理与数据分析p教学知识点p数据表p数据排序p筛选p分类汇总p数据透视表p数据表概念n字段：数据表中的列称为字段，列标题称为字段名n记录：数据表中的每一行称为一条记录数据表的几点说明 一个工作表一般只创建一个数据表 同一列的数据类型相同 避免使用空行或空列记录单的使用p数据排序普通排序自定义排序案例：对商品销售表进行排序p筛选：只显示满足特定条件的记录，不符合条件的 记录隐藏起来自动筛选案例：对商品销售表进行筛选高级筛选说明：n高级筛选首先要构造一个条

15、件区域，注意条件区域不 能与数据表连接，必须空出一行或一列n条件区域构造的规则为：同一列中的条件是“或”关系 ，同一行的条件是“与”关系p分类汇总分类汇总是对数据表指定的行或列中的数据进行汇总统 计说明：进行分类汇总前，必须对汇总的类进行排序分类汇总可以完成不同的计算：求和、平均值等案例1：统计销售表中各个类别的商品销售数量案例2：对每个类别的不同品牌商品的销售金额与 利润金额进行汇总 分析：该题是按类别进行汇总还是按品牌进行汇总思路：先对类别进行分类汇总，然后在此基础 上再对品牌进行分类汇总排序：以类别为主关键字，品牌为此关键字进 行排序p数据透视表用于数据分析，可以从庞大的数据库中产生一个

16、动态的 汇总表格，能够根据字段的调整快速得出不同的分析结 果创建数据透视表方法：利用向导创建案例：统计本月每种类别不同品牌商品的销售总量第四部分 图表操作p教学知识点p图表概述p图表建立p不同类型图表的建立方法p图表概述图表是指将工作表中的数据用图形表示出来，方便 用户查阅信息图表中的图形与数据源关联在一起，当数据源发生 变化时，图表相应的发生变化（自动更新）图表分为图表工作表和嵌入式图表，区别是图表工 作表存在于一个单独的工作表中，而嵌入式图表与 原始数据位于同一工作表中p图表的建立p一般用柱形图或条形图比较数据的多少关系； 用折线图反映数据间的趋势关系；用饼图表现 数据间的比例分配关系条形图的数据要从大到小排序，最大的在最上面 柱型图数据要从小到大排序，最小的在最左边在条形图或柱型图中，同一种数据系列，应使用同 一颜色图表的标题尽量说出你的观点