Funkcja konwersji numeru kolumny na literę?


143

Czy ktoś ma funkcję Excel VBA, która może zwrócić literę (y) kolumny z liczby?

Na przykład wpisanie 100 powinno zwrócić CV.



@FrancisDean czyli odwrotność tego pytania szukającego adresu z numeru
brettdj

2
@brettdj Łącze do odpowiedzi pokazuje zarówno liczbę do litery, jak i literę do liczby.
Francis Dean

2
@FrancisDean uczciwa uwaga, spojrzałem na tytuł pytania w linku, a nie na zaakceptowaną odpowiedź
brettdj

Odpowiedzi:


211

Ta funkcja zwraca literę kolumny dla danego numeru kolumny.

Function Col_Letter(lngCol As Long) As String
    Dim vArr
    vArr = Split(Cells(1, lngCol).Address(True, False), "$")
    Col_Letter = vArr(0)
End Function

kod testowy dla kolumny 100

Sub Test()
    MsgBox Col_Letter(100)
End Sub

9
Możesz dodać (0)znak na końcu polecenia Split, jeśli chcesz zapisać sobie deklarację zmiennej i dodatkową linię kodu. np.Col_letter = Split(Cells(1, lngCol).Address(True, False), "$")(0)
Caltor

3
To jest całkiem poprawne, ale pomyślałem, że bardziej czytelne będzie użycie kilku linijek.
brettdj

6
Po co w tej sytuacji zawracać sobie głowę parametrami boolowskimi. Możesz to zrobić:............................................. ......v = Split(Cells(1, lngCol).Address, "$")(1)
Excel Hero

1
Chociaż jest to bardzo stare, mam drobny dodatek - najpierw sprawdzam, czy liczba jest dodatnia, ponieważ w przeciwnym razie napotkasz błędy. jeśli lngcol <= 0 to
Selkie

1
Korzystając z VBS, pamiętaj, że .Cellsjest to właściwość programu Excel, co oznacza, że ​​musisz jej używać <excel_object>.Cells(). W przeciwnym razie zostanie wyświetlony błąd niezgodności typu.
Stevoisiak

88

Jeśli wolisz nie używać obiektu zakresu:

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

1
Nie jest jasne, dlaczego opublikowałeś dłuższą metodę z pętlą na podstawie Jeśli wolisz nie używać obiektu zakresu:
brettdj

29
@brettdj Mogę sobie wyobrazić kilka powodów: 1) ta metoda jest około 6x szybsza po moich testach 2) nie wymaga dostępu do Excel API 3) przypuszczalnie zajmuje mniej pamięci. EDYCJA: Nie jestem też pewien, dlaczego skomentowałem odpowiedź starszą niż rok: S
Blackhawk

6
Zwiększona prędkość ma jednak wadę. Użycie obiektu zakresu generuje błąd, jeśli przekażesz nieprawidłowy numer kolumny. Działa nawet, jeśli ktoś nadal używa programu Excel 2003. Jeśli potrzebujesz takiego wyjątku, skorzystaj z metody zakresu. W przeciwnym razie uznanie dla robartsd.
Inżynier toast za

6
IF ColumnNumber <= Columns.Countbyłoby lepiej unikać założeń dotyczących wersji.
brettdj

1
Innym powodem używania tego kodu jest to, że nie jesteś w VBA, ale w VB, .net itp.
Maury Markowitz


33

Jestem zaskoczony, że nikt nie zaproponował: ** <code> </code> <code> Columns (</code> *** <code> Column Index </code> *** <code>) .Address </code> <code> </code> **

  • Na przykład: MsgBox Columns( 9347 ).Address zwraca ** <code> $ MUM: $ MUM </code> ** .

Aby zwrócić TYLKO litery w kolumnach :Split((Columns(Column Index).Address(,0)),":")(0)

  • Na przykład: MsgBox Split((Columns( 2734 ).Address(,0)),":")(0) zwraca ** <code> DAD </code> ** .

  Więcej przykładów



Ten działa dla mnie w Office 365: string col = Worksheet.Columns [kolumna] .Address; return col.Substring (col.IndexOf (":") + 2); Z jakiegoś powodu inne wyrażenia, takie jak Range = Worksheet.Cells [1, column], rzucały błędy (albo w wywołaniu Cells, albo kiedy próbowałem pobrać adres, nie pamiętam - przepraszam - które wiersze, w których wrzucam, konkretnych przypadkach.)
Sam Azer,

19

Jeszcze tylko jeden sposób, aby to zrobić. Odpowiedź Brettdj sprawiła, że ​​pomyślałem o tym, ale jeśli używasz tej metody, nie musisz używać tablicy wariantowej, możesz przejść bezpośrednio do łańcucha.

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

lub może sprawić, że będzie trochę bardziej zwarty

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

Zauważ, że zależy to od tego, czy odwołujesz się do wiersza 1 w obiekcie komórek.


18

I rozwiązanie wykorzystujące rekursję:

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

Wytnij i wklej idealnie do konwersji liczb większych niż 676. Dzięki!
David Krider,

1
Reszta nigdy nie może być większa niż 26, więc dlaczego nie jest liczbą całkowitą, a nie długością?
Caltor

10
@Caltor O ile nie masz specjalnego celu używania liczby całkowitej, jak na przykład wywołanie interfejsu API, który wymaga takiego, nigdy nie powinieneś wybierać liczby całkowitej zamiast długości. VBA jest zoptymalizowany pod kątem Longs. VBA przetwarza Longs szybciej niż liczby całkowite.
Excel Hero

1
@ExcelHero Nie wiedziałem tego. Czy jednak Long nie zajmuje więcej pamięci niż liczba całkowita?
Caltor

6
@Caltor Rzeczywiście, Long to 32 bity, a liczba całkowita to 16. Ale to nie ma znaczenia w nowoczesnych komputerach. 25 lat temu ... to miało duże znaczenie. Ale dzisiaj (nawet 15 lat temu) różnica jest zupełnie nieistotna.
Excel Hero

9

Można to uzyskać za pomocą wzoru:

=SUBSTITUTE(ADDRESS(1,COLUMN(),4),"1","")

więc może być również zapisany jako funkcja VBA na żądanie:

Function ColName(colNum As Integer) As String
    ColName = Split(Worksheets(1).Cells(1, colNum).Address, "$")(1)
End Function

8

To jest wersja odpowiedzi robartsda (ze smakiem jednowierszowego rozwiązania Jana Wijninckxa ), wykorzystująca rekursję zamiast pętli.

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

Przetestowałem to z następującymi danymi wejściowymi:

1   => "A"
26  => "Z"
27  => "AA"
51  => "AY"
702 => "ZZ"
703 => "AAA" 
-1  => ""
-234=> ""

2
Właśnie zauważyłem, że jest to zasadniczo to samo, co rozwiązanie Nikołaja Iwanowa, co sprawia, że ​​moje jest trochę mniej nowatorskie. Zostawię to, ponieważ pokazuje nieco inne podejście w kilku drobiazgach
alexanderbird

Dobre rozwiązanie z dwóch powodów: nieużywanie żadnego obiektu (takiego jak zakres, komórka itd.); bardzo zwarta definicja.
kochany.by.Jezus

8

Kod robertsda jest elegancki, ale aby był przyszłościowy, zmień deklarację n na typ long

Jeśli chcesz, aby formuła unikała makr, oto coś, co działa do kolumny 702 włącznie

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

gdzie A1 to komórka zawierająca numer kolumny do przekonwertowania na litery.


7

NAJNOWSZA AKTUALIZACJA : Proszę zignorować poniższą funkcję, @SurasinTancharoen zdołał mnie ostrzec, że jest uszkodzony n = 53.
Dla zainteresowanych, poniżej znajdują się inne zepsute wartości n = 200:

Niektóre wartości

Użyj funkcji @brettdj do wszystkich swoich potrzeb. Działa nawet w przypadku ostatniego limitu maksymalnej liczby kolumn programu Microsoft Excel: 16384powinien dawaćXFD

wprowadź opis obrazu tutaj

KONIEC AKTUALIZACJI


Poniższa funkcja jest udostępniana przez firmę 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

Źródło: Jak przekonwertować numery kolumn programu Excel na znaki alfabetyczne

DOTYCZY

  • Microsoft Office Excel 2007
  • Microsoft Excel 2002 Standard Edition
  • Microsoft Excel 2000 Standard Edition
  • Microsoft Excel 97 Standard Edition

2
Dla porównania, to wymiotuje z większymi zestawami kolumn, ponieważ Chr () nie radzi sobie dobrze z dużymi liczbami.
Azuvector,

2
To ma błąd. Spróbuj ConvertToLetter (53), który powinien być 'BA', ale się nie powiedzie.
Surasin Tancharoen

@SurasinTancharoen Bardzo dziękuję za zwrócenie uwagi na tę usterkę. Nigdy nie myślałem, że Microsoft zapewni zepsutą funkcję, ponieważ to on sam stworzył Microsoft Excel. Od teraz porzucę tę funkcję i będę używać funkcji @brettdj, która nawet koryguje maksymalny limit liczby kolumn do najnowszego Microsoft ExcelCol_Letter(16384) = "XFD"
mtbink.com

4
A skąd na Ziemi pochodzi to „podziel przez 27”? Ostatnio sprawdzałem, czy jest 26 liter. Dlatego ten kod się psuje.
ib11

5

Jest to funkcja oparta na powyższej odpowiedzi @ DamienFennelly . Jeśli dasz mi kciuki do góry, daj mi też kciuki! : P

Function outColLetterFromNumber(iCol as Integer) as String
    sAddr = Cells(1, iCol).Address
    aSplit = Split(sAddr, "$")
    outColLetterFromNumber = aSplit(1)
End Function

2
Dobra, ale czym różni się od zaakceptowanej odpowiedzi?
Ioannis

@loannis Swoją oparłem na odpowiedzi DamianaFennelly'ego, a nie na zaakceptowanej. Ale tak, moja wygląda bardzo podobnie do zaakceptowanej odpowiedzi, z wyjątkiem tego, że jeden wiersz jest podzielony na dwa, aby uczynić go bardziej czytelnym.
BrettFromLA

3

Istnieje bardzo prosty sposób korzystania z mocy Excela: Użyj Range.Cells.Addresswłaściwości, w ten sposób:

strCol = Cells(1, lngRow).Address(xlRowRelative, xlColRelative)

Spowoduje to zwrócenie adresu żądanej kolumny w wierszu 1. Weź go z 1:

strCol = Left(strCol, len(strCol) - 1)

Zauważ, że jest tak szybki i potężny, że możesz zwrócić adresy kolumn, które nawet istnieją!

Zastąp lngRowżądany numer kolumny za pomocą Selection.Columnwłaściwości!


2

Oto prosta wkładka, której można użyć.

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

Będzie działać tylko dla oznaczenia 1-literowego kolumny, ale jest przyjemny w prostych przypadkach. Jeśli potrzebujesz, aby działał wyłącznie dla oznaczeń 2-literowych, możesz użyć:

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

2

To zadziała niezależnie od kolumny wewnątrz jednej linii kodu dla komórki znajdującej się w wierszu X, w kolumnie Y:

Mid(Cells(X,Y).Address, 2, instr(2,Cells(X,Y).Address,"$")-2)

Jeśli masz komórkę z unikalną, zdefiniowaną nazwą „Nazwa komórki”:

Mid(Cells(1,val(range("Cellname").Column)).Address, 2, instr(2,Cells(1,val(range("Cellname").Column)).Address,"$")-2)

1

Rozwiązanie firmy brettdj działa fantastycznie, ale jeśli natrafisz na to jako potencjalne rozwiązanie z tego samego powodu, co ja, pomyślałem, że zaoferuję swoje alternatywne rozwiązanie.

Problem, który miałem, polegał na przewijaniu do określonej kolumny na podstawie danych wyjściowych funkcji MATCH (). Zamiast konwertować numer kolumny na równoległą literę kolumny, zdecydowałem się tymczasowo przełączyć styl odniesienia z A1 na R1C1. W ten sposób mogłem po prostu przewinąć do numeru kolumny bez konieczności modyfikowania funkcji VBA. Aby łatwo przełączać się między dwoma stylami referencyjnymi, możesz użyć tego kodu VBA:

Sub toggle_reference_style()

If Application.ReferenceStyle = xlR1C1 Then
  Application.ReferenceStyle = xlA1
Else
  Application.ReferenceStyle = xlR1C1
End If

End Sub

1

Kontynuując odpowiedź brettdj, tutaj należy wprowadzić opcjonalny numer kolumny. Jeśli pole wprowadzania numeru kolumny zostanie pominięte, funkcja zwraca literę kolumny komórki, która wywołuje funkcję. Wiem, że można to również osiągnąć za pomocą zwykłego ColumnLetter(COLUMN()), ale pomyślałem, że byłoby miło, gdyby mógł to sprytnie to zrozumieć.

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

Wadą tej funkcji jest to, że byłaby ona bardzo, bardzo, bardzo wolniejsza niż odpowiedź brettdj z powodu rozszerzenia IF testu . Ale można to poczuć, jeśli funkcja jest wielokrotnie używana przez bardzo dużą liczbę razy.


1

Oto późna odpowiedź, tylko dla uproszczonego podejścia przy użyciu Int()i Ifw przypadku kolumn 1-3 znaków:

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

1
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

1

Tutaj prosta funkcja w Pascalu (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;

1

Ta formuła poda kolumnę na podstawie zakresu (tj. A1 ), gdzie zakres to pojedyncza komórka. Jeśli podany zostanie zakres obejmujący wiele komórek, zwróci on lewą górną komórkę. Uwaga: oba odwołania do komórek muszą być takie same:

MID (CELL ("adres", A1 ), 2, SEARCH ("$", CELL ("adres", A1 ), 2) -2)

Jak to działa:

KOMÓRKA („właściwość”, „zakres”) zwraca określoną wartość zakresu w zależności od użytej właściwości. W tym przypadku adres komórki. Właściwość address zwraca wartość $ [col] $ [wiersz], tj. A1 -> $ A $ 1. Funkcja MID analizuje wartość kolumny między symbolami $.


0

Łatwy sposób na uzyskanie nazwy kolumny

Sub column()

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

End Sub

Mam nadzieję, że to pomoże =)


0
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

0

Więc spóźniłem się na imprezę tutaj, ale chcę dać kolejną odpowiedź, której nikt inny jeszcze nie rozwiązał, która nie dotyczy tablic. Możesz to zrobić za pomocą prostej manipulacji na strunach.

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

Po wprowadzeniu danych w formacie $A$1użyj Midfunkcji, zacznij od pozycji 2, aby uwzględnić pierwszą pozycję $, a następnie znajdź miejsce, w którym $pojawia się druga w ciągu InStr, a następnie odejmij 2, aby uwzględnić tę pozycję początkową.

Daje to korzyść w postaci możliwości dostosowania do całej gamy możliwych kolumn. Dlatego ColLetter(1)zwraca "A" i ColLetter(16384)zwraca "XFD", która jest ostatnią możliwą kolumną dla mojej wersji Excela.


-1

Literę kolumny z numeru kolumny można wyodrębnić za pomocą formuły, wykonując następujące kroki
1. Oblicz adres kolumny za pomocą formuły ADDRESS
2. Wyodrębnij literę kolumny za pomocą funkcji MID i FIND

Przykład:
1. ADDRESS (1000,1000,1)
wyniki $ ALL 1000 $
2 . = MID (F15,2, FIND ("$", F15,2) -2)
wyniki WSZYSTKO zakładając, że F15 zawiera wynik z kroku 1 Za

jednym razem możemy wpisać
MID (ADRES (1000,1000,1), 2, ZNAJDŹ („$”; ADRES (1000,1000,1); 2) -2)


-1

to jest tylko dla REFEDIT ... ogólnie używaj tam kodu w krótkiej wersji ... łatwy do odczytania i zrozumienia / używa poz z $

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


-2

co powiesz na konwersję na liczbę ascii i użycie Chr () do konwersji z powrotem na literę?

col_letter = Chr (Selection.Column + 96)


-6

Oto inny sposób:

{

      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

}

1
Nie ma potrzeby tworzenia łańcucha zawierającego litery alfabetu. ASCII zasadniczo zrobiło to dla nas.
Caltor
Korzystając z naszej strony potwierdzasz, że przeczytałeś(-aś) i rozumiesz nasze zasady używania plików cookie i zasady ochrony prywatności.
Licensed under cc by-sa 3.0 with attribution required.