Función DIRECCIÓN de Excel para obtener la dirección de la celda y más

Si eres usuario de Excel, seguramente has utilizado la función DIRECCIÓN en más de una ocasión. Esta poderosa función te permite obtener la dirección de una celda específica en una hoja de cálculo de Excel. Pero, ¿sabías que también puedes aprovecharla para realizar otras operaciones mucho más avanzadas? En este artículo, te enseñaremos cómo utilizar la función DIRECCIÓN de Excel para obtener no solo la dirección de una celda, sino también para realizar búsquedas, realizar operaciones matemáticas y hasta crear fórmulas personalizadas. ¡Sigue leyendo y descubre todas las posibilidades que puedes aprovechar con la función DIRECCIÓN de Excel!

El tutorial ofrece una breve introducción a la sintaxis de la función DIRECCIÓN y muestra cómo usarla para devolver una dirección de celda de Excel y más.

Para crear una referencia de celda en Excel, puede escribir las coordenadas de columna y fila manualmente. Alternativamente, puede obtener una dirección de celda de Excel a partir de los números de fila y columna proporcionados a la función DIRECCIÓN. Casi inútil por sí sola, en combinación con otras funciones esta técnica puede ser la única solución en situaciones en las que no es posible hacer referencia a una celda directamente.

Función DIRECCIÓN de Excel: sintaxis y usos básicos

La función DIRECCIÓN está diseñada para obtener una dirección de celda en Excel según los números de fila y columna especificados. La dirección de una celda se devuelve como una cadena de texto, no como una referencia real.

La función está disponible en todas las versiones de Excel para Microsoft 365 – Excel 2007.

La sintaxis de la función DIRECCIÓN es la siguiente:

DIRECCIÓN(núm_fila, núm_columna, [abs_num], [a1], [sheet_text])

Los dos primeros argumentos son obligatorios:

número_fila – el número de fila que se utilizará en la referencia de celda.

núm_columna – el número de columna para construir la referencia de celda.

Los últimos tres argumentos, que especifican el formato de referencia de la celda, son opcionales:

núm_abs – el tipo de referencia, absoluta o relativa. Puede tomar cualquiera de los siguientes números; el valor predeterminado es absoluto.

  • 1 u omitido: referencia de celda absoluta como $A$1
  • 2 – referencia mixta: columna relativa y fila absoluta como A$1
  • 3 – referencia mixta: columna absoluta y fila relativa como $A1
  • 4 – referencia de celda relativa como A1

a1 – el estilo de referencia, A1 o R1C1. Si se omite, se utiliza el estilo A1 predeterminado.

  • 1 o VERDADERO u omitido: devuelve la dirección de la celda en el estilo de referencia A1 donde las columnas son letras y las filas son números.
  • 0 o FALSO: devuelve la dirección de la celda en el estilo de referencia R1C1 donde las filas y columnas están representadas por números.

texto_hoja – el nombre de la hoja de trabajo a incluir en la referencia externa. El nombre de la hoja debe proporcionarse como una cadena de texto y entre comillas, por ejemplo, «Hoja2». Si se omite, no se utiliza ningún nombre de hoja de cálculo y la dirección predeterminada es la hoja actual.

Por ejemplo:

=ADDRESS(1,1) – devuelve la dirección de la primera celda (es decir, la celda en la intersección de la primera fila y la primera columna) como una referencia absoluta de celda $A$1.

=ADDRESS(1,1,4) – devuelve la dirección de la primera celda como referencia de celda relativa A1.
Función DIRECCIÓN de Excel para obtener la dirección de la celda y más

En la siguiente tabla, encontrará algunos tipos de referencia más que pueden devolver las fórmulas DIRECCIÓN.

Fórmula Resultado Descripción
=DIRECCIÓN(1,2) $B$1 Referencia de celda absoluta
=DIRECCIÓN(1,2,4) B1 Referencia de celda relativa
=DIRECCIÓN(1,2,2) B$1 Columna relativa y fila absoluta.
=DIRECCIÓN(1,2,3) $B1 Columna absoluta y fila relativa.
=DIRECCIÓN(1,2,1,FALSO) R1C2 Referencia absoluta en el estilo R1C1
=DIRECCIÓN(1,2,4,FALSO) R[1]C[2] Referencia relativa en el estilo R1C1.
=DIRECCIÓN(1,2,1,,»Hoja2″) Hoja2!$B$1 Referencia absoluta a otra hoja.
=DIRECCIÓN(1,2,4,,»Hoja2″) Hoja2!B1 Referencia relativa a otra hoja.

Cómo utilizar la función DIRECCIÓN en Excel: ejemplos de fórmulas

Los siguientes ejemplos muestran cómo utilizar la función DIRECCIÓN dentro de fórmulas más grandes para realizar tareas más difíciles.

Devuelve un valor de celda en una fila y columna determinadas

Si su objetivo es obtener un valor de una celda específica en función de sus números de fila y columna, use la función DIRECCIÓN junto con INDIRECTA:

INDIRECTO(DIRECCIÓN(núm_fila, núm_columna))

La función DIRECCIÓN genera la dirección de la celda como texto. La función INDIRECTA convierte ese texto en una referencia normal y devuelve el valor de la celda correspondiente.

Por ejemplo, para obtener un valor de celda basado en el número de fila en E1 y el número de columna en E2, use esta fórmula:

=INDIRECT(ADDRESS(E1,E2))
Función DIRECCIÓN de Excel para obtener la dirección de la celda y más

Obtener la dirección de una celda con el valor más alto o más bajo

En este ejemplo, primero encontraremos los valores más alto y más bajo en el rango B2:B7 usando las funciones MAX y MIN y enviaremos esos valores a celdas especiales:

Celda E2: =MAX(B2:B7)

Celda F2: =MIN(B2:B7)
Función DIRECCIÓN de Excel para obtener la dirección de la celda y más

Y luego, usaremos DIRECCIÓN en combinación con la función COINCIDIR para obtener las direcciones de las celdas.

Celda con el valor máximo:

=ADDRESS(MATCH(E2,B:B,0), COLUMN(B2))

Celda con el valor mínimo:

=ADDRESS(MATCH(F2,B:B,0), COLUMN(B2))
Función DIRECCIÓN de Excel para obtener la dirección de la celda y más

En caso de que no desee que los valores más alto y más bajo estén en celdas separadas, puede anidar la función MAX/MIN en el primer argumento de COINCIDIR. Por ejemplo:

Celda con el valor más alto:

=ADDRESS(MATCH(MAX(B2:B7),B:B,0), COLUMN(B2))

Celda con el valor más bajo:

=ADDRESS(MATCH(MIN(B2:B7),B:B,0), COLUMN(B2))

Cómo funcionan estas fórmulas

Para encontrar el número de fila, utiliza MATCH(lookup_value, lookup_array, [match_type]) función que devuelve una posición relativa de lookup_value en lookup_array. En nuestra fórmula, el valor de búsqueda es el número devuelto por la función MAX o MIN, y la matriz de búsqueda es la columna completa. En consecuencia, la posición relativa del valor de búsqueda en la matriz coincide exactamente con el número de fila de la hoja.

Para encontrar el número de columna, utiliza la función COLUM. Por supuesto, nada le impide escribir el número directamente en la fórmula, pero COLUMNA le ahorra la molestia de contar manualmente en caso de que la columna de destino esté en el medio de la hoja.

Obtener una letra de columna a partir de un número de columna

Para convertir cualquier número dado en una letra de columna, use la función DIRECCIÓN dentro de SUSTITUIR:

SUSTITUIR(DIRECCIÓN(1,número_columna,4),»1″,»»)

Como ejemplo, busquemos la letra de la columna correspondiente al número en A2:

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

Al observar los resultados a continuación, podemos decir que la primera columna de la hoja es A, lo cual es obvio; la décima columna es J, la 50th la columna es AX, y el 100th la columna es CV:
Función DIRECCIÓN de Excel para obtener la dirección de la celda y más

Cómo funciona esta fórmula

Para empezar, configure la función DIRECCIÓN para devolver una referencia relativa a la primera celda de la columna de destino:

  • Para el número de fila, utilice 1.
  • Para el número de columna, proporcione la referencia a la celda que contiene el número, A2 en nuestro ejemplo.
  • Para el argumento abs_num, ingrese 4.

Como resultado, DIRECCIÓN(1,A2,4) devolvería A1.

Para deshacerse de las coordenadas de la fila, ajuste la fórmula anterior en la función SUSTITUIR y reemplace «1» con una cadena vacía («»). ¡Hecho!

Obtener la dirección de un rango con nombre

Para encontrar la dirección de un rango con nombre en Excel, primero deberá obtener la primera y la última referencia de celda y luego unirlas. Esto funciona de manera un poco diferente en Excel predinámico (2019 y versiones anteriores) y Excel de matriz dinámica (Office 365 y Excel 2021). Los siguientes ejemplos son para Excel 2019 – Excel 2007. Las instrucciones para Excel 365 y Excel 2021 son aquí.

Cómo obtener la dirección de la primera celda de un rango

Para devolver una referencia a la primera celda de un rango con nombre, utilice esta fórmula genérica:

DIRECCIÓN(FILA(rango),COLUMNA(rango))

Suponiendo que el rango se denomina «Ventas», la fórmula real es la siguiente:

=ADDRESS(ROW(Sales), COLUMN(Sales))

Y devuelve la dirección de la celda superior izquierda del rango:
Función DIRECCIÓN de Excel para obtener la dirección de la celda y más

En esta fórmula, las funciones FILA y COLUMNA devuelven una matriz de todos los números de fila y columna del rango, respectivamente. Basándose en esos números, la función DIRECCIÓN crea una matriz de direcciones de celda. Pero debido a que la fórmula se ingresa en una sola celda, solo se muestra el primer elemento de la matriz, que corresponde a la primera celda del rango.

Cómo obtener la dirección de la última celda de un rango

Para encontrar la dirección de la última celda en un rango con nombre, use esta fórmula genérica:

DIRECCIÓN(FILA(rango)+FILAS(rango)-1, COLUMNA (rango)+COLUMNAS(rango)-1)

Aplicada a nuestra gama denominada «Ventas», la fórmula toma la siguiente forma:

=ADDRESS(ROW(Sales) + ROWS(Sales)-1, COLUMN(Sales) + COLUMNS(Sales)-1)

Y devuelve la referencia a la celda inferior derecha del rango:
Función DIRECCIÓN de Excel para obtener la dirección de la celda y más

Esta vez, necesitamos cálculos un poco más complejos para calcular el número de fila. Como en el ejemplo anterior, la función FILA nos da una matriz de todos los números de fila en el rango, {4;5;6;7} en nuestro caso. Necesitamos «desplazar» estos números por el número total de filas menos 1, de modo que el primer elemento de la matriz se convierta en el último número de fila. Para encontrar el recuento total de filas, empleamos la función FILAS y restamos 1 de su resultado: (4-1=3). Luego, sumamos 3 a cada elemento de la matriz inicial para hacer el cambio requerido: {4;5;6;7} + 3 = {7;8;9;10}.

El número de columna se calcula de manera similar: {2,3,4}+3-1 = {4,5,6}

A partir de las matrices anteriores de números de fila y columna, la función DIRECCIÓN ensambla una matriz de direcciones de celda, pero devuelve solo la primera correspondiente a la última celda del rango.

También se puede lograr el mismo resultado seleccionando los valores máximos de las matrices de números de fila y columna. Sin embargo, esto sólo funciona en una fórmula matricial, que requiere presionar Ctrl + Mayús + Intro para completarse correctamente:

=ADDRESS(MAX(ROW(Sales)), MAX(COLUMN(Sales)))

Cómo obtener la dirección completa de un rango con nombre

Para devolver la dirección completa de un rango con nombre, solo necesita concatenar las dos fórmulas de los ejemplos anteriores e insertar el operador de rango (:) en el medio.

DIRECCIÓN(FILA(rango), COLUMNA(rango)) & «:» & DIRECCIÓN (FILA (rango) + FILAS(rango)-1, COLUMNA(rango) + COLUMNAS(rango)-1)

Para que funcione para nuestro conjunto de datos de muestra, reemplazamos el «rango» genérico con el nombre del rango real «Ventas»:

=ADDRESS(ROW(Sales), COLUMN(Sales)) & ":" & ADDRESS(ROW(Sales) + ROWS(Sales)-1, COLUMN(Sales) + COLUMNS(Sales)-1)

Y obtenga la dirección del rango completo como absoluto referencia $B$4:$D$7:
Función DIRECCIÓN de Excel para obtener la dirección de la celda y más

Para devolver la dirección del rango como relativo referencia (sin el signo $, como B4:D7), establezca el argumento abs_num en ambas funciones DIRECCIÓN en 4:

=ADDRESS(ROW(Sales), COLUMN(Sales), 4) & ":" & ADDRESS(ROW(Sales) + ROWS(Sales)-1, COLUMN(Sales) + COLUMNS(Sales)-1, 4)

Naturalmente, se pueden realizar los mismos cambios en fórmulas individuales para la primera y la última celda, y el resultado será similar a este:
Función DIRECCIÓN de Excel para obtener la dirección de la celda y más

Cómo obtener la dirección de un rango con nombre en Excel 365 y Excel 2021

A diferencia del comportamiento tradicional de «una fórmula, una celda» en versiones anteriores, en el nuevo Excel, cualquier fórmula que potencialmente pueda devolver múltiples valores lo hace automáticamente. Este comportamiento se llama derrame.

Por ejemplo, en lugar de devolver la dirección de la primera celda, la siguiente fórmula genera las direcciones de todas y cada una de las celdas de un rango con nombre:

=ADDRESS(ROW(Sales), COLUMN(Sales))
Función DIRECCIÓN de Excel para obtener la dirección de la celda y más

Para obtener la dirección del primera celda Solo es necesario habilitar la intersección implícita, que se activa de forma predeterminada en Excel 2019 y versiones anteriores. Para esto, coloque el símbolo @ (operador de intersección implícito) antes de los nombres de los rangos:

=ADDRESS(@ROW(Sales), @COLUMN(Sales))

De manera similar, puedes arreglar otras fórmulas.

Llegar última celda en el rango:

=ADDRESS(@ROW(Sales) + ROWS(Sales)-1, @COLUMN(Sales) + COLUMNS(Sales)-1)

Para obtener el dirección de un rango con nombre:

=ADDRESS(@ROW(Sales), @COLUMN(Sales)) & ":" & ADDRESS(@ROW(Sales) + ROWS(Sales)-1, @COLUMN(Sales) + COLUMNS(Sales)-1)

La siguiente captura de pantalla muestra los resultados:
Función DIRECCIÓN de Excel para obtener la dirección de la celda y más

Consejo. Al abrir una hoja de cálculo con fórmulas creadas en una versión anterior en Excel de matriz dinámica, Excel inserta automáticamente un operador de intersección implícito.

Así es como se devuelve la dirección de una celda en Excel. Para ver más de cerca todas las fórmulas analizadas en este tutorial, puede descargar nuestro libro de trabajo de muestra a continuación. ¡Te agradezco por leer y espero verte en nuestro blog la próxima semana!

Libro de práctica para descargar.

Función DIRECCIÓN de Excel: ejemplos de fórmulas (archivo .xlsx)

Usted también podría estar interesado en

Error 403 The request cannot be completed because you have exceeded your quota. : quotaExceeded




Función DIRECCIÓN de Excel para obtener la dirección de la celda y más

Función DIRECCIÓN de Excel para obtener la dirección de la celda y más

La función DIRECCIÓN de Excel es una herramienta muy útil que nos permite obtener la dirección de una celda específica en una hoja de cálculo. Además de proporcionarnos la dirección de la celda, esta función también puede ser utilizada para obtener la dirección de otras referencias, como rangos de celdas o nombres definidos.

¿Cómo funciona la función DIRECCIÓN?

La función DIRECCIÓN se utiliza de la siguiente manera:

  1. Sintaxis: =DIRECCIÓN(fil, columna, [absoluta], [referencia])
  • fil: número que representa el número de fila.
  • columna: número que representa el número de columna.
  • absoluta: un valor opcional que indica si la referencia debe ser absoluta o relativa. El valor predeterminado es VERDADERO (absoluta).
  • referencia: un valor opcional que especifica el estilo de referencia de la celda. El valor predeterminado es A1.

Por ejemplo, si deseamos obtener la dirección de la celda en la fila 3 y columna 4, utilizando referencias absolutas y estilo de referencia R1C1, la fórmula sería: =DIRECCIÓN(3, 4, VERDADERO, 2).

¿Qué podemos hacer con la función DIRECCIÓN?

La función DIRECCIÓN puede ser utilizada para diversas tareas, como:

  • Crear fórmulas dinámicas que se ajusten automáticamente cuando se copian o mueven.
  • Obtener la dirección de una celda en función de alguna condición específica.
  • Referenciar rangos o nombres definidos según su dirección.
  • Generar enlaces a celdas específicas en hojas de cálculo.

Existen muchas formas de utilizar la función DIRECCIÓN en Excel para optimizar nuestros trabajos y agilizar nuestras tareas diarias.

Fuentes externas

A continuación, te proporcionamos algunas fuentes adicionales donde puedes obtener más información sobre la función DIRECCIÓN de Excel:

Esperamos que esta introducción a la función DIRECCIÓN de Excel te sea útil y te ayude a mejorar tu productividad al utilizar esta poderosa herramienta. ¡Empieza a aprovechar al máximo la función DIRECCIÓN en tus hojas de cálculo!


Deja un comentario