Comprobando Vencimientos con Funciones Lógicas

Descargar el Archivo

Hoy vamos a resolver un pequeño caso que me habéis mandado a mi mail y que me parece una muy buena oportunidad para practicar las funciones lógicas anidadas SI, Y, O, (además de otras herramientas como la Validación de Datos y el Formato Condicional).

Se trata de un modelo donde el usuario realiza entrada de pedidos en una tabla cubriendo los campos: Nº de Factura; Concepto del Pedido; Importe del Pedido; Fecha del Pedido; Fecha de Vencimiento; y Estado de Cobro. Lo que queremos conseguir es que el modelo nos avise, en base a unas reglas que comentaremos a continuación, de qué actuación debemos seguir. Para no complicar innecesariamente la visualización del modelo trabajaremos sólo con la parte de la tabla que hace referencia a las fechas y al estado de cobro. En las siguientes imágenes puede ver la tabla original y la parte de la tabla en la que nos centraremos:

Como se puede ver en la imagen, lo que vamos a hacer es: por un lado, en la columna G, establecer la acción a seguir en base a las reglas que enseguida comentaré. Por otro lado destacaremos en distintos colores cada operación en función de la actuación a seguir. También añadiremos alguna validación de datos para asegurarnos que la entrada de datos es correcta.

Las reglas que debe contemplar el modelo son las siguientes:

a) El Estado de Cobro debe presentar dos opciones: Pagado o Pendiente. Si la operación está Pagada entonces en la correspondiente celda de la columna Actuación debe aparecer el concepto Terminado. Además toda la fila de la operación se mantendrá sin ningún color de relleno.

b) Si la Fecha de Vencimiento de la operación no supera la fecha de Hoy (fecha del día corriente en el que nos encontremos) entonces debe aparecer en Actuación el concepto No Vencido, y rellenar de color verde toda la fila.

c) Si la Fecha de Vencimiento supera la fecha de Hoy entonces tendremos que comprobar si además la Fecha de Vencimiento respecto a la Fecha de Pedido supera o no el Límite días establecido (en nuestro ejemplo este límite serán 10 días, como se puede ver en la celda B2). En caso de que se superen ambas restricciones, en el apartado Actuaciones deberá aparecer el concepto Reclamar, y la fila deberá rellenarse en rojo. Si no supera el Límite días entonces deberá aparecer el concepto Informar y colorearse la fila en naranja.

Este tipo de planteamiento es muy habitual y, aunque parezca un poco lioso, enseguida veremos que la solución es, como casi siempre, razonablemente sencilla. Veamos cómo resolverlo:

Una aclaración antes de comenzar. En las imágenes mostradas más arriba aparecen ya los datos de varias operaciones introducidos. Sin embargo, los pasos que vamos a describir a continuación parten de la base de que todavía no han sido introducidos dichos datos. 

1. Nos situamos en la celda G3 e introducimos la siguiente fórmula:

=HOY()

Esta función nos devuelve la fecha de hoy y, lógicamente, irá cambiando cada día. Fíjese que no tiene argumentos dentro de los paréntesis.

2. Seleccionamos el rango E6:E18 y vamos al menú Datos/Validación. SeleccionamosPermitir/Fecha. En el cuadro Datos seleccionamos mayor o igual que. En el cuadro Fecha Inicialescribimos =D6. Como teníamos seleccionado el rango E6:E18 cuando aceptemos, excel entenderá que debe validar E6 contra D6; E7 contra D7; etcétera. Con esta operación nos aseguramos de no introducir una fecha de vencimiento inferior a la fecha de pedido.

3. Nos situamos en I7 y escribimos Pagado. Nos situamos en I8 y escribimos Pendiente.

4. Seleccionamos el rango F6:F18 y vamos al menú Datos/Validación. SeleccionamosPermitir/Lista y, en el cuadro origen, seleccionamos el rango I7:I8. De esta manera tendremos una lista desplegable en cada celda del rango F6:F18 para elegir entre pagado o pendiente. 

5. A continuación debemos proceder con la fórmula. Utilizaremos los términos indicados en las reglas (Pagado, Pendiente, Terminado, No Vencido, Reclamar, Informar) para realizar las comprobaciones. Personalmente prefiero escribir estos conceptos en celdas y en las fórmulas hacer referencia a dichas celdas. Para que la comprensión de la fórmula sea más sencilla lo haremos con los conceptos directamente. No situamos en G6 y escribimos:

(Evidentemente la fórmula podemos escribirla en una única linea pero la muestro en este formato para facilitar su lectura)

6. Copiamos la fórmula de G6 en el rango G7:G18.

Vamos a desmenuzar esta fórmula:

=SI(O(D6="";E6="";F6="");""

Lo que estamos haciendo en esta parte es comprobar si se han introducido datos. Si cualquiera de las tres celdas (D6, E6, ó F6) está en blanco (sin datos) entonces le pedimos que deje G6 en blanco también (hasta que se introduzcan los datos correspondientes de la operación). En caso de que las tres celdas estén cubiertas lo siguiente que comprobamos es si F6 contiene el concepto Pagado. En tal caso directamente le indicamos que escriba Terminado:

SI(F6="Pagado";"Terminado";

Si las celdas no están en blanco y F6 no contiene el concepto Pagado, entonces es que la operación está Pendiente. En tal caso debemos realizar una nueva comprobación:

Con esta parte de la fórmula comprobamos si la fecha de HOY ($G$3) es menor que la Fecha de Vencimiento (E6). Si lo es entonces que escriba No Vencido. Si no lo es debemos seguir realizando comprobaciones:

SI(Y($G$3>E6;E6-D6>$E$2);"Reclamar";"Informar"))))

Aquí comprobamos dos condiciones a la vez, a saber: si la Fecha de Vencimiento supera la fecha de Hoy y, además, si la Fecha de Vencimiento menos la Fecha de Pedido supera el Límite días establecido. En tal caso que escriba la palabra Reclamar. Una vez analizadas todas las posibilidades la única opción que queda es la de Informar (que ocurrirá cuando se ha superado la fecha de vencimiento pero no el límite de días). Fíjese que hemos abierto 4 condicionales y por eso cerramos 4 paréntesis al final de la fórmula. 

Ya sólo nos queda solucionar el problema de los colores:

6. Seleccionamos el rango A6:G18 y abrimos el menú Formato/Formato condicional.

7. En el cuadro que presenta valor de la celda lo  cambiamos por Fórmula y en el cuadro de la derecha escribimos la siguiente: =SI($G6="Informar";1;0)    Una vez escrita la fórmula pulsamos el botón Formato y seleccionamos Trama de color naranja. Pulsamos AGREGAR.

8. En la nueva condición volvemos a seleccionar Fórmula y escribimos:  =SI($G6="reclamar";1;0)  y pulsamos el botón Formato y seleccionamos Trama de color rojo. Pulsamos AGREGAR.

9. En la nueva y última condición escribimos como fórmula: =SI($G6="no vencido";1;0)  y pulsamos el botón Formato y seleccionamos Trama de color verde. Pulsamos ACEPTAR.

Nota: La parte de la fórmula: =SI(O(D6="";E6="";F6="");"" puede reducirla con la siguiente expresión =SI(O(D6:F6="");""  como entrada matricial, pero recuerde que entonces al acabar la fórmula NO DEBE PULSAR ENTER sino Ctrl+Shift+Enter. La fórmula quedará como: