1、EXCEL高级应用与案例分析高级应用与案例分析函数函数VBAVBA分析分析图表图表操作操作EXCELEXCEL学习学习EXCEL高级应用与案例分析高级应用与案例分析教学内容：教学内容：第一部分：第一部分：Excel常用操作技巧常用操作技巧第二部分：第二部分：Excel常用函数案例常用函数案例第三部分：数据管理与数据分析第三部分：数据管理与数据分析第四部分：图表操作第四部分：图表操作第一部分第一部分 ExcelExcel常用操作技巧常用操作技巧教学知识点教学知识点数字格式的设置数字格式的设置数据输入有效性设置数据输入有效性设置选择性粘贴选择性粘贴单元格的引用单元格的引用p数字格式的设置数字格式的
2、设置n快速输入大写的人民币值快速输入大写的人民币值方法：方法：“格式格式”“单元格单元格”“数数字字”“特殊特殊”快速输入大量负数快速输入大量负数方法：选中单元格，方法：选中单元格，“格式格式”“单元单元格格”“数字数字”“自定义自定义”“类型（类型（-0）”n快速输入有部分重复的数据（如学号）快速输入有部分重复的数据（如学号）方法：选中单元格，方法：选中单元格，“格式格式”“单元单元格格”“数字数字”“自定义自定义”。例：例：”200918”形式形式n在不连续的单元格中输入相同的数据在不连续的单元格中输入相同的数据方法：利用方法：利用Ctrl键，将不连续的单元格选中，在键，将不连续的单元格选
3、中，在最后一个选中的单元格中输入要显示的内容，按最后一个选中的单元格中输入要显示的内容，按Ctrl+Entern长串数字的输入（包括长串数字的输入（包括0001型数据）型数据）输入以输入以0开头，且可以运算的数（开头，且可以运算的数（“自定义自定义”）n分数的输入分数的输入方法：输入方法：输入0，按空格后，输入分数，按空格后，输入分数n当前日期：当前日期：Ctrl +；p数据输入有效性设置数据输入有效性设置若数据输入不符合，显示警告信息若数据输入不符合，显示警告信息设置鼠标移动时显示提示信息设置鼠标移动时显示提示信息让单元格提供下拉列表项进行选择输入让单元格提供下拉列表项进行选择输入p选择性粘
4、贴选择性粘贴n行列转置行列转置n数学运算（加、减、乘、除）数学运算（加、减、乘、除）方法：在空白单元格内输入要进行运算的数，方法：在空白单元格内输入要进行运算的数，按按Ctrl+C，选中要进行此运算的单元格，进，选中要进行此运算的单元格，进行行“选择性粘贴选择性粘贴”p单元格的引用单元格的引用n单元格运算单元格运算算术运算、文本运算（&）、引用运算（，：！）引用运算：区域运算符，如=Sum(A1:B3)，共6个单元格，联合运算符，如=Sum(A1:B3,C1:D2)，共10个空格交叉运算符，如=Sum(A1:D2 C1:E3)，共4个！三维引用运算符，如=Sheet1!B2n相对引用相对引用以
5、当前单元格的位置为基准，在复制公式时，当前单元格改变时，单元格中的地址也随之发生变化例：A1n绝对引用：例：$A$1n混合引用：例：$A1，A$1 案例：制作一张乘法口诀表案例：制作一张乘法口诀表n三维引用三维引用：不同工作表之间的引用：不同工作表之间的引用同一工作簿中不同工作表之间的引用同一工作簿中不同工作表之间的引用格式：格式：工作表标签工作表标签!单元格引用单元格引用 例：例：Sheet1！B2不同工作簿中工作表的引用不同工作簿中工作表的引用格式：格式：路径路径工作簿名工作簿名.xls工作表标签工作表标签!单元格引单元格引用用例：例：C:工资表工资表.xlssheet1!A1分析：对分析
6、：对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,D
7、max)财务函数(PMT、IPMT、FV、PV和SLN)第二部分第二部分 ExcelExcel常用函数案例常用函数案例SumIfSumIf：按指定条件对若干单元格求和：按指定条件对若干单元格求和语法：语法：=SumIfSumIf（RangeRange，CriteriaCriteria，Sum_rangeSum_range）说明：Range：条件判断区域Criteria：条件表达式Sum_Range：需要求和的单元格区域文本函数文本函数MidMid：从文本指定的位置起返回指定长度的文本：从文本指定的位置起返回指定长度的文本语法：语法：=Mid(text,start_num,num_charsMi
8、d(text,start_num,num_chars)说明：text：指定文本start_num：起始位置Num_chars：返回字符的个数文本函数文本函数ReplaceReplace：将文本中某几位字符以新的字符替换：将文本中某几位字符以新的字符替换语法：语法：=Replace(old_text,start_num,num_chars,new_textReplace(old_text,start_num,num_chars,new_text)说明：Old_text：原始文本start_num：替换的起始位置num_char：多少个字符被替换new_text：用来替换的新字符串IfIf函数与条
9、件格式函数与条件格式条件函数语条件函数语法法:=:=If(Logical_Test,value_True,value_FalseIf(Logical_Test,value_True,value_False)注意：条件函数与条件格式格式的区别 条件函数的嵌套条件函数的嵌套统计函数统计函数CountCount：统计区域内数字的个数：统计区域内数字的个数CountIfCountIf：统计区域内满足特定条件的单元格个数：统计区域内满足特定条件的单元格个数语法：语法：=CountIf(Range,CriteriaCountIf(Range,Criteria)RankRank：返回一个数值在一批数中的位置
10、：返回一个数值在一批数中的位置语法：语法：=Rank=Rank（numbernumber，rangerange，orderorder）说明：说明：Number：参加排序的数值Range：排序的范围Order：0为升序，非0为降序统计函数还有统计函数还有CountBlankCountBlank、MaxMax和和MinMin等常用函数等常用函数查询与引用函数查询与引用函数LookUpLookUp：根据条件查询，分为向量型和数组型：根据条件查询，分为向量型和数组型向量型：向量型：在单行或单列中查找指定的值，返回第二个单行或单列相同位置的值 语法：语法：=LookUp(value,lookup_vec
11、tor,result_vectorLookUp(value,lookup_vector,result_vector)说明：说明：value：表示要搜索的值Lookup_vector：要搜索的值所在的区域Result_vector：返回值所在区域【案例案例】根据员工的编号查询销售情况LookUpLookUp：根据条件查询，分为向量型和数组型：根据条件查询，分为向量型和数组型数组型：数组型：在数组的第一行或第一列查找指定的值，返回数组最后一行或最后一列内同一位置的值 语法：语法：=look_up(lookup_value,arraylook_up(lookup_value,array)【案例案例】
12、根据员工的编号查询销售情况HlookupHlookup：在表格首行查找指定的值，返回表格指定行的：在表格首行查找指定的值，返回表格指定行的同一列上的值同一列上的值 语法：语法：=Hlookup(value,array,indexHlookup(value,array,index)说明：说明：value：表示在数据表的第一行中要查找的值Array：查找的数据表Index：表示返回数据表中第几行的值，如index=1表示返回第一行的值 【案例案例】统计每个销售员的提成比例VLookupVLookup：在表格或数组的首列查找指定的值，返回表格：在表格或数组的首列查找指定的值，返回表格当前行中其他列的
13、值当前行中其他列的值 语法：语法：=VLookup(value,array,col_index,TrueVLookup(value,array,col_index,True/False)/False)说明：说明：Value：表示要查找的值Array：表示查找的数据表Col_index：返回数据表第几列值True/False：表示返回近似值还是精确值财务函数财务函数PMTPMT：基于固定利率及等额分期付款方式，返回贷款的每：基于固定利率及等额分期付款方式，返回贷款的每期付款额期付款额语法：语法：=PMT(rate,nper,pv,fv,typePMT(rate,nper,pv,fv,type)说
14、明：说明：Rate：贷款利率nper：贷款的时间Pv：贷款的本金Fv：未来值（最后一次付款后的现金余额），可选Type：付款时间是在期初（1）还是在期末（0）【案例案例】IPMTIPMT：用于固定利率及等额分期付款方式，返回贷款在：用于固定利率及等额分期付款方式，返回贷款在某一给定期限内的利息金额某一给定期限内的利息金额语法：语法：=IPMT(rateIPMT(rate，per,nper,pv,fv,typeper,nper,pv,fv,type)说明：per用于计算利息数额的期数，其值在1-nper之间【案例案例】某公司向银行贷款5万元，期限是5年，年利率为6%，请计算该公司每年的偿还金额和
15、每年偿还的利息金额FVFV：用于固定利率及等额分期付款方式，返回某项投资的未来值：用于固定利率及等额分期付款方式，返回某项投资的未来值语法：语法：=FV(rate,nper,pmt,fv,typeFV(rate,nper,pmt,fv,type)rate：各期利率nper：总投资期pmt：各期应支付的金额Pv：本金type：付款时间是在期初（1）还是在期末（0）【案例案例】：某人用于某项投资5万元，年利率6%，并在接下来的5年中每年再投资5000元，5年后应得到的金额是多少？PVPV：用于计算某项投资所需的金额：用于计算某项投资所需的金额语法：语法：=FV(rate,nper,pmt,pv,t
16、ypeFV(rate,nper,pmt,pv,type)rate：贷款利率nper：总投资期pmt：各期应支付的金额fv：未来值type：付款时间是在期初（1）还是在期末（0）SLNSLN：用于计算设备每日、每月、每年的折旧值：用于计算设备每日、每月、每年的折旧值语法：语法：=SLN(cost,salvage,lifeSLN(cost,salvage,life)cost：资产原值salvage：资产在折旧末期的价值，即资产残值Life：资产的使用寿命【案例案例】某店铺拥有固定资产5万元，使用10年后的资产残值为8000元，那么每天、每月、每年固定资产的折旧值是多少？日期时间函数日期时间函数Da
17、teDate：计算某一特定日期的系列编号：计算某一特定日期的系列编号语法：语法：=Date(year,month,dayDate(year,month,day)说明：说明：year：指定的年份month：月份day：月份中的第几天注意：注意：若输入的月份大于12，将从指定年份一月份开始往上累加，如date(2008,14,2)，返回2009-2-2若输入的天数大于该月的最大天数，则从指定月数的第一天往上累加，如date(2008,1,35)，返回2008-2-4日期时间函数日期时间函数DayDay：返回指定日期所对应月份中的第几天：返回指定日期所对应月份中的第几天语法：语法：=Day(seri
18、al_numberDay(serial_number)说明：说明：Serial_number：指定的日期或数值例：Day（2008-1-1），返回值为1Day（39448），返回值为1，系统日期将1900-1-1对应于序列号1，后面的日期都是相对于这个序列号进行累加拓展：拓展：YearYear、MonthMonth函数函数日期时间函数日期时间函数TODAYTODAY函数：返回系统当前的日期函数：返回系统当前的日期语法：语法：=TODayTODay()()数据库函数数据库函数数据库函数的语法：数据库函数的语法：=函数名函数名(database,field,criteriadatabase,fie
19、ld,criteria）说明：说明：database：数据清单field：指定函数所使用的数据列criteria：包含给定条件的单元格区域数据库函数的特点数据库函数的特点数据库函数均有上述三个参数数据库函数几乎都是以D开头，将字母D去掉，就是Excel中其他类型的函数。如DCountCount数据库函数数据库函数DcountDcount：返回数据库中满足指定条件的记录字段中包含数值单元格的个数DGetDGet：用于从列表或数据库列中提取符合指定条件的单个值DAverageDAverage：计算列表或数据库的列中满足指定条件的数值的平均值DMaxDMax：返回列表或数据库的列中满足指定条件的最大
20、值DSumDSum：返回列表或数据库的列中满足指定条件的数字之和第三部分第三部分 数据管理与数据分析数据管理与数据分析教学知识点教学知识点数据表数据表数据排序数据排序筛选筛选分类汇总分类汇总数据透视表数据透视表p数据表数据表概念概念 字段：数据表中的列称为字段，列标题称为字段名字段：数据表中的列称为字段，列标题称为字段名 记录：数据表中的每一行称为一条记录记录：数据表中的每一行称为一条记录数据表的几点说明数据表的几点说明 一个工作表一般只创建一个数据表一个工作表一般只创建一个数据表 同一列的数据类型相同同一列的数据类型相同 避免使用空行或空列避免使用空行或空列记录单的使用记录单的使用p数据排序
21、数据排序普通排序普通排序自定义排序自定义排序案例案例：对商品销售表进行排序：对商品销售表进行排序p筛选：只显示满足特定条件的记录，不符合条件的筛选：只显示满足特定条件的记录，不符合条件的记录隐藏起来记录隐藏起来自动筛选自动筛选案例案例：对商品销售表进行筛选：对商品销售表进行筛选高级筛选高级筛选说明：说明：高级筛选首先要构造一个条件区域，注意条件区域不高级筛选首先要构造一个条件区域，注意条件区域不能与数据表连接，必须空出一行或一列能与数据表连接，必须空出一行或一列 条件区域构造的规则为：同一列中的条件是条件区域构造的规则为：同一列中的条件是“或或”关关系，同一行的条件是系，同一行的条件是“与与”
22、关系关系p分类汇总分类汇总分类汇总是对数据表指定的行或列中的数据进行汇总分类汇总是对数据表指定的行或列中的数据进行汇总统计统计说明：说明：进行分类汇总前，必须对汇总的类进行排序进行分类汇总前，必须对汇总的类进行排序分类汇总可以完成不同的计算：求和、平均值等分类汇总可以完成不同的计算：求和、平均值等案例案例1：统计销售表中各个类别的商品销售数量：统计销售表中各个类别的商品销售数量案例案例2：对每个类别的不同品牌商品的销售金额与：对每个类别的不同品牌商品的销售金额与利润金额进行汇总利润金额进行汇总分析：分析：该题是按类别进行汇总还是按品牌进行汇总该题是按类别进行汇总还是按品牌进行汇总思路：先对类别
23、进行分类汇总，然后在此基思路：先对类别进行分类汇总，然后在此基础上再对品牌进行分类汇总础上再对品牌进行分类汇总排序：以类别为主关键字，品牌为此关键字排序：以类别为主关键字，品牌为此关键字进行排序进行排序p数据透视表数据透视表用于数据分析，可以从庞大的数据库中产生一个动态用于数据分析，可以从庞大的数据库中产生一个动态的汇总表格，能够根据字段的调整快速得出不同的分的汇总表格，能够根据字段的调整快速得出不同的分析结果析结果创建数据透视表创建数据透视表方法：利用向导创建方法：利用向导创建案例案例：统计本月每种类别不同品牌商品的销售总量：统计本月每种类别不同品牌商品的销售总量第四部分第四部分 图表操作图
24、表操作教学知识点教学知识点图表概述图表概述图表建立图表建立不同类型图表的建立方法不同类型图表的建立方法p图表概述图表概述图表是指将工作表中的数据用图形表示出来，方便图表是指将工作表中的数据用图形表示出来，方便用户查阅信息用户查阅信息图表中的图形与数据源图表中的图形与数据源关联关联在一起，当数据源发生在一起，当数据源发生变化时，图表相应的发生变化（变化时，图表相应的发生变化（自动更新自动更新）图表分为图表分为图表工作表和嵌入式图表图表工作表和嵌入式图表，区别是图表工，区别是图表工作表存在于一个单独的工作表中，而嵌入式图表与作表存在于一个单独的工作表中，而嵌入式图表与原始数据位于同一工作表中原始数
25、据位于同一工作表中p图表的建立图表的建立p一般用柱形图或条形图比较数据的多少关系；一般用柱形图或条形图比较数据的多少关系；用折线图反映数据间的趋势关系；用饼图表现用折线图反映数据间的趋势关系；用饼图表现数据间的比例分配关系数据间的比例分配关系条形图条形图的数据要从大到小排序，最大的在最上面的数据要从大到小排序，最大的在最上面柱型图柱型图数据要从小到大排序，最小的在最左边数据要从小到大排序，最小的在最左边在条形图或柱型图中，同一种数据系列，应使用同在条形图或柱型图中，同一种数据系列，应使用同一颜色一颜色图表的标题尽量说出你的观点图表的标题尽量说出你的观点在图表中尽量少使用在图表中尽量少使用3D效
26、果效果图表美观，布局合理，注释文字恰当图表美观，布局合理，注释文字恰当p单一图表的创建（图表向导）单一图表的创建（图表向导）部分数据创建图表部分数据创建图表全部数据创建图表全部数据创建图表向图表中添加数据系列向图表中添加数据系列删除图表中的数据系列删除图表中的数据系列按行产生与按列产生图表按行产生与按列产生图表p图表的编辑（美化图表）图表的编辑（美化图表）n案例介绍案例介绍n图表的标题图表的标题标题的旋转标题的旋转标题的链接标题的链接n让数据源显示在图表中让数据源显示在图表中n图表的转换图表的转换n系列与分类的交换系列与分类的交换n使用数据标签（删除坐标轴）使用数据标签（删除坐标轴）n绘图区背
27、景颜色绘图区背景颜色 n数据系列的颜色数据系列的颜色n图例的颜色图例的颜色p在同一个图表中使用两种图表类型在同一个图表中使用两种图表类型在一个图表中使用两种或者以上的图表类型。在这种在一个图表中使用两种或者以上的图表类型。在这种情况下，需要理解：使用不同的图表类型是针对不同情况下，需要理解：使用不同的图表类型是针对不同的数据系列而言的。在具体操作时，要注意选择恰当的数据系列而言的。在具体操作时，要注意选择恰当的数据系列进行图表类型的修改的数据系列进行图表类型的修改案例案例：将预测值用条形图表显示，实际值用折线图显：将预测值用条形图表显示，实际值用折线图显示示p使用次坐标轴使用次坐标轴若若图表中
28、包含图表中包含的的两个数据系列之间的值相差很大，如两个数据系列之间的值相差很大，如果使用一个数值轴，那么另一个数据系列的差别情况果使用一个数值轴，那么另一个数据系列的差别情况可能显示得很不明显，在这种情况下，可以通过使用可能显示得很不明显，在这种情况下，可以通过使用次坐标轴来改善另一个数据系列的显示情况次坐标轴来改善另一个数据系列的显示情况设置方法：图表可采用两种类型，选中没有坐标轴值设置方法：图表可采用两种类型，选中没有坐标轴值的数据系列，右击的数据系列，右击“数据系列格式数据系列格式”，次坐标轴，次坐标轴）案例分析案例分析p双坐标轴图表双坐标轴图表和次坐标轴相似和次坐标轴相似案例分析案例分
29、析：20082008年绍兴每月的平均气温和降水量见表年绍兴每月的平均气温和降水量见表格，请制作图表，平均气温用柱型图表示，降水量用格，请制作图表，平均气温用柱型图表示，降水量用折线图表示。其中，左边数值轴表示气温的数值，右折线图表示。其中，左边数值轴表示气温的数值，右边的数值轴表示降水量的数值边的数值轴表示降水量的数值p对称条形图表对称条形图表案例分析案例分析：用四大城市的实际：用四大城市的实际GDPGDP与预测与预测GDPGDP建立一个建立一个对称条形图表对称条形图表方法：方法：首先建立一个条形图表，设置坐标轴刻度，选中首先建立一个条形图表，设置坐标轴刻度，选中MinMin为为-Max-Ma
30、x，数字格式为自定义的，数字格式为自定义的“#，#0#0，-#-#，#0”#0”选中要反转的数据系列，选中次坐标轴。在图表的上选中要反转的数据系列，选中次坐标轴。在图表的上方出现次坐标轴方出现次坐标轴同样的方法设置次坐标轴的最小值和数字格式，然后同样的方法设置次坐标轴的最小值和数字格式，然后将数值次序反转即可将数值次序反转即可p瀑布图表瀑布图表一般用于财务上，分析工程的成本构成一般用于财务上，分析工程的成本构成 方法：方法：为源数据增加辅助数据列，用以计算数据之间的差额为源数据增加辅助数据列，用以计算数据之间的差额制作一堆积柱型图，数据源包括辅助列制作一堆积柱型图，数据源包括辅助列在图表中选中
31、辅助列，设置其图案的边框和填充为无在图表中选中辅助列，设置其图案的边框和填充为无进行必要的美化进行必要的美化案例分析案例分析p饼图饼图n饼图的一些规范：饼图的一些规范：一张饼图显示的区块不应超过五块一张饼图显示的区块不应超过五块，人脑容易记住前，人脑容易记住前五位，数据太多分散注意力五位，数据太多分散注意力 数据项若超过五项，可以将五项以外的数据放入到第数据项若超过五项，可以将五项以外的数据放入到第二个饼图中，构建二个饼图中，构建复合饼图复合饼图各区块最好从大到小依序呈现，最大的区块由各区块最好从大到小依序呈现，最大的区块由1212点钟点钟方向开始，顺时针方向显示；若所显示的数据项中有方向开始
32、，顺时针方向显示；若所显示的数据项中有“其他其他”选项时，无论选项时，无论“其他其他”项百分比大小，均应项百分比大小，均应在最后在最后强调差异的方法是将各区块的颜色强调差异的方法是将各区块的颜色由浅到深由浅到深配色，大配色，大块浅色小块深色，色彩感会显得平衡块浅色小块深色，色彩感会显得平衡饼图又称为饼图又称为100%100%图，所以在饼图上不应出现负数，图，所以在饼图上不应出现负数，也应避免百分比总和大于也应避免百分比总和大于100100的情况的情况建议不使用图例说明，直接将数据项、百分比或数值建议不使用图例说明，直接将数据项、百分比或数值标识在各饼图上标识在各饼图上饼图上的数字，最好使用饼图
33、上的数字，最好使用ArialArial，较之宋体字更醒目。，较之宋体字更醒目。最好去掉饼块的线条边框最好去掉饼块的线条边框按照上面的规范，构建的饼图如下：按照上面的规范，构建的饼图如下：n复合饼图复合饼图案例分析案例分析：构建一个饼图，要求第一绘图区显示：构建一个饼图，要求第一绘图区显示5 5个城个城市的市的GDPGDP所占比例，第二个绘图区显示剩下城市的所占比例，第二个绘图区显示剩下城市的GDPGDP的的所占比例所占比例n自定义复合饼图自定义复合饼图p散点图散点图散点图主要应用在：散点图主要应用在：相关性表现，看变量相关性表现，看变量y y与与x x之间是否存在相关性的变化之间是否存在相关性的变化趋势趋势矩阵分析，看各分析对象在两个维度交叉的格子上如矩阵分析，看各分析对象在两个维度交叉的格子上如何分布何分布散点图的方法：散点图的方法：散点图与做其他图表不同，选择源数据范围时不能包散点图与做其他图表不同，选择源数据范围时不能包含标签列，否则无法顺利做出含标签列，否则无法顺利做出添加趋势线，求出变量添加趋势线，求出变量y y与与x x的变化趋势的变化趋势案例分析案例分析
