【原】多列数据合并虽是EXCEL难题，但多维引用与VBA代码好似英雄联盟，携手打怪！

欢迎转发和点一下”再看“，文末留言互动!

置顶公众号或设为星标及时接收更新不迷路

小伙伴们好，今天来和大家分享一道数据格式转换的题目。在今天的推文中会向大家提供函数即VBA两种解法。好了，赶紧一起来看看吧！

题目是这样的：

题目要求将左侧的数据格式转换为右侧的数据格式。观察一下源数据，发现数据的提取是先横向提取再纵向提取，那么，这道题目该如何操作呢？

01

此时可以考虑使用多维引用的技巧。

在单元格K2中输入下列公式，三键确认后向下向右拖曳即可。

=INDIRECT(TEXT(MIN((COUNTIF($K$1:K1,$A$2:$F$6)+($A$2:$F$6<>"")*($A$1:$F$1<>K$1)+($A$2:$F$6=""))/1%%+ROW($A$2:$F$6)/1%+COLUMN($A$2:$F$6)),"r0c00"),)&""

思路：

• COUNTIF($K$1:K1,$A$2:$F$6)部分，目的是要把已经在K列中的人名分配一个较大的权重。如果能查找到，COUNTIF函数会返回数字1（如果有重复数字就会大于1）

• ($A$2:$F$6<>"")*($A$1:$F$1<>K$1)+($A$2:$F$6="")部分，这部分是其余的条件，由于K列（当前列）是要提取姓名，因此其它无用的数据（$A$2:$F$6<>""）、姓名列（$A$1:$F$1<>K$1）以及空格（$A$2:$F$6=""）都需要分配一个较大的权重，目的是在提取时排除这些数据或区域

• 因此(COUNTIF($K$1:K1,$A$2:$F$6)+($A$2:$F$6<>"")*($A$1:$F$1<>K$1)+($A$2:$F$6=""))/1%%这部分最后扩大1万倍

• ROW($A$2:$F$6)/1%+COLUMN($A$2:$F$6)部分，源数据所在的行号扩大100倍，再加上列号

• MIN部分求最小值，以当前单元格K2为例，当前最小值是201，对应的单元格是第2行第1列

• TEXT函数将其转换为“r0c00”的格式

• INDIRECT函数提取单元格内的数据，即姓名“赵灵灵”

当“赵灵灵”被提取到之后，随着公式向下拖曳，COUNTIF函数会在其对应位置上返回数字“1”，因此它会被赋值较大的权重，就再不会被提取到了。

这个公式其实也具有去重功能的。

02

这道题目的公式比较复杂，不是很好理解。相对的，代码就简单一些。

完成代码如下：

Sub test()    Dim i As Integer, y As Integer, m As Integer, n As Integer, arr    arr = Range("A2:F6").Value    m = 2    n = 2    For y = 1 To UBound(arr)        For i = 1 To UBound(arr, 2)                If Application.WorksheetFunction.IsText(arr(y, i)) Then                    Range("N" & m).Value = arr(y, i)                    m = m + 1                ElseIf Application.WorksheetFunction.IsNumber(arr(y, i)) Then                    Range("O" & n).Value = arr(y, i)                    n = n + 1                End If        Next i    Next yEnd Sub

简单解释一下。对源数据数据类型的判断来决定数据输出时的位置，通过两层循环完成最终需要的结果。

-END-