Office中国论坛/Access中国论坛

标题: 如何把整数1,2,3,4...转换成纵列标标号A,B,C,D... [打印本页]

作者: year2006 时间: 2006-9-20 10:25
标题: 如何把整数1,2,3,4...转换成纵列标标号A,B,C,D...
请问：如何把整数1,2,3,4...转换成纵列标标号A,B,C,D... 可以手动输2列然后用lookup，可是太繁琐一点；是否有简单的公式可以实现？谢谢！

作者: tangqp 时间: 2006-9-20 17:34
Public Function WordToNum(Num As Integer) As String
If Num < 27 Then
WordToNum = Chr(64 + Num)
Else
WordToNum = Chr(64 + Int(Num / 26)) & Chr(64 + Num Mod 26)
End If
End Function

作者: 老鬼 时间: 2006-9-21 06:44
=CHAR(A1+64)

作者: lirong 时间: 2006-9-22 01:28
Col就是你的數字欄

Gs = IIf((Col <= 26), Chr(64 + Col), IIf((Col Mod 26) = 0, Chr(63 + Col \ 26) & "Z", Chr(64 + Col \ 26) & Chr(64 + Col Mod 26)))

作者: 方漠 时间: 2006-9-22 20:12

以下是引用tangqp在2006-9-20 9:34:00的发言：
Public Function WordToNum(Num As Integer) As String
If Num < 27 Then
WordToNum = Chr(64 + Num)
Else
WordToNum = Chr(64 + Int(Num / 26)) & Chr(64 + Num Mod 26)
End If
End Function

WordToNum(78) = C@?????

作者: 方漠 时间: 2006-9-22 20:34
Public Function WordToNum(Num As Integer) As String
If Num <= 26 Then
      WordToNum = Chr(64 + Num)
Else
      If Num Mod 26 = 0 Then
         WordToNum = Chr(64 + Int(Num / 26)-1) & Chr(64 + 26)
      Else
         WordToNum = Chr(64 + Int(Num / 26)) & Chr(64 + Num Mod 26)
      End If
End If
End Function

[此贴子已经被作者于2006-9-22 15:51:34编辑过]

作者: tangqp 时间: 2006-9-22 23:36
谢过方大侠,修改了以下

Public Function WordToNum(Num As Integer) As String
If Num <= 0 Or Num > 256 Then
      WordToNum = "ERROR"
      Exit Function
End If
If Num < 27 Then
      WordToNum = Chr(64 + Num)
Else
      WordToNum = Chr(64 + Int((Num - 1) / 26)) & IIf(Num Mod 26 = 0, "Z", Chr(64 + Num Mod 26))
End If
End Function

作者: czzqb 时间: 2006-10-1 04:02
=LEFT(ADDRESS(1,A1,2),FIND("$",ADDRESS(1,A1,2))-1)

作者: 游戏人生 时间: 2006-10-1 06:14
个个都是高手

欢迎光临 Office中国论坛/Access中国论坛 (http://www.office-cn.net/)