Jak uzyskać bieżącą nazwę kolumny w programie Excel?

Jaka jest funkcja pobierania bieżącego numeru wiersza i bieżącej nazwy kolumny dla komórki w programie Excel?

Można użyć ROWi COLUMNfunkcje, aby to zrobić. Pominięcie argumentu dla tych formuł powoduje użycie bieżącej komórki. Te mogą być bezpośrednio wykorzystane z tej OFFSETfunkcji , lub jakiejkolwiek innej funkcji, gdzie można określić zarówno wiersza i kolumny jako wartości liczbowych.

Na przykład, jeśli wpiszesz =ROW()w komórce D8, zwrócona wartość to 8. Jeśli wpiszesz =COLUMN()w tej samej komórce, zwrócona wartość to 4.

Jeśli chcesz literę kolumny, możesz użyć CHARfunkcji. Nie polecam używania liter do reprezentowania kolumny, ponieważ sytuacja staje się trudna przy przechodzeniu do dwuliterowych nazw kolumn (gdzie używanie liczb jest i tak bardziej logiczne).

Niezależnie od tego, jeśli nadal chcesz uzyskać literę kolumny, możesz po prostu dodać 64 do numeru kolumny (64 to mniej niż jeden znak A), więc w poprzednim przykładzie, jeśli ustawisz wartość komórki na =CHAR(COLUMN()+64), zwracana wartość to D. Jeśli chcesz, aby wartość komórki była samą lokalizacją komórki, byłaby to pełna formuła =CHAR(COLUMN()+64) & ROW().

Po prostu FYI, mam 64 z tabeli ASCII. Możesz także użyć CODEformuły, więc zaktualizowana formuła, która by to wykorzystała, byłaby =CHAR(COLUMN() + CODE("A") - 1). Musisz odjąć 1, ponieważ minimalna wartość COLUMNto zawsze 1, a wtedy minimalna wartość zwracana dla całej formuły byłaby B.

Nie będzie to jednak działać w przypadku kolumn dwuliterowych. W takim przypadku potrzebujesz następującej formuły, aby poprawnie przeanalizować dwuliterowe kolumny:

=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 to łatwiejszy sposób, ale nie wiem, ale wiem, że działa od komórki A1do ZZ99bez problemów. Jednak ilustruje to, dlaczego najlepiej unikać używania kolumnowych identyfikatorów kolumn i trzymać się formuł opartych na liczbach (np. Używając numeru kolumny zamiast litery z OFFSET).

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

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

Objaśnienie: ADDRESS(row_num, column_num, [abs_num]). [abs_num] = 4= adres względny. Oznacza to, że w zwracanej wartości nie ma znaków „$”. W przypadku kolumny „AB” ADDRESSzwróci „AB1”. Zastępca usuwa „1”.

Spróbuj tego

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

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

Wpisz to w dowolnej komórce:

Español :

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

Angielski :

=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.

Aby uzyskać nazwę kolumny, użyłem następujących wzorów.

Dla konkretnej komórki:

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

Dla bieżącej komórki:

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

Wypróbuj tę odmianę. Działa na 3-literowych kolumnach i nie pozostawia znaku „$” na interfejsie:

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

Nieznacznie manualny, ale mniej VBA i prostsza formuła:

• W wierszu programu Excel, np. Komórce A1, wprowadź numer kolumny =column()
• W wierszu poniżej wprowadź =Address(1,A1)
• To zapewni wynik $A$1

Po skopiowaniu wartości znajdź i zamień $oraz 1puste.

To też zadziała

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

Innym możliwym sposobem byłoby użycie czegoś takiego:

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

Gdzie colidodnosi się do nazwanego zakresu, który utworzyłbyś gdzie indziej w skoroszycie zawierającym dwie sąsiednie kolumny z wieloma wierszami: pierwsza kolumna zawierająca liczby od 1 do n odpowiadające COLUMN()liczbie, druga zawierająca litery A - ZZ lub jak wiele kolumn odnosi się do ciebie chcę pomieścić. Pozostało ROW()dobrze, ponieważ ma zwrócić numer wiersza.

Tak więc, jeśli skopiujesz powyższy ciąg do komórki A1 „MySheet2”, zostanie on oceniony jako =MySheet1!A1i zwróci wartość znalezioną w odpowiedniej komórce MySheet1.

Umożliwi to na przykład użycie MySheet1jako obszaru roboczego do usuwania i ponownego wstawiania nowych danych, podczas gdy wszelkie formatowanie lub obliczenia MySheet2odnoszące się do tych treści będą nadal działać poprawnie z nowymi zestawami danych z docelowego arkusza z kartami.

Rozwiązanie dla polskiej wersji Excela:

• dla określonej komórki:

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

• dla bieżącej komórki:

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

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

Umieść następujące elementy 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 komórki.
= COLUMNLETTER (B3) w dowolnej komórce powróci B .

Ta funkcja zdefiniowana przez użytkownika działa świetnie podczas tworzenia ogólnych formuł w funkcji POŚREDNIEJ.

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

Formuła adresu działa, zwracając nazwę kolumny i wiersza. Zwracanym formatem zawsze będzie $(Column Letters)$(Row Numbers)- np. $AA$2Lub$XAA$243556

Jeśli wiemy, że $ zawsze będzie występować przy pierwszym znaku, możemy użyć pierwszej środkowej formuły, aby zacząć ciągnąć znaki po pierwszym znaku $ (tj. Drugim znaku).

Następnie znajdujemy następny znak $ (jak wiemy, będą tylko dwa) i wiemy, ile znaków jest między pierwszym a drugim znakiem dolara. Reszta to proste odejmowanie.

Oto jak znaleźć nagłówek kolumny (tj. Literę):

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

Spróbuj tego:

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

Poniższa formuła działa bez względu na to, gdzie ją umieścisz (zwróci Ci nazwę kolumny).

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

Spróbuj tego:

= JEŻELI (KOLUMNA ()> 26, ZNAK (KOLUMNA () / 26 +64), „”) i ZNAK (MOD (KOLUMNA () - 1,26) +65) i WIERSZ ()

funkcja działa do momentu „ZY1” lub kolumny = 701

=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)))