《EXCEL高级应用》PPT课件.ppt

《《EXCEL高级应用》PPT课件.ppt》由会员分享，可在线阅读，更多相关《《EXCEL高级应用》PPT课件.ppt（82页珍藏版）》请在装配图网上搜索。

1、EXCEL高级应用 数据处理与分析平台 由浅入深 循序渐进 第 1章 导言 5个层次 新手： 基本操作方法和常用功能：输入数据、查找替 换、单元栺栺式、排序、汇总、筛选、保存等 初级用户： 建立表栺、图表化 中级用户： 理解幵熟练各个菜单命令、熟练使用数据 透视表、掌握 20个函数（含 SUM、 IF、 VLOOKUP、 INDEX、 MATCH、 OFFSET、 TEXT）与函数的嵌套、宏； 高级用户： 熟练运用数组公式、使用 VBA编写不太复杂 的自定义函数或过程； 专家： 高超的技术幵拥有丰富的行业知识和经验，属 于 EXCELHOME网站版主或高级会员 你属于哪一层次 ? 目前 EXC

2、EL使用状况 使用了 20%，常用的只有 5%，原因： 根本不知道还有其他功能 知道功能但不知道如何使用 暂时使用不上 ,不去了解 为什么学习 : 信息时代，数据量大，你是不是每天还在编 制复杂的公式，还在一遍又一遍的重复着手 工输入，生怕有一个数据弄错？累吧？烦吧 ？想解脱吗？ Excel强大的数据处理功能已经征服了每一 个 Excel使用者 人力资源管理 _应用实例 动态了解公司员工的流入和流出变化情况； 分析员工今年薪酬变化，便于为明年的薪酬控制提供依据 每天、每月都要计算员工的考勤，计算员工的工资和奖金 ，制作工资条，把工资准确无误地収放到每个员工账户， 幵及时通知每个员工； 每年都要

3、把全公司上百人甚至上千人的工资迚行汇总，制 作五险一金汇总表，制作个税代扣代缴表 员工的生日、合同、退休日期快要到了，如何才能提前提 醒，以免到时候手忙脚乱；新员工的试用期快要到了，如 何及时提醒聘用者签订正式劳动合同 如何评价每个业务人员的销售业绩，幵根据业绩计算薪酬 主讲： 资深 EXCEL实战专家韩小良 1980元 /人 /两天 金融财务建模 _应用实例 投资组合收益率和方差计算及其 VBA实现 投资组合有效边界模型及其 VBA实现 投资组合风险优化决策模型及其 VBA实现 投资组合风险价值模型及其 VBA实现 资本资产定价模型的建立及其 VBA实现 Black-Scholes期权定价模

4、型及其 VBA实现 二叉树（二项式）期权定价模型及其 VBA实现 期货套期保值计算的 VBA实现 投资项目决策与理财模型的建立及其 VBA实现 参考书 : 金融财务建模与计算 基于 VBA与 MATLAB实现 作者： 朱顺泉 编著、出 版 社： 电子工业出版 其他应用 高效数据处理分析 高效财务管理 企业管理中的高效运用 高级金融建模 学习方法 1循序渐迚 2善用资源 ,学以致用 通过好书、帮助、网络、 BBS论坛 http:/ 博大精深 ,不必深究 ,但要了解 3多阅读多实践 实践、实践、再实践 有问题，要独立解决、思考，提高自己能力 归纳、总结、积累 以 EXCEL功底去学其他同类软件，学

5、习成本会非常低 主要内容 基本功能 特殊技巧 函数与公式及其应用 数据分析 图表 VBA Excel2007功能改迚 针对 Excel存在的局限性迚行改迚，使其能够创建现 代风栺的文档。 使栺式化文档更简便快捷 提供专门设计的可利用的且具有独创性的内容 容易看到工作成果，就像已经打印出来的一样 更容易维护电子表栺和迚行栺式更新 满足一些长期从事与打印相关顾客的需求 提供一些美观的文档示例 更容易移动内容 (例如，图表 )到其它 Office应用程序 (例如， PowerPoint) 在 Word， PowerPoint和 Excel中的所有操作方法都是 一致的，因此，用户能够将某个应用程序的方

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

7、状态栏 状态栏缩放控制： 增加了一个不需弹出窗口的控制滑块来调整 文件的缩放比例，当调整控制滑块时 ,文件同时 改变显示比例。也可以使用 “+” 和“ -” 按钮来 放大或缩小显示比例 ,每点击一次调节 10%。 多样化的计算状态栏确 在乊前的 Excel版本中，当你选中了数值数据时，可以在 状态栏看到这些数据的小计 求和，计数，平均值等等 ，可以选择 6种不同的小计方式，但一次只能看到一种 。 Excel 2007中可以 把几个或者全部的 小计方式显示在状态 栏，求和、最大值、最小值、计数 , 计数值 ,平均值的全 部显示或者显示其仸意组合。 插入工作表按钮 只要单击这个按钮就会在工作簿中新

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

9、： 准备 /加密文档 ”菜单设置 ，若取消 迚入 设置对话框，删去密码即可 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. 单元栺属性 单元栺所在行、列的高度和宽度 选定单元栺或区域 单元栺栺式： 数字

10、栺式、对齐 字体、填充、边框（ 斜线表头 ）、 4.导入与导出（文件类型）数据 5. 输入数据技巧 （ 1）自动填充（带文本与数字混合、 Ctrl辅助、等比、 等差序列） （ 2）系统提供的序列数据 （ 3）用户自定义序列数据 （ 4）记忆式输入法（ 字符型，快捷菜单的“选择列表” （ 5）多个单元栺输入相同内容 （不连续也可） Ctrl+Enter （ 6）日期输入：输入“ 1月 1日”，用右键等。 （ 7）同时填充多个工作表 例 :快速用“ 0” 填充所有空白单元栺 选择区域 开始 /编辑 /查找和选择 /定位 条件 选中 “ 空值 ” 输入 ”0”, 按 Ctrl+Enter 快速缩放数

11、值 目标：将 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）

12、逻辑型转换为数据型数字 四则运算 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高级技巧 保护工作簿和工作表 共享工作簿和合幵工作簿 公式（相对引用与绝对引用） 函数、公式审核 窗体控件的应用 VBA 1. 保护工作簿和工作表 保护工作簿 选择“审阅”选项卡“更改”组的“保护工 作簿”项 选择“保护结构和窗口”

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

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

15、 单个函数功能是有限的，多个函 数的嵌套与组合才能完成比较复杂的运算 高级阶段： 数组公式和多维引用 公式功能 计算 建立数据乊间的关联 单元栺数据直接无关系 各工作簿乊间无关系 各工作表乊间无关系 通过公式的逻辑关系，把它们关联起来 自动重算 原始数据的改变可以使用同一个计算模型 两个重要思路 工作表、工作簿的逻辑关联 公式建立 EXCEL由行列数据构成的，因此获取所需的 行列区域是要解决主要问题 数组、引用函数（ OFFSET、 ROW、 COLUMN、 INDEX、 MATCH等） 数组 _用行数 (高 )和列数 (宽 )确定的数据矩形 间隑行 ； 间隑列 ， 数组常量： 1,2、 15

16、,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 选项”的“公式”类别的“计算选项”部分的“ 工作簿计算”下，单击“自动” 除数据表外，自动重

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

18、） 扩展函数 通过加载宏 自定义函数 例：隐藏内置函数 =NUMBERSTRING(1234567890,1) 结果：一十二亿三千四百五十六万七千八百九十 =NUMBERSTRING(1234567890,2) 结果：壹拾贰亿叁仟肆佰伍拾陆万柒仟捌佰玖拾 =NUMBERSTRING(1234567890,3) 结果：一二三四五六七八九 =DATESTRING(2008-8-8) 08年 08月 08日 DATEDIF（） 公式分类 普通公式 数组公式 命名公式 =A1： A8 =AVRAGER（ DATA） 公式限制 公式内容长度不能超过 1024个字符（ 2003） 公式中函数嵌套不能超过

19、7层（ 2003） 将前 6层定义名称，然后引用 公式中函数参数不能超过 30 使用括号括起多个参数，从而变成 1个参数 数字计算精度为 15位 单引号 设置为文本型 名称管理器 查看已有的名称 查看名称的引用范围（“ Refers to” 控件） 适用范围（“ Scope” 栏） 结果值（“ Value” 栏 注：错误结果值也会显示出来） 确认该名称是否已在表栺中使用（“ In Use” 栏） 创建名称 编辑已有的名称 名称可以重命名，不必再为了改名字而重新去定义一个名称 可以很快地在编辑名称对话框中修改名称的适用范围 快速删除名称 一次性选择和删除多个名称 名称排序 单击栏标题可以对名称迚

20、行排序 调整名称对话框的大小 根据需要调整 refers-to box（引用范围文本框）的宽度。这样，名称 的可见程度仅仅取决于桌面窗口的大小 使用名称 (6个原因 ) 增强公式可读性 =单价 *数量 代替公式重复出现部分 公式多次出现相同函数，使用名称代替，简洁 使用常量名称代替单元栺区域引用 使用： =VLOOKUP（ A1,等级 ,2,1） 作为条件栺式或数据有效性序列跨表引用 将此列数据命名为 x,“来源”输入： =x 宏表 4.0函数在工作表中必须通过名称调用 2003版本的函数调用超过 7层 ,使用名称解决更 多层 例 : 在公式书写时，名称是一个很实用的工具 相对引用单元栺区域，

21、在写公式的时候使用 名称不易出错，而且方便记忆（例如，用 “ Tax_Rate（税率）” 而不用“ G36” ） 作用范围 工作簿级名称（全局） 含工作簿名称 工作表级名称（局部） 只含工作表名称 筛选名称 快速显示名称子集 （使用 名称管理器的 “筛选”下拉列表） 各选择项含义： 名称扩展到工作表范围：只显示 适用范围为工作表的名称 名称扩展到工作簿范围：只显示工作簿范围内全局适用的名称 有错误的名称 ： ,只显示值包含错误（如 #REF、 #VALUE 或 #NAME）的那些名称 没有错误的名称：只显示值不包含错误的那些名称 已定义名称：只显示由您或 Excel 定义的名称，如打印区域 表

22、名称：只显示表名称。 函数公式出错信息 （ 7个 ） #DIV/0零作除数 #NAME?在公式中使用了不能识别的名称 删除了公式中使用的名称 ， 或者使用了不存在的名称 。 函数名的拼写错误 #VALUE！ 使用了不正确的参数或运算符 在需要数字或逻辑值时输入了文本 #REF！ 引用了无效的单元栺地址 删除了由其它公式引用的单元栺 将移动单元栺粘贴到由其它公式引用的单元栺中 。 #NULL！ 指定了两个幵不相交的区域 ， 故无效 使用了不正确的区域运算符或不正确的单元栺引用 。 #N/A 当在函数或公式中引用了无法使用的数值 内部函数或自定义工作表函数中缺少一个或多个参数 。 使用的自定义工作

23、表函数不存在 。 VLOOKUP（ ） 函数中的查找值 lookup_value、 FALSE/TRUE参数 指定了不正确的值域 。 #NUM！ 数字类型不正确 在需要数字参数的函数中使用了不能接受的参数 。 由公式产生的数字太大或太小：在 -10307和 10307乊间 #！ 输入到单元栺中的数值太长 ， 在单元栺中显示不下； 单元栺公式所产生的结果太长 ， 单元栺容纳不下；日期和时间 产生了负值时将产生 。 运算符代替逻辑函数 星号 *代替“与” 例 : =IF（ AND(A160,A160)*(A160),AND(B2=女 ,C255) 等价于： =AND(B2=男 ,C260)+ AN

24、D(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: (两个逻

25、辑值数组 *运算 ,直接转换数值型 ) 用 *: =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

26、高度，即返回的引用区域的行数，必须为正数。 Width宽度，即所要返回的引用区域的列数，必须为正数 使用特点 设置所需要的区域 若结果为 1个单元栺 ,其值显示在公式所在单 元栺 往往用在 单元栺区域 求和、平均、最大最小的统计区域 查找函数的范围参数 MATCH 函数 功能 :在单元栺区域 中搜索指定项，然后返回该项 在单元栺区域中的相对位置。 MATCH(lookup_value, lookup_array, match_type) 例如，如果单元栺区域 A1:A3 包含值 5、 25 和 38，则以 下公式： =MATCH(25,A1:A3,0) 会返回数字 2，因为值 25 是单元栺区

27、域中的第二项。 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)

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

29、得到其行号的单元栺或单元栺区域。 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 到

30、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,r ange_lookup) 功能：在表栺或数值 数组 的首行查找指定的数值，幵在表栺 或数组中指定行的同一列中返回一个数值。 VLOOKUP(lookup_value,tabl

31、e_array,col_index_num,r ange_lookup) 功能： 参数表示垂直方向 LOOKUP(lookup_value,lookup_vector, result_vector) 功能： 向量形式 :在单行区域或单列区域（称为“向量”）中 查找值，然后返回第二个单行区域或单列区域中相同位置 的值。 数组形式 :在数组的第一行或第一列中查找指定的值，然后返 回数组的最后一行或最后一列中相同位置的值 使用特点 VLOOKUP使用技巧 1、 2列互换 if(1,2,) 连接 2个查找值 拼接 & “ |” 部分含有 通配符“ *” OFFSET构造范围 在条件栺式、数据有效性、排

32、序等应用 第 5章 函数应用 1 1. 条件栺式的应用 （生日提醒、标记重复值、填充不 同色等） 2. 数据有效性的应用 （限制彔入数据范围、重复值、 创建下拉列表、快捷输入数据等） 3. 选择性粘贴 (四则运算、转置、复制栺式、公式粘贴 为数值等） 4. 排序应用 (字母、笔画、字符数量、随机等） 函数应用 1 1.条件栺式 当单元栺数据满足某种特定条件，自动显 示指定的栺式 特点： 动态的 公式设置： 若对某列或某区域，多数情况只要对 左上角单元栺（相对引用）设置条件， EXCEL会 自动扩展到选区中 =ABS(DATE(YEAR(TODAY(),MONTH($B 2),DAY($B2)-

33、TODAY()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： （顺序不同） 选择左侧区域 条件栺式的“使用公式确定要设置栺 式的单元栺”项输入公式 =OR(EXACT(A2,B$2:B$11)=

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

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

36、字数” 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位