EXCEL电子表格处理软

EXCEL高级应用与案例分析EXCEL电子表格处理软共57页，您现在浏览的是第1页!EXCEL电子表格处理软共57页，您现在浏览的是第2页!EXCEL高级应用与案例分析教学内容：部分：Excel常用操作技巧第二部分：Excel常用函数案例第三部分：数据管理与数据分析第四部分：图表操作EXCEL电子表格处理软共57页，您现在浏览的是第3页!部分Excel常用操作技巧教学知识点数字格式的设置数据输入有效性设置选择性粘贴单元格的引用EXCEL电子表格处理软共57页，您现在浏览的是第4页!在不连续的单元格中输入相同的数据 方法：利用Ctrl键，将不连续的单元格选中，在 最后一个选中的单元格中输入要显示的内容，按 Ctrl+Enter长串数字的输入（包括0001型数据） 输入以0开头，且可以运算的数（“自定义”）分数的输入 方法：输入0，按空格后，输入分数当前日期：Ctrl+；EXCEL电子表格处理软共57页，您现在浏览的是第5页!选择性粘贴行列转置数学运算（加、减、乘、除） 方法：在空白单元格内输入要进行运算的数，按Ctrl+C，选中要进行此运算的单元格，进行“选择性粘贴”EXCEL电子表格处理软共57页，您现在浏览的是第6页!三维引用：不同工作表之间的引用同一工作簿中不同工作表之间的引用 格式：工作表标签!单元格引用例：Sheet1！B2不同工作簿中工作表的引用 格式：‘路径\[工作簿名.xls]工作表标签’!单元格引用 例：’C:\[工资表.xls]sheet1’!A1 分析：对sheet1至sheet4四个工作表中A1单元格的数据求和

=Sum(sheet1:sheet4!A1)

EXCEL电子表格处理软共57页，您现在浏览的是第7页!SumIf：按指定条件对若干单元格求和 语法：=SumIf（Range，Criteria，Sum_range）

说明：Range：条件判断区域Criteria：条件表达式Sum_Range：需要求和的单元格区域EXCEL电子表格处理软共57页，您现在浏览的是第8页!文本函数Replace：将文本中某几位字符以新的字符替换 语法：=Replace(old_text,start_num,num_chars,new_text)

说明：Old_text：原始文本start_num：替换的起始位置num_char：多少个字符被替换new_text：用来替换的新字符串EXCEL电子表格处理软共57页，您现在浏览的是第9页!统计函数Count：统计区域内数字的个数 CountIf：统计区域内满足特定条件的单元格个数语法：=CountIf(Range,Criteria)EXCEL电子表格处理软共57页，您现在浏览的是第10页!查询与引用函数LookUp：根据条件查询，分为向量型和数组型向量型：在单行或单列中查找指定的值，返回第二个单行或单列相同位置的值 语法：=LookUp(value,lookup_vector,result_vector) 说明：value：表示要搜索的值Lookup_vector：要搜索的值所在的区域Result_vector：返回值所在区域【案例】根据员工的编号查询销售情况EXCEL电子表格处理软共57页，您现在浏览的是第11页!Hlookup：在表格首行查找指定的值，返回表格指定行的同一列上的值 语法：=Hlookup(value,array,index) 说明：value：表示在数据表的行中要查找的值Array：查找的数据表Index：表示返回数据表中第几行的值，如index=1表示返回行的值 【案例】统计每个销售员的提成比例EXCEL电子表格处理软共57页，您现在浏览的是第12页!财务函数PMT：基于固定利率及等额分期付款方式，返回贷款的每期付款额 语法：=PMT(rate,nper,pv,fv,type) 说明：Rate：贷款利率nper：贷款的时间Pv：贷款的本金Fv：未来值（最后一次付款后的现金余额），可选Type：付款时间是在期初（1）还是在期末（0）【案例】EXCEL电子表格处理软共57页，您现在浏览的是第13页!

FV：用于固定利率及等额分期付款方式，返回某项投资的未来值

语法：=FV(rate,nper,pmt,fv,type)rate：各期利率nper：总投资期pmt：各期应支付的金额Pv：本金type：付款时间是在期初（1）还是在期末（0）【案例】：某人用于某项投资5万元，年利率6%，并在接下来的5年中每年再投资5000元，5年后应得到的金额是多少？EXCEL电子表格处理软共57页，您现在浏览的是第14页!

SLN：用于计算设备每日、每月、每年的折旧值

语法：=SLN(cost,salvage,life)cost：资产原值salvage：资产在折旧末期的价值，即资产残值Life：资产的使用寿命【案例】某店铺拥有固定资产5万元，使用10年后的资产残值为8000元，那么每天、每月、每年固定资产的折旧值是多少？EXCEL电子表格处理软共57页，您现在浏览的是第15页!日期时间函数Day：返回指定日期所对应月份中的第几天 语法：=Day(serial_number) 说明：Serial_number：指定的日期或数值例：Day（2008-1-1），返回值为1Day（39448），返回值为1，系统日期将1900-1-1对应于序列号1，后面的日期都是相对于这个序列号进行累加拓展：Year、Month函数EXCEL电子表格处理软共57页，您现在浏览的是第16页!数据库函数 数据库函数的语法：=函数名(database,field,criteria） 说明：database：数据清单field：指定函数所使用的数据列criteria：包含给定条件的单元格区域数据库函数的特点数据库函数均有上述三个参数数据库函数几乎都是以D开头，将字母D去掉，就是Excel中其他类型的函数。如DCountCountEXCEL电子表格处理软共57页，您现在浏览的是第17页!第三部分数据管理与数据分析教学知识点数据表数据排序筛选分类汇总数据透视表EXCEL电子表格处理软共57页，您现在浏览的是第18页!数据排序普通排序自定义排序

案例：对商品销售表进行排序筛选：只显示满足特定条件的记录，不符合条件的记录隐藏起来自动筛选

案例：对商品销售表进行筛选EXCEL电子表格处理软共57页，您现在浏览的是第19页!分类汇总 分类汇总是对数据表指定的行或列中的数据进行汇总统计 说明：进行分类汇总前，必须对汇总的类进行排序分类汇总可以完成不同的计算：求和、平均值等

案例1：统计销售表中各个类别的商品销售数量EXCEL电子表格处理软共57页，您现在浏览的是第20页!数据透视表

用于数据分析，可以从庞大的数据库中产生一个动态的汇总表格，能够根据字段的调整快速得出不同的分析结果创建数据透视表

方法：利用向导创建

案例：统计本月每种类别不同品牌商品的销售总量EXCEL电子表格处理软共57页，您现在浏览的是第21页!图表概述图表是指将工作表中的数据用图形表示出来，方便用户查阅信息图表中的图形与数据源关联在一起，当数据源发生变化时，图表相应的发生变化（自动更新）图表分为图表工作表和嵌入式图表，区别是图表工作表存在于一个单独的工作表中，而嵌入式图表与原始数据位于同一工作表中EXCEL电子表格处理软共57页，您现在浏览的是第22页!单一图表的创建（图表向导）部分数据创建图表全部数据创建图表向图表中添加数据系列删除图表中的数据系列按行产生与按列产生图表EXCEL电子表格处理软共57页，您现在浏览的是第23页!使用数据标签（删除坐标轴）绘图区背景颜色数据系列的颜色图例的颜色EXCEL电子表格处理软共57页，您现在浏览的是第24页!使用次坐标轴

若图表中包含的两个数据系列之间的值相差很大，如果使用一个数值轴，那么另一个数据系列的差别情况可能显示得很不明显，在这种情况下，可以通过使用次坐标轴来改善另一个数据系列的显示情况 设置方法：图表可采用两种类型，选中没有坐标轴值的数据系列，右击“数据系列格式”，次坐标轴）

案例分析EXCEL电子表格处理软共57页，您现在浏览的是第25页!对称条形图表

案例分析：用四大城市的实际GDP与预测GDP建立一个对称条形图表方法：首先建立一个条形图表，设置坐标轴刻度，选中Min为-Max，数字格式为自定义的“#，##0，-#，##0”选中要反转的数据系列，选中次坐标轴。在图表的上方出现次坐标轴同样的方法设置次坐标轴的最小值和数字格式，然后将数值次序反转即可EXCEL电子表格处理软共57页，您现在浏览的是第26页!饼图EXCEL电子表格处理软共57页，您现在浏览的是第27页!强调差异的方法是将各区块的颜色由浅到深配色，大块浅色小块深色，色彩感会显得平衡饼图又称为100%图，所以在饼图上不应出现负数，也应避免百分比总和大于100％的情况建议不使用图例说明，直接将数据项、百分比或数值标识在各饼图上饼图上的数字，最好使用Arial，较之宋体字更醒目。最好去掉饼块的线条边框EXCEL电子表格处理软共57页，您现在浏览的是第28页!复合饼图EXCEL电子表格处理软共57页，您现在浏览的是第29页!案例分析：构建一个饼图，要求绘图区显示5个城市的GDP所占比例，第二个绘图区显示剩下城市的GDP的所占比例自定义复合饼图EXCEL电子表格处理软共57页，您现在浏览的是第30页!数字格式的设置快速输入大写的人民币值 方法：“格式”“单元格”“数字”“特殊”快速输入大量负数 方法：选中单元格，“格式”“单元格”“数字” “自定义”“类型（-0）”快速输入有部分重复的数据（如学号） 方法：选中单元格，“格式”“单元格”“数字” “自定义”。例：”200918”@形式EXCEL电子表格处理软共57页，您现在浏览的是第31页!数据输入有效性设置若数据输入不符合，显示警告信息设置鼠标移动时显示提示信息让单元格提供下拉列表项进行选择输入EXCEL电子表格处理软共57页，您现在浏览的是第32页!单元格的引用单元格运算

算术运算、文本运算（&）、引用运算（，：！）引用运算：区域运算符，如=Sum(A1:B3)，共6个单元格，联合运算符，如=Sum(A1:B3,C1:D2)，共10个空格交叉运算符，如=Sum(A1:D2C1:E3)，共4个！三维引用运算符，如=Sheet1!B2相对引用

以当前单元格的位置为基准，在复制公式时，当前单元格改变时，单元格中的地址也随之发生变化 例：A1绝对引用：例：$A$1混合引用：例：$A1，A$1

案例：制作一张乘法口诀表EXCEL电子表格处理软共57页，您现在浏览的是第33页!教学知识点求和相关函数(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常用函数案例EXCEL电子表格处理软共57页，您现在浏览的是第34页!文本函数Mid：从文本指定的位置起返回指定长度的文本 语法：=Mid(text,start_num,num_chars)

说明：text：指定文本start_num：起始位置Num_chars：返回字符的个数EXCEL电子表格处理软共57页，您现在浏览的是第35页!If函数与条件格式 条件函数语法:=If(Logical_Test,value_True,value_False)

注意：条件函数与条件格式的区别条件函数的嵌套

EXCEL电子表格处理软共57页，您现在浏览的是第36页!Rank：返回一个数值在一批数中的位置语法：=Rank（number，range，order） 说明：Number：参加排序的数值Range：排序的范围Order：0为升序，非0为降序统计函数还有CountBlank、Max和Min等常用函数EXCEL电子表格处理软共57页，您现在浏览的是第37页!LookUp：根据条件查询，分为向量型和数组型数组型：在数组的行或列查找指定的值，返回数组最后一行或最后一列内同一位置的值 语法：=look_up(lookup_value,array) 【案例】根据员工的编号查询销售情况EXCEL电子表格处理软共57页，您现在浏览的是第38页!VLookup：在表格或数组的首列查找指定的值，返回表格当前行中其他列的值语法：=VLookup(value,array,col_index,True/False)说明：Value：表示要查找的值Array：表示查找的数据表Col_index：返回数据表第几列值True/False：表示返回近似值还是精确值EXCEL电子表格处理软共57页，您现在浏览的是第39页!

IPMT：用于固定利率及等额分期付款方式，返回贷款在某一给定期限内的利息金额

语法：=IPMT(rate，per,nper,pv,fv,type)说明：per用于计算利息数额的期数，其值在1-nper之间

【案例】某公司向银行贷款5万元，期限是5年，年利率为6%，请计算该公司每年的偿还金额和每年偿还的利息金额EXCEL电子表格处理软共57页，您现在浏览的是第40页!

PV：用于计算某项投资所需的金额

语法：=FV(rate,nper,pmt,pv,type)rate：贷款利率nper：总投资期pmt：各期应支付的金额fv：未来值type：付款时间是在期初（1）还是在期末（0）EXCEL电子表格处理软共57页，您现在浏览的是第41页!日期时间函数Date：计算某一特定日期的系列编号 语法：=Date(year,month,day) 说明：year：指定的年份month：月份day：月份中的第几天注意：若输入的月份大于12，将从指定年份一月份开始往上累加，如date(2008,14,2)，返回2009-2-2若输入的天数大于该月的最大天数，则从指定月数的天往上累加，如date(2008,1,35)，返回2008-2-4EXCEL电子表格处理软共57页，您现在浏览的是第42页!日期时间函数TODAY函数：返回系统当前的日期 语法：=TODay()EXCEL电子表格处理软共57页，您现在浏览的是第43页!数据库函数Dcount：返回数据库中满足指定条件的记录字段中包含数值单元格的个数DGet：用于从列表或数据库列中提取符合指定条件的单个值DAverage：计算列表或数据库的列中满足指定条件的数值的平均值DMax：返回列表或数据库的列中满足指定条件的最大值DSum：返回列表或数据库的列中满足指定条件的数字之和 EXCEL电子表格处理软共57页，您现在浏览的是第44页!数据表概念字段：数据表中的列称为字段，列标题称为字段名记录：数据表中的每一行称为一条记录数据表的几点说明一个工作表一般只创建一个数据表同一列的数据类型相同避免使用空行或空列记录单的使用EXCEL电子表格处理软共57页，您现在浏览的是第45页!高级筛选说明：高级筛选首先要构造一个条件区域，注意条件区域不能与数据表连接，必须空出一行或一列条件区域构造的规则为：同一列中的条件是“或”关系，同一行的条件是“与”关系EXCEL电子表格处理软共57页，您现在浏览的是第46页!

案例2：对每个类别的不同品牌商品的销售金额与利润金额进行汇总 分析：该题是按类别进行汇总还是按品牌进行汇总思路：先对类别进行分类汇总，然后在此基础上再对品牌进行分类汇总排序：以类别为主关键字，品牌为此关键字进行排序

EXCEL电子表格处理软共57页，您现在浏览的是第47页!第四部分图表操作教学知识点图表概述图表建立不同类型图表的建立方法EXCEL电子表格处理软共57页，您现在浏览的是第48页!图表的建立一般用柱形图或条形图比较数据的多少关系；用折线图反映数据间的趋势关系；用饼图表现数据间的比例分配关系条形图的数据要从大到小排序，最大的在最上面

柱型图数据要从小到大排序，最小的在最左边在条形图或柱型图中，同一种数据系列，应使用同一颜色图表的标题尽量说出你的观点在图表中尽量少使用3D效果图表美观，布局合理，注释文字恰当EXCEL电子表格处理软共57页，您现在浏览的是第49页!图表的编辑（美化图表）案例介绍图表的标题标题的旋转标题的链接让数据源显示在图表中图表的转换系列与分类的交换EXCEL电子表格处理软共57页，您现在浏览的是第50页!在同一个图表中使用两种图表类型

在一个图表中使用两种或者以上的