HR办公EXCEL常用技巧大全【PPT课件】

中级班教材 2 使用公式 公式中的运算符 公式中的运算顺序 引用单元格 创建公式 创建公式的方法 创建简单公式 创建包含引用或名称的公式 创建包含函数的公式 使用函数 常用函数 财务函数 日期和时间函数 数学与三角函数 统计函数 函数的输入 手工输入 利用函数向导输入 求和计算 扩充的自动求和 条件求和 建立数据清单 建立数据清单的准则 使用记录单 数据排序 对数据清单进行排序 创建自定义排序 数据筛选 自动筛选 高级筛选 分类汇总 分类汇总的计算方法 汇总报表和图表 插入和删除分类汇总 插入图表表示数据 复杂的数据链接 使用数据透视表 创建数据透视表 编辑数据透视表 控件工具箱 ) 课程大纲 3 使用公式 公式中的运算符 1. 算术运算符 算术运算符号 运算符含义 示 例 +(加号 ) 加 2+3=5 -(减号 ) 减 3 *(星号 乘 3*2=6 /(斜杠 ) 除 6/2=3 %(百分号 ) 百分号 50% (脱字号 ) 乘方 43=43=64 4 使用公式 公式中的运算符 2. 文本运算符 “ &”号 ,可以将文本连接起来 . 3. 比较运算符 比较运算符 运算符含义 示 例 =(等号 ) 相等 1,若 1中的值相等 ,则产生逻辑真值 不相等 ,则产生逻辑假值 大于号 ) 大于 1,若 ,则条件成立产生逻辑真值 则产生逻辑假值=(大于等于号 ) 大于等于 1 1350姓名 部门 基本工资 奖金 所得税 实发工资王小明 销售 1300 250 40 1510张力浩 销售 1200 200 20 1380祁春 销售 1200 200 30 137033 数据的筛选 高级筛选 第一步 : 建立条件区域 ; 输入筛选条件 : 若要找到满足两组条件（每一组条件都包含针对多列的条件）之一的数据行，请在各行中键入条件 若要找到满足两组以上条件的行，请用相同的列标包括多列。 条件：所指定的限制查询或筛选的结果集中包含哪些记录的条件。 姓名 部门 基本工资 奖金 所得税 实发工资王小明 销售 1300 250 40 1510李大双 销售 1150 150 50 1250张力浩 销售 1200 200 20 1380祁春 销售 1200 200 30 1370部门 实发工资销售 1350姓名 部门 基本工资 奖金 所得税 实发工资王小明 销售 1300 250 40 1510张力浩 销售 1200 200 20 1380祁春 销售 1200 200 30 137034 数据的筛选 第二步：选定需要筛选的数据清单中任一单元格； 第三步：选择菜单 ” 第四步：在“条件区域”编辑框中，输入条件区域的引用，并包括条件标志。 第五步：若要更改筛选数据的方式，可更改条件区域中的值，并再次筛选数据。 注：如果需要将筛选结果放到另一位置，请选择 ”to 35 数据的筛选 请使用 若要查找 ?（问号） 任何单个字符 例如， sm?找“ “ *（星号） 任何字符数 例如， *找“ “ （波形符）后跟 ?、 * 或 问号、星号或波形符 例如，“ ”将会查找“ 通配符 以下通配符可作为筛选以及查找和替换内容时的比较条件 : 36 分类汇总的概述 分类汇总是对数据清单进行数据分析的一种方法 . 类汇总 37 一行的每一列都有标志，并且同一列中应包含相似的数据，在区域中没有空行或空列。 上面的示例中，应单击“运动”列（列 B）中的单元格。 序排序” 或“降序排序”。 据”菜单上，单击“分类汇总”。 类字段”框中，单击要分类汇总的列。在上面示例中，应单击“运动”列。 总方式”框中，单击所需的用于计算分类汇总的汇总函数。 定汇总项”框中，选中包含了要进行分类汇总的数值的每一列的复选框。在上面的示例中，应选中“销售”列。 分类汇总 要分类汇总的列 分类汇总 步骤 : 38 选中“每组数据分页”复选框。 不是在行的下方，请清除“汇总结果显示在数据下方”复选框。 定”。 可再次使用“分类汇总”命令来添加多个具有不同汇总函数的分类汇总。若要防止覆盖已存在的分类汇总，请清除“替换当前分类汇总”复选框。 分类汇总 要分类汇总的列 分类汇总 39 删除分类汇总 删除分类汇总时， 删除分级显示以及随分类汇总一起插入列表中的所有分页符。 在含有分类汇总的列表中，单击任一单元格。 在“数据”菜单上，单击“分类汇总”。 单击“全部删除”。 分类汇总 40 删除分类汇总 删除分类汇总时， 删除分级显示以及随分类汇总一起插入列表中的所有分页符。 在含有分类汇总的列表中，单击任一单元格。 在“数据”菜单上，单击“分类汇总”。 单击“全部删除”。 分类汇总 41 如图 数据透视表的使用 数据透视表是交互式报表，可快速合并和比较大量数据。您可旋转其行和列以看到源数据的不同汇总，而且可显示感兴趣区域的明细数据。 源数据 第三季度高尔夫汇总的源值 数据透视表 源值的汇总 42 数据透视表的使用 (续 1) 何时应使用数据透视表 如果要分析相关的汇总值，尤其是在要合计较大的列表并对每个数字进行多种比较时，可以使用数据透视表。在上面所述报表中，用户可以很清楚地看到单元格 第三季度高尔夫销售额是如何通过其他运动或季度的销售额或总销售额计算出来的。由于数据透视表是交互式的，因此，您可以更改数据的视图以查看更多明细数据或计算不同的汇总额，如计数或平均值。 数据是如何组织的 在数据透视表中，源数据中的每列或字段都成为汇总多行信息的数据透视表字段。在上例中，“运动”列成为“运动”字段，高尔夫的每条记录在单个高尔夫项中进行汇总。 数据字段（如“求和项：销售额”）提供要汇总的值。上述报表中的单元格 含的“求和项：销售额”值来自源数据中“运动”列包含“高尔夫”和“季度”列包含“第三季度”的每一行。 43 数据透视表的使用 (续 2) 创建数据透视表 打开要创建数据透视表的工作簿。 请单击列表或数据库中的单元格。 在“数据”菜单上，单击“数据透视表和数据透视图”。 在“数据透视表和数据透视图向导”的步骤 1 中，遵循下列指令，并单击“所需创建的报表类型”下的“数据透视表”。 按向导步骤 2 中的指示进行操作。 按向导步骤 3 中的指示进行操作，然后决定是在屏幕上还是在向导中设置报表 版式。 通常，可以在屏幕上设置报表的版式，推荐使用这种方法。只有在从大型的外部数据源缓慢地检索信息，或需要设置页字段来一次一页地检索数据时，才使用向导设置报表版式。如果不能确定，请尝试在屏幕上设置报表版式。如有必要，可以返回向导。 44 在屏幕上设置报表版式 从“数据透视表字段列表”窗口中，将要在行中显示数据的字段拖到标有“将行字段拖至此处”的拖放区域。 如果没有看见字段列表，请在数据透视表拖放区域的外边框内单击，并确保“显示字段列表” 被按下。 若要查看具有多个级别的字段中哪些明细数据级别可用，请单击该字段旁的 。 对于要将其数据显示在整列中的字段，请将这些字段拖到标有“将列字段拖至此处”的拖放区域。 对于要汇总其数据的字段，请将这些字段拖到标有“请将数据项拖至此处”的区域。 只有带有 或 图标的字段可以被拖到此区域。 如果要添加多个数据字段，则应按所需顺序排列这些字段，方法是：用鼠标右键单击数据字段，指向快捷菜单上的“顺序”，然后使用“顺序”菜单上的命令移动该字段。 将要用作为页字段的字段拖动到标有“请将页字段拖至此处”的区域。 若要重排字段，请将这些字段拖到其他区域。若要删除字段，请将其拖出数据透视表。 若要隐藏拖放区域的外边框，请单击数据透视表外的某个单元格。 注释 如果在设置报表版式时，数据出现得很慢，则请单击“数据透视表”工具栏上的“始终显示项目” 来关闭初始数据显示。如果检索还是很慢或出现错误信息，请单击“数据”菜单上的“数据透视表和数据透视图”，在向导中设置报表布局。 数据透视表的使用 (续 ) 45 在向导中设置报表布局 如果已经从向导中退出，则请单击 “ 数据 ” 菜单上的 “ 数据透视表和数据透视图 ”以返回该向导中。 在向导的步骤 3 中，单击 “ 布局 ” 。 将所需字段从右边的字段按钮组拖动到图示的 “ 行 ” 和 “ 列 ” 区域中。 对于要汇总其数据的字段，请将这些字段拖动到 “ 数据 ” 区。 将要作为页字段使用的字段拖动到 “ 页 ” 区域中。 如果希望 次检索一页数据，以便可以处理大量的源数据，请双击页字段，单击 “ 高级 ” ，再单击 “ 当选择页字段项时，检索外部数据源 ” 选项，再单击 “ 确定 ” 按钮两次。（该选项不可用于某些源数据，包括 据库和“据连接 ” 。） 若要重排字段，请将它们拖到其他区域。某些字段只能用于某些区域；如果将一个字段拖动到其不能使用的区域，该字段将不会显示。 若要删除字段，请将其拖到图形区之外。 如果对版式满意，可单击 “ 确定 ” ，然后单击 “ 完成 ” 。 数据透视表的使用 (续 ) 46 删除数据透视表 单击数据透视表。 在“数据透视表”工具栏上，单击“数据透视表”，指向“选定”，再单击“整张表格”。 在“编辑”菜单上，指向“清除”，再单击“全部”。 注释 对于数据透视图报表，删除与其相关的数据透视表，将会冻结图表，使得不可再对其进行更改。 数据透视表的使用 (续 ) 47 制作与使用图表 创建图表 图表是 通过创建各种不同类 型的图表 ,为分析工作表中的各种数据提供更直观的表示结果。 使用图表向导 下面我们先在工作薄中建立数据表如下页图所示 ,根据 工作表中的数据 ,创建不同类型的图表。 图表的编辑 色和图案 48 图 49 操作步骤如下： 1)选定工作表中包含所需数据的所有单元格。 2)单击 常用工具栏 中的图表向导按钮 ,弹出图（ 1 示的对话框： 图表的使用 50 图表的使用（续 1） 3）在显示的图表向导中 ,你可以选择最合适的图表类型 ,单击 签 ,在 拉式列表框中选择任何一种图表类型 ,再在其相应的 子图表类型 的样式中选择其中一种子图表类型 ,然后按下 查看示例 按钮不放 ,就可预览所选类型的示例。单击下一步按钮 ,弹出如图的对话框： 52 图（ 1 图表插入到当前打开的工作表中 ,接着再单击完成按钮 ,便可得到如图所示的图表。 53 图表的编辑 一、插入数据标志及增加图表标题 加入数据标志的过程如下： 单中的 令 ,弹出如图 (1图表标题 分类 数值 54 图（ 1 单击 出现如图 ,择所需选项即可。 55 数据标志的解说 数据标志 说明 显示指定到数据点的分类 示数据点的值 对于饼图、圆环图显示出对整体的百分比 于离散图、气泡图显示出气泡的百分比 56 二、改变图表的文字、颜色和图案 图表的编辑 若要对标题进行编辑 ,可将鼠标移至标题的左下角处双击 ,即会出现右图所示对话框 ,用该对话框可以对标题的字体、字体的背景色等进行修改。 57 图 (1若要对图表区域进行修改 ,可将鼠标移至图表的空白区域双击鼠标 ,则出现图（ 1话框 ,在此对话框中可选定要修改的内容。 58 图 (1若要对图例进行修改 ,可将鼠标移至图例区双击鼠标 ,则出现如图（ 1话框。在此对话框中可对图例进行编辑 ,如图案、字体及位置。 59 附加内容 选框 可通过选中或清除来打开或关闭的选项。您可以在一个工作表中同时选中多个复选框。 文本框 可以向其中键入文字的框。 命令按钮 单击可启动某项操作的按钮。 选项按钮 用于从一组选项中选择某一选项的按钮。 列表框 包含项目列表的框。 组合框 含有下拉列表框的文本框。您可以从列表中选择 ,或在框中键入所需内容。 切换按钮 单击后保持按下状态 ,再单击时又弹起的按钮。 微调按钮 可附加在单元格上或文本框上的按钮。若要增加数值 ,请单击向上箭头；若要减小数值 ,请单击向下箭头。 滚动条 可通过单击滚动箭头或拖动滚动块来滚动数据区域的一种控件。单击滚动箭头与滚动块之间的区域时 ,可以滚动整页数据。 标签 添加到工作表或窗体中 ,用以提供有关控件、工作表或窗体信息的文本。 图像 将图片嵌入到窗体中的控件。 其他控件 其他 件的列表。 60 附加内容 宏的录制与运用 关于宏 如果经常在 重复某项任务 ,那么可以用宏自动执行该任务。宏是一系列命令和 函数 ,存储于 块 中 ,并且在需要执行该项任务时可随时运行。 例如 ,如果经常在单元格中输入长文本字符串 ,则可以创建一个宏来将单元格格式设置为文本可自动换行。 录制宏 在录制宏时 ,您执行一系列命令时存储该过程的每一步信息。然后即可运行宏来重复所录制的过程或“回放”这些命令。如果在录制宏时出错 ,所做的修改也会被录制下来。 附属于某工作薄的新模块中存储每个宏。 61 附加内容 宏的录制与运用 使宏易于运行 可以在“宏”对话框的列表中选择所需的宏并运行宏。如果希望通过单击特定按钮或按下特定组合键来运行宏 ,可将宏指定给某个工具栏按钮、 键盘快捷键 或工作表中的图形对象。 管理宏 宏录制完后 ,可用 辑器 查看宏代码以进行改错或更改宏的功能。例如 ,如果希望用于文本换行的宏还可以将文本变为粗体 ,则可以再录制另一个将单元格文本变为粗体的宏 ,然后将其中的指令复制到用于文本换行的宏中。 “ 辑器”是一个为初学者设计的编写和编辑宏代码的程序 ,而且提供了很多联机帮助。不必学习如何编程或如何用 言来对宏进行简单的修改。利用“ 辑器” ,您可以编辑宏、在模块间复制宏、在不同工作簿之间复制宏、重命名存储宏的模块或重命名宏。 62 附加内容 宏的录制与运用 宏安全性 可通过宏传播的 病毒 提供安全保护。如果您与其他人共享宏 ,则可使用 数字签名 来验证其他用户 ,这样就可保证其他用户为可靠来源。无论何时打开包含宏的工作簿 ,都可以先验证宏的来源再启用宏。 63 附加内容 宏的录制与运用 创建宏 录制宏 将安全级设置为“中”或“低”。 操作方法 在“工具”菜单上 ,单击“选项”。 单击“安全性”选项卡。 在“宏安全性”之下 ,单击“宏安全性”。 单击“安全级”选项卡 ,再选择所要使用的安全级。 在“工具”菜单上 ,指向“宏” ,再单击“录制新宏”。 在“宏名”框中 ,输入 宏 的名称。 注意 宏名的首字符必须是字母 ,其他字符可以是字母、数字或下划线。宏名中不允许有空格；可用下划线作为分词符。 宏名不允许与单元格引用重名 ,否则会出现错误信息显示宏名无效。 如果要通过按键盘上的来运行宏 , 请在“快捷键”框中 ,输入一个字母。可用 母（小写字母）或 母（大写字母） ,其中字母可以是键盘上的任意字母键。快捷键字母不允许是数字或特殊字符（如 或 #）。 注释 当包含宏的工作簿打开时 ,宏快捷键优先于任何相当的 默认快捷键。 64 附加内容 宏的录制与运用 在“保存在”框中 ,单击要存放宏的地址。 如果要使宏在使用 任何时候都可用 ,请选中“个人宏工作簿”。 如果要添加有关宏的说明 ,请在“说明”框中键入该说明。 单击“确定”。 如果要使宏相对于活动单元格位置运行 ,请用相对单元格引用来录制该宏。在“停止录制”工具栏上 ,单击“相对引用” 以将其选中。 继续用“相对引用”录制宏 ,直至退出 再次单击“相对引用” 以将其取消。 执行需要录制的操作。 在“停止录制”工具栏上 ,单击“停止录制” 。 用 建宏 在 “工具”菜单上 ,指向“宏” ,再单击“ 辑器”。 在“插入”菜单上 ,单击“模块”。 将代码键入或复制到模块的代码窗口中。 如果要在模块窗口中运行 宏 请按 编写完宏后 ,请单击“文件”菜单上的“关闭并返回到 65 附加内容 宏的录制与运用 创建启动宏 自动例如： 在启动 自动运行的。有关自动宏的详细信息 ,请参阅 助” 复制宏的一部分以创建另一个宏 将安全级