Función DIRECCION – Obtener la Dirección de la Celda – Excel y Google Sheets

Este tutorial muestra cómo utilizar la Función DIRECCIÓN en Excel y Google Sheets para devolver una dirección de celda como texto.

 

¿Qué es la Función DIRECCION?

La función DIRECCION devuelve la dirección de una celda como texto.

Normalmente, en una hoja de cálculo, proporcionamos una referencia de celda, y se devuelve un valor de esa celda. En cambio, la función DIRECCIÓN construye el nombre de una celda.

La dirección puede ser relativa o absoluta, en estilo A1 o R1C1, y puede o no incluir el nombre de la hoja.

DIRECCION – Ejemplo Básico

Digamos que queremos construir una referencia a la celda de la 4ta columna y 1era fila, también conocida como celda D1. Podemos utilizar el diseño que se muestra aquí:

 

Nuestra fórmula en B3 es simplemente

=DIRECCION(B1;B2)

Nota: Por defecto, la función DIRECCION devuelve una referencia de celda absoluta. Actualizando un argumento opcional podemos cambiar las referencias de celdas de absolutas a relativas. Revisemos esta y otras entradas a la Función DIRECCION…

Sintaxis y Entradas de la Función DIRECCION:

=DIRECCION(fila; columna; [abs]; [a1]; [hoja])

fila – El número de fila de la referencia. Ejemplo: 5 para la fila 5.

columna – El número de columna de la referencia. Ejemplo: 5 para la columna E. No se puede introducir «E» para la columna E

abs – [opcional] Un número que representa si la referencia debe tener referencias absolutas o relativas de fila/columna. 1 para absoluta. 2 para fila absoluta/columna relativa. 3 para fila relativa/columna absoluta. 4 para relativa.

a1 – [opcional]. Un número que indica si se utiliza el formato de referencia de celda estándar (A1) o el formato R1C1. 1/TRUE para estándar (por defecto). 0/FALSO para R1C1.

hoja – [opcional] El nombre de la hoja de trabajo a utilizar. Por defecto es la hoja actual.

 

DIRECCION con INDIRECTO

Podemos combinar DIRECCION con la función INDIRECTO. Considere este diseño, donde tenemos una lista de elementos en la columna D.

Podemos generar una referencia a D1 así

=DIRECCION(B1; B2)
=$D$1

Poniendo la función DIRECCION dentro de una función INDIRECTO, podremos utilizar la referencia de la celda generada y usarla de forma práctica. La función INDIRECTO tomará la referencia de «$D$1» y la utilizará para obtener el valor de esa celda.

=INDIRECTO(DIRECCION(B1; B2)
=INDIRECTO($D$1)
="Manzana"

Nota: Mientras que lo anterior da un buen ejemplo de cómo hacer útil la función DIRECCION, no es una buena fórmula para usar normalmente. Se requiere dos funciones, y debido al INDIRECTO será de naturaleza volátil. Una mejor alternativa hubiera sido usar la Función INDICE así: =INDICE(1:1048576; B1; B2)

Dirección del Valor Específico

A veces, cuando se tiene una lista grande de elementos, se necesita saber la ubicación de un elemento en la lista. Considere esta tabla de puntuaciones de los estudiantes. Hemos calculado los valores mínimo, mediano y máximo de estas puntuaciones en las celdas E2:G2.

Digamos que queremos encontrar estos valores. Tenemos dos opciones:

• Filtre nuestra tabla para cada uno de estos elementos.
• Utilice la función COINCIDIR con DIRECCION. Recuerde que COINCIDIR devolverá la posición relativa de un valor dentro de un rango.

Nuestra fórmula en E3 entonces es:

=DIRECCION(COINCIDIR(E2;$B:$B;0);2)

Podemos copiar esta misma fórmula en G3, y sólo la referencia E2 cambiará ya que es la única referencia relativa. Mirando hacia atrás en E3, la función COINCIDIR fue capaz de encontrar el valor de 98 en la 5ta fila de la columna B. Nuestra función DIRECCION entonces utilizó esto para construir la dirección completa de «$B$5».

Traducir las Letras de la Columna a Partir de los Números

Hasta ahora, todos los ejemplos han devuelto una referencia absoluta. Vamos a devolver una referencia relativa en su lugar.

A continuación, en la columna B, queremos calcular la letra de la columna correspondiente al número de la columna A.

Utilizaremos la función DIRECCION para devolver una referencia en la fila 1 en formato relativo, y luego eliminaremos el «1» de la cadena de texto para que sólo nos quede la(s) letra(s). Consideremos en nuestra tabla la fila 3, donde nuestra entrada es 13. Nuestra fórmula en B3 es

=SUSTITUIR(DIRECCION(1;A2;4);"1";"")

Observe que hemos dado el 3er argumento dentro de la función DIRECCION, que controla la referencia relativa frente a la absoluta. La función DIRECCION dará como resultado «M1», y luego la función SUSTITUIR elimina el «1» para que nos quedemos sólo con la «M».

Encontrar la Dirección de los Rangos con Nombre

En Excel, puedes nombrar rangos o rangos de celdas, lo que te permite simplemente referirte al rango nombrado en lugar de la referencia de la celda.

La mayoría de los rangos con nombre son estáticos, lo que significa que siempre se refieren al mismo rango. Sin embargo, también puedes crear rangos con nombre dinámicos que cambian de tamaño en función de alguna(s) fórmula(s).

Con un rango con nombre dinámico, podría necesitar saber la dirección exacta a la que apunta su rango con nombre. Podemos hacer esto con la función DIRECCION.

En este ejemplo, veremos cómo definir la dirección para nuestro rango con nombre llamado «Grades».

Volvamos a nuestra tabla de antes:

 

Para obtener la dirección de un rango, necesitas conocer la celda superior izquierda y la celda inferior izquierda. La primera parte es bastante fácil de lograr con la ayuda de las funciones FILA y COLUMNA. Nuestra fórmula en E1 puede ser

=DIRECCION(@FILA(Grades);@ COLUMNA(Grades))

La función FILA devuelve la fila de la primera celda de nuestro rango (que será 1), y la COLUMNA hará lo mismo para la columna (también 1). Esta fórmula obtendrá la celda inferior derecha

=DIRECCION(FILAS(Grades)-@FILA(Grades)+1;COLUMNAS(Grades)-@COLUMNA(Grades)+1)

Utilizamos las funciones FILAS y COLUMNAS para calcular el alto y el ancho del rango. Restando los números de la primera fila y columna, calculamos la última celda del rango.

Finalmente, para juntar todo en una sola cadena, podemos simplemente concatenar los valores con dos puntos en el medio. La fórmula en E3 puede ser

=E1 & ":" & E2

Nota: Si bien pudimos determinar la dirección del rango, nuestra función DIRECCION determinó si las referencias debían ser relativas o absolutas. Sus rangos dinámicos tendrán referencias relativas que esta técnica no recogerá.

Nota 2: Esta técnica sólo funciona en un rango de nombres continuo. Si tuviera un rango de nombres que se definiera como algo parecido a esta fórmula

=A1:B2; A5:B6

entonces la técnica anterior daría lugar a errores.

Función DIRECCION en Google Sheets

La función DIRECCION funciona exactamente igual en Google Sheets que en Excel.