Fórmula de escritura automática de comentarios en Excel – Amazing LAMBDA

Para un profesional de las finanzas en la industria, nada es más molesto que escribir el comentario de fin de período o pronóstico. (Está bien, tal vez las palabras “cambio de último momento en las disposiciones” sean más molestas, pero el comentario está a la altura). Ha creado los informes, ha encontrado los motivos de las variaciones y ahora necesita escribirlos como un resumen de texto. Siempre lleva más tiempo del debido y genera más retrabajo del que debería. Hoy quiero compartir una fórmula de redacción automática de comentarios en Excel para automatizar ese proceso.

NOTAS:

• Este es un ejemplo de lo que es posible con LAMBDA y funciones de matriz dinámica. No estoy sugiriendo que sea perfecto o que funcione para su escenario. Sin embargo, si puede tomarlo y adaptarlo para que funcione en su negocio, puede ahorrarle mucho tiempo y frustración.
• Necesitará una versión de Excel compatible con LAMBDA para trabajar junto con esta publicación (actualmente solo Excel 365).

Tabla de contenido

• Función de escritura automática de comentarios.
• Ejemplo básico
• Sintaxis
• Argumentos
• Notas adicionales
• Ejemplos
• La función
• El código
• ¿Cómo utilizar el código?
• Conclusión

Descargue el archivo de ejemplo: únase al programa Insiders gratuito y obtenga acceso al archivo de ejemplo utilizado para esta publicación.

Nombre del archivo: 0134 Fórmula de escritura de comentarios.xlsx

Tener acceso

Función de escritura automática de comentarios.

La función está construida como una función LAMBDA; por lo tanto, puede copiarlo y pegarlo en sus libros de trabajo. Pero antes de llegar a eso, veamos cómo funciona.

Ejemplo básico

Mire la captura de pantalla a continuación. El texto del comentario en la celda D11 ha sido escrito por mi función AUTOCOMMENTARIO.

Debido a que ha sido escrito mediante una fórmula, cuando los valores cambian, también lo hace el texto del comentario. Por ejemplo, mire la captura de pantalla a continuación; la descripción de la variación ha cambiado según los nuevos valores.

Sintaxis

La sintaxis de la función es la siguiente:

=AUTOCOMMENTARY(Heading, ComparativeName, SubHeadings, BaseValues, ComparativeValue, Reasons, SortOrder, PlusWord, MinusWord, StatementNumberFormat, VarianceNumberFormat, Threshold, DecimalAccuracy)

Sí, hay muchos argumentos, pero esto es para crear la función más flexible posible.

Argumentos

Los argumentos de la función son los siguientes (todos los argumentos son obligatorios)

• Encabezado: El nombre de lo que trata el comentario (por ejemplo, Ventas, EBIT, Acciones, Deudores, etc.)
• ComparativeName: el nombre de la medida comparativa (p. ej., Previsión, Presupuesto)
• Subtítulos: la serie de nombres por los cuales se clasifican los valores (por ejemplo, EBIT puede ser Ventas, Costo de Ventas, Salarios, Gastos Generales, Depreciación)
• BaseValues: la matriz de valores base que corresponden a la lista de subtítulos
• ComparativeValue: la matriz de valores comparativos que corresponden a la lista de subtítulos.
• Motivos: la matriz de valores que contiene los motivos de cada variación.
• SortOrder: el orden de clasificación para mostrar variaciones en el texto del comentario:
  • 1: orden ascendente
  • -1: orden descendente
  • 2: orden ascendente absoluto
  • -2: orden descendente absoluto
• PlusWord: Las palabras utilizadas para describir un movimiento positivo (por ejemplo, “más alto que”, “favorable a”)
• MinusWord: Las palabras utilizadas para describir un movimiento negativo (p. ej., “inferior a”, “adverso a”)
• StatementNumberFormat: la cadena de texto del formato de número personalizado para el número base total (por ejemplo, “$0.0,,m;($0.0,,m)”)
• VarianceNumberFormat: la cadena de texto del formato de número personalizado para los números de variación (por ejemplo, “+$0.0,,m;($0.0,,m)”)
• Umbral: el valor mínimo sobre el que informar las variaciones. Cualquier valor por debajo de este umbral se ignora o se agrupa en un solo comentario (por ejemplo, el uso de 100000 en este argumento ignora todas las variaciones inferiores a esta cantidad).
• DecimalAccuracy: la precisión del redondeo aplicada a los números. Utilice números negativos para redondear a decenas, centenas, millares, etc. (por ejemplo, -6 redondea al millón más cercano).

Notas adicionales

Aplicar los argumentos en sus escenarios puede volverse complejo, ya que conceptos matemáticos simples pierden significado en contabilidad. Por ejemplo, lo que se considera positivo o negativo, o favorable o adverso, puede depender de la señalización de un informe y también difiere entre empresas.

A continuación se demuestra la combinación de términos y cómo se ven en diferentes negocios:

• El costo de ventas puede mostrarse como un número negativo porque es un costo, o puede mostrarse como un número positivo porque todos saben que es un costo.
• Los acreedores comerciales más altos pueden considerarse una variación positiva porque no pagamos el efectivo, o una variación negativa porque es un pasivo mayor.
• En los saldos acreedores, la palabra "más alto" significa un crédito mayor. Pero los saldos acreedores se muestran como números negativos; por lo tanto, un saldo "más alto" da como resultado un número "más bajo".

Hay 4 argumentos que determinan cómo se comparan y muestran los números StatementNumberFormat, VarianceNumberFormat, Threshold y DecimalAccuracy. Tómese el tiempo para comprender estos argumentos y cómo impactan el resultado del texto.

Una vez que la fórmula esté configurada con la señalización correcta y el significado numérico para cada área, debería funcionar correctamente.

Ejemplos

Echemos un vistazo a algunos ejemplos. Todos estos están disponibles en el archivo de ejemplo.

Ejemplo 1

La fórmula en la celda D23 es:

=AUTOCOMMENTARY(A4,C4,A5:A8,B5:B8,C5:C8,D5:D8,-2,"higher than","lower than","$0.0,,m;($0.0,,m)","+$0.0,,m;($0.0,,m)",100000,-5)

Esto muestra:

• Las variaciones se muestran en orden absoluto descendente (es decir, del mayor impacto al menor impacto)
• Los números se muestran en millones con 1 decimal.
• Los valores inferiores a 100.000 están por debajo del umbral
• Los valores se redondean al 100.000 más cercano (es decir, una precisión decimal de -5)

Ejemplo #2

La fórmula en la celda D36 es:

=AUTOCOMMENTARY(A16,C16,A17:A21,B17:B21,C17:C21,D17:D21,1,"higher than","lower than","$0,k;($0,k)","+$0,k;($0,k)",2000,-3)

Esto muestra:

• Variaciones en orden ascendente (es decir, del número más pequeño al número más grande)
• Los números se muestran en miles.
• Los valores inferiores a 2000 están por debajo del umbral. Después de detallar las variaciones clave sobre 2000, la variación restante es cero; por lo tanto, no se requiere ninguna declaración adicional sobre los valores más pequeños.
• Los valores se redondean al 1000 más cercano (es decir, una precisión decimal de -3)

Ejemplo #3

La fórmula en la celda D49 es:

=AUTOCOMMENTARY(A29,C29,A30:A34,B30:B34,C30:C34,D30:D34,-1,"favourable to","adverse to","$0k;($0k)","+$0k;($0k)",10,0)

Esto muestra:

• Variaciones en orden descendente (es decir, del número más pequeño al número más grande)
• Los números se muestran con ak para indicar miles aunque los valores en los datos de origen no estén en miles (se supone que los datos de origen ya se muestran en miles)
• Los valores inferiores a 10 están por debajo del umbral y, por lo tanto, se incluyen en la frase final.
• Los valores se redondean a cero decimales (es decir, precisión decimal de 0)

Ejemplo #4

La fórmula en la celda D61 es:

=AUTOCOMMENTARY(A42,C42,A43:A46,B43:B46,C43:C46,D43:D46,-2,"higher than","lower than","$0.0,,m;($0.0,,m)","+$0.0,,m;($0.0,,m)",100000,-5)

Esto muestra:

• Las variaciones totales están por debajo del umbral; por lo tanto, no se devuelve ningún comentario detallado sobre la variación.

La función

Bien, ahora veamos la función en sí.

El código

Bien, aquí va; es largo.

=LAMBDA(Heading,ComparativeName,SubHeadings,BaseValues,ComparativeValues,Reasons,SortOrder,PlusWord,MinusWord,StatementNumberFormat,VarianceNumberFormat,Threshold,DecimalAccuracy,LET(BaseTotal,ROUND(SUM(BaseValues),DecimalAccuracy),ComparativeTotal,ROUND(SUM(ComparativeValues),DecimalAccuracy),VarianceTotal,ROUND(BaseTotal-ComparativeTotal,DecimalAccuracy),VarianceValues,ROUND(BaseValues-ComparativeValues,DecimalAccuracy),SortOrderValues,IF(ABS(SortOrder)=2,ROUND(ABS(BaseValues-ComparativeValues),DecimalAccuracy),ROUND(BaseValues-ComparativeValues,DecimalAccuracy)),isAre,IF(RIGHT(Heading,1)="s","are","is"),TempArray,SORT(FILTER(HSTACK(VarianceValues,SortOrderValues,SubHeadings" variance of "TEXT((VarianceValues),VarianceNumberFormat)IF(ISBLANK(Reasons),""," due to "Reasons)),ABS(VarianceValues)=Threshold,{0,0,"N/A"}),2,IF(SortOrder0,-1,1)),ReasonList,TEXTJOIN("; ",TRUE,DROP(TempArray,,2)),UnanalyzedTotal,VarianceTotal-ROUND(SUM(TAKE(TempArray,,1)),DecimalAccuracy),ReasonsError,ISERROR(TAKE(TempArray,1,1)),IndexItem,SWITCH(TRUE,VarianceTotal=0,1,ABS(VarianceTotal)Threshold,1,ReasonsError=TRUE,2,3),TempStack,VSTACK(Heading" of "TEXT(BaseTotal,StatementNumberFormat)" "isAre" ""inline with "ComparativeName".",Heading" of "TEXT(BaseTotal,StatementNumberFormat)" "isAre" "TEXT(VarianceTotal,VarianceNumberFormat)" "IF(VarianceTotal0,MinusWord,PlusWord)" "ComparativeName". This is driven by variances below "TEXT(Threshold,StatementNumberFormat)".",Heading" of "TEXT(BaseTotal,StatementNumberFormat)" "isAre" "TEXT(VarianceTotal,VarianceNumberFormat)" "IF(VarianceTotal0,MinusWord,PlusWord)" "ComparativeName"."" This is driven by "ReasonListIF(UnanalyzedTotal=0,".","; the remaning varaince of "TEXT(UnanalyzedTotal,VarianceNumberFormat)" relates to items below "TEXT(Threshold,StatementNumberFormat)".")),CHOOSEROWS(TempStack,IndexItem)))

La fórmula es más fácil de leer en el complemento Advanced Formula Environment:

¿Cómo utilizar el código?

Hay varias formas de incorporar esta función a sus libros de trabajo.

Administrador de nombres

Para usar esto en sus libros de trabajo, copie la función anterior en el administrador de nombres y use el nombre AUTOCOMMENTARIO.

Copiar del archivo de ejemplo

Alternativamente, descargue el archivo de ejemplo y copie y pegue una de las celdas en su libro de trabajo. Esto moverá la función LAMBDA a su libro de trabajo para que pueda usarla.

Conclusión

Escribir comentarios de variación a partir de números es un proceso que requiere mucho tiempo. Usando las últimas funciones de Excel (LAMBDA, LET, Dynamic Arrays), podemos aplicar una lógica compleja para crear (con suerte) oraciones significativas. Si lo hacemos bien, podemos ahorrar mucho tiempo al tener que actualizar y reescribir los comentarios.

Artículos Relacionados

• Matrices dinámicas en Excel: todo lo que NECESITAS saber
• Uso de rebanadores con fórmulas matriciales dinámicas en Excel

---