熟悉Excel操作的用户都知道在工作表中列标题有列标（字母形式）和列号（数字形式）两种，如下图所示。
 
 
同样在VBA代码中也会用到这两种不同的表示方式。例如应用C6单元格可以使用如下几种方式来引用对象，对于Cells的第二个参数，即可以使用数字列号，也可以使用列标字母，然而对于Range来说参数为字符串，也就是使用列标形式。
 
Cells(6,3)
Cells(6,"C")
Range("C6")
 
在代码中使用变量的示例。
 
strCol = "C"
intCol = 3
Cells(6, intCol)
Cells(6, strCol)
Range(strCol & "6")
 
在VBA代码灵活选择单元格的引用形式，将使得代码简洁高效。某些场景中，可能还是需要实现列标和列号的转换，此时可以使用自定义函数。
 
 
列标字母编号由单个字母开始A，B，C，…，Z，之后为AA，AB，…，ZZ，接下来为AAA，AAB，…，XFD，可以看作是26进制编码的表示形式，和其他的进制转换类似，也是采用除以26取余数的方式进行计算，此处需要额外多一步的处理是，对于余数要转换为字母形式。
 列号转列标的自定义函数代码如下。
 
Function Num2Name1(ByVal ColNum As Long) As String
    Dim i As Long
    Dim intMod As Integer
    Num2Name1 = ""
    i = ColNum
    Do While i > 0
        intMod = IIf(i \ 26 = i / 26, 26, i Mod 26)
        Num2Name1 = Chr$(intMod + 64) + Num2Name1
        i = (i - intMod) / 26
    Loop
End Function

 
【代码解析】
 第7行代码判断循环变量i是否可以被26整除，如果可以，那么intMod赋值为26，否者赋值为余数。
 第8行代码使用Chr函数将1~26的数字转换为大小字母，并和前面步骤已经得到的结果组合为新的字符串。
 第9行代码获得下一次循环需要处理的数字，如果i<=0，则终止循环。
 
 
列标转列号的方法是上述的逆运算，自定义函数代码如下。
 
Function Name2Num1(ByVal ColName As String) As Long
    Dim i As Integer
    Dim intLen As Integer
    Name2Num1 = 0
    intLen = Len(ColName)
    If intLen > 0 Then
        ColName = UCase$(ColName)
        For i = 1 To intLen
            Name2Num1 = Name2Num1 + (Asc(Mid(ColName, i, 1)) - 64) * 26 ^ (intLen - i)
        Next i
    End If
End Function

 
【代码解析】
 第5行代码获取列标字符串的长度。
 第7行代码将列标转换为大写字母。
 第9行代码中使用Mid函数逐位读取列标字符，使用Asc函数转换为ASCII码，减去64实现了将字母变换为1~26的数组序列，根据所在位置不同，乘以26 ^ (intLen - i)转换为相应的数值进行累加。
 
 
使用如下代码可以测试两个自定义函数的结果。
 
Sub Demo()
    Debug.Print Name2Num1("XFD")
    Debug.Print Num2Name1(16384)
End Sub

 
 
上述两个自定义过程利用进制原理转换实现了列标和列号的转换，但是在VBA中灵活利用对象的属性可以更简洁的实现这个功能。
 
Function Num2Name(ByVal ColNum As Long) As String
    Num2Name = ""
    On Error Resume Next
    Num2Name = Split(Cells(1, ColNum).Address, "$")(1)
End Function
 
【代码解析】
 第4行代码使用Cells(1, ColNum).Address获取列号为ColNum的列中第一个单元格的绝对引用地址（例如：$ABC$1），Split函数使用$作为分隔符将绝对引用地址拆分具有3个元素的数组（下标从零开始），第2个元素为列标。
 
 
 
Function Name2Num(ByVal ColName As String) As Long
    Name2Num = 0
    On Error Resume Next
    Name2Num = Cells(1, ColName).Column
End Function

 
【代码解析】
 第4行代码使用使用单元格的Column返回列号。
 
 
这两个自定义函数避繁就简，核心代码就只有一行，代码非常简洁，有些“高手”可能会质疑这样的实现方式，他们认为调用Excel对象就会增加耗时，但是普通实现方式循环过程次数多，最终的整体效果哪个更好，我个人更欣赏简洁的代码。大家有兴趣的话，可以进行耗时对比测试，并在评论区发布测试结果。

excel的列号：A B C D E ... X Y Z AA AB AC ...
 
可以看成是一个27进制的数字序列，A对应十进制1，Z对应十进制数字26，A@对应十进制27，AA对应28，这里补上@（ascii码为64）起到0的作用，也就把列号变成：@ A B ... Z A@ AA AB ...
 
// 求$col 的下一个列号
function excelColPlus($col)
{
	//先转化成27进制数字
    $col_chars = str_split($col);
    $col_num = 0;
    foreach ($col_chars as $col_char) {
        $col_num = (ord($col_char) - 64) + 27 * ($col_num);
    }
    $col_num++;
    //数字转化为答案
    $ans = '';
    while ($col_num) {
        $ans .= chr(floor($col_num % 27) + 64);
        $col_num = floor($col_num / 27);
    }
    // '@' 都变成A
    $ans = str_replace('@','A',$ans);
	// 反转
    $ans = strrev($ans);
    return $ans;
}
 
测试一下
 
$col = 'A';
for ($i = 0; $i < 100; $i++) {
    echo $col . " ";
    $col= excelColPlus($col);
}
 
输出结果
 

   工作中因为要做Excel的三级联动，但是数据是从数据库中导出的，混杂在一起。利用字段间的关系对数据进行整理后，数据只能展示为竖列的形式。在制作联动时，需要用到横行的数据排列方式，故研究该公式。具体如下图：
 
 
源数据是竖型分组排序好的数据：
 
 
需要用到的数据是这样，一级菜单在首行，下面跟着二级菜单的形式。
 
 
公式如下：
 
=OFFSET(Sheet2!$A$1,SMALL(IF(INDEX(Sheet2!$A$1:$A$2826,MATCH(A$1,Sheet2!$A$1:$A$2826,0)+ROW(1:30)-1,1)=$A$1,MATCH(A$1,Sheet2!$A$1:$A$2826,0)+ROW(1:30),65536),1)-2,1)
 
分布解析如下：
 
用到的公式有：
 
=OFFSET（基准位置，向下或上偏移几行，向右或左偏移几列，引用区域的高度--不必要，引用区域的宽度--不必要）
 
=SMALL(array, k)  在一列数值范围array中，按从小到大的顺序取第K个值
 
=IF（判断条件 ， TRUE返回值 ， FALSE返回值）
 
=INDEX(单元格区域，指定行号，指定列号）
 
=MATCH（查找值， 查找范围，匹配方式） 1：小于  0：精确匹配   -1：大于
 
=ROW（）返回所选行号，如果是一个区域范围“A2:A20”或者“3:40”，会跟随下拉动作进行序号顺序排列。
 
具体思路：
 
1、将一级菜单删除重复项后，横向排列在“研究方向二级字典”sheet页中
 
2、通过 “ MATCH(A$1,Sheet2!$A$1:$A$2826,0) ”函数获取到该列一级菜单在 sheet2 里范围是 A1:A2826 的第一次出现的位置，参数 0 进行精确匹配
 
3、用 INDEX 函数 INDEX(Sheet2!$A$1:$A$2826,MATCH(A$1,Sheet2!$A$1:$A$2826,0)+ROW(1:30)-1,1)=$A$1  在sheet2中A1:A2826 区域内，通过 MATCH  查到的行号，因为是A列所以列值是1 ，行列交叉得到该出单元格的value值 ，与当前列第一行的一级菜单的value值进行判断。注意，因为MATCH 函数得到的是首行的值，不会随着下拉动作而进行变化，故 ROW(1:30) 是为了随着公式的下拉将匹配的行数进行变化。
 
4、利用 IF 函数进行控制，当区域内所有一级菜单值都取值完成后结束。没有判断的话，则不会终结，数据将会在一列中进行展示。IF(INDEX(Sheet2!$A$1:$A$2826,MATCH(A$1,Sheet2!$A$1:$A$2826,0)+ROW(1:30)-1,1)=$A$1,MATCH(A$1,Sheet2!$A$1:$A$2826,0)+ROW(1:30),65536)  
 
5、接着利用 SAMLL函数，获取到数据偏移行数。SMALL(IF(INDEX(Sheet2!$A$1:$A$2826,MATCH(A$1,Sheet2!$A$1:$A$2826,0)+ROW(1:30)-1,1)=$A$1,MATCH(A$1,Sheet2!$A$1:$A$2826,0)+ROW(1:30),65536),1)-2
 
6、最终，通过步骤 5 得到了偏移行数，偏移列数因为就两列，所以偏移列数为1 。故用 OFFSET 函数，在起始点 Sheet2!$A$1处向下偏移 步骤5 中的行数，在向后偏移 1 得到。对应位置的二级菜单的value值。
 
 

目标：
 将一列数据按照内容不同，分为多列。
 【案例】将不同【分支机构】的【时长】单独统计出来。
 数据源如下：
 
 期望得到如下表格：
 
 【步骤一】
 1.1在【F2】单元格中输入公式
 =INDEX($ B:$ B,SMALL(IF(A:A=$ D$ 2,ROW(A:A),4^8),ROW(A1)))&""
 1.2语法解释：INDEX(单元格区域，行号，列号)
 ①目标单元格区域：B列；
 ②行号：SMALL(IF(A:A=$D$2,ROW(A:A),4^8),ROW(A1))其中，IF(A:A=$D$2,ROW(A:A),4^8)返回的是一个数组，解释如下：1）将A列中存储的所有数值与【D2】单元格逐一匹配，若相等，则返回当前可匹配数值的行号；若不等，则返回一个超大的数值，这里选定为4^8（原因：4的8次方65536是03版本EXCEL的最大行数。03版本时候，一般认为该行的数据是空白的，在老版的查询公式写法中，会用这个数返回该行的数据，用于屏蔽零值）因为公式中用到了small函数，所以使用一个超大数值替代不符合if条件的数值，使其在small函数中不被返回，即超出我们原数据有效范围即可（如本例中有17个行号，这个数值>=18就可以了）。2）那么具体IF(A:A=$D$2,ROW(A:A),4^8)返回的数组是什么呢？从数据源表可以看出，A1的值是“分支机构”，A1！=D2，那么返回4^8，即数组IF(A:A=$D$2,ROW(A:A),4^8）的第一个返回值是4^8，同理，A2单元格的值是“安徽分公司”，显然A2！=D2，那么数组IF(A:A=$D$2,ROW(A:A),4^8）的第二个元素也是4^8；同理因为A3！=D2，所以数组IF(A:A=$D$2,ROW(A:A),4^8）的第三个元素也是4^8；而A4单元格的值是“重庆分公司”，A4=D2，这时数组IF(A:A=$D$2,ROW(A:A),4^8）返回A4所在行号，即4。因为A5=D2，所以数组IF(A:A=$D$2,ROW(A:A),4^8）返回A5单元格的行号5。
 到时，目前数组中存储的数据为{65536 ,65536,65536,4,5,…}，同理，A列的所有数据与D2逐一进行对比，得到if函数的数组为{65536 ,65536,65536,4,5,6,65536 ,65536,65536,…65536}(该数组中共有65536个元素。如果将4^8改为20，则该数组有20个元素)。
 3）在此if函数返回数组的外层，是small函数。
 计算完if函数后，则small变为（{65536 ,65536,65536,4,5,6,65536 ,65536,65536,…65536}，row(A1)），其中row（A1）返回值为数字1。
 small（单元格区域或数组，K）函数语法含义：在一列数值中，按从小到大的顺序返回第K个值。
 那么在此small函数中，返回值就是if数组中按从小到大排序后的第1个值，即4。
 4）这时外层的index函数即为index（B:B，4），在B列中返回第4行的数值，即B4单元格的【28】。
 5）index函数后面加上了【""】。
 &是文本合并运算符，"“是空文本，公式【&”"】表示将公式的运算结果与空文本合并。它的作用是用空文本替代返回值中的错误值。
 如果没有【&""】则结果如下：
 
 1.3在【F2】中输入数组公式以后，按shift+ctrl+enter。
 
 【步骤二】
 将【F2】的公式向下拖动，将公式填充到下面足够多的单元格中（可以与数据源的个数保持一致，因为返回结果不会超过数据源个数）
 【步骤三】
 利用数据有效性，将【D2】单元格做成下拉框，这样数据就可以变动了。（略）
 
 【注意】公式中，if函数的比较值范围要与返回行号完全对应。
 即下面标红的部分要保持一致。
 
 完成。