Excel 2024/365新函数系列讲座(24):TOCOL函数(2)——将二维表转换为一维表 (本文阅读时间较长,要详细介绍逻辑思路,请耐心)
- 2026-09-15 11:43:10
Excel 2024/365新函数系列讲座(24):TOCOL函数(2)——将二维表转换为一维表 (本文阅读时间较长,要详细介绍逻辑思路,请耐心)下图左侧A列至E列是一个标准二维表，现在的任务是将其转换为H列至J列的一维表，该一维表由产品、地区和销售额三列构成。 
1、问题分解 我们先分析一下二维表转换为一维表的基本逻辑。 对于本例而言，每个产品每个地区的销售额是核心数据，共有4列（4个地区）5行（5个产品）数据，共4×5=20行数据。 因此，我们需要先将这4列5行的销售额数据展开为一列，再分别设计两列用于标识每个数据所属的地区和产品。 下面分解这种转换过程。 1) 假若按照默认的按行扫描，将4列5行的销售额数据进行转换的公式如下，结果如图1所示。 =TOCOL(B2:E6) 
图1 按行扫描，将5行4列的销售额数据转换为一列 2) 由于4列5行的销售额数据是按行扫描转换为一列的，也就是说，每4个数据分别对应4个地区，因此现在的难点就是如何再得到一列对应的地区名称了。 这个处理起来也不复杂，将原始表中的所有销售额数据单元格全部替换为对应的地区名称，如图2所示 
图2 向下填充地区名称 那么，将这样的地区名称，使用TOCOL函数按行扫描这样的数据区域，就得到了与前面转换后的销售额所对应的地区名称列，如图3所示。 
图3 转换后的销售额所对应的地区名称列 而要直接以源数据区域得到这样的地区名称列，公式如下，结果如图4所示。 =TOCOL(IF(B2:E6<>"",B1:E1,"")) 
图4 获取地区列 3) 同样的道理，如果要设计一列与转换后的销售额数字对应的产品名称列，也需要将原始表中的所有销售额数据单元格全部替换为对应的产名称，，然后依据这样名称表，再使用TOCOL函数转换，得到与销售额对应的产品名称列，如图5所示。 
图5 转换后的销售额所对应的产品名称列 而要直接以源数据区域得到这样的产品名称列，公式如下，结果如图6所示。 =TOCOL(IF(B2:E6<>"",A2:A6,"")) 
图6 获取产品列 4) 这样，我们就得到分别表示产品名称、地区名称和销售额的3列数据，最后使用HSTACK函数将这3列合并起来，就是最终的一维表了。 2、转换公式 根据前面的分步推理，我们有了解决思路和解决方案，最终的转换公式如下，结果如图7所示。 =HSTACK( TOCOL(IF(B2:E6<>"",A2:A6,"")), TOCOL(IF(B2:E6<>"",B1:E1,"")), TOCOL(B2:E6) ) 
图7 转换公式及结果 3、注意事项 前面介绍的公式是基于根据原始数据表中没有空单元格，因而使用IF函数判断处理地区名称和产品名称，如果原始表中有空单元格，结果表中就会出现一些空行的产品和地区，如图8所示。 
图8 原始数据出现空单元格时的结果 不过话说回来，原始表格中各个地区各个产品的销售额单元格不可能为空的，最低也是数字0。如果真存在了销售额空单元格，最好将其填充为数字0。 4、完善公式 如果原始表格中不可避免会存在空单元格，而又不想填充这样的空单元格，则需要使用下面的公式了，如图9所示。 =HSTACK( TOCOL(IF(B2:E6<>"",A2:A6,NA()),2), TOCOL(IF(B2:E6<>"",B1:E1,NA()),2), TOCOL(B2:E6,1) ) 这个公式中，对于地区名称和产品名称引用判断，如果是销售额是空值，就将空单元格替换为错误值（使用NA函数），而在使用TOCOL转换产品名称和地区名称时，设置忽略错误值来跳过空单元格， 
图9 完善公式 5、自动添加标题 如果要自动转换成带第一行标题的二维表，可以使用下面的公式： =VSTACK( {"产品","地区","销售额"}, HSTACK( TOCOL(IF(B2:E6<>"",A2:A6,"")), TOCOL(IF(B2:E6<>"",B1:E1,"")), TOCOL(B2:E6) ) ) 也就是使用VSTACK函数将一个文本常量数组与HSTACK函数返回的数组合并起来。 ---------------------------- 你看着累，我写着更累，新年伊始，先累一把，然后2026年的一年都不累了。
本文来自网友投稿或网络内容，如有侵犯您的权益请联系我们删除，联系邮箱：wyl860211@qq.com 。
