Excel情报局
职场联盟Excel
大家好,今天我们来解决一位的群友的求助:如何得到两列数据单元格之间所有的组合。问题看似很烧脑,但是只要思路清晰,这都不是大问题!为了方便讲解,我们简化数据源模拟数据。
如下图所示:
AB为两列数据,单元格内容及个数都是随机的。我们想要的结果是:将AB两列之间的单元格的所有组合的可能性显示到D列中。即A1与B1~D1中内容的组合;A2与B1~D1中内容的组合,;A3与B1~D1中内容的组合。这种问题像是我们上高中课上的数学排列组合题。
我们说了,思路是这个问题的核心,函数公式就用到那么简单的3个,从技术含量上来说并不高。下面我们来看看思路妙在哪里!
首先我们借助IF和TOROW函数:
=IF(A1:A3<>"",TOROW(B1:B4))
这里我们使用IF函数,其实并没有用到它的现实作用,只是用它当做一个跳板。而TOROW函数是一个转置函数,可以将一列或多行多列数据转置为一行,也非常的基础。
利用IF函数,如果A1:A3区域内单元格不为空值,则我们将B1:B4这一列转置为一行显示。因为A1:A3区域内有3个单元格,它会在这个数组内依次对每个单元格分别进行不为空的判断,因为3个单元格都不为空,也就是说判断为真值TRUE,则会3次将B1:B4这一列转置为一行显示,即最后显示3行相同的转置后的数据,即A6:D8。
接着,我们再次用上一步同样的思路,借助IF和TOROW函数:
=IF(B1:B4<>"",TOROW(A1:A3))
将A1:A3区域的数据,进行4次将列数据转为一行的转置操作,形成A10:C13。
因为A1~A3中每个单元格分别在B1~B4中有4种组合方式,那么最终就是3*4=12种组合方式,我们只需要将A6:D8与A10:C13各自分别转置成一列就得到了12种一一对应的组合结果。(这里有些绕,只要动手操作并仔细思考就会理解透彻)。
所以利用TOCOL函数:
=TOCOL(A10#,,TRUE)
TOROW函数是一个转置函数,可以将一行或多行多列数据转置为一列。
将A10:C13区域的数据,跳过省略第2参数,第3参数设置为TRUE(通过列扫描方式:先列后行方向),转置成一列显示。
再次利用TOCOL函数:
=TOCOL(A6#)
将A6:D8区域的数据,跳过省略第2参数,第3参数设置为FAISE或省略(通过行扫描方式:先行后列方向),转置成一列显示。
合并F列与G列后就是所有组合的明细了:
=F1&G1
上面A6:D8、A10:C13、F列、G列其实都是辅助区域,目的都是为了帮助我们 得到H列的组合结果,并帮助我们进行思路细化。
我们可以将函数公式嵌套合并,进而将辅助区域省略掉。
将H2单元格的F1和G1分别用TOCOL(A10#,,TRUE)与TOCOL(A6#)代替:
=TOCOL(A10#,,TRUE)&TOCOL(A6#)
再将A10#、A6#分别用IF(B1:B4<>"",TOROW(A1:A3))
与IF(A1:A3<>"",TOROW(B1:B4))代替:
=TOCOL(IF(B1:B4<>"",TOROW(A1:A3)),,TRUE)&TOCOL(IF(A1:A3<>"",TOROW(B1:B4)))
这样函数公式最终就嵌套合并成了一个,辅助区域自然就不需要了,这就考验到我们的思路清晰程度了。如果理解以及输入困难,可以继续使用辅助列哦!
〖往期知识点精彩超链接点击阅读〗