En este tutorial, veremos cómo cambiar los números de columna de Excel a los caracteres alfabéticos correspondientes.
Al crear fórmulas complejas en Excel, es posible que a veces necesite obtener una letra de columna de una celda específica o de un número determinado. Esto se puede hacer de dos maneras: usando funciones integradas o una personalizada.
Cómo convertir el número de columna en alfabeto (columnas de una sola letra)
En caso de que el nombre de la columna consista en una sola letra, de la A a la Z, puede obtenerlo utilizando esta sencilla fórmula:
CARÁCTER(64 + número_columna)
Por ejemplo, para convertir el número 10 en una letra de columna, la fórmula es:
=CHAR(64 + 10)
También es posible ingresar un número en alguna celda y hacer referencia a esa celda en su fórmula:
=CHAR(64 + A2)
Cómo funciona esta fórmula:
La función CHAR devuelve un carácter basado en el código de carácter en el ASCII establecer. Los valores ASCII de las letras mayúsculas del alfabeto inglés son 65 (A) a 90 (Z). Entonces, para obtener el código de carácter de A mayúscula, agrega 1 a 64; para obtener el código de carácter de la B mayúscula, sumas 2 a 64, y así sucesivamente.
Cómo convertir el número de columna de Excel a letra (cualquier columna)
Si está buscando una fórmula versátil que funcione para cualquier columna en Excel (1 letra, 2 letras y 3 letras), necesitará usar una sintaxis un poco más compleja:
SUSTITUIR(DIRECCIÓN(1, número_columna, 4), «1», «»)
Con la letra de la columna en A2, la fórmula toma esta forma:
=SUBSTITUTE(ADDRESS(1, A2, 4), "1", "")
Cómo funciona esta fórmula:
Primero, construye una dirección de celda con el número de columna de interés. Para ello, suministre los siguientes argumentos a la función DIRECCIÓN:
- 1 para row_num (el número de fila realmente no importa, por lo que puede usar cualquiera).
- A2 (la celda que contiene el número de columna) para column_num.
- 4 para que el argumento abs_num devuelva una referencia relativa.
Con los parámetros anteriores, la función DIRECCIÓN devuelve la cadena de texto «A1» como resultado.
Como solo necesitamos una letra de columna, quitamos el número de fila con la ayuda de la función SUSTITUIR, que busca «1» (o cualquier número de fila que hayas codificado dentro de la función DIRECCIÓN) en el texto «A1» y lo reemplaza con un cuerda vacía («»).
Obtenga la letra de la columna del número de columna usando la función personalizada Función personalizada
Si necesita convertir números de columna en caracteres alfabéticos de forma regular, una función personalizada definida por el usuario (UDF) puede ahorrarle mucho tiempo.
El código de la función es bastante simple y directo:
Public Function ColumnLetter(col_num) ColumnLetter = Split(Cells(1, col_num).Address, "$")(1) End Function
Aquí, usamos la propiedad Cells para hacer referencia a una celda en la fila 1 y el número de columna especificado y la propiedad Address para devolver una cadena que contiene una referencia absoluta a esa celda (como $A$1). Luego, la función Dividir divide la cadena devuelta en elementos individuales usando el signo $ como separador, y devolvemos el elemento (1), que es la letra de la columna.
Pegue el código en el editor de VBA y su nueva función ColumnLetter estará lista para usarse. Para obtener instrucciones detalladas, consulte: Cómo insertar código VBA en Excel.
Desde el punto de vista del usuario final, la sintaxis de la función es tan simple como esto:
ColumnLetter(col_num)
Donde col_num es el número de columna que desea convertir en una letra.
Su fórmula real puede verse de la siguiente manera:
=ColumnLetter(A2)
Y devolverá exactamente los mismos resultados que las funciones nativas de Excel discutidas en el ejemplo anterior:
Cómo obtener la letra de la columna de cierta celda
Para identificar una letra de columna de una celda específica, use la función COLUMNA para recuperar el número de columna y envíe ese número a la función DIRECCIÓN. La fórmula completa tomará esta forma:
SUSTITUIR(DIRECCIÓN(1, COLUMNA(dirección_celular), 4), «1», «»)
Como ejemplo, busquemos una letra de columna de la celda C5:
=SUBSTITUTE(ADDRESS(1, COLUMN(C5), 4), "1", "")
Obviamente, el resultado es «C» 🙂
Cómo obtener la letra de la columna de la celda actual
Para calcular la letra de la celda actual, la fórmula es casi la misma que en el ejemplo anterior. La única diferencia es que la función COLUMNA() se usa con un argumento vacío para referirse a la celda donde está la fórmula:
=SUBSTITUTE(ADDRESS(1, COLUMN(), 4), "1", "")
Cómo crear una referencia de rango dinámico a partir del número de columna
Con suerte, los ejemplos anteriores le han dado algunos nuevos temas de reflexión, pero es posible que se esté preguntando acerca de las aplicaciones prácticas.
En este ejemplo, le mostraremos cómo usar la fórmula de «número de columna a letra» para resolver tareas de la vida real. En particular, crearemos una fórmula dinámica XLOOKUP que extraerá valores de una columna específica en función de su número.
De la siguiente tabla de muestra, suponga que desea obtener una cifra de ganancias para un proyecto determinado (H2) y una semana (H3).
Para realizar la tarea, debe proporcionar XLOOKUP con el rango desde el cual devolver valores. Como solo tenemos el número de semana, que corresponde al número de columna, primero convertiremos ese número en una letra de columna y luego construiremos la referencia del rango.
Para mayor comodidad, dividamos todo el proceso en 3 pasos fáciles de seguir.
- Convertir un número de columna en una letra
Con el número de columna en H3, use la fórmula ya familiar para cambiarlo a un carácter alfabético:
=SUBSTITUTE(ADDRESS(1, H3, 4), "1", "")
Consejo. Si el número en su conjunto de datos no coincide con el número de columna, asegúrese de hacer la corrección requerida. Por ejemplo, si tuviéramos los datos de la semana 1 en la columna B, los datos de la semana 2 en la columna C, y así sucesivamente, usaríamos H3+1 para obtener el número de columna correcto.
- Construya una cadena que represente una referencia de rango
Para crear una referencia de rango en forma de cadena, concatene la letra de la columna devuelta por la fórmula anterior con los números de la primera y la última fila. En nuestro caso, las celdas de datos están en las filas 3 a 8, por lo que estamos usando esta fórmula:
=SUBSTITUTE(ADDRESS(1, H3, 4), "1", "") & "3:" & SUBSTITUTE(ADDRESS(1, H3, 4), "1", "") & "8"Dado que H3 contiene «3», que se convierte en «C», nuestra fórmula sufre la siguiente transformación:
="C"&"3:"&"C"&"8"Y produce la cadena C3:C8.
- Hacer una referencia de rango dinámico
Para transformar una cadena de texto en una referencia válida que Excel pueda entender, anide la fórmula anterior en la función INDIRECTA y luego pásela al tercer argumento de BUSCARX:
=XLOOKUP(H2, E3:E8, INDIRECT(H4), "Not found")
Para deshacerse de una celda adicional que contiene la cadena de rango de retorno, puede colocar la fórmula DIRECCIÓN DE SUSTITUCIÓN dentro de la función INDIRECTA misma:
=XLOOKUP(H2, E3:E8, INDIRECT(SUBSTITUTE(ADDRESS(1, H3, 4), "1", "") & "3:" & SUBSTITUTE(ADDRESS(1, H3, 4), "1", "") & "8"), "Not found")
Con nuestra costumbre Función ColumnLetterpuede obtener una solución más compacta y elegante:
=XLOOKUP(H2, E3:E8, INDIRECT(ColumnLetter(H3) & "3:" & ColumnLetter(H3) & "8"), "Not found")
Así es como encontrar una letra de columna de un número en Excel. ¡Gracias por leer y espero verte en nuestro blog la próxima semana!
(archivo .xlsm)