HR的EXCEL技巧.pptx

HR的EXCEL技巧.pptx

《HR的EXCEL技巧.pptx》由会员分享，可在线阅读，更多相关《HR的EXCEL技巧.pptx（69页珍藏版）》请在冰豆网上搜索。

1ClerkOffice2003TrainingMaterial文员Office2003培训教材Exceltrainingmanual-IntermediateMaterialExcel培训手册中级班教材培训手册中级班教材2使用公式使用公式公式中的运算符公式中的运算符公式中的运算顺序公式中的运算顺序引用单元格引用单元格创建公式创建公式创建公式的方法创建公式的方法创建简单公式创建简单公式创建包含引用或名称的公式创建包含引用或名称的公式创建包含函数的公式创建包含函数的公式使用函数使用函数常用函数常用函数财务函数财务函数日期和时间函数日期和时间函数数学与三角函数数学与三角函数统计函数统计函数函数的输入函数的输入手工输入手工输入利用函数向导输入利用函数向导输入求和计算求和计算扩充的自动求和扩充的自动求和条件求和条件求和建立数据清单建立数据清单建立数据清单的准则建立数据清单的准则使用记录单使用记录单数据排序数据排序对数据清单进行排序对数据清单进行排序创建自定义排序创建自定义排序数据筛选数据筛选自动筛选自动筛选高级筛选高级筛选分类汇总分类汇总分类汇总的计算方法分类汇总的计算方法汇总报表和图表汇总报表和图表插入和删除分类汇总插入和删除分类汇总插入图表表示数据插入图表表示数据复杂的数据链接复杂的数据链接使用数据透视表使用数据透视表创建数据透视表创建数据透视表编辑数据透视表编辑数据透视表controlltoolboxusage（控件工具箱控件工具箱）课程大纲课程大纲3使用公式使用公式公式中的运算符公式中的运算符1.1.算术运算符算术运算符算术运算符号算术运算符号运算符含义运算符含义示例示例+（加号加号）加加2+3=5-（减号减号）减减3-1=2*（星号星号乘乘3*2=6/（斜杠斜杠）除除6/2=3%（百分号百分号）百分号百分号50%（脱字号脱字号）乘方乘方43=43=644使用公式使用公式公式中的运算符公式中的运算符2.文本运算符文本运算符“&”号号,可以将文本连接起来可以将文本连接起来.3.比较运算符比较运算符比较运算符比较运算符运算符含义运算符含义示例示例=（等号等号）相等相等B1=C1,若若B1中单元格内的值确实与中单元格内的值确实与C1中的中的值相等值相等,则产生逻辑真值则产生逻辑真值TRUE,若不相等若不相等,则产则产生逻辑假值生逻辑假值FALSE（小于号小于号）小于小于B1（大于号大于号）大于大于B1C1,若若B1中数值为中数值为6,C1中数值为中数值为4,则条则条件成立产生逻辑真值件成立产生逻辑真值TRUE,否则产生逻辑假值否则产生逻辑假值FALSE=（大于等于号大于等于号）大于等于大于等于B1=C1（不等号不等号）不等于不等于B1C1=（小于等于号小于等于号）小于等于小于等于B11350姓名部门基本工资奖金所得税实发工资王小明销售1300250401510张力浩销售1200200201380祁春销售120020030137033数据的筛选数据的筛选高级筛选高级筛选第一步第一步:

建立条件区域建立条件区域;输入筛选条件输入筛选条件:

4.两列上具有两组条件之一若要找到满足两组条件（每一组条件都包含针对多列的条件）之一的数据行，请在各行中键入条件5.一列有两组以上条件若要找到满足两组以上条件的行，请用相同的列标包括多列。

条件：

所指定的限制查询或筛选的结果集中包含哪些记录的条件。

姓名部门基本工资奖金所得税实发工资王小明销售1300250401510李大双销售1150150501250张力浩销售1200200201380祁春销售1200200301370部门实发工资销售1350姓名部门基本工资奖金所得税实发工资王小明销售1300250401510张力浩销售1200200201380祁春销售120020030137034数据的筛选数据的筛选第二步：

选定需要筛选的数据清单中任一单元格；第三步：

选择菜单”Data/Filter/AdvancedFilter”第四步：

在“条件区域”编辑框中，输入条件区域的引用，并包括条件标志。

第五步：

若要更改筛选数据的方式，可更改条件区域中的值，并再次筛选数据。

注：

如果需要将筛选结果放到另一位置，请选择”CopytoanotherLocation”35数据的筛选数据的筛选请使用若要查找?

（问号）任何单个字符例如，sm?

th查找“smith”和“smyth”*（星号）任何字符数例如，*east查找“Northeast”和“Southeast”（波形符）后跟?

、*或问号、星号或波形符例如，“fy91?

”将会查找“fy91?

”通配符通配符以下通配符可作为筛选以及查找和替换内容时的比较条件:

36分类汇总的概述分类汇总的概述分类汇总是对数据清单进行数据分析的一种方分类汇总是对数据清单进行数据分析的一种方法法.Data/SubtotalData/Subtotal分类汇总分类汇总371.确保要进行分类汇总的数据为下列格式：

第一行的每一列都有标志，并且同一列中应包含相似的数据，在区域中没有空行或空列。

2.单击要分类汇总的列中的单元格。

在上面的示例中，应单击“运动”列（列B）中的单元格。

3.单击“升序排序”或“降序排序”。

4.在“数据”菜单上，单击“分类汇总”。

5.在“分类字段”框中，单击要分类汇总的列。

在上面示例中，应单击“运动”列。

6.在“汇总方式”框中，单击所需的用于计算分类汇总的汇总函数。

7.在“选定汇总项”框中，选中包含了要进行分类汇总的数值的每一列的复选框。

在上面的示例中，应选中“销售”列。

分类汇总分类汇总要分类汇总的列分类汇总步骤步骤:

388.如果想在每个分类汇总后有一个自动分页符，请选中“每组数据分页”复选框。

9.如果希望分类汇总结果出现在分类汇总的行的上方，而不是在行的下方，请清除“汇总结果显示在数据下方”复选框。

10.单击“确定”。

11.注释可再次使用“分类汇总”命令来添加多个具有不同汇总函数的分类汇总。

若要防止覆盖已存在的分类汇总，请清除“替换当前分类汇总”复选框。

分类汇总分类汇总要分类汇总的列分类汇总39删除分类汇总删除分类汇总删除分类汇总时，MicrosoftExcel也删除分级显示以及随分类汇总一起插入列表中的所有分页符。

在含有分类汇总的列表中，单击任一单元格。

在“数据”菜单上，单击“分类汇总”。

单击“全部删除”。

分类汇总分类汇总40删除分类汇总删除分类汇总删除分类汇总时，MicrosoftExcel也删除分级显示以及随分类汇总一起插入列表中的所有分页符。

在含有分类汇总的列表中，单击任一单元格。

在“数据”菜单上，单击“分类汇总”。

单击“全部删除”。

分类汇总分类汇总411.1.选中所需要的数据选中所需要的数据:

如图如图数据透视表的使用数据透视表的使用数据透视表是交互式报表，可快速合并和比较大量数据。

您可旋转其行和列以看到源数据的不同汇总，而且可显示感兴趣区域的明细数据。

源数据第三季度高尔夫汇总的源值数据透视表C2和C8中源值的汇总42数据透视表的使用数据透视表的使用（续续1）1）何时应使用数据透视表何时应使用数据透视表如果要分析相关的汇总值，尤其是在要合计较大的列表并对每个数字进行多种比较时，可以使用数据透视表。

在上面所述报表中，用户可以很清楚地看到单元格F3中第三季度高尔夫销售额是如何通过其他运动或季度的销售额或总销售额计算出来的。

由于数据透视表是交互式的，因此，您可以更改数据的视图以查看更多明细数据或计算不同的汇总额，如计数或平均值。

数据是如何组织的数据是如何组织的在数据透视表中，源数据中的每列或字段都成为汇总多行信息的数据透视表字段。

在上例中，“运动”列成为“运动”字段，高尔夫的每条记录在单个高尔夫项中进行汇总。

数据字段（如“求和项：

销售额”）提供要汇总的值。

上述报表中的单元格F3包含的“求和项：

销售额”值来自源数据中“运动”列包含“高尔夫”和“季度”列包含“第三季度”的每一行。

43数据透视表的使用数据透视表的使用（续续2）2）创建数据透视表创建数据透视表打开要创建数据透视表的工作簿。

请单击列表或数据库中的单元格。

在“数据”菜单上，单击“数据透视表和数据透视图”。

在“数据透视表和数据透视图向导”的步骤1中，遵循下列指令，并单击“所需创建的报表类型”下的“数据透视表”。

按向导步骤2中的指示进行操作。

按向导步骤3中的指示进行操作，然后决定是在屏幕上还是在向导中设置报表版式。

通常，可以在屏幕上设置报表的版式，推荐使用这种方法。

只有在从大型的外部数据源缓慢地检索信息，或需要设置页字段来一次一页地检索数据时，才使用向导设置报表版式。

如果不能确定，请尝试在屏幕上设置报表版式。

如有必要，可以返回向导。

44在屏幕上设置报表版式在屏幕上设置报表版式从“数据透视表字段列表”窗口中，将要在行中显示数据的字段拖到标有“将行字段拖至此处”的拖放区域。

如果没有看见字段列表，请在数据透视表拖放区域的外边框内单击，并确保“显示字段列表”被按下。

若要查看具有多个级别的字段中哪些明细数据级别可用，请单击该字段旁的。

对于要将其数据显示在整列中的字段，请将这些字段拖到标有“将列字段拖至此处”的拖放区域。

对于要汇总其数据的字段，请将这些字段拖到标有“请将数据项拖至此处”的区域。

只有带有或图标的字段可以被拖到此区域。

如果要添加多个数据字段，则应按所需顺序排列这些字段，方法是：

用鼠标右键单击数据字段，指向快捷菜单上的“顺序”，然后使用“顺序”菜单上的命令移动该字段。

将要用作为页字段的字段拖动到标有“请将页字段拖至此处”的区域。

若要重排字段，请将这些字段拖到其他区域。

若要删除字段，请将其拖出数据透视表。

若要隐藏拖放区域的外边框，请单击数据透视表外的某个单元格。

注释如果在设置报表版式时，数据出现得很慢，则请单击“数据透视表”工具栏上的“始终显示项目”来关闭初始数据显示。

如果检索还是很慢或出现错误信息，请单击“数据”菜单上的“数据透视表和数据透视图”，在向导中设置报表布局。

数据透视表的使用数据透视表的使用（续续）45在向导中设置报表布局在向导中设置报表布局如果已经从向导中退出，则请单击“数据”菜单上的“数据透视表和数据透视图”以返回该向导中。

在向导的步骤3中，单击“布局”。

将所需字段从右边的字段按钮组拖动到图示的“行”和“列”区域中。

对于要汇总其数据的字段，请将这些字段拖动到“数据”区。

将要作为页字段使用的字段拖动到“页”区域中。

如果希望Excel一次检索一页数据，以便可以处理大量的源数据，请双击页字段，单击“高级”，再单击“当选择页字段项时，检索外部数据源”选项，再单击“确定”按钮两次。

（该选项不可用于某些源数据，包括OLAP数据库和“Office数据连接”。

）若要重排字段，请将它们拖到其他区域。

某些字段只能用于某些区域；如果将一个字段拖动到其不能使用的区域，该字段将不会显示。

若要删除字段，请将其拖到图形区之外。

如果对版式满意，可单击“确定”，然后单击“完成”。

数据透视表的使用数据透视表的使用（续续）46删除数据透视表删除数据透视表单击数据透视表。

在“数据透视表”工具栏上，单击“数据透视表”，指向“选定”，再单击“整张表格”。

在“编辑”菜单上，指向“清除”，再单击“全部”。

注释对于数据透视图报表，删除与其相关的数据透视表，将会冻结图表，使得不可再对其进行更改。

数据透视表的使用数据透视表的使用（续续）47制作与使用图表制作与使用图表创建图表图表是Excel为用户提供的强大功能,通过创建各种不同类型的图表,为分析工作表中的各种数据提供更直观的表示结果。

l使用图表向导下面我们先在工作薄中建立数据表如下页图所示,根据工作表中的数据,创建不同类型的图表。

l图表的编辑1.插入数据标志及增加图表标题2.改变图表的文字、颜色和图案48图49操作步骤如下：

1）选定工作表中包含所需数据的所有单元格。

2）单击常用工具栏中的图表向导按钮,弹出图（1-16）所示的对话框：

图表的使用图表的使用50图表的使用（续图表的使用（续11）33）在显示的图表向导中）在显示的图表向导中,你可以选择最合适的图表类型你可以选择最合适的图表类型,单击单击standardtypestandardtype标签标签,在在charttypecharttype下拉式列表框中选择任何一种图表类型下拉式列表框中选择任何一种图表类型,再在其相应的再在其相应的子图表类型子图表类型的样式中选择其中一种子图表类型的样式中选择其中一种子图表类型,然后按下然后按下查看示例查看示例按钮不放按钮不放,就可预览所就可预览所选类型的示例。

单击下一步按钮选类型的示例。

单击下一步按钮,弹出如图的对话框：

弹出如图的对话框：

51图表的使用（续图表的使用（续22）4）然后继续单击下一步弹出图对话框选择图表存放的位置,若要将图表放到另一个新的工作表上,请选中Asnewsheet单选框,然后作为新工作表插入文本框,键入新工作表的名字,默认为,如果选择Asobjectin单选框,可将：

52图（图（1-181-18）图表插入到当前打开的工作表中图表插入到当前打开的工作表中,接着再单击完成按钮接着再单击完成按钮,便可得到如图便可得到如图所示的图表。

所示的图表。

53图表的编辑图表的编辑一、插入数据标志及增加图表标题一、插入数据标志及增加图表标题加入数据标志的过程如下：

加入数据标志的过程如下：

1.1.激活要添加数据标志的图表激活要添加数据标志的图表2.2.单击单击chartchart菜单中的菜单中的chartoptionschartoptions命令命令,弹出如图弹出如图（1-19）（1-19）图表标题分类X轴数值Y轴54图（图（1-201-20）单击单击datalabels,datalabels,出现如图出现如图,择所需选项即可。

择所需选项即可。

55数据标志的解说数据标志的解说数据标志数据标志说明说明categorynamecategoryname显示指定到数据点的分类显示指定到数据点的分类valuevalue显示数据点的值显示数据点的值percentagepercentage对于饼图、圆环图显示出对整体的百分比对于饼图、圆环图显示出对整体的百分比bubblesizebubblesize对于离散图、气泡图显示出气泡的百分比对于离散图、气泡图显示出气泡的百分比56二、改变图表的文字、颜色和图案二、改变图表的文字、颜色和图案图表的编辑图表的编辑若要对标题进行编辑,可将鼠标移至标题的左下角处双击,即会出现右图所示对话框,用该对话框可以对标题的字体、字体的背景色等进行修改。

57图图（1-23）（1-23）若要对图表区域进行修改,可将鼠标移至图表的空白区域双击鼠标,则出现图（1-23）对话框,在此对话框中可选定要修改的内容。

58图图（1-24）（1-24）若要对图例进行修改,可将鼠标移至图例区双击鼠标,则出现如图（1-24）对话框。

在此对话框中可对图例进行编辑,如图案、字体及位置。

59附加内容附加内容-控件工具箱控件工具箱ControlBox复选框复选框可通过选中或清除来打开或关闭的选项。

您可以在一个工作表中同时选中多个复选框。

文本框文本框可以向其中键入文字的框。

命令按钮命令按钮单击可启动某项操作的按钮。

选项按钮选项按钮用于从一组选项中选择某一选项的按钮。

列表框列表框包含项目列表的框。

组合框组合框含有下拉列表框的文本框。

您可以从列表中选择,或在框中键入所需内容。

切换按钮切换按钮单击后保持按下状态,再单击时又弹起的按钮。

微调按钮微调按钮可附加在单元格上或文本框上的按钮。

若要增加数值,请单击向上箭头；若要减小数值,请单击向下箭头。

滚动条滚动条可通过单击滚动箭头或拖动滚动块来滚动数据区域的一种控件。

单击滚动箭头与滚动块之间的区域时,可以滚动整页数据。

标签标签添加到工作表或窗体中,用以提供有关控件、工作表或窗体信息的文本。

图像图像将图片嵌入到窗体中的控件。

其他控件其他控件其他ActiveX控件的列表。

60附加内容宏的录制与运用附加内容宏的录制与运用关于宏关于宏如果经常在MicrosoftExcel中重复某项任务,那么可以用宏自动执行该任务。

宏是一系列命令和函数,存储于VisualBasic模块中,并且在需要执行该项任务时可随时运行。

例如,如果经常在单元格中输入长文本字符串,则可以创建一个宏来将单元格格式设置为文本可自动换行。

录制宏录制宏在录制宏时,Excel在您执行一系列命令时存储该过程的每一步信息。

然后即可运行宏来重复所录制的过程或“回放”这些命令。

如果在录制宏时出错,所做的修改也会被录制下来。

VisualBasic在附属于某工作薄的新模块中存储每个宏。

61附加内容宏的录制与运用附加内容宏的录制与运用使宏易于运行使宏易于运行可以在“宏”对话框的列表中选择所需的宏并运行宏。

如果希望通过单击特定按钮或按下特定组合键来运行宏,可将宏指定给某个工具栏按钮、键盘快捷键或工作表中的图形对象。

管理宏管理宏宏录制完后,可用VisualBasic编辑器查看宏代码以进行改错或更改宏的功能。

例如,如果希望用于文本换行的宏还可以将文本变为粗体,则可以再录制另一个将单元格文本变为粗体的宏,然后将其中的指令复制到用于文本换行的宏中。

“VisualBasic编辑器”是一个为初学者设计的编写和编辑宏代码的程序,而且提供了很多联机帮助。

不必学习如何编程或如何用VisualBasic语言来对宏进行简单的修改。

利用“VisualBasic编辑器”,您可以编辑宏、在模块间复制宏、在不同工作簿之间复制宏、重命名存储宏的模块或重命名宏。

62附加内容宏的录制与运用附加内容宏的录制与运用宏安全性宏安全性Excel对可通过宏传播的病毒提供安全保护。

如果您与其他人共享宏,则可使用数字签名来验证其他用户,这样就可保证其他用户为可靠来源。

无论何时打开包含宏的工作簿,都可以先验证宏的来源再启用宏。

63附加内容宏的录制与运用附加内容宏的录制与运用创建宏创建宏录制宏将安全级设置为“中”或“低”。

操作方法在“工具”菜单上,单击“选项”。

单击“安全性”选项卡。

在“宏安全性”之下,单击“宏安全性”。

单击“安全级”选项卡,再选择所要使用的安全级。

在“工具”菜单上,指向“宏”,再单击“录制新宏”。

在“宏名”框中,输入宏的名称。

注意注意宏名的首字符必须是字母,其他字符可以是字母、数字或下划线。

宏名中不允许有空格；可用下划线作为分词符。

宏名不允许与单元格引用重名,否则会出现错误信息显示宏名无效。

如果要通过按键盘上的来运行宏,请在“快捷键”框中,输入一个字母。

可用Ctrl+字母（小写字母）或Ctrl+Shift+字母（大写字母）,其中字母可以是键盘上的任意字母键。

快捷键字母不允许是数字或特殊字符（如或#）。

注释注释当包含宏的工作簿打开时,宏快捷键优先于任何相当的MicrosoftExcel的默认快捷键。

64附加内容宏的录制与运用附加内容宏的录制与运用在“保存在”框中,单击要存放宏的地址。

如果要使宏在使用Excel的任何时候都可用,请选中“个人宏工作簿”。

如果要添加有关宏的说明,请在“说明”框中键入该说明。

单击“确定”。

如果要使宏相对于活动单元格位置运行,请用相对单元格引用来录制该宏。

在“停止录制”工具栏上,单击“相对引用”以将其选中。

Excel将继续用“相对引用”录制宏,直至退出Excel或再次单击“相对引用”以将其取消。

执行需要录制的操作。

在“停止录制”工具栏上,单击“停止录制”。

用MicrosoftVisualBasic创建宏在MicrosoftExcel的“工具”菜单上,指向“宏”,再单击“VisualBasic编辑器”。

在“插入”菜单上,单击“模块”。

将代码键入或复制到模块的代码窗口中。

如果要在模块窗口中运行宏请按F5。

编写完宏后,请单击“文件”菜单上的“关闭并返回到MicrosoftExcel”。

65附加内容宏的录制与运用附加内容宏的录制与运用创建启动宏自动例如：

Auto_Activate）是在启动Excel时自动运行的。

有关自动宏的详细信息,请参阅VisualBasic“帮助”复制宏的一部分以创建另一个宏将安全级设置为“中”或“低”。

请打开要复制的宏所在的工作簿。

在“工具”菜单上,指向“宏”,再单击“宏”。

在“宏名”框中,输入要复制的宏的名称。

单击“编辑”。

在宏中选取要复制的程序行。

若要复制整个宏,请确认在选定区域中包括了“Sub”和“EndSub”行。

在“常用”工具栏上,单击“复制”。

切换到要放置代码的模块。

单击“粘贴”。

提示您可在任何时候通过在VisualBasic编辑器（Alt+F11）中打开个人宏工作簿文件（Personal.xls）来对其进行查看。

由于Personal.xls是一个总是打开着的隐藏工作簿,所以如果要复制其中的某个宏,必须先显示该工作簿。

66编辑宏编辑宏在编辑宏之前在编辑宏之前,必须先熟悉“必须先熟悉“VisualBasic编辑器”。

“编辑器”。

“VisualBasic编辑编辑器”能够用于编写和编辑附属于器”能够用于编写和编辑附属于MicrosoftExcel工作簿的宏。

工作簿的宏。

将安全级设置为“中”或“低”。

操作方法将安全级设置为“中”或“低”。

操作方法在“工具”菜单上在“工具”菜单上,单击“选项”。

单击“选项”。

单击“安全性”选项卡。

单击“安全性”选项卡。

在“宏安全性”之下在“宏安全性”之下,单击“宏安全性”。

单击“宏安全性”。

单击“安全级”选项卡单击“安全级”选项卡,再选择所要使用的安全级。

再选择所要使用的安全级。

在“工具”菜单上在“工具”菜单上,指向“宏”指向“宏”,再单击“宏”。

再单击“宏”。

在“宏名”框中在“宏名”框中,输入宏的名称。

输入宏的名称。

单击“编辑”。

单击“编辑”。

如果需要“如果需要“VisualBasic编辑器”的“帮助”编辑器”的“帮助”,请在“帮助”菜单上请在“帮助”菜单上,单单击“击“MicrosoftVisualBasic帮助”。

帮助”。

附加内容宏的录制与运用附加内容宏的录制与运用67删除宏删除宏打开含有要删除的宏的工作簿。

打开含有要删除的宏的工作簿。

在“工具”菜单上在“工具”菜单上,指向“宏”指向“宏”,再单击“宏”。

再单击“宏”。

在“位置”列表中在“位置”列表中,单击“当前工作簿”。

单击“当前工作簿”。

在“宏名”框中在“宏名”框中,单击要删除的宏的名称。

单击要删除的宏的名称。

单击“删除”。

单击“删除”。

附加内容宏的录制与运用附加内容宏的录制与运用68附加内容宏的录制与运用附加内容宏的录制与运用停止运行宏停止运行宏请执行下列操作之一：

请执行下列操作之一：

如果要停止当前正在运行的宏如果要停止当前正在运行的宏,请按请按Esc,在“在“MicrosoftVisualBasic”对话框中单击“结束”。

对话框中单击“结束”。

如果要防止在启动如果要防止在启动MicrosoftExcel时自动运行某个宏时自动运行某个宏,请在启动时请在启动时,按住按住Shift。

69课程结束课程结束!

谢谢谢谢!