使用VBA在Excel中分列

在今年的国考职位表出炉后，我去浏览了一下，发现计算机专业可以报考的职位还是不少的

以中央党群机关职位表为例（部分无关字段已隐藏）
那么问题来了，到底哪个专业在国考中可报考的职位范围是最广的呢？（这里我们暂且不考虑每个职位的招考人数和职位表中各专业的包含关系)即

如何获得职位与专业的一对多的关系呢？

首先想到的是对L列以“、”为分隔符进行“数据”-“分列”，但是由于L列的各个单元格中顿号个数不一致，少的没有，多的可能有7个，在L列右侧新建列的话，不知道建多列才合适，即使这样分列成功了也不好统计各专业在各职位中的分布情况。
后来百度中发现，在VBA中有一个Split的函数，貌似就是对应Excel中的“数据”-“分列”，用法：

Split函数：返回一个下标从零开始的一维数组，它包含指定数目的子字符串。
Split(expression[, delimiter[, limit[, compare]]])
Split函数语法有如下命名参数：
expression：必需的。包含子字符串和分隔符的字符串表达式 。如果expression是一个长度为零的字符串(“”)，Split则返回一个空数组，即没有元素和数据的数组。
delimiter：可选的。用于标识子字符串边界的字符串字符。如果忽略，则使用空格字符(” “)作为分隔符。如果delimiter是一个长度为零的字符串，则返回的数组仅包含一个元素，即完整的 expression字符串。
limit ：可选的。要返回的子字符串数，–1表示返回所有的子字符串。
compare：可选的。数字值，表示判别子字符串时使用的比较方式。关于其值，请参阅“设置值”部分。
设置值
compare参数的设置值如下：
|常数|值|描述|
|vbUseCompareOption | –1 | 用Option Compare语句中的设置值执行比较。
|vbBinaryCompare | 0 | 执行二进制比较。
|vbTextCompare | 1 | 执行文字比较。
|vbDatabaseCompare | 2 | 仅用于Microsoft Access。基于您的数据库的信息执行比较。

ExcelHome原文链接
原文中还针对含有limit参数的情况进行了讨论，在此我们不涉及。

所以对于图中的情况，我们只需要一句：

arr = Split(Sheets("中央党群机关").Range("l" & i), "、")  

其中用数组arr存储Split的结果，i代表原始sheet中的行号

• 当然这中间还涉及到如何表示重命名过的sheet的问题，原来的sheet1、sheet2直接引用就可以了，现在的sheet的名称为”中央党群机关”，百度了一下才知道可以写成Sheets(“名称”)，当然后来也看到了这个
  

想是写成sheet1的话也应该没问题吧。
下面的问题就是如何在VBA使用数组，并为单元格或单元格区域赋值的问题。
以单元格L2为例，这里我们把“法学、侦查学”分隔成了数组“法学”“侦查学”，那么如何把这个数组的值赋给sheet2中的单元格B2和B3呢？
这里涉及到数组的转置、UBound和单元格的Resize，直接上代码：

Sheet2.Range("b" & j).Resize(UBound(arr) + 1, 1) = Application.WorksheetFunction.Transpose(arr)  

其中用j来记录每一个Spilt的数组结果赋值到sheet2的B列时的起始行位置。
Application.WorksheetFunction.Transpose(arr)是数组转置，Application.WorksheetFunction这个现在对我还处于不明觉厉的状态，但是反正我知道这个可以把“法学”“侦查学”变成
“法学”
“侦查学”
UBound是求数组下标的最大值。
Sheet2.Range(“b” & 2 ).Resize(UBound(arr) + 1, 1)就是把B2格变成了以B2为起始的2行1列的区域，正好用来接收arr转置后的赋值。
最终的代码：

Sub a()Dim i, j As Integerj = 2  '用j来记录每一个Spilt的数组结果赋值到sheet2的B列时的起始行位置For i = 2 To 252   'i代表原始Sheets("中央党群机关")中的行号arr = Split(Sheets("中央党群机关").Range("l" & i), "、") '分列Sheet2.Range("b" & j).Resize(UBound(arr) + 1, 1) = Application.WorksheetFunction.Transpose(arr)    '将“专业”一列中的分列结果赋值给sheet2的B列Sheet2.Range("a" & j).Resize(UBound(arr) + 1, 1) = Sheets("中央党群机关").Range("i" & i)'将“职位代码”一列中的分列结果赋值给sheet2的A列Sheet2.Range("c" & j).Resize(UBound(arr) + 1, 1) = Sheets("中央党群机关").Range("b" & i)'将“部门名称”一列中的分列结果赋值给sheet2的C列Sheet2.Range("d" & j).Resize(UBound(arr) + 1, 1) = Sheets("中央党群机关").Range("d" & i)'将“单位性质”一列中的分列结果赋值给sheet2的D列j = j + UBound(arr) + 1 '将j以上一个分列出的数组大小为步长向下移动Next iEnd Sub

这样就在sheet2中获得了每个专业对应每个部门的情况。

其实“职位代码”可能就类似于职位表中的主键了，接下来可以多写几个类似于Sheet2.Range("a" & j).Resize(UBound(arr) + 1, 1) = Sheets("中央党群机关").Range("i" & i)的语句，把Sheets(“中央党群机关”)中关心的列对应过来，也可以把Sheets(“中央党群机关”)和sheet2导入数据库做个join，就可以进行各种统计了。