如何把整数1,2,3,4...转换成纵列标标号A,B,C,D...

year2006

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

tangqp

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

老鬼

=CHAR(A1+64)

lirong

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)))

方漠

以下是引用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@?????

方漠

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

谢过方大侠,修改了以下

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

=LEFT(ADDRESS(1,A1,2),FIND("$",ADDRESS(1,A1,2))-1)

游戏人生

个个都是高手