excel高级技巧部分公式使用PPT课件.ppt

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