skip to main |
skip to sidebar
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í.
Luego de los valores numéricos las fechas deben ser el siguiente tipo de dato más común en Excel y por ello hoy explicaré como realizar algunos cálculos con fechas. Entre los ejemplos utilizaremos dos funciones muy útiles llamadas DIA.LAB y DIAS.LAB que nos permitirán hacer cálculos con fechas pero tomando solo días laborales.
Entre los cálculos de fechas más comunes encontramos el cálculo de una fecha a x días del día actual. Otro cálculo común es el número de días que hay entre dos fechas.
Veamos unos ejemplos
Para obtener la fecha que se encuentra a 30 días a partir de hoy utilizamos =AHORA() + 30 o para saber que fecha corresponde a 7 días antes de hoy utilizamos =AHORA() - 7.
Si tenemos dos fechas y se encuentran en las celdas A1 y A2 entonces podemos ingresar la fórmula =A1-A2 que nos calculará la cantidad de días.
Estos cálculos los podemos realizar de esta forma ya que Excel guarda internamente las fechas como valores numéricos que representan el número de días transcurridos desde el 01/01/1900. La parte decimal se utiliza para determinar la cantidad de segundos, minutos y horas desde el comienzo del día. Pueden probarlo usando la función AHORA y luego cambiando el formato de la celda a numérico.
En muchos casos no necesitamos hacer estos cálculos con días corridos sino con días laborales y para ello Excel tiene dos funciones DIAS.LAB y DIA.LAB
Primero analizaremos la función DIA.LAB que nos permite calcular una fecha que se encuentra a x días laborales de una fecha inicial. Esta función toma tres parámetros, el primero es la fecha inicial sobre la cual haremos el cálculo. El segundo es la cantidad de días positivo para una fecha futura o negativo para una pasada. El tercer parámetro es un array con fechas que son feriados los mismos no se consideran días laborales.
La segunda función DIAS.LAB nos permite contar la cantidad de días laborales que hay entre dos fechas. Esta función toma tres parámetros, el primero es la fecha inicial, el segundo es la fecha final y el último es el array con fechas que son feriados y no serán considerados como días laborales.
Veamos unos ejemplos
En la siguiente imagen mostramos los cálculos de fechas utilizando los cálculos con días corridos y con días laborales.
Para calcular los días laborares entre dos fechas usamos =DIAS.LAB(B1;B2;Feriados) y para calcular la fecha que se encuentra a 60 días laborales de la fecha de inicio usamos =DIA.LAB(B1;60;Feriados)
Feriados es una matriz o array con valores constantes que contiene los días que no consideramos como laborales. En nuestro ejemplo el día 9 de Julio es considerado feriado. Para más ayuda sobre arrays haga clic aquí.
En la nota anterior expliqué como crear un gráfico de cascada, ahora realizaremos algunos cambios que mejoraran ese gráfico. Si bien estos cambios también pueden lograrse mediante macros, en este artículo mostraré alternativas sin usar macros ya que en ciertos casos es necesario generar un archivo sin incluir macros.
Como en el ejemplo anterior utilizamos la siguiente tabla para armar nuestro grafico y le asignamos el nombre TablaChart.
Veamos los cambios
El primer cambio nos permitirá asignarle distintos colores a las variaciones, en el caso que sean positivas o negativas. Para lograr esto separamos los valores positivos y negativos en dos columnas que serán incluidas como dos series distintas de datos y por lo tanto podremos asignarle distintos colores.
La columna que tiene los valores positivos contiene la siguiente fórmula =SI(B2>0;B2;0). En el caso de ser un valor negativo entonces mostramos un cero y por lo tanto esto no queda visible en el gráfico. A esta columna la llamaremos Series2Mas.
Por otra parte la columna que tiene los valores negativos contiene la siguiente fórmula =SI(B2<0;ABS(B2);0). Similar al caso anterior, si la variación es positiva se mostrará cero y sino el valor absoluto. A esta columna la llamaremos Series2Menos.
El otro cambio es la utilización de las tablas de Excel 2007 para que podamos ingresar nuevas filas según la necesidad y el gráfico se actualice automáticamente. Para lograr que funcione correctamente la tabla y se actualicen las fórmulas debemos utilizar una misma fórmula para cada fila y que ésta haga referencia a la misma fila.
En el ejemplo anterior las fórmulas de la columna Series1 eran diferentes para la primera y última fila ya que siempre llevan el valor cero. Lo primero que debemos lograr es una fórmula que devuelva 0 solo para la primera y última fila de la tabla. Para saber si estoy en la primera fila utilizamos FILA(TablaChart) y si es igual a FILA(), que devuelve la fila actual, entonces estamos en la primer fila. Para calcular la última fila FILA(TablaChart)+FILAS(TablaChart)-1, de la misma forma chequeamos si esta es igual a FILA(). La fórmula completa sería =SI(O(FILA(TablaChart)=FILA();FILA(TablaChart)+FILAS(TablaChart)-1 = FILA());0;B2).
Finalmente la fórmula utilizada para acumular los valores, en el ejemplo anterior llamada Ultimo tope, tampoco funciona correctamente al insertar nuevas filas en una tabla y por ello lo reemplazamos por =SUMA($B$2:B2), la referencia absoluta fija la primer fila mientras que la segunda se incrementa a medida que se incrementan las filas en la tabla.
Si integramos la fórmula para acumular con la explicada en el paso anterior obtenemos la siguiente fórmula =SI(O(FILA(TablaChart)=FILA();FILA(TablaChart)+FILAS(TablaChart)-1 = FILA());0; SI(B2<0;SUMA($B$2:B2);SUMA(B$2:B2)-B2))
Para acceder al archivo del ejemplo haga clic aquí.
que figuran en la página.
