最新-EXCEL高级应用-PPT精品课件

《最新-EXCEL高级应用-PPT精品课件》由会员分享，可在线阅读，更多相关《最新-EXCEL高级应用-PPT精品课件（82页珍藏版）》请在金锄头文库上搜索。

1、EXCEL高高级应用用数据数据处理与分析平台理与分析平台由浅入深 循序渐进第第1章章导言言5个个层次次新手：新手：基本操作方法和常用功能：基本操作方法和常用功能：输入数据、入数据、查找替找替换、单元格格式、排序、元格格式、排序、汇总、筛选、保存等、保存等初初级用用户：建立表格、建立表格、图表化表化中中级用用户：理解并熟理解并熟练各个菜各个菜单命令、熟命令、熟练使用数据使用数据透透视表、掌握表、掌握20个函数（含个函数（含SUM、IF、VLOOKUP、INDEX、MATCH、OFFSET、TEXT）与函数的嵌）与函数的嵌套、宏；套、宏；高高级用用户：熟熟练运用数运用数组公式、使用公式、使用VBA

2、编写不太复写不太复杂的自定的自定义函数或函数或过程；程；专家：家：高超的技高超的技术并并拥有丰富的行有丰富的行业知知识和和经验，属，属于于EXCELHOME网站版主或高网站版主或高级会会员你属于哪一你属于哪一层次次?目前目前EXCEL使用状况使用状况使用了使用了20%，常用的只有，常用的只有5%，原因：，原因：u根本不知道根本不知道还有其他功能有其他功能u知道功能但不知道如何使用知道功能但不知道如何使用u暂时使用不上使用不上,不去了解不去了解为什么学什么学习:信息信息时代，数据量大，你是不是每天代，数据量大，你是不是每天还在在编制复制复杂的公式，的公式，还在一遍又一遍的重复着手在一遍又一遍的重

3、复着手工工输入，生怕有一个数据弄入，生怕有一个数据弄错？累吧？累吧？烦吧吧？想解脱？想解脱吗？Excel强大的数据大的数据处理功能已理功能已经征服了每一征服了每一个个Excel使用者使用者人力人力资源管理源管理_应用用实例例 动态了解公司了解公司员工的流入和流出工的流入和流出变化情况；化情况；分析分析员工今年薪酬工今年薪酬变化，便于化，便于为明年的薪酬控制提供依据明年的薪酬控制提供依据每天、每月都要每天、每月都要计算算员工的考勤，工的考勤，计算算员工的工工的工资和和奖金，金，制作工制作工资条，把工条，把工资准确无准确无误地地发放到每个放到每个员工工账户，并及，并及时通知每个通知每个员工；工；每

4、年都要把全公司上百人甚至上千人的工每年都要把全公司上百人甚至上千人的工资进行行汇总，制作，制作五五险一金一金汇总表，制作个税代扣代表，制作个税代扣代缴表表员工的生日、合同、退休日期快要到了，如何才能提前提醒，工的生日、合同、退休日期快要到了，如何才能提前提醒，以免到以免到时候手忙脚乱；新候手忙脚乱；新员工的工的试用期快要到了，如何及用期快要到了，如何及时提醒聘用者提醒聘用者签订正式正式劳动合同合同如何如何评价每个价每个业务人人员的的销售售业绩，并根据，并根据业绩计算薪酬算薪酬主讲： 资深EXCEL实战专家韩小良 1980元/人/两天 金融金融财务建模建模_应用用实例例 投投资组合收益率和方差合

5、收益率和方差计算及其算及其VBA实现投投资组合有效合有效边界模型及其界模型及其VBA实现投投资组合合风险优化决策模型及其化决策模型及其VBA实现投投资组合合风险价价值模型及其模型及其VBA实现资本本资产定价模型的建立及其定价模型的建立及其VBA实现Black-Scholes期期权定价模型及其定价模型及其VBA实现二叉二叉树（二（二项式）期式）期权定价模型及其定价模型及其VBA实现期期货套期保套期保值计算的算的VBA实现投投资项目决策与理目决策与理财模型的建立及其模型的建立及其VBA实现参考书:金融财务建模与计算基于VBA与MATLAB实现作者：朱顺泉编著、出版社：电子工业出版其他其他应用用高效

6、数据高效数据处理分析理分析高效高效财务管理管理企企业管理中的高效运用管理中的高效运用 高高级金融建模金融建模 学学习方法方法1循序循序渐进2善用善用资源源,学以致用学以致用u通通过好好书、帮助、网、帮助、网络、BBS论坛club.excelhome/thread-117862-1-1.htmlu博大精深博大精深,不必深究不必深究,但要了解但要了解3多多阅读多多实践践u实践、践、实践、再践、再实践践u有有问题，要独立解决、思考，提高自己能力，要独立解决、思考，提高自己能力u归纳、总结、积累累以以EXCEL功底去学其他同功底去学其他同类软件，学件，学习成本会非常低成本会非常低主要内容主要内容基本功

7、能基本功能特殊技巧特殊技巧函数与公式及其函数与公式及其应用用数据分析数据分析图表表VBAExcel2019功能改功能改进 针对Excel存在的局限性存在的局限性进行改行改进，使其能，使其能够创建建现代代风格的文档。格的文档。 使格式化文档更使格式化文档更简便快捷便快捷 提供提供专门设计的可利用的且具有独的可利用的且具有独创性的内容性的内容 容易看到工作成果，就像已容易看到工作成果，就像已经打印出来的一打印出来的一样 更容易更容易维护电子表格和子表格和进行格式更新行格式更新 满足一些足一些长期从事与打印相关期从事与打印相关顾客的需求客的需求 提供一些美提供一些美观的文档示例的文档示例 更容易移更

8、容易移动内容内容(例如，例如，图表表)到其它到其它Office应用程用程序序(例如，例如，PowerPoint)在在Word，PowerPoint和和Excel中的所有操作方法中的所有操作方法都是一致的，因此，用都是一致的，因此，用户能能够将某个将某个应用程序的方用程序的方法法应用到另一个用到另一个应用程序中用程序中 主要特点主要特点增加在工作薄中可显示的颜色数从(原先的)256色到(现在的)43亿(32位色)格式化的“现场预览”极大的改进了图表,专业的“图表”样式改进了单元格样式特点，添加了条件格式到列表，数据透视表和图表一个新的视图页面布局视图，增加了普遍需要的、与打印相关的特点，单击即可

9、输入页眉和页脚“文档主题”(颜色，字体和效果变化能在Office应用程序间共享)更新了Office界面外观(绘图工具条)和艺术字25个美观且具有独创性的实用模板非常酷的状非常酷的状态栏和精美和精美图表表 Excel2019质的突破：的突破：1.灵巧灵巧变化的状化的状态栏状状态栏缩放控制：放控制： 增加了一个不需增加了一个不需弹出窗口的控制滑出窗口的控制滑块来来调整整文件的文件的缩放比例，当放比例，当调整控制滑整控制滑块时,文件同文件同时改改变显示比例。也可以使用示比例。也可以使用“+”和和“-”按按钮来放来放大或大或缩小小显示比例示比例,每点每点击一次一次调节10%。多多样化的化的计算状算状态

10、栏确确 u在之前的在之前的Excel版本中，当你版本中，当你选中了数中了数值数据数据时，可以，可以在状在状态栏看到看到这些数据的小些数据的小计 求和，求和，计数，平均数，平均值等等等，可以等，可以选择6种不同的小种不同的小计方式，但一次只能看到一种。方式，但一次只能看到一种。uExcel2019中可以中可以把几个或者全部的把几个或者全部的小小计方式方式显示在状示在状态栏，求和、最大，求和、最大值、最小、最小值、计数数,计数数值,平均平均值的的全部全部显示或者示或者显示其任意示其任意组合。合。插入工作表按插入工作表按钮 只要只要单击这个按个按钮就会在工作簿中新增一个工作表，就会在工作簿中新增一个

11、工作表，这一一点比点比较快捷快捷 。2.几个几个图表表 精髓：填充柄、精髓：填充柄、单元格引元格引用用第第2章章基本功能基本功能一、一、Excel基本操作基本操作工作簿属性工作表属性单元格属性输入数据技巧页面设置与打印工作表编辑、格式条件格式选择性粘贴导入与导出数据数据有效性的应用排序、筛选、分类汇总、数据透视表1. 工作簿属性工作表缺省数量(3)工作表缺省用户名(sheet1、sheet2、sheet3）使用“Office按钮”下的“准备/属性”菜单设置文档属性重点掌握其中的、主题、关键词、作者标记为最终状态(只读方式,不可修改)密码：方法1：另存为对话框设置方法2：准备/加密文档”菜单设置

12、，若取消进入设置对话框，删去密码即可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）自动

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

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

15、ALSE=0u例：例：=TRUE+1等于等于2，=FALSE-1等于等于-1逻辑判断判断u0=FALSE非零非零=TRUE6个公式：个公式：u=A1*1 u=A1/1u=A1+0u=A1-0u=-A1减减负、=N（）（）（N函数）函数）其他功能：其他功能：数据有效性数据有效性条件格式条件格式选择性粘性粘贴排序、排序、筛选、分、分类汇总、数据透、数据透视表表二、二、Excel高高级技巧技巧保护工作簿和工作表共享工作簿和合并工作簿公式（相对引用与绝对引用）函数、公式审核窗体控件的应用VBA1. 保护工作簿和工作表保护工作簿选择“审阅”选项卡“更改”组的“保护工作簿”项选择“保护结构和窗口”。勾选“

16、结构”或“窗口”复选框保护结构: 不能插入、删除工作表、更改工作表名称等保护窗口: 保留窗口的大小及位置等保护工作表:保护工作表中数据不被任意修改锁定定+保保护工作表工作表：保护所有被锁定的单元格。选择“审阅”选项卡的“更改”组的“保护工作表”项，在对话框里输入密码在“保护工作表”对话框勾选所需的保护内容保护工作表命令只对本工作表起作用只允许用户编辑指定单元格区域取消取消锁定定+保保护工作表工作表选择指定单元格，取消单元格的保护锁定此时只有可以被编辑，其它区域都被锁住了隐藏公式隐藏藏+保保护工作表工作表2. 共享工作簿共享工作簿：使用“审阅”选项卡的“更改”组选择“共享工作簿”，打开其对话框，

17、勾选“允许多用户”复选框。共享工作簿+保护：以追踪修订方式共享：选择“审阅”选项卡的“更改”组的“保护共享工作簿”项，打开“保护共享工作簿”对话框。勾选“以追踪修订方式共享”选项，输入密码突出显示修订:当数据被修改时,像批注一样标示出,格式变了不标示主要解决主要解决问题：工作表工作簿：工作表工作簿关关联、引用数据区域、引用数据区域第第3章章 函数与公式基函数与公式基础函数与公式学函数与公式学习方法方法最有魅力的功能之一最有魅力的功能之一初初级阶段：段：常用函数，如何填写参数；遇到常用函数，如何填写参数；遇到了了if函数，再遇到函数，再遇到VLOOKUP函数（函数（难：需：需要空要空间感、理解数

18、据在不同方位的定位、感、理解数据在不同方位的定位、查找和返回的找和返回的过程）程）中中级阶段：段：单个函数功能是有限的，多个函个函数功能是有限的，多个函数的嵌套与数的嵌套与组合才能完成比合才能完成比较复复杂的运算的运算高高级阶段：段：数数组公式和多公式和多维引用引用公式功能公式功能计算算建立数据之建立数据之间的关的关联u单元格数据直接无关系元格数据直接无关系u各工作簿之各工作簿之间无关系无关系u各工作表之各工作表之间无关系无关系l通通过公式的公式的逻辑关系，把它关系，把它们关关联起来起来l自自动重算重算l原始数据的改原始数据的改变可以使用同一个可以使用同一个计算模型算模型两个重要思路两个重要思

19、路工作表、工作簿的工作表、工作簿的逻辑关关联公式建立公式建立EXCEL由行列数据构成的，因此由行列数据构成的，因此获取所需的取所需的行列区域是要解决主要行列区域是要解决主要问题数数组、引用函数（、引用函数（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

20、)操作功能键：CTRL+SHIFT+ENTER(完成的是多重完成的是多重计算算)重新重新计算公式的算公式的时间和方式和方式自自动重新重新计算（默算（默认的的设置）置）u只有在公式所依只有在公式所依赖的的单元格元格发生更改生更改u第一次打开工作簿以及第一次打开工作簿以及编辑工作簿工作簿时“Excel 选项”的“公式”类别的“计算选项”部分的“工作簿计算”下，单击“自动”除数据表外，自除数据表外，自动重算重算u若要在每次更改若要在每次更改值、公式或名称、公式或名称时重新重新计算算除数据表之外除数据表之外所有相关的公式所有相关的公式手手动计算算u若要关若要关闭自自动重新重新计算算单击“手动”时，Ex

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

22、表中的公式函数分函数分类内置函数内置函数DateDif（）（）扩展函数展函数通通过加加载宏宏自定自定义函数函数例：例：隐藏内置函数藏内置函数u=NUMBERSTRING(1234567890,1)l结果：一十二亿三千四百五十六万七千八百九十 u=NUMBERSTRING(1234567890,2)l结果：壹拾贰亿叁仟肆佰伍拾陆万柒仟捌佰玖拾 u=NUMBERSTRING(1234567890,3)l结果：一二三四五六七八九 u=DATESTRING(2019-8-8)l08年08月08日 uDATEDIF（）（）公式分公式分类普通公式普通公式数数组公式公式命名公式命名公式u=A1：A8u=AV

23、RAGER（DATA）公式限制公式限制公式内容公式内容长度不能超度不能超过1024个字符（个字符（2019）公式中函数嵌套不能超公式中函数嵌套不能超过7层（2019）u将前将前6层定定义名称，然后引用名称，然后引用公式中函数参数不能超公式中函数参数不能超过30u使用括号括起多个参数，从而使用括号括起多个参数，从而变成成1个参数个参数数字数字计算精度算精度为15位位u单引号引号u设置置为文本型文本型名称管理器名称管理器查看已有的名称看已有的名称 u查看名称的引用范看名称的引用范围（“Refersto”控件）控件）u适用范适用范围（“Scope”栏）u结果果值（“Value”栏注：注：错误结果果值

24、也会也会显示出来）示出来）u确确认该名称是否已在表格中使用（名称是否已在表格中使用（“InUse”栏） 创建名称建名称编辑已有的名称已有的名称 u名称可以重命名，不必再名称可以重命名，不必再为了改名字而重新去定了改名字而重新去定义一个名称一个名称 u可以很快地在可以很快地在编辑名称名称对话框中修改名称的适用范框中修改名称的适用范围 快速快速删除名称除名称 u一次性一次性选择和和删除多个名称除多个名称u名称排序名称排序u单击栏标题可以可以对名称名称进行排序行排序u调整名称整名称对话框的大小框的大小u根据需要根据需要调整整refers-tobox（引用范（引用范围文本框）的文本框）的宽度。度。这样

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

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

27、含含义：名称扩展到工作表范围：只显示 适用范围为工作表的名称名称扩展到工作簿范围：只显示工作簿范围内全局适用的名称 有错误的名称 ：,只显示值包含错误（如 #REF、#VALUE 或 #NAME）的那些名称没有错误的名称：只显示值不包含错误的那些名称 已定义名称：只显示由您或 Excel 定义的名称，如打印区域表名称：只显示表名称。函数公式出函数公式出错信息信息（7个）个）# #DIV/0零作除数零作除数# #NAME?在公式中使用了不能在公式中使用了不能识别的名称的名称删除了公式中使用的名称，或者使用了不存在的名称。除了公式中使用的名称，或者使用了不存在的名称。函数名的拼写函数名的拼写错误#

28、 #VALUE！使用了不正确的参数或运算符使用了不正确的参数或运算符在需要数字或在需要数字或逻辑值时输入了文本入了文本# #REF！引用了无效的引用了无效的单元格地址元格地址删除了由其它公式引用的除了由其它公式引用的单元格元格将移将移动单元格粘元格粘贴到由其它公式引用的到由其它公式引用的单元格中。元格中。# #NULL！指定了两个并不相交的区域，故无效指定了两个并不相交的区域，故无效使用了不正确的区域运算符或不正确的使用了不正确的区域运算符或不正确的单元格引用。元格引用。# #N/A当在函数或公式中引用了无法使用的数当在函数或公式中引用了无法使用的数值内部函数或自定内部函数或自定义工作表函数中

29、缺少一个或多个参数。工作表函数中缺少一个或多个参数。使用的自定使用的自定义工作表函数不存在。工作表函数不存在。VLOOKUP（ ） 函函 数数 中中 的的 查 找找 值 lookup_value、FALSE/TRUE参数指定了不正确的参数指定了不正确的值域。域。# #NUM！数字数字类型不正确型不正确在需要数字参数的函数中使用了不能接受的参数。在需要数字参数的函数中使用了不能接受的参数。由公式由公式产生的数字太大或太小：在生的数字太大或太小：在-10-10307307和和1010307307之之间#！输入入到到单元元格格中中的的数数值太太长，在在单元元格格中中显示示不不下下；单元元格格公公式式

30、所所产生生的的结果果太太长，单元元格格容容纳不不下下；日日期期和和时间产生了生了负值时将将产生。生。运算符代替运算符代替逻辑函数函数星号星号* *代替代替“与与”例例:=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代替代替*、+原因

31、：数原因：数组公式需要公式需要执行多重行多重计算，而算，而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章章 引用与引用与查找函数找函数Of

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

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

34、等于 lookup_value 的最小值0 :查找等于 lookup_value 的第一个值1:(默认值)查找小于或等于 lookup_value 的最大值使用特点使用特点第第2个参数：个参数：1行或行或1列数列数组是一个数是一个数,表示表示查找找值的行号或列号的行号或列号常常使用在常常使用在uIndex函数的行号或列号函数的行号或列号uOFFSET的参数（偏移量等）的参数（偏移量等）区区别（解决同（解决同问题）u=INDEX(A1:D11,MATCH(F1,A1:A11,0),MATCH(G1,A1:D1,0) u=OFFSET(A1,MATCH(F1,A2:A11,0),MATCH(G1,

35、B1:D1,0)INDEXINDEX(array,row_num,column_num)功能：返回功能：返回单元格或数元格或数组中的数据或元素中的数据或元素值，此元素由行号和列号的索引值来给定。Array单元格区域或数组常量。Row_num数组中某行的行号，函数从该行返回数值。如果省略 row_num，则必须有 column_num。Column_num数组中某列的列标，函数从该列返回数值。如果省略 column_num，则必须有 row_num。使用特点使用特点给出出选定区域的行号或列号，定区域的行号或列号，获取取查找找值u多行多列多行多列u单行行u单列列常使用在常使用在u指定行或列的指定行

36、或列的查找找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返回数返回数值参数列表中参数列表中的数的

37、数值。Index_num指定所选定的值参数。Index_num 必须为 1 到 254 之间的数字，或者是包含数字 1 到 254 的公式或单元格引用。如果 index_num 为 1，函数 CHOOSE 返回 value1；如果为 2，函数 CHOOSE 返回 value2，以此类推。Value1,value2,.为 1 到 254 个数值参数，函数 CHOOSE 基于 index_num，从中选择一个数值或一项要执行的操作。LOOKUPHLOOKUP(lookup_value,table_array,row_index_num,range_lookup)功能：在表格或数功能：在表格或数值数

38、数组组的首行的首行查找指定的数找指定的数值，并在表格或，并在表格或数数组中指定行的同一列中返回一个数中指定行的同一列中返回一个数值。VLOOKUP(lookup_value,table_array,col_index_num,range_lookup)功能：功能：参数表示垂直方向参数表示垂直方向LOOKUP(lookup_value,lookup_vector,result_vector)功能：功能：向量形式:在单行区域或单列区域（称为“向量”）中查找值，然后返回第二个单行区域或单列区域中相同位置的值。数组形式:在数组的第一行或第一列中查找指定的值，然后返回数组的最后一行或最后一列中相同位置的

39、值使用特点使用特点VLOOKUP使用技巧使用技巧u1、2列互列互换if(1,2,)u连接接2个个查找找值拼接拼接&“|”u部分含有部分含有通配符通配符“*”uOFFSET构造范构造范围在条件格式、数据有效性、排序等在条件格式、数据有效性、排序等应用用第第5章章 函数函数应用用11.1.条件格式的条件格式的应用用（生日提醒、（生日提醒、标记重复重复值、填充不、填充不同色等）同色等）2.2.数据有效性的数据有效性的应用用（限制（限制录入数据范入数据范围、重复、重复值、创建下拉列表、快捷建下拉列表、快捷输入数据等）入数据等）3.3.选择性粘性粘贴(四四则运算、运算、转置、复制格式、公式粘置、复制格式

40、、公式粘贴为数数值等）等）4.4.排序排序应用用(字母、笔画、字符数量、随机等）字母、笔画、字符数量、随机等）函数函数应用用11.条件格式条件格式当当单元格数据元格数据满足某种特定条件，自足某种特定条件，自动显示指定的格式示指定的格式特点：特点：动态的的公式公式设置：置：若对某列或某区域，多数情况只要对左上角单元格（相对引用）设置条件，EXCEL会自动扩展到选区中=ABS(DATE(YEAR(TODAY(),MONTH($B2),DAY($B2)-TODAY()1设置填充色置填充色例例3：填充：填充国国际象棋棋象棋棋盘u=MOD(ROW()+COLUMN(),2)=0u=MOD(ROW()+C

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

42、Eu=NOT(OR(A2=B$2:B$11)（同理）（同理）尤其体现在大区域数值的比较中特点:1.复制:通过“选择性粘贴”2 .数据有效性的设置仅对手工录入有效，对复制粘贴输入不生效例1:输入提框方法:“数据有效性”对话框中的“输入信息”和“出错警告”选项卡，可以设置输入提示和出错提示信息。例2:标识出已录入数据中不符合录入范围的数据：先设置录入数据范围，再显示出“公式审核”工具栏，单击“圈释无效数据”按钮。2.数据有效性的数据有效性的应用用要求：按要求：按时序序输入日期入日期方法：方法：u选择区域，区域，设置置为日期格式日期格式u数据有效性：自定数据有效性：自定义，公式：，公式：=N(A2)

43、=N(A1)例例3：按日期：按日期顺序序输入数据入数据例例4:为单元格元格设置下拉列表供置下拉列表供录入数据入数据时选择,可以避免可以避免误输入数据入数据例例5：切：切换不同数据列（数据有效性）不同数据列（数据有效性）根据根据A1的的输入入值13，可，可以在以在A2获取由右取由右侧3列构列构成的相成的相应下拉列表数据下拉列表数据方法：方法：u选择D1：D11，定，定义名称：名称：List.1u选择A2u设置数据有效性：序列、来置数据有效性：序列、来源：源：=OFFSET（List.1,A1-1）单字段排序多字段排序按列/行排序汉字笔画排序按职务排序，顺序为“主任”“科长”“职员”“实习生”方法

44、:先“自定义序列”排序时选择“自定义序列”3. 排序的排序的应用用例例1：按字符数量排序：按字符数量排序方法：方法：uC列：列：C1输入入“字数字数”uC2输入公式：入公式：=LEN（B2）u排序：排序：C列升序列升序u删除除C列列例例2：随机排序：随机排序uC列：列：输入公式入公式=RNAD（）（）u按按C列排序列排序例例3：字母与数字混合排序：字母与数字混合排序要求：先比要求：先比较字母大小再比字母大小再比较数字大小数字大小方法：方法：u在在B2输入公式：入公式：=LEFT(A2,1)&RIGHT(000&RIGHT(A2,LEN(A2)-1),3)u复制到复制到B3B14u对B列列实现升序排序升序排序解解释u数字部分数字部分变化化为3位，不足的前面位，不足的前面补0uLEFT(A2,1)取字母取字母AB单个字母，若多字母适当个字母，若多字母适当改改变uRIGHT(A2,LEN(A2)-1)获取除第一个字母外的数取除第一个字母外的数字字uRIGHT(“000”&“7”,3)获得得007，从右，从右侧取取3位位