Excel Function Learning Material_Preparation

To protect the confidential and proprietary information included in this material, it may not

be disclosed or provided to any third parties without the approval of Hewitt Associates LLC

.Excel Function Learning Preparation for Salary Structure Training

Aon

Hewitt

Content

Vlookup

Advanced Use of Vlookup – Approximate match –

Matrix match

Pivot Table Excel Chart

34入职日期

=vlookup( “张三”,“数据表格区”, 3 (年龄列号)，0)Formula: 3 年龄

3 年龄

2 部门

1 姓名

张三丰张三1 姓名From available database 从现有数据库调取数据

从现有数据库调取数据

Vlookup 查找取数

indicator – “lookup_value”通过对共有指示符-“查找值”连接

Vlookup ( lookup_value table_array , Col_Index_Num, [check])

指示符

数据表格区 相对列数 匹配方式

E.g. =VLOOKUP($A6,'employee info'!$A:$E 0)

Pull out data from available data source (cont’)

从现有数据库调取数据（续）

Note注意事项:

?Indicator must be in the same cell type (Value VS Text), check replicated items and blanks 两张表格的指示符必须在格式上统一(数值型VS 文本型),务必清理好空格和重复项

?First column of “table array”must be the indicator

数据表格区首列必须为指示符所在列

?Before copy the formula, remember to properly freeze($*) column of indicator and table_array 在复制公式前，记住要对指示符以及数据表格区所在列进行合理固定

(Cell type*)Convert the indicator into the same type 将指示符进行格式统一

From Text to Value: =Value(A6) or Click the tag here

从文本到数值：公式=value(A6) 或点击此处的智能标签

($*)Absolute reference (freeze) 绝对引用(固定符号)

Shortcut快捷键:F4

.

Content

Vlookup

Advanced Use of Vlookup – Approximate match –

Matrix match

Pivot Table Excel Chart

Advanced use of Vlookup – Approximate match Vlookup 公式的高阶用法–模糊匹配Vlookup in Performance Assessment 在绩效考核中的运用

Given 已知：Convert table connecting assessment score to performance rate

数与绩效等级的转换表

Wanted 求值：Performance rate for each staff in Department A 等级

E.g. =VLOOKUP(E2,$A$1:$B$6,2)

Vlookup ( lookup_value , table_array , Col_Index_Num, [Check]*)

? [Check]: value =1,True or omitted, approximate match is returned.

当匹配方式的值为1,真或者省略时，返回模糊匹配结果。

? Approximate : return the value of the largest indicator that is less

than the

lookup_value . 模糊匹配：返回数据表格区中，小于且最接近查找值数值Note 注意: Indicator in table_array 2, ..., A-Z, FALSE, TRUE 数据表格区的指示符Convert Table

指示符

Advanced use of Vlookup–Matrix match

Vlookup公式的高阶用法–矩阵匹配

Vlookup in Salary Review 在年度调薪中的运用

Given已知：

1.Performance rate and comp ratio of each employee. 1.员工绩效等级及其薪酬竞争力比率

2. Salary increase matrix based on preceding two factors. 2.根据上述两个条件设计的薪酬增长矩阵Wanted求值：Salary Increase rate for each employee员工调薪率

E.g. =VLOOKUP($J3,$A$2:$G$13,MATCH($K3,$A$3:$G$3,0),1

)

Salary Increase Matrix

Indicator 指

示符

Advanced use of Vlookup2 – Matrix match (cont’) Vlookup 公式的高阶用法2–矩阵匹配（续）E.g. match ($K3,$A$3:$G$3,0) = 3

MATCH(lookup_value ,lookup_array ,match_type)

MATCH returns the position of the matched value within lookup_array,返回查找值在数据表格区的位置，e.g. MATCH(“b”,{“a”,“b”,“c”},0) returns 2 在a,b,c 数列中，b 位于第二位

Note 注意：If match_type is 0, MATCH finds the

first

value that is exactly equal to lookup_value. Lookup_array can be in any order. 若匹配方式为0，匹配返回数列中第一个与查找值精确配对值的位置，数据表格区不需要排序

Salary Increase Matrix Indicator2 指示符2

Content

Vlookup

Advanced Use of Vlookup –

Approximate match –

Matrix match

Pivot Table Excel Chart

Pivot Table and Pivot Chart report

数据透视表Pivot table is a multidimensional table that can be rearranged to allow different views of the data.

数据透视表是一种对大量数据快速汇总和建立交叉列表的交互式表格,利用它可以很快地从不同

角度对数据进行分类汇兑.Count 计数

Average

平均

Pivot Table Process

数据透视表使用步骤Process

数据透视表使用步骤Select data area 选中数据区域Data(数据) - PivotTable and PivotChart Report(数据透视表) –

next to Layout in Wizard(在向导中

选择至布局界面)Drag classifying factors to “row” ,”column” or “page” area, then drag target data in “data” area 拖曳分类因素至透视表” 行”，”列”或”页”位置，随后

拖曳汇兑数据至”数据”区

Choose way of Calculation 选择汇 兑方式Update and adjust 调整显示信息或 运算方式分类区汇兑区

汇兑

方式

Content

Vlookup

Advanced Use of Vlookup – Approximate match –

Matrix match

Pivot Table Excel Chart

Regression line Chart 平滑回归图-

整体情况比较

Trend Line Analysis - by Level

200,000

400,000

600,000

800,000

1,000,000

1,200,000123

4567Market My Data

Compound Chart 组合图（柱状回归线图）- 薪酬架构回顾0

100,000

200,000

300,000

400,000

500,000

600,000

700,000

800,000

900,000

1,000,000

1,100,000

1,200,000

1,300,000

1,400,000

1267

Mid Market Data Structure Mid Trend Line Maket Trend Line Process of drawing Compound Chart:1.Draw the basic chart for one series 2.Add new series in Source Data 3. Assign Name and preliminary Values to new series 4.Change chart type of new series 5. Final adjustment, and complete adding the data needed for the new chart type

Compound Chart 组合图（柱状散点图）-薪酬架构回顾0

150,000

300,000

450,000

600,000

750,000

900,0001,050,0001,200,0001,350,0001234567

组合图形制作流程:

1.根据某一数列制作基本图

2.在数据源中添加新的数列

3.指定新数列的名称及初步数值

4.修改新数列的图表类型

5.后期调整，补全新图表类型所需数据

本文来源：https://www.bwwdw.com/article/kcme.html