Como Cambiar el formato del número según el valor de una celda

Imagine una situación en la que ha creado un informe o panel dinámico del que está realmente orgulloso. El usuario puede realizar selecciones en un cuadro desplegable y el informe se actualiza automáticamente, es algo hermoso. Sólo hay un problema con este tipo de informes/paneles dinámicos: no toda la información tiene la misma importancia. Por ejemplo, el Producto A tiene una ganancia de $30.000.000, pero el Producto J solo tiene una ganancia de $100.000. ¿Cómo debería formatear los números para que funcionen con cualquier producto seleccionado? ¿Cómo se garantiza que los números grandes no sean demasiado detallados y que los números pequeños no queden enmascarados por el tamaño del redondeo? Si el producto A cambió con respecto al último período en $50 000, a nadie le importa, es demasiado pequeño, pero si el Producto J cambia en esa cantidad, ese es un movimiento significativo.

Tengo dos posibles soluciones para ti. Dependiendo de las circunstancias podrás elegir la opción que se adapte a tus necesidades. Ambas circunstancias dependen del formato de número personalizado, así que tengamos una breve descripción general antes de ver las dos opciones.

Formato de número personalizado

Como probablemente sepa, puede formatear números en Excel usando los íconos en el menú Inicio.

Pero no estás limitado a estas opciones. Puede utilizar formatos de números personalizados para mostrar los números como desee. El formato de número personalizado es una cadena de texto que le indica a Excel cómo desea que se formatee el número.

Los formatos de números personalizados están separados en 4 secciones, todas separadas por punto y coma.

[visualización de valores positivos] ; [visualización de valores negativos] ; [visualización de valores cero] ; [visualización de valores de texto]

• # (hash) representa un dígito opcional. Si hay un número, se mostrará.
• 0 (cero) representa un dígito forzado. Incluso si no hay un número para mostrar, se mostrará un cero. Cualquier cero que siga a los decimales representa el número de decimales.
• , (coma) al final indica que el valor debe dividirse entre 1.000, dos comas significan que se divide entre 1.000.000.
• , (coma) entre # o 0 indica el carácter separador de mil
• . (punto) indica que siempre debe mostrarse un decimal.

Cualquier formato de número de ejemplo es:

#,##0.0,,;-#,##0.0,,;0.0,,

Con base en las reglas básicas anteriores, vea si puede descubrir cómo este formato de número personalizado mostraría un número. Para obtener una descripción más detallada de los formatos de números personalizados, consulte este tutorial .

Opción 1: formatear con la función de texto

Esta opción es útil cuando el valor se utiliza en una cadena de texto. Eche un vistazo a la captura de pantalla a continuación.

La fórmula en la celda C10 es

=TEXTO(B10,BUSCARV(B10,$B$4:$C$5,2,1))

La función TEXTO tiene dos argumentos:

• Valor: el número que se va a formatear
• Format_text: el formato de número que se aplicará

Entonces, nuestra fórmula formatea el valor en la celda B10 según el valor BUSCARV devuelto por las celdas B4-C5. Hemos utilizado una coincidencia aproximada en nuestra BUSCARV , que proporciona el valor más cercano debajo del valor de búsqueda. En nuestras circunstancias, la celda B5, que tiene un valor de 1.000.000, es el valor más cercano a B10, como resultado, el valor de la celda C5 se devuelve como Format_text.

C5 muestra el siguiente texto:

#,##0.0,,;-#,##0.0,,;0.0,,

El resultado es que la celda C10 se muestra como 30,0 (30.000.000 dividido por 1.000.000 y se muestra con un solo punto decimal).

La fórmula en la celda C11 es:

=TEXTO(B11,BUSCARV(B12,$B$4:$C$5,2,1))

Esta BUSCARV devolverá el formato de número de la celda C4. Entonces, el resultado de la celda C11 es 100 (100 000 dividido por 1000 y se muestra sin decimales).

Esta opción de usar la función TEXTO es muy útil cuando el valor se va a usar dentro de una cadena de texto. Sin embargo, es importante darse cuenta de que los valores de las celdas C10 y C11 ya no son números, sino texto almacenado para que parezcan números. Por lo tanto, no puede aplicar ninguna función numérica en estas celdas.

Opción 2: Formatear con formato condicional

Si queremos realizar algún cálculo sobre el número formateado, debemos utilizar el formato condicional.

Los valores en las celdas D10 y D11 son iguales a B10 y B11, pero formatearemos los números según sus valores. Seleccione las celdas D10 y D11, luego en el menú Inicio seleccione Formato condicional – Nueva regla…

Primero, configuraremos el formato de número personalizado donde el valor es mayor o igual a 1.000.000. Haga clic en Usar un formato para determinar qué celdas formatear y luego ingrese una regla para determinar cuándo se debe aplicar el formato. Luego, haga clic en Formatear….

Formatee el número para que sea el mismo que el texto de la celda C5.

Haga clic en Aceptar y luego en Aceptar nuevamente para volver al Administrador de reglas de formato condicional.

Seleccione Nueva regla y configure otro número personalizado para valores inferiores a 1.000.000

Esta vez cree un formato de número igual que la celda C4.

Haga clic en Aceptar y luego en Aceptar nuevamente. Una vez de regreso en el Administrador de reglas de formato condicional, haga clic en Aplicar. Los valores de las celdas D10 y D11 tienen el formato para parecerse al resultado de las celdas C10 y C11.

Es importante tener en cuenta que los valores en D10 y D11 siguen siendo 30.000.000 y 100.000. El formato de número aplicado simplemente formatea el número para que aparezca como lo hemos solicitado. Cualquier cálculo realizado en estas celdas se tratará como su valor original.

---