lunes, 4 de mayo de 2009

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:


13 comentarios:

  1. Estimado, he buscado este desarrollo por todas partes y te agradezco tu disposicion en haber publicado esto.
    Sin duda esto no cambia la humanidad pero la humanidad estaria incompleta sin esto.

    Slds

    ResponderEliminar
  2. Me alegro que te haya resultado útil.
    Saludos

    ResponderEliminar
  3. muy clara la informacion, me ha servido muchisimo y creo que con personas como usted que les gusta compartir los conocimientos es evidente que podemos aportar al desarrollo personal y social

    ResponderEliminar
  4. Buenas tardes, me llamo Diana necesito de tu colaboracion necesito sacar un cuadro con las fechas de vencimiento, solo quiero que me de aviso. Gracias

    ResponderEliminar
    Respuestas
    1. Hola Johanna. Por favor mándame un archivo con un ejemplo de lo que quieres hacer y te respondo. Gracias

      Eliminar
  5. el archivo solo lo puedo descargar pagando, ¿es asi?. Alguien si es tan amable de enviarlo a mi correo "gloton at g m ai l dot com"

    ResponderEliminar
    Respuestas
    1. No hay que pagar nada. Sólo tienes que darte de alta en Scribd y ya está. Un saludo

      Eliminar
  6. Buenas tardes,
    He realizado una tabla siguiendo todos sus pasos y todo bien hasta que ha llegado meter el color.
    Selecciono A6:G18 y al entrar en formato condicional no veo ninguna casilla de "valor de celda" que pueda modificar en "fórmula".
    Si pudiese ayudarme se lo agradecería porque esta tabla es lo que yo necesitaba para un trabajo que me han encargado.
    Muchas gracias.

    ResponderEliminar
    Respuestas
    1. Hola Silvia. Tienes que entrar en Formato Condicional y seleccionar la antepenúltima opción: Nueva regla. En la ventana que se te abrirá el primer apartado, Seleccionar un tipo de regla, debes seleccionar la opción: "Utilice una fórmula que determine las celdas para aplicar formato". La fórmula en cuestión, debes escribirla dentro del recuadro: Dar formato a los valores donde esta fórmula sea verdadera.
      Un saludo

      Eliminar
  7. Hola,

    estoy intentado descargar el cuadro de vencimiento con scribd estando registrada, pero me dice que tengo que pagar cuota. Me lo podrías enviar por mail.

    gracias

    ResponderEliminar
    Respuestas
    1. Que raro... Por supuesto que te lo puedo mandar. Dime tu mail, por favor

      Eliminar
  8. hola buen día,

    Vi tu planilla y se me viene a la mente una planilla que necesito confecciónar en base a información de horas, te explico yo trabajo con Equipos Mineros ej: Camiones, Grúas, grupos electrógenos, la información que necesito administrar es la mantención de dichos equipos, a estos se le realizan mantenciones cada 250 horas (según horómetro), necesito que me informe 50 horas antes de la mantención para poder programar EJECUTAR, luego que se efectúe cambiar estatus a EJECUTADA... luego de cambiar ejecutada al seguir con las horas hasta la proxima mantención VIGENTE, en caso que no se cumpla con la mantención no se cambie la informacion en la planilla el status sea VENCIDA. crees que sea posible ??? por lo que veo tendría que reemplazar en mi caso las columnas de las fechas por horas ? y tiempo límite debería tener uno por 200 para EJECUTAR ??

    ResponderEliminar
  9. Te recomiendo el siguiente artículo http://www.excelavanzado.com/2012/10/sumar-horas-en-excel.html
    del blog de Fernando Aparicio para que sepas cómo tratar las horas y minutos con excel. Un saludo

    ResponderEliminar