EXCEL高级应用课件.ppt

1、数据处理与分析平台数据处理与分析平台由浅入深 循序渐进第第1章章 导言导言5 5个层次个层次新手：新手：基本操作方法和常用功能：输入数据、查找替基本操作方法和常用功能：输入数据、查找替换、单元格格式、排序、汇总、筛选、保存等换、单元格格式、排序、汇总、筛选、保存等初级用户：初级用户：建立表格、图表化建立表格、图表化中级用户：中级用户：理解并熟练各个菜单命令、熟练使用数据理解并熟练各个菜单命令、熟练使用数据透视表、掌握透视表、掌握2020个函数（含个函数（含SUMSUM、IF IF、VLOOKUPVLOOKUP、INDEXINDEX、MATCHMATCH、OFFSETOFFSET、TEXTTEX

2、T）与函数的嵌套、宏；）与函数的嵌套、宏；高级用户：高级用户：熟练运用数组公式、使用熟练运用数组公式、使用VBAVBA编写不太复杂编写不太复杂的自定义函数或过程；的自定义函数或过程；专家：专家：高超的技术并拥有丰富的行业知识和经验，属高超的技术并拥有丰富的行业知识和经验，属于于EXCELHOMEEXCELHOME网站版主或高级会员网站版主或高级会员你属于哪一层次你属于哪一层次?目前目前EXCELEXCEL使用状况使用状况使用了使用了20%20%，常用的只有，常用的只有5%5%，原因：，原因：u根本不知道还有其他功能根本不知道还有其他功能u知道功能但不知道如何使用知道功能但不知道如何使用u暂时使

3、用不上暂时使用不上,不去了解不去了解为什么学习为什么学习:信息时代，数据量大，你是不是每天还在编信息时代，数据量大，你是不是每天还在编制复杂的公式，还在一遍又一遍的重复着手制复杂的公式，还在一遍又一遍的重复着手工输入，生怕有一个数据弄错？累吧？烦吧工输入，生怕有一个数据弄错？累吧？烦吧？想解脱吗？想解脱吗？Excel强大的数据处理功能已经征服了每一强大的数据处理功能已经征服了每一个个Excel使用者使用者人力资源管理人力资源管理_应用实例应用实例 动态了解公司员工的流入和流出变化情况；动态了解公司员工的流入和流出变化情况；分析员工今年薪酬变化，便于为明年的薪酬控制提供依据分析员工今年薪酬变化，

4、便于为明年的薪酬控制提供依据 每天、每月都要计算员工的考勤，计算员工的工资和奖金每天、每月都要计算员工的考勤，计算员工的工资和奖金，制作工资条，把工资准确无误地发放到每个员工账户，制作工资条，把工资准确无误地发放到每个员工账户，并及时通知每个员工；并及时通知每个员工；每年都要把全公司上百人甚至上千人的工资进行汇总，制每年都要把全公司上百人甚至上千人的工资进行汇总，制作五险一金汇总表，制作个税代扣代缴表作五险一金汇总表，制作个税代扣代缴表 员工的生日、合同、退休日期快要到了，如何才能提前提员工的生日、合同、退休日期快要到了，如何才能提前提醒，以免到时候手忙脚乱；新员工的试用期快要到了，如醒，以免

5、到时候手忙脚乱；新员工的试用期快要到了，如何及时提醒聘用者签订正式劳动合同何及时提醒聘用者签订正式劳动合同 如何评价每个业务人员的销售业绩，并根据业绩计算薪酬如何评价每个业务人员的销售业绩，并根据业绩计算薪酬主讲：资深EXCEL实战专家韩小良 1980元/人/两天 金融财务建模金融财务建模_应用实例应用实例 投资组合收益率和方差计算及其投资组合收益率和方差计算及其VBAVBA实现实现投资组合有效边界模型及其投资组合有效边界模型及其VBAVBA实现实现投资组合风险优化决策模型及其投资组合风险优化决策模型及其VBAVBA实现实现投资组合风险价值模型及其投资组合风险价值模型及其VBAVBA实现实现资

6、本资产定价模型的建立及其资本资产定价模型的建立及其VBAVBA实现实现Black-ScholesBlack-Scholes期权定价模型及其期权定价模型及其VBAVBA实现实现二叉树（二项式）期权定价模型及其二叉树（二项式）期权定价模型及其VBAVBA实现实现期货套期保值计算的期货套期保值计算的VBAVBA实现实现投资项目决策与理财模型的建立及其投资项目决策与理财模型的建立及其VBAVBA实现实现参考书:金融财务建模与计算基于VBA与MATLAB实现作者：朱顺泉 编著、出 版 社：电子工业出版其他应用其他应用高效数据处理分析高效数据处理分析高效财务管理高效财务管理企业管理中的高效运用企业管理中的

7、高效运用 高级金融建模高级金融建模 学习方法学习方法1 1循序渐进循序渐进2 2善用资源善用资源,学以致用学以致用u 通过好书、帮助、网络、通过好书、帮助、网络、BBSBBS论坛论坛 club.excelhome/thread-117862-1-1.htmlclub.excelhome/thread-117862-1-1.htmlu博大精深博大精深,不必深究不必深究,但要了解但要了解3 3多阅读多实践多阅读多实践u实践、实践、再实践实践、实践、再实践u有问题，要独立解决、思考，提高自己能力有问题，要独立解决、思考，提高自己能力u归纳、总结、积累归纳、总结、积累以以EXCELEXCEL功底去学其

8、他同类软件，学习成本会非常低功底去学其他同类软件，学习成本会非常低 主要内容主要内容基本功能基本功能特殊技巧特殊技巧函数与公式及其应用函数与公式及其应用数据分析数据分析图表图表VBAVBAExcel2019Excel2019功能改进功能改进 针对针对ExcelExcel存在的局限性进行改进，使其能够创建现存在的局限性进行改进，使其能够创建现代风格的文档。代风格的文档。使格式化文档更简便快捷使格式化文档更简便快捷 提供专门设计的可利用的且具有独创性的内容提供专门设计的可利用的且具有独创性的内容 容易看到工作成果，就像已经打印出来的一样容易看到工作成果，就像已经打印出来的一样 更容易维护电子表格和

9、进行格式更新更容易维护电子表格和进行格式更新 满足一些长期从事与打印相关顾客的需求满足一些长期从事与打印相关顾客的需求 提供一些美观的文档示例提供一些美观的文档示例 更容易移动内容更容易移动内容(例如，图表例如，图表)到其它到其它OfficeOffice应用程序应用程序(例如，例如，PowerPoint)PowerPoint)在在WordWord，PowerPointPowerPoint和和ExcelExcel中的所有操作方法都是中的所有操作方法都是一致的，因此，用户能够将某个应用程序的方法应一致的，因此，用户能够将某个应用程序的方法应用到另一个应用程序中用到另一个应用程序中 主要特点主要特点

10、增加在工作薄中可显示的颜色数从(原先的)256色到(现在的)43亿(32位色)格式化的“现场预览”极大的改进了图表,专业的“图表”样式改进了单元格样式特点，添加了条件格式到列表，数据透视表和图表一个新的视图页面布局视图，增加了普遍需要的、与打印相关的特点，单击即可输入页眉和页脚“文档主题”(颜色，字体和效果变化能在Office应用程序间共享)更新了Office界面外观(绘图工具条)和艺术字 25个美观且具有独创性的实用模板非常酷的状态栏和精美图表非常酷的状态栏和精美图表 Excel2019Excel2019质的突破：质的突破：1.1.灵巧变化的状态栏灵巧变化的状态栏状态栏缩放控制：状态栏缩放控

11、制：增加了一个不需弹出窗口的控制滑块来调整增加了一个不需弹出窗口的控制滑块来调整文件的缩放比例，当调整控制滑块时文件的缩放比例，当调整控制滑块时,文件同时文件同时改变显示比例。也可以使用改变显示比例。也可以使用“+”“+”和和“-”-”按钮来按钮来放大或缩小显示比例放大或缩小显示比例,每点击一次调节每点击一次调节10%10%。多样化的计算状态栏确多样化的计算状态栏确 u 在之前的在之前的ExcelExcel版本中，当你选中了数值数据时，可以在版本中，当你选中了数值数据时，可以在状态栏看到这些数据的小计状态栏看到这些数据的小计 求和，计数，平均值等等求和，计数，平均值等等，可以选择，可以选择6

12、6种不同的小计方式，但一次只能看到一种种不同的小计方式，但一次只能看到一种。uExcel 2019Excel 2019中可以中可以把几个或者全部的把几个或者全部的小计方式显示在状态小计方式显示在状态栏，求和、最大值、最小值、计数栏，求和、最大值、最小值、计数,计数值计数值,平均值的全平均值的全部显示或者显示其任意组合。部显示或者显示其任意组合。插入工作表按钮插入工作表按钮 只要单击这个按钮就会在工作簿中新增一个工作表，这一只要单击这个按钮就会在工作簿中新增一个工作表，这一点比较快捷点比较快捷 。2.2.几个图表几个图表 精髓：填充柄、单元格引用精髓：填充柄、单元格引用第第2章章 基本功能基本功

13、能一、一、ExcelExcel基本操作基本操作 工作簿属性 工作表属性 单元格属性 输入数据技巧 页面设置与打印 工作表编辑、格式 条件格式 选择性粘贴 导入与导出数据 数据有效性的应用 排序、筛选、分类汇总、数据透视表1.工作簿属性 工作表缺省数量(3)工作表缺省用户名(sheet1、sheet2、sheet3）使用“Office按钮”下的“准备/属性”菜单设置文档属性 重点掌握其中的、主题、关键词、作者 标记为最终状态(只读方式,不可修改)密码：方法1：另存为对话框设置方法2：准备/加密文档”菜单设置，若取消 进入设置对话框，删去密码即可2.工作表属性 工作表列：A,B,XFD(16384

14、=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）系统提供的序列数据

15、（3）用户自定义序列数据（4）记忆式输入法（字符型，快捷菜单的“选择列表”（5）多个单元格输入相同内容（不连续也可）Ctrl+Enter（6）日期输入：输入“1月1日”，用右键等。（7）同时填充多个工作表 例例:快速用快速用“0”0”填充所有空白单元格填充所有空白单元格选择区域选择区域开始开始/编辑编辑/查找和选择查找和选择/定位定位条件条件选中选中“空值空值”输入输入”0”,”0”,按按Ctrl+EnterCtrl+Enter快速缩放数值快速缩放数值目标：将目标：将1 1个大数个大数变成万、千等单变成万、千等单位表示的数值位表示的数值方法：通过自定方法：通过自定义格式实现。义格式实现。B B

16、列：列：u公式：公式：=A2=A2u格式使用格式使用C C列定列定义义6.6.数据类型数据类型计算日期间隔：日之差：日期直接相减月之差：DATEDIF(A1,A2,M)年之差：DATEDIF(A1,A2,y)文本型数字转换为数据型数字文本型数字转换为数据型数字点击点击 智能标记智能标记,选择选择“转换为数字转换为数字”6 6个公式：个公式：u=A1=A1*1 1u=A1/1=A1/1u=A1+0=A1+0u=A1-0=A1-0u=-A1=-A1 减负运算减负运算 （第（第1 1个个-是减法，第是减法，第2 2个个-是负数）是负数）例：例：=SUMPRODUCT（-(LEFT（A2：A10）=“

17、陈”)统计姓陈的员工数u=VALUE=VALUE（A1A1）逻辑型转换为数据型数字逻辑型转换为数据型数字四则运算四则运算uTRUE=1TRUE=1 FALSE=0FALSE=0u例：例：=TRUE+1=TRUE+1等于等于2 2，=FALSE-1=FALSE-1等于等于-1-1逻辑判断逻辑判断u0=FALSE0=FALSE 非零非零=TRUE=TRUE6 6个公式：个公式：u=A1=A1*1 1 u=A1/1=A1/1u=A1+0=A1+0u=A1-0=A1-0u=-A1=-A1 减负、减负、=N=N（）（）（N N函数）函数）其他功能：其他功能：数据有效性数据有效性条件格式条件格式选择性粘贴

18、选择性粘贴排序、筛选、分类汇总、数据透视表排序、筛选、分类汇总、数据透视表二、二、ExcelExcel高级技巧高级技巧 保护工作簿和工作表 共享工作簿和合并工作簿 公式（相对引用与绝对引用）函数、公式审核 窗体控件的应用 VBA1.保护工作簿和工作表 保护工作簿 选择“审阅”选项卡“更改”组的“保护工作簿”项选择“保护结构和窗口”。勾选“结构”或“窗口”复选框保护结构:不能插入、删除工作表、更改工作表名称等保护窗口:保留窗口的大小及位置等 保护工作表:保护工作表中数据不被任意修改 锁定锁定+保护工作表保护工作表：保护所有被锁定的单元格。选择“审阅”选项卡的“更改”组的“保护工作表”项，在对话框

19、里输入密码 在“保护工作表”对话框勾选所需的保护内容 保护工作表命令只对本工作表起作用 只允许用户编辑指定单元格区域 取消锁定取消锁定+保护工作表保护工作表 选择指定单元格，取消单元格的保护锁定 此时只有可以被编辑，其它区域都被锁住了 隐藏公式 隐藏隐藏+保护工作表保护工作表2.共享工作簿共享工作簿：使用“审阅”选项卡的“更改”组选择“共享工作簿”，打开其对话框，勾选“允许多用户”复选框。共享工作簿+保护：以追踪修订方式共享：选择“审阅”选项卡的“更改”组的“保护共享工作簿”项，打开“保护共享工作簿”对话框。勾选“以追踪修订方式共享”选项，输入密码突出显示修订:当数据被修改时,像批注一样标示出

20、,格式变了不标示 主要解决问题：工作表工作簿关主要解决问题：工作表工作簿关联、引用数据区域联、引用数据区域第第3 3章章 函数与公式基础函数与公式基础函数与公式学习方法函数与公式学习方法最有魅力的功能之一最有魅力的功能之一初级阶段：初级阶段：常用函数，如何填写参数；遇到常用函数，如何填写参数；遇到了了if if函数，再遇到函数，再遇到VLOOKUPVLOOKUP函数（难：需要函数（难：需要空间感、理解数据在不同方位的定位、查找空间感、理解数据在不同方位的定位、查找和返回的过程）和返回的过程）中级阶段：中级阶段：单个函数功能是有限的，多个函单个函数功能是有限的，多个函数的嵌套与组合才能完成比较复

21、杂的运算数的嵌套与组合才能完成比较复杂的运算高级阶段：高级阶段：数组公式和多维引用数组公式和多维引用公式功能公式功能计算计算建立数据之间的关联建立数据之间的关联u单元格数据直接无关系单元格数据直接无关系u各工作簿之间无关系各工作簿之间无关系u各工作表之间无关系各工作表之间无关系l通过公式的逻辑关系，把它们关联起来通过公式的逻辑关系，把它们关联起来l自动重算自动重算l原始数据的改变可以使用同一个计算模型原始数据的改变可以使用同一个计算模型两个重要思路两个重要思路工作表、工作簿的逻辑关联工作表、工作簿的逻辑关联公式建立公式建立EXCEL由行列数据构成的，因此获取所需的由行列数据构成的，因此获取所需

22、的行列区域是要解决主要问题行列区域是要解决主要问题数组、引用函数（数组、引用函数（OFFSET、ROW、COLUMN、INDEX、MATCH等）等）数组数组_用行数(高)和列数(宽)确定的数据矩形间隔行；间隔列，数组常量：1,2、15,18水平数组(1行5列)u1,2,3,4,5 、COLUMN（A：E）垂直数组(5行1列)u1；2；3；4；5、ROW（1：5）单元素数组u1、row（1：1）、column（A：A）=SMALL(IF(A1:B40,A1:B4),1,2,3)操作功能键：CTRL+SHIFT+ENTER(完成的是多重计算完成的是多重计算)重新计算公式的时间和方式重新计算公式的时

23、间和方式自动重新计算（默认的设置）自动重新计算（默认的设置）u只有在公式所依赖的单元格发生更改只有在公式所依赖的单元格发生更改u第一次打开工作簿以及编辑工作簿时第一次打开工作簿以及编辑工作簿时“Excel 选项”的“公式”类别的“计算选项”部分的“工作簿计算”下，单击“自动”除数据表外，自动重算除数据表外，自动重算u若要在每次更改值、公式或名称时重新计算若要在每次更改值、公式或名称时重新计算除数据表之外除数据表之外所有相关的公式所有相关的公式手动计算手动计算u若要关闭自动重新计算若要关闭自动重新计算单击“手动”时，Excel 将自动选中“保存工作簿前重新计算”复选框。如果保存工作簿需要很长时间

24、，那么清除“保存工作簿前重新计算”可缩短保存时间。函数工具与技巧函数工具与技巧公式复制公式复制u拖曳填充柄拖曳填充柄u双击填充柄（向下填充到邻列第双击填充柄（向下填充到邻列第1 1个空单元格上方）个空单元格上方）u选择性粘贴公式选择性粘贴公式函数工具提示函数工具提示 （选项设置）（选项设置）判断参数是否可以省略（带方括号的参数）判断参数是否可以省略（带方括号的参数）逐步看计算结果逐步看计算结果uF9:F9:当选中当选中单元格地址或函数时，在编辑栏显示部分计算结单元格地址或函数时，在编辑栏显示部分计算结果果u公式求值公式求值使用监视窗口使用监视窗口保护保护/隐藏工作表中的公式隐藏工作表中的公式函

25、数分类函数分类内置函数内置函数DateDifDateDif（）（）扩展函数扩展函数通过加载宏通过加载宏自定义函数自定义函数例：隐藏内置函数例：隐藏内置函数u=NUMBERSTRING(1234567890,1)=NUMBERSTRING(1234567890,1)l 结果：一十二亿三千四百五十六万七千八百九十 u=NUMBERSTRING(1234567890,2)=NUMBERSTRING(1234567890,2)l 结果：壹拾贰亿叁仟肆佰伍拾陆万柒仟捌佰玖拾 u=NUMBERSTRING(1234567890,3)=NUMBERSTRING(1234567890,3)l 结果：一二三四五

26、六七八九 u=DATESTRING(2019-8-8)=DATESTRING(2019-8-8)l 08年08月08日 uDATEDIFDATEDIF（）（）公式分类公式分类普通公式普通公式数组公式数组公式命名公式命名公式u=A1=A1：A8A8u=AVRAGER=AVRAGER（DATADATA）公式限制公式限制公式内容长度不能超过公式内容长度不能超过10241024个字符（个字符（20192019）公式中函数嵌套不能超过公式中函数嵌套不能超过7 7层（层（20192019）u将前将前6 6层定义名称，然后引用层定义名称，然后引用公式中函数参数不能超过公式中函数参数不能超过3030u使用括号

27、括起多个参数，从而变成使用括号括起多个参数，从而变成1 1个参数个参数数字计算精度为数字计算精度为1515位位u单引号单引号u设置为文本型设置为文本型名称管理器名称管理器 查看已有的名称查看已有的名称 u查看名称的引用范围（查看名称的引用范围（“Refers to”Refers to”控件）控件）u适用范围（适用范围（“Scope”Scope”栏）栏）u结果值（结果值（“Value”Value”栏栏注：错误结果值也会显示出来）注：错误结果值也会显示出来）u确认该名称是否已在表格中使用（确认该名称是否已在表格中使用（“In Use”In Use”栏）栏）创建名称创建名称 编辑已有的名称编辑已有的

28、名称 u名称可以重命名，不必再为了改名字而重新去定义一个名称名称可以重命名，不必再为了改名字而重新去定义一个名称 u可以很快地在编辑名称对话框中修改名称的适用范围可以很快地在编辑名称对话框中修改名称的适用范围 快速删除名称快速删除名称 u一次性选择和删除多个名称一次性选择和删除多个名称u名称排序名称排序u单击栏标题可以对名称进行排序单击栏标题可以对名称进行排序u 调整名称对话框的大小调整名称对话框的大小u根据需要调整根据需要调整refers-to boxrefers-to box（引用范围文本框）的宽度。这样，名称（引用范围文本框）的宽度。这样，名称的可见程度仅仅取决于桌面窗口的大小的可见程度

29、仅仅取决于桌面窗口的大小使用名称使用名称(6(6个原因个原因)增强公式可读性增强公式可读性u=单价单价*数量数量代替公式重复出现部分代替公式重复出现部分u公式多次出现相同函数，使用名称代替，简洁公式多次出现相同函数，使用名称代替，简洁使用常量名称代替单元格区域引用使用常量名称代替单元格区域引用u使用：使用：=VLOOKUP=VLOOKUP（A1,A1,等级等级,2,1,2,1）作为条件格式或数据有效性序列跨表引用作为条件格式或数据有效性序列跨表引用u将此列数据命名为将此列数据命名为x,x,“来源来源”输入：输入：=x=x 宏表宏表4.04.0函数在工作表中必须通过名称调用函数在工作表中必须通过

30、名称调用20192019版本的函数调用超过版本的函数调用超过7 7层层,使用名称解决更使用名称解决更多层多层例例:在公式书写时，名称是一个很实用的工具在公式书写时，名称是一个很实用的工具相对引用单元格区域，在写公式的时候使用相对引用单元格区域，在写公式的时候使用名称不易出错，而且方便记忆（例如，用名称不易出错，而且方便记忆（例如，用“Tax_RateTax_Rate（税率）（税率）”而不用而不用“G36”G36”）作用范围作用范围u工作簿级名称（全局）工作簿级名称（全局）l含工作簿名称含工作簿名称u工作表级名称（局部）工作表级名称（局部）l只含工作表名称只含工作表名称筛选名称筛选名称快速显示名

31、称子集快速显示名称子集（使用（使用名称管理器的名称管理器的“筛选筛选”下拉列表）下拉列表）各选择项含义：各选择项含义：名称扩展到工作表范围：只显示 适用范围为工作表的名称 名称扩展到工作簿范围：只显示工作簿范围内全局适用的名称 有错误的名称：,只显示值包含错误（如#REF、#VALUE 或#NAME）的那些名称 没有错误的名称：只显示值不包含错误的那些名称 已定义名称：只显示由您或 Excel 定义的名称，如打印区域 表名称：只显示表名称。#DIV/0DIV/0零作除数零作除数#NAME?NAME?在公式中使用了不能识别的名称在公式中使用了不能识别的名称 删除了公式中使用的名称，或者使用了不存

32、在的名称。删除了公式中使用的名称，或者使用了不存在的名称。函数名的拼写错误函数名的拼写错误#VALUEVALUE！使用了不正确的参数或运算符使用了不正确的参数或运算符 在需要数字或逻辑值时输入了文本在需要数字或逻辑值时输入了文本#REFREF！引用了无效的单元格地址引用了无效的单元格地址 删除了由其它公式引用的单元格删除了由其它公式引用的单元格 将移动单元格粘贴到由其它公式引用的单元格中。将移动单元格粘贴到由其它公式引用的单元格中。#NULLNULL！指定了两个并不相交的区域，故无效指定了两个并不相交的区域，故无效 使用了不正确的区域运算符或不正确的单元格引用。使用了不正确的区域运算符或不正确

33、的单元格引用。#N/AN/A 当在函数或公式中引用了无法使用的数值当在函数或公式中引用了无法使用的数值 内部函数或自定义工作表函数中缺少一个或多个参数。内部函数或自定义工作表函数中缺少一个或多个参数。使用的自定义工作表函数不存在。使用的自定义工作表函数不存在。VLOOKUPVLOOKUP（）函数中的查找值函数中的查找值lookup_valuelookup_value、FALSE/TRUEFALSE/TRUE参数参数指定了不正确的值域。指定了不正确的值域。#NUMNUM！数字类型不正确数字类型不正确 在需要数字参数的函数中使用了不能接受的参数。在需要数字参数的函数中使用了不能接受的参数。由公式产

34、生的数字太大或太小：在由公式产生的数字太大或太小：在 -10 -10307307和和1010307307之间之间#！输入到单元格中的数值太长，在单元格中显示不下；输入到单元格中的数值太长，在单元格中显示不下；单元格公式所产生的结果太长，单元格容纳不下；日期和时间单元格公式所产生的结果太长，单元格容纳不下；日期和时间产生了负值时将产生。产生了负值时将产生。运算符代替逻辑函数运算符代替逻辑函数星号星号*代替代替“与与”例例:=IF=IF（AND(A160,A160,A160)=IF(A160)*(A1=70),(A160),AND(B2=,C260),AND(B2=女女,C255),C255)等价

35、于：等价于：=AND(B2=AND(B2=男男,C260),C260)+AND(B2=AND(B2=女女,C255),C255)例例2 2:=SUMPRODUCT(B2:B11=SUMPRODUCT(B2:B11=江西江西,广东广东)*(C2:C11=(C2:C11=男男)*D2:D11)D2:D11)=SUMPRODUCT(B2:B11=SUMPRODUCT(B2:B11=江西江西)+(B2:B11=(B2:B11=广东广东“)“)*(C2:C11=(C2:C11=男男)*D2:D11)D2:D11)不能用不能用AND、OR代替代替*、+原因：数组公式需要执行多重计算，而原因：数组公式需要执

36、行多重计算，而AND、OR返回的是单值返回的是单值TRUE或或FALSE，不能形成数，不能形成数组公式多区域之间的一一对应关系。组公式多区域之间的一一对应关系。例：例：=SUM（AND(C3:C770,C3:C770)*(C3:C720)u=SUMPRODUCT(B2:B11=一班一班)*(C2:C1120)免去免去*1:(两个逻辑值数组*运算,直接转换数值型)u用用*:=SUMPRODUCT(B2:B11=一班一班)*C2:C1120)u=SUMPRODUCT(B2:B11=一班一班)*1,(C2:C1120)*1)第第4 4章章 引用与查找函数引用与查找函数Offset()Offset()

37、功能：通过给定偏移量得到新的引用区域。语法语法:OFFSET(reference，rows，cols，height，width)有5个参数：uReference基点或参照系，即引用区域左上角单元格；uRows偏移的行数。l行数正数：在基点的下方；负数：在基点的上方uCols偏移的列数。l列数正数：基点的右边；负数：在基点的左边。uHeight高度，即返回的引用区域的行数，必须为正数。uWidth宽度，即所要返回的引用区域的列数，必须为正数使用特点使用特点设置所需要的区域设置所需要的区域若结果为若结果为1个单元格个单元格,其值显示在公式所在单其值显示在公式所在单元格元格往往用在往往用在u单元格区

38、域单元格区域u求和、平均、最大最小的统计区域求和、平均、最大最小的统计区域u查找函数的范围参数查找函数的范围参数MATCHMATCH 函数函数功能功能:在单元格区域在单元格区域 中搜索指定项，然后返回该项中搜索指定项，然后返回该项在单元格区域中的相对位置。在单元格区域中的相对位置。MATCH(lookup_value,lookup_array,match_type)例如，如果单元格区域例如，如果单元格区域 A1:A3 A1:A3 包含值包含值 5 5、25 25 和和 3838，则以，则以下公式：下公式：=MATCH(25,A1:A3,0)=MATCH(25,A1:A3,0)会返回数字会返回数

39、字 2 2，因为值，因为值 25 25 是单元格区域中的第二项。是单元格区域中的第二项。match_typematch_type可选可选:-1:查找大于或等于 lookup_value 的最小值0:查找等于 lookup_value 的第一个值 1:(默认值)查找小于或等于 lookup_value 的最大值使用特点使用特点第第2个参数：个参数：1行或行或1列数组列数组是一个数是一个数,表示查找值的行号或列号表示查找值的行号或列号常常使用在常常使用在uIndex函数的行号或列号函数的行号或列号uOFFSET的参数（偏移量等）的参数（偏移量等）区别（解决同问题）区别（解决同问题）u=INDEX(

40、A1:D11,MATCH(F1,A1:A11,0),MATCH(G1,A1:D1,0)u=OFFSET(A1,MATCH(F1,A2:A11,0),MATCH(G1,B1:D1,0)INDEXINDEX(array,row_num,column_num)功能：返回单元格或数组中的数据或元素值，功能：返回单元格或数组中的数据或元素值，此元素由行号和列号的索引值来给定。Array单元格区域或数组常量。Row_num数组中某行的行号，函数从该行返回数值。如果省略 row_num，则必须有 column_num。Column_num数组中某列的列标，函数从该列返回数值。如果省略 column_num，

41、则必须有 row_num。使用特点使用特点给出选定区域的行号或列号，获取查找值给出选定区域的行号或列号，获取查找值u多行多列多行多列u单行单行u单列单列常使用在常使用在u指定行或列的查找指定行或列的查找ROW(reference)功能：返回引用的行号。功能：返回引用的行号。Reference需要得到其行号的单元格或单元格区域。COLUMN(reference)功能：返回引用的列号。功能：返回引用的列号。Reference需要得到其列号的单元格或单元格区域。使用特点：变化的特点：作为随行/列变化的变量=COLUMN（），（），=ROW（）（）=COLUMN（A1），），=ROW（A1）=COLU

42、MN（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，从中选择一

43、个数值或一项要执行的操作。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

44、)功能：功能：向量形式:在单行区域或单列区域（称为“向量”）中查找值，然后返回第二个单行区域或单列区域中相同位置的值。数组形式:在数组的第一行或第一列中查找指定的值，然后返回数组的最后一行或最后一列中相同位置的值使用特点使用特点VLOOKUP使用技巧使用技巧u1、2列互换列互换if(1,2,)u连接连接2个查找值个查找值 拼接拼接&“|”u部分含有部分含有通配符通配符“*”uOFFSET构造范围构造范围在条件格式、数据有效性、排序等应用在条件格式、数据有效性、排序等应用第第5 5章章 函数应用函数应用1 1条件格式的应用条件格式的应用（生日提醒、标记重复值、填充不（生日提醒、标记重复值、填充不

45、同色等）同色等）数据有效性的应用数据有效性的应用（限制录入数据范围、重复值、（限制录入数据范围、重复值、创建下拉列表、快捷输入数据等）创建下拉列表、快捷输入数据等）选择性粘贴选择性粘贴(四则运算、转置、复制格式、公式粘贴四则运算、转置、复制格式、公式粘贴为数值等）为数值等）排序应用排序应用(字母、笔画、字符数量、随机等）字母、笔画、字符数量、随机等）函数应用函数应用1 11.1.条件格式条件格式当单元格数据满足某种特定条件，自动显当单元格数据满足某种特定条件，自动显示指定的格式示指定的格式特点：特点：动态的动态的公式设置：公式设置：若对某列或某区域，多数情况只要对左上角单元格（相对引用）设置条

46、件，EXCEL会自动扩展到选区中=ABS(DATE(YEAR(TODAY(),MONTH($B=ABS(DATE(YEAR(TODAY(),MONTH($B2),DAY($B2)-TODAY()=72),DAY($B2)-TODAY()1=COUNTIF(A1:A$2,A1)1设置填充色设置填充色例例3 3：填充：填充国际象棋棋盘国际象棋棋盘u=MOD(ROW()+COLUMN(),2)=0=MOD(ROW()+COLUMN(),2)=0u=MOD(ROW()+COLUMN(),2)=1=MOD(ROW()+COLUMN(),2)=1奇偶行不同奇偶行不同u=MOD(ROW(),2)0=MOD(

47、ROW(),2)0动态的间隔底纹动态的间隔底纹u=MOD(SUBTOTAL(3,A$2:A2),2)=0=MOD(SUBTOTAL(3,A$2:A2),2)=0u=MOD(SUBTOTAL(3,A$2:A2),2)=1=MOD(SUBTOTAL(3,A$2:A2),2)=1例例4 4：比较不同区域数值：比较不同区域数值方法方法1 1：u 条件格式的条件格式的“只为包含以下内只为包含以下内容的单元格设置格式容的单元格设置格式”项输入公项输入公式：式：=A2=A2，选择，选择“不等于不等于方法方法2 2：（顺序不同）（顺序不同）u选择左侧区域选择左侧区域u条件格式的条件格式的“使用公式确定要设置格

48、使用公式确定要设置格式的单元格式的单元格”项输入公式项输入公式u=OR(EXACT(A2,B$2:B$11)=FALSE=OR(EXACT(A2,B$2:B$11)=FALSEu=NOT(OR(A2=B$2:B$11)=NOT(OR(A2=B$2:B$11)（同理）（同理）尤其体现在大区域数值的比较中特点:1.复制:通过“选择性粘贴”2.数据有效性的设置仅对手工录入有效，对复制粘贴输入不生效例1:输入提框方法:“数据有效性”对话框中的“输入信息”和“出错警告”选项卡，可以设置输入提示和出错提示信息。例2:标识出已录入数据中不符合录入范围的数据：先设置录入数据范围，再显示出“公式审核”工具栏，单

49、击“圈释无效数据”按钮。2.2.数据有效性的应用数据有效性的应用要求：按时序输入日期要求：按时序输入日期方法：方法：u选择区域，设置为日期格式选择区域，设置为日期格式u数据有效性：自定义，公式：数据有效性：自定义，公式：=N(A2)=N(A1)=N(A2)=N(A1)例例3 3：按日期顺序输入数据：按日期顺序输入数据例例4:4:为单元格设置下拉列表供录入数据时选择为单元格设置下拉列表供录入数据时选择,可以避免可以避免误输入数据误输入数据例例5 5：切换不同数据列（数据有效性）：切换不同数据列（数据有效性）根据根据A1A1的输入值的输入值1313，可，可以在以在A2A2获取由右侧获取由右侧3 3

50、列构列构成的相应下拉列表数据成的相应下拉列表数据方法：方法：u选择选择D1D1：D11D11，定义名称：，定义名称：List.1List.1u选择选择A2A2u设置数据有效性：序列、来设置数据有效性：序列、来源：源：=OFFSET=OFFSET（List.1,A1-1List.1,A1-1）单字段排序 多字段排序 按列/行排序 汉字笔画排序 按职务排序，顺序为“主任”“科长”“职员”“实习生”方法:先“自定义序列”排序时选择“自定义序列”3.3.排序的应用排序的应用例例1 1：按字符数量排序：按字符数量排序方法：方法：uC C列：列：C1C1输入输入“字数字数”uC2C2输入公式：输入公式：=