EXCEL高级应用(经典).ppt

EXCEL高级应用,数据处理与分析平台,由浅入深循序渐进,第1章导言,5个层次,新手：基本操作方法和常用功能：输入数据、查找替换、单元格格式、排序、汇总、筛选、保存等初级用户：建立表格、图表化中级用户：理解并熟练各个菜单命令、熟练使用数据透视表、掌握20个函数（含SUM、IF、VLOOKUP、INDEX、MATCH、OFFSET、TEXT）与函数的嵌套、宏；高级用户：熟练运用数组公式、使用VBA编写不太复杂的自定义函数或过程；专家：高超的技术并拥有丰富的行业知识和经验，属于EXCELHOME网站版主或高级会员,你属于哪一层次?,目前EXCEL使用状况,使用了20%，常用的只有5%，原因：根本不知道还有其他功能知道功能但不知道如何使用暂时使用不上,不去了解,为什么学习:,信息时代，数据量大，你是不是每天还在编制复杂的公式，还在一遍又一遍的重复着手工输入，生怕有一个数据弄错？累吧？烦吧？想解脱吗？Excel强大的数据处理功能已经征服了每一个Excel使用者,人力资源管理_应用实例,动态了解公司员工的流入和流出变化情况；分析员工今年薪酬变化，便于为明年的薪酬控制提供依据每天、每月都要计算员工的考勤，计算员工的工资和奖金，制作工资条，把工资准确无误地发放到每个员工账户，并及时通知每个员工；每年都要把全公司上百人甚至上千人的工资进行汇总，制作五险一金汇总表，制作个税代扣代缴表员工的生日、合同、退休日期快要到了，如何才能提前提醒，以免到时候手忙脚乱；新员工的试用期快要到了，如何及时提醒聘用者签订正式劳动合同如何评价每个业务人员的销售业绩，并根据业绩计算薪酬,主讲：资深EXCEL实战专家韩小良1980元/人/两天,金融财务建模_应用实例,投资组合收益率和方差计算及其VBA实现投资组合有效边界模型及其VBA实现投资组合风险优化决策模型及其VBA实现投资组合风险价值模型及其VBA实现资本资产定价模型的建立及其VBA实现Black-Scholes期权定价模型及其VBA实现二叉树（二项式）期权定价模型及其VBA实现期货套期保值计算的VBA实现投资项目决策与理财模型的建立及其VBA实现参考书:金融财务建模与计算基于VBA与MATLAB实现作者：朱顺泉编著、出版社：电子工业出版,其他应用,高效数据处理分析高效财务管理企业管理中的高效运用高级金融建模,学习方法,1循序渐进2善用资源,学以致用通过好书、帮助、网络、BBS论坛,主要内容,基本功能特殊技巧函数与公式及其应用数据分析图表VBA,Excel2007功能改进,针对Excel存在的局限性进行改进，使其能够创建现代风格的文档。使格式化文档更简便快捷提供专门设计的可利用的且具有独创性的内容容易看到工作成果，就像已经打印出来的一样更容易维护电子表格和进行格式更新满足一些长期从事与打印相关顾客的需求提供一些美观的文档示例更容易移动内容(例如，图表)到其它Office应用程序(例如，PowerPoint)在Word，PowerPoint和Excel中的所有操作方法都是一致的，因此，用户能够将某个应用程序的方法应用到另一个应用程序中,主要特点,增加在工作薄中可显示的颜色数从(原先的)256色到(现在的)43亿(32位色)格式化的“现场预览”极大的改进了图表,专业的“图表”样式改进了单元格样式特点，添加了条件格式到列表，数据透视表和图表一个新的视图页面布局视图，增加了普遍需要的、与打印相关的特点，单击即可输入页眉和页脚“文档主题”(颜色，字体和效果变化能在Office应用程序间共享)更新了Office界面外观(绘图工具条)和艺术字25个美观且具有独创性的实用模板,非常酷的状态栏和精美图表,Excel2007质的突破：1.灵巧变化的状态栏状态栏缩放控制：增加了一个不需弹出窗口的控制滑块来调整文件的缩放比例，当调整控制滑块时,文件同时改变显示比例。也可以使用“+”和“-”按钮来放大或缩小显示比例,每点击一次调节10%。,多样化的计算状态栏确在之前的Excel版本中，当你选中了数值数据时，可以在状态栏看到这些数据的小计求和，计数，平均值等等，可以选择6种不同的小计方式，但一次只能看到一种。Excel2007中可以把几个或者全部的小计方式显示在状态栏，求和、最大值、最小值、计数,计数值,平均值的全部显示或者显示其任意组合。插入工作表按钮只要单击这个按钮就会在工作簿中新增一个工作表，这一点比较快捷。,2.几个图表,精髓：填充柄、单元格引用,第2章基本功能,一、Excel基本操作工作簿属性工作表属性单元格属性输入数据技巧页面设置与打印工作表编辑、格式条件格式选择性粘贴导入与导出数据数据有效性的应用排序、筛选、分类汇总、数据透视表,1.工作簿属性工作表缺省数量(3)工作表缺省用户名(sheet1、sheet2、sheet3）使用“Office按钮”下的“准备/属性”菜单设置文档属性重点掌握其中的、主题、关键词、作者标记为最终状态(只读方式,不可修改)密码：方法1：另存为对话框设置方法2：准备/加密文档”菜单设置，若取消进入设置对话框，删去密码即可,2.工作表属性工作表列：A,B,XFD(16384=214)工作表行:1,2,1048576=220工作表多个独立单元格214220单元格地址：列标行标；区域地址：左上角单元格地址:右下角单元格地址相对引用：例：B6，A4，C5：F8。绝对引用：例：$B$6,$A$4,$C$5:$F$8。混合引用：例：B$6,A$4,C$5:F$8、$B6,$A4,$C5:$F8工作表格式化工作表编辑,3.单元格属性单元格所在行、列的高度和宽度选定单元格或区域单元格格式：数字格式、对齐字体、填充、边框（斜线表头）、4.导入与导出（文件类型）数据,5.输入数据技巧（1）自动填充（带文本与数字混合、Ctrl辅助、等比、等差序列）（2）系统提供的序列数据（3）用户自定义序列数据（4）记忆式输入法（字符型，快捷菜单的“选择列表”（5）多个单元格输入相同内容（不连续也可）Ctrl+Enter（6）日期输入：输入“1月1日”，用右键等。（7）同时填充多个工作表,例:快速用“0”填充所有空白单元格,选择区域开始/编辑/查找和选择/定位条件选中“空值”输入”0”,按Ctrl+Enter,快速缩放数值,目标：将1个大数变成万、千等单位表示的数值方法：通过自定义格式实现。B列：公式：=A2格式使用C列定义,6.数据类型,计算日期间隔：日之差：日期直接相减月之差：DATEDIF(A1,A2,M)年之差：DATEDIF(A1,A2,y),文本型数字转换为数据型数字,点击智能标记,选择“转换为数字”6个公式：=A1*1=A1/1=A1+0=A1-0=-A1减负运算（第1个-是减法，第2个-是负数）例：=SUMPRODUCT（-(LEFT（A2：A10）=“陈”)统计姓陈的员工数=VALUE（A1）,逻辑型转换为数据型数字,四则运算TRUE=1FALSE=0例：=TRUE+1等于2，=FALSE-1等于-1逻辑判断0=FALSE非零=TRUE6个公式：=A1*1=A1/1=A1+0=A1-0=-A1减负、=N（）（N函数）,其他功能：,数据有效性条件格式选择性粘贴排序、筛选、分类汇总、数据透视表,二、Excel高级技巧保护工作簿和工作表共享工作簿和合并工作簿公式（相对引用与绝对引用）函数、公式审核窗体控件的应用VBA,1.保护工作簿和工作表保护工作簿选择“审阅”选项卡“更改”组的“保护工作簿”项选择“保护结构和窗口”。勾选“结构”或“窗口”复选框保护结构:不能插入、删除工作表、更改工作表名称等保护窗口:保留窗口的大小及位置等,保护工作表:保护工作表中数据不被任意修改锁定+保护工作表：保护所有被锁定的单元格。选择“审阅”选项卡的“更改”组的“保护工作表”项，在对话框里输入密码在“保护工作表”对话框勾选所需的保护内容保护工作表命令只对本工作表起作用只允许用户编辑指定单元格区域取消锁定+保护工作表选择指定单元格，取消单元格的保护锁定此时只有可以被编辑，其它区域都被锁住了隐藏公式隐藏+保护工作表,2.共享工作簿共享工作簿：使用“审阅”选项卡的“更改”组选择“共享工作簿”，打开其对话框，勾选“允许多用户”复选框。共享工作簿+保护：以追踪修订方式共享：选择“审阅”选项卡的“更改”组的“保护共享工作簿”项，打开“保护共享工作簿”对话框。勾选“以追踪修订方式共享”选项，输入密码突出显示修订:当数据被修改时,像批注一样标示出,格式变了不标示,主要解决问题：工作表工作簿关联、引用数据区域,第3章函数与公式基础,函数与公式学习方法最有魅力的功能之一,初级阶段：常用函数，如何填写参数；遇到了if函数，再遇到VLOOKUP函数（难：需要空间感、理解数据在不同方位的定位、查找和返回的过程）中级阶段：单个函数功能是有限的，多个函数的嵌套与组合才能完成比较复杂的运算高级阶段：数组公式和多维引用,公式功能,计算建立数据之间的关联单元格数据直接无关系各工作簿之间无关系各工作表之间无关系通过公式的逻辑关系，把它们关联起来自动重算原始数据的改变可以使用同一个计算模型,两个重要思路,工作表、工作簿的逻辑关联公式建立EXCEL由行列数据构成的，因此获取所需的行列区域是要解决主要问题数组、引用函数（OFFSET、ROW、COLUMN、INDEX、MATCH等）,数组,_用行数(高)和列数(宽)确定的数据矩形间隔行；间隔列，数组常量：1,2、15,18水平数组(1行5列)1,2,3,4,5、COLUMN（A：E）垂直数组(5行1列)1；2；3；4；5、ROW（1：5）单元素数组1、row（1：1）、column（A：A）=SMALL(IF(A1:B40,A1:B4),1,2,3)操作功能键：CTRL+SHIFT+ENTER(完成的是多重计算),重新计算公式的时间和方式,自动重新计算（默认的设置）只有在公式所依赖的单元格发生更改第一次打开工作簿以及编辑工作簿时“Excel选项”的“公式”类别的“计算选项”部分的“工作簿计算”下，单击“自动”除数据表外，自动重算若要在每次更改值、公式或名称时重新计算除数据表之外所有相关的公式手动计算若要关闭自动重新计算单击“手动”时，Excel将自动选中“保存工作簿前重新计算”复选框。如果保存工作簿需要很长时间，那么清除“保存工作簿前重新计算”可缩短保存时间。,函数工具与技巧,公式复制拖曳填充柄双击填充柄（向下填充到邻列第1个空单元格上方）选择性粘贴公式函数工具提示（选项设置）判断参数是否可以省略（带方括号的参数）逐步看计算结果F9:当选中单元格地址或函数时，在编辑栏显示部分计算结果公式求值使用监视窗口保护/隐藏工作表中的公式,函数分类,内置函数DateDif（）扩展函数通过加载宏自定义函数例：隐藏内置函数=NUMBERSTRING(1234567890,1)结果：一十二亿三千四百五十六万七千八百九十=NUMBERSTRING(1234567890,2)结果：壹拾贰亿叁仟肆佰伍拾陆万柒仟捌佰玖拾=NUMBERSTRING(1234567890,3)结果：一二三四五六七八九=DATESTRING(2008-8-8)08年08月08日DATEDIF（）,公式分类,普通公式数组公式命名公式=A1：A8=AVRAGER（DATA）,公式限制,公式内容长度不能超过1024个字符（2003）公式中函数嵌套不能超过7层（2003）将前6层定义名称，然后引用公式中函数参数不能超过30使用括号括起多个参数，从而变成1个参数数字计算精度为15位单引号设置为文本型,名称管理器,查看已有的名称查看名称的引用范围（“Refersto”控件）适用范围（“Scope”栏）结果值（“Value”栏注：错误结果值也会显示出来）确认该名称是否已在表格中使用（“InUse”栏）创建名称编辑已有的名称名称可以重命名，不必再为了改名字而重新去定义一个名称可以很快地在编辑名称对话框中修改名称的适用范围快速删除名称一次性选择和删除多个名称名称排序单击栏标题可以对名称进行排序调整名称对话框的大小根据需要调整refers-tobox（引用范围文本框）的宽度。这样，名称的可见程度仅仅取决于桌面窗口的大小,使用名称(6个原因),增强公式可读性=单价*数量代替公式重复出现部分公式多次出现相同函数，使用名称代替，简洁使用常量名称代替单元格区域引用使用：=VLOOKUP（A1,等级,2,1）作为条件格式或数据有效性序列跨表引用将此列数据命名为x,“来源”输入：=x宏表4.0函数在工作表中必须通过名称调用2003版本的函数调用超过7层,使用名称解决更多层,例:,在公式书写时，名称是一个很实用的工具相对引用单元格区域，在写公式的时候使用名称不易出错，而且方便记忆（例如，用“Tax_Rate（税率）”而不用“G36”）作用范围工作簿级名称（全局）含工作簿名称工作表级名称（局部）只含工作表名称,筛选名称,快速显示名称子集（使用名称管理器的“筛选”下拉列表）各选择项含义：名称扩展到工作表范围：只显示适用范围为工作表的名称名称扩展到工作簿范围：只显示工作簿范围内全局适用的名称有错误的名称：,只显示值包含错误（如#REF、#VALUE或#NAME）的那些名称没有错误的名称：只显示值不包含错误的那些名称已定义名称：只显示由您或Excel定义的名称，如打印区域表名称：只显示表名称。,函数公式出错信息（7个）#DIV/0零作除数#NAME?在公式中使用了不能识别的名称删除了公式中使用的名称，或者使用了不存在的名称。函数名的拼写错误#VALUE！使用了不正确的参数或运算符在需要数字或逻辑值时输入了文本#REF！引用了无效的单元格地址删除了由其它公式引用的单元格将移动单元格粘贴到由其它公式引用的单元格中。#NULL！指定了两个并不相交的区域，故无效使用了不正确的区域运算符或不正确的单元格引用。,#N/A当在函数或公式中引用了无法使用的数值内部函数或自定义工作表函数中缺少一个或多个参数。使用的自定义工作表函数不存在。VLOOKUP（）函数中的查找值lookup_value、FALSE/TRUE参数指定了不正确的值域。#NUM！数字类型不正确在需要数字参数的函数中使用了不能接受的参数。由公式产生的数字太大或太小：在-10307和10307之间#！输入到单元格中的数值太长，在单元格中显示不下；单元格公式所产生的结果太长，单元格容纳不下；日期和时间产生了负值时将产生。,运算符代替逻辑函数,星号*代替“与”例:=IF（AND(A160,A160)*(A160),AND(B2=女,C255)等价于：=AND(B2=男,C260)+AND(B2=女,C255)例2:=SUMPRODUCT(B2:B11=江西,广东)*(C2:C11=男)*D2:D11)=SUMPRODUCT(B2:B11=江西)+(B2:B11=广东“)*(C2:C11=男)*D2:D11),不能用AND、OR代替*、+,原因：数组公式需要执行多重计算，而AND、OR返回的是单值TRUE或FALSE，不能形成数组公式多区域之间的一一对应关系。例：=SUM（AND(C3:C770,C3:C770)*(C3:C720)=SUMPRODUCT(B2:B11=一班)*(C2:C1120)免去*1:(两个逻辑值数组*运算,直接转换数值型)用*:=SUMPRODUCT(B2:B11=一班)*C2:C1120)=SUMPRODUCT(B2:B11=一班)*1,(C2:C1120)*1),第4章引用与查找函数,Offset(),功能：通过给定偏移量得到新的引用区域。语法:OFFSET(reference，rows，cols，height，width)有5个参数：Reference基点或参照系，即引用区域左上角单元格；Rows偏移的行数。行数正数：在基点的下方；负数：在基点的上方Cols偏移的列数。列数正数：基点的右边；负数：在基点的左边。Height高度，即返回的引用区域的行数，必须为正数。Width宽度，即所要返回的引用区域的列数，必须为正数,使用特点,设置所需要的区域若结果为1个单元格,其值显示在公式所在单元格往往用在单元格区域求和、平均、最大最小的统计区域查找函数的范围参数,MATCH函数,功能:在单元格区域中搜索指定项，然后返回该项在单元格区域中的相对位置。MATCH(lookup_value,lookup_array,match_type)例如，如果单元格区域A1:A3包含值5、25和38，则以下公式：=MATCH(25,A1:A3,0)会返回数字2，因为值25是单元格区域中的第二项。match_type可选:-1:查找大于或等于lookup_value的最小值0:查找等于lookup_value的第一个值1:(默认值)查找小于或等于lookup_value的最大值,使用特点,第2个参数：1行或1列数组是一个数,表示查找值的行号或列号常常使用在Index函数的行号或列号OFFSET的参数（偏移量等）区别（解决同问题）=INDEX(A1:D11,MATCH(F1,A1:A11,0),MATCH(G1,A1:D1,0)=OFFSET(A1,MATCH(F1,A2:A11,0),MATCH(G1,B1:D1,0),INDEX,INDEX(array,row_num,column_num)功能：返回单元格或数组中的数据或元素值，此元素由行号和列号的索引值来给定。Array单元格区域或数组常量。Row_num数组中某行的行号，函数从该行返回数值。如果省略row_num，则必须有column_num。Column_num数组中某列的列标，函数从该列返回数值。如果省略column_num，则必须有row_num。,使用特点,给出选定区域的行号或列号，获取查找值多行多列单行单列常使用在指定行或列的查找,ROW(reference)功能：返回引用的行号。Reference需要得到其行号的单元格或单元格区域。COLUMN(reference)功能：返回引用的列号。Reference需要得到其列号的单元格或单元格区域。使用特点：变化的特点：作为随行/列变化的变量=COLUMN（），=ROW（）=COLUMN（A1），=ROW（A1）=COLUMN（A：G），=ROW（4：10）,CHOOSE(index_num,value1,value2,.)功能：使用index_num返回数值参数列表中的数值。Index_num指定所选定的值参数。Index_num必须为1到254之间的数字，或者是包含数字1到254的公式或单元格引用。如果index_num为1，函数CHOOSE返回value1；如果为2，函数CHOOSE返回value2，以此类推。Value1,value2,.为1到254个数值参数，函数CHOOSE基于index_num，从中选择一个数值或一项要执行的操作。,LOOKUP,HLOOKUP(lookup_value,table_array,row_index_num,range_lookup)功能：在表格或数值数组的首行查找指定的数值，并在表格或数组中指定行的同一列中返回一个数值。VLOOKUP(lookup_value,table_array,col_index_num,range_lookup)功能：参数表示垂直方向LOOKUP(lookup_value,lookup_vector,result_vector)功能：向量形式:在单行区域或单列区域（称为“向量”）中查找值，然后返回第二个单行区域或单列区域中相同位置的值。数组形式:在数组的第一行或第一列中查找指定的值，然后返回数组的最后一行或最后一列中相同位置的值,使用特点,VLOOKUP使用技巧1、2列互换if(1,2,)连接2个查找值拼接&“|”部分含有通配符“*”OFFSET构造范围,在条件格式、数据有效性、排序等应用,第5章函数应用1,条件格式的应用（生日提醒、标记重复值、填充不同色等）数据有效性的应用（限制录入数据范围、重复值、创建下拉列表、快捷输入数据等）选择性粘贴(四则运算、转置、复制格式、公式粘贴为数值等）排序应用(字母、笔画、字符数量、随机等）,函数应用1,1.条件格式,当单元格数据满足某种特定条件，自动显示指定的格式特点：动态的公式设置：若对某列或某区域，多数情况只要对左上角单元格（相对引用）设置条件，EXCEL会自动扩展到选区中,=ABS(DATE(YEAR(TODAY(),MONTH($B2),DAY($B2)-TODAY()1设置填充色,例3：填充,国际象棋棋盘=MOD(ROW()+COLUMN(),2)=0=MOD(ROW()+COLUMN(),2)=1奇偶行不同=MOD(ROW(),2)0动态的间隔底纹=MOD(SUBTOTAL(3,A$