Excel情报局
职场联盟Excel
大家好,今天我们来学习用函数的方法解决二维表向一维表转换的问题,以前我们可以使用Power Query编辑器或者数据透视表的方法来解决,但是总有小伙伴们说不会Power Query编辑器,也嫌弃数据透视表的方法不易理解,那么今天我们就用几个新函数组合使用完美的解决它。只要你的微软Excel版本或金山WPS表格版本足够新,就可以抢先尝尝鲜了!
如下图所示:
左表是款式与型号的数量表,有行标题、列标题和值区域,每一行与每一列的交叉位置确定一组数量值,很显然是典型二维表格式。现在我们想要将其转换为右表所示的一维表,特点就是说每一行为一组对应的数据。
下面我们分几步一步一步的理解组合函数的原理。只要大家用心看完,肯定会豁然开朗!
解决这个问题的总体思路一定是确定一维表的款式列,以及对应的型号列和数量列,才能得到完整的一维表格式的数据。那么如何确定呢?下面我们先来看看如何确定款式列。
首先在F2单元格输入函数公式:
=A2:A4&B5:D5
公式中的A2:A4&B5:D5,使用A2:A4区域的款式连接空白区域B5:D5(注意空白区域B5:D5,该区域的列数要与A2:A4区域行数保持一致),那么A2:A4区域的3个数组元素会分别与B5:D5区域内的3个空值相合并,并输出为一个新的数组,其内部数组元素如下所示,3行3列的数据。
数组结果自动溢出:
{"冲锋衣","冲锋衣","冲锋衣";"派克服","派克服","派克服";"羽绒服","羽绒服","羽绒服"}
我们立即使用TOCOL函数将3行3列的内存数组元素转换为一列显示:
=TOCOL(A2:A4&B5:D5)
TOCOL函数的作用是将区域的数据转换成一列。
函数语法:
=TOCOL (数据区域,忽略空白和错误,指定行/列扫描)
第一参数是必选项,是需要转化成列的数组。
第二参数是可选项,它有四种情况:用0表示保留所有值(省略默认),用1表示忽略空白值,用2表示忽略错误值,用3表示忽略空白和错误值。
第三参数是可选项,用FALSE表示按行扫描(省略默认),用TRUE表示按列扫描。
用TOCOL函数很容易将A2:A4&B5:D5合并后的数组,省略第二参数和第三参数后将所有数据,按先行后列的顺序合并成了一列显示。
新的内存数组自动溢出:
{"冲锋衣";"冲锋衣";"冲锋衣";"派克服";"派克服";"派克服";"羽绒服";"羽绒服";"羽绒服"}
确定型号列和上面的方法是相同的原理:
在G2单元格输入函数公式:
=B1:D1&E2:E4
注意空白区域E2:E4,该区域的行数要与B1:D1区域列数保持一致。
输出3行3列的内存数组:
{"S","M","L";"S","M","L";"S","M","L"}
用TOCOL函数将3行3列的内存数组转换为一列显示:
=TOCOL(B1:D1&E2:E4)
数组自动溢出显示:
{"S";"M";"L";"S";"M";"L";"S";"M";"L"}
确定数量列就更简单了:
=TOCOL(B2:D4)
直接使用TOCOL函数将B2:D4区域数量转换为一列显示:
{2;4;9;7;5;10;1;7;2}
至此,款式、型号和数量列就确定好了,很巧妙的是三列数据是一一对应的,与数据源区域数据也是对应吻合的。
我们还可以用HSTACK函数,直接将三个TOCOL函数相连接,一次性得到结果:
=HSTACK(TOCOL(A2:A4&B5:D5),TOCOL(B1:D1&E2:E4),TOCOL(B2:D4))
HSTACK函数官方定义:按水平顺序追加数组,即数据汇总。(以前讲过其合并的几种模型,可以去查啊,不讲了)。
HSTACK函数可以将3个数组按从左向右方向依次合并连接到一起,形成新的数组输出结果。
回顾关键内容,善用图片表达,学会建立联系,拓展深度广度,浓缩关键概念,应用到行动中,善于归纳总结,尝试进行分享。
联系客服