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

Excel在管理中的应用

案例01

第1部分 Excel基础概念

1. 名称
名称可以代表一个单元格或者一个单元格区域，或者是常量，公式。
名称的定义：选中需要命名的单元格或区域，在界面左上角名称框中输入名称后回车；
名称的删除：插入－名称－定义，选中需要删除的名称点击删除按钮，点确定。
名称的引用：需要引用某单元格时输入该单元格的名称：＝名称

第1步：选中需要命名的某个单元格或单元格区域。
第2步：在左上角名称框输入命名后回车。

删除已有命名：选择“插入”－“名称”－“定义”；选中需要删除的名称，点击“删除”按钮。

名称命名的优点：
1) 避免绝对引用的错误
2) 对公式进行文字化表述，让公式更加容易理解
3) 可以在整个工作簿中通用，引用方便

2. 常用引用函数

row: 返回指定单元格的行号
column: 返回指定单元格的列标
match: 返回查找值在查找范围中的序号
=match(查找值，查找范围，0)
其第三个参数为0，表示查找精确值
address: 返回单元格名称，其参数为行号列标或计算行号列标的表达式
=address(行号，列标)
=address(1,1) 此公式返回A1
indirect: 返回单元格的值.其参数为单元格名称
=indirect(“A1”),假设A1=10,则indirect返回10
index: 在某区域内查找某个位置的值
=index(查找区域，查找值所在的行号，查找值所在的列号)
offset：指定基点，指定位移量，得到单元格引用
单个单元格引用：=offset(基点单元格，向下移动的行，向右移动的列)
区域的引用：=offset(基点单元格，向下移动的行，向右移动的列，区域
包括的行数，区域包括的列数)
向上和向左移动时，位移量为负值。
=offset(A1,1,1) 将得到B2单元格的值

3. 动态引用
动态引用是通过引用函数实现对于单元格或区域的相对引用。它和相对引用的效果很相似，但比简单的相对引用用途广泛。
常用的动态引用的实现方法有：
引用函数嵌套。比如offset和row,column嵌套；index和match，indirect,address嵌套等；
引用函数与控件结合使用，此类控件包括组合框，滚动条。
动态引用的作用：实现对于单元格的动态引用；进行动态分析；制作动态报表。

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. 绝对引用和相对引用：
绝对引用：所引用的单元格不随着公式的复制而移动的引用方式。
相对引用：所引用的单元格随着公式的复制而移动的引用方式。

改变引用方式的方法：
1) 在公式栏中选中需要改变引用方式的单元格，按F4键
2) 给需要引用的单元格定义名称，然后在公式中引用该名称

7. 数组公式

对单元格区域进行多重计算的计算方式。与普通计算公式的区别是录入公式结束后，需要同时按下 ctrl + shift + enter ，其特征是在公式两端会出现一对大括号。

如上图例，使用一个公式计算出所有产品的金额合计，引用的是所有的单价和所有的数量，执行的是多重计算。

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

第2部分 Excel设置

1 显示当前文件的完整路径
菜单区域－右键菜单－Web

2 显示菜单项全部菜单
视图－工具栏－自定义－选项－始终显示整个菜单

3 鼠标移动方向
工具－选项－编辑－按Enter键后移动方向

4 隐藏界面要素
工具－选项－视图
包括：网格线，滚动条，工作表标签，行号列标等。

5 以显示值为准
工具－选项－重新计算－以显示精度为准

行的合计与列的合计有时出现不相等的情况。

可以采用以下方法解决。
选择“工具”－“选项”－“重新计算”－“以显示精度为准”，选中该选项。

6 自定义序列
工具－选项－自定义序列

7 改变文件保存位置
工具－选项－常规－默认文件位置

8 改变文件用户名
工具－选项－用户名

9 单元格自动换行
格式－单元格－对齐－自动换行
第3部分 Excel基础操作
1. 快速选中数据表的整行或者整列
ctrl + shift + 下箭头/右箭头

2. 快速选中区域
ctrl + shift + 8

3. 行列互换
复制需要进行行列互换的区域后，将光标放置在数据表外面位置，选择性粘贴－选中“转置”选项

4. 一个单元格内容输入为多行
alt + 回车键

5. 显示公式
ctrl + ~

6. 冻结窗口
选择需要进行冻结的单元格位置，选择窗口－冻结窗格

7. 自定义格式语法：
[大于条件值]格式；[小于条件值]格式；[等于条件值]格式；文本格式

8. 缩放数值
0.00，， 按百万缩放
0“.”0，按万缩放
0.00, 按千缩放

9. 不复制隐藏的行或列
a首先选中需要复制的被隐藏了一些行或列的表格区域；b 然后点击“编辑”-“定位”-“定位条件”，在其中选择“可见单元格”；c 复制表格区域，粘贴即可。

案例02
一 文本的处理

1. 等长文本的分割
从字符串的左边取字符：=Left (字符串，文本长度)
从字符串的右边取字符：=Right (字符串，文本长度)
从字符串的中间取字符：=Mid (字符串，文本起始位置，文本长度)

2. 不等长文本的分割
第1步：选中要进行分割的字符串区域。

第2步：点击“数据”－“分列”，在文本分列向导中选择适合的分隔符。

第3步：设置需要导入的列以及放置该列的位置。

3. 文本的合并
=Concatenate(文本1，文本2，…)

使用连接符&：
动态表头：="ABC公司"&YEAR(NOW())&"年"&MONTH(NOW())&"月报表"

4. 有效性
选中需要设置有效性的区域，点击“数据”－“有效性”，在有效性条件中选择“序列”，在来源中录入或选取列表。

输入信息：事前提醒。
出错警告：事后提示。

如果待选列表不在当前工作表中，需要首先为该列表定义名称，然后在有效性来源框中输入：
“=该列表的名称”

5. 有效性的其他用法

不允许录入重复数据的有效性设定：countif(e:e,e12)=1
输入的内容中必须包括某字符：=not(iserror(find("中国",g30)))

二级选项：
首先将一级选项的每个项目定义一个名称，该名称内容包括相应的二级项目；
制作一级项目的有效性；
制作二级项目的有效性：内容为：=INDIRECT(g6)，其中g6为设定了有效性的一级选项所在的单元格。

6. 圈示无效数据。
选择“工具”－“公式审核”－“显示公式审核工具栏”，点击“圈示无效数据”按钮。

该工具可以将所有不符合有效性设定的内容圈示出来。

二 日期的处理

1. Datedif 函数：用于计算两个日期之间的年数，月数，天数。

=Datedif(开始日期，结束日期，“y”)

第三个参数：
“y”：表示年数
“m”：表示月数
“d”：表示天数

2. 生日提醒
=DATEDIF(TODAY(),DATE(YEAR(TODAY()),MONTH(G2),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 为需要在数组第一列中查找的数值。Lookup_value 可以为数值、引用或文本字符串。也可以理解为：两表共有的索引字段。
Table_array 为需要在其中查找数据的数据表。可以使用对区域或区域名称的引用，例如数据库或列表。必须使得共有字段位于该范围的第一列。
col_ index_num 为需要调转的数据位于第二个参数中定义的范围的第几列。必须为单纯数值。
range_lookup 定义大致匹配或精确匹配。False或0:精确匹配；true或忽略或1：如果无法找到精确匹配的值，那么就查找并匹配比查找值小的最近似的值。

2. HLOOKUP()
Hlookup中的H是水平的意思，在横向排列的表格中查询数据用Hlookup，垂直排列的表格查询用Vlookup函数。其参数含义与VLOOKUP近似。

案例04 查询表

信息查询表
利用有效性和VLOOKUP函数实现。
利用有效性实现索引字段的切换。
利用VLOOKUP函数查询与索引字段相关的信息。

案例05 数据筛选

1. 自动筛选（略）
2. 高级筛选
由于自动筛选只能筛选出简单条件的数据，因此如要在复杂条件下进行筛选就需要采用高级筛选的方式。
在需要进行筛选的数据表外设定筛选条件。

在对话框中设定：列表区域为数据表区域；条件区域为刚才在数据表外部设定的条件区域，要包括字段名称和条件所在的单元格区域。

3. 条件格式

条件格式可以使得符合特定条件的记录按照某种设定的格式显示。
1) 选中要进行条件格式设定的单元格范围
2) 执行【格式】【条件格式】

3) 在条件格式设置界面进行条件的设定：

4. 统计
Frequency(需要进行统计的数据区域，分段点)
对数据进行区间统计。此函数需要和数组公式一起使用。此函数是根据数值的大小划分区间进行统计的。

列出需要分析的工资区间，选中需要计算统计结果的单元格区域，输入函数：

按下组合键：ctrl + shift + enter

5. 排名次
=rank(需排名数值，排名数据范围，排序方式)
排序方式：0或忽略降序，否则为升序。

案例06 窗体控件的使用

1. 下拉菜单的制作。
选择“视图”－“工具栏”－“窗体”，出现“窗体”工具条，在工具条上选择“组合框”。

按下鼠标左键，拖放出合适的大小后释放鼠标，出现下拉菜单形状。选中该下拉菜单，右键选择“设置控件格式”

在“控制”菜单上，去选择相应的数据源。“三维阴影”选项是下拉菜单显示效果设定。

单元格链接：该处指定的单元格中将存储在控件中被选中的项目在数据源中的序列号。我们经常利用该序列号引用控件选中的值，还可以利用该值制作动态图表。

根据报销单上填报的日期自动生成该报销单的起止日期。

使用最大值函数MAX和最小值函数MIN。

制作单选按钮。

使用窗体工具条上的“选项按钮”

2. 界面定制

行号列标，滚动条，工作表标签的隐藏。选择“工具”－“选项”－“视图”，将窗口选项中的行号列标，水平滚动条，垂直滚动条，工作表标签选项去掉。

案例07 汇总报表

1. 对于分布在同一文件中的表格汇总：

首先创建格式完全一致的汇总表的表结构
在汇总表需要汇总的项目上输入：=sum()
将光标放入括号，用鼠标选择第一张工作表，按下shift键，再选取最后一张工作表，然后选择需要汇总的单元格，回车。
将该公式复制到其他需要汇总的单元格即可实现整张表的汇总。

2. 对于分布在不同文件中的表格汇总：

首先创建格式完全一致的汇总表的表结构
将光标置于起始报表项目上
选择【数据】－【合并计算】

“引用位置”选择各个需要被合并的报表，点击“添加”，直至所有需要被合并的报表添加完成。

需要删除一张表格时，只需要在合并计算界面上选中该表格，点击删除按钮。
当子表格数据发生变化需要更新汇总表数据时，只需执行：数据-合并计算，进入合并计算界面后直接点击确定退出该界面即可完成刷新数据的操作。

案例08 报表保护

单元格区域的保护。
首先取消不需要保护的单元格区域的锁定状态。右键“设置单元格格式”，在“保护”标签上将锁定选项取消。如果需要隐藏单元格公式内容，可以将隐藏选项选中。

选择“工具”－“保护”－“保护工作表”，输入保护密码。

隐藏公式
右键菜单－设置单元格格式－保护－选中隐藏选项，然后执行对工作表的保护

隐藏工作表：

隐藏工作表后需要保护工作簿。

案例09 按钮的制作

选择“视图”－“工具栏”－“窗体”，调出窗体工具条。选择工具条上的命令按钮。绘制出按钮的基本形状后，自动跳出“指定宏”对话框，点击“新建”按钮，出现VBA编程界面。

在Sub和End Sub之间输入：sheets (“要跳转的工作表名称”).select，关闭该窗口。选中该按钮，右键菜单“编辑文字”，可以修改按钮名称。

案例10 财务报表

利用excel创建财务报表，要注意报表之间的勾稽关系。一方面是出于高效率管理报表的考虑，另一方面，可以帮助我们分析报表中的数据，并快速检查出报表中的可能错误。

为了能够自动维护报表间的数据关系，我们经常需要使用单元格链接，将所有的表链接起来。注意链接方式请使用并联而非串联。串联导致链接关系更加复杂且难以维护，而并联的方式将只有一个统一的数据源，链接关系简单不容易出错，而且易于管理。

比如损益表，资产负债表，现金流量表，需要使用公式和链接使得这些表链接起来，该链接关系能够自动维护和更新多表之间的勾稽关系，时刻能够保证报表数据之间的勾稽的正确性。（具体公式参见电子文档）。

案例11 表格的几种类型

数据表可以分为数据列表，二维表，多维表和带有格式的报表等几种类型。

数据表类型
操作目标
汇总方法
是否需要重复制作
是否与数据源有链接关系

多字段数据列表
记录叠加并按表名区分
导入外部数据 + SQL语句
不需要
有
二维表
多个二维表生成透视表
透视表多维数据区域合并
不需要
有
同一文件内的报表
数据汇总
=sum(begin:end!B2)
不需要
有
不同文件中的报表
数据汇总
合并计算
不需要
有
非标准数据表
汇总
建议修改原表的结构或者仅作为终端报表，上层再建立一个数据源表作为收集数据使用
需要
无

案例12 数据透视表

1. 应用数据透视表进行各类数据分析。
选择“数据”－“数据透视表和数据透视图”，进入透视表设置向导。

点击“下一步”，选择正确的数据范围。

点击“下一步”，选择“布局”按钮。

在如下图界面上，将右侧的字段拖入左侧相应区域内。

形成如下图的布局，点击确定按钮。

生成如下的透视表后，将鼠标悬停在想要移动的字段上，鼠标左键按下，将字段拖放到其他区域，生成自己需要的数据显示。

数据百分比显示：右键菜单－字段设置－选项，将数据显示方式改为“占同列数据总和的百分比”

2. 同时显示数据与百分比：
1) 在【布局】里将销售收入两次拖入数据区域
2) 将其中一个销售收入改为百分比显示
3) 在报表项目上输入新的名称可以修改项目名称
4) 将报表项目拖拽到列标题位置，可以将数值和百分比改为按列排列

3. 筛选数据：
点击字段名称后面的下拉菜单，可以对字段内容进行筛选。

4. 显示明细数据：
双击需要查看明细的数据，在新的工作表上将显示构成此汇总数据的所有明细数据。

5. 对数据进行排序：
把光标放在行位置的报表项目上，【右键菜单】【字段设置】，【高级】按钮，左侧可以进行排序设置。

6. 按照日期分组：
1) 将日期放入行区域，【右键菜单】【组及显示明细数据】【组合】
2) 在【步长】中选择需要的分组标准

7. 按照数值分组：
1) 将销售收入放入行区域，【右键菜单】【组及显示明细数据】【组合】
2) 在【步长】中选择需要的分组标准

8. 插入计算字段：

在【名称】后给计算字段定义名称
在【公式】后输入计算字段的计算公式，可以引用下面的字段

9. 自动更新：
【右键菜单】【表格选项】【打开时刷新】
可以让报表始终有更新后的数据显示。

10. 透视图

可以单独生成数据透视图，也可以基于现有的透视表生成透视图。如果已经有现成的透视表，通过点击数据透视表工具条上的图表按钮可以生成一个透视图。

通过拖动透视图上的各个字段到右侧或者底部的位置，可以方便地改变图表组织数据的方式。

隐藏透视图字段按钮：
把光标悬停在任意字段上，右键菜单，选择【隐藏数据透视图字段按钮】，即可以将字段按钮隐藏；
再次显示字段按钮：
点击透视图工具条上第一项，在出现的下拉菜单中选择【隐藏数据透视图字段按钮】

如果有些数据无法在数据透视表内部分析，可以引用透视表的数据作为分析的基础。

11. 制作试算平衡表

点击“数据”并拖拽到“汇总”

Ø 隐藏数据透视表中的汇总字段
或

Ø 单元格格式自定义功能

格式设定为：
[红色]"借贷不平衡";[红色]"金额借贷不平衡"；[蓝色]”借贷平衡”

Ctrl+拖拽标签来复制工作表

编制损益表
Ø 创建损益表格式

Ø 创建IS嵌套函数
IS 类函数，可以检验数值的类型并根据参数取值返回 TRUE 或 FALSE。
函数
如果为下面的内容，则返回 TRUE
ISBLANK
值为空白单元格。
ISERR
值为任意错误值（除去 #N/A）。
ISERROR
值为任意错误值（#N/A、#VALUE!、#REF!、#DIV/0!、#NUM!、#NAME? 或 #NULL!）。
ISLOGICAL
值为逻辑值。
ISNA
值为错误值 #N/A（值不存在）。
ISNONTEXT
值为不是文本的任意项（注意此函数在值为空白单元格时返回 TRUE）。
ISNUMBER
值为数字。
ISREF
值为引用。
ISTEXT
值为文本。

Ø 创建财务比率
销售毛利率=销售毛利/销售净额——毛利率大，表示经营能力强。
销售成本率=销售成本/销售净额——成本率低，表示经营能力好。
净利率率=净利润/销售净额——净利润率越高，表示经营能力强。
营业比率=（销售成本+营业费用）/销售净额，营业比率越低，表示经营能力越强
营业费用率=销售费用/销售净额——营业费用越低，表示经营绩效越好。

Ø 用MAX、MIN函数显示制表日期
MAX，返回一组值中的最大值
MIN，返回一组值中的最小值

案例13 多重合并计算数据

如下图格式的即为二维表：

现在我们需要将数个格式相同的二维表汇总为一张表格，且可以区分不同表格属性进行分析，比如三张表分别为北京，上海，深圳分公司的表格。我们使用的方法是利用透视表多重合并计算数据区域的功能。

选择数据-数据透视表和数据透视图

选择第3个数据源类型：多重合并计算数据区域，点击下一步按钮：

在出现的界面上选择：自定义字段

选择需要被合并的表格区域，点击添加将其添加到所有区域；将页字段数据改为1，在项标志处输入该表格的标志，使用相同的方法将其他需要合并的表格全部添加。

透视表生成后如下图所示，已经将多个表格合并成一个：

双击字段名，可以对字段名进行修改：

案例14 回归与预测

回归分析表明事物之间相互影响的关系，主要用于分析单个因变量如何受一个或多个自变量影响的。比如某地空调的销量与气温的变化之间的关系。可以用统计获得的历史数据，对未来的数据进行预测。可以利用它帮助经营，财务，销售，营销等方面的决策。

1. 趋势线预测

1) 根据历史数据制作出折线图
2) 选中图表中的折线，右键菜单－添加趋势线
3) 选择预测类型（可以先选择默认的线性）
4) 切换到选项标签，根据需要预测的期间数，在“前推”处输入预测期间个数，并选中“显示公式”和“显示R平方值”选项
5) 根据出现的回归系数（R平方）判断预测类型是否适合，R平方越接近1越好（多项式除外，需要考虑业务数据是否存在较大的波动，如果没有则不适用多项式分析类型）
6) 根据回归方程求预测值
7) 移动平均没有R平方，不适用此分析方法。

2. Correl函数判断相关性。

相关系数在0.8以上为强相关，0.6以上为弱相关。

案例15-16 数据分析与图表分析

日常工作中主要用到的数据分析有：

构成：局部与整体比例关系。
序列：在一个时间序列内进行的比较与分析。
差异：预算与实际数的版本差异等。
增长：增长率与增速，进行趋势预测。
勾稽：数据之间固有的相互联系的属性，利用此属性进行数据合理性的判断。

图表用于形象地展示数据。利用图表可以更好地对业务数据进行分析。

1. 差异分析：

利用上面的表格制作面积图，数据区域选择C25:D31

选中B公司数据系列，右键菜单“数据系列格式”

将其内部（填充色）和边框色都改为“无”

图表上将只显示差异部分，如下图：

2. 双坐标图表：

利用下面的数据制作双坐标图表：

选择“插入”-“图表”，进入图表向导，选择柱形图，第一种子图表类型簇状柱形图。

选择“视图”-“工具栏”-“图表”，调出图表工具条：

点开图表工具条上下拉菜单，选择 系列“完成率”

点击图表工具条上“数据系列格式”按钮：

在数据系列格式界面上，切换到坐标轴标签，将系列绘制在由“主坐标轴”改为“次坐标轴”

再次选中系列“完成率”，右键菜单选择“图表类型”

选择折线图，点确定：

双坐标图表制作完成。

3. 气泡图：

利用下表制作气泡图：

在图表向导中选择“气泡图”

因为气泡图没有分类轴（其横坐标为序列号），我们需要添加数据标识，点击下一步，切换到数据标志标签，将系列名称打勾：

纵坐标表示%Growth,气泡大小表示Cost:

4. 单元格嵌入式图表：

利用下表制作如图中所示的嵌入式图表

在单元格中敲入如下公式：
=REPT("|",B2/400)
其中，除数的值由源数据的大小决定，源数据大，除数应相应变大，其意义在于缩减竖线的数量，使得单元格可以容纳由一系列竖线构成的图表。

5. 复合饼图：

当数据系列内值的大小差异较大时，可以制作复合饼图以便数据显示更为清楚，如下图所示：

选择饼图中的复合饼图子图表类型：

点击下一步，切换到图例标签，将显示图例的勾去掉：

切换到数据标志标签，把类别名称和百分比打勾：

选中数据系列，右键菜单，选择数据系列格式：

切换到选项标签，将第二绘图区包含最后的值改为：4

复合饼图制作完成。

6. 图表组合：

一个图表中的不同数据系列可以采用不同的图表类型显示，如下图：

首先制作一个柱形图：

在图表工具条上选择数据系列 Compensation:

在图表工具条上选择数据系列格式按钮：

在数据系列格式界面上，切换到坐标轴标签，将主坐标轴改为次坐标轴：

右键点击Compensation,选择图表类型：

将其图表类型改为折线图；
用同样的方式操作Production系列，将其图表类型改为面积图；
完成图表组合的制作。

7. 下拉菜单式图表：

写入如下的公式：index函数查找出的值由A9单元格来指定。

制作三维饼图，其数据区域为A3:I3,A9:I9,需要手工选取。

点击“视图”-“工具栏”-“窗体”，调出窗体工具条：

在窗体工具条上选择组合框，在饼图上拖放出一个组合框：

右键选中组合框，在菜单中选择“设置控件格式”：

在控制标签中做如下设置：其中单元格链接为存储控件选中项目序号的单元格。

完成下拉菜单式图表的制作：

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. 甘特图
如果想制作如下图的甘特图，需要以下4列数据：

首先制作堆积条形图：

点击下一步，切换到系列标签，删除结束日期系列：

生成的条形图如下图所示：

下面转换纵坐标的项目排列次序：
双击纵坐标，切换到刻度，将分类次序反转和数据轴交叉于最大分类选项打勾。

此时纵坐标次序已经反转
双击开始日期数据系列，在数据系列格式对话框中将边框和内部都改选为无，此时开始日期数据系列隐藏。

此时需要将横坐标开始日期改为真正的项目开始日期：
在excel中，每一个日期都对应一个数值，选中B2,查看2008-7-1对应数值为39630

双击横坐标，切换到刻度，将最小值改为39630，同样将横坐标最大值改为真正的项目结束日期。

甘特图制作完成。

案例17　动态图表

选中北京，深圳，上海以下的区域，分别定义北京，深圳，上海的名称

选择视图-工具栏-窗体，选择选项按钮，画出如下三个选项按钮：

右键选中选项按钮，在右键菜单中选择设置控件格式，在跳出的对话框中选择控制标签，在单元格链接中选择A16：

在A17单元格中输入公式：=choose(a16,”北京”,”上海”,”深圳”)，为A17定义名称：choose
在A2,A3,A4单元格中输入以下内容：

在B16单元中写入以下公式，并复制到该行1-12月的单元格中：
=VLOOKUP(A3,INDIRECT(choose),COLUMN()-1,0)

依据此数据表制作双曲线图：

案例18 比率分析

利用透视表灵活的特性，将两个结构完全相同的透视表相除，即得到可以进行任意两个因素间的比率分析图表。

案例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，条件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)>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(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: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数组公式的使用用于多条件求和。
sumproduct((条件1)*(条件2)*…) 用于计数
sumproduct((条件1)*(条件2)*…*求和区域) 用于求和

案例21 模拟运算表（略）

案例22 单变量求解

如果希望利润达到200万，则需要相应调整的单价、直接成本、固定成本、销售量为多少？

可变单元格：要求解的值所在的单元格
目标单元格：关系式表达所在的单元格
目标值：关系式的值

单变量求解的原理：根据在单元格中描述的一元方程求解方程中的未知数。使用单变量求解的前提：需要在单元格中将一元方程的方程式描述清楚。如果是函数关系，则需要在单元格中引用该函数。

案例23　规划求解

步骤：构建已知条件的关系模型，然后运行规划求解工具。构建模型的过程实际上是利用Excel单元格引用的方式将条件描述清楚的过程。

在规划求解对话框中，进行相关设置。目标单元格为求解后达到的目标。可变单元格为要求解的单元格。在约束中添加已知的其他条件。

规划求解可以求解多元方程，可以解决求极值的问题，可以替代计算较为复杂的计算过程。

案例24 自定义函数

宏的安全性级别调整
点击：工具-宏-安全性

如果定义了宏或者自定义函数并且想使用它们，那么需要将宏的安全性级别调整为中或者低。

自定义函数：
1. 进入VB编辑器

2. 选中左侧工程管理器中的当前工作簿，查看其下项目中是否存在“模块”。如果不存在，点击【插入】菜单，选择“模块”；如果存在，则略过此步骤。

3. 双击模块下的某一模块，右侧将弹出该模块的代码窗口，我们将在此代码窗口中编写自定义函数。
4. 在右侧代码窗口输入以下语句：
Function valuation(a, b, c, d)
valuation = (a + b) *0.6 + (c + d) * 0.4
End Function
其中，valuation是该函数的函数名，a,b,c,d是该自定义函数的参数，function 与end function是自定义函数的起始与结束语句。

带有判断的自定义函数：

Function limitation(job)
If job = “总经理”Or job = “市场销售” Then
limitation = 1500
ElseIf job = “客户服务” Or job = “副总经理” Then
limitation = 1200
ElseIf job = “职能管理” Or job = “业务总监” Then
limitation = 800
ElseIf job = “产品” Or job = “采购” Then
limitation = 600
ElseIf job = “研发技术”Or job = “生产运作” Then
limitation = 400
Else: limitation = 100
End If
End Function

附：VBA简介

VBA简介：

VBA 是 Visual basic for Applicatio