hr办公常用excel大全

1、Clerk Office 2003 Training Material 文员文员Office 2003培训教材培训教材 Excel training manual- Intermediate Material Excel培训手册培训手册 中级班教材中级班教材 课程大纲课程大纲 使用公式使用公式 ? 公式中的运算符公式中的运算符 ? 公式中的运算顺序公式中的运算顺序 ? 引用单元格引用单元格 创建公式创建公式 ? 创建公式的方法创建公式的方法 ? 创建简单公式创建简单公式 ? 创建包含引用或名称的公式创建包含引用或名称的公式 ? 创建包含函数的公式创建包含函数的公式 使用函数使用函数 ? 常用函

2、数常用函数 ? 财务函数财务函数 ? 日期和时间函数日期和时间函数 ? 数学与三角函数数学与三角函数 ? 统计函数统计函数 函数的输入函数的输入 手工输入手工输入 利用函数向导输入利用函数向导输入 求和计算求和计算 扩充的自动求和扩充的自动求和 条件求和条件求和 建立数据清单建立数据清单 ? 建立数据清单的准则建立数据清单的准则 ? 使用记录单使用记录单 数据排序数据排序 ? 对数据清单进行排序对数据清单进行排序 ? 创建自定义排序创建自定义排序 数据筛选数据筛选 ? 自动筛选自动筛选 ? 高级筛选高级筛选 分类汇总分类汇总 ? 分类汇总的计算方法分类汇总的计算方法 ? 汇总报表和图表汇总报表

3、和图表 ? 插入和删除分类汇总插入和删除分类汇总 插入图表表示数据插入图表表示数据 复杂的数据链接复杂的数据链接 使用数据透视表使用数据透视表 ? 创建数据透视表创建数据透视表 ? 编辑数据透视表编辑数据透视表 controll toolbox usage (控件工具箱控件工具箱) 2 使用公式使用公式 公式中的运算符公式中的运算符 1. 算术运算符算术运算符 算术运算符号算术运算符号 +(加号加号) -(减号减号) *(星号星号 /(斜杠斜杠) %(百分号百分号) (脱字号脱字号) 运算符含义运算符含义 加加 减减 乘乘 除除 百分号百分号 乘方乘方 2+3=5 3-1=2 3*2=6 6/

4、2=3 50% 43=43=64 示示 例例 3 使用公式使用公式 公式中的运算符公式中的运算符 2. 文本运算符文本运算符 “&” 号号,可以将文本连接起来可以将文本连接起来. 3. 比较运算符比较运算符 比较运算符比较运算符 =(等号等号) 运算符含义运算符含义 相等相等 示示 例例 B1=C1,若若B1中单元格内的值确实与中单元格内的值确实与C1中的值相中的值相等等,则产生逻辑真值则产生逻辑真值TRUE,若不相等若不相等,则产生逻辑则产生逻辑假值假值FALSE B1C1,若若B1中数值为中数值为6,C1中数值为中数值为4,则条件成则条件成立产生逻辑真值立产生逻辑真值TRUE,否则产生逻辑

5、假值否则产生逻辑假值FALSE B1=C1 B1C1 B1=C1 (大于号大于号) 小于小于 大于大于 =(大于等于号大于等于号) (不等号不等号) 1350说明: 1.单列上具有多个条件 : 如果对于某一列具有两个或多个筛选条件，那么可直接在各行中从上到下依次键入各个条件 ; 姓名王小明张力浩祁春部门销售销售销售基本工资奖金所得税 实发工资1300250401510120020020138012002003013702.多列上具有单个条件 若要在两列或多列中查找满足单个条件的数据，请在条件区域的同一行中输入所有条件 ; 3.某一列或另一列上具有单个条件 若要找到满足一列条件或另一列条件的数据

6、，请在条件区域的不同行中输入条件 ; 未完 下页续 32 数据的筛选数据的筛选 高级筛选高级筛选 第一步第一步: 建立条件区域建立条件区域; 输入筛选条件输入筛选条件: 4.两列上具有两组条件之一 若要找到满足两组条件（每一组条件都包含针对多列的条件）之一的数据行，请在各行中键入条件 姓名王小明李大双张力浩祁春部门销售销售销售销售基本工资奖金1300115012001200部门销售所得税250150200200实发工资4015105012502013803013705.一列有两组以上条件 若要找到满足两组以上条件的行，请用相同的列标包括多列。 条件：所指定的限制查询或筛选的结果集中包含哪些记录

7、的条件。 姓名王小明张力浩祁春部门销售销售销售实发工资1350基本工资奖金所得税 实发工资13002504015101200200201380120020030137033 数据的筛选数据的筛选 第二步：选定需要筛选的数据清单中任一单元格； 第三步：选择菜单 ”Data / Filter / Advanced Filter” 第四步：在“条件区域”编辑框中，输入条件区域的引用，并包括条件标志。 第五步：若要更改筛选数据的方式，可更改条件区域中的值，并再次筛选数据。 注：如果需要将筛选结果放到另一位置，请选择”Copy to another Location” 34 数据的筛选数据的筛选 通配符

8、通配符 以下通配符可作为筛选以及查找和替换内容时的比较条件 : 请使用 ?（问号） 若要查找 任何单个字符 例如，sm?th 查找“smith” 和“smyth” 任何字符数 *（星号） 例如，*east 查找“Northeast” 和“Southeast” 问号、星号或波形符 （波形符）后跟 ?、* 或 例如，“fy91?” 将会查找“fy91?” 35 分类汇总分类汇总 分类汇总的概述分类汇总的概述 分类汇总是对数据清单进行数据分析的一种方分类汇总是对数据清单进行数据分析的一种方法法. . Data/ SubtotalData/ Subtotal 36 分类汇总分类汇总 步骤步骤: 1.确

9、保要进行分类汇总的数据为下列格式：第一行的每一列都有标志，并且同一列中应包含相似的数据，在区域中没有空行或空列。 2.单击要分类汇总的列中的单元格。在上面的示例中，应单击“运动”列（列 B）中的单元格。 3.单击“升序排序” 或“降序排序”。 4.在“数据”菜单上，单击“分类汇总”。 5.在“分类字段”框中，单击要分类汇总的列。在上面示例中，应单击“运动”列。 6.在“汇总方式”框中，单击所需的用于计算分类汇总的汇总函数。 7.在“选定汇总项”框中，选中包含了要进行分类汇总的数值的每一列的复选框。在上面的示例中，应选中“销售”列。 要分类汇总的列 分类汇总 37 分类汇总分类汇总 8.如果想在

10、每个分类汇总后有一个自动分页符，请选中“每组数据分页”复选框。 9.如果希望分类汇总结果出现在分类汇总的行的上方，而不是在行的下方，请清除“汇总结果显示在数据下方”复选框。 10.单击“确定”。 11.注释 可再次使用“分类汇总”命令来添加多个具有不同汇总函数的分类汇总。若要防止覆盖已存在的分类汇总，请清除“替换当前分类汇总”复选框。 要分类汇总的列 分类汇总 38 分类汇总分类汇总 删除分类汇总删除分类汇总 删除分类汇总时，Microsoft Excel 也删除分级显示以及随分类汇总一起插入列表中的所有分页符。 在含有分类汇总的列表中，单击任一单元格。 在“数据”菜单上，单击“分类汇总”。

11、单击“全部删除”。 39 分类汇总分类汇总 删除分类汇总删除分类汇总 删除分类汇总时，Microsoft Excel 也删除分级显示以及随分类汇总一起插入列表中的所有分页符。 在含有分类汇总的列表中，单击任一单元格。 在“数据”菜单上，单击“分类汇总”。 单击“全部删除”。 40 数据透视表的使用数据透视表的使用 数据透视表是交互式报表，可快速合并和比较大量数据。您可旋转其行和列以看到源数据的不同汇总，而且可显示感兴趣区域的明细数据。 1. 1.选中所需要的数据选中所需要的数据 : :如图如图 源数据 第三季度高尔夫汇总的源值 数据透视表 C2 和 C8 中源值的汇总 41 数据透视表的使用数

12、据透视表的使用( (续续1) 1) 何时应使用数据透视表何时应使用数据透视表 如果要分析相关的汇总值，尤其是在要合计较大的列表并对每个数字进行多种比较时，可以使用数据透视表。在上面所述报表中，用户可以很清楚地看到单元格 F3 中第三季度高尔夫销售额是如何通过其他运动或季度的销售额或总销售额计算出来的。由于数据透视表是交互式的，因此，您可以更改数据的视图以查看更多明细数据或计算不同的汇总额，如计数或平均值。 数据是如何组织的数据是如何组织的 在数据透视表中，源数据中的每列或字段都成为汇总多行信息的数据透视表字段。在上例中，“运动”列成为“运动”字段，高尔夫的每条记录在单个高尔夫项中进行汇总。 数

13、据字段（如“求和项：销售额”）提供要汇总的值。上述报表中的单元格 F3 包含的“求和项：销售额”值来自源数据中“运动”列包含“高尔夫”和“季度”列包含“第三季度”的每一行。 42 数据透视表的使用数据透视表的使用( (续续2) 2) 创建数据透视表创建数据透视表 打开要创建数据透视表的工作簿。 请单击列表或数据库中的单元格。 在“数据”菜单上，单击“数据透视表和数据透视图”。 在“数据透视表和数据透视图向导”的步骤 1 中，遵循下列指令，并单击“所需创建的报表类型”下的“数据透视表”。 按向导步骤 2 中的指示进行操作。 按向导步骤 3 中的指示进行操作，然后决定是在屏幕上还是在向导中设置报表

14、 版式。 通常，可以在屏幕上设置报表的版式，推荐使用这种方法。只有在从大型的外部数据源缓慢地检索信息，或需要设置页字段来一次一页地检索数据时，才使用向导设置报表版式。如果不能确定，请尝试在屏幕上设置报表版式。如有必要，可以返回向导。 43 数据透视表的使用数据透视表的使用( (续续) ) 在屏幕上设置报表版式在屏幕上设置报表版式 从“数据透视表字段列表”窗口中，将要在行中显示数据的字段拖到标有“将行字段拖至此处”的拖放区域。 如果没有看见字段列表，请在数据透视表拖放区域的外边框内单击，并确保“显示字段列表” 被按下。 若要查看具有多个级别的字段中哪些明细数据级别可用，请单击该字段旁的 。 对于

15、要将其数据显示在整列中的字段，请将这些字段拖到标有“将列字段拖至此处”的拖放区域。 对于要汇总其数据的字段，请将这些字段拖到标有“请将数据项拖至此处”的区域。 只有带有 或 图标的字段可以被拖到此区域。 如果要添加多个数据字段，则应按所需顺序排列这些字段，方法是：用鼠标右键单击数据字段，指向快捷菜单上的“顺序”，然后使用“顺序”菜单上的命令移动该字段。 将要用作为页字段的字段拖动到标有“请将页字段拖至此处”的区域。 若要重排字段，请将这些字段拖到其他区域。若要删除字段，请将其拖出数据透视表。 若要隐藏拖放区域的外边框，请单击数据透视表外的某个单元格。 注释 如果在设置报表版式时，数据出现得很慢

16、，则请单击“数据透视表”工具栏上的“始终显示项目” 来关闭初始数据显示。如果检索还是很慢或出现错误信息，请单击“数据”菜单上的“数据透视表和数据透视图”，在向导中设置报表布局。 44 数据透视表的使用数据透视表的使用( (续续) ) 在向导中设置报表布局在向导中设置报表布局 如果已经从向导中退出，则请单击“数据”菜单上的“数据透视表和数据透视图”以返回该向导中。 在向导的步骤 3 中，单击“布局”。 将所需字段从右边的字段按钮组拖动到图示的“行”和“列”区域中。 对于要汇总其数据的字段，请将这些字段拖动到“数据”区。 将要作为页字段使用的字段拖动到“页”区域中。 如果希望 Excel 一次检索

17、一页数据，以便可以处理大量的源数据，请双击页字段，单击“高级”，再单击“当选择页字段项时，检索外部数据源”选项，再单击“确定”按钮两次。（该选项不可用于某些源数据，包括 OLAP 数据库和“Office 数据连接”。） 若要重排字段，请将它们拖到其他区域。某些字段只能用于某些区域；如果将一个字段拖动到其不能使用的区域，该字段将不会显示。 若要删除字段，请将其拖到图形区之外。 如果对版式满意，可单击“确定”，然后单击“完成”。 45 数据透视表的使用数据透视表的使用( (续续) ) 删除数据透视表删除数据透视表 单击数据透视表。 在“数据透视表”工具栏上，单击“数据透视表”，指向“选定”，再单击

18、“整张表格”。 在“编辑”菜单上，指向“清除”，再单击“全部”。 注释 对于数据透视图报表，删除与其相关的数据透视表，将会冻结图表，使得不可再对其进行更改。 46 制作与使用图表制作与使用图表 创建图表 图表是Excel为用户提供的强大功能,通过创建各种不同类 型的图表,为分析工作表中的各种数据提供更直观的表示结果。 ?使用图表向导 下面我们先在工作薄中建立数据表如下页图所示,根据 工作表中的数据,创建不同类型的图表。 ?图表的编辑 1.插入数据标志及增加图表标题 2.改变图表的文字、颜色和图案 47 图 48 图表的使用图表的使用 操作步骤如下： 1)选定工作表中包含所需数据的所有单元格。

19、2)单击常用工具栏中的图表向导按钮 ,弹出图（1-16）所 示的对话框： 49 图表的使用（续图表的使用（续1 1） 3 3）在显示的图表向导中）在显示的图表向导中 , ,你可以选择最合适的图表类型你可以选择最合适的图表类型 , ,单击单击 standard typestandard type 标签标签, ,在在chart typechart type 下拉式列表框中选择任何一种图表类型下拉式列表框中选择任何一种图表类型 , ,再在其相应的再在其相应的 子子图表类型图表类型 的样式中选择其中一种子图表类型的样式中选择其中一种子图表类型 , ,然后按下然后按下 查看示例查看示例 按钮不放按钮不放

20、 , ,就可预览所选类型的就可预览所选类型的示例。单击下一步按钮示例。单击下一步按钮 , ,弹出如图的对话框：弹出如图的对话框： 50 图（图（1-181-18） 图表插入到当前打开的工作表中图表插入到当前打开的工作表中, ,接着再单击完成按钮接着再单击完成按钮, ,便可得到如图所便可得到如图所示的图表。示的图表。 52 图表的编辑图表的编辑 一、插入数据标志及增加图表标题一、插入数据标志及增加图表标题 加入数据标志的过程如下：加入数据标志的过程如下：1.1.激活要添加数据标志的图表激活要添加数据标志的图表 2. 2.单击单击chartchart菜单中的菜单中的chart optionscha

21、rt options命令命令, ,弹出如图弹出如图(1-19) (1-19) 图表标题 分类X轴 数值Y轴 53 图（图（1-201-20） 单击单击data labels,data labels,出现如图出现如图, ,择所需选项即可。择所需选项即可。 54 数据标志的解说数据标志的解说 数据标志数据标志 category name category name valuevalue percentage percentage 说明说明 显示指定到数据点的分类显示指定到数据点的分类 显示数据点的值显示数据点的值 对于饼图、圆环图显示出对整体的百分比对于饼图、圆环图显示出对整体的百分比 bubbl

22、e sizebubble size 对于离散图、气泡图显示出气泡的百分比对于离散图、气泡图显示出气泡的百分比 55 图表的编辑图表的编辑 二、改变图表的文字、颜色和图案二、改变图表的文字、颜色和图案 若要对标题进行编辑,可将鼠标移至标题的左下角处双击,即会出现右图所示对话框,用该对话框可以对标题的字体、字体的背景色等进行修改。 56 图图(1-23) (1-23) 若要对图表区域进行修改,可将鼠标移至图表的空白区域双击鼠标,则出现图（1-23）对话框,在此对话框中可选定要修改的内容。 57 图图(1-24) (1-24) 若要对图例进行修改,可将鼠标移至图例区双击鼠标,则出现如图（1-24）对

23、话框。在此对话框中可对图例进行编辑,如图案、字体及位置。 58 附加内容附加内容-控件工具箱控件工具箱Control Box 复选框复选框 可通过选中或清除来打开或关闭的选项。您可以在一个工作表中同时选中多个复选框。 文本框文本框 可以向其中键入文字的框。 命令按钮命令按钮 单击可启动某项操作的按钮。 选项按钮选项按钮 用于从一组选项中选择某一选项的按钮。 列表框列表框 包含项目列表的框。 组合框组合框 含有下拉列表框的文本框。您可以从列表中选择,或在框中键入所需内容。 切换按钮切换按钮 单击后保持按下状态,再单击时又弹起的按钮。 微调按钮微调按钮 可附加在单元格上或文本框上的按钮。若要增加数

24、值,请单击向上箭头；若要减小数值,请单击向下箭头。 滚动条滚动条 可通过单击滚动箭头或拖动滚动块来滚动数据区域的一种控件。单击滚动箭头与滚动块之间的区域时,可以滚动整页数据。 标签标签 添加到工作表或窗体中,用以提供有关控件、工作表或窗体信息的文本。 图像图像 将图片嵌入到窗体中的控件。 其他控件其他控件其他 ActiveX 控件的列表。 59 附加内容附加内容宏的录制与运用宏的录制与运用 关于宏关于宏 如果经常在 Microsoft Excel 中重复某项任务,那么可以用宏自动执行该任务。宏是一系列命令和函数,存储于 Visual Basic 模块中,并且在需要执行该项任务时可随时运行。 例

25、如,如果经常在单元格中输入长文本字符串,则可以创建一个宏来将单元格格式设置为文本可自动换行。 录制宏录制宏 在录制宏时,Excel 在您执行一系列命令时存储该过程的每一步信息。然后即可运行宏来重复所录制的过程或“回放”这些命令。如果在录制宏时出错,所做的修改也会被录制下来。Visual Basic 在附属于某工作薄的新模块中存储每个宏。 60 附加内容附加内容宏的录制与运用宏的录制与运用 使宏易于运行使宏易于运行 可以在“宏”对话框的列表中选择所需的宏并运行宏。如果希望通过单击特定按钮或按下特定组合键来运行宏,可将宏指定给某个工具栏按钮、键盘快捷键或工作表中的图形对象。 管理宏管理宏 宏录制完

26、后,可用 Visual Basic 编辑器查看宏代码以进行改错或更改宏的功能。例如,如果希望用于文本换行的宏还可以将文本变为粗体,则可以再录制另一个将单元格文本变为粗体的宏,然后将其中的指令复制到用于文本换行的宏中。 “Visual Basic 编辑器”是一个为初学者设计的编写和编辑宏代码的程序,而且提供了很多联机帮助。不必学习如何编程或如何用 Visual Basic 语言来对宏进行简单的修改。利用“Visual Basic 编辑器”,您可以编辑宏、在模块间复制宏、在不同工作簿之间复制宏、重命名存储宏的模块或重命名宏。 61 附加内容附加内容宏的录制与运用宏的录制与运用 宏安全性宏安全性 E

27、xcel 对可通过宏传播的病毒提供安全保护。如果您与其他人共享宏,则可使用数字签名来验证其他用户,这样就可保证其他用户为可靠来源。无论何时打开包含宏的工作簿,都可以先验证宏的来源再启用宏。 62 附加内容附加内容宏的录制与运用宏的录制与运用 创建宏创建宏 录制宏 将安全级设置为“中”或“低”。 操作方法 在“工具”菜单上,单击“选项”。 单击“安全性”选项卡。 在“宏安全性”之下,单击“宏安全性”。 单击“安全级”选项卡,再选择所要使用的安全级。 在“工具”菜单上,指向“宏”,再单击“录制新宏”。 在“宏名”框中,输入宏 的名称。 注意注意 宏名的首字符必须是字母,其他字符可以是字母、数字或下

28、划线。宏名中不允许有空格；可用下划线作为分词符。 宏名不允许与单元格引用重名,否则会出现错误信息显示宏名无效。 如果要通过按键盘上的来运行宏, 请在“快捷键”框中,输入一个字母。可用 Ctrl+字母（小写字母）或 Ctrl+Shift+字母（大写字母）,其中字母可以是键盘上的任意字母键。快捷键字母不允许是数字或特殊字符（如 或 #）。 注释注释 当包含宏的工作簿打开时,宏快捷键优先于任何相当的 Microsoft Excel 的默认快捷键。 63 附加内容附加内容宏的录制与运用宏的录制与运用 在“保存在”框中,单击要存放宏的地址。 如果要使宏在使用 Excel 的任何时候都可用,请选中“个人宏

29、工作簿”。 如果要添加有关宏的说明,请在“说明”框中键入该说明。 单击“确定”。 如果要使宏相对于活动单元格位置运行,请用相对单元格引用来录制该宏。在“停止录制”工具栏上,单击“相对引用” 以将其选中。Excel 将继续用“相对引用”录制宏,直至退出 Excel 或再次单击“相对引用” 以将其取消。 执行需要录制的操作。 在“停止录制”工具栏上,单击“停止录制” 。 用 Microsoft Visual Basic 创建宏 在 Microsoft Excel 的“工具”菜单上,指向“宏”,再单击“Visual Basic 编辑器”。 在“插入”菜单上,单击“模块”。 将代码键入或复制到模块的代

30、码窗口中。 如果要在模块窗口中运行宏 请按 F5。 编写完宏后,请单击“文件”菜单上的“关闭并返回到 Microsoft Excel” 。 64 附加内容附加内容宏的录制与运用宏的录制与运用 创建启动宏 自动例如：Auto_Activate）是在启动 Excel 时自动运行的。有关自动宏的详细信息,请参阅 Visual Basic“ 帮助” 复制宏的一部分以创建另一个宏 将安全级设置为“中”或“低”。 请打开要复制的宏所在的工作簿。 在“工具”菜单上,指向“宏”,再单击“宏”。 在“宏名”框中,输入要复制的宏的名称。 单击“编辑”。 在宏中选取要复制的程序行。 若要复制整个宏,请确认在选定区域中包括了“Sub” 和“End Sub” 行。 在“常用”工具栏 上,单击“复制” 。 切换到要放置代码的模块。 单击“粘贴” 。 提示 您可在任何时候通过在 Visual Basi