98 365新函数之TOCOL的几种经典方法

今天开始，来聊聊Office 365版本的新函数，这些函数有些是专属的，有些已经更新在最新版本的Office上面，如2021的版本，还有就是国产的WPS也更新了部分新函数。

平时因为工作的原因，需要分析的数据太多了，在没接触Office 365版本函数前，用原来的方法在处理一些特定的数据分析的时候，感觉效率太慢了，用了新函数后，发现真是好用，以至于后来没有365版本的Excel根本用不习惯；

函数缺点

说了这样多365版本的优点，还是说一下缺点吧，就是这个软件不能买断。因为365版本是订阅制，每年都需要交钱，这也造成了实际在很多中小工厂的普及率基本等于零。所以现在的状态就是就自己的笔记本电脑把数据做好，再转到台式电脑。

不管怎么样，新函数带来的Excel函数更新，确实是非常“香”，无论现在你的办公软件有没有这个函数学习一下，也是非常有必要的，今天更新的就TOCOL函数

函数语法

函数的语法：=TOCOL(array, [ignore],[scan_by_column])

语法这样长不好记，只需要记住最常用的第一参数就可以了，就是array区域，理解为把一组数据转成以一列形式返回的数组，什么是列形式呢？就是垂直方向，“把多行或多列的数据变成一列”如下图

经典案例1

一般函数有一个特点就是如果只是简单的用这个函数本身的功能就看不出有什么特别厉害的功能，如TOCOL只是转成列的数组的话，完成可以用转置函数TRANSPOSE 来实现，如下图案例，想统计标题字段的工单数，汇总数量等，需要把标题转成列的方向来显示：

分别录入函数：=TOCOL(A1:D1)和 =TRANSPOSE(A1:D1)效果是一样的，都实现了把标题行转成列的形式返回，也就是垂直方向。

经典案例2

如果这个函数配合其它函数一起使用的话，有些原来在老版本中非常复杂的事情就会变得非常简单了，举例说明，工作中我们需要把一组二维数据转成一维数据，如下图所示，如果用老版本的函数，需要用到的函数有offset /index/row/column 等一系列函数，非常痛苦，也需要一定的基础，而用365函数就简单几步就可以了；

步骤1：边上表1录入标题数量，因为数量比较好理解，我们在此录入函数=TOCOL(B2:E5)，相当于把B2:E5的所有数据转成垂直数组了，也就是列的方向，也是一维数据的基础，接下来就是要把工单和月份一一对应转过来

步骤2：边上表1录入标题工单和月份，并录入函数：

=TOCOL(A2:A5&"#"&B1:E1)，此时就会发现，工单和月份已经转成一维了，唯一的不足就是，工单和月份在一个单元格内，如果不会其它嵌套函数，此时只需要把这一列转成数值后，直接点数据分列就完成了二维转一维数据。

步骤3：如果再学习一些新的365函数，如文本合并与文本分列的函数，就可以通过嵌套的方式把这一列的数据快速分开。

合并函数：

= TEXTJOIN(";",,TOCOL(A2:A5&"#"&B1:E1))

分开函数：=TEXTSPLIT(TEXTJOIN(";",,TOCOL(A2:A5&"#"&B1:E1)),"#",";")

效果如下，此时就基本完成了，有些用户对细节要求非常高的情况，就再把0的数据去掉，加上筛选函数就可以了；

步骤4：去除包含0的行，用筛选函数，再用筛选函数前我们需要把3列数据合并在一起，也就是把原来分开写的函数全部合并成一个公式完成，录入以下函数后就可以实现一个函数自动生成。

=VSTACK({"标题1","标题2","标题3"},HSTACK(TEXTSPLIT(TEXTJOIN(";",,TOCOL(A2:A5&"#"&B1:E1)),"#",";"),TOCOL(B2:E5)))

有了这个动态数组区域，加上筛选函数就完成了最终版本的设计，录入以下函数：

=VSTACK({"标题1","标题2","标题3"},FILTER(HSTACK(TEXTSPLIT(TEXTJOIN(";",,TOCOL(A2:A5&"#"&B1:E1)),"#",";"),TOCOL(B2:E5)),TOCOL(B2:E5>0)))

自定义函数

最后一步转成自定义函数：

=LAMBDA(行标题,列标题,二维区域,VSTACK({“标题1”,”标题2”,”标题3”},FILTER(HSTACK(TEXTSPLIT(TEXTJOIN(“;”,,TOCOL(行标题&”#”&列标题)),”#”,”;”),TOCOL(二维区域)),TOCOL(二维区域)>0)))

按上面的代码定义名称后，输入以下自定义函数就可以快速的转换了。

我是古哥：

从事制造行业18年，在企业运营、供应链管理、智能制造系统等方面具有丰富的实战经验。企业智能化，柔性化计划运营管理专家，擅长通过企业流程优化规范，企业管理、导入计划运营提升企业效率；对提高企业准时交货率，降低企业库存，输出智能制造人才有丰富的经验。学习PMC生产计划，关注古哥计划！