Excel操作技巧与实例(大全).doc

Leo Sino HR Manage Consulting Studio I Excel 操作技巧操作技巧 Skills About Excel 版本 A Rev 20120820 2012 8 202012 8 20 初版 初版 Email jungwongcn Leo Sino HR Manage Consulting Studio II 目录 1 操作技巧操作技巧 1 1 1 1 关于换行 ALT ENTER 1 1 2 快速在多上合计行中求和 1 1 3 不复制隐藏的行或列 1 1 4 查找通配符 2 1 5 文本处理 2 1 5 1 等长文本的分割 2 1 5 2 不等长文本的分割 2 1 5 3 文本的合并 3 1 5 4 文本转数值的 10 种办法 3 1 5 5 把引号中的字符串 Trends in Genetics 23 4 192 9 中的前半部 分英文提取出来 3 1 5 6 使用分列更改文本为数值 4 1 6 高级筛选 6 1 6 1 如何将一列数据中在另外一列出现过的项目筛选出来 7 1 7 条件格式 7 1 8 数据透视表 8 1 8 1 应用数据透视表进行各类数据分析 8 1 8 2 同时显示数据与百分比 10 1 8 3 筛选数据 10 1 8 4 显示明细数据 11 1 8 5 自动更新 12 1 8 6 透视图 12 1 8 7 多重合并计算数据 13 1 9 名称的引用 15 1 10 自定义格式语法 16 1 11 动态表头 17 1 12 行与列的合计有时出现不相等的情况 17 1 13 表格的几种类型及其处理技巧 17 1 14 快速删除工作表中所有的文本框 18 2 函数数组函数数组 2020 2 1 函数宝典 20 Leo Sino HR Manage Consulting Studio III 2 2 自定义函数 157 个 20 2 3 VLOOKUP 20 2 4 OFFSET 20 2 5 IS 函数 20 2 6 数组公式特征 21 2 7 SQL 语句在 EXCEL 中的典型用法 22 3 图表图表 2323 3 1 复合饼图 23 3 2 图表组合 26 3 3 甘特图 30 3 4 带滚动条的图表 34 3 5 动态图表的制作 36 4 数据分析数据分析 3939 4 1 数据有效性 39 4 1 1 防止重复录入 39 4 1 2 只能输入日期 40 4 1 3 输入的内容中必须包括某字符 41 4 1 4 制作二级选项菜单 41 4 2 整合数据表 41 4 3 自定义数字格式的实用案例 46 4 4 批量取消合并单元格并使用 CTRL ENTER填充数据 47 4 5 隐藏小于 3000 的数值 48 4 6 将一列数据转化为多列数据 48 4 7 将多列数据转化为一列 48 4 8 合并计算汇总多表数据 49 4 9 利用合并计算核对数值型数据 50 5 5 实例技巧实例技巧 5151 5 1 各种各样的排名汇总 51 5 2 资产折旧 51 5 3 巧设单元格格式打印席卡 53 Leo Sino HR Manage Consulting Studio 0 1 操作技巧操作技巧 1 11 1 关于换行关于换行 Alt EnterAlt Enter 同一单元格内 有些长数据或条列式内容必须强行换行才能对齐 方法是 光标移到在需要换行的位置上同时按下 Alt Enter 键 使用强行换行时 系统会 同时选择自动换行功能 1 输入数据随时换行 用户若要在输入数据时换行 只要通过 Alt Enter 组合键即可轻松实现 此方法同样可使已输入内容的单元格在光标所在处换行 2 单元格区域内换行 将某个长行转成段落并在指定区域内换行 例如 A10 内容很长 欲将其显 示在 A 列至 C 列之内 步骤是 选定区域 A10 C12 先选 A10 选择 编辑 填 充 内容重排 A10 内容就会分布在 A10 C12 区域中 此法特别适合用于表 格内的注释 3 调整单元格格式换行 选定单元格 选择 格式 单元格 在弹出的对话框中单击 对齐 选中 自动换行 复选框 单击 确定 按钮即可 4 录制一个宏 定义快捷键 1 21 2 快速在多上合计行中求和快速在多上合计行中求和 选择区域 按 F5 选择 空值 按 Alt 快捷键 快速在多上合计行中求和 gif 1 31 3 不复制隐藏的行或列不复制隐藏的行或列 a 首先选中需要复制的被隐藏了一些行或列的表格区域 b 然后点击 编辑 定位 定位条件 在其中选择 可见单元格 c 复制表格区域 粘贴即可 Leo Sino HR Manage Consulting Studio 1 1 41 4 查找通配符查找通配符 在 Excel 中 如何替换单元格中的 号呢 由于二者在 查找和替换 对话框中扮演通配符的角色 代表多个 单个任意字符 而要替换这两个符号 本身 需要在前面增加一个 符号 如附图所示 同理要替换 符号时 也 需要在其前面增加一个 符号 1 51 5 文本处理文本处理 1 5 11 5 1 等长文本的分割等长文本的分割 从字符串的左边取字符 Left 字符串 文本长度 从字符串的右边取字符 Right 字符串 文本长度 从字符串的中间取字符 Mid 字符串 文本起始位置 文本长度 1 5 21 5 2 不等长文本的分割不等长文本的分割 第 1 步 选中要进行分割的字符串区域 第 2 步 点击 数据 分列 在文本分列向导中选择适合的分隔符 第 3 步 设置需要导入的列以及放置该列的位置 Leo Sino HR Manage Consulting Studio 2 1 5 31 5 3 文本的合并文本的合并 Concatenate 文本 1 文本 2 使用连接符 1 5 41 5 4 文本转数值的文本转数值的 1010 种办法种办法 文本转数值的10种方法 gif 文本转数值的10种 办法 xls 1 5 51 5 5 把引号中的字符串把引号中的字符串 Trends Trends inin GeneticsGenetics 2323 4 4 192192 9 9 中的前半中的前半 部分英文提取出来部分英文提取出来 使用数组公式 LEFT A1 MIN FIND ROW 1 10 1 A1 1 17 1 也可使用公式 LEFT A1 MIN FIND ROW 1 10 1 A1 0123456789 1 说明 1 17是一个包含了0到9的数字小数 目的是在原数据的后面连接上 0123456789 以避免 find 时出错 这是简化公式的常用手法 与此类似作用 的还有5 19 1 5 61 5 6 使用分列更改文本为数值使用分列更改文本为数值 如果你发现在 Excel 中输入的公式计算结果不正确 也许是单元格中保存 Leo Sino HR Manage Consulting Studio 3 的数字被保存为文本类型的原因 这时需要将文本类型的数这转为数值 方法 如下 Leo Sino HR Manage Consulting Studio 4 1 61 6 高级筛选高级筛选 由于自动筛选只能筛选出简单条件的数据 因此如要在复杂条件下进行筛 选就需要采用高级筛选的方式 在需要进行筛选的数据表外设定筛选条件 在对话框中设定 列表区域为数据表区域 条件区域为刚才在数据表外部 设定的条件区域 要包括字段名称和条件所在的单元格区域 Leo Sino HR Manage Consulting Studio 5 1 6 11 6 1 如何将一列数据中在另外一列出现过的项目筛选出来如何将一列数据中在另外一列出现过的项目筛选出来 1 71 7 条件格式条件格式 条件格式可以使得符合特定条件的记录按照某种设定的格式显示 选中要进行条件格式设定的单元格范围 执行 格式 条件格式 在条件格式设置界面进行条件的设定 Leo Sino HR Manage Consulting Studio 6 1 81 8 数据透视表数据透视表 1 8 11 8 1 应用数据透视表进行各类数据分析应用数据透视表进行各类数据分析 选择 数据 数据透视表和数据透视图 进入透视表设置向导 Leo Sino HR Manage Consulting Studio 7 点击 下一步 选择正确的数据范围 点击 下一步 选择 布局 按钮 在如下图界面上 将右侧的字段拖入左侧相应区域内 形成如下图的布局 点击确定按钮 Leo Sino HR Manage Consulting Studio 8 生成如下的透视表后 将鼠标悬停在想要移动的字段上 鼠标左键按下 将字 段拖放到其他区域 生成自己需要的数据显示 数据百分比显示 右键菜单 字段设置 选项 将数据显示方式改为 占同列 数据总和的百分比 1 8 21 8 2 同时显示数据与百分比 同时显示数据与百分比 1 在 布局 里将销售收入两次拖入数据区域 2 将其中一个销售收入改为百分比显示 3 在报表项目上输入新的名称可以修改项目名称 4 将报表项目拖拽到列标题位置 可以将数值和百分比改为按列排列 1 8 31 8 3 筛选数据 筛选数据 点击字段名称后面的下拉菜单 可以对字段内容进行筛选 Leo Sino HR Manage Consulting Studio 9 1 8 41 8 4 显示明细数据 显示明细数据 双击需要查看明细的数据 在新的工作表上将显示构成此汇总数据的所有明细 数据 1 对数据进行排序 把光标放在行位置的报表项目上 右键菜单 字段设置 高级 按钮 左侧可以进行排序设置 2 按照日期分组 1 将日期放入行区域 右键菜单 组及显示明细数据 组合 2 在 步长 中选择需要的分组标准 3 按照数值分组 1 将销售收入放入行区域 右键菜单 组及显示明细数据 组合 2 在 步长 中选择需要的分组标准 4 插入计算字段 Leo Sino HR Manage Consulting Studio 10 在 名称 后给计算字段定义名称 在 公式 后输入计算字段的计算公式 可以引用下面的字段 1 8 51 8 5 自动更新 自动更新 右键菜单 表格选项 打开时刷新 可以让报表始终有更新后的数据显 示 1 8 61 8 6 透视图透视图 可以单独生成数据透视图 也可以基于现有的透视表生成透视图 如果已经有 现成的透视表 通过点击数据透视表工具条上的图表按钮可以生成一个透视图 Leo Sino HR Manage Consulting Studio 11 通过拖动透视图上的各个字段到右侧或者底部的位置 可以方便地改变图 表组织数据的方式 隐藏透视图字段按钮 把光标悬停在任意字段上 右键菜单 选择 隐藏数据透视图字段按钮 即可以将字段按钮隐藏 再次显示字段按钮 点击透视图工具条上第一项 在出现的下拉菜单中选择 隐藏数据透视图 字段按钮 如果有些数据无法在数据透视表内部分析 可引用透视表的数据作为分析基础 1 8 71 8 7 多重合并计算数据多重合并计算数据 如下图格式的即为二维表 Leo Sino HR Manage Consulting Studio 12 现在我们需要将数个格式相同的二维表汇总为一张表格 且可以区分不同表格 属性进行分析 比如三张表分别为北京 上海 深圳分公司的表格 我们使用 的方法是利用透视表多重合并计算数据区域的功能 选择数据 数据透视表和数据透视图 选择第 3 个数据源类型 多重合并计算数据区域 点击下一步按钮 在出现的界面上选择 自定义字段 Leo Sino HR Manage Consulting Studio 13 选择需要被合并的表格区域 点击添加将其添加到所有区域 将页字段数据改 为 1 在项标志处输入该表格的标志 使用相同的方法将其他需要合并的表格 全部添加 透视表生成后如下图所示 已经将多个表格合并成一个 双击字段名 可以对字段名进行修改 Leo Sino HR Manage Consulting Studio 14 1 91 9 名称的引用名称的引用 名称 名称可以代表一个单元格或者一个单元格区域 或者是常量 公式 名称的定义 选中需要命名的单元格或区域 在界面左上角名称框中输入名称后回车 名称的删除 插入 名称 定义 选中需要删除的名称点击删除按钮 点确定 名称的引用 需要引用某单元格时输入该单元格的名称 名称 第 1 步 选中需要命名的某个单元格或单元格区域 第 2 步 在左上角名称框输入命名后回车 删除已有命名 选择 插入 名称 定义 选中需要删除的名称 点击 删除 按钮 Leo Sino HR Manage Consulting Studio 15 名称命名的优点 1 避免绝对引用的错误 2 对公式进行文字化表述 让公式更加容易理解 3 可以在整个工作簿中通用 引用方便 1 101 10 自定义格式语法 自定义格式语法 大于条件值 格式 小于条件值 格式 等于条件值 格式 文本格式 1 111 11 动态表头 动态表头 ABC 公司 YEAR NOW 年 MONTH NOW 月报表 1 121 12 行与列的合计有时出现不相等的情况 行与列的合计有时出现不相等的情况 选择 工具 选项 重新计算 以显示精度为准 选中该选项 1 131 13 表格的几种类型及其处理技巧表格的几种类型及其处理技巧 数据表类型操作目标汇总方法 是否需要重复 制作 是否与数据源 有链接关系 多字段数据 列表 记录叠加 并按表名 区分 导入外部数据 SQL 语句 不需要有 Leo Sino HR Manage Consulting Studio 16 二维表 多个二维 表生成透 视表 透视表多维数据区 域合并 不需要有 同一文件内 的报表 数据汇总 sum begin end B2 不需要有 不同文件中 的报表 数据汇总合并计算不需要有 非标准数据 表 汇总 建议修改原表的结 构或者仅作为终端 报表 上层再建立 一个数据源表作为 收集数据使用 需要无 1 141 14 快速删除工作表中所有的文本框快速删除工作表中所有的文本框 有时公莫名其妙地在工作表中添加许多文本框 想删除所有文本框可以用 附图中的方法 其他插入的对象 如图片 自选图形等都可以用同样的方式删 除哦 Leo Sino HR Manage Consulting Studio 17 Leo Sino HR Manage Consulting Studio 18 2 函数数组函数数组 2 12 1 函数宝典函数宝典 函数宝典2011 11版 xls 2 22 2 自定义函数自定义函数 157157 个个 Excel自定义函数15 7个 xls 2 32 3 vLookupvLookup vlookup函数详解 E xcel Home xls 2 42 4 OffsetOffset OFFSET引用函数应 用 ByGdliyy xls 2 52 5 ISIS 函数函数 可以检验数值的类型并根据参数取值返回 TRUE 或 FALSE 函数函数如果为下面的内容 则返回如果为下面的内容 则返回 TRUETRUE ISBLANK 值为空白单元格 ISERR 值为任意错误值 除去 N A Leo Sino HR Manage Consulting Studio 19 ISERROR 值为任意错误值 N A VALUE REF DIV 0 NUM NAME 或 NULL ISLOGICAL值为逻辑值 ISNA 值为错误值 N A 值不存在 ISNONTEXT 值为不是文本的任意项 注意此函数在值为空白单元格时返回 T ISNUMBER 值为数字 ISREF 值为引用 ISTEXT 值为文本 2 62 6 数组公式特征数组公式特征 对单元格区域进行多重计算的计算方式与普通计算公式的区别是录入公式 结束后 需要同时按下 ctrl shift enter 其特征是在公式两端会出现一对大 括号 Leo Sino HR Manage Consulting Studio 20 2 72 7 SQLSQL 语句在语句在 EXCELEXCEL 中的典型用法中的典型用法 Leo Sino HR Manage Consulting Studio 21 3 图表图表 3 13 1 复合饼图复合饼图 当数据系列内值的大小差异较大时 可以制作复合饼图以便数据显示更为 清楚 如下图所示 选择饼图中的复合饼图子图表类型 点击下一步 切换到图例标签 将显示图例的勾去掉 Leo Sino HR Manage Consulting Studio 22 切换到数据标志标签 把类别名称和百分比打勾 选中数据系列 右键菜单 选择数据系列格式 切换到选项标签 将第二绘图区包含最后的值改为 4 Leo Sino HR Manage Consulting Studio 23 复合饼图制作完成 Leo Sino HR Manage Consulting Studio 24 3 23 2 图表组合图表组合 一个图表中的不同数据系列可以采用不同的图表类型显示 如下图 首先制作一个柱形图 在图表工具条上选择数据系列 Compensation 在图表工具条上选择数据系列格式按钮 Leo Sino HR Manage Consulting Studio 25 在数据系列格式界面上 切换到坐标轴标签 将主坐标轴改为次坐标轴 右键点击 Compensation 选择图表类型 将其图表类型改为折线图 用同样的方式操作 Production 系列 将其图表类型改为面积图 完成图表组合的制作 下拉菜单式图表 Leo Sino HR Manage Consulting Studio 26 写入如下的公式 index 函数查找出的值由 A9 单元格来指定 制作三维饼图 其数据区域为 A3 I3 A9 I9 需要手工选取 点击 视图 工具栏 窗体 调出窗体工具条 在窗体工具条上选择组合框 在饼图上拖放出一个组合框 Leo Sino HR Manage Consulting Studio 27 右键选中组合框 在菜单中选择 设置控件格式 在控制标签中做如下设置 其中单元格链接为存储控件选中项目序号的单元格 完成下拉菜单式图表的制作 Leo Sino HR Manage Consulting Studio 28 3 33 3 甘特图甘特图 如果想制作如下图的甘特图 需要以下 4 列数据 首先制作堆积条形图 Leo Sino HR Manage Consulting Studio 29 点击下一步 切换到系列标签 删除结束日期系列 生成的条形图如下图所示 Leo Sino HR Manage Consulting Studio 30 下面转换纵坐标的项目排列次序 双击纵坐标 切换到刻度 将分类次序反转和数据轴交叉于最大分类选项打勾 此时纵坐标次序已经反转 双击开始日期数据系列 在数据系列格式对话框中将边框和内部都改选为无 此时开始日期数据系列隐藏 Leo Sino HR Manage Consulting Studio 31 此时需要将横坐标开始日期改为真正的项目开始日期 在 excel 中 每一个日期都对应一个数值 选中 B2 查看 2008 7 1 对应数值为 39630 双击横坐标 切换到刻度 将最小值改为 39630 同样将横坐标最大值改为真 正的项目结束日期 Leo Sino HR Manage Consulting Studio 32 甘特图制作完成 3 43 4 带滚动条的图表带滚动条的图表 该图表中可以随着点击滚动条的动作而动态翻看源数据中的大量数据 定义两个动态引用的名称 Period offset 7 动态图表 A 1 7 动态图表 D 1 0 10 1 Data offset 7 动态图表 B 1 7 动态图表 D 1 0 10 1 制作折线图 选择系列标签 删除 period 系列 Leo Sino HR Manage Consulting Studio 33 将 值 和 分类 X 轴标志 设置为如下图内容 调出窗体工具条 在图表外绘制滚动条 右键选中滚动条 选择设置控件格式 Leo Sino HR Manage Consulting Studio 34 在单元格链接中设置为 D1 3 53 5 动态图表的制作动态图表的制作 选中北京 深圳 上海以下的区域 分别定义北京 深圳 上海的名称 选择视图 工具栏 窗体 选择选项按钮 画出如下三个选项按钮 Leo Sino HR Manage Consulting Studio 35 右键选中选项按钮 在右键菜单中选择设置控件格式 在跳出的对话框中 选择控制标签 在单元格链接中选择 A16 在 A17单元格中输入公式 choose a16 北京 上海 深圳 为 A17定 义名称 choose 在 A2 A3 A4单元格中输入以下内容 在 B16单元中写入以下公式 并复制到该行1 12月的单元格中 VLOOKUP A3 INDIRECT choose COLUMN 1 0 Leo Sino HR Manage Consulting Studio 36 依据此数据表制作双曲线图 Leo Sino HR Manage Consulting Studio 37 4 数据分析数据分析 4 14 1 数据有效性数据有效性 4 1 14 1 1 防止重复录入防止重复录入 Leo Sino HR Manage Consulting Studio 38 4 1 24 1 2 只能输入日期只能输入日期 Leo Sino HR Manage Consulting Studio 39 4 1 34 1 3 输入的内容中必须包括某字符输入的内容中必须包括某字符 not iserror find 中国 g30 4 1 44 1 4 制作二级选项菜单 制作二级选项菜单 首先将一级选项的每个项目定义一个名称 该名称内容包括相应的二级项目 制作一级项目的有效性 制作二级项目的有效性 内容为 INDIRECT g6 其中 g6为设定了有效性的 一级选项所在的单元格 4 24 2 整合数据表整合数据表 我们经常需要将几个表格中的数据整合成一张表 如下图所示 将 5 个月的数 据 目前分布在 5 张表格上 整合到一个总表上 我们使用的方法是利用 office 的查询工具 query 进行的 这样做的优点在于今 后对于新数据的更新可以自动化刷新 而不需要重复的进行整合操作 如复制 粘贴 office 的查询工具 query 在典型安装 office 时是不会被安装的 我们需要首 先检查我们的 office 是否已经安装了 query 方法如下 Leo Sino HR Manage Consulting Studio 40 打开 Excel 点击 数据 菜单中 导入外部数据 新建数据库查询 若系统提示安装 则说明该功能未被安装 此时可能需要在光驱中插入 Office 安装光盘完成该功能的安装 在光驱中插入安装光盘后 安装过程中选择自定义安装 点开 Excel 前面 的加号 选中要安装的功能前面的下拉菜单 选择 从本机运行 点开 Office 工具 前面的加号 选中 Microsoft Query 的下拉菜单 选择 从本机运行 然后执行余下的安装过程即可 下面开始整合工作 选择 excel files 选择要导入数据的 excel 文件 Leo Sino HR Manage Consulting Studio 41 出现选择数据表的界面 如果以上界面提示错误 点击以上界面的 选项 按钮 出现以下界面 将系 统表打勾即可 任选一个字段到右侧 点下一步按钮 Leo Sino HR Manage Consulting Studio 42 直到以下界面出现 选择第二项 点完成按钮 此时出现 query 程序界面 点击工具条上 SQL 按钮 Leo Sino HR Manage Consulting Studio 43 出现以下界面 在界面中将 SQL 语句改为以下 出现以下提示 点确定按钮 Leo Sino HR Manage Consulting Studio 44 出现数据查询结果 选文件菜单 将数据返回 EXCEL 选择在新工作表中存储数据