EXCEL在人力资源管理中的应用(案例与技巧)-HR猫猫

1、Excel在管理中的应用案例01第1部分 Excel基础概念1. 名称名称可以代表一个单元格或者一个单元格区域，或者是常量，公式。名称的定义：选中需要命名的单元格或区域，在界面左上角名称框中输入名称后回车；名称的删除：插入名称定义，选中需要删除的名称点击删除按钮，点确定。名称的引用：需要引用某单元格时输入该单元格的名称：名称第1步：选中需要命名的某个单元格或单元格区域。第2步：在左上角名称框输入命名后回车。删除已有命名：选择“插入”“名称”“定义”；选中需要删除的名称，点击“删除”按钮。名称命名的优点：1) 避免绝对引用的错误2) 对公式进行文字化表述，让公式更加容易理解3) 可以在整个工作簿

2、中通用，引用方便2. 常用引用函数row: 返回指定单元格的行号column: 返回指定单元格的列标match: 返回查找值在查找范围中的序号 =match(查找值，查找范围，0) 其第三个参数为0，表示查找精确值address: 返回单元格名称，其参数为行号列标或计算行号列标的表达式 =address(行号，列标) =address(1,1) 此公式返回A1indirect: 返回单元格的值.其参数为单元格名称 =indirect(“A1”),假设A1=10,则indirect返回10index: 在某区域内查找某个位置的值 =index(查找区域，查找值所在的行号，查找值所在的列号)off

3、set：指定基点，指定位移量，得到单元格引用 单个单元格引用：=offset(基点单元格，向下移动的行，向右移动的列) 区域的引用：=offset(基点单元格，向下移动的行，向右移动的列，区域 包括的行数，区域包括的列数) 向上和向左移动时，位移量为负值。 =offset(A1,1,1) 将得到B2单元格的值3. 动态引用动态引用是通过引用函数实现对于单元格或区域的相对引用。它和相对引用的效果很相似，但比简单的相对引用用途广泛。常用的动态引用的实现方法有：引用函数嵌套。比如offset和row,column嵌套；index和match，indirect,address嵌套等；引用函数与控件结合

4、使用，此类控件包括组合框，滚动条。动态引用的作用：实现对于单元格的动态引用；进行动态分析；制作动态报表。4. 运算类型数值运算：1 + 1 = 2逻辑运算：1 > 0 = TRUE在逻辑运算中，TRUE=1，FALSE=0我们利用逻辑运算进行条件判断在Excel中常用的一些逻辑函数：if,and,or,not,iserror等and: 只有当所有条件全部满足，才会返回true的逻辑值 语法：and(条件1，条件2，)or: 满足其中任何一个条件，都会返回true的逻辑值 语法：or(条件1，条件2，条件3,)5. 函数调用的语法函数名称（参数1，参数2，）6. 绝对引用和相对引用：绝对引

5、用：所引用的单元格不随着公式的复制而移动的引用方式。相对引用：所引用的单元格随着公式的复制而移动的引用方式。改变引用方式的方法：1) 在公式栏中选中需要改变引用方式的单元格，按F4键2) 给需要引用的单元格定义名称，然后在公式中引用该名称7. 数组公式对单元格区域进行多重计算的计算方式。与普通计算公式的区别是录入公式结束后，需要同时按下 ctrl + shift + enter ，其特征是在公式两端会出现一对大括号。如上图例，使用一个公式计算出所有产品的金额合计，引用的是所有的单价和所有的数量，执行的是多重计算。8. 错误提示Excel中存在错误的类型，比如1/0=#DIV/0!, todas

6、()=#NAMES!iserror是一个逻辑函数，用以判断某个单元格内的值是否是一个错误，是错误则返回TRUE,不是错误则返回FALSE.iserror有时可以和if函数嵌套进行一些较为复杂的判断。9. 有取值区间的随机数=最小值（最大值最小值）*rand()10. 循环引用是单元格引用其自身的引用方式。可以设置Excel允许进行循环引用：工具-选项-重新计算：将迭代计算选项打钩选中。行列互换（函数方式）：利用transpose函数+数组公式实现。首先选中行列数和原区域相反的一个区域；然后输入transpose函数最后按下组合键ctrl+shift+enter第2部分 Excel设置1 显示当

7、前文件的完整路径菜单区域右键菜单Web2 显示菜单项全部菜单视图工具栏自定义选项始终显示整个菜单3 鼠标移动方向工具选项编辑按Enter键后移动方向4 隐藏界面要素工具选项视图包括：网格线，滚动条，工作表标签，行号列标等。5 以显示值为准工具选项重新计算以显示精度为准行的合计与列的合计有时出现不相等的情况。可以采用以下方法解决。选择“工具”“选项”“重新计算”“以显示精度为准”，选中该选项。6 自定义序列工具选项自定义序列7 改变文件保存位置工具选项常规默认文件位置8 改变文件用户名工具选项用户名9 单元格自动换行格式单元格对齐自动换行第3部分 Excel基础操作1. 快速选中数据表的整行或者

8、整列ctrl + shift + 下箭头/右箭头2. 快速选中区域ctrl + shift + 83. 行列互换复制需要进行行列互换的区域后，将光标放置在数据表外面位置，选择性粘贴选中“转置”选项4. 一个单元格内容输入为多行alt + 回车键5. 显示公式ctrl + 6. 冻结窗口选择需要进行冻结的单元格位置，选择窗口冻结窗格7. 自定义格式语法： 大于条件值格式；小于条件值格式；等于条件值格式；文本格式8. 缩放数值 0.00， 按百万缩放 0“.”0，按万缩放 0.00, 按千缩放9. 不复制隐藏的行或列a首先选中需要复制的被隐藏了一些行或列的表格区域；b 然后点击“编辑”-“定位”-

9、“定位条件”，在其中选择“可见单元格”；c 复制表格区域，粘贴即可。案例02 一 文本的处理1. 等长文本的分割从字符串的左边取字符：=Left (字符串，文本长度)从字符串的右边取字符：=Right (字符串，文本长度)从字符串的中间取字符：=Mid (字符串，文本起始位置，文本长度)2. 不等长文本的分割第1步：选中要进行分割的字符串区域。第2步：点击“数据”“分列”，在文本分列向导中选择适合的分隔符。第3步：设置需要导入的列以及放置该列的位置。3. 文本的合并=Concatenate(文本1，文本2，)使用连接符&：动态表头：="ABC公司"&YEAR

10、(NOW()&"年"&MONTH(NOW()&"月报表"4. 有效性选中需要设置有效性的区域，点击“数据”“有效性”，在有效性条件中选择“序列”，在来源中录入或选取列表。输入信息：事前提醒。出错警告：事后提示。如果待选列表不在当前工作表中，需要首先为该列表定义名称，然后在有效性来源框中输入：“=该列表的名称”5. 有效性的其他用法不允许录入重复数据的有效性设定：countif(e:e,e12)=1输入的内容中必须包括某字符：=not(iserror(find("中国",g30)二级选项：首先将一级选项的每个项目

11、定义一个名称，该名称内容包括相应的二级项目；制作一级项目的有效性；制作二级项目的有效性：内容为：=INDIRECT(g6)，其中g6为设定了有效性的一级选项所在的单元格。6. 圈示无效数据。选择“工具”“公式审核”“显示公式审核工具栏”，点击“圈示无效数据”按钮。该工具可以将所有不符合有效性设定的内容圈示出来。二 日期的处理1. Datedif 函数：用于计算两个日期之间的年数，月数，天数。=Datedif(开始日期，结束日期，“y”)第三个参数：“y”：表示年数“m”：表示月数“d”：表示天数2. 生日提醒=DATEDIF(TODAY(),DATE(YEAR(TODAY(),MONTH(G2

12、),DAY(G2),"d")3. 某日期的星期=weekday(A2,2) 返回的值是3，则表示该日期是星期三。4. 两个日期间的工作日天数=networkdays(开始日期，结束日期，节假日列表)需要首先加载“分析工具库”才能使用此工具案例03 数据查询1. VLOOKUP（）VLOOKUP在表格或数值数组的首列查找指定的数值，并由此返回表格或数组当前行中指定列处的数值。VLOOKUP 中的 V 代表垂直。VLOOKUP(lookup_value, table_array, col_ index_num, range_lookup)Lookup_value 为需要在数组第

13、一列中查找的数值。Lookup_value 可以为数值、引用或文本字符串。也可以理解为：两表共有的索引字段。Table_array 为需要在其中查找数据的数据表。可以使用对区域或区域名称的引用，例如数据库或列表。必须使得共有字段位于该范围的第一列。col_ index_num 为需要调转的数据位于第二个参数中定义的范围的第几列。必须为单纯数值。range_lookup 定义大致匹配或精确匹配。False或0:精确匹配；true或忽略或1：如果无法找到精确匹配的值，那么就查找并匹配比查找值小的最近似的值。2. HLOOKUP()Hlookup中的H是水平的意思，在横向排列的表格中查询数据用Hlo

14、okup，垂直排列的表格查询用Vlookup函数。其参数含义与VLOOKUP近似。案例04 查询表信息查询表利用有效性和VLOOKUP函数实现。利用有效性实现索引字段的切换。利用VLOOKUP函数查询与索引字段相关的信息。案例05 数据筛选1. 自动筛选（略）2. 高级筛选由于自动筛选只能筛选出简单条件的数据，因此如要在复杂条件下进行筛选就需要采用高级筛选的方式。在需要进行筛选的数据表外设定筛选条件。在对话框中设定：列表区域为数据表区域；条件区域为刚才在数据表外部设定的条件区域，要包括字段名称和条件所在的单元格区域。3. 条件格式条件格式可以使得符合特定条件的记录按照某种设定的格式显示。1)

15、选中要进行条件格式设定的单元格范围2) 执行【格式】【条件格式】3) 在条件格式设置界面进行条件的设定：4. 统计Frequency(需要进行统计的数据区域，分段点)对数据进行区间统计。此函数需要和数组公式一起使用。此函数是根据数值的大小划分区间进行统计的。列出需要分析的工资区间，选中需要计算统计结果的单元格区域，输入函数：按下组合键：ctrl + shift + enter5. 排名次=rank(需排名数值，排名数据范围，排序方式)排序方式：0或忽略降序，否则为升序。案例06 窗体控件的使用1. 下拉菜单的制作。选择“视图”“工具栏”“窗体”，出现“窗体”工具条，在工具条上选择“组合框”。

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

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

18、需要更新汇总表数据时，只需执行：数据-合并计算，进入合并计算界面后直接点击确定退出该界面即可完成刷新数据的操作。案例08 报表保护单元格区域的保护。首先取消不需要保护的单元格区域的锁定状态。右键“设置单元格格式”，在“保护”标签上将锁定选项取消。如果需要隐藏单元格公式内容，可以将隐藏选项选中。选择“工具”“保护”“保护工作表”，输入保护密码。隐藏公式右键菜单设置单元格格式保护选中隐藏选项，然后执行对工作表的保护隐藏工作表：隐藏工作表后需要保护工作簿。案例09 按钮的制作选择“视图”“工具栏”“窗体”，调出窗体工具条。选择工具条上的命令按钮。绘制出按钮的基本形状后，自动跳出“指定宏”对话框，点击

19、“新建”按钮，出现VBA编程界面。在Sub和End Sub之间输入：sheets (“要跳转的工作表名称”).select，关闭该窗口。选中该按钮，右键菜单“编辑文字”，可以修改按钮名称。案例10 财务报表利用excel创建财务报表，要注意报表之间的勾稽关系。一方面是出于高效率管理报表的考虑，另一方面，可以帮助我们分析报表中的数据，并快速检查出报表中的可能错误。为了能够自动维护报表间的数据关系，我们经常需要使用单元格链接，将所有的表链接起来。注意链接方式请使用并联而非串联。串联导致链接关系更加复杂且难以维护，而并联的方式将只有一个统一的数据源，链接关系简单不容易出错，而且易于管理。比如损益表，

20、资产负债表，现金流量表，需要使用公式和链接使得这些表链接起来，该链接关系能够自动维护和更新多表之间的勾稽关系，时刻能够保证报表数据之间的勾稽的正确性。（具体公式参见电子文档）。案例11 表格的几种类型数据表可以分为数据列表，二维表，多维表和带有格式的报表等几种类型。数据表类型操作目标汇总方法是否需要重复制作是否与数据源有链接关系多字段数据列表记录叠加并按表名区分导入外部数据 + SQL语句不需要有二维表多个二维表生成透视表透视表多维数据区域合并不需要有同一文件内的报表数据汇总=sum(begin:end!B2)不需要有不同文件中的报表数据汇总合并计算不需要有非标准数据表汇总建议修改原表的结构或

21、者仅作为终端报表，上层再建立一个数据源表作为收集数据使用需要无案例12 数据透视表1. 应用数据透视表进行各类数据分析。选择“数据”“数据透视表和数据透视图”，进入透视表设置向导。点击“下一步”，选择正确的数据范围。点击“下一步”，选择“布局”按钮。在如下图界面上，将右侧的字段拖入左侧相应区域内。形成如下图的布局，点击确定按钮。生成如下的透视表后，将鼠标悬停在想要移动的字段上，鼠标左键按下，将字段拖放到其他区域，生成自己需要的数据显示。数据百分比显示：右键菜单字段设置选项，将数据显示方式改为“占同列数据总和的百分比”2. 同时显示数据与百分比：1) 在【布局】里将销售收入两次拖入数据区域2)

22、将其中一个销售收入改为百分比显示3) 在报表项目上输入新的名称可以修改项目名称4) 将报表项目拖拽到列标题位置，可以将数值和百分比改为按列排列3. 筛选数据：点击字段名称后面的下拉菜单，可以对字段内容进行筛选。4. 显示明细数据：双击需要查看明细的数据，在新的工作表上将显示构成此汇总数据的所有明细数据。5. 对数据进行排序：把光标放在行位置的报表项目上，【右键菜单】【字段设置】，【高级】按钮，左侧可以进行排序设置。6. 按照日期分组：1) 将日期放入行区域，【右键菜单】【组及显示明细数据】【组合】2) 在【步长】中选择需要的分组标准7. 按照数值分组：1) 将销售收入放入行区域，【右键菜单】【

23、组及显示明细数据】【组合】2) 在【步长】中选择需要的分组标准8. 插入计算字段：在【名称】后给计算字段定义名称在【公式】后输入计算字段的计算公式，可以引用下面的字段9. 自动更新：【右键菜单】【表格选项】【打开时刷新】可以让报表始终有更新后的数据显示。10. 透视图可以单独生成数据透视图，也可以基于现有的透视表生成透视图。如果已经有现成的透视表，通过点击数据透视表工具条上的图表按钮可以生成一个透视图。通过拖动透视图上的各个字段到右侧或者底部的位置，可以方便地改变图表组织数据的方式。隐藏透视图字段按钮：把光标悬停在任意字段上，右键菜单，选择【隐藏数据透视图字段按钮】，即可以将字段按钮隐藏；再次

24、显示字段按钮：点击透视图工具条上第一项，在出现的下拉菜单中选择【隐藏数据透视图字段按钮】如果有些数据无法在数据透视表内部分析，可以引用透视表的数据作为分析的基础。11. 制作试算平衡表 点击“数据”并拖拽到“汇总” Ø 隐藏数据透视表中的汇总字段或Ø 单元格格式自定义功能格式设定为：红色"借贷不平衡"红色"金额借贷不平衡"；蓝色”借贷平衡”Ctrl+拖拽标签来复制工作表编制损益表Ø 创建损益表格式 Ø 创建IS嵌套函数IS 类函数，可以检验数值的类型并根据参数取值返回 TRUE 或 FALSE。函数如果为下面的内容

25、，则返回 TRUEISBLANK值为空白单元格。ISERR值为任意错误值（除去 #N/A）。ISERROR值为任意错误值（#N/A、#VALUE!、#REF!、#DIV/0!、#NUM!、#NAME? 或 #NULL!）。ISLOGICAL值为逻辑值。ISNA值为错误值 #N/A（值不存在）。ISNONTEXT值为不是文本的任意项（注意此函数在值为空白单元格时返回 TRUE）。ISNUMBER值为数字。ISREF值为引用。ISTEXT值为文本。Ø 创建财务比率销售毛利率=销售毛利/销售净额毛利率大，表示经营能力强。销售成本率=销售成本/销售净额成本率低，表示经营能力好。净利率率=净利

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

27、钮：在出现的界面上选择：自定义字段选择需要被合并的表格区域，点击添加将其添加到所有区域；将页字段数据改为1，在项标志处输入该表格的标志，使用相同的方法将其他需要合并的表格全部添加。透视表生成后如下图所示，已经将多个表格合并成一个：双击字段名，可以对字段名进行修改：案例14 回归与预测回归分析表明事物之间相互影响的关系，主要用于分析单个因变量如何受一个或多个自变量影响的。比如某地空调的销量与气温的变化之间的关系。可以用统计获得的历史数据，对未来的数据进行预测。可以利用它帮助经营，财务，销售，营销等方面的决策。1. 趋势线预测1) 根据历史数据制作出折线图2) 选中图表中的折线，右键菜单添加趋势线

28、3) 选择预测类型（可以先选择默认的线性）4) 切换到选项标签，根据需要预测的期间数，在“前推”处输入预测期间个数，并选中“显示公式”和“显示R平方值”选项5) 根据出现的回归系数（R平方）判断预测类型是否适合，R平方越接近1越好（多项式除外，需要考虑业务数据是否存在较大的波动，如果没有则不适用多项式分析类型）6) 根据回归方程求预测值7) 移动平均没有R平方，不适用此分析方法。2. Correl函数判断相关性。相关系数在0.8以上为强相关，0.6以上为弱相关。案例15-16 数据分析与图表分析日常工作中主要用到的数据分析有：构成：局部与整体比例关系。序列：在一个时间序列内进行的比较与分析。差

29、异：预算与实际数的版本差异等。增长：增长率与增速，进行趋势预测。勾稽：数据之间固有的相互联系的属性，利用此属性进行数据合理性的判断。图表用于形象地展示数据。利用图表可以更好地对业务数据进行分析。1. 差异分析：利用上面的表格制作面积图，数据区域选择C25:D31选中B公司数据系列，右键菜单“数据系列格式”将其内部（填充色）和边框色都改为“无”图表上将只显示差异部分，如下图：2. 双坐标图表：利用下面的数据制作双坐标图表：选择“插入”-“图表”，进入图表向导，选择柱形图，第一种子图表类型簇状柱形图。选择“视图”-“工具栏”-“图表”，调出图表工具条：点开图表工具条上下拉菜单，选择 系列“完成率”

30、点击图表工具条上“数据系列格式”按钮：在数据系列格式界面上，切换到坐标轴标签，将系列绘制在由“主坐标轴”改为“次坐标轴”再次选中系列“完成率”，右键菜单选择“图表类型”选择折线图，点确定：双坐标图表制作完成。3. 气泡图：利用下表制作气泡图：在图表向导中选择“气泡图”因为气泡图没有分类轴（其横坐标为序列号），我们需要添加数据标识，点击下一步，切换到数据标志标签，将系列名称打勾：纵坐标表示%Growth,气泡大小表示Cost:4. 单元格嵌入式图表：利用下表制作如图中所示的嵌入式图表在单元格中敲入如下公式：=REPT("|",B2/400)其中，除数的值由源数据的大小决定，源

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

32、式按钮：在数据系列格式界面上，切换到坐标轴标签，将主坐标轴改为次坐标轴：右键点击Compensation,选择图表类型：将其图表类型改为折线图；用同样的方式操作Production系列，将其图表类型改为面积图；完成图表组合的制作。7. 下拉菜单式图表：写入如下的公式：index函数查找出的值由A9单元格来指定。制作三维饼图，其数据区域为A3:I3,A9:I9,需要手工选取。点击“视图”-“工具栏”-“窗体”，调出窗体工具条：在窗体工具条上选择组合框，在饼图上拖放出一个组合框：右键选中组合框，在菜单中选择“设置控件格式”：在控制标签中做如下设置：其中单元格链接为存储控件选中项目序号的单元格。完成

33、下拉菜单式图表的制作：8. 带有滚动条的图表：该图表中可以随着点击滚动条的动作而动态翻看源数据中的大量数据。定义两个动态引用的名称：Period: = offset('7.动态图表'!$A$1,'7.动态图表'!$D$1,0,10,1)Data: = offset('7.动态图表'!$B$1,'7.动态图表'!$D$1,0,10,1)制作折线图，选择系列标签，删除period系列：将“值”和“分类（X）轴标志”设置为如下图内容：调出窗体工具条，在图表外绘制滚动条，右键选中滚动条，选择设置控件格式：在单元格链接中设置为D1:9. 甘

34、特图如果想制作如下图的甘特图，需要以下4列数据：首先制作堆积条形图：点击下一步，切换到系列标签，删除结束日期系列：生成的条形图如下图所示：下面转换纵坐标的项目排列次序：双击纵坐标，切换到刻度，将分类次序反转和数据轴交叉于最大分类选项打勾。此时纵坐标次序已经反转双击开始日期数据系列，在数据系列格式对话框中将边框和内部都改选为无，此时开始日期数据系列隐藏。此时需要将横坐标开始日期改为真正的项目开始日期：在excel中，每一个日期都对应一个数值，选中B2,查看2008-7-1对应数值为39630双击横坐标，切换到刻度，将最小值改为39630，同样将横坐标最大值改为真正的项目结束日期。甘特图制作完成。

35、案例17动态图表选中北京，深圳，上海以下的区域，分别定义北京，深圳，上海的名称选择视图-工具栏-窗体，选择选项按钮，画出如下三个选项按钮：右键选中选项按钮，在右键菜单中选择设置控件格式，在跳出的对话框中选择控制标签，在单元格链接中选择A16：在A17单元格中输入公式：=choose(a16,”北京”,”上海”,”深圳”)，为A17定义名称：choose在A2,A3,A4单元格中输入以下内容：在B16单元中写入以下公式，并复制到该行1-12月的单元格中：=VLOOKUP(A3,INDIRECT(choose),COLUMN()-1,0)依据此数据表制作双曲线图：案例18 比率分析利用透视表灵活的

36、特性，将两个结构完全相同的透视表相除，即得到可以进行任意两个因素间的比率分析图表。案例19 逻辑判断1. IF(条件判断，如果条件满足则返回的结果，如果条件不满足则返回的结果)根据工龄计算员工年假。年假规则：规定：公司工龄小于1年的，享受10天年假；大于1年小于10年的，工龄每增加一年，年假增加1天；增长到20天不再增加。使用IF函数嵌套实现。单元格F3为工龄。=IF(F3<1,10,IF(F3<10,9+F3,20)2. IF 和OR ，AND嵌套使用用于执行更为复杂的判断if(or(条件1，条件2，)，若条件满足则返回的结果，若条件不满足则返回的结果)if(and(条件1，条件

37、2，)，若条件满足则返回的结果，若条件不满足则返回的结果)3. 计算工资及所得税=-IF(K3-1600)>100000,(K3-1600)*0.45-15375,IF(K3-1600)>80000,(K3-1600)*0.4-10375,IF(K3-1600)>60000,(K3-1600)*0.35-6375,IF(K3-1600)>40000,(K3-1600)*0.3-3375,IF(K3-1600)>20000,(K3-1600)*0.25-1375,IF(K3-1600)>5000,(K3-1600)*0.2-375,IF(K3-1600)>

38、;2000,(K3-1600)*0.15-125,IF(K3-1600)>500,(K3-1600)*0.1-25,(K3-1600)*0.05)4. 自动计算加班费=IF(ISERROR(VLOOKUP(D2,$L$2:$L$12,1,FALSE),IF(OR(G2=6,G2=7),"周末加班","工作日加班"),"节假日加班")5. 自动计算请假扣除=IF(OR(D5="病假",D5="事假"),E5*1,IF(D5="旷工",E5*3,0)6. 隐藏错误提示=IF

39、(ISERROR(表达式),"",表达式)案例20 计数与求和1. 条件计数countif(range，criteria)countif(A1:A100，”>8”)2. 条件求和如果满足某个条件，就对该记录里的指定数值字段求和。在第一个参数所在的区域里面查找第二个参数指定的值，找到后对第三个参数指定的字段进行求和。sumif(range, criteria, sum_range)sumif(A1:A100, “?海*”, E1:E100) 对A列中第4个字为海的E列的值求和3. 模糊条件求和sumif(a1:a100, “*”&”海”&”*”, e1:

40、e100) 对A列中包含“海”字的E列的值求和4. sumif对多个条件进行求和=SUM(SUMIF(F:F,F2:F3,E:E)=SUM(SUMIF(F:F,"陈露","程静",E:E)criteria为常量，普通公式；criteria为单元格引用，需要使用数组公式。5. sum与数组公式联手计数=sum(a1:a100>1000)*(a1:a100<5000)6. sum与数组公式联手求和=sum(条件1)*(条件2)*.*求和区域)7. sumproduct的使用返回相应的数组或区域的乘积的和。可以使用sumproduct替代sum数组

41、公式的使用用于多条件求和。sumproduct(条件1)*(条件2)*) 用于计数sumproduct(条件1)*(条件2)*求和区域) 用于求和案例21 模拟运算表（略）案例22 单变量求解如果希望利润达到200万，则需要相应调整的单价、直接成本、固定成本、销售量为多少？可变单元格：要求解的值所在的单元格目标单元格：关系式表达所在的单元格目标值：关系式的值单变量求解的原理：根据在单元格中描述的一元方程求解方程中的未知数。使用单变量求解的前提：需要在单元格中将一元方程的方程式描述清楚。如果是函数关系，则需要在单元格中引用该函数。案例23规划求解步骤：构建已知条件的关系模型，然后运行规划求解工具

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

43、模块下的某一模块，右侧将弹出该模块的代码窗口，我们将在此代码窗口中编写自定义函数。4. 在右侧代码窗口输入以下语句：Function valuation(a, b, c, d)valuation = (a + b) *0.6 + (c + d) * 0.4End Function其中，valuation是该函数的函数名，a,b,c,d是该自定义函数的参数，function 与end function是自定义函数的起始与结束语句。带有判断的自定义函数：Function limitation(job)If job = “总经理”Or job = “市场销售” Thenlimitation = 15

44、00ElseIf job = “客户服务” Or job = “副总经理” Thenlimitation = 1200ElseIf job = “职能管理” Or job = “业务总监” Thenlimitation = 800ElseIf job = “产品” Or job = “采购” Thenlimitation = 600ElseIf job = “研发技术”Or job = “生产运作” Thenlimitation = 400Else: limitation = 100End IfEnd Function附：VBA简介VBA简介：VBA 是 Visual basic for Ap

45、plication 的缩写。是用来开发应用程序的Visual basic语言，除了它无法象Visual basic般独立存在外，其它的如程序的编辑方法，对象的使用等，都与Visual basic相同，因此如果学习过Visual basic语言，入门VBA将是一件很容易的事。VBE是Visual basic Editor的缩写,是用来编写VBA应用程序的编辑器,它与VB编器有什么区别呢?Visual basic是用来开发完整程序的工具,必须使用相当大的系统资源,但是在控制EXCEL或其他OFFICE办公软件时,并不需要使用这么庞大的资源,因此Microsoft将Visual basic编写及执行

46、程序的功能附属于OFFICE之下，使其可以使用较少的系统资源，达到编辑出应用程序的目的。因此将提供这些功能的编辑器称为Visual basic Editor。EXCELVBA基本构架对象、属性、事件、方法对象： 所谓的对象，我们可以将其想象成日常生活中所用的各类物品比如车子桌子、椅子等以车子的组成为例，车子是由轮子、车座、车灯、外壳等组合而成的一个大对象而这些具有单一功能的对象又可以分为更小的对象如：螺丝、电线等。   就EXCEL而言其对象就是工作薄、工作表、图表工作表或其他更细微的窗体组件。属性： 所谓属性就是对象所属的特征，例如当年到一个苹果时，可以马上知道它的颜色形

47、状及大小这些都可以用来形容对象的形容词也就是对象的属性，比如工作表的名称就是工作表的一个属性。取得属性值的语法：变量=对象.属性比如我们要取得当前工作表的名子和工作表的数量AAA=ABBB=Activesheet.count设定属性的语法:对象.属性=属性值例:sheets(1).name="我的工作薄1" 设置第一个工作表的名称是"我的工作薄1"事件: 所谓事件,就是发生在对象上的事情例如在按按上单击鼠标这个动作就是发生于按纽上的事件，工作表的增加，单元格的修改都是事件。下面的图示中右边的下拉框中都是WORKSHEET（工

48、作表）的事件事件有什么用呢？程序我们平常并不希望它时时运行，所以我们需要在使用到它时再让它运行，比如：你可以设置一个按纽用单击这个事件去触发程序的运行，当我们单击按纽时就运行，不单击就不运行。再如，我们有时候需要在改变某个工作表中的单元格时运行某个程序这时就可以用Worksheet_Change这个事件。方法:控制对象的动作或功能称为方法,简单的说就是为了达到某种效果而下达的命令:比如:对工作表这个对象进行新建或删除的功能.而新建及删除功能即为工作表这个对象所提供的方法.使用方法的程序语法.对象.方法例:SHEET1.DELETE上例中SHEET1的部分为对象,而"."后方

49、的即为对象所提供的方法,至于对象提供的方法有哪些,并不需要特意去记忆它们,当输入了对象名称然后输入"."时，系统就会显示该对象提供的方法菜单以便从菜单中选取适用的方法.举例： 点击“新建”并加入Sheets("sheet1").Range("a1") = 100Sheets("sheet1").Cells(2, 1) = 200Dim i As Integer DIM变量名称AS 变量类型For i = 1 To 20Sheets("sheet1").Cells(i, 1) = iNext添加：

50、With Application.WorksheetFunctionRange("a21").Value = .Sum(Range("a1:a20") 注意SUM前面要有个点才行Range("a22").Value = .Average(Range("a1:a20")End With在VBA不直接支持在EXCEL工作表中的一些函数，如果要调用就必须加上Application.WorksheetFunction宏：许多VBA高手都是从录制宏入门VBA的，宏的功能就是将所操作的步骤记录下来,以便以后的任何时候只要使用宏

51、,就可以重复执行操作过的步骤。有时在编程序时，忘记了EXCEL某些基本功能的编写，录制宏就是一个很好的方法，比如你忘记了不连续单元格的表示方法，你就可以在工作表中把你选取不连续单元格的过程录制下来，代码自然就出来了。 录制宏的操作： “工具”“宏”-点击录制宏按纽-在弹出的录制新宏对话框中输入宏名和快捷键（可不填）-进行事选设定的操作-停止录制（其实这个和平时生活中录音或录像一样，开始-录制-停止），补充一点，就是如果以后想用按纽来执行宏，可以直接在弹出的指定宏对话框中点击录制宏，其余步骤同上。案例25资产折旧Ø 直线折旧法计算资产折旧=SLN（Cost,Satvage,Life）&

52、#216; 固定余额递减折旧法计算资产折旧=DB（Cost,Satvage,Life,Year）Ø 双倍余额递减折旧法计算资产折旧=DDB（Cost,Satvage,Life,Year,2）Ø 可变余额递减折旧法计算资产折旧=VDB（Cost,Satvage,Life,StartPeriod,EndPeriod,Factor,NoSwitch）Ø 年数总和折旧法计算资产折旧=SYD（Cost,Satvage,Life,Year）附1：投资决策分析进行投资分析需要为提供一系列现金流量的值。净现值。内部报酬率。净现值和内部报酬率是关系紧密的一对函数。当内部报酬率资金成

53、本时，净现值。附2： 整合数据表我们经常需要将几个表格中的数据整合成一张表，如下图所示，将5个月的数据（目前分布在5张表格上）整合到一个总表上：我们使用的方法是利用office的查询工具query进行的，这样做的优点在于今后对于新数据的更新可以自动化刷新，而不需要重复的进行整合操作（如复制粘贴）。office的查询工具query在典型安装office时是不会被安装的，我们需要首先检查我们的office是否已经安装了query，方法如下：l 打开Excel，点击【数据】菜单中【导入外部数据】【新建数据库查询】，若系统提示安装，则说明该功能未被安装，此时可能需要在光驱中插入Office安装光盘完成该功能的安装。l 在光驱中插入安装光盘后，安装过程中选择自定义安装；点开Excel前面的加号，选中要安装的功能前面的下拉菜单，选择【从本机运行】；点开【Office工具】前面的加号，选中【Microsoft Query】的下