EXCEL电子表格处理软件.ppt

EXCEL高级应用与案例分析,EXCEL高级应用与案例分析,教学内容： 第一部分：Excel常用操作技巧 第二部分： Excel常用函数案例 第三部分：数据管理与数据分析 第四部分：图表操作,第一部分 Excel常用操作技巧,教学知识点 数字格式的设置 数据输入有效性设置 选择性粘贴 单元格的引用,数字格式的设置 快速输入大写的人民币值 方法：“格式”“单元格”“数字”“特殊” 快速输入大量负数 方法：选中单元格， “格式”“单元格”“数字” “自定义”“类型（-0）” 快速输入有部分重复的数据（如学号） 方法：选中单元格， “格式”“单元格”“数字” “自定义”。例：”200918”形式,在不连续的单元格中输入相同的数据 方法：利用Ctrl键，将不连续的单元格选中，在 最后一个选中的单元格中输入要显示的内容，按 Ctrl+Enter 长串数字的输入（包括0001型数据） 输入以0开头，且可以运算的数（“自定义”） 分数的输入 方法：输入0，按空格后，输入分数 当前日期：Ctrl + ；,数据输入有效性设置 若数据输入不符合，显示警告信息 设置鼠标移动时显示提示信息 让单元格提供下拉列表项进行选择输入,选择性粘贴 行列转置 数学运算（加、减、乘、除） 方法：在空白单元格内输入要进行运算的数，按Ctrl+C，选中要进行此运算的单元格，进行“选择性粘贴”,单元格的引用 单元格运算 算术运算、文本运算（&）、引用运算（，：！） 引用运算 ：区域运算符，如=Sum(A1:B3)，共6个单元格 ，联合运算符，如=Sum(A1:B3,C1:D2)，共10个 空格交叉运算符，如=Sum(A1:D2 C1:E3)，共4个 ！ 三维引用运算符，如=Sheet1!B2 相对引用 以当前单元格的位置为基准，在复制公式时，当前单元格改变时，单元格中的地址也随之发生变化 例：A1 绝对引用：例：$A$1 混合引用：例：$A1，A$1 案例：制作一张乘法口诀表,三维引用：不同工作表之间的引用 同一工作簿中不同工作表之间的引用 格式：工作表标签!单元格引用 例：Sheet1！B2 不同工作簿中工作表的引用 格式：路径工作簿名.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) 数据库函数(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：将文本中某几位字符以新的字符替换 语法：=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：返回一个数值在一批数中的位置 语法：=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：根据条件查询，分为向量型和数组型 数组型：在数组的第一行或第一列查找指定的值，返回数组最后一行或最后一列内同一位置的值 语法：=look_up(lookup_value,array) 【案例】根据员工的编号查询销售情况,Hlookup：在表格首行查找指定的值，返回表格指定行的同一列上的值 语法：=Hlookup(value,array,index) 说明： value：表示在数据表的第一行中要查找的值 Array：查找的数据表 Index：表示返回数据表中第几行的值，如index=1表 示返回第一行的值 【案例】统计每个销售员的提成比例,VLookup：在表格或数组的首列查找指定的值，返回表格当前行中其他列的值 语法：=VLookup(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(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：用于计算某项投资所需的金额 语法： =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：指定的年份 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），返回值为1 Day（39448），返回值为1，系统日期将1900-1-1对应于序列号1，后面的日期都是相对于这个序列号进行累加 拓展：Year、Month函数,日期时间函数 TODAY函数：返回系统当前的日期 语法：=TODay(),数据库函数 数据库函数的语法：=函数名(database,field,criteria） 说明： database：数据清单 field：指定函数所使用的数据列 criteria：包含给定条件的单元格区域 数据库函数的特点 数据库函数均有上述三个参数 数据库函数几乎都是以D开头，将字母D去掉，就是Excel中其他类型的函数。如DCountCount,数据库函数 Dcount：返回数据库中满足指定条件的记录字段中包含数值单元格的个数 DGet：用于从列表或数据库列中提取符合指定条件的单个值 DAverage：计算列表或数据库的列中满足指定条件的数值的平均值 DMax：返回列表或数据库的列中满足指定条件的最大值 DSum：返回列表或数据库的列中满足指定条件的数字之和,第三部分 数据管理与数据分析,教学知识点 数据表 数据排序 筛选 分类汇总 数据透视表,数据表 概念 字段：数据表中的列称为字段，列标题称为字段名 记录：数据表中的每一行称为一条记录 数据表的几点说明 一个工作表一般只创建一个数据表 同一列的数据类型相同 避免使用空行或空列 记录单的使用,数据排序 普通排序 自定义排序 案例：对商品销售表进行排序 筛选：只显示满足特定条件的记录，不符合条件的记录隐藏起来 自动筛选 案例：对商品销售表进行筛选,高级筛选 说明： 高级筛选首先要构造一个条件区域，注意条件区域不能与数据表连接，必须空出一行或一列 条件区域构造的规则为：同一列中的条件是“或”关系，同一行的条件是“与”关系,分类汇总 分类汇总是对数据表指定的行或列中的数据进行汇总统计 说明： 进行分类汇总前，必须对汇总的类进行排序 分类汇总可以完成不同的计算：求和、平均值等 案例1：统计销售表中各个类别的商品销售数量,案例2：对每个类别的不同品牌商品的销售金额与利润金额进行汇总 分析： 该题是按类别进行汇总还是按品牌进行汇总 思路：先对类别进行分类汇总，然后在此基础上再对品牌进行分类汇总 排序：以类别为主关键字，品牌为此关键字进行排序,数据透视表 用于数据分析，可以从庞大的数据库中产生一个动态的汇总表格，能够根据字段的调整快速得出不同的分析结果 创建数据透视表 方法：利用向导创建 案例：统计本月每种类别不同品牌商品的销售总量,第四部分 图表操作,教学知识点 图表概述 图表建立 不同类型图表的建立方法,图表概述 图表是指将工作表中的数据用图形表示出来，方便用户查阅信息 图表中的图形与数据源关联在一起，当数据源发生变化时，图表相应的发生变化（自动更新） 图表分为图表工作表和嵌入式图表，区别是图表工作表存在于一个单独的工作表中，而嵌入式图表与原始数据位于同一工作表中,图表的建立 一般用柱形图或条形图比较数据的多少关系；用折线图反映数据间的趋势关系；用饼图表现数据间的比例分配关系 条形图的数据要从大到小排序，最大的在最上面 柱型图数据要从小到大排序，最小的在最左边 在条形图或柱型图中，同一种数据系列，应使用同一颜色 图表的标题尽量说出你的观点 在图表中尽量少使用3D效果 图表美观，布局合理，注释文字恰当,单一图表的创建（图表向导） 部分数据创建图表 全部数据创建图表 向图表中添加数据系列 删除图表中的数据系列 按行产生与按列产生图表,图表的编辑（美化图表） 案例介绍 图表的标题 标题的旋转 标题的链接 让数据源显示在图表中 图表的转换 系列与分类的交换,使用数据标签（删除坐标轴） 绘图区背景颜色 数据系列的颜色 图例的颜色,在同一个图表中使用两种图表类型 在一个图表中使用两种或者以上的图表类型。在这种情况下，需要理解：使用不同的图表类型是针对不同的数据系列而言的。在具体操作时，要注意选择恰当的数据系列进行图表类型的修改 案例：将预测值用条形图表显示，实际值用折线图显示,使用次坐标轴 若图表中包含的两个数据系列之间的值相差很大，如果使用一个数值轴，那么另一个数据系列的差别情况可能显示得很不明显，在这种情况下，可以通过使用次坐标轴来改善另一个数据系列的显示情况 设置方法：图表可采用两种类型，选中没有坐标轴值的数据系列，右击“数据系列格式”，次坐标轴） 案例分析,双坐标轴图表 和次坐标轴相似 案例分析：2008年绍兴每月的平均气温和降水量见表格，请制作图表，平均气温用柱型图表示，降水量用折线图表示。其中，左边数值轴表示气温的数值，右边的数值轴表示降水量的数值,对称条形图表 案例分析：用四大城市的实际GDP与预测GDP建立一个对称条形图表 方法： 首先建立一个条形图表，设置坐标轴刻度，选中Min为-Max，数字格式为自定义的“#，#0，-#，#0” 选中要反转的数据系列，选中次坐标轴。在图表的上方出现次坐标轴 同样的方法设置次坐标轴的最小值和数字格式，然后将数值次序反转即可,瀑布图表 一般用于财务上，分析工程的成本构成 方法： 为源数据增加辅助数据列，用以计算数据之间的差额 制作一堆积柱型图，数据源包括辅助列 在图表中选中辅助列，设置其图案的边框和填充为无 进行必要的美化 案例分析,饼图,饼图的一些规范： 一张饼图显示的区块不应超过五块，人脑容易记住前五位，数据太多分散注意力 数据项若超过五项，可以将五项以外的数据放入到第二个饼图中，构建复合饼图 各区块最好从大到小依序呈现，最大的区块由12点钟方向开始，顺时针方向显示；若所显示的数据项中有“其他”选项时，无论“其他”项百分比大小，均应在最后,强调差异的方法是将各区块的颜色由浅到深配色，大块浅色小块深色，色彩感会显得平衡 饼图又称为100% 图，所以在饼图上不应出现负数，也应避免百分比总和大于100的情况 建议不使用图例说明，直接将数据项、百分比或数值标识在各饼图上 饼图上的数字，最好使用Arial，较之宋体字更醒目。最好去掉饼块的线条边框,按照上面的规范，构建的饼图如下：,复合饼图,案例分析：构建一个饼图，要求第一绘图区显示5个城 市的GDP所占比例，第二个绘图区显示剩下城市的GDP的 所占比例 自定义复合饼图,散点图 散点图主要应用在： 相关性表现，看变量y与x之间是否存在相关性的变化趋势 矩阵分析，看各分析对象在两个维度交叉的格子上如何分布 散点图的方法： 散点图与做其他图表不同，选择源数据范围时不能包含标签列，否则无法顺利做出 添加趋势线，求出变量y与x的变化趋势 案例分析,