▲ 判断数据是一维表格还是二维表格的最简单的办法,就是看其列的内容。如果每一列都是独立的值那就是一维表,如果每一列都是同类值那就是二维表。一维表是条绳,二维表是张网。 如下图所示,一维表的【成绩】都是在一列出现的,而二维表的【成绩】是按照科目分列在不同的列。 规范的一维表在后期能够通过数据透视表进行高效和便利的汇总。所以建议大家一开始创建表格的时候尽量按照一维表的模式创建,这样即使后期需要二维表的样式,也可以通过数据透视表获得。 但是如果二维表已经做好了,需要对其进行一些分类汇总,这个时候我们怎样快速将二维表转换成一维表呢? 我们有两个方法:① 手动调整配合函数法。②逆透视法。(仅office2016版本有此功能) 今天我们先来看看如何快速通过手动调整并结合函数MATCH和HLOOKUP来实现一维表变二维表。如果您对MATCH函数不太了解,请您参见往期教程:Excel031-VLOOKUP的得力好帮手MATCH之表格的移位大法。 我们先来看看HLOOKUP的函数解析。4个参数: HLOOKUP(lookup_value, table_array, row_index_num, [range_lookup]) ①lookup_value:要找什么?可以是值、 引用或文本字符串。 ②table_array:在哪找? ③row_index_num:返回值所在行号。 ④[range_lookup]:查找方式。(TRUR/FALSE,0或者1) 我们来看看HLOOKUP的简单应用。 案例1:用HLOOKUP求出「刘瑞锋」的数学成绩。 我们也可以用MATCH函数来求出「刘瑞锋」所在行号:=MATCH('刘瑞锋',G1:G6,0),结果为4。然后就可以用HLOOKUP求数学成绩啦,公式为:=HLOOKUP('数学',I1:I6,4,0)。 好了,抓紧来看看怎样利用HLOOKUP将一维表变二维表吧。 案例2:将图1所示的二维表转换成图2所示的1维表。 图1:员工销售业绩(二维表) 图2:调整后的员工销售业绩(一维表) 这种转变看似挺复杂,但是理清了里面的逻辑就很简单啦!具体方法如下: ① 从表【员工销售业绩】里将姓名一列复制到新的工作表,并将工作表重命名为【调整后的员工销售业绩表】。 ② 在B列添加【辅助列1】,B2录入公式:=MOD(ROW(A3037),3037)+1。这里的3037是指员工的个数,我们可以根据不同员工的个数进行调整。这样就可以构建一个1-3037的序列。 ③计算一维表的行数。一维表的行数应该等于二维表列的行数乘以行的列数。二维表中,我们有3037个员工,所有年月加起来一共是60列,所以一维表的行数应该为3037*60=182220。 ④在名称框中输入B2:B182221,回车,CTRL+D向下填充公式。如下图所示: ⑤在表【员工销售业绩】的第一行B1录入公式:=B3&B2,向右填充至最后一列。 ⑥将表【调整后的员工销售业绩表】中【辅助列1】进行升序排序。 ⑦用定位空值填充姓名一列,使其下方的单元格等于上一单元格。 ⑧复制表【员工销售业绩】中的B1:BI1单元格,到任意空白处粘贴为值,再复制,到任意空白处转置粘贴备用。 ⑨在表【员工销售业绩】中C列添加辅助列2,复制上一步转置粘贴的数据,在表【员工销售业绩】的名称框中输入C2:C182221,,回车,CTRL+V粘贴。 ⑩用公式根据【辅助列2】提取出年份(D列)和月份(E列),并将公式向下填充。 年份:=MID(C2,FIND('月',C2)+1,4) 月份:=LEFT(C2,FIND('月',C2)) ?在F列求出销量,录入公式并向下填充:=HLOOKUP(C2,调整后的员工销售业绩!B:BI,MATCH(A2,调整后的员工销售业绩!A:A,0),0) 文字描述难免有不详尽的地方,视频讲解更详尽哦! |
|