Функция для преобразования номера столбца в букву?
У кого-нибудь есть функция Excel VBA, которая может возвращать буквы столбца из числа?
Например, ввод 100 должен вернуть CV,
27 ответов
Эта функция возвращает букву столбца для данного номера столбца.
Function Col_Letter(lngCol As Long) As String
Dim vArr
vArr = Split(Cells(1, lngCol).Address(True, False), "$")
Col_Letter = vArr(0)
End Function
тестовый код для столбца 100
Sub Test()
MsgBox Col_Letter(100)
End Sub
Если вы не хотите использовать объект диапазона:
Function ColumnLetter(ColumnNumber As Long) As String
Dim n As Long
Dim c As Byte
Dim s As String
n = ColumnNumber
Do
c = ((n - 1) Mod 26)
s = Chr(c + 65) & s
n = (n - c) \ 26
Loop While n > 0
ColumnLetter = s
End Function
Что-то, что работает для меня:
Cells(Row,Column).Address
Это вернет вам ссылку на формат $AE$1.
$ МАМА: $ MUM , Еще один способ сделать это. Ответ Бреттджа заставил меня задуматься об этом, но если вы используете этот метод, вам не нужно использовать вариантный массив, вы можете перейти непосредственно к строке.
ColLtr = Cells(1, ColNum).Address(True, False)
ColLtr = Replace(ColLtr, "$1", "")
или может сделать это немного более компактным с этим
ColLtr = Replace(Cells(1, ColNum).Address(True, False), "$1", "")
Обратите внимание, что это зависит от того, ссылаетесь ли вы на строку 1 в объекте клеток.
И решение с использованием рекурсии:
Function ColumnNumberToLetter(iCol As Long) As String
Dim lAlpha As Long
Dim lRemainder As Long
If iCol <= 26 Then
ColumnNumberToLetter = Chr(iCol + 64)
Else
lRemainder = iCol Mod 26
lAlpha = Int(iCol / 26)
If lRemainder = 0 Then
lRemainder = 26
lAlpha = lAlpha - 1
End If
ColumnNumberToLetter = ColumnNumberToLetter(lAlpha) & Chr(lRemainder + 64)
End If
End Function
Это доступно через формулу:
=SUBSTITUTE(ADDRESS(1,COLUMN(),4),"1","")
и так также может быть записан как функция VBA по запросу:
Function ColName(colNum As Integer) As String
ColName = Split(Worksheets(1).Cells(1, colNum).Address, "$")(1)
End Function
Это версия ответа robartsd (со вкусом однострочного решения Яна Вийнинка), использующего рекурсию вместо цикла.
Public Function ColumnLetter(Column As Integer) As String
If Column < 1 Then Exit Function
ColumnLetter = ColumnLetter(Int((Column - 1) / 26)) & Chr(((Column - 1) Mod 26) + Asc("A"))
End Function
Я проверил это со следующими входами:
1 => "A"
26 => "Z"
27 => "AA"
51 => "AY"
702 => "ZZ"
703 => "AAA"
-1 => ""
-234=> ""
Код Робертса элегантен, но чтобы сделать его перспективным, измените объявление n на тип long
В случае, если вы хотите, чтобы формула избегала макросов, вот что работает до столбца 702 включительно
=IF(A1>26,CHAR(INT((A1-1)/26)+64),"")&CHAR(MOD(A1-1,26)+65)
где A1 - это ячейка, содержащая номер столбца, который нужно преобразовать в буквы.
ПОСЛЕДНИЕ ОБНОВЛЕНИЯ: пожалуйста, игнорируйте функцию ниже, @SurasinTancharoen удалось предупредить меня, что она сломана в n = 53,
Для тех, кто заинтересован, вот другие сломанные значения чуть ниже n = 200:
Пожалуйста, используйте функцию @brettdj для всех ваших нужд. Это работает даже для последнего максимального числа столбцов в Microsoft Excel: 16384 должен дает XFD
КОНЕЦ ОБНОВЛЕНИЯ
Функция ниже предоставлена Microsoft:
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
Источник: Как преобразовать номера столбцов Excel в алфавитные символы
ОТНОСИТСЯ К
- Microsoft Office Excel 2007
- Microsoft Excel 2002 Standard Edition
- Microsoft Excel 2000 Standard Edition
- Microsoft Excel 97 Standard Edition
Эта функция основана на ответе @DamienFennelly выше. Если ты дашь мне большой палец, тоже подними его!:П
Function outColLetterFromNumber(iCol as Integer) as String
sAddr = Cells(1, iCol).Address
aSplit = Split(sAddr, "$")
outColLetterFromNumber = aSplit(1)
End Function
Существует очень простой способ использования Excel: используйте Range.Cells.Address свойство, таким образом:
strCol = Cells(1, lngRow).Address(xlRowRelative, xlColRelative)
Это вернет адрес нужного столбца в строке 1. Возьмите его из 1:
strCol = Left(strCol, len(strCol) - 1)
Обратите внимание, что он настолько быстрый и мощный, что вы можете вернуть адреса столбцов, которые даже существуют!
Замена lngRow для нужного номера столбца, используя Selection.Column имущество!
Это будет работать независимо от того, какой столбец внутри вашей строки кода для ячейки, расположенной в строке X, в столбце Y:
Mid(Cells(X,Y).Address, 2, instr(2,Cells(X,Y).Address,"$")-2)
Если у вас есть ячейка с уникальным определенным именем "Cellname":
Mid(Cells(1,val(range("Cellname").Column)).Address, 2, instr(2,Cells(1,val(range("Cellname").Column)).Address,"$")-2)
Вот простой вкладыш, который можно использовать.
ColumnLetter = Mid(Cells(Row, LastColA).Address, 2, 1)
Он будет работать только для обозначения в 1 букву, но это хорошо для простых случаев. Если вам нужно, чтобы он работал исключительно для двухбуквенных обозначений, вы можете использовать следующее:
ColumnLetter = Mid(Cells(Row, LastColA).Address, 2, 2)
Так что я опаздываю на вечеринку здесь, но я хочу дать еще один ответ, который еще никто не адресовал, который не связан с массивами. Вы можете сделать это с помощью простых манипуляций со строками.
Function ColLetter(Col_Index As Long) As String
Dim ColumnLetter As String
'Prevent errors; if you get back a number when expecting a letter,
' you know you did something wrong.
If Col_Index <= 0 Or Col_Index >= 16384 Then
ColLetter = 0
Exit Function
End If
ColumnLetter = ThisWorkbook.Sheets(1).Cells(1, Col_Index).Address 'Address in $A$1 format
ColumnLetter = Mid(ColumnLetter, 2, InStr(2, ColumnLetter, "$") - 2) 'Extracts just the letter
ColLetter = ColumnLetter
End Sub
После того, как у вас есть вход в формате $A$1, использовать Mid функция, начните с позиции 2, чтобы учесть первый $потом найдешь где второй $ появляется в строке с помощью InStr, а затем вычтите 2, чтобы учесть эту начальную позицию.
Это дает вам возможность адаптироваться ко всему диапазону возможных столбцов. Следовательно, ColLetter(1) возвращает "А", и ColLetter(16384) возвращает "XFD", который является последним возможным столбцом для моей версии Excel.
Здесь простая функция на Паскале (Delphi).
function GetColLetterFromNum(Sheet : Variant; Col : Integer) : String;
begin
Result := Sheet.Columns[Col].Address; // from Col=100 --> '$CV:$CV'
Result := Copy(Result, 2, Pos(':', Result) - 2);
end;
Function fColLetter(iCol As Integer) As String
On Error GoTo errLabel
fColLetter = Split(Columns(lngCol).Address(, False), ":")(1)
Exit Function
errLabel:
fColLetter = "%ERR%"
End Function
Эта формула даст столбец на основе диапазона (т. Е. A1), где диапазон - это одна ячейка. Если указан диапазон из нескольких ячеек, он вернет верхнюю левую ячейку. Обратите внимание, что обе ссылки на ячейки должны быть одинаковыми:
MID (CELL ("адрес",A1), 2, ПОИСК ("$",CELL("адрес",A1), 2) -2)
Как это устроено:
CELL ("свойство", "диапазон") возвращает конкретное значение диапазона в зависимости от используемого свойства. В этом случае адрес ячейки. Свойство address возвращает значение $[col]$[row], т.е. A1 -> $A$1. Функция MID анализирует значение столбца между символами $.
Вот поздний ответ, просто для упрощенного подхода с использованием Int() а также If в случае столбцов из 1-3 символов:
Function outColLetterFromNumber(i As Integer) As String
If i < 27 Then 'one-letter
col = Chr(64 + i)
ElseIf i < 677 Then 'two-letter
col = Chr(64 + Int(i / 26)) & Chr(64 + i - (Int(i / 26) * 26))
Else 'three-letter
col = Chr(64 + Int(i / 676)) & Chr(64 + Int(i - Int(i / 676) * 676) / 26)) & Chr(64 + i - (Int(i - Int(i / 676) * 676) / 26) * 26))
End If
outColLetterFromNumber = col
End Function
В продолжение ответа brettdj, здесь можно сделать необязательным ввод номера столбца. Если ввод номера столбца опущен, функция возвращает букву столбца ячейки, которая вызывает функцию. Я знаю, что это также может быть достигнуто с помощью просто ColumnLetter(COLUMN()), но я подумал, что было бы неплохо, если бы он умело понял это.
Public Function ColumnLetter(Optional ColumnNumber As Long = 0) As String
If ColumnNumber = 0 Then
ColumnLetter = Split(Application.Caller.Address(True, False, xlA1), "$")(0)
Else
ColumnLetter = Split(Cells(1, ColumnNumber).Address(True, False, xlA1), "$")(0)
End If
End Function
Компромисс этой функции в том, что она будет очень, немного медленнее, чем ответ brettdj из-за IF тестовое задание. Но это можно почувствовать, если эту функцию многократно использовать очень много раз.
Решение от brettdj работает фантастически, но если вы столкнетесь с этим как с потенциальным решением по той же причине, что и я, я подумал, что предложу свое альтернативное решение.
У меня была проблема с прокруткой до определенного столбца на основе выходных данных функции MATCH(). Вместо того, чтобы преобразовывать номер столбца в его параллельную букву, я решил временно переключить стиль ссылки с A1 на R1C1. Таким образом, я мог бы просто прокрутить номер столбца без необходимости использовать функцию VBA. Чтобы легко переключаться между двумя ссылочными стилями, вы можете использовать этот код VBA:
Sub toggle_reference_style()
If Application.ReferenceStyle = xlR1C1 Then
Application.ReferenceStyle = xlA1
Else
Application.ReferenceStyle = xlR1C1
End If
End Sub
Простой способ получить имя столбца
Sub column()
cell=cells(1,1)
column = Replace(cell.Address(False, False), cell.Row, "")
msgbox column
End Sub
Надеюсь это поможет =)
Sub GiveAddress()
Dim Chara As String
Chara = ""
Dim Num As Integer
Dim ColNum As Long
ColNum = InputBox("Input the column number")
Do
If ColNum < 27 Then
Chara = Chr(ColNum + 64) & Chara
Exit Do
Else
Num = ColNum / 26
If (Num * 26) > ColNum Then Num = Num - 1
If (Num * 26) = ColNum Then Num = ((ColNum - 1) / 26) - 1
Chara = Chr((ColNum - (26 * Num)) + 64) & Chara
ColNum = Num
End If
Loop
MsgBox "Address is '" & Chara & "'."
End Sub
Это только для REFEDIT ... в общем, используйте короткую версию кода... легко читаемую и понятную / используйте poz of $
Private Sub RefEdit1_Change()
Me.Label1.Caption = NOtoLETTER(RefEdit1.Value) ' you may assign to a variable var=....'
End Sub
Function NOtoLETTER(REFedit)
Dim First As Long, Second As Long
First = InStr(REFedit, "$") 'first poz of $
Second = InStr(First + 1, REFedit, "$") 'second poz of $
NOtoLETTER = Mid(REFedit, First + 1, Second - First - 1) 'extract COLUMN LETTER
End Function
Букву столбца из номера столбца можно извлечь с помощью формулы, выполнив следующие действия
1. Рассчитать адрес столбца по формуле ADDRESS
2. Извлеките букву столбца, используя функцию MID и НАЙТИ
Пример:
1. АДРЕС (1000,1000,1)
результаты $ ALL $ 1000
2. = MID(F15,2, FIND ("$", F15,2) -2)
Результаты ВСЕ, как предполагается, F15 содержит результат шага 1
За один раз мы можем написать
MID(АДРЕС (1000,1000,1),2,FIND("$", адрес (1000,1000,1),2)-2)
Как насчет простого преобразования в число ascii и использования Chr() для преобразования обратно в букву?
col_letter = Chr (Selection.Column + 96)
Колпачок А составляет 65 так:
MsgBox Chr(ActiveCell.Column + 64)
Найдено в: http://www.vbaexpress.com/forum/showthread.php?6103-Solved-get-column-letter
Вот еще один способ:
{
Sub find_test2()
alpha_col = "A,B,C,D,E,F,G,H,I,J,K,L,M,N,O,P,Q,R,S,T,U,V,W,X,W,Z"
MsgBox Split(alpha_col, ",")(ActiveCell.Column - 1)
End Sub
}