Excel列标与列名转换问题,本质上是一个十进制数和二十六进制数的转换问题。记得以前学C、C#、JAVA等编程语言时,已经做过一些相关练习了。但是,老实说,在用公式法解决这个十进制转二十六进制的问题时还真是难到我了,花了好几个小时也没有解决。
于是,求助网络,在一篇文章的启发下,先实现了VBA编程方式来解决问题。
在此总结一下经验:
1. 编程方式的思路更为清晰一些(很容易识别程序逻辑,如核心算法)
2. 调试起来方便很多。Excel自带函数法严格来讲不算调试,只是不断的试错而已。而VBE中可通过DEBUG.PRINT语句输出中间变量及计算过程,对理清思路非常有用
3. 多看看别人的代码可以少走很多弯路
代码分享:
Sub GetExcelColumnName(ByVal ExcelColumnName As String, ByVal columnNumber As Long)
Dim dividend As Long
Dim columnName As String
Dim modulo As Integer
Dim n As Integer '循环次数
dividend = columnNumber
Debug.Print "Input: ColumnNumber = " & columnNumber
Debug.Print "Calculation process:" & vbNewLine
While (dividend > 0)
n = n + 1
modulo = (dividend - 1) Mod 26
Debug.Print "Iteration No." & n
Debug.Print "dividend = " & dividend & ", modulo = (dividend - 1) Mod 26 = " & modulo
columnName = Chr(65 + modulo) + columnName
dividend = Round((dividend - modulo) / 26, 0)
Debug.Print "columnName = " & columnName & ", dividend = Round((dividend - modulo) / 26, 0) = " & dividend & vbNewLine
Wend
ExcelColumnName = columnName
Debug.Print "Result: ColumnNumber = " & columnNumber & " <--> ColumnName = " & columnName & vbNewLine & vbNewLine
End Sub
测试代码:
Sub GetExcelColumnNameTest()
Dim result As String
'For i = 676 To 678
GetExcelColumnName result, 2147483647
'Next
End Sub
测试结果:
Input: ColumnNumber = 2147483647
Calculation process:
Iteration No.1
dividend = 2147483647, modulo = (dividend - 1) Mod 26 = 22
columnName = W, dividend = Round((dividend - modulo) / 26, 0) = 82595524
Iteration No.2
dividend = 82595524, modulo = (dividend - 1) Mod 26 = 23
columnName = XW, dividend = Round((dividend - modulo) / 26, 0) = 3176750
Iteration No.3
dividend = 3176750, modulo = (dividend - 1) Mod 26 = 17
columnName = RXW, dividend = Round((dividend - modulo) / 26, 0) = 122182
Iteration No.4
dividend = 122182, modulo = (dividend - 1) Mod 26 = 7
columnName = HRXW, dividend = Round((dividend - modulo) / 26, 0) = 4699
Iteration No.5
dividend = 4699, modulo = (dividend - 1) Mod 26 = 18
columnName = SHRXW, dividend = Round((dividend - modulo) / 26, 0) = 180
Iteration No.6
dividend = 180, modulo = (dividend - 1) Mod 26 = 23
columnName = XSHRXW, dividend = Round((dividend - modulo) / 26, 0) = 6
Iteration No.7
dividend = 6, modulo = (dividend - 1) Mod 26 = 5
columnName = FXSHRXW, dividend = Round((dividend - modulo) / 26, 0) = 0
Result: ColumnNumber = 2147483647 <--> ColumnName = FXSHRXW
参考文章:
https://stackoverflow.com/questions/181596/how-to-convert-a-column-number-eg-127-into-an-excel-column-eg-aa