Excel不为人知的一些实用技巧.doc

Excel不为人知的一些实用技巧Excel实现数据挖掘从数据到信息到决策这一篇将根据一个虚拟的故事，来介绍如何通过历史数据来帮助一个销售人员发现规律信息从而辅助他来做一些决策信息。 本文的主角是Tim，Tim在一个销售部门，部门最近决定做新一轮销售计划，然后根据计划结束时，各个销售人员的销售业绩来进行KPI考核。Tim的部门在确定了销售任务后，其它人很快的投入到店面的销售工作中去，而Tim则跑到了公司的IT维护部门，向IT 部门要了一份历史的客户数据。此时已经有人在责怪Tim，说你一个销售不去外面跑业务，怎么跑到IT部门不务正业来了，而且，专门要以前的客户数据，居然不去不关心新客户。 其实，Tim还有一个搞IT的朋友，James，他是一位数据分析师，在零售行业也有一定的经验。Tim的第一个想法就是找 James给些建议。平时两个人聊天的过程中，James给Tim讲了无数多次关于商业智能的知识。虽然Tim是搞销售的，但是受到James的经常灌输，自己对里面的知识也有了一些印象，比如，数据挖掘，当然这个概念对于搞销售的Tim来说简直就是天书，正所谓隔行如隔山，但是有一点他非常理解，那么就是：从数据到信息，也就是说，从数据中获取信息。于是，销售任务一开始，Tim就跑到了IT部门要了这样一份数据，来看看James到底能从这里头得到什么样的信息，从而能帮助Tim更准确的知道因该给什么样的人推荐产品才比较靠谱。James拿到Tim的数据之后，大概的浏览了一下：从这份数据中，James看到，里面包含了客户的性别，婚姻状况，年收入以及家庭相关的和教育等基本信息。其中最后一列关键信息，就是客户是否购买过产品。如果购买过就记为1，否则就记为0。James拿过来这个Excel文件，首先做了一个关键影响因素分析。根据这个工具，首先指定关注的列，就是客户是否购买了产品的标记：这一篇将根据一个虚拟的故事，来介绍如何通过历史数据来帮助一个销售人员发现规律信息从而辅助他来做一些决策信息。 本文的主角是Tim，Tim在一个销售部门，部门最近决定做新一轮销售计划，然后根据计划结束时，各个销售人员的销售业绩来进行KPI考核。Tim的部门在确定了销售任务后，其它人很快的投入到店面的销售工作中去，而Tim则跑到了公司的IT维护部门，向IT 部门要了一份历史的客户数据。此时已经有人在责怪Tim，说你一个销售不去外面跑业务，怎么跑到IT部门不务正业来了，而且，专门要以前的客户数据，居然不去不关心新客户。 其实，Tim还有一个搞IT的朋友，James，他是一位数据分析师，在零售行业也有一定的经验。Tim的第一个想法就是找 James给些建议。平时两个人聊天的过程中，James给Tim讲了无数多次关于商业智能的知识。虽然Tim是搞销售的，但是受到James的经常灌输，自己对里面的知识也有了一些印象，比如，数据挖掘，当然这个概念对于搞销售的Tim来说简直就是天书，正所谓隔行如隔山，但是有一点他非常理解，那么就是：从数据到信息，也就是说，从数据中获取信息。于是，销售任务一开始，Tim就跑到了IT部门要了这样一份数据，来看看James到底能从这里头得到什么样的信息，从而能帮助Tim更准确的知道因该给什么样的人推荐产品才比较靠谱。James拿到Tim的数据之后，大概的浏览了一下：从这份数据中，James看到，里面包含了客户的性别，婚姻状况，年收入以及家庭相关的和教育等基本信息。其中最后一列关键信息，就是客户是否购买过产品。如果购买过就记为1，否则就记为0。James拿过来这个Excel文件，首先做了一个关键影响因素分析。每一个属性的值都变成了一个下拉列表，各个属性的Impact影响值加在一起最后得出一个分数，这个分数如果达到一定的高度，那么就表明这个客户很有可能会购买产品。于是，James把这个Excel文件发给了Tim，这样Tim就可以根据收集到的客户信息对应选择里面的项，然后通过计算知道用户是否是潜在客户。这个文件帮了Tim不少的忙，也准确的识别了一些客户。但Tim也往往抱怨，在出去跑业务的过程中，电脑不是总带在身边，所以往往很难及时的做出判断。James知道Tim的这个烦恼后，告诉他，你把第三份分析报告中的表格打印出来就可以了。算好总分后，对比下面的分数，也就是说分数最起码要达到601才有可能是一个潜在客户。于是，对于IT系统的操作不是很熟悉的Tim就可以每次对着这个单子来对新客户作出潜在客户的判断。 在这个故事里，James并没有用到什么太复杂的数据，通篇他只在用一个软件，就是Excel，Excel从2007版本开始通过SQL Server的功能扩充可以实现简单的数据挖掘功能，它通过SQL Server Analysis Services分析服务来生成临时的挖掘模型，通过样本数据以及挖掘模型和算法来发现数据中存在的一些规律，相关性等信息。 通过Excel对SQL Server数据挖掘功能的封装，使得用户即使不清楚数据挖掘的具体算法也可以实现数据挖掘的功能从而做挖掘预测分析来辅助决策，甚至都不需要用户了解什么样的挖掘模型算法适合解决什么样的问题，只需要关注Excel里所带的表分析工具就可以做简单的预测分析。 总之，数据挖掘，不是已不是数据分析师们的专属，有了Excel，你也可以。Excel PowerPivot建立简单的分析模型 数据透视表是一个很灵活的工具，通过这个工具用户可以很容易的生成自己需要的报表。无论是对于专业的IT用户还是业务部门的用户，他们都很熟悉Excel这个工具，并且对于PowerPivot的使用方法也相当的炉火纯青。 传统透视表的数据来源可以是Excel工作表，也可以是分析服务中的多维数据集这两种主要的方式。相对前者由于数据是存储在Excel的工作表中，所以业务操作人员很容易上手，很适合小规模的数据统计分析。后者分析服务的多维数据集这种方式，由于数据是以一种特殊的方式聚合在独特的文件系统中，所以适合大规模的数据量分析，缺点是分析服务的开发对于IT的要求比较高，只能由IT人员完成，所以业务人员的一个需求往往会等待很长的时间才会得到响应。 那么，业务操作人员是否可以有一种高性能的去分析稍微大一点的规模的数据呢？PowerPivot就是微软提供的一个方案。在这个方案中，数据直接加载到内存当中，并且经过一定的优化，保证了通过透视表的统计有一个很高的性能。 首先，在Excel 2013之前的版本中，这个工具是需要单独下载的。如果你没有Office 2013，那么我建议你的版本不要低于2010，在这个版本之中PowerPivot的版本得以演化。下载需要留意Excel对应的语言版本还有是32位版还是64位版。还有需要注意的一个地方是，这个是PovitTable绿色软件下载吧是针对 Excel 2010的第二个版本，之前还有一个版本，在微软目前的教程以及本文的介绍中缺失了部分功能。所以如果你已经先前安装了PowerPivot，请务必确认这个版本是否正确。安装完毕后，打开Excel后，可以看到Ribbon菜单中多了一项：使用这个工具前，需要先准备数据。你可以直接使用在 Excel工作表里面的数据，也可以使用SQLServer等其它数据源的数据。这里假定一个销售部门的数据，已经在IT部门的数据仓库中存在了，而销售分析人员，只需要把相关的数据导入到PowerPivot中，然后通过简单的设置就可以生成自己的分析模型了。在PowerPivot选项卡中单击PowerPoint Window，会打开PowerPivot工具： 假定IT部门已经授予了销售分析部门的数据仓库系统部分响应表的访问权限，那么这里分析人员需要做的就是把相应的表导入到PivotTable工具中。点击上图工具栏中的From Database： 选择From SQL Server。从这里可以看到，PowerPivot支持的数据源很多，还有Access和SSAS等。 在弹出的表导入工具中，输入数据仓库所在的服务器名称和数据仓库的名称。 在数据仓库下的所有表被列了出来。在这个界面中，可以通过Friendly Name来指定一个友好名称，然后通过Filter Details指定需要表里的哪些列。在PowerPivot有一个很赞的功能就是Selected Related Tables，选择相关表。假如在数据仓库中已经定义好了主外键关系（现在似乎很少有人愿意这么做，但我觉得定义好还是一个不错的习惯），那么在这里面会直接检测到，并且自动勾选上那些维表。点击这个按钮后，可以发现很多Dim开头的维表已经都被选中了。实际的操作中，还是建议这里给每一个表都指定一个Friendly Name，并且做适应的Filter。但这里为了演示方便直接点Finish开始导入数据。在这个关系视图里继承了数据仓库中定义的主外键结构（熟悉SSAS的同学可以把这里理解为数据源视图的定义）。假如实际环境中，数据仓库没有定义这部分内容，就需要自己来指定表之间的关系（这个过程对于开发SSAS的朋友来说，更像是在指定维度用法）。而方法很简单，假如我要建立FactInternetSales表中ProductKey和DimProduct中的ProductKey列的主外键关系，只需拖拽FactInternetSales表中的ProductKey字段到DimProduct表中的ProductKey字段就可以了。接下来指定一个层次结构。建立层次结构的好处在于，可以方便在后续的透视表操作中，方便维度属性的导航，比如对于区域维度的，从大洲到国家到省再到市，或者一个时间维度的从年到半年再到季度然后月份和天的导航。这里我们在DimDate表中定义一个年月日的层次结构导航关系。右键DimDate表，选择Create Hierarchy：拖拽DimProduct的Color到Slicers Vertial，DimDate的DateHierarchy到Row Labels，FactInternetSales的Sum of SalesAmount到Values。图中可以看到Color切片器，通过这个切