用Excle处理数据常用的两大利器就是VLOOKUP和透视表,对于一些用Excle处理数据的公司,面试也会问是否懂得这两大利器。学好他们,不仅有助于快速处理数据,还可能助你拿到Offer。
1.VLOOKUP
VLOOKUP是按列查找,最终返回该列所需查询列序所对应的值。
=VLOOKUP(lookup_value,table_array,col_index_num,range_lookup)
注意:
①查找的区域最好锁定,否则拉动公式查找区域会变。选定区域,按F4键即可,行列字符前都会出现$。
②查找值与查找区域的匹配值数据类型应一致,否侧会无法匹配。最简单的就是全选区域、复制,在原区域粘贴时选择为数值或文本。
③查找值的匹配值应为查找区域的第一列。
(1)精确匹配-- range_lookup为FALSE或0(常用于数值匹配)
例1:按表2的地市顺序,从表1中获取D值
运用VLOOKUP即可快速解决这一类问题,=VLOOKUP(G4,$A$3:$D$16,4,0),其中“G4”是查找的值即查找的地市,它的匹配值必须是是查找区域的第一列;”$A$3:$D$16”是锁定的查找区域,选定A3:D16,按F4键即可锁定;“4”是返回数据在查找区域的第几列数(即想要的数值在查找区域的第几列),“0”精确匹配(或FALSE)。最后下拉公式D值填充完毕。
2)模糊匹配 -- range_lookup为TRUE或1(常用于分类匹配)
例2:将业绩按下列区间(见表2)进行分类
VLOOKUP模糊匹配是分类数据向组内阀值(最低值)靠拢,公式:
=VLOOKUP(C3,$F$8:$H$13,2,1),“$F$8:$H$13”F4锁定区域,“1”模糊匹配(或TRUE)。下拉公式即可得到每个业绩的分类。
2. 透视表
数据透视表是一种交互式的表,可以进行某些计算,如求和与计数等。所进行的计算与数据跟数据透视表中的排列有关(也就是更方便进行按类别计算,常用的就是计数、求和)。
例3:计算星级店铺各地市区县每个业绩分类的个数、业绩和
(1)选中计算区域,插入 → 数据透视表 → 确定
(2)选择数据透视表字段
依据自己的计算需求分别将各标签拖拽到“在以下区域间拖动字段”下面的行、列、∑值以及筛选器的空白处。故把地市、区县放在行,把业绩分类放在列标签(∑值是自动填到列的),把业绩、区县放在∑值,把是否星级店铺放在筛选器(通过点击表B1中的“▾”,点击是或否,本例子选是)。
其中:∑值也可以通过下面的“▾”进行设置。点击值字段设置,通过计算类型,设计自己的计算要求。
(3)设置数据透视表的布局(此功能2010及以上版本能用)
从上面图片可以看出,透视表的表格与我们日常用到的不太相符,所以我们要设置数据透视表的布局。点击透视表:
- 设计 → 报表布局 → 以表格形式显示(或者点击透视表区域 → 数据透视表选项→(打勾)经典数据透视表布局(启用网络中的字段拖放))
- 设计 → 报表布局 → 重复所有项目标签
- 分类汇总 → 不显示分类汇总
(4)最终呈现结果