¿Cuál es la función para obtener el número de línea actual y el nombre de la columna actual para una celda en Excel?
Intente la siguiente función:
=SUBSTITUTE(ADDRESS(1,COLUMN(),4),"1","")
Explicación: ADDRESS(row_num, column_num, [abs_num]). [abs_num] = 4 = dirección relativa. Eso significa que no hay ‘$’s en el valor devuelto. Para la columna 'AB’, el ADDRESS devolverá ‘AB1’. El sustituto elimina el ‘1’.
Puedes usar las funciones ROW y COLUMN para hacer esto. Si omites el argumento de esas fórmulas, se utiliza la celda actual. Estas pueden ser usadas directamente con la función OFFSET , o cualquier otra función donde puedas especificar tanto la fila como la columna como valores numéricos.
Por ejemplo, si introduces =ROW() en la celda D8, el valor devuelto es 8. Si introduces =COLUMN() en la misma celda, el valor devuelto es 4.
Si quieres la letra de la columna, puedes usar la función CHAR. No recomiendo el uso de letras para representar la columna, ya que las cosas se complican cuando se pasa a nombres de columna de doble letra (donde, de todas formas, es más lógico usar sólo números).
De todas formas, si aún quieres obtener la letra de columna, puedes simplemente añadir 64 al número de columna (64 es un carácter menos que A), por lo que en el ejemplo anterior, si fijas el valor de la celda en =CHAR(COLUMN()+64), el valor devuelto sería D. Si quisieras que el valor de una celda fuera la ubicación de la misma, la fórmula completa sería =CHAR(COLUMN()+64) & ROW().
Para tu información, obtuve 64 de una tabla ASCII. También podrías usar la fórmula CODE, por lo que la fórmula actualizada usando esto sería =CHAR(COLUMN() + CODE("A") - 1). Tienes que restar 1 ya que el valor mínimo de COLUMN es siempre 1, y entonces el valor mínimo de retorno de toda la fórmula sería B.
Sin embargo, esto no funcionará con columnas de dos letras. En ese caso, necesitas la siguiente fórmula para analizar correctamente las columnas de dos letras:
=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()
No estoy seguro de que haya una forma más fácil de hacerlo o no, pero sé que funciona desde la celda A1 hasta ZZ99 sin problemas. Sin embargo, esto ilustra por qué es mejor evitar el uso de identificadores de columna basados en letras, y atenerse a las fórmulas basadas puramente en números (por ejemplo, usando el número de columna en lugar de la letra con OFFSET).
Prueba esto
=SUBSTITUTE(SUBSTITUTE(CELL("address"),"$" & ROW(),""), "$", "")
Esto te da el encabezado exacto de la columna, sin ningún $ etc.
Ligeramente manual pero menos VBA y una fórmula más simple:
=column() =Address(1,A1) $A$1 Una vez copiados los valores, encuentre y reemplace $ y 1 por un espacio en blanco.
Para obtener el nombre de la columna utilicé las siguientes fórmulas.
Para una celda en particular:
=SUBSTITUTE(CELL("address",H3),"$" & ROW(H3),"")
Para la celda actual:
=SUBSTITUTE(CELL("address"),"$" & ROW(),"")
Pruebe esta variación. Funciona en columnas de 3 letras, y no deja un “$” en la parte delantera:
=SUBSTITUTE(ADDRESS(ROW(XFD123),COLUMN(XFD123),4),ROW(XFD123),"")
Escriba esto en cualquier celda:
Español :
=SI(ENTERO((COLUMNA()-1)/26)=0;"";CAR((ENTERO(COLUMNA()-1)/26)+64))&CAR(COLUMNA()-(ENTERO((COLUMNA()-1)/26)*26)+64)
Inglés :
=IF(INT((COLUMN()-1)/26)=0,"",CHAR((INT(COLUMN()-1)/26)+64))&CHAR(COLUMN()-(INT((COLUMN()-1)/26)*26)+64)
Puede reemplazar la columna() por el número de fila.
Otra forma posible sería utilizar algo como esto:
=INDIRECT("MySheet1!"&LOOKUP(COLUMN(),colid)&ROW())
Donde colid se refiere a un rango nombrado que usted crearía en otra parte del libro de trabajo que comprende dos columnas adyacentes con múltiples filas: la primera columna que contiene los números del 1 al n correspondientes al número COLUMN(), la segunda que contiene las letras A - ZZ, o cuantas referencias de columna desee acomodar. El ROW() está bien a la izquierda como para devolver el número de fila.
Así que si usted copiara la cadena anterior en la celda A1 de ‘Mi Hoja2’, evaluaría como =MySheet1!A1, y devolvería el valor que encontró en la celda correspondiente de MySheet1.
Esto le permitiría, por ejemplo, utilizar MySheet1 como área de trabajo, para borrar y volver a insertar nuevos datos, mientras que cualquier formato o cálculos en MySheet2 que se refieran a esos contenidos seguirán funcionando correctamente con los nuevos conjuntos de datos de la hoja de trabajo de la pestaña de destino.
=MID(ADDRESS(ROW(),COLUMN()),2,FIND("$",ADDRESS(ROW(),COLUMN()),2)-2)
La fórmula de la dirección funciona devolviendo el nombre de la columna y la fila. El formato devuelto siempre será $(Column Letters)$(Row Numbers) - por ejemplo $AA$2 o $XAA$243556
Si sabemos que $ siempre aparecerá en el primer carácter, podemos usar la primera fórmula intermedia para empezar a sacar caracteres después del primer signo $ (es decir, el 2º carácter).
A continuación, encontramos el siguiente signo $ (ya que sabemos que sólo habrá dos) y sabemos cuántos caracteres hay entre el primer y el segundo signo de dólar. El resto es una simple sustracción.
Aquí hay un VBA, fórmula definida por el usuario, solución. Funciona con columnas de 1, 2 y 3 letras.
Ponga lo siguiente en un módulo de código:
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() en cualquier celda devolverá la letra de la columna de la celda. =COLUMNLETTER(B3) en cualquier celda devolverá B.
Esta función definida por el usuario funciona muy bien cuando se crean fórmulas genéricas dentro de la función INDIRECTA.
La siguiente fórmula funciona sin importar donde la pongas (te devolverá el nombre de la columna).
=SUBSTITUTE(SUBSTITUTE(ADDRESS(ROW(),COLUMN()),"$",""),ROW(),"")