lunes, 4 de octubre de 2010

Cálculo de antiguiedad de deuda con INDICE y COINCIDIR

En una nota anterior expliqué como utilizar las funciones INDICE y COINCIDIR (INDEX y MATCH) para buscar valores, en la nota de hoy explicaré como utilizar ambas funciones para construir un cálculo de antigüedad de deuda.

Tenemos un listado de facturas con sus fechas de vencimiento y los importes.


Cálculamos los días desde el vencimiento utilizando una fecha de referencia ubicada en la celda J7, también se puede utilizar la fecha actual para que se actualice automáticamente. Para realizar el cálculo pero tomando días laborales debemos utilizar la fórmula DIAS.LAB.

Una forma de resolverlo es con SI anidados utilizando la siguiente fórmula =SI(D2<30,"0-30",SI(D2<60,"30-60",SI(D2<90,"60-90","+90"))). El problema que presenta esta solución es que se puede volver muy grande si tenemos varios rangos y es dificil de ingresar y mantener. Adicionalmente si queremos cambiar algún rango o agregar nuevos tenemos que modificar todas las fórmulas.

Definimos dos rangos con nombres que son RangoValores y RangoNombre, estos definene los distintos rangos sobre los cuales calcularemos la antigüedad de deuda. En el RangoValores tenemos que escribir el límite superior de cada rango y en RangoNombre ponemos la descripción que identifica a cada rango.

La fórmula que utilizamos es INDICE(RangoNombres,COINCIDIR(D2,RangosValores,1)). La función COINCIDIR toma el valor búscado, en nuestro ejemplo "D2" y lo busca en RangoValores devolviendo la posición dentro de RangoValores. El último parámetro de la fórmula COINCIDIR nos permite definir si buscamos un valor exacto en ese caso definimos el parámetro con el valor 0 o bien buscamos el valor mas grande en nuestro RangoValores que sea menor al valor búscado.


Si necesitamos modificar los rangos o agregar nuevos simplemente tenemos que modificar los dos nombres que definimos anteriormente y nuestra fórmula funcionará sin tener que ajustarla.

Para acceder el archivo de ejemplo hacer clic aquí.