Excel应该这么玩——5、三种数据:Excel也是系统

    Excel最常用的功能就是记录数据,把数据按照行列记录下来。这部分数据是源数据,是业务活动中最原始的流水账,作为后续操作的依据。为了从源数据中得出一定的结论,需要对源数据进行分析得出报表数据。在分析数据的过程中,除了用到根据实际情况变化的源数据,还存在不变的数据,例如各种比例、基数、基础信息,这里把这一类数据叫做基础数据。其实在业务系统中的数据也可以按这三种数据分类,只不过业务系统能提供友好的用户界面,Excel则完全面向数据。虽然Excel在用户界面上没法和业务系统相比,但对于数据的分类和处理方式和业务系统是一致的,遵循相同的规范会让数据处理起来更高效。
 
1、基础数据
    基础数据是指最基础的配置数据和基本信息数据,一般创建之后不会经常改动。例如组织架构、员工信息、产品分类、产品信息这些都属于基础数据。
基础数据又可以分为两类,单个基础数据和列表基础数据。单个基础数据一般是独立存在的单个值,例如各种基数。这种数据可以使用命名单元格的方式使用,后面做报表的时候只需要通过名称引用即可。列表基础数据是一类数据的集合,有多行或多列,例如组织架构信息、产品信息。对于列表基础数据可以用命名表格的方式使用。
    在命名表格中介绍了COLUMN函数,指定某个表格的列名,COLUMN函数就会返回该列的从1开始对应的序号。但是这个序号是从A列开始计算的,而VLOOKUP函数中第三个函数是指定从当前表格中的第1列开始计算的。如果在一行中存在多个表格,那么对于第二个表格使用COLUMN函数就会得到错误的列序号。所以建议基础数据纵向排列,不要在一行中存在多个表格。纵向排列还有一个好处,可以随意的添加和删除行,而不用担心删除其他表格的数据。纵向排列有一个弊端,定位数据会比较麻烦,需要较大幅度操作滚动条。但是这个弊端可以很容易避免,可以通过添加链接的方式。添加链接的方式会在后面的文章中介绍。
    实际维护基础数据时,建议对于较少量基础数据,可以把多个表格放在一个Sheet中。如果某一类基础数据的行数非常多,建议放到单独的Sheet中。
 
2、源数据
    源数据通常是手工输入,或者是从其他业务系统中导出的。如果是手工输入的,尽量减少录入的数据量,可以通过引用基础数据来实现自动填充一部分数据。另外为了避免手工输入数据发生错误,对于有一定限制的数据,建议使用设定数据类型和设定数据有效性(下拉列表)的方式进行限制。
    在设定源数据表格的列时,需要注意不要在一列中包含复合信息。服装有尺码和版型之分,例如衬衫36A表示36码A版。如果混合在一起,要分析出ABCY这四种版型中哪一个卖的最多,就没法直接用数据透视表分析出来了。为了给数据透视表提供正确的数据,源数据必须有列标题,不能有合并单元格,不能有空行。
    在设计源数据结构的时候,最需要注意的是不要使用维度项作为列,特别是把多个维度做成多表头的形式,如下图:
    这个是我看过的一个真实例子的缩减版(真实情况更复杂),把多个尺码和颜色做成列,导致后续分析的时候根本没法使用数据透视表。例如需要统计产品A的每个尺码各卖了多少件,或者按颜色统计,这个格式只能手工计算。正确的做法应该是这样:
    虽然这样导致数据行增多了,但后续分析起来更方便。
 
3、报表数据
    Excel提供了很强大的数据分析工具——数据透视表。以下面的数据作为源数据进行分析(使用命名表格,名称为销售源数据),要求得出每种产品按尺码统计的销售数量
    切换到插入菜单,点击数据透视表。
    在弹出的对话框中输入命名表格的名称,如果没有使用命名表格,则使用鼠标选择要分析的数据区域,注意需要包含列标题。数据透视表的位置一般建议放在新工作表,避免对源数据所在的Sheet做修改。
    点击确定按钮之后,在新工作表中会展示如下提示信息:
    在工作表的右边会显示数据透视表的操作界面:
    通过拖动的方式将产品拖放到行区域,将尺码拖放到列区域,将梳理拖放到值区域。
    在工作表中会自动生成如下报表:
    可以把列标签和行标签修改为码数和产品。如果需要分析的是颜色,就把颜色拖到列区域,如果颜色和尺码都要分析,就可以同时把两个字段都拖到列区域。这个报表的格式就是前面提到的错误的源数据格式。
    在列区域中调整尺码和颜色的顺序,报表也会跟着改变。
    有了规范的源数据,就可以使用数据透视表通过拖拉的方式灵活得到想要的报表,根本不需要手工做报表。当然这里只是简单介绍数据透视表的用法,实际它的功能更强大。
 



posted @ 2014-11-06 21:56 Alex Leo 阅读(...) 评论(...) 编辑 收藏