|
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个数组按从左向右方向依次合并连接到一起，形成新的数组输出结果。
回顾关键内容，善用图片表达，学会建立联系，拓展深度广度，浓缩关键概念，应用到行动中，善于归纳总结，尝试进行分享。
|
|
|
