How to convert Excel column numbers into alphabetical characters

???? ID: 833402 - ?? ???????? ?? ?????? ??? ?? ?? ???? ???? ???? ??.
???? ?????? ???????????? ?????? ???????? KB ?? ???????? ????? ?? ??? ???? ????? ????
??? ?? ??????? ???? | ??? ?? ??????? ????

?????

This article discusses how to use the Microsoft Visual Basic for Applications (VBA) function in Microsoft Excel to convert column numbers into their corresponding alphabetical character designator for the same column.

For example, the column number 30 is converted into the equivalent alphabetical characters "AD".

???? ???????

Microsoft ???????????? ?????? ???? ???????? ?? ??? ??? ???? ??, ???? ??? ??? ?? ?????? ?? ?????????? ??? ??? ?????? ???? ?? ????.. ?????? ???????? ?????? ?? ???? ????? ???????? ?? ??? ???????? ????? ??? ?? ???? ?? ????? ???? ???.. ?? ??? ????? ?? ?? ?? ????? ?? ?? ??? ???????????? ???? ?? ?? ??????? ?? ?????? ??? ????? ????? ???????????? ?? ????? ?? ???? ???? ?? ??? ???? ?? ??? ??.. Microsoft ?????? ???????? ?? ????? ????????? ?? ???????????? ?? ?????? ??? ?????? ?? ???? ???, ??, ?? ?? ???????? ?? ???? ??????? ?????????? ?? ?????? ???? ?? ??? ???????? ??????????????? ?? ?????? ?? ???????????? ????? ?? ??? ??????? ???? ??????..
TheConvertToLetterfunction works by using the following algorithm:
  1. Divide the column number by 27, and then put the resulting integer in the variable "i".
  2. ????? ?????? ?? subtract "i" 26 ?????? multiplied, ?? ?? ?? "j" ??? ?????? ?????
  3. ???????? ??? ?? ???? ??????? ???????? ?????? ??? ?????? ???? "i" ?? "j" ????? ?????? 0 ?? ???? ?? ??? 26 respectively ???
?????? ?? ???: ????? ?????? ??? 30 ???
  1. ????? ?????? 27: 30 ?? ??????? ?? / 27 = 1.1111, ???? ??Int?????? "1" ?? ??? ???
    I = 1
  2. ???? ????? ??????-(??? * 26) = 30-(1 * 26) 30-26 = = 4?
    j = 4
  3. ????? ??? ?????? ???? ???????? ????? ???-??? ????
    ??? = 1 "??" =
    j = 4 "D" =
  4. ?? ??? ???????, ?? ??????? ????? designator "AD"?
VBA ?????? ?? ????? ?????? ??? ?? ???? ?????? ???????? ?????? ??? ?????? ???? ?? ??? ???? ?? ????? ??:
Function ConvertToLetter(iCol As Integer) As String
   Dim iAlpha As Integer
   Dim iRemainder As Integer
   iAlpha = Int(iCol / 27)
   iRemainder = iCol - (iAlpha * 26)
   If iAlpha > 0 Then
      ConvertToLetter = Chr(iAlpha + 64)
   End If
   If iRemainder > 0 Then
      ConvertToLetter = ConvertToLetter & Chr(iRemainder + 64)
   End If
End Function
???:?? ????? ??? ???? ?????? ??? ?????????????? ???? ????? ??? ??? ??? ?? ?????????? ??? ???? ?????? ???? ??? ?? ????? ?? ?????? ????? ????? ?????????? ?? ?? ?????? ????????? ???? ???? ???

???

???? ID: 833402 - ????? ???????: 20 ??????? 2011 - ??????: 3.0
???? ???? ???? ??:
  • Microsoft Office Excel 2007
  • Microsoft Excel 2002 Standard Edition
  • Microsoft Excel 2000 Standard Edition
  • Microsoft Excel 97 Standard Edition
??????: 
kbcodesnippet kbcode kbhowto kbmt KB833402 KbMthi
???? ?????? ????????
??????????: ?? ???? ?? ???? ??????? ?? ????? ?? Microsoft ????-?????? ?????????? ?????? ?????? ???? ??? ??. Microsoft ???? ??? ????-???????? ?? ????-???????? ????? ?????? ?? ???? ???????? ???? ?? ???? ????? ????? ??? ?? ??? ?????? ?? ???? ???? ???? ??? ????? ??. ???????, ????-???????? ???? ????? ???? ???? ???? ???. ?????, ????????, ?????-???? ?? ??????? ?? ???????? ?? ???? ???, ???? ?? ??? ?????? ???? ???? ??? ????? ??? ?? ???? ??. Microsoft ??????? ??? ???? ?? ?????? ?? ??????????, ????????? ?? ??? ?????? ?? ???? ????? ?? ???? ???????? ?? ??? ???? ????? ?? ??? ????????? ???? ??. Microsoft ????-?????? ?????????? ?? ????? ?????? ?? ?? ??? ??.
?????????? ?? ??????? ????????? ??????? ??:833402
(http://support.microsoft.com/kb/833402/en-us/ )
????? ?? ???? ???? | ??????????? ???

??????????? ???

 
????? ?? ???? ????????? ?? ???? ????