活用Vlookup和数据透视表

活用Vlookup和数据透视表
Excel是大家都非常熟悉的软件,本文与大家分享一下如何活用Vlookup和数据透视表这两个易学易用的功能,可以将原本平面的分散的多个数据表集中起来,使之变成“立体”的数据库并且可以随意“旋转”,以达到及时准确的多角度分析的需要;
一、现实需求
许多朋友可能会有这样的经历,刚给老板做完按销售部门的全年按月销售趋势报表,老板突然就想再看看,按客户类型的销售趋势是怎样的刚把按客户类型的销售趋势报给老板
之后,老板可能马上就会打电话说还想看看按产品类型的销售趋势是怎么样的之后,可能又会需要按员工性别的销售趋势、按员工年龄段的销售趋势等等,换句话说从涉及的员工、客户、产品的每一个特点都有可能成为老板关注的对象,但这几个方面的排列组合就可以产生出无数的报表;
是老板的主意变得太快还是我们做报表的速度太慢呢
大家都清楚这样数据分析会对决策起着非常重要的支持作用,而且数据提供的速度越
快越及时,所起的效用就越大;如果数据能够像我们看展览品一样,老板需要看哪个角度,分析人员马上就将那个角度转给老板看,那将是多么惬意的事情啊,老板不仅及时得到了所要的报告,分析人员的工作效率也得到较大的提高;
以前与同事提出过这个想法,有的同事说,这是在讲一个神话吧通过多年的实践总结发现,只要活用了Excel的两个功能,这个“神话”就很快变成了人人都能够很快学会的现实;
以下通过一个实例来介绍这个“神话”实现过程和所用的功能;
二、实例介绍
本实例以常见三个表为例子来介绍如何让三个图的数据旋转起来简单的报表会用之后,大家只要按同一思路,翻阅相关专业书籍,那么不管多么复杂的报表还是多个不同的报表都会达到让我们任意旋转的要求;
对于这三张图,老板可能会从员工的部门、性别,客户的城市、行政区等各个角度来对销售情况进行分析,换一句话来说,员工表和客户表中的任何一列都可能会成为对销售进行分析的一种情况,那么作为分析人员,我们如何来及时准确的来满足这一需求呢
在利用数据透视表功能将数据旋转之前我们先来看一下如何利用Vlookup的功能将分散的数据集中到一个表中;
三、组织数据：活用Vlookup使相关数据向核心数据集中
大家知道,有一个事实是无法改变的,那就是Excel是一个两维的空间表,做出的结果也必然体现在这个两维的空间表上;
为了更好的利用Excel 的数据透视表功能将数据旋转起来,我们首先需要将这些数据内容集中到一个工作表中,然后再针对集中后的数据利用数据透视表的功能将数据旋转起来,图4是我们首先想要达到的结果,也就是将销售流水表的业务员和客户的相关信息全部
集中到销售流水表中;
即使有成千上万条销售记录,我们如何实现快速的将这些信息集中到一起呢Excel提供的Vlookup函数可以帮助我们在几分钟之内就完成数据的集合功能;
许多朋友特别是非理工科的朋友一听到函数,就感到有点高深莫测,其实函数并没有那么神秘,我们只要能够很快学会家用电器是怎么使用的,那么学会函数的使用也会同样的容易;举个使用“自动洗衣机”的例子,大家只要将衣服放到规定的洗衣桶中,选择好洗衣模式,确认启动之后,就等着将衣服拿出来就可以了,至于洗衣机是怎么洗的,我们完全可以不用过问;使用函数也一样;
图5是Vlookup的使用实例,我们一步一步来揭开它的神秘面纱,看它是不是与使用洗衣机一样简单
以下是这个函数的公式：
标准函数公式：Vlookuplookup_value,table_array,col_index_num,range_lookup 实例函数公式：VlookupF2,员工$B$2：$C$13,2,FALSE
许多朋友可能一看这么一堆,就感觉很复杂,开始望而却步,其实所谓的参数也与洗衣
机的按钮功能是一样的、我们分别来看一下：
lookup_value：这个参数的目的是告诉Excel我们要查什么；实例中的“Ｆ2”的位置,是告诉Excel我们要找“刘天王”；
table_array：这个参数的目的是告诉Excel我们要在哪里找；实例中的“员工$B$2P：$C$13”是告诉Excel我们要在员工表的这些区域内去找“刘天王”;
：需要特别提醒注意的是这个区域的第一列必须是我们要找的位置列,也就是姓名列；
col_index_num：这个参数的目的是告诉Excel在指定区域内找到lookup_value之后,取该区域的第几列的值回来；实例中的“２”是告诉Excel在这个区域找到“刘天王”后,将“刘天王”所属的部门给取回来,也就是取第二列的值回来,即员工表中部门那一列;需
要特别注意的是：这个序号是从１开始数,在指定区域内按从左到右顺序;
range_lookup：这个参数的目的是告诉Excel如果找不到lookup_value,Excel怎么办实例中“FALSE”是告诉计算机精确匹配,也就是当Excel在员工表中没有找到“刘天王”
时返回错误值;强调建议使用这个函数的时候全部使用“FALSE”这个参数值,以便使你及
时发现问题,使分析更有效;
当我们将这几个设置指定给Vlookup之后,回车之后,Excel就会自动将“刘天王”所属的部门“业务二部”从员工表中找出来放到相应的单元格内,利用同样的方法,我们就可以很快的将需要做分析的相关员工客户信息从各自的表中取出来集中到我们想要分析的报表中来;
其实总结起来,我们的目的是想将销售流水表中每一行中员工对应的所属部门集中到
这张表中,Vlookup所起到的作用就是我们只要告诉要找哪一列的值流水表的员工列,然后在哪里找员工表这个数据表,找到后返回什么值员工表的部门列,找不到怎么办Ｆalse这几个条件之后,剩下的工作就全部由Excel来完成就可以了,Excel会精确的将对应员工的部门给选择出来;
快速填充公式提示：当鼠标移动到单元格右下角的时候,即鼠标变成一个小十字的时候,双击鼠标左键就自动填充到列末的位置
在完成了如何将相关数据集中起来之后,那么我们如何实现将这些数据“旋转起来”
呢以下我们将详细介绍一下数据透视表的功能;
四、数据旋转——活用数据透视表
有许多朋友一看到数据透视表的那么多功能,就开始望而却步,继续使用Excel的初级功能来完成工作,其实数据透视表类似于“堆积木“,也就是将我们需要的内容堆积到相应的位置基本上就可以完成分析的目的;
在使用数据透视表之前,我们先想一下我们最终想要的报表格式,Excel本身特点决定了报表的格式是由三个要素组成的,行、列、数据;以部门销售趋势分析为例,表1是我们需要的最终格式;
如果行列的显示,我们可以任意拖动数据表的项目进去,里面的数据随着行列项目的不同而进行调整,那么我们的数据不就旋转起来了吗
以下我们介绍一下数据透视表是如何帮助我们实现这一功能的：
第一步：首先打开刚才做好的数据表,用鼠标单击数据清单上的任意单元格;
第二步：选择数据Ｄ,执行子菜单数据透视表和图表报告Ｐ菜单项命令,进入“数据透视表和数据透视图表向导”的步骤一界面,如图6：
第三步：我们按默认设置,在“所需创建的报表类型”中选中“数据透视表”单选按钮,然后按“下一步”按钮;
第四步：“数据透视表和数据透视图向导”的步骤二要求指定要建立的数据透视表报表所基于的数据区域;如果在进入该向导之前,你没有打开一览表,可按“浏览”按钮选择
它所在的工作簿；如果进入该向导之前已经打开该工作簿并且单击了其上的任意单元格,
则Excel将自动选中整个数据清单,如图7：
第五步：进入向导的步骤三,如果你希望把数据透视表报表显示在和源数据不同的工作表上,可选中“新建工作表”单选按钮；如果你希望把透视表建立在源数据所在的工作表上,则选中“现有工作表”单选按钮,按该单选按钮下输入域后的“压缩对话框”图标进入工
作表,在工作表上选定显示数据透视表的单元格区域;如图8：
第六步：你可以在向导的步骤三中按“布局”按钮进入如图9所示界面,设置数据透视表报表的布局;大家看这就是Excel给我们提供的拖拉积木的平台：
第七步：把右边我们集中后数据表的列名称拖放到我们想要的行、列、数据位置后,你还可以用鼠标双击字段的名称进行更高级的设置;我们以部门销售趋势分析为例,如图10：
我们将“部门”拖到“行”的位置,将“销售日期”拖到“列”的位置,将“销售金额”拖到“数据”的位置;
我们看一下按“确定”、“完成”之后的结果是怎么样的
第八步：图11是“确定”之后的结果：
我们发现数据已经按照部门进行汇总了,但是销售日期却是按天进行汇总,并不是我们想要的按月进行汇总的结果;这个问题Excel 早已经帮我们想好了,我们点击“销售日期”按右键,选择“组及显示明细数据”及下拉菜单“组合”,就会出现“分组”的小窗口;
第九步：日期分组示例见图12：
大家可以看到Excel提供了按月、按季度以及按年度的组合,可以根据我们的需要进行选择;在这个例子中我们选择“季度”之后按“确定”;就是我们想要的结果,如图13：
第十步：数据透视表扩展,大家看到在“部门”和“销售日期”边上都有下拉箭头,我们点击下拉箭头可以任意选择所需要查看的数据;
通过这个实例我们利用Excel的两个功能实现了将数据旋转,分享一下做报表的思路,有关更详细的功能介绍请参阅相关专业书籍或Excel的帮助功能;。