Reglas de formato condicional personalizadas

Hoy veremos el uso de formato condicional con fórmulas, validación de datos y fórmulas de comparación. ¿Crees que estás preparado para ello? Hagamos esto.

Digamos que tenemos una hoja de trabajo que calcula el pago de un préstamo. Podemos utilizar la función PMT para realizar fácilmente esta tarea. La función PMT requiere tres argumentos para calcular el monto del pago. La función requiere el monto del préstamo, la tasa de interés y el plazo. Lo que pasa con la función de pago es que la unidad de tiempo debe ser consistente. Es decir, si desea calcular el pago mensual, entonces la tasa de interés debe expresarse como una tasa de interés mensual y el plazo debe expresarse como el número de meses. Si desea el monto del pago anual, debe expresar la tasa de interés como una tasa anual y el plazo como el número de años.

Si queremos que la hoja de trabajo sea fácil para nuestro usuario, entonces queremos permitirle elegir entre meses y años. Configuramos una celda de entrada del Período de pago y le proporcionamos al usuario un menú desplegable dentro de la celda para elegir Mensual o Anual. (Se crea fácilmente un menú desplegable dentro de la celda con la función Validación de datos; Datos Validación de datos).

Normalmente, las tasas de interés se expresan como tasas anuales, por lo que le pediremos al usuario que ingrese la tasa de interés anual y luego la convierta a una tasa mensual si es necesario. En lo que respecta al término, podríamos configurar una celda de entrada para permitir al usuario ingresar el término, y eso estaría bien. Sin embargo, preferimos configurar dos celdas de entrada para que, si el usuario desea realizar un pago mensual, pueda ingresar el número de meses en la celda de entrada Plazo (meses). Si el usuario desea realizar un pago anual, puede ingresar el número de años en el cuadro de entrada Plazo (años). Esta idea se ilustra en la captura de pantalla siguiente.

Lo que pasa con nuestra área de configuración es que puede resultar confuso para el usuario, ya que es posible que no esté seguro de si se requiere una entrada en ambas celdas de entrada de Término.

La fórmula que calcula el monto del pago usa el valor del Plazo (meses) si el usuario elige Mensual y usa el valor del Plazo (años) si el usuario elige Anual. Debemos dejar claro al usuario qué celdas de entrada utiliza actualmente la fórmula. La fórmula de pago utiliza una función SI para realizar diferentes cálculos según el período de pago seleccionado. Si el usuario selecciona un Período de pago mensual, entonces la fórmula utiliza el valor en la celda de entrada C10 del Plazo (meses). Si el usuario selecciona un Período de pago anual, entonces la fórmula utiliza el valor en la celda de entrada C11 del Plazo (años).

La fórmula de pago es la siguiente:

=-IF(C9=”Anual”,PMT(C8, C11 , C7),PMT(C8/12, C10 , C7))

Si el Período de pago es “Anual”, entonces se usa una función PMT que usa la celda de entrada del término C11 ; de lo contrario, se usa una función PMT que usa la celda de entrada del término C10 .

Esto nos lleva a la razón por la que estamos aquí. Podemos usar una regla de formato condicional simple que deja claro al usuario en qué celda de entrada de término ingresar un valor y qué celdas de entrada están siendo evaluadas por la fórmula.

Básicamente, si el usuario elige un Período de pago anual, entonces queremos que ingrese un valor en la celda de entrada Plazo (años). Por lo tanto, atenuaremos la celda de entrada Término (meses) con formato gris. Del mismo modo, si el usuario elige un Período de pago mensual, queremos que el usuario ingrese un valor en la celda de entrada Plazo (meses). Por lo tanto, atenuaremos la celda de entrada Término (años) con formato gris.

En la celda de entrada Plazo (meses), queremos establecer el formato en gris si el Período de pago es igual a Anual. Entonces, seleccionamos la celda de entrada Término (meses) y aplicamos la siguiente regla de formato condicional dirigiéndonos al comando de cinta Formato condicional Nueva regla:

Primero, observe que seleccionamos “Usar una fórmula para determinar qué celdas formatear”. Esta configuración nos permite ingresar cualquier fórmula o función que devuelva un valor VERDADERO o FALSO. Si devuelve VERDADERO, entonces se aplica el formato. En nuestro caso, simplemente usamos una fórmula de comparación simple. =$C$9=”Anual” que devuelve VERDADERO si el valor en C9 es igual a “Anual”. Si el usuario selecciona un Período de pago anual, la celda de entrada Plazo (meses) aparece atenuada en gris. Puede elegir cualquier formato que desee haciendo clic en el botón Formato. Elegí una fuente gris con un relleno de celda gris claro.

Los resultados se muestran a continuación.

Aplicamos una regla de formato condicional similar al cuadro de entrada Plazo (años), que se muestra a continuación:

Y los resultados se muestran a continuación:

Esta simple idea de usar formato condicional para dejarle claro al usuario qué celdas de entrada están activas y no activas se puede usar en una amplia variedad de situaciones.

Si desea descargar el archivo de trabajo, utilice el siguiente enlace.

Formato