Referencias similares estructuradas en rangos con nombre

La función de tabla introducida en Excel 2007 es sorprendente y casi ha eliminado la necesidad de crear rangos con nombres dinámicos, ya que las tablas se expanden automáticamente. Más allá de la expansión automática, las tablas ofrecen muchos otros beneficios, incluidas referencias estructuradas que nos permiten hacer referencia a un área dentro de la tabla, como una columna específica. En esta publicación, examinaremos un método para simular referencias estructuradas cuando se usa un rango con nombre ordinario.

Objetivo

Antes de llegar demasiado lejos, dejemos claro nuestro objetivo.

Me encanta almacenar datos en tablas debido a sus propiedades especiales, como la expansión automática, las referencias estructuradas y el autocompletado de fórmulas. Siempre que sea posible, almaceno los datos de origen en tablas (Insertar Tabla). Por ejemplo, considere la siguiente tabla.

La tabla se llama Tabla1. Las referencias a tablas estructuradas comienzan con el nombre de la tabla seguido del área deseada entre corchetes. Por ejemplo, para hacer referencia a la columna denominada Importe en la tabla denominada Tabla1, la referencia estructurada es: Tabla1[Importe]. Entonces, para sumar la columna de monto, podríamos usar la siguiente fórmula:

=SUMA(Tabla1[Cantidad])

El sistema estructurado de nombres de referencia es maravilloso porque podemos referirnos fácilmente a regiones seleccionadas dentro de la tabla. Desafortunadamente, las referencias estructuradas no se aplican a rangos con nombres comunes configurados con el Administrador de nombres o el Cuadro de nombres. Afortunadamente, es fácil simular referencias estructuradas con una función de hoja de trabajo.

Veamos cómo.

Rangos con nombre

Comenzamos mirando nuestra hoja de trabajo. Los siguientes datos no se han convertido en una tabla con el comando Insertar Tabla. Se le ha asignado el nombre DataRange con el comando Fórmulas Administrador de nombres. Este nombre aparece en el cuadro de nombre (a la izquierda de la barra de fórmulas).

Esto nos lleva al motivo por el que estamos aquí: simular el sistema de referencia estructurado en un rango con nombre. Queremos hacer referencia a un área seleccionada dentro del rango nombrado. Queremos escribir una fórmula para sumar la columna de monto.

La función ÍNDICE nos ayudará. La función ÍNDICE puede devolver un área dentro de una referencia, como una fila o columna específica. Examinamos los primeros tres argumentos de la función ÍNDICE.

=ÍNDICE(referencia, núm_fila, [núm_columna])

Dónde:

• La referencia es la referencia completa, como nuestro DataRange.
• row_num es el número de fila, o 0 para todas las filas
• [column_num] es el número de columna opcional, o 0 para todas las columnas

Apliquemos la función ÍNDICE para sumar la columna de monto.

Podríamos usar la siguiente fórmula para sumar la columna de monto, que es la tercera columna del rango.

=SUMA(ÍNDICE(Rango de datos,0,3))

¡Sí lo hicimos! ¡Simulamos una referencia de tabla estructurada! Esta fórmula suma los valores de todas las filas de la tercera columna de la referencia denominada DataRange.

Cuando las posiciones de las columnas son estáticas, use un valor entero, como 3, para identificar la columna funciona bien. Pero, si el orden de las columnas es dinámico o queremos escribir una fórmula consistente para completar hacia abajo o hacia la derecha, podemos ser elegantes y ubicar el número de columna a través de la etiqueta del encabezado de la columna.

Vamos a ver.

Dinámica

Si es necesario, podemos usar la función COINCIDIR para ubicar dinámicamente la columna y luego proporcionar el número de columna correspondiente a la función ÍNDICE. La función COINCIDIR devuelve la posición relativa del valor coincidente y tiene la siguiente sintaxis.

=COINCIDIR(valor_buscado, matriz_buscada, [tipo_coincidencia])

Dónde:

• valor_buscado es el valor que estamos tratando de encontrar, el encabezado de la columna
• lookup_array es donde estamos buscando
• match_type … usaremos 0 porque queremos un valor coincidente exacto

En la captura de pantalla siguiente, hemos nombrado el rango de datos Datos y necesitamos escribir una fórmula en C6 para sumar la columna de cantidad… y… queremos completar esa misma fórmula para sumar los valores en las otras columnas.

La idea es utilizar la función COINCIDIR para ubicar el encabezado de la columna, como Monto, Impuesto sobre las ventas o Envío, y luego devolver el número de columna coincidente con la función ÍNDICE. Procedamos paso a paso.

Primero, la función COINCIDIR. Le pedimos a la función COINCIDIR que determine el número de columna. Queremos encontrar la etiqueta de la columna, Cantidad, dentro de la primera fila del rango de Datos. Podemos generar una referencia a la primera fila del rango de Datos con la función ÍNDICE. Por lo tanto, la siguiente fórmula ingresada en C6 devolvería 3 ya que el encabezado Monto está en la tercera posición:

= COINCIDIR (B6, ÍNDICE (Datos, 1,0), 0)

Dónde:

• B6 es el valor que estamos intentando encontrar, la etiqueta Cantidad
• INDEX(Data,1,0) es donde estamos buscando, la primera fila del rango de datos
• Dónde:
  • Los datos son la referencia.
  • 1 es la primera fila
  • 0 para todas las columnas
• 0 significa que queremos un valor coincidente exacto

La función COINCIDIR devuelve 3 ya que la etiqueta Cantidad se encuentra en la tercera posición dentro del rango de búsqueda. Ahora, podemos usar la función COINCIDIR en una función ÍNDICE que devuelve la referencia a toda la columna Monto, de la siguiente manera:

= ÍNDICE (Datos, 0, COINCIDIR (B6, ÍNDICE (Datos, 1,0), 0)))

Dónde:

• Los datos son la referencia.
• 0 significa todas las filas
• MATCH(B6,INDEX(Data,1,0),0) devuelve la tercera columna

Luego podemos colocar esta función ÍNDICE dentro de una función SUMA y completar la fórmula, como se muestra a continuación.

Es bueno saber que podemos simular el sistema de nombres de referencias de tablas estructuradas con funciones de hoja de trabajo para que podamos hacer referencia a regiones dentro de un rango con nombres ordinarios.

Si esta publicación ha sido útil o, si prefiere otro método, compártala agregando un comentario a continuación.

Recursos

• Ejemplo de simulación de referencia estructurada
• Otras publicaciones del ÍNDICE
• Otras publicaciones de PARTIDO
• Otras publicaciones de la mesa