Utilice el encabezado de columna para recuperar valores de una tabla de Excel

Esta publicación analiza formas de recuperar valores agregados de una tabla según las etiquetas de las columnas.

Descripción general

A partir de Excel 2007, podemos almacenar datos en una tabla con el icono de comando Insertar Cinta de tabla. Si aún no ha explorado esta increíble característica, consulte este artículo de la revista CalCPA Reglas de Excel .

Con frecuencia, necesitamos recuperar valores de tablas de datos para generar informes o análisis. Esta tarea es bastante sencilla utilizando funciones de búsqueda tradicionales o funciones de suma condicional. Sin embargo, al preparar libros de trabajo para utilizarlos de forma continua, debemos tener en cuenta el principio de coherencia de la fórmula. Esto significa que escribimos fórmulas consistentes dentro de un rango, para que podamos completarlas correctamente. A menudo, esto es sólo una cuestión de configurar correctamente las referencias de celda, como relativas, absolutas o mixtas. Sin embargo, cuando nuestros datos se almacenan en una tabla, podemos usar referencias de tablas estructuradas y encabezados de columnas para crear fórmulas consistentes.

Objetivos

Identifiquemos nuestras metas y objetivos antes de comenzar. Hemos exportado cierta información de facturas desde nuestro sistema de contabilidad y la hemos almacenado en una tabla llamada tbl_inv, que se muestra a continuación:

Nos gustaría recuperar valores de la tabla y agregarlos por ID de cliente para completar nuestro pequeño informe resumido, que se muestra a continuación:

Nuestro primer objetivo es escribir una fórmula única en C8 y luego completarla hacia abajo y hacia la derecha. Es decir, utilizar fórmulas consistentes.

A continuación, examinemos nuestros datos y nuestro informe. Una cosa a tener en cuenta es que cada cliente puede aparecer en muchas filas. Por lo tanto, necesitamos usar una función de suma condicional como SUMIFS en lugar de una función de búsqueda tradicional, que solo devolvería el valor relacionado del primer elemento coincidente. Si no está familiarizado con la función SUMIFS, consulte Suma de múltiples condiciones en Excel con SUMIFS .

A continuación, observamos que el orden de las columnas del informe difiere del orden de las columnas de datos. El orden del informe es cliente, importe, impuestos, envío y total. El orden de las columnas de datos es cliente, envío, impuestos, monto y total. Eso significa que necesitamos escribir una fórmula que pueda acomodar las diferencias en el orden de las columnas.

Entonces, para resumir nuestros objetivos:

• Utilice fórmulas consistentes
• Agregar varias filas
• Acomodar las diferencias en el orden de las columnas entre los datos y el informe

Podemos cumplir nuestros objetivos anidando las funciones ÍNDICE y COINCIDIR dentro de nuestra función SUMIFS para seleccionar dinámicamente la columna de suma adecuada. Analicemos la fórmula paso a paso.

Fórmula

El primer argumento de la función SUMIFS es la columna de números a sumar. Al completar la primera columna de nuestro informe, la columna que se debe agregar es la columna de monto de la tabla tbl_inv[Amount] . Nuestra fórmula sería algo como esto:

=SUMIFS(tbl_inv[Cantidad], tbl_inv[IDCliente],$B8)

Sin embargo, al completar la siguiente columna del informe, la columna de números que se agregará es la columna de impuestos de la tabla tbl_inv[Tax] . Nuestra fórmula sería algo como esto:

=SUMIFS(tbl_inv[Impuesto], tbl_inv[IDCliente],$B8)

Dado que el primer argumento de la función debe ser diferente para cada columna del informe, nos vemos obligados a escribir fórmulas únicas para cada columna. Esto no se ajusta a nuestro objetivo de coherencia de la fórmula. Entonces, la pregunta es, ¿cómo expresamos el primer argumento SUMIFS para que sea dinámico?

Un enfoque es utilizar las funciones ÍNDICE/COINCIDIR. Si no está familiarizado con las funciones ÍNDICE/COINCIDIR, no dude en consultar Cómo devolver un valor a la izquierda de la columna de búsqueda de BUSCARV para obtener más información. En esa publicación de blog, discutimos cómo la función ÍNDICE devuelve un valor de celda, pero hace mucho más que eso.

La función ÍNDICE en realidad puede devolver un valor de celda o una referencia de rango. Microsoft describe la función con dos “formas”, la forma de matriz y la forma de referencia. Es una forma elegante de decir que la función puede devolver un valor de celda (forma de matriz) o una referencia de rango (forma de referencia). Le pediremos a la función que devuelva una referencia de rango que pueda usarse como primer argumento SUMIFS.

Cuando hayamos terminado, el primer argumento de la función SUMIFS será dinámico y utilizará ÍNDICE para devolver una referencia de rango y COINCIDIR para determinar dinámicamente qué columna. Se verá más o menos así:

=SUMIFS(ÍNDICE(COINCIDIR(...)), tbl_inv[IDCliente],$B8)

Las funciones ÍNDICE/COINCIDIR proporcionarán a la función SUMIFS la columna de números para sumar. La idea básica es que le pediremos a la función ÍNDICE que devuelva una referencia y le pediremos a la función COINCIDIR que le indique a qué columna hacer referencia según el valor del encabezado. MATCH buscará el encabezado de la columna de nuestro informe, como Monto, en la fila del encabezado de la tabla. La suposición aquí es que las etiquetas del encabezado del informe coinciden con las etiquetas del encabezado de datos.

Dado que la función COINCIDIR devuelve el número de posición relativa de un elemento de la lista, le pedimos que nos diga el número de columna de la etiqueta del informe coincidente. Por ejemplo, la columna Importe es la sexta columna, por lo que devolvería 6 a la función ÍNDICE. INDEX utiliza esta información para devolver la referencia de la columna Importe a la función SUMIFS. SUMIFS utiliza esta referencia como columna de números para sumar.

Dado que hay varias partes móviles, simplemente profundizaremos en esta fórmula. Primero, reemplacemos el primer argumento de la función SUMIFS con una función ÍNDICE. La fórmula resultante se ve así:

=SUMIFS(ÍNDICE(tbl_inv,,6), tbl_inv[IDCliente],$B8)

Esta fórmula utiliza la función SUMIFS para agregar una columna de números. La columna de números a sumar es el primer argumento, que es una función ÍNDICE. El primer argumento de la función ÍNDICE proporciona el rango inicial, la tabla completa, tbl_inv. El segundo argumento de la función ÍNDICE es el argumento núm_fila y le indica a la función ÍNDICE qué número de fila devolver. Como queremos devolver todas las filas, dejamos este argumento en blanco. El tercer argumento de la función ÍNDICE es el argumento núm_columna, e ingresamos 6 porque la columna de cantidad de la tabla es la sexta columna. Por lo tanto, la función ÍNDICE anterior devuelve la referencia de rango correspondiente a la sexta columna de la tabla a la función SUMIFS.

Sin embargo, no podemos detenernos aquí porque codificamos el argumento column_num 6. Si completamos esta fórmula a la derecha, el 6 permanecería y todas las columnas del informe devolverían el mismo resultado, la suma de la columna de monto. En su lugar, debemos pedirle a Excel que determine dinámicamente qué número de columna debe usar la función ÍNDICE. Y esto se logra con la función COINCIDIR. La siguiente función COINCIDIR determinará qué columna tiene una etiqueta de encabezado de columna coincidente.

=COINCIDIR(C$7, tbl_inv[#Encabezados],0)

Esta función busca el valor en C$7 en la fila de encabezados de la tabla. Usamos una referencia de columna relativa (C) para que se actualice a medida que se llena a la derecha, y una referencia de fila absoluta ($7) para que quede bloqueada en la fila del encabezado del informe. Tenga en cuenta la referencia de la tabla estructurada especial que hace referencia a la fila de encabezados. Comienza con el nombre de la tabla, tbl_inv, y luego #Headers entre corchetes. Puede escribir esta referencia o simplemente usar el mouse para seleccionarla de forma interactiva. El tercer argumento, 0, le dice a la función que estamos buscando una coincidencia exacta.

Dado que la función COINCIDIR anterior devuelve 6, debemos anidarlo dentro de la función ÍNDICE. Si anidamos la función COINCIDIR dentro de la función ÍNDICE, obtenemos lo siguiente:

ÍNDICE(tbl_inv,, COINCIDIR(C$7,tbl_inv[#Headers],0))

Este segmento de fórmula devuelve la referencia de columna que necesita la función SUMIFS. Entonces, anidar las funciones ÍNDICE/COINCIDIR dentro de la función SUMIFS da como resultado lo siguiente:

=SUMIFS(ÍNDICE(tbl_inv,, COINCIDIR(C$7,tbl_inv[#Headers],0)), tbl_inv[IDCliente],$B8)

Podríamos escribir esta fórmula en C8 y luego completarla hacia abajo y hacia la derecha para crear el informe. Pero hay un pequeño detalle más.

Si este libro de trabajo está diseñado para usarse de forma recurrente y permanecer en su lugar durante mucho tiempo, entonces debemos pensar en formas en que un futuro usuario podría romper el libro y abordar cualquier riesgo desde el principio, como se analiza en el volumen de Excel University. 1 Capítulo 19. Por lo tanto, debemos considerar las diferentes formas en que los futuros usuarios pueden intentar completar nuestra fórmula hacia abajo y hacia la derecha, porque la forma en que lo hacen correctamente podría romper nuestra fórmula accidentalmente.

Específicamente, si un usuario intenta completar la fórmula correctamente con el comando de relleno, copiar y pegar o con Ctrl+Entrar, Excel trata la referencia tbl_inv[CustID] como absoluta. Esto es bueno porque a medida que completamos la fórmula correctamente, queremos que todas las fórmulas hagan referencia a la columna de identificación del cliente. Sin embargo, si un usuario elige completar la fórmula correctamente arrastrando el controlador de relleno, entonces la referencia se trata como relativa, es decir, también se deslizará hacia la derecha y cambiará a tbl_inv[Shipping] , tbl_inv[Tax] , etc. en.

Entonces, para estar seguros, modificaremos ligeramente la referencia para que Excel la trate como absoluta incluso si un usuario completa correctamente con el controlador de relleno. Lo actualizaremos a una referencia de rango de una sola columna: tbl_inv[[CustID]:[CustID]] . La fórmula final es:

=SUMIFS(ÍNDICE(tbl_inv,,COINCIDIR(C$7,tbl_inv[#Headers],0)),tbl_inv[[IDCliente]:[IDCliente]], $B8)

Ingresamos la fórmula en C8 , la completamos hacia abajo y hacia la derecha, y el informe resultante se muestra a continuación.

Aunque parezca que nos llevó mucho tiempo llegar hasta aquí, el resultado final es una fórmula que cumple con nuestros objetivos. Se puede completar hacia abajo y hacia la derecha y continuar funcionando, se adapta a las diferencias en el orden de las columnas y agrega valores.

Conclusión

En general, vale la pena dedicar tiempo a crear fórmulas coherentes que se puedan completar correctamente y seguir funcionando para libros de trabajo de uso recurrente porque hace que su actualización sea más rápida con el tiempo. Además, escribir fórmulas que puedan adaptarse a cambios estructurales menores, como el orden de las columnas que cambian con el tiempo, ayudará a reducir los errores y mejorar la eficiencia. Aunque la inversión inicial de tiempo para lograr que una fórmula como la anterior funcione puede ser significativa, recuperará su inversión en cada período posterior a través de una mayor productividad.

Archivo de muestra

Para consultar el archivo de Excel que se utilizó para preparar las capturas de pantalla, no dude en descargar el archivo de muestra:

Recuperar datos de tabla

Para comprobar la versión actualizada que reemplaza la referencia de celda $B8 con una referencia con nombre, no dude en descargar el archivo de muestra:

Recuperar datos de tabla2

Notas adicionales

• Si necesita recuperar una cadena de texto, en lugar de la suma de números, probablemente desee utilizar funciones de búsqueda tradicionales, como BUSCARV. El archivo de muestra del libro de trabajo incluye una hoja BUSCARV con las fórmulas relevantes.
• Si desea obtener más ayuda con las funciones SUMIFS, ÍNDICE y COINCIDIR, no dude en consultar nuestros cursos de capacitación en línea de Excel .
• Si prefiere otro enfoque, publique un comentario, ¡nos encantaría conocerlo!