Функция для преобразования номера столбца в букву?

Есть ли у кого-нибудь функция Excel VBA, которая может возвращать букву (буквы) столбца из числа?

Например, ввод 100 должен вернуть CV.

114 голосов | спросил mezamorphic 9 +04002012-10-09T13:33:40+04:00312012bEurope/MoscowTue, 09 Oct 2012 13:33:40 +0400 2012, 13:33:40

23 ответа


0

Эта функция возвращает букву столбца для данного номера столбца.

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
ответил brettdj 9 +04002012-10-09T13:44:55+04:00312012bEurope/MoscowTue, 09 Oct 2012 13:44:55 +0400 2012, 13:44:55
0

Если вы не хотите использовать объект диапазона:

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
ответил robartsd 12 MarpmTue, 12 Mar 2013 20:37:04 +04002013-03-12T20:37:04+04:0008 2013, 20:37:04
0

Что-то, что работает для меня:

Cells(Row,Column).Address 

Это вернет вам ссылку на формат $ AE $ 1.

ответил Damian Fennelly 22 52013vEurope/Moscow11bEurope/MoscowFri, 22 Nov 2013 01:00:41 +0400 2013, 01:00:41
0

И решение с использованием рекурсии:

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
ответил Nikolay Ivanov 27 32013vEurope/Moscow11bEurope/MoscowWed, 27 Nov 2013 14:31:57 +0400 2013, 14:31:57
0

Еще один способ сделать это. Ответ Brettdj заставил меня задуматься об этом, но если вы используете этот метод, вам не нужно использовать вариантный массив, вы можно перейти непосредственно к строке.

ColLtr = Cells(1, ColNum).Address(True, False)
ColLtr = Replace(ColLtr, "$1", "")

или можете сделать его немного более компактным с этим

ColLtr = Replace(Cells(1, ColNum).Address(True, False), "$1", "")

Обратите внимание, что это зависит от того, ссылаетесь ли вы на строку 1 в объекте cell.

ответил OSUZorba 23 Maypm14 2014, 19:22:58
0

 Я удивлен, что никто не предложил: ** <code </code & code <code> Columns (</code> *** <code> индекс столбца </code> *** & lt ; code>). Адрес </code <code </code & **> **

  • Например: MsgBox Columns( 9347 ).Address возвращает  ** <code> $ MUM: $ MUM </code> ** .

Чтобы вернуть ТОЛЬКО букву столбца : Split((Columns( Column Index ).Address(,0)),":")(0)

  • Например: MsgBox Split((Columns( 2734 ).Address(,0)),":")(0) возвращает  ** <code> DAD </code> ** .

Другие примеры


ответил ashleedawg 30 MarpmFri, 30 Mar 2018 18:22:56 +03002018-03-30T18:22:56+03:0006 2018, 18:22:56
0

Это доступно через формулу:

=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
ответил Alistair Collins 9 TueEurope/Moscow2014-12-09T15:08:47+03:00Europe/Moscow12bEurope/MoscowTue, 09 Dec 2014 15:08:47 +0300 2014, 15:08:47
0

Это версия ответа 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=> ""
ответил alexanderbird 4 FebruaryEurope/MoscowbWed, 04 Feb 2015 19:18:07 +0300000000pmWed, 04 Feb 2015 19:18:07 +030015 2015, 19:18:07
0

код robertsd элегантен, но, чтобы сделать его ориентированным на будущее, измените объявление n на тип long

В случае, если вы хотите, чтобы формула избегала макросов, здесь есть кое-что, что работает до столбца 702 включительно

=IF(A1>26,CHAR(INT((A1-1)/26)+64),"")&CHAR(MOD(A1-1,26)+65)

где A1 - это ячейка, содержащая номер столбца, который нужно преобразовать в буквы.

ответил Jan Wijninckx 17 FebruaryEurope/MoscowbMon, 17 Feb 2014 07:29:01 +0400000000amMon, 17 Feb 2014 07:29:01 +040014 2014, 07:29:01
0

Эта функция основана на @ ответе DamienFennelly выше. Если ты дашь мне большой палец, тоже подними его! : P

Function outColLetterFromNumber(iCol as Integer) as String
    sAddr = Cells(1, iCol).Address
    aSplit = Split(sAddr, "$")
    outColLetterFromNumber = aSplit(1)
End Function
ответил BrettFromLA 20 MarpmThu, 20 Mar 2014 22:43:32 +04002014-03-20T22:43:32+04:0010 2014, 22:43:32
0

Вот простой однострочник, который можно использовать.

ColumnLetter = Mid(Cells(Row, LastColA).Address, 2, 1)

Он будет работать только для 1-буквенного обозначения столбца, но это хорошо для простых случаев. Если вам нужно, чтобы он работал исключительно для двухбуквенных обозначений, вы можете использовать следующее:

ColumnLetter = Mid(Cells(Row, LastColA).Address, 2, 2)
ответил Syd B 26 PM00000060000003331 2014, 18:15:33
0

Это будет работать независимо от того, какой столбец внутри одной строки кода для ячейки, расположенной в строке 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)
ответил Codeplayer 5 32014vEurope/Moscow11bEurope/MoscowWed, 05 Nov 2014 20:30:41 +0300 2014, 20:30:41
0

Решение от 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
ответил Will Ediger 12 FebruaryEurope/MoscowbThu, 12 Feb 2015 21:07:37 +0300000000pmThu, 12 Feb 2015 21:07:37 +030015 2015, 21:07:37
0

В дополнение к ответу 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. Но это можно почувствовать, если функцию многократно использовать очень большое количество раз.

ответил Rosetta 19 MaramSat, 19 Mar 2016 07:52:09 +03002016-03-19T07:52:09+03:0007 2016, 07:52:09
0

Вот поздний ответ, только для упрощенного подхода с использованием 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
ответил ib11 29 Mayam16 2016, 00:56:30
0
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
ответил Krzysztof 5 MaramSun, 05 Mar 2017 01:36:16 +03002017-03-05T01:36:16+03:0001 2017, 01:36:16
0

Здесь простая функция на Паскале (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;
ответил Jordi 8 thEurope/Moscowp30Europe/Moscow09bEurope/MoscowFri, 08 Sep 2017 14:00:21 +0300 2017, 14:00:21
0

Эта формула даст столбец на основе диапазона (то есть A1 ), где диапазон - это одна ячейка. Если указан диапазон из нескольких ячеек, он вернет верхнюю левую ячейку. Обратите внимание, что обе ссылки на ячейки должны быть одинаковыми:

MID (ЯЧЕЙКА ( "адрес", A1 ), 2, ПОИСК ( "$", ЯЧЕЙКА ( "адрес", A1 ), 2) -2)

Как это работает:

CELL ("свойство", "диапазон") возвращает конкретное значение диапазона в зависимости от используемого свойства. В этом случае адрес ячейки. Свойство address возвращает значение $ [col] $ [row], т. Е. A1 -> $ A $ 1. Функция MID анализирует значение столбца между символами $.

ответил Thom 31 Jpm1000000pmWed, 31 Jan 2018 21:49:22 +030018 2018, 21:49:22
0

Простой способ получить имя столбца

Sub column()

cell=cells(1,1)
column = Replace(cell.Address(False, False), cell.Row, "")
msgbox column

End Sub

Надеюсь, это поможет =)

ответил cristobal 11 22014vEurope/Moscow11bEurope/MoscowTue, 11 Nov 2014 15:09:03 +0300 2014, 15:09:03
0

Итак, я опаздываю на вечеринку здесь, но я хочу дать еще один ответ, который еще никто не адресовал, который не связан с массивами. Вы можете сделать это с помощью простых манипуляций со строками.

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) возвращает «A», а ColLetter(16384) возвращает «XFD», который является последним возможным столбцом для моей версии Excel.

ответил SandPiper 28 FriEurope/Moscow2018-12-28T02:51:48+03:00Europe/Moscow12bEurope/MoscowFri, 28 Dec 2018 02:51:48 +0300 2018, 02:51:48
0

Букву столбца из номера столбца можно извлечь с помощью формулы, выполнив следующие действия:
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)

ответил Bhanu Sinha 22 ndEurope/Moscowp30Europe/Moscow09bEurope/MoscowTue, 22 Sep 2015 23:54:34 +0300 2015, 23:54:34
0
ответил PEDRO COUTO 30 MarpmWed, 30 Mar 2016 12:31:28 +03002016-03-30T12:31:28+03:0012 2016, 12:31:28
0

Вот еще один способ:

{

      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

}
ответил Mike Powell 28 J000000Monday14 2014, 18:17:37

Похожие вопросы

Популярные теги

security × 330linux × 316macos × 2827 × 268performance × 244command-line × 241sql-server × 235joomla-3.x × 222java × 189c++ × 186windows × 180cisco × 168bash × 158c# × 142gmail × 139arduino-uno × 139javascript × 134ssh × 133seo × 132mysql × 132