Consulte indirectamente las columnas de la tabla

Anteriormente, exploramos el uso de la función INDIRECTA para hacer referencia a varias tablas en un libro. En esta publicación de seguimiento, ampliaremos la discusión y nos referiremos a columnas de tablas individuales.

Objetivo

Empecemos por nuestro objetivo. Tenemos varias tablas en un libro de trabajo. Tienen la misma estructura y datos del departamento de tienda. Por ejemplo, aquí está la tabla del Departamento A.

Nos gustaría poder seleccionar la tabla y la columna de los controles desplegables y hacer que Excel use la tabla y la columna seleccionadas en una función SUMA simple como se ilustra a continuación.

Muy bien, vayamos a ello.

Referencias de tablas estructuradas

Primero, descubramos cómo funcionan las referencias de tablas estructuradas de Excel. Una Referencia de Tabla Estructurada (STR) nos permite hacer referencia a un área específica dentro de una tabla, como por ejemplo una columna específica. Los STR comienzan con el nombre de la tabla seguida del área específica entre corchetes [].

Hacer referencia a una columna específica se logra así: NombreTabla[NombreColumna]. Por ejemplo, para hacer referencia a la columna Q1 en la tabla DeptA, usaríamos: DeptA[Q1].

Además de hacer referencia a columnas específicas, podemos hacer referencia a filas específicas. Por ejemplo, para hacer referencia solo a la fila del encabezado de la tabla DeptA, podríamos usar DeptA[#Headers]. Para obtener una lista de los STR disponibles, escriba el nombre de la tabla en una fórmula y luego escriba el corchete abierto. Excel mostrará una lista de STR válidos en la lista de autocompletar, como se muestra a continuación.

Ahora que conocemos la anatomía de los STR, pasamos a las celdas desplegables.

Validación de datos

Para crear los controles desplegables, usaremos la función de validación de datos.

El control desplegable en C6 se genera con la función de Validación de datos al permitir una lista igual a los nombres de las tablas. La configuración de esto se discutió en la publicación anterior .

El control desplegable en C7 se genera con la función de Validación de datos al permitir una lista igual a los nombres de las columnas. Para hacerlo, seleccione la celda de entrada C7 y luego el comando de cinta Validación de datos. Configúrelo para permitir una lista igual a las etiquetas de las columnas que desea que se muestren, como se muestra a continuación.

INDIRECTO

Llegados a este punto sólo queda escribir la fórmula. Necesitamos crear la referencia deseada con una cadena de texto y luego pasarla a la función INDIRECTA. La función INDIRECTA convertirá la cadena de texto en una referencia válida de Excel.

Por ejemplo, digamos que finalmente queremos reanudar la columna Q1 de la tabla Departamento. Podríamos utilizar la siguiente fórmula.

=SUMA(DptoA[Q1])

Pero, en lugar de ingresar el STR directamente en la fórmula, queremos almacenar el nombre de la tabla en la celda C6 y el nombre de la columna en C7. Entonces, construiríamos el STR usando el operador de concatenación (), de la siguiente manera:

=C6 y "[" y C7 y "]"

Nota: los espacios se muestran en la fórmula anterior para facilitar la lectura.

Suponiendo que DeptA está almacenado en C6 y Q1 está almacenado en C7, la fórmula anterior devuelve el STR deseado, DeptA[Q1].

Para convertir el STR en una referencia válida de Excel, usaremos la función INDIRECTA, de la siguiente manera.

=INDIRECTO(C6"["C7"]")

Finalmente, sumaremos las celdas de la referencia con la función SUMA, de la siguiente manera.

=SUM(INDIRECTO(C6"["C7"]"))

Esto calculará la suma de la tabla y columna seleccionadas, como se ilustra a continuación.

Si tiene alguna idea o método adicional, compártalo publicando un comentario a continuación.

Recursos adicionales

• Archivo de Excel de muestra: IndirectTableColRef
• Hacer referencia dinámicamente a las columnas de la tabla con INDEX/MATCH: SUMIFS con datos de tabla dinámica
• Publicación relacionada: Hacer referencia a tablas indirectamente
• Nota: nuestras tablas tenían los mismos encabezados de columna; Si fueran diferentes, podría usar la Validación de datos y permitir una lista igual a =INDIRECT(C6”[#Headers]”) para que la columna desplegable proporcione los encabezados de columna que se encuentran en la tabla identificada en C6.