1、EXCEL高级应用,数据处理与分析平台,由浅入深 循序渐进,第1章 导言,5个层次,新手：基本操作方法和常用功能：输入数据、查找替换、单元格格式、排序、汇总、筛选、保存等 初级用户：建立表格、图表化 中级用户：理解并熟练各个菜单命令、熟练使用数据透视表、掌握20个函数（含SUM、IF、VLOOKUP、INDEX、MATCH、OFFSET、TEXT）与函数的嵌套、宏； 高级用户：熟练运用数组公式、使用VBA编写不太复杂的自定义函数或过程； 专家：高超的技术并拥有丰富的行业知识和经验，属于EXCELHOME网站版主或高级会员,目前EXCEL使用状况,使用了20%，常用的只有5%，原因： 根本不知道
2、还有其他功能 知道功能但不知道如何使用 暂时使用不上,不去了解,为什么学习:,信息时代，数据量大，你是不是每天还在编制复杂的公式，还在一遍又一遍的重复着手工输入，生怕有一个数据弄错？累吧？烦吧？想解脱吗？ Excel强大的数据处理功能已经征服了每一个Excel使用者,人力资源管理_应用实例,动态了解公司员工的流入和流出变化情况； 分析员工今年薪酬变化，便于为明年的薪酬控制提供依据 每天、每月都要计算员工的考勤，计算员工的工资和奖金，制作工资条，把工资准确无误地发放到每个员工账户，并及时通知每个员工； 每年都要把全公司上百人甚至上千人的工资进行汇总，制作五险一金汇总表，制作个税代扣代缴表 员工的
3、生日、合同、退休日期快要到了，如何才能提前提醒，以免到时候手忙脚乱；新员工的试用期快要到了，如何及时提醒聘用者签订正式劳动合同 如何评价每个业务人员的销售业绩，并根据业绩计算薪酬,主讲： 资深EXCEL实战专家韩小良 1980元/人/两天,金融财务建模_应用实例,投资组合收益率和方差计算及其VBA实现 投资组合有效边界模型及其VBA实现 投资组合风险优化决策模型及其VBA实现 投资组合风险价值模型及其VBA实现 资本资产定价模型的建立及其VBA实现 Black-Scholes期权定价模型及其VBA实现 二叉树（二项式）期权定价模型及其VBA实现 期货套期保值计算的VBA实现 投资项目决策与理财
4、模型的建立及其VBA实现 参考书:金融财务建模与计算基于VBA与MATLAB实现作者： 朱顺泉 编著、出 版 社： 电子工业出版,其他应用,高效数据处理分析 高效财务管理 企业管理中的高效运用 高级金融建模,学习方法,1循序渐进 2善用资源,学以致用通过好书、帮助、网络、BBS论坛http:/ 博大精深,不必深究,但要了解 3多阅读多实践 实践、实践、再实践 有问题，要独立解决、思考，提高自己能力 归纳、总结、积累 以EXCEL功底去学其他同类软件，学习成本会非常低,主要内容,基本功能 特殊技巧 函数与公式及其应用 数据分析 图表 VBA,Excel2007功能改进,针对Excel存在的局限性
5、进行改进，使其能够创建现代风格的文档。 使格式化文档更简便快捷 提供专门设计的可利用的且具有独创性的内容 容易看到工作成果，就像已经打印出来的一样 更容易维护电子表格和进行格式更新 满足一些长期从事与打印相关顾客的需求 提供一些美观的文档示例 更容易移动内容(例如，图表)到其它Office应用程序(例如，PowerPoint) 在Word，PowerPoint和Excel中的所有操作方法都是一致的，因此，用户能够将某个应用程序的方法应用到另一个应用程序中,主要特点,增加在工作薄中可显示的颜色数从(原先的)256色到(现在的)43亿(32位色) 格式化的“现场预览” 极大的改进了图表,专业的“图
6、表”样式 改进了单元格样式特点，添加了条件格式到列表，数据透视表和图表 一个新的视图页面布局视图，增加了普遍需要的、与打印相关的特点，单击即可输入页眉和页脚 “文档主题”(颜色，字体和效果变化能在Office应用程序间共享) 更新了Office界面外观(绘图工具条)和艺术字 25个美观且具有独创性的实用模板,非常酷的状态栏和精美图表,Excel2007质的突破： 1. 灵巧变化的状态栏 状态栏缩放控制： 增加了一个不需弹出窗口的控制滑块来调整文件的缩放比例，当调整控制滑块时,文件同时改变显示比例。也可以使用“+”和“-”按钮来放大或缩小显示比例,每点击一次调节10%。,多样化的计算状态栏确 在
7、之前的Excel版本中，当你选中了数值数据时，可以在状态栏看到这些数据的小计 求和，计数，平均值等等，可以选择6种不同的小计方式，但一次只能看到一种。 Excel 2007中可以把几个或者全部的小计方式显示在状态栏，求和、最大值、最小值、计数, 计数值,平均值的全部显示或者显示其任意组合。插入工作表按钮 只要单击这个按钮就会在工作簿中新增一个工作表，这一点比较快捷 。,2. 几个图表, 精髓：填充柄、单元格引用,第2章 基本功能,一、Excel基本操作 工作簿属性 工作表属性 单元格属性 输入数据技巧 页面设置与打印 工作表编辑、格式 条件格式 选择性粘贴 导入与导出数据 数据有效性的应用 排
8、序、筛选、分类汇总、数据透视表,1. 工作簿属性 工作表缺省数量(3) 工作表缺省用户名(sheet1、sheet2、sheet3） 使用“Office按钮”下的“准备/属性”菜单设置文档属性 重点掌握其中的、主题、 关键词、作者 标记为最终状态(只读方式,不可修改) 密码： 方法1：另存为对话框设置 方法2：准备/加密文档”菜单设置，若取消 进入设置对话框，删去密码即可,2. 工作表属性 工作表列：A,B,XFD(16384=214) 工作表行:1,2,1048576=220 工作表多个独立单元格214220 单元格地址：列标行标； 区域地址：左上角单元格地址:右下角单元格地址 相对引用：例
9、：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
10、（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 =A
11、1-0 =- -A1 减负运算 （第1个-是减法，第2个-是负数） 例：=SUMPRODUCT（-(LEFT（A2：A10）=“陈”)统计姓陈的员工数 =VALUE（A1）,逻辑型转换为数据型数字,四则运算 TRUE=1 FALSE=0 例： =TRUE+1等于2， =FALSE-1等于-1 逻辑判断 0=FALSE 非零=TRUE 6个公式： =A1*1 =A1/1 =A1+0 =A1-0 =- -A1 减负、=N（）（N函数）,其他功能：,数据有效性 条件格式 选择性粘贴 排序、筛选、分类汇总、数据透视表,二、Excel高级技巧 保护工作簿和工作表 共享工作簿和合并工作簿 公式（相对引用与
12、绝对引用） 函数、公式审核 窗体控件的应用 VBA,1. 保护工作簿和工作表 保护工作簿 选择“审阅”选项卡“更改”组的“保护工作簿”项 选择“保护结构和窗口”。 勾选“结构”或“窗口”复选框 保护结构: 不能插入、删除工作表、更改工作表名称等 保护窗口: 保留窗口的大小及位置等,保护工作表: 保护工作表中数据不被任意修改 锁定+保护工作表：保护所有被锁定的单元格。 选择“审阅”选项卡的“更改”组的“保护工作表”项，在对话框里输入密码 在“保护工作表”对话框勾选所需的保护内容 保护工作表命令只对本工作表起作用 只允许用户编辑指定单元格区域 取消锁定+保护工作表 选择指定单元格，取消单元格的保护
13、锁定 此时只有可以被编辑，其它区域都被锁住了 隐藏公式 隐藏+保护工作表,2. 共享工作簿共享工作簿： 使用“审阅”选项卡的“更改”组选择“共享工作簿”，打开其对话框，勾选“允许多用户”复选框。共享工作簿+保护： 以追踪修订方式共享： 选择“审阅”选项卡的“更改”组的“保护共享工作簿”项，打开“保护共享工作簿”对话框。 勾选“以追踪修订方式共享”选项，输入密码 突出显示修订: 当数据被修改时,像批注一样标示出,格式变了不标示, 主要解决问题：工作表工作簿关联、引用数据区域,第3章 函数与公式基础,函数与公式学习方法最有魅力的功能之一,初级阶段：常用函数，如何填写参数；遇到了if函数，再遇到VL
14、OOKUP函数（难：需要空间感、理解数据在不同方位的定位、查找和返回的过程） 中级阶段：单个函数功能是有限的，多个函数的嵌套与组合才能完成比较复杂的运算 高级阶段：数组公式和多维引用,公式功能,计算 建立数据之间的关联 单元格数据直接无关系 各工作簿之间无关系 各工作表之间无关系 通过公式的逻辑关系，把它们关联起来 自动重算 原始数据的改变可以使用同一个计算模型,两个重要思路,工作表、工作簿的逻辑关联 公式建立EXCEL由行列数据构成的，因此获取所需的行列区域是要解决主要问题 数组、引用函数（OFFSET、ROW、COLUMN、INDEX、MATCH等）,数组,_用行数(高)和列数(宽)确定的
15、数据矩形 间隔行； 间隔列， 数组常量：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 选项”的“公式”类别的“计算选项”部分的“工作簿计算”下，单击“自动” 除数据表外，自动
16、重算 若要在每次更改值、公式或名称时重新计算除数据表之外所有相关的公式 手动计算 若要关闭自动重新计算 单击“手动”时，Excel 将自动选中“保存工作簿前重新计算”复选框。如果保存工作簿需要很长时间，那么清除“保存工作簿前重新计算”可缩短保存时间。,函数工具与技巧,公式复制 拖曳填充柄 双击填充柄（向下填充到邻列第1个空单元格上方） 选择性粘贴公式 函数工具提示 （选项设置） 判断参数是否可以省略（带方括号的参数） 逐步看计算结果 F9: 当选中单元格地址或函数时，在编辑栏显示部分计算结果 公式求值 使用监视窗口 保护/隐藏工作表中的公式,函数分类,内置函数DateDif（） 扩展函数通过加
17、载宏 自定义函数 例：隐藏内置函数 =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层定义
18、名称，然后引用 公式中函数参数不能超过30 使用括号括起多个参数，从而变成1个参数 数字计算精度为15位 单引号 设置为文本型,名称管理器,查看已有的名称 查看名称的引用范围（“Refers to” 控件） 适用范围（“Scope” 栏） 结果值（“Value” 栏注：错误结果值也会显示出来） 确认该名称是否已在表格中使用（“In Use”栏） 创建名称 编辑已有的名称 名称可以重命名，不必再为了改名字而重新去定义一个名称 可以很快地在编辑名称对话框中修改名称的适用范围 快速删除名称 一次性选择和删除多个名称 名称排序 单击栏标题可以对名称进行排序调整名称对话框的大小 根据需要调整refers
19、-to box（引用范围文本框）的宽度。这样，名称的可见程度仅仅取决于桌面窗口的大小,使用名称(6个原因),增强公式可读性 =单价*数量 代替公式重复出现部分 公式多次出现相同函数，使用名称代替，简洁 使用常量名称代替单元格区域引用 使用：=VLOOKUP（A1,等级,2,1） 作为条件格式或数据有效性序列跨表引用 将此列数据命名为x,“来源”输入：=x 宏表4.0函数在工作表中必须通过名称调用 2003版本的函数调用超过7层,使用名称解决更多层,例:,在公式书写时，名称是一个很实用的工具 相对引用单元格区域，在写公式的时候使用名称不易出错，而且方便记忆（例如，用“Tax_Rate（税率）”
20、而不用“G36”） 作用范围 工作簿级名称（全局） 含工作簿名称 工作表级名称（局部） 只含工作表名称,筛选名称,快速显示名称子集 （使用名称管理器的“筛选”下拉列表） 各选择项含义： 名称扩展到工作表范围：只显示 适用范围为工作表的名称 名称扩展到工作簿范围：只显示工作簿范围内全局适用的名称有错误的名称 ：,只显示值包含错误（如 #REF、#VALUE 或 #NAME）的那些名称 没有错误的名称：只显示值不包含错误的那些名称已定义名称：只显示由您或 Excel 定义的名称，如打印区域 表名称：只显示表名称。,函数公式出错信息 （7个）#DIV/0零作除数 #NAME?在公式中使用了不能识别的
21、名称 删除了公式中使用的名称，或者使用了不存在的名称。 函数名的拼写错误 #VALUE！使用了不正确的参数或运算符 在需要数字或逻辑值时输入了文本 #REF！引用了无效的单元格地址 删除了由其它公式引用的单元格 将移动单元格粘贴到由其它公式引用的单元格中。 #NULL！指定了两个并不相交的区域，故无效 使用了不正确的区域运算符或不正确的单元格引用。,#N/A 当在函数或公式中引用了无法使用的数值 内部函数或自定义工作表函数中缺少一个或多个参数。 使用的自定义工作表函数不存在。 VLOOKUP（ ）函数中的查找值lookup_value、FALSE/TRUE参数指定了不正确的值域。 #NUM！数
22、字类型不正确 在需要数字参数的函数中使用了不能接受的参数。 由公式产生的数字太大或太小：在 -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:B1
23、1=“江西“)+ (B2:B11=“广东“)*(C2:C11=“男“)*D2:D11),不能用AND、OR代替*、+,原因：数组公式需要执行多重计算，而AND、OR返回的是单值TRUE或FALSE，不能形成数组公式多区域之间的一一对应关系。 例：=SUM（AND(C3:C770, C3:C770)*(C3:C780)= TRUE;FALSE; TRUE; TRUE; TRUE*TRUE; TRUE; FALSE; TRUE; FALSE =1;0;1;1;1*1;1;0;1;0=1;0;0;1;0=sum (1;0;0;1;0)=2,CELL函数,CELL(info_type, referen
24、ce) 功能：返回有关单元格的格式、位置或内容的信息。 info_type必需。一个文本值，指定要返回的单元格信息的类型,逻辑函数,AND函数AND(logical1, logical2, .) 功能：对参数值求并，逻辑与。 logical1必需。要检验的第一个条件，其计算结果可以为 TRUE 或 FALSE。 logical2可选。要检验的其他条件，其计算结果可以为 TRUE 或 FALSE，最多可包含 255 个条件。 OR函数OR(logical1, logical2, .) 功能：对参数值求或，逻辑或。 logical1必需。要检验的第一个条件，其计算结果可以为 TRUE 或 FALS
25、E。 logical2可选。要检验的其他条件，其计算结果可以为 TRUE 或 FALSE，最多可包含 255 个条件。 NOT函数NOT(logical1) 功能：对参数值求反，即逻辑非。 Logical为一个可以计算出 TRUE 或 FALSE 的逻辑值或逻辑表达式,SUMPRODUCT,功能：返回相应几组数组或区域乘积的和，若只有1组,则返回该数组之和。 SUMPRODUCT（array1, array2, array3,） Array1, array2, array3, 为 1 到 255 个数组（数组参数必须具有相同的维数），其相应元素需要进行相乘并求和。 注意: 括号问题:含有=的值
26、要加括号,例: (B2:B11=“江西”,“广东”)、(C2:C11=“男“) * 问题: =SUMPRODUCT(B2:B11=“江西”)*1,(C2:C11=“男”)*1,(D2:D11) -，=SUMPRODUCT(B2:B11=“江西”)*(C2:C11=“男”)*(D2:D11) -*,使用时特点,参数时多个时,大小必须相等 若结果是0,是因为没有转换为数值型 不需要CTRL+SHIFT+ENTER操作,它本身是数组公式 替代 =SUM(B2:B11=“一班“)*(C2:C1120) =SUMPRODUCT(B2:B11=“一班“)*(C2:C1120) 免去*1: (两个逻辑值数组
27、*运算,直接转换数值型) 用 *: =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高度，即返回的引用区域的行数
28、，必须为正数。 Width宽度，即所要返回的引用区域的列数，必须为正数,使用特点,设置所需要的区域 若结果为1个单元格,其值显示在公式所在单元格 往往用在 单元格区域 求和、平均、最大最小的统计区域 查找函数的范围参数,MATCH 函数,功能:在单元格区域 中搜索指定项，然后返回该项在单元格区域中的相对位置。 MATCH(lookup_value, lookup_array, match_type) 例如，如果单元格区域 A1:A3 包含值 5、25 和 38，则以下公式： =MATCH(25,A1:A3,0) 会返回数字 2，因为值 25 是单元格区域中的第二项。 match_type 可选
29、: -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,c
30、olumn_num) 功能：返回单元格或数组中的数据或元素值，此元素由行号和列号的索引值来给定。Array单元格区域或数组常量。 Row_num数组中某行的行号，函数从该行返回数值。如果省略 row_num，则必须有 column_num。 Column_num数组中某列的列标，函数从该列返回数值。如果省略 column_num，则必须有 row_num。,使用特点,给出选定区域的行号或列号，获取查找值 多行多列 单行 单列 常使用在 指定行或列的查找,ROW(reference) 功能：返回引用的行号。 Reference需要得到其行号的单元格或单元格区域。 COLUMN (referenc
31、e) 功能：返回引用的列号。 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 返回 value
32、1；如果为 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
33、(lookup_value,lookup_vector, result_vector) 功能：向量形式:在单行区域或单列区域（称为“向量”）中查找值，然后返回第二个单行区域或单列区域中相同位置的值。 数组形式:在数组的第一行或第一列中查找指定的值，然后返回数组的最后一行或最后一列中相同位置的值,使用特点,VLOOKUP使用技巧 1、2列互换if(1,2,) 连接2个查找值 拼接& “|” 部分含有通配符“*” OFFSET构造范围,在条件格式、数据有效性、排序等应用,第5章 函数应用1,条件格式的应用（生日提醒、标记重复值、填充不同色等） 数据有效性的应用（限制录入数据范围、重复值、创建下拉列
34、表、快捷输入数据等） 选择性粘贴(四则运算、转置、复制格式、公式粘贴为数值等） 排序应用(字母、笔画、字符数量、随机等）,函数应用1,1.条件格式,当单元格数据满足某种特定条件，自动显示指定的格式 特点： 动态的 公式设置：若对某列或某区域，多数情况只要对左上角单元格（相对引用）设置条件，EXCEL会自动扩展到选区中,=ABS(DATE(YEAR(TODAY(),MONTH($B2),DAY($B2)-TODAY()=7,1）使用填充色标记今天开始7天要过生日的员工。,2）使用填充色标记当天要过生日的员工。,=AND(month($B2)=month(TODAY(),DAY($B2)=DAY(
35、TODAY(),例1 : 生日提醒,例2：标记重复值,选择区域 条件格式 选择“使用公式确定” 公式： =COUNTIF(A1:A$2,A1)1 设置填充色,例3：填充,国际象棋棋盘 =MOD(ROW()+COLUMN(),2)=0 =MOD(ROW()+COLUMN(),2)=1奇偶行不同 =MOD(ROW(),2)0动态的间隔底纹 =MOD(SUBTOTAL(3,A$2:A2),2)=0 =MOD(SUBTOTAL(3,A$2:A2),2)=1,例4：比较不同区域数值,方法1：条件格式的“只为包含以下内容的单元格设置格式”项输入公式：=A2，选择“不等于 方法2： （顺序不同） 选择左侧区
36、域 条件格式的“使用公式确定要设置格式的单元格”项输入公式 =OR(EXACT(A2,B$2:B$11)=FALSE =NOT(OR(A2=B$2:B$11)（同理）,尤其体现在大区域数值的比较中,特点: 1.复制:通过“选择性粘贴” 2 .数据有效性的设置仅对手工录入有效，对复制粘贴输入不生效 例1: 输入提框 方法: “数据有效性”对话框中的“输入信息”和“出错警告”选项卡，可以设置输入提示和出错提示信息。 例2: 标识出已录入数据中不符合录入范围的数据： 先设置录入数据范围，再显示出“公式审核”工具栏，单击“圈释无效数据”按钮。,2.数据有效性的应用,要求：按时序输入日期 方法： 选择区
37、域，设置为日期格式 数据有效性：自定义，公式：=N(A2)=N(A1),例3：按日期顺序输入数据,例4:为单元格设置下拉列表供录入数据时选择, 可以避免误输入数据,例5：切换不同数据列（数据有效性）,根据A1的输入值13，可以在A2获取由右侧3列构成的相应下拉列表数据 方法： 选择D1：D11，定义名称：List.1 选择A2 设置数据有效性：序列、来源：=OFFSET（List.1 ,A1-1）,单字段排序 多字段排序 按列/行排序 汉字笔画排序 按职务排序，顺序为“主任”“科长”“职员”“实习生” 方法: 先“自定义序列” 排序时选择“自定义序列”,3. 排序的应用,例1：按字符数量排序,
38、方法： C列：C1输入“字数” C2输入公式：=LEN（B2） 排序： C列升序 删除C列,例2：随机排序,C列：输入公式=RNAD（） 按C列排序,例3：字母与数字混合排序,要求：先比较字母大小再比较数字大小 方法： 在B2输入公式：=LEFT(A2,1) & RIGHT(“000“ & RIGHT(A2,LEN(A2)-1),3) 复制到B3B14 对B列实现升序排序 解释 数字部分变化为3位，不足的前面补0 LEFT(A2,1)取字母AB单个字母，若多字母适当改变 RIGHT(A2,LEN(A2)-1)获取除第一个字母外的数字 RIGHT(“000” & “7”,3)获得007，从右侧取3位,
