EXCEL在人力资源管理中的应用案例与技巧

1、Exceel在管理理中的应应用案例011第1部分分 Exxcell基础概概念名称名称可以以代表一一个单元元格或者者一个单单元格区区域，或或者是常常量，公公式。名名称的定定义：选选中需要要命名的的单元格格或区域域，在界界面左上上角名称称框中输输入名称称后回车车；名称称的删除除：插入入名称称定义义，选中中需要删删除的名名称点击击删除按按钮，点点确定。名称的的引用：需要引引用某单单元格时时输入该该单元格格的名称称：名名称第1步：选选中需要要命名的的某个单单元格或或单元格格区域。第2步：在左上上角名称称框输入入命名后后回车。删除已有有命名：选择“插入”“名称”“定义”；选中中需要删删除的名名称，点点击

2、“删除”按钮。名称命名名的优点点：避免绝对对引用的的错误对公式进进行文字字化表述述，让公公式更加加容易理理解可以在整整个工作作簿中通通用，引引用方便便常用引用用函数rrow: 返回指指定单元元格的行行号collumnn: 返回指指定单元元格的列列标mattch: 返回查查找值在在查找范范围中的的序号 =mattch(查找值值，查找找范围，0) 其第三三个参数数为0，表示示查找精精确值adddresss: 返回单单元格名名称，其其参数为为行号列列标或计计算行号号列标的的表达式式 =adddreess(行号，列列标) =aaddrresss(1,1) 此公式式返回A1iindiirecct: 返回

3、单单元格的的值.其参数数为单元元格名称称 =inndirrectt(“A1”),假设A1=10,则inddireect返回10iindeex: 在某区区域内查查找某个个位置的的值 =inddex(查找区区域，查查找值所所在的行行号，查查找值所所在的列列号)offfseet：指定基基点，指指定位移移量，得得到单元元格引用用 单个单单元格引引用：=offfseet(基点单单元格，向向下移动动的行，向向右移动动的列) 区域的的引用：=offfseet(基点单单元格，向向下移动动的行，向向右移动动的列，区区域 包括的的行数，区区域包括括的列数数) 向上和和向左移移动时，位位移量为为负值。 =offfs

4、ett(A11,1,1) 将得到B2单元格格的值动态引用用动态引引用是通通过引用用函数实实现对于于单元格格或区域域的相对对引用。它和相相对引用用的效果果很相似似，但比比简单的的相对引引用用途途广泛。常用的的动态引引用的实实现方法法有：引引用函数数嵌套。比如offfsett和roww,coolummn嵌套；inddex和mattch，inddireect,adddresss嵌套等等；引用用函数与与控件结结合使用用，此类类控件包包括组合合框，滚滚动条。动态引引用的作作用：实实现对于于单元格格的动态态引用；进行动动态分析析；制作作动态报报表。运算类型型数值运运算：1 + 1 = 22逻辑运运算：1

5、0 = TTRUEE在逻辑辑运算中中，TRUUE=11，FALLSE=0我们们利用逻逻辑运算算进行条条件判断断在Exccel中常用用的一些些逻辑函函数：if,andd,orr,noot,iiserrrorr等andd: 只有当当所有条条件全部部满足，才才会返回回truue的逻辑辑值 语法：andd(条件1，条件2，)orr: 满足其其中任何何一个条条件，都都会返回回truue的逻辑辑值 语法：or(条件1，条件2，条件3,)函数调用用的语法法函数数名称（参参数1，参数2，）绝对引用用和相对对引用：绝对引用用：所引引用的单单元格不不随着公公式的复复制而移移动的引引用方式式。相对引用用：所引引用的

6、单单元格随随着公式式的复制制而移动动的引用用方式。改变引引用方式式的方法法：在公式栏栏中选中中需要改改变引用用方式的的单元格格，按F4键给需要引引用的单单元格定定义名称称，然后后在公式式中引用用该名称称数组公式式对单元元格区域域进行多多重计算算的计算算方式。与普通通计算公公式的区区别是录录入公式式结束后后，需要要同时按按下 cttrl + sshifft + ennterr ，其特特征是在在公式两两端会出出现一对对大括号号。如上上图例，使使用一个个公式计计算出所所有产品品的金额额合计，引引用的是是所有的的单价和和所有的的数量，执执行的是是多重计计算。错误提示示Exccel中存在在错误的的类型，

7、比比如1/00=#DDIV/0!, toodass()=#NAAMESS!isserrror是一个个逻辑函函数，用用以判断断某个单单元格内内的值是是否是一一个错误误，是错错误则返返回TRUUE,不是错错误则返返回FALLSE.iseerroor有时可可以和if函数嵌嵌套进行行一些较较为复杂杂的判断断。有取值区区间的随随机数=最小值值（最最大值最小值值）*raand()循环引用用是单元元格引用用其自身身的引用用方式。可以设设置Exccel允许进进行循环环引用：工具-选项-重新计计算：将将迭代计计算选项项打钩选选中。行列互换换（函数数方式）：利用traanspposee函数+数组公公式实现现。首先

8、先选中行行列数和和原区域域相反的的一个区区域；然然后输入入traanspposee函数最最后按下下组合键键ctrrl+sshifft+eenteer第2部分分Exccel设置显示当前前文件的完整路径径菜单区区域右右键菜单单Webb显示菜单单项全部部菜单视视图工工具栏自定义义选项项始终终显示整整个菜单单鼠标移动动方向工工具选选项编编辑按按Entter键后移移动方向向隐藏界面面要素工工具选选项视视图包括括：网格格线，滚滚动条，工工作表标标签，行行号列标标等。以显示值值为准工工具选选项重重新计算算以显显示精度度为准行的合计计与列的的合计有有时出现现不相等等的情况况。可以采用用以下方方法解决决。选择“

9、工工具”“选项”“重新计计算”“以显示示精度为为准”，选中中该选项项。自定义序序列工具具选项项自定定义序列列改变文件件保存位位置工具具选项项常规规默认认文件位位置改变文件件用户名名工具选项用户名名单元格自自动换行行格式单元格格对齐齐自动动换行第3部分分 Exccel基础操操作快速选中中数据表表的整行行或者整整列ctrrl + shhiftt + 下箭头/右箭头头快速选中中区域ctrrl + shhiftt + 8行列互换换复制需需要进行行行列互互换的区区域后，将光标放置在数据表外面位置，选择性粘贴选中“转置”选项一个单元元格内容容输入为为多行altt + 回车键键显示公式式ctrrl + 冻结

10、窗口口选择需需要进行行冻结的的单元格格位置，选选择窗口口冻结结窗格自定义格格式语法法： 大于条条件值格式；小于条条件值格式；等于条条件值格式；文本格格式缩放数值值 00.000， 按百万万缩放 00“.”0，按万万缩放 00.000, 按千缩缩放不复制隐隐藏的行行或列a首先选选中需要要复制的的被隐藏藏了一些些行或列列的表格格区域；b 然后点点击“编辑”-“定位”-“定位条条件”，在其其中选择择“可见单单元格”；c 复制表表格区域域，粘贴贴即可。案例022 一 文本本的处理理等长文本本的分割割从字符符串的左左边取字字符：=Leftt(字符串串，文本本长度)从字符符串的右右边取字字符：=Righh

11、t(字符串串，文本本长度)从字符符串的中中间取字字符：=Mid(字符串串，文本本起始位位置，文文本长度度)不等长文文本的分分割第1步：选选中要进进行分割割的字符符串区域域。第2步：点击“数据”“分列”，在文文本分列列向导中中选择适适合的分分隔符。第3步：设置需需要导入入的列以以及放置置该列的的位置。文本的合合并=CConccateenatte(文本1，文本2，)使用连连接符&：动态态表头：=AABC公司&YYEARR(NOOW()&年&MMONTTH(NNOW()&月报表有效性选选中需要要设置有有效性的的区域，点点击“数据”“有效性”，在有有效性条条件中选选择“序列”，在来来源中录录入或选选取

12、列表表。输入信息息：事前前提醒。出错警告告：事后后提示。如果待选选列表不不在当前前工作表表中，需需要首先先为该列列表定义义名称，然然后在有有效性来来源框中中输入：“=该列表表的名称称”有效性的的其他用用法不允许录录入重复复数据的的有效性性设定：couuntiif(ee:e,e122)=11输入的内内容中必必须包括括某字符符：=noot(iiserrrorr(fiind(中国,gg30)二级选项项：首先先将一级级选项的的每个项项目定义义一个名名称，该该名称内内容包括括相应的的二级项项目；制作一级级项目的的有效性性；制作二级级项目的的有效性性：内容容为：=INDDIREECT(g6)，其中g6为设

13、定定了有效效性的一一级选项项所在的的单元格格。圈示无效效数据。选择“工工具”“公式审审核”“显示公公式审核核工具栏栏”，点击“圈示无无效数据据”按钮。该工具可可以将所所有不符符合有效效性设定定的内容容圈示出出来。二日期的的处理Dateediff 函数：用于计计算两个个日期之之间的年年数，月月数，天天数。=Dateediff(开始日日期，结结束日期期，“y”)第三个个参数：“y”：表示示年数“m”：表示示月数“d”：表示示天数生日提醒醒=DAATEDDIF(TODDAY(),DDATEE(YEEAR(TODDAY(),MONNTH(G2),DAAY(GG2),dd)某日期的的星期=weeekda

14、ay(AA2,22) 返回的的值是3，则表表示该日日期是星星期三。两个日期期间的工工作日天天数=neetwoorkddayss(开始日日期，结结束日期期，节假假日列表表)需要首首先加载载“分析工工具库”才能使使用此工工具案例033数据查查询VLOOOKUPP（）VLOOOKUPP在表格格或数值值数组的的首列查查找指定定的数值值，并由由此返回回表格或或数组当当前行中中指定列列处的数数值。VLOOOKUUP 中的 V 代表垂垂直。VLOOOKUPP(loookuup_vvaluue,ttablle_aarraay,ccol_inddex_numm,raangee_loookuup)Lookkup_

15、vallue 为需要要在数组组第一列列中查找找的数值值。Loookupp_vaaluee 可以为为数值、引用或或文本字字符串。也可以以理解为为：两表表共有的的索引字字段。Tablle_aarraay 为需要要在其中中查找数数据的数数据表。可以使使用对区区域或区区域名称称的引用用，例如如数据库库或列表表。必须须使得共共有字段段位于该该范围的的第一列列。col_inddex_numm 为需要要调转的的数据位位于第二二个参数数中定义义的范围围的第几几列。必必须为单单纯数值值。rangge_llookkup 定义大大致匹配配或精确确匹配。Falsse或0:精确匹匹配；truue或忽略略或1：如果无无法

16、找到到精确匹匹配的值值，那么么就查找找并匹配配比查找找值小的的最近似似的值。HLOOOKUPP()HHloookupp中的H是水平平的意思思，在横横向排列列的表格格中查询询数据用用Hloookuup，垂直直排列的的表格查查询用Vloookuup函数。其参数数含义与与VLOOOKUUP近似。案例044 查询表表信息查询询表利用用有效性性和VLOOOKUUP函数实实现。利利用有效效性实现现索引字字段的切切换。利利用VLOOOKUUP函数查查询与索索引字段段相关的的信息。案例055 数据筛筛选自动筛选选（略）高级筛选选由于自动动筛选只只能筛选选出简单单条件的的数据，因因此如要要在复杂杂条件下下进行筛

17、筛选就需需要采用用高级筛筛选的方方式。在需要进进行筛选选的数据据表外设设定筛选选条件。在对话框框中设定定：列表表区域为为数据表表区域；条件区区域为刚刚才在数数据表外外部设定定的条件件区域，要要包括字字段名称称和条件件所在的的单元格格区域。条件格式式条件格式式可以使使得符合合特定条条件的记记录按照照某种设设定的格格式显示示。选中要进进行条件件格式设设定的单单元格范范围执行【格格式】【条条件格式式】在条件格格式设置置界面进进行条件件的设定定：统计Frrequuenccy(需要进进行统计计的数据据区域，分分段点)对数据据进行区区间统计计。此函函数需要要和数组组公式一一起使用用。此函函数是根根据数值值

18、的大小小划分区区间进行行统计的的。列出出需要分分析的工工资区间间，选中中需要计计算统计计结果的的单元格格区域，输输入函数数：按下组合合键：ctrl + sshifft + ennterr排名次=rannk(需排名名数值，排排名数据据范围，排排序方式式)排序方方式：0或忽略略降序，否否则为升升序。案例066 窗体控控件的使使用下拉菜单单的制作作。选择“视视图”“工具栏”“窗体”，出现“窗体”工具条条，在工工具条上上选择“组合框”。按下鼠标标左键，拖拖放出合合适的大大小后释释放鼠标标，出现现下拉菜菜单形状状。选中中该下拉拉菜单，右右键选择择“设置控控件格式式”在“控制制”菜单上上，去选选择相应应的

19、数据据源。“三维阴阴影”选项是是下拉菜菜单显示示效果设设定。单元格链链接：该该处指定定的单元元格中将将存储在在控件中中被选中中的项目目在数据据源中的的序列号号。我们们经常利利用该序序列号引引用控件件选中的的值，还还可以利利用该值值制作动动态图表表。根据报销销单上填填报的日日期自动动生成该该报销单单的起止止日期。使用最大大值函数数MAX和最小小值函数数MIN。制作单选选按钮。使用窗体体工具条条上的“选项按按钮”界面定制制行号列标标，滚动动条，工工作表标标签的隐隐藏。选选择“工具”“选项”“视图”，将窗窗口选项项中的行行号列标标，水平平滚动条条，垂直直滚动条条，工作作表标签签选项去去掉。案例077

20、 汇总报报表对于分布布在同一一文件中中的表格格汇总：首先创建建格式完完全一致致的汇总总表的表表结构在汇总表表需要汇汇总的项项目上输输入：=suum()将光标标放入括括号，用用鼠标选选择第一一张工作作表，按按下shiift键，再再选取最最后一张张工作表表，然后后选择需需要汇总总的单元元格，回回车。将该公式式复制到到其他需需要汇总总的单元元格即可可实现整整张表的的汇总。对于分布布在不同同文件中中的表格格汇总：首先创建建格式完完全一致致的汇总总表的表表结构将光标置置于起始始报表项项目上选择【数数据】【合并并计算】“引用位位置”选择各各个需要要被合并并的报表表，点击击“添加”，直至至所有需需要被合合并

21、的报报表添加加完成。需要删除除一张表表格时，只只需要在在合并计计算界面面上选中中该表格格，点击击删除按按钮。当子表格格数据发发生变化化需要更更新汇总总表数据据时，只只需执行行：数据据-合并计计算，进进入合并并计算界界面后直直接点击击确定退退出该界界面即可可完成刷刷新数据据的操作作。案例088 报表表保护单元格区区域的保保护。首先取消消不需要要保护的的单元格格区域的的锁定状状态。右右键“设置单单元格格格式”，在“保护”标签上上将锁定定选项取取消。如如果需要要隐藏单单元格公公式内容容，可以以将隐藏藏选项选选中。选择“工工具”“保护”“保护工工作表”，输入入保护密密码。隐藏公式式右键菜菜单设设置单元

22、元格格式式保护护选中中隐藏选选项，然然后执行行对工作作表的保保护隐藏工作作表：隐藏工作作表后需需要保护护工作簿簿。案例099 按钮的的制作选择“视视图”“工具栏”“窗体”，调出出窗体工工具条。选择工工具条上上的命令令按钮。绘制出出按钮的的基本形形状后，自自动跳出出“指定宏”对话框框，点击击“新建”按钮，出出现VBA编程界界面。在Subb和Endd Suub之间输输入：sheeetss (“要跳转转的工作作表名称称”).sseleect，关闭闭该窗口口。选中中该按钮钮，右键键菜单“编辑文文字”，可以以修改按按钮名称称。案例100 财务报报表利用exxcell创建财财务报表表，要注注意报表表之间的

23、的勾稽关关系。一一方面是是出于高高效率管管理报表表的考虑虑，另一一方面，可可以帮助助我们分分析报表表中的数数据，并并快速检检查出报报表中的的可能错错误。为了能够够自动维维护报表表间的数数据关系系，我们们经常需需要使用用单元格格链接，将将所有的的表链接接起来。注意链链接方式式请使用用并联而而非串联联。串联联导致链链接关系系更加复复杂且难难以维护护，而并并联的方方式将只只有一个个统一的的数据源源，链接接关系简简单不容容易出错错，而且且易于管管理。比如损益益表，资资产负债债表，现现金流量量表，需需要使用用公式和和链接使使得这些些表链接接起来，该该链接关关系能够够自动维维护和更更新多表表之间的的勾稽关

24、关系，时时刻能够够保证报报表数据据之间的的勾稽的的正确性性。（具具体公式式参见电电子文档档）。案例111 表格的的几种类类型数据表可可以分为为数据列列表，二二维表，多多维表和和带有格格式的报报表等几几种类型型。数据表类类型操作目标标汇总方法法是否需要要重复制制作是否与数数据源有有链接关关系多字段数数据列表表记录叠加加并按表表名区分分导入外部部数据 + SQLL语句不需要有二维表多个二维维表生成成透视表表透视表多多维数据据区域合合并不需要有同一文件件内的报报表数据汇总总=summ(beeginn:ennd!BB2)不需要有不同文件件中的报报表数据汇总总合并计算算不需要有非标准数数据表汇总建议修改

25、改原表的的结构或或者仅作作为终端端报表，上上层再建建立一个个数据源源表作为为收集数数据使用用需要无案例122 数据透透视表应用数据据透视表表进行各各类数据据分析。选择“数数据”“数据透透视表和和数据透透视图”，进入入透视表表设置向向导。点击“下下一步”，选择择正确的的数据范范围。点击“下下一步”，选择“布局”按钮。在如下图图界面上上，将右右侧的字字段拖入入左侧相相应区域域内。形成如下下图的布布局，点点击确定定按钮。生成如下下的透视视表后，将将鼠标悬悬停在想想要移动动的字段段上，鼠鼠标左键键按下，将将字段拖拖放到其其他区域域，生成成自己需需要的数数据显示示。数据百分分比显示示：右键键菜单字段设设

26、置选选项，将将数据显显示方式式改为“占同列列数据总总和的百百分比”同时显示示数据与与百分比比：在【布局局】里将将销售收收入两次次拖入数数据区域域将其中一一个销售售收入改改为百分分比显示示在报表项项目上输输入新的的名称可可以修改改项目名名称将报表项项目拖拽拽到列标标题位置置，可以以将数值值和百分分比改为为按列排排列筛选数据据：点击字段段名称后后面的下下拉菜单单，可以以对字段段内容进进行筛选选。显示明细细数据：双击需要要查看明明细的数数据，在在新的工工作表上上将显示示构成此此汇总数数据的所所有明细细数据。对数据进进行排序序：把光标放放在行位位置的报报表项目目上，【右右键菜单单】【字字段设置置】，【

27、高高级】按按钮，左左侧可以以进行排排序设置置。按照日期期分组：将日期放放入行区区域，【右右键菜单单】【组组及显示示明细数数据】【组组合】在【步长长】中选选择需要要的分组组标准按照数值值分组：将销售收收入放入入行区域域，【右右键菜单单】【组组及显示示明细数数据】【组组合】在【步长长】中选选择需要要的分组组标准插入计算算字段：在【名称称】后给给计算字字段定义义名称在【公式式】后输输入计算算字段的的计算公公式，可可以引用用下面的的字段自动更新新：【右键菜菜单】【表表格选项项】【打打开时刷刷新】可以让报报表始终终有更新新后的数数据显示示。透视图可以单独独生成数数据透视视图，也也可以基基于现有有的透视视

28、表生成成透视图图。如果果已经有有现成的的透视表表，通过过点击数数据透视视表工具具条上的的图表按按钮可以以生成一一个透视视图。通过拖动动透视图图上的各各个字段段到右侧侧或者底底部的位位置，可可以方便便地改变变图表组组织数据据的方式式。隐藏透视视图字段段按钮：把光标悬悬停在任任意字段段上，右右键菜单单，选择择【隐藏藏数据透透视图字字段按钮钮】，即即可以将将字段按按钮隐藏藏；再次显示示字段按按钮：点击透视视图工具具条上第第一项，在在出现的的下拉菜菜单中选选择【隐隐藏数据据透视图图字段按按钮】如果有些些数据无无法在数数据透视视表内部部分析，可可以引用用透视表表的数据据作为分分析的基基础。制作试算算平衡

29、表表点击“数数据”并拖拽拽到“汇总”隐藏数据据透视表表中的汇汇总字段段或单元格格格式自定定义功能能格式设定定为：红色借贷不不平衡;红色金额借借贷不平平衡；蓝色”借贷平平衡”Ctrll+拖拽标标签来复复制工作作表编制损益益表创建损益益表格式式创建ISS嵌套函函数IS 类类函数，可可以检验验数值的的类型并并根据参参数取值值返回 TRRUE 或 FAALSEE。函数如果为下下面的内内容，则则返回 TRRUEISBLLANKK值为空白白单元格格。ISERRR值为任意意错误值值（除去去 #NN/A）。ISERRRORR值为任意意错误值值（#N/A、#VAALUEE!、#REEF!、#DIIV/00!、#

30、NUUM!、#NAAME? 或 #NNULLL!）。ISLOOGICCAL值为逻辑辑值。ISNAA值为错误误值 #NN/A（值不不存在）。ISNOONTEEXT值为不是是文本的的任意项项（注意意此函数数在值为为空白单单元格时时返回 TRRUE）。ISNUUMBEER值为数字字。ISREEF值为引用用。ISTEEXT值为文本本。创建财务务比率销售毛利利率=销售毛毛利/销售净净额毛利率率大，表表示经营营能力强强。销售成本本率=销售成成本/销售净净额成本率率低，表表示经营营能力好好。净利率率率=净利润/销售净净额净利润润率越高高，表示示经营能能力强。营业比率率=（销售售成本+营业费费用）/销售净净额

31、，营营业比率率越低，表表示经营营能力越越强营业费用用率=销售费费用/销售净净额营业费费用越低低，表示示经营绩绩效越好好。用MAXX、MIN函数显显示制表表日期MAX，返回一一组值中中的最大大值MIN，返回一一组值中中的最小小值案例一三三多重合合并计算算数据如下图格格式的即即为二维维表：现在我们们需要将将数个格格式相同同的二维维表汇总总为一张张表格，且且可以区区分不同同表格属属性进行行分析，比比如三张张表分别别为北京京，上海海，深圳圳分公司司的表格格。我们们使用的的方法是是利用透透视表多多重合并并计算数数据区域域的功能能。选择数据据-数据透透视表和和数据透透视图选择第33个数据据源类型型：多重重

32、合并计计算数据据区域，点点击下一一步按钮钮：在出现的的界面上上选择：自定义义字段选择需要要被合并并的表格格区域，点点击添加加将其添添加到所所有区域域；将页页字段数数据改为为1，在项项标志处处输入该该表格的的标志，使使用相同同的方法法将其他他需要合合并的表表格全部部添加。透视表生生成后如如下图所所示，已已经将多多个表格格合并成成一个：双击字段段名，可可以对字字段名进进行修改改：案例144 回归与与预测回归分析析表明事事物之间间相互影影响的关关系，主主要用于于分析单单个因变变量如何何受一个个或多个个自变量量影响的的。比如如某地空空调的销销量与气气温的变变化之间间的关系系。可以以用统计计获得的的历史

33、数数据，对对未来的的数据进进行预测测。可以以利用它它帮助经经营，财财务，销销售，营营销等方方面的决决策。趋势线预预测根据历史史数据制制作出折折线图选中图表表中的折折线，右右键菜单单添加加趋势线线选择预测测类型（可可以先选选择默认认的线性性）切换到选选项标签签，根据据需要预预测的期期间数，在在“前推”处输入入预测期期间个数数，并选选中“显示公公式”和“显示R平方值”选项根据出现现的回归归系数（R平方）判判断预测测类型是是否适合合，R平方越越接近1越好（多多项式除除外，需需要考虑虑业务数数据是否否存在较较大的波波动，如如果没有有则不适适用多项项式分析析类型）根据回归归方程求求预测值值移动平均均没有

34、R平方，不不适用此此分析方方法。Corrrel函数判判断相关关性。相关系数数在0.8以上为为强相关关，0.6以上为为弱相关关。案例一五五-166 数据分分析与图图表分析析日常工作作中主要要用到的的数据分分析有：构成：局局部与整整体比例例关系。序列：在在一个时时间序列列内进行行的比较较与分析析。差异：预预算与实实际数的的版本差差异等。增长：增增长率与与增速，进进行趋势势预测。勾稽：数数据之间间固有的的相互联联系的属属性，利利用此属属性进行行数据合合理性的的判断。图表用于于形象地地展示数数据。利利用图表表可以更更好地对对业务数数据进行行分析。差异分析析：利用上面面的表格格制作面面积图，数数据区域域

35、选择C255:D331选中B公公司数据据系列，右右键菜单单“数据系系列格式式”将其内部部（填充充色）和和边框色色都改为为“无”图表上将将只显示示差异部部分，如如下图：双坐标图图表：利用下面面的数据据制作双双坐标图图表：选择“插插入”-“图表”，进入入图表向向导，选选择柱形形图，第第一种子子图表类类型簇状状柱形图图。选择“视视图”-“工具栏”-“图表”，调出出图表工工具条：点开图表表工具条条上下拉拉菜单，选选择 系列“完成率”点击图表表工具条条上“数据系系列格式式”按钮：在数据系系列格式式界面上上，切换换到坐标标轴标签签，将系系列绘制制在由“主坐标标轴”改为“次坐标标轴”再次选中中系列“完成率”

36、，右键键菜单选选择“图表类类型”选择折线线图，点点确定：双坐标图图表制作作完成。气泡图：利用下表表制作气气泡图：在图表向向导中选选择“气泡图”因为气泡泡图没有有分类轴轴（其横横坐标为为序列号号），我我们需要要添加数数据标识识，点击击下一步步，切换换到数据据标志标标签，将将系列名名称打勾勾：纵坐标表表示%Grrowtth,气泡大大小表示示Cosst:单元格嵌嵌入式图图表：利用下表表制作如如图中所所示的嵌嵌入式图图表在单元格格中敲入入如下公公式：=REPPT(|,B2/4000)其中，除除数的值值由源数数据的大大小决定定，源数数据大，除除数应相相应变大大，其意意义在于于缩减竖竖线的数数量，使使得单

37、元元格可以以容纳由由一系列列竖线构构成的图图表。复合饼图图：当数据系系列内值值的大小小差异较较大时，可可以制作作复合饼饼图以便便数据显显示更为为清楚，如如下图所所示：选择饼图图中的复复合饼图图子图表表类型：点击下一一步，切切换到图图例标签签，将显显示图例例的勾去去掉：切换到数数据标志志标签，把把类别名名称和百百分比打打勾：选中数据据系列，右右键菜单单，选择择数据系系列格式式：切换到选选项标签签，将第第二绘图图区包含含最后的的值改为为：4复合饼图图制作完完成。图表组合合：一个图表表中的不不同数据据系列可可以采用用不同的的图表类类型显示示，如下下图：首先制作作一个柱柱形图：在图表工工具条上上选择数

38、数据系列列 Coompeensaatioon:在图表工工具条上上选择数数据系列列格式按按钮：在数据系系列格式式界面上上，切换换到坐标标轴标签签，将主主坐标轴轴改为次次坐标轴轴：右键点击击Commpennsattionn,选择图图表类型型：将其图表表类型改改为折线线图；用同样的的方式操操作Prooducctioon系列，将将其图表表类型改改为面积积图；完成图表表组合的的制作。下拉菜单单式图表表：写入如下下的公式式：inddex函数查查找出的的值由A9单元格格来指定定。制作三维维饼图，其其数据区区域为A3:I3,A9:I9,需要手手工选取取。点击“视视图”-“工具栏”-“窗体”，调出出窗体工工具条

39、：在窗体工工具条上上选择组组合框，在在饼图上上拖放出出一个组组合框：右键选中中组合框框，在菜菜单中选选择“设置控控件格式式”：在控制标标签中做做如下设设置：其其中单元元格链接接为存储储控件选选中项目目序号的的单元格格。完成下拉拉菜单式式图表的的制作：带有滚动动条的图表：该图表中中可以随随着点击击滚动条条的动作作而动态态翻看源源数据中中的大量量数据。定义两个个动态引引用的名名称：Periiod: =ooffsset(7.动态图图表!$A$11,77.动态图图表!$D$11,0,10,1)Dataa: =ooffsset(7.动态图图表!$B$11,77.动态图图表!$D$11,0,10,1)制作

40、折线线图，选选择系列列标签，删删除perriodd系列：将“值”和“分类（X）轴标标志”设置为为如下图图内容：调出窗体体工具条条，在图图表外绘绘制滚动动条，右右键选中中滚动条条，选择择设置控控件格式式：在单元格格链接中中设置为为D1:甘特图如果想制制作如下下图的甘甘特图，需需要以下下4列数据据：首先制作作堆积条条形图：点击下一一步，切切换到系系列标签签，删除除结束日日期系列列：生成的条条形图如如下图所所示：下面转换换纵坐标标的项目目排列次次序：双击纵坐坐标，切切换到刻刻度，将将分类次次序反转转和数据据轴交叉叉于最大大分类选选项打勾勾。此时纵坐坐标次序序已经反反转双击开始始日期数数据系列列，在数

41、数据系列列格式对对话框中中将边框框和内部部都改选选为无，此此时开始始日期数数据系列列隐藏。此时需要要将横坐坐标开始始日期改改为真正正的项目目开始日日期：在exccel中，每每一个日日期都对对应一个个数值，选选中B2,查看20008-7-11对应数数值为396630双击横坐坐标，切切换到刻刻度，将将最小值值改为396630，同样样将横坐坐标最大大值改为为真正的的项目结结束日期期。甘特图制制作完成成。案例177动态态图表选中北京京，深圳圳，上海海以下的的区域，分分别定义义北京，深深圳，上上海的名名称选择视图图-工具栏-窗体，选选择选项项按钮，画画出如下下三个选选项按钮钮：右键选中中选项按按钮，在在

42、右键菜菜单中选选择设置置控件格格式，在在跳出的的对话框框中选择择控制标标签，在在单元格格链接中中选择A16：在A177单元格格中输入入公式：=chhoosse(aa16,”北京”,”上海”,”深圳”)，为A17定义名名称：chooosee在A2,A3,A4单元格格中输入入以下内内容：在B166单元中中写入以以下公式式，并复复制到该该行1-112月的单单元格中中：=VLOOOKUUP(AA3,IINDIIRECCT(cchooose),COOLUMMN()-1,0)依据此数数据表制制作双曲曲线图：案例一八八 比率分分析利用透视视表灵活活的特性性，将两两个结构构完全相相同的透透视表相相除，即即得到

43、可可以进行行任意两两个因素素间的比比率分析析图表。案例199逻辑判判断IF(条条件判断断，如果果条件满满足则返返回的结结果，如如果条件件不满足足则返回回的结果果)根据工工龄计算算员工年年假。年年假规则则：规定定：公司司工龄小小于1年的，享享受10天年假假；大于于1年小于10年的，工工龄每增增加一年年，年假假增加1天；增增长到20天不再再增加。使用IF函数嵌嵌套实现现。单元元格F3为工龄龄。=IF(F31,110,IIF(FF310000000,(K3-16000)*0.445-一五3755,IFF(KK3-116000)8800000,(K3-16000)*0.44-1003755,IFF(K

44、K3-116000)6600000,(K3-16000)*0.335-663755,IFF(KK3-116000)4400000,(K3-16000)*0.33-33375,IF(K33-16600)2000000,(KK3-116000)*00.255-一三75,IF(K33-16600)50000,(K33-16600)*0.2-3375,IF(K33-16600)20000,(K33-16600)*0.一五-1225,IIF(K3-16000)5000,(KK3-116000)*00.1-25,(K33-16600)*0.05)自动计算算加班费费=IFF(ISSERRROR(VLOOOK

45、UUP(DD2,$L$22:$LL$122,1,FALLSE),IIF(OOR(GG2=66,G22=7),周末加加班,工作日日加班),节假日日加班)自动计算算请假扣扣除=IFF(ORR(D55=病假,DD5=事假),E5*1,IIF(DD5=旷工,E5*3,00)隐藏错误误提示=IF(ISEERROOR(表达式),表达式)案例200计数与与求和条件计数数couuntiif(rrangge，criiterria)couuntiif(AA1:AA1000，”8”)条件求和和如果满满足某个个条件，就就对该记记录里的的指定数数值字段段求和。在第一一个参数数所在的的区域里里面查找找第二个个参数指指定的

46、值值，找到到后对第第三个参参数指定定的字段段进行求求和。ssumiif(rrangge, criiterria, suum_rrangge)ssumiif(AA1:AA1000, “?海*”, EE1:EE1000) 对A列中第4个字为为海的E列的值值求和模糊条件件求和summif(a1:a1000, “*”&”海”&”*”, ee1:ee1000) 对A列中包包含“海”字的E列的值值求和sumiif对多个个条件进进行求和和=SUUM(SSUMIIF(FF:F,F2:F3,E:EE)=SUMM(SUUMIFF(F:F,陈露,程静,E:EE)ccritteriia为常量量，普通通公式；criit

47、erria为单元元格引用用，需要要使用数数组公式式。sum与与数组公公式联手手计数=suum(a1:a1000110000)*(a1:a1000550000)sum与与数组公公式联手手求和=suum(条件1)*(条件2)*.*求和区区域)sumpprodductt的使用用返回相相应的数数组或区区域的乘乘积的和和。可以以使用summprooducct替代sum数组公公式的使使用用于于多条件件求和。summprooducct(条件1)*(条件2)*) 用于计计数summprooducct(条件1)*(条件2)*求和区区域) 用于求求和案例211 模拟运运算表（略略）案例222 单变变量求解解如果希

48、望望利润达达到200万，则则需要相相应调整整的单价价、直接接成本、固定成成本、销销售量为为多少？可变单元元格：要要求解的的值所在在的单元元格目标单元元格：关关系式表表达所在在的单元元格目标值：关系式式的值单变量求求解的原原理：根根据在单单元格中中描述的的一元方方程求解解方程中中的未知知数。使使用单变变量求解解的前提提：需要要在单元元格中将将一元方方程的方方程式描描述清楚楚。如果果是函数数关系，则则需要在在单元格格中引用用该函数数。案例233规划求求解步骤：构构建已知知条件的的关系模模型，然然后运行行规划求求解工具具。构建建模型的的过程实实际上是是利用Exccel单元格格引用的的方式将将条件描描

49、述清楚楚的过程程。在规划求求解对话话框中，进进行相关关设置。目标单单元格为为求解后后达到的的目标。可变单单元格为为要求解解的单元元格。在在约束中中添加已已知的其其他条件件。规划求解解可以求求解多元元方程，可可以解决决求极值值的问题题，可以以替代计计算较为为复杂的的计算过过程。案例244 自定义义函数宏的安全全性级别别调整点击：工工具-宏-安全性性如果定义义了宏或或者自定定义函数数并且想想使用它它们，那那么需要要将宏的的安全性性级别调调整为中中或者低低。自定义函函数：进入VBB编辑器器选中左侧侧工程管管理器中中的当前前工作簿簿，查看看其下项项目中是是否存在在“模块”。如果果不存在在，点击击【插入

50、入】菜单单，选择择“模块”；如果果存在，则则略过此此步骤。双击模块块下的某某一模块块，右侧侧将弹出出该模块块的代码码窗口，我我们将在在此代码码窗口中中编写自自定义函函数。在右侧代代码窗口口输入以以下语句句：Funcctioon vvaluuatiion(a, b, cc, dd)valuuatiion = (a + b) *0.66 + (c + dd) * 0.4End Funnctiion其中，vvaluuatiion是该函函数的函函数名，a,bb,c,d是该自自定义函函数的参参数，funnctiion 与endd fuuncttionn是自定定义函数数的起始始与结束束语句。带有判断断的自

51、定定义函数数：Funcctioon llimiitattionn(joob)If jjob = “总经理”Or jobb = “市场销销售” Thhenlimiitattionn = 一五00ElseeIf jobb = “客户服服务” Orr joob = “副总经经理” Thhenlimiitattionn = 12000ElseeIf jobb = “职能管管理” Orr joob = “业务总总监” Thhenlimiitattionn = 8000ElseeIf jobb = “产品” Orr joob = “采购” Thhenlimiitattionn = 6000ElseeIf

52、jobb = “研发技技术”Or jobb = “生产运运作” Thhenlimiitattionn = 4000Elsee: llimiitattionn = 1000End IfEnd Funnctiion附：VBBA简介VBA简简介：VBA 是 Viisuaal bbasiic ffor Apppliccatiion 的缩写写。是用用来开发发应用程程序的Vissuall baasicc语言，除除了它无无法象Vissuall baasicc般独立立存在外外，其它它的如程程序的编编辑方法法，对象象的使用用等，都都与Vissuall baasicc相同，因因此如果果学习过过Vissuall b

53、aasicc语言，入入门VBA将是一一件很容容易的事事。VBE是是Vissuall baasicc Edditoor的缩写,是用来来编写VBA应用程程序的编编辑器,它与VB编器有有什么区区别呢?Viisuaal bbasiic是用来来开发完完整程序序的工具具,必须使使用相当当大的系系统资源源,但是在在控制EXCCEL或其他OFFFICEE办公软软件时,并不需需要使用用这么庞庞大的资资源,因此Miccrossoftt将Vissuall baasicc编写及及执行程程序的功功能附属属于OFFFICEE之下，使使其可以以使用较较少的系系统资源源，达到到编辑出出应用程程序的目目的。因因此将提提供这些些

54、功能的的编辑器器称为Vissuall baasicc Edditoor。EXCEELVBBA基本构构架对象、属属性、事事件、方法对象：所所谓的对对象，我我们可以以将其想想象成日日常生活活中所用用的各类类物品比比如车子子桌子、椅子等等以车子子的组成成为例，车车子是由由轮子、车座、车灯、外壳等等组合而而成的一一个大对对象而这这些具有有单一功功能的对对象又可可以分为为更小的的对象如如：螺丝丝、电线线等。 就就EXCCEL而言其其对象就就是工作作薄、工工作表、图表工工作表或或其他更更细微的的窗体组组件。属性：所谓属性性就是对对象所属属的特征征，例如如当年到到一个苹苹果时，可可以马上上知道它它的颜色色形

55、状及及大小这这些都可可以用来来形容对对象的形形容词也也就是对对象的属属性，比比如工作作表的名名称就是是工作表表的一个个属性。取得属性性值的语语法：变变量=对象.属性比如我们们要取得得当前工工作表的的名子和工工作表的的数量AAA=Acttiveesheeet.nammeBBB=Acttiveesheeet.couunt设定属性性的语法法:对象.属属性=属性值值例:shheetts(11).nnamee=我的工工作薄1 设置第第一个工工作表的的名称是是我的工工作薄1事件:所谓事件件,就是发发生在对对象上的的事情例例如在按按按上单单击鼠标标这个动动作就是是发生于于按纽上上的事件件，工作作表的增增加，

56、单单元格的的修改都都是事件件。下面面的图示示中右边边的下拉拉框中都都是WORRKSHHEETT（工作作表）的的事件事件有什什么用呢呢？程序序我们平平常并不不希望它它时时运运行，所所以我们们需要在在使用到到它时再再让它运运行，比比如：你你可以设设置一个个按纽用用单击这这个事件件去触发发程序的的运行，当当我们单单击按纽纽时就运运行，不不单击就就不运行行。再如如，我们们有时候候需要在在改变某某个工作作表中的的单元格格时运行行某个程程序这时时就可以以用Worrkshheett_Chhangge这个事事件。方法:控制对象象的动作作或功能能称为方方法,简单的的说就是是为了达达到某种种效果而而下达的的命令:

57、比如:对对工作表表这个对对象进行行新建或或删除的的功能.而新建建及删除除功能即即为工作作表这个个对象所所提供的的方法.使用方法法的程序序语法.对象.方方法例:SHHEETT1.DDELEETE上例中SSHEEET1的部分分为对象象,而.后方的的即为对对象所提提供的方方法,至于对对象提供供的方法法有哪些些,并不需需要特意意去记忆忆它们,当输入入了对象象名称然然后输入入.时，系系统就会会显示该该对象提提供的方方法菜单单以便从从菜单中中选取适适用的方方法.举例：点击“新新建”并加入SSheeets(shheett1).Raangee(aa1) = 1000Sheeetss(ssheeet1).CCe

58、llls(22, 11) = 2000Dim i AAs IInteegerr DDIM变量名名称AS 变量类类型For i = 1 To 20SSheeets(shheett1).Ceellss(i, 1) = iNeext添加：WWithh Apppliicattionn.WoorkssheeetFuuncttionnRannge(a221).Vaaluee = .Suum(RRangge(a1:a200) 注意SUM前面要要有个点点才行RRangge(a222).Vallue = .Aveeragge(RRangge(a1:a200)Endd Wiith在VBAA不直接接支持在在EXCC

59、EL工作表表中的一一些函数数，如果果要调用用就必须须加上AAppllicaatioon.WWorkksheeetFFuncctioon宏：许多多VBA高手都都是从录录制宏入入门VBA的，宏宏的功能能就是将将所操作作的步骤骤记录下下来,以便以以后的任任何时候候只要使使用宏,就可以以重复执执行操作作过的步步骤。有有时在编编程序时时，忘记记了EXCCEL某些基基本功能能的编写写，录制制宏就是是一个很很好的方方法，比比如你忘忘记了不不连续单单元格的的表示方方法，你你就可以以在工作作表中把把你选取取不连续续单元格格的过程程录制下下来，代代码自然然就出来来了。录制宏的的操作： “工具”“宏”点击录录制宏按

60、按纽在弹出出的录制制新宏对对话框中中输入宏宏名和快快捷键（可可不填）进行事事选设定定的操作作停止录录制（其其实这个个和平时时生活中中录音或或录像一一样，开开始录制-停止），补补充一点点，就是是如果以以后想用用按纽来来执行宏宏，可以以直接在在弹出的的指定宏宏对话框框中点击击录制宏宏，其余余步骤同同上。案例255资产产折旧直线折旧旧法计算算资产折折旧=SLNN（Cosst,SSatvvagee,Liife）固定余额额递减折折旧法计计算资产产折旧=DB（Cosst,SSatvvagee,Liife,Yeaar）双倍余额额递减折折旧法计计算资产产折旧=DDBB（Cosst,SSatvvagee,Lii