EXCEL电子表格处理软.ppt

《EXCEL电子表格处理软.ppt》由会员分享，可在线阅读，更多相关《EXCEL电子表格处理软.ppt（57页珍藏版）》请在三一办公上搜索。

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

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

3、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分析：对

4、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、

5、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

6、,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：参加排序的数值Rang

7、e：排序的范围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：在表格或数组的首列查找指定的值，返回表格当前行中其他列的值 语法：=VLookup(value,array,col_index,True/False)说明：V

9、alue：表示要查找的值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用于计算利息数额

10、的期数，其值在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

11、：总投资期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，将从指定年份一

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、Month函数,日期时间函数TODAY函数：返回系统当前的日期语法：=TODay(),数据库

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

14、表或数据库的列中满足指定条件的数字之和,第三部分 数据管理与数据分析,教学知识点数据表数据排序筛选分类汇总数据透视表,数据表概念字段：数据表中的列称为字段，列标题称为字段名记录：数据表中的每一行称为一条记录数据表的几点说明一个工作表一般只创建一个数据表同一列的数据类型相同避免使用空行或空列记录单的使用,数据排序普通排序自定义排序案例：对商品销售表进行排序筛选：只显示满足特定条件的记录，不符合条件的记录隐藏起来自动筛选案例：对商品销售表进行筛选,高级筛选说明：高级筛选首先要构造一个条件区域，注意条件区域不能与数据表连接，必须空出一行或一列条件区域构造的规则为：同一列中的条件是“或”关系，同一行的

15、条件是“与”关系,分类汇总分类汇总是对数据表指定的行或列中的数据进行汇总统计说明：进行分类汇总前，必须对汇总的类进行排序分类汇总可以完成不同的计算：求和、平均值等案例1：统计销售表中各个类别的商品销售数量,案例2：对每个类别的不同品牌商品的销售金额与利润金额进行汇总分析：该题是按类别进行汇总还是按品牌进行汇总思路：先对类别进行分类汇总，然后在此基础上再对品牌进行分类汇总排序：以类别为主关键字，品牌为此关键字进行排序,数据透视表用于数据分析，可以从庞大的数据库中产生一个动态的汇总表格，能够根据字段的调整快速得出不同的分析结果创建数据透视表方法：利用向导创建案例：统计本月每种类别不同品牌商品的销售

16、总量,第四部分 图表操作,教学知识点图表概述图表建立不同类型图表的建立方法,图表概述图表是指将工作表中的数据用图形表示出来，方便用户查阅信息图表中的图形与数据源关联在一起，当数据源发生变化时，图表相应的发生变化（自动更新）图表分为图表工作表和嵌入式图表，区别是图表工作表存在于一个单独的工作表中，而嵌入式图表与原始数据位于同一工作表中,图表的建立一般用柱形图或条形图比较数据的多少关系；用折线图反映数据间的趋势关系；用饼图表现数据间的比例分配关系条形图的数据要从大到小排序，最大的在最上面柱型图数据要从小到大排序，最小的在最左边在条形图或柱型图中，同一种数据系列，应使用同一颜色图表的标题尽量说出你的

17、观点在图表中尽量少使用3D效果图表美观，布局合理，注释文字恰当,单一图表的创建（图表向导）部分数据创建图表全部数据创建图表向图表中添加数据系列删除图表中的数据系列按行产生与按列产生图表,图表的编辑（美化图表）案例介绍图表的标题标题的旋转标题的链接让数据源显示在图表中图表的转换系列与分类的交换,使用数据标签（删除坐标轴）绘图区背景颜色 数据系列的颜色图例的颜色,在同一个图表中使用两种图表类型在一个图表中使用两种或者以上的图表类型。在这种情况下，需要理解：使用不同的图表类型是针对不同的数据系列而言的。在具体操作时，要注意选择恰当的数据系列进行图表类型的修改案例：将预测值用条形图表显示，实际值用折线

18、图显示,使用次坐标轴若图表中包含的两个数据系列之间的值相差很大，如果使用一个数值轴，那么另一个数据系列的差别情况可能显示得很不明显，在这种情况下，可以通过使用次坐标轴来改善另一个数据系列的显示情况设置方法：图表可采用两种类型，选中没有坐标轴值的数据系列，右击“数据系列格式”，次坐标轴）案例分析,双坐标轴图表和次坐标轴相似案例分析：2008年绍兴每月的平均气温和降水量见表格，请制作图表，平均气温用柱型图表示，降水量用折线图表示。其中，左边数值轴表示气温的数值，右边的数值轴表示降水量的数值,对称条形图表案例分析：用四大城市的实际GDP与预测GDP建立一个对称条形图表方法：首先建立一个条形图表，设置

19、坐标轴刻度，选中Min为-Max，数字格式为自定义的“#，#0，-#，#0”选中要反转的数据系列，选中次坐标轴。在图表的上方出现次坐标轴同样的方法设置次坐标轴的最小值和数字格式，然后将数值次序反转即可,瀑布图表一般用于财务上，分析工程的成本构成 方法：为源数据增加辅助数据列，用以计算数据之间的差额制作一堆积柱型图，数据源包括辅助列在图表中选中辅助列，设置其图案的边框和填充为无进行必要的美化案例分析,饼图,饼图的一些规范：一张饼图显示的区块不应超过五块，人脑容易记住前五位，数据太多分散注意力 数据项若超过五项，可以将五项以外的数据放入到第二个饼图中，构建复合饼图各区块最好从大到小依序呈现，最大的

20、区块由12点钟方向开始，顺时针方向显示；若所显示的数据项中有“其他”选项时，无论“其他”项百分比大小，均应在最后,强调差异的方法是将各区块的颜色由浅到深配色，大块浅色小块深色，色彩感会显得平衡饼图又称为100%图，所以在饼图上不应出现负数，也应避免百分比总和大于100的情况建议不使用图例说明，直接将数据项、百分比或数值标识在各饼图上饼图上的数字，最好使用Arial，较之宋体字更醒目。最好去掉饼块的线条边框,按照上面的规范，构建的饼图如下：,复合饼图,案例分析：构建一个饼图，要求第一绘图区显示5个城市的GDP所占比例，第二个绘图区显示剩下城市的GDP的所占比例自定义复合饼图,散点图散点图主要应用在：相关性表现，看变量y与x之间是否存在相关性的变化趋势矩阵分析，看各分析对象在两个维度交叉的格子上如何分布散点图的方法：散点图与做其他图表不同，选择源数据范围时不能包含标签列，否则无法顺利做出添加趋势线，求出变量y与x的变化趋势案例分析,