Calcular semestres o trimestres en Excel.

Excel cuenta con varias funciones que nos permiten calcular, por ejemplo, el día, el mes o el año dada una determinada fecha.
Por ejemplo, si en la celda A1 tenemos la fecha 27/08/2008, la fórmula =DIA(A1) da como resultado 27; la fórmula =MES(A1) da como resultado 8 y la fórmula =AÑO(A1) dará como resultado 2008.
Excel no tiene funciones nativas para calcular otros intervalos como el semestre del año o el trimestre del año.
Para calcular el trimestre del año de una determinada fechas podemos usar una de estas fórmulas

=REDONDEAR.MAS(MES(A1)/3,0)

=COINCIDIR(MES(A1),{1;4;7;10})

En la primer fórmula obtenemos primero el número de mes con la función MES, luego lo dividimos por 3, el número de meses que hay en cada trimestre y finalmente redondeamos el resultado hacia arriba para obtener el número de trimestre.
En nuestro ejemplo,

=MES(A1) da 8

8/3= 2.666667

=REDONDEAR.MAS(2.666667) = 3

La segunda fórmula usa una constante matricial como matriz de búsqueda en la función COINCIDIR. Siguiendo con nuestro ejemplo, =MES(A1) da 8, que coincide con el tercer elemento de la constante matricial.

Si queremos calcular el semestre del año a partir de la fecha, podemos usar una de estas fórmulas

=REDONDEAR.MAS(MES(A2)/6,0)

=COINCIDIR(MES(A2),{1;7})

Y cómo calculamos la semana del año a partir de la fecha? Bien, Chip Pearson propone esta fórmula


=TRUNCAR(((A1-FECHA(AÑO(A1),1,1))/7))+1+SI(DIASEM(FECHA(AÑO(A1),1,1))>DIASEM(A1),1,0)

Technorati Tags: MS Excel

Crear vínculos con Pegado Especial

Ya hemos mostrado algunos de los usos de Pegado Especial en Excel. Una de las posibilidades es la de crear vínculos a la celda o al rango que estamos copiando. Esto la hacemos usando la opción Pegar Vínculos



Al copiar el rango A1:A5 usando esta opción vemos el valor de la celda A1 en la celda B4, pero en la barra de fórmulas vemos que Excel a creado una referencia a la celda de origen



También podemos usar esta opción para crear referencias al rango en otra hoja



Y también crear vínculos a cuadernos remotos



Excel tiene un comportamiento un tanto curioso con esta opción. Al copiar rangos usando Pegar Vínculos, el vínculo es creado como referencia relativa (sin los símbolos $ en la referencia). En cambio se usamos Pegar Vínculos para copiar una única celda, Excel creará una referencia absoluta (es decir $A$1).

Otra forma de copiar creando vínculos es arrastrando el rango a copiar presionando al mismo tiempo la tecla Mayúsculas (o Ctrl o Alt). Al arrastrar el rango a copiar presionamos el botón derecho del ratón



Al igual que con el menú de Pegado Especial, si copiamos una única celda se creará una referencia absoluta. Si lo hacemos con un rango de celdas, la referencia será relativa.

Technorati Tags: MS Excel

Calcular fracciones de año

Ya hace varios días que no sólo no publico notas en este blog, sino que tampoco estoy respondiendo a los muchos mails que recibo con todo tipo de consultas.
Así que a modo de disculpa hasta que encuentre el tiempo de ir respondiendo a las consultas que han llegado, va aquí una sugerencia sobre como calcular fracciones de año.
Excel dispone de muchas funciones que permiten hacer cálculos con tiempo. Una lista de estas funciones se puede ver abriendo al asistente de funciones y seleccionando la categoría Fecha y hora



Sin embargo, hay otras funciones para este tipo de cálculos que están a nuestra disposición sólo si hemos activado el Analysis ToolPak.
Una de estas funciones es FRAC.AÑO (YEARFRAC en la versión inglesa).
Esta función calcula la fracción de año que representa el número de días enteros entre la fecha inicial, el primer argumento de la función, y la fecha final, el segundo argumento.
Existe un tercer argumento, base. Este argumento determina sobre que base se efectuará el cálculo.
Supongamos que en la celda A1 tenemos el valor de fecha 01/01/2008 y en la celda A2 01/07/2008. La fracción de días transcurridos es obviamente 1/2 (o 0.5). Sin embargo veremos que dependiendo de la base escogida obtendremos distintos resultados:

=FRAC.AÑO(A1,A2) = 0.5

En esta fórmula hemos omitido el argumento "base", por lo que Excel supone que la base es 30/360.

=FRAC.AÑO(A1,A2,1) = 0.4972677

Usar uno como base es interpretado con "real/real", es decir, el número real de días transcurridos dividido por el número real de días del año en cuestión. En nuestro ejemplo 182/366 (2008 es bisiesto!).

=FRAC.AÑO(A1,A2,2) = 0.505555

La base 2 es "real/360", es decir, la cantidad de días realmente transcurridos (182) dividido por un año estándar de 360 días (cosa de gringos!).

=FRAC.AÑO(A1,A2,3) = 0.4986301

La base es como la anterior pero dividida por un año de 365 días (a pesar que 2008 tiene 366, como ya hemos señalado).

=FRAC.AÑO(A1,A2,4) = 0.5

La base 4 calcula de acuerdo a 30/360 (europea), es decir meses de 30 días y año de 360.

Technorati Tags: MS Excel