Cómo crear texto dinámico en Excel (TEXTO + Formatos numéricos)

En una publicación anterior , analizamos los formatos numéricos ideales de Excel para contabilidad y finanzas. En cada ejemplo, formateamos los valores de las celdas. Sin embargo, existe otra forma de utilizar estos formatos numéricos; podemos usarlos dentro de la función TEXTO para generar encabezados personalizados y texto dinámico. Eso es lo que estamos viendo en esta publicación.

Tabla de contenido

• Comprender la función TEXTO
• Sintaxis y argumentos
• Ejemplo sencillo
• Contexto diferente: celdas vs función TEXTO
• Ignorar el formato del texto
• Reconsiderando el cero
• Alineación
• Colores
• formato base
• Aplicar format_text como valor de celda versus codificado
• Mostrando unidades
• Símbolos y decimales
• Usar formatos numéricos en lugar de la función SI
• 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 de archivo: 0147 Formatos numéricos dentro de la función TEXTO.xlsx

Tener acceso

Comprender la función TEXTO

El propósito de la función TEXTO es convertir un número en texto usando un formato de número específico.

Sintaxis y argumentos

=TEXT(value, format_text)

• valor : el número a convertir a texto
• format_text : una cadena de texto que define el formato numérico que se aplicará

Ejemplo sencillo

El siguiente es un ejemplo sencillo para ilustrar la función TEXTO.

La fórmula en la celda D4 es:

="Training costs for September were " C4 "."

El número 5000 en la celda C4 está incluido en la oración, pero no tiene formato.

Podemos hacerlo mejor que eso. La fórmula en la celda D5 incluye la función TEXTO:

="Training costs for September were " TEXT(C5,"$#,##0")  "."

Los $5,000 en C5 siguen siendo el mismo número pero incluyen un símbolo de moneda y un separador de mil. Esto es mucho más fácil de leer.

Para comprender estos códigos de formato numérico, consulte esta publicación: Formatos de números de Excel para finanzas contables que NECESITA saber

Para comprender más sobre la función de texto: https://exceljet.net/functions/text-function

Contexto diferente: celdas vs función TEXTO

Al aplicar formatos de números en la función TEXTO, el contexto de visualización difiere de aplicar formatos de números a las celdas. Por lo tanto, podemos simplificar el formato numérico básico.

Comencemos con un formato de número de celular común:

• Mil separadores
• Números negativos en rojo entre paréntesis.
• cero es un guión
• Alineación correcta de números (espacios en blanco a la derecha)

#,##0_);[Red](#,##0);-_);@_)

Cuando se aplica a las celdas, muestra valores positivos, negativos, cero y de texto de la siguiente manera:

Ignorar el formato del texto

La cuarta sección de un código de formato numérico define cómo debe mostrarse el texto. Sin embargo, la función TEXTO ya muestra texto. Por lo tanto, la cuarta sección sirve de poco en la función TEXTO. Entonces, ignorémoslo.

Por lo tanto, nuestro formato de ejemplo cambia de este:

#,##0_);[Red](#,##0);-_);@_)

A esto:

#,##0_);[Red](#,##0);-_)

Reconsiderando el cero

La tercera sección de un código de formato numérico define cómo se muestran los valores cero. Cuando se usa en una columna de números, es posible que deseemos usar un guión para representar un cero. Pero si estamos concatenando con otro texto, el guión probablemente no se verá bien. Es más probable que mostremos el cero como número, que es el formato numérico predeterminado. Por lo tanto, también se puede ignorar el formato cero de la tercera posición.

Nuestro formato vuelve a cambiar. De esto:

#,##0_);[Red](#,##0);-_)

A esto:

#,##0_);[Rojo](#,##0)

Alineación

Cuando utilizamos formatos de números dentro de una celda, queremos que los números negativos se muestren entre corchetes/paréntesis. Para que los números se alineen correctamente, forzamos los espacios en blanco a la derecha de los números positivos.

Sin embargo, cuando se utiliza la función TEXTO, la alineación de los números es prácticamente irrelevante.

Nuestro formato de ejemplo se simplifica una vez más. De esto:

#,##0_);[Red](#,##0)

A esto:

#,##0;[Rojo](#,##0)

Colores

Finalmente, si bien los colores funcionan al formatear celdas, no funcionan dentro de la función TEXTO.

Entonces, nuestro formato se reduce una vez más. De esto:

#,##0_);[Rojo](#,##0)

Esto esto:

#,##0;(#,##0)

Los valores positivos y cero se mostrarán con al menos un dígito y un separador de mil. Los números negativos se mostrarán entre paréntesis con al menos un dígito.

formato base

Lo que comenzó de la siguiente manera, para un formato de número de celular

#,##0_);[Red](#,##0);-_);@_)

Se ha reducido a lo siguiente para un formato de número de función de TEXTO:

#,##0;(#,##0)

Aplicar format_text como valor de celda versus codificado

Dentro de la función TEXTO, el argumento format_text es una cadena. Esto se puede codificar directamente en la función o puede ser un valor vinculado a una celda.

Codificado en función

La captura de pantalla anterior muestra el format_text aplicado como un valor codificado.

Impulsado por células

La captura de pantalla anterior muestra el formato_texto aplicado como un valor vinculado a una celda. El formato del número se incluye en la celda C2 .

Esta opción crea más flexibilidad. Sin embargo, rara vez cambiamos estos formatos; por lo tanto, creo que es un escenario en el que es aceptable incluir formatos de números codificados en estas funciones.

Mostrando unidades

Dentro del formato numérico, es posible que deseemos mostrar unidades para proporcionar más contexto a los números.

Podemos incluir símbolos de moneda incluyéndolos dentro del formato.

También podemos incluir k , m o b para mostrar números como miles, millones o miles de millones.

Al formatear números de esta manera, es común incluir comas y decimales para mostrar la granularidad requerida. Esto evita la necesidad de dividir un número para lograr el formato de visualización correcto.

Este formato de número también funciona con un valor codificado en una función de TEXTO.

=TEXT(C17,"$#,##0.0,,m;($#,##0.0,,m)")

Un método alternativo utiliza comillas dobles alrededor de las letras. Para el método vinculado a células esto no supone ningún problema. El valor de la celda sería:

$#,##0.0,,"m";($#,##0.0,,"m")

Pero para la versión codificada necesitamos incluir comillas dobles adicionales para que actúen como caracteres de escape.

=TEXTO(C14,”$#,##0.0,,””m””;($#,##0.0,,””m””)”)

También podemos usar CHAR(34) para representar un carácter de comilla doble. Consulte esta publicación para obtener más información sobre el uso de comillas dobles en cadenas de texto: Cómo agregar comillas dobles en una fórmula de Excel .

Símbolos y decimales

Los símbolos y decimales son los mismos para ambos métodos. Por lo que no necesitamos aplicar ningún tratamiento especial para ellos.

Usar formatos numéricos en lugar de la función SI

Un escenario financiero típico puede ser indicar si un movimiento es favorable o adverso a un período o presupuesto anterior.

Podríamos generar las palabras favorable o adversa usando una función SI.

La fórmula en la celda D3 es:

=IF(C3=0,"favorable","adverse")

En la captura de pantalla anterior, C3 es positivo y muestra la palabra favorable.

Sin embargo, podemos lograr esto directamente con TEXTO sin una función SI.

La captura de pantalla anterior muestra la palabra favorable en la celda E6 porque el valor en la celda C6 es positivo o cero. Este valor cambia a adverso en la celda E7 porque la celda C7 es negativa.

La fórmula en la celda F6 muestra cómo podemos usar estas funciones de TEXTO para crear oraciones.

="There is a "  E6  " variance of " D6  " to last month"

Podemos lograr el mismo valor favorable o adverso a partir de formatos codificados.

La fórmula en la celda E4 es:

=TEXTO(C4,”””favorable””;””adverso”””)

Con este método, los caracteres de escape hacen que la fórmula sea mucho más difícil de entender.

Conclusión

Los formatos de números personalizados que usamos para las celdas también se pueden aplicar a la función TEXTO. Esto proporciona una excelente manera de agregar comentarios/conocimientos dinámicos a los informes.

Como el contexto difiere del formato de celda, es posible que debamos pensar de manera diferente sobre cómo aplicamos los formatos numéricos. No podemos usar colores, pero la otra funcionalidad aún está disponible.

Hemos visto dos formas de aplicar esto: valores vinculados a celdas o codificados. Dependiendo del método que elijamos, podríamos encontrarnos con algunos problemas de sintaxis. Pero los hemos cubierto en esta publicación, por lo que, con suerte, no lo sorprenderemos.

Artículos Relacionados:

• Formatos de números de Excel para finanzas contables que NECESITAS saber
• Cambiar el formato del número según el valor de una celda
• Cómo agregar comillas dobles en la fórmula de Excel

---