Какова функция для получения текущего номера строки и текущего имени столбца для ячейки в Excel?
16 ответов
31
Вы можете использовать функции ROW и COLUMN для этого. Если вы опустите аргумент для этих формул, используется текущая ячейка. Их можно напрямую использовать с функцией OFFSET или любой другой функцией, в которой вы можете указать как строку, так и столбец в виде числовых значений.
Например, если вы введете =ROW() в ячейку D8, будет возвращено значение 8. Если вы введете =COLUMN() в той же ячейке, возвращается значение 4.
Если вам нужна буква столбца, вы можете использовать функцию CHAR . Я не рекомендую использовать буквы для представления столбца, поскольку при переходе к двухбуквенным именам столбцов все становится сложнее (где использование цифр в любом случае более логично).
В любом случае, если вы все еще хотите получить букву столбца, вы можете просто добавить 64 к номеру столбца (64 - на один символ меньше, чем A), поэтому в предыдущем примере, если вы установите значение ячейки =CHAR(COLUMN()+64) , возвращаемое значение будет D Если вы хотите, чтобы значением ячейки была сама ячейка, полная формула была бы =CHAR(COLUMN()+64) & ROW() .
Просто к вашему сведению, я получил 64 из таблицы ASCII. Вы также можете использовать формулу CODE , поэтому обновленная формула с использованием этого будет =CHAR(COLUMN() + CODE("A") - 1) . Вы должны вычесть 1, так как минимальное значение COLUMN всегда равно 1, а затем минимальное возвращаемое значение всей формулы будет B
Однако это не будет работать с двухбуквенными столбцами. В этом случае вам потребуется следующая формула для правильного анализа двухбуквенных столбцов:
=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()
Я не уверен, есть ли более простой способ сделать это или нет, но я знаю, что работает от ячейки A1 до ZZ99 без проблем. Однако это иллюстрирует, почему лучше избегать использования буквенных идентификаторов столбцов и придерживаться формул, основанных исключительно на числах (например, используя номер столбца вместо буквы с OFFSET).
31
Попробуйте следующую функцию:
=SUBSTITUTE(ADDRESS(1,COLUMN(),4),"1","")
Объяснение: ADDRESS(row_num, column_num, [abs_num]) . [abs_num] = 4 = относительный адрес. Это означает, что в возвращаемом значении нет $. Для столбца «AB» ADDRESS вернет «AB1». Заменитель удаляет «1».
5
Попробуй это
=SUBSTITUTE(SUBSTITUTE(CELL("address"),"$" & ROW(),""), "$", "")
Это дает вам точный заголовок столбца, без каких-либо $ и т.д.
2
Чтобы получить имя столбца, я использовал следующие формулы.
Для конкретной ячейки:
=SUBSTITUTE(CELL("address",H3),"$" & ROW(H3),"")
Для текущей ячейки:
=SUBSTITUTE(CELL("address"),"$" & ROW(),"")
Попробуйте этот вариант. Он работает на трехбуквенных столбцах и не оставляет «$» на входе:
=SUBSTITUTE(ADDRESS(ROW(XFD123),COLUMN(XFD123),4),ROW(XFD123),"")
2
Введите это в любую ячейку:
Español:
=SI(ENTERO((COLUMNA()-1)/26)=0;"";CAR((ENTERO(COLUMNA()-1)/26)+64))&CAR(COLUMNA()-(ENTERO((COLUMNA()-1)/26)*26)+64)
Английский:
=IF(INT((COLUMN()-1)/26)=0,"",CHAR((INT(COLUMN()-1)/26)+64))&CHAR(COLUMN()-(INT((COLUMN()-1)/26)*26)+64)
Вы можете заменить column() номером строки.
2
Это будет работать так же
=MID(CELL("address"),2,FIND("$",CELL("address"),2)-2)
2
Немного ручная, но меньше VBA и более простая формула:
- В строке Excel, например, ячейка A1, введите номер
=column() - В строке ниже введите
=Address(1,A1) - Это даст результат
$A$1
Как только значения будут скопированы, найдите и замените $ и 1 на пустое.
1
Другой возможный способ - использовать что-то вроде этого:
=INDIRECT("MySheet1!"&LOOKUP(COLUMN(),colid)&ROW())
Где colid относится к именованному диапазону, который вы создали бы в другом месте в рабочей книге, содержащей два смежных столбца с несколькими строками: первый столбец, содержащий числа от 1 до n, соответствующий числу COLUMN() , второй, содержащий буквы A - ZZ, или много ссылок на колонки, которые вы хотите разместить. ROW() в порядке, чтобы вернуть номер строки.
Поэтому, если бы вы скопировали указанную выше строку в ячейку A1 «MySheet2», она была бы оценена как =MySheet1!A1 , и верните найденное значение в соответствующую ячейку MySheet1 .
Это позволит вам, например, использовать MySheet1 в качестве рабочей области, удалять и повторно вставлять новые данные, в то время как любое форматирование или вычисления в MySheet2 которые ссылаются на это содержимое, продолжат корректно работать с новыми наборами данных из целевой вкладки. Рабочий лист.
1
Решение для польской версии Excel:
- для конкретной ячейки:
=PODSTAW(PODSTAW(KOMÓRKA("adres";B1);"$" & WIERSZ();""); "$"; "")- для текущей ячейки:
=PODSTAW(PODSTAW(KOMÓRKA("adres");"$" & WIERSZ();""); "$"; "")0
Вот как вы можете найти заголовок столбца (то есть букву):
=RIGHT(LEFT(ADDRESS(1,COLUMN()),LEN(ADDRESS(1,COLUMN()))-2),LEN(ADDRESS(1,COLUMN()))-3)
0
Вот VBA, пользовательская формула, решение. Работает с 1, 2 и 3 буквенными столбцами.
Поместите следующее в модуль кода:
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() в любой ячейке вернет букву столбца ячейки.
= COLUMNLETTER(B3) в любой ячейке вернет B.
Эта пользовательская функция прекрасно работает при создании общих формул внутри функции INDIRECT.
0
Следующая формула работает независимо от того, где вы ее поместили (она вернет вам имя столбца).
=SUBSTITUTE(SUBSTITUTE(ADDRESS(ROW(),COLUMN()),"$",""),ROW(),"")
0
=MID(ADDRESS(ROW(),COLUMN()),2,FIND("$",ADDRESS(ROW(),COLUMN()),2)-2)
Формула адреса работает, возвращая имя столбца и строки. Возвращенный формат всегда будет $(Column Letters)$(Row Numbers) строк )- например, $AA$2 или $XAA$243556
Если мы знаем, что $ всегда будет встречаться в первом символе, мы можем использовать первую среднюю формулу, чтобы начать вытягивать символы после первого знака $ (то есть 2-го символа).
Затем мы находим следующий знак $ (как мы знаем, их будет только два) и мы знаем, сколько символов находится между первым и вторым знаком доллара. Остальное простое вычитание.
0
попробуй это:
=LEFT(ADDRESS(1,COLUMN(),4),LEN(ADDRESS(1,COLUMN(),4))-1)
-1
=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)))
-1
попробуй это:
= IF(COLUMN()> 26, CHAR(COLUMN()/26 +64), "") & CHAR(MOD(COLUMN() - 1,26) +65) & ROW()
эта функция действует до "ZY1" или столбца = 701