Jak uzyskać nazwę aktualnej kolumny w Excelu?

Wypróbuj następującą funkcję:

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

Wyjaśnienie: ADDRESS(row_num, column_num, [abs_num]). [abs_num] = 4 = adres względny. Oznacza to, że w zwracanej wartości nie ma ‘$’s’. Dla kolumny ‘AB’, ADDRESS zwróci ‘AB1’. Zastępca usuwa ‘1’.

Można do tego celu użyć funkcji ROW i COLUMN. Jeśli pominiesz argument dla tych formuł, użyta zostanie aktualna komórka. Można ich użyć bezpośrednio z funkcja OFFSET , lub dowolną inną funkcją, w której zarówno wiersz jak i kolumna mogą zostać określone jako wartości liczbowe.

Na przykład, jeśli w komórce D8 wpiszemy =ROW(), zwróconą wartością będzie 8. Jeśli w tej samej komórce wpiszemy =COLUMN(), zwróconą wartością będzie 4.

Jeśli chcemy otrzymać literę kolumny, możemy użyć funkcji CHAR. Nie polecam używania liter do reprezentowania kolumny, ponieważ rzeczy stają się trudne przy podawaniu dwuliterowych nazw kolumn (gdzie i tak logiczniejsze jest używanie liczb).

Niezależnie od tego, jeśli nadal chcesz otrzymać literę kolumny, możesz po prostu dodać 64 do numeru kolumny (64 to o jeden znak mniej niż A), więc w poprzednim przykładzie, jeśli ustawimy wartość komórki na =CHAR(COLUMN()+64), zwróconą wartością będzie D. Jeśli chcemy, aby wartość komórki była lokalizacją samej komórki, pełną formułą będzie =CHAR(COLUMN()+64) & ROW().

• *

Po prostu FYI, otrzymałem 64 z tabeli ASCII. Możesz również użyć formuły CODE, więc zaktualizowana formuła przy jej użyciu będzie wyglądać jak =CHAR(COLUMN() + CODE("A") - 1). Musisz odjąć 1, ponieważ minimalna wartość COLUMN zawsze wynosi 1, a następnie minimalna wartość zwrotu całej formuły wynosiłaby B.

Jednak nie będzie to działać w przypadku dwuliterowych kolumn. W takim przypadku, aby poprawnie przetworzyć dwuliterowe kolumny potrzebny jest następujący wzór:

=IF(COLUMN()>26,IF(RIGHT(CHAR(IF(MOD(COLUMN()-1,26)=0,1,MOD(COLUMN()-1,26))+64),1)="Y",CHAR(INT((COLUMN()-1)/26)+64) & "Z",CHAR(INT((COLUMN()-1)/26)+64) & CHAR(IF(MOD(COLUMN(),26)=0,1,MOD(COLUMN(),26))+64)),CHAR(COLUMN()+64))&ROW()

Nie jestem pewien, czy jest łatwiejszy sposób, ale wiem, że działa bez problemów od komórki A1 do ZZ99. Ilustruje to jednak dlaczego najlepiej jest unikać stosowania identyfikatorów kolumn opartych na literach i trzymać się formuł opartych na czystych liczbach (np. stosowanie numeru kolumny zamiast litery z OFFSET).

Spróbuj tego

=SUBSTITUTE(SUBSTITUTE(CELL("address"),"$" & ROW(),""), "$", "")

To daje dokładny nagłówek kolumny, bez żadnych $ itp.

Trochę manualnie, ale mniej VBA i prostsza formuła:

• W wierszu Excela, np. komórka A1, wpisz numer kolumny =column()
• W wierszu poniżej wpisz =Address(1,A1)
• To da wynik $A$1

Gdy wartości zostaną skopiowane, znajdź i zamień $ i 1 na puste.

Aby uzyskać nazwę kolumny użyłem następujących formuł.

Dla danej komórki:

=SUBSTITUTE(CELL("address",H3),"$" & ROW(H3),"")

Dla bieżącej komórki:

=SUBSTITUTE(CELL("address"),"$" & ROW(),"")

• *

Spróbuj tej odmiany. Działa ona na 3-literowych kolumnach i nie pozostawia “$” na przednim końcu:

=SUBSTITUTE(ADDRESS(ROW(XFD123),COLUMN(XFD123),4),ROW(XFD123),"")

To będzie działać również

=MID(CELL("address"),2,FIND("$",CELL("address"),2)-2)

Wpisz to do dowolnej komórki:

Español :

=SI(ENTERO((COLUMNA()-1)/26)=0;"";CAR((ENTERO(COLUMNA()-1)/26)+64))&CAR(COLUMNA()-(ENTERO((COLUMNA()-1)/26)*26)+64)

English :

=IF(INT((COLUMN()-1)/26)=0,"",CHAR((INT(COLUMN()-1)/26)+64))&CHAR(COLUMN()-(INT((COLUMN()-1)/26)*26)+64)

Możesz zastąpić kolumnę() numerem wiersza.

Innym możliwym sposobem jest użycie czegoś takiego:

=INDIRECT("MySheet1!"&LOOKUP(COLUMN(),colid)&ROW())

Gdzie colid odnosi się do zakresu named, który utworzyłbyś w innym miejscu podręcznika składającego się z dwóch sąsiednich kolumn z wieloma wierszami: pierwsza kolumna zawierająca cyfry od 1 do n odpowiadające numerowi COLUMN(), druga zawierająca litery A - ZZ, lub dowolną ilość odniesień do kolumn, które chciałbyś umieścić. ROW() jest w porządku po lewej stronie, ponieważ ma zwracać numer wiersza.

Więc jeśli miałbyś skopiować powyższy ciąg do komórki A1 “MySheet2”, oceniłbyś go jako =MySheet1!A1, i zwróciłbyś wartość znalezioną w odpowiedniej komórce MySheet1.

Umożliwiłoby to na przykład użycie MySheet1 jako obszaru roboczego, usunięcie i ponowne wprowadzenie nowych danych, podczas gdy wszelkie formatowania lub obliczenia w MySheet2, które odnoszą się do tych treści, będą nadal działać poprawnie z nowymi zestawami danych z zakładki docelowej arkusza.

Rozwiązanie dla polskiej wersji programu Excel:

• dla danej komórki:

=PODSTAW(PODSTAW(KOMÓRKA("adres";B1);"$" & WIERSZ();""); "$"; "")

• dla bieżącej komórki:

=PODSTAW(PODSTAW(KOMÓRKA("adres");"$" & WIERSZ();""); "$"; "")

=MID(ADDRESS(ROW(),COLUMN()),2,FIND("$",ADDRESS(ROW(),COLUMN()),2)-2)

Formuła adresu działa poprzez zwrócenie nazwy kolumny i wiersza. Zwracanym formatem będzie zawsze $(Column Letters)$(Row Numbers) - np. $AA$2 lub $XAA$243556

Jeśli wiemy, że przy pierwszym znaku $ zawsze będzie występował pierwszy znak, możemy skorzystać z pierwszej formuły mid, aby zacząć ciągnąć znaki po pierwszym znaku $ (czyli drugim znaku).

Następnie znajdujemy następny znak $ (ponieważ wiemy, że będą tylko dwa) i wiemy, ile znaków jest między pierwszym a drugim znakiem dolara. Reszta jest prostym odejmowaniem.

Oto VBA, formuła zdefiniowana przez użytkownika, rozwiązanie. Działa z 1, 2 i 3-literowymi kolumnami.

Umieść w module kodu:

Function COLUMNLETTER(Optional rng As Range) As String
    'Returns the Column Letter of the top left cell in rng.

    If rng Is Nothing Then Set rng = Application.Caller
    COLUMNLETTER = Left(rng.Address(0, 0), IIf(rng.Column > 26, IIf(rng.Column > 702, 3, 2), 1))

End Function

=COLUMNLETTER() w dowolnej komórce zwróci literę kolumny. =COLUMNLETTER(B3) w dowolnej komórce zwróci B.

Funkcja zdefiniowana przez użytkownika działa świetnie podczas tworzenia ogólnych formuł wewnątrz funkcji INDIRECT.

spróbuj tego:

=LEFT(ADDRESS(1,COLUMN(),4),LEN(ADDRESS(1,COLUMN(),4))-1)

Oto jak można znaleźć nagłówek kolumny (tzn. literę):

=RIGHT(LEFT(ADDRESS(1,COLUMN()),LEN(ADDRESS(1,COLUMN()))-2),LEN(ADDRESS(1,COLUMN()))-3)

Poniższa formuła działa niezależnie od tego, gdzie ją umieścisz (zwróci Ci nazwę kolumny).

=SUBSTITUTE(SUBSTITUTE(ADDRESS(ROW(),COLUMN()),"$",""),ROW(),"")

=IF(COLUMN()>702,RIGHT(LEFT(ADDRESS(ROW(),COLUMN()),4),3),IF(COLUMN()>26,RIGHT(LEFT(ADDRESS(ROW(),COLUMN()),3),2),RIGHT(LEFT(ADDRESS(ROW(),COLUMN()),2),1)))

spróbuj tego:

=IF(COLUMN()>26,CHAR(COLUMN()/26 +64),“) & CHAR(MOD(COLUMN()-1,26)+65) & ROW()

ta funkcja działa do "ZY1” lub kolumny=701