Tabla de mapeo sin columna auxiliar

Una tabla de mapeo es una forma práctica de traducir automáticamente etiquetas entre sistemas e informes. Como ocurre con casi todo en Excel, hay muchas formas de implementar una tabla de mapeo. Por ejemplo, podríamos crear una columna auxiliar para almacenar los montos con la función SUMIFS como mencioné en este artículo de Journal of Accountancy. También podemos usar Power Query o Power Pivot como se analiza en la serie de blogs Treasure Maps . En esta publicación, hablaremos de otra opción que usa la función FILTRO y no requiere una columna auxiliar.

Objetivo

Antes de entrar en detalles, obtengamos una comprensión básica de una tabla de mapeo y cómo funciona.

Digamos que exportamos algunos datos de un sistema y muestra el total de cada cuenta de esta manera:

Nos gustaría resumir los valores. Pero nos gustaría utilizar diferentes etiquetas (o grupos) en nuestro informe, como este:

Nuestros datos se presentan por cuenta, como cuenta corriente, mercado monetario y cuenta de ahorros. Pero estos tres valores deben sumarse y presentarse en la línea del informe denominada Efectivo y equivalentes de efectivo.

Entonces, una opción es incluir una tabla de mapeo en el libro de trabajo, que esencialmente proporciona a Excel las traducciones necesarias. Se parece a esto:

Ahora que le hemos dicho a Excel cómo los nombres de las cuentas (AcctName) se traducen en líneas de los estados financieros (Línea FS), necesitamos escribir una fórmula inteligente para resumir los valores correspondientes para el informe. Vamos a por ello.

Detalles

Escribiremos nuestra fórmula en el informe, que recuperará y agregará las cantidades correspondientes. Queremos escribir nuestra fórmula en la primera celda del informe J12 y queremos poder copiar esa misma fórmula para todas las filas del informe:

Nuestra fórmula utilizará FILTRO, SUMIFS y SUM. Hablemos de ellos uno a la vez.

Nota: no todas las versiones de Excel admiten la función FILTRO. Para determinar si el suyo lo hace, navegue hasta cualquier celda en blanco y escriba =FIL. Si ve FILTRO en la lista de funciones desplegable, entonces está listo para comenzar. Si no, utilice esta técnica en su lugar.

FILTRAR

Usaremos la función FILTRO para devolver una lista de las cuentas para cualquier línea fs determinada. Por ejemplo, cuando le pedimos que recupere todas las cuentas en la tabla de asignación de Efectivo y Equivalentes de efectivo, queremos que devuelva Cuenta corriente, Mercado monetario y Ahorros.

Escribimos la siguiente fórmula en J12:

=FILTRO(Tabla2[NombreCuenta],Tabla2[Línea FS]=I12)

Esto le indica a Excel que recupere los valores de la columna AcctName, donde el valor de la columna Línea FS es igual a nuestra línea de informe en la celda I12 . Dado que la fórmula devuelve varios valores, Excel usa automáticamente las celdas adyacentes para mostrar esos valores. Entonces, la fórmula única en J12 devuelve tres valores, que se muestran en el rango de derrame J12:J14 que se muestra a continuación:

Podemos ver que nuestra fórmula devolvió con éxito las tres cuentas asignadas a la línea Efectivo y equivalentes de efectivo. Hasta aquí todo bien… ahora necesitamos de alguna manera sumar los montos de esas cuentas. Aquí es donde SUMIFS resultará útil.

Nota: si las celdas en el rango de derrame adyacente están ocupadas, recibirás un #DERRAME. error hasta que borre esas celdas. Si desea obtener más información sobre la función FILTRO, consulte esta publicación.

SUMIFS

Usaremos la función SUMIFS para sumar todas las filas de la tabla de datos para cada una de las cuentas devueltas por la función FILTRO.

Entonces, actualizamos nuestra fórmula en J12 de la siguiente manera:

=SUMIFS(Tabla1[Cantidad],Tabla1[Nombre de cuenta],FILTRO(Tabla2[Nombre de cuenta],Tabla2[Línea FS]=I12))

Notarás que básicamente usamos la función FILTRO anterior como tercer argumento de la función SUMIFS. Esto le indica a Excel que sume los valores en la columna Monto, pero solo incluya aquellas filas donde el valor AcctName es igual a las cuentas devueltas por la función FILTRO.

Los resultados se muestran a continuación (estos son los montos de las tres cuentas individuales de Efectivo y Equivalentes de Efectivo):

Ahora, solo necesitamos agregar los tres resultados en un solo valor y lo haremos con la función SUMA.

Nota: si su tabla de datos contiene solo una fila para cada cuenta, puede usar BUSCAR XL en lugar de SUMIFS. El beneficio de SUMIFS es que funciona incluso cuando hay múltiples transacciones de datos para cada cuenta.

SUMA

El último paso es sumar todos los valores devueltos por la función SUMIFS. Podemos hacer esto envolviendo una función SUMA alrededor de nuestra fórmula anterior, así:

=SUMA(SUMARSIFS(Tabla1[Cantidad],Tabla1[Nombre de la cuenta],FILTRO(Tabla2[Nombre de la cuenta],Tabla2[Línea FS]=I12)))

Le damos enter y bam:

Ahora podemos copiar o completar esa fórmula para las filas restantes del informe y listo:

Conclusión

La idea de utilizar una tabla de mapeo para traducir etiquetas es muy útil. Hay muchas formas de implementar una tabla de mapeo y esta publicación demuestra una de esas opciones. La ventaja de esta opción es que no requiere una columna auxiliar. Realmente espero que este artículo ayude

Si tiene alguna idea, comentario, pregunta o alternativa, publique un comentario a continuación.

Archivo de muestra

MapaSinAyuda.xlsxDescargar