致命缺陷:不懂一维表!
没有一维表的支撑,神也无法帮你成为Excel高手
作者:老菜鸟来源:部落窝教育发布时间:2019-01-04 18:29:36点击:12560
今天的教程重点说的是制表习惯,只有采用一维表制作数据源表(原始数据表)才能够让后续工作变得轻松,才不会去花大量无用功学模板,写复杂公式。可以说,从接触Excel那天开始,你就需要懂什么是一维表什么是二维表,否则永远也成不了Excel高手。什么是Excel中的一维表和二维表呢?最好的答案就在本篇教程中。
我们每天都在和各种各样的数据打交道:财务数据、进销数据、客户管理数据、人事管理数据……有时候需要将数据按照领导的需要做成各种各样的报表,有时候需要将数据导入到某个管理系统中。很多Excel新手、半灌水在处理数据的过程中问题层出不穷,究其原因,最主要一条:原始数据表(数据源表)不规范,不是一维表。
何为Excel中的一维表二维表
那么什么样的原始数据表才是规范的呢?在说这个问题之前,需要明白什么是一维表、什么是二维表。
先来看一维表。
表1 表2
上图就是两个常见的一维表。简单来说,一维表具有两个典型的特征:
第一、单元格中的数据属性等于对应的列标题。
譬如:表1中单元格值“第一季度”“第二季度”属性就是季度时间,所以其列标题就是“季度”;表2中单元格值“36”“45”属性就是人的年龄,所以其列标题就是“年龄”。
第二、所有列标题之间的关系不是排除性的选填关系。
譬如:表1中“季度”“实际金额”标题下数值,并非是填写了“季度”就不能填写“实际金额”了。可能这一条,大家还有疑惑,不急,接着看二维表就了解了。
下面看二维表。
表3
表4
表3中单元格值“23954.89”“14122.31”的属性是金额,但是列标题是“东北地区”“东南地区”等,属性与列标题不一致。其实,如果把表3的表头完整地绘制出来,大家看得更明白:
很明显,表中各数字的属性是“金额”,而非列标题“地区”。
表4中列标题“博士”“硕士”“本科”“大专及以下”对当前表而言属于典型的选填关系,同一行数据填写了“博士”,就不能填写“硕士”“本科”等了。同样,把表4的表头完整绘制出来,大家看得更明白:
很明显“博士”“硕士”“本科”等列标题实际属于同一个属性“学历”下的具体属性值,按照习惯,学历都登记最高学历,因此各属性值之间具有排除性,填写了“博士”就不能填写“本科”了。
在Excel中我们可以把所有非一维表的表都称为二维表,上面的表3、表4都是典型的二维表。(注意:Excel中所说的二维表与关系模型中所说的二维表、关系表不是一个概念!!)
一维表适合存储数据、记录数据,一般作为数据源使用,我们通过一维表进行统计分析时,不管使用公式函数还是透视表都很方便。更加重要的是,当我们需要向某个系统导入数据时,一般只能以一维表的形式导入。
相对于一维表来说,二维表更加符合我们的视觉习惯,因此二维表经常用来展示数据,也可以简单的理解为二维表适合用来做给人看的各种报表。
了解以上知识后,很多朋友可能会有一个疑问:到底什么时候使用一维表,什么时候使用二维表才算是正确呢?
很简单,做数据源的时候或者是需要导入系统时,就得使用一维表;做数据展示时,就得使用二维表。举个例子:办公人员领用办公用具的时候一般需要在一张领用表上登记,记录上某年某月某日某部门的某某领取某型号(编码)的某物品多少。这样的登记表就必须是一维表,它根据事件记录数据,是后续数据的源头。而一个月后,管理人员向上级汇报公司各部门的办公用品领用情况,就使用二维表。
一维登记表
二维汇总表
数据源表采用二维表的危害
毫不夸张的说:只有正确地使用一维表和二维表才有可能高效率地使用Excel。我们就是因为没有在适合的时候正确地使用表格形式,所以给自己的工作带来很多麻烦。
如果数据源表采用了二维表,那在后续的数据汇总、分析中,就可能造成非常大的麻烦,无法直接通过透视表或者简单的公式实现汇总分析。譬如畅销书作者卢子就曾分享过自己的惨痛经验:为了从下面的原始数据表中得到不良品汇总表,苦思一周最终在朋友的帮助下编写了一个超长的公式才解决问题。
原始数据表
不良汇总表
公式:
=INDEX(小天使!G:G,RIGHT(TEXT(LARGE(MMULT((LOOKUP(ROW($5:$136),IF(小天使!$B$5:$B$136<>"",ROW($5:$136)),小天使!$B$5:$B$136)=$B$4)*(MONTH(小天使!$I$4:$DJ$4)=$F$4)*(小天使!$G$5:$G$136<>"检查数")*(小天使!$G$5:$G$136<>"其它")*小天使!$I$5:$DJ$136,ROW($1:$106)^0)+(LOOKUP(ROW($5:$136),IF(小天使!$B$5:$B$136<>"",ROW($5:$136)),小天使!$B$5:$B$136)=$B$4)*(小天使!$G$5:$G$136<>"检查数")*(小天使!$G$5:$G$136<>"其它")*(小天使!$G$5:$G$136<>"")*0.1+ROW($5:$136)%%,ROW(A1)),"0.0000"),3)*1)
一维表与二维表的转换
这时候就会出现另一个问题,为了用好一维表和二维表,我们可能经常需要在两种表之间进行转换。会不会很麻烦呢?接下来为大家分享两种简单的转换方法,希望大家能够动手操作,将其掌握。
一维表转为二维表
推荐使用数据透视表进行转换,具体操作步骤看动画演示:
平时更多的是使用一维表制作各种统计报表,单纯将一维表转换为二维表的需求并不多。
反而二维表转为一维表的需求比较多,推荐使用公式的方法进行转换。
二维表转为一维表
(1)在A11处输入如下公式后向下拉获取二维表中的季度值,作为一维表的首列。
=OFFSET($A$1,INT((ROW(A1)-1)/7)+1,0)
这个公式中需要注意“7”这个数字,因为有7个机构,所以每个季度都需要重复7次。关于这个公式的原理,可以参考之前的教程:
网站用户点击:《Excel教程:等差数列、循环数列和重复数列构造》
微信用户点击:《3个“规律”让你轻松写出Excel公式,比小学入学题还简单!超实用!》
(2)在B11处输入如下公式并向下拉获取二维表中的机构名称,作为一维表的第2列。
=OFFSET($A$1,0,MOD(ROW(A1)-1,7)+1)
在这个公式中同样要注意“7”,与机构数相同即可。公式利用的是循环构造数据的原理。
(3)在C11处输入如下公式向下拉获取二维表中的具体数据作为一维表的第3列。
=OFFSET($A$1,INT((ROW(A1)-1)/7)+1,MOD(ROW(A1)-1,7)+1)
这个公式相信大家都看出来了,有两个地方出现了7,同时兼顾了重复性和循环性。
利用公式将二维表转为一维表的几个公式,套路相对比较固定,只要根据二维表的列数去修改公式中的数字就行,本例中数字是7。
小结:
一维表和二维表互相转换的方法很多,就不一一列举了。我们今天重点阐述的是这两种表的特性和用途。同时告诉大家,养成采用一维表制作数据源表的习惯,远比掌握几个技巧会用几个公式更加重要。当然数据表的制作规范并非仅仅是一维表这一条要求,更多要求我们会逐渐介绍给大家。
本文配套的练习课件请加入QQ群:264539405下载。
做Excel高手,快速提升工作效率,部落窝教育《一周Excel直通车》视频和《Excel极速贯通班》直播课全心为你!
扫下方二维码关注公众号,可随时随地学习Excel:
相关推荐:
写公式必须会构造数列《 Excel教程:等差数列、循环数列和重复数列构造》
数据表的几条制作规范《 excel教程:Excel规范的数据录入》
数据透视表入门教程1《按需汇总数据和工作表拆分》
数据透视表入门教程2《常见疑难杂症的处理方法》
数据透视表入门教程3《透视表排序的4种方法和插入公式》