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

Autofiltro - comandos personalizados

Si utilizan con cierta frecuencia Autofiltro para analizar datos de una tabla en Excel, seguramente sabrán apreciar esta sugerencia.
Para activar Autofiltro lo que hacemos normalmente es abrir el menú Datos-Filtro-Autofiltro



Si quisiéramos filtrar la lista para ver sólo los clientes de la Argentina, pulsamos la fecha del campo (Country, en nuestro ejemplo) y marcamos "Argentina"



Contemos la cantidad de operaciones que hemos efectuado:
1 - Clic al menú Datos
2 - Clic al menú Filtro
3 - Clic al menú Autofiltro
4 - Clic a la flecha del filtro en el campo Country
5 - Clic a la opción "Argentina".

En total cinco operaciones. Todo esto se puede hacer en un solo clic, si instalamos previamente el comando Autofiltro en alguna de la barras de herramientas.

Para instalar el comando, abrimos el menú Herramientas-Personalizar (o clic con el botón derecho del Mouse apuntando al área de las barras de herramientas)



En la pestaña Categorías del diálogo Personalizar, elegimos la categoría Datos; en la ventanilla Comandos seleccionamos Autofiltro



y lo arrastramos a alguna de las barras de herramientas



Una vez instalado, el comando quedará a nuestra disposición cada vez que usemos Excel.

Volviendo a nuestro ejemplo, en el cual hemos quitado el Autofiltro, seleccionamos la celda A13 que contiene el valor "Argentina" (el criterio con el cual queremos filtrar la lista).



Todo lo que tenemos que hacer es pulsar una vez el icono de Autofiltro. Excel activa
el autofiltro y realiza el filtrado de acuerdo al valor de la celda activa



Lo que antes hicimos con cinco operaciones. Hemos hecho ahora con un único clic.

Como podrán notar, he agregado otro comando muy útil, "Mostrar todo". Un clic a este comando elimina todos los filtros de la lista.

Technorati Tags: MS Excel

Consolidación de datos de distintos cuadernos.

En el pasado hemos mostrado cómo consolidar datos de distintas hojas de un cuaderno usando Datos-Consolidar, Tablas Dinámicas, y MS Query.
Naturalmente se presentan situaciones en las cuales queremos consolidar datos que se encuentran en hojas de distintos cuadernos.
En esta nota veremos algunas de las técnicas posibles. Supongamos estos dos cuadernos con datos de ventas del primer trimestre de una cadena de tiendas, un cuaderno para el año 2007 (2007.xls) y el otro para el 2008 (2008.xls).

Técnica #1 - transferir los datos a un único cuaderno.
La ventaja de esta técnica es que nos permite usar con facilidad Datos-Consolidar o Tablas Dinámicas. Podemos, por ejemplo, crear un nuevo cuaderno y transferir a él los datos en hojas separadas o en una única hoja. Por ejemplo, creamos el cuaderno "Consolidado" y pasamos a él las hojas de los cuadernos 2007 y 2008




Cambiamos el nombre de las hojas transferidas a "2007" y "2008"



Todo lo que nos queda por hacer es crear una hoja consolidada con Datos-Consolidar



Alternativamente podemos usar tablas dinámicas con rangos de consolidación múltiples



Técnica #2 - Datos en cuadernos separados
Usando Datos-Consolidar.
Como en el caso anterior, empezamos creando un cuaderno donde consolidaremos los datos (Consolidado.xls).
Una vez creado el cuaderno abrimos el menú datos consolidar y creamos la referencia al rango relevante en el cuaderno 2007.xls



Apretamos "agregar". Ahora veremos que Excel no nos permite crear una referencia al rango relevante en el cuaderno 2008.xls. Lo que haremos será crear la referencia manualmente



Sencillamente tecleamos la referencia manualmente, cambiando 2007 por 2008 y extendiendo el rango a D8.

Pulsamos "Aceptar" y Excel consolidará los datos.



Usando Tablas dinámicas.
También aquí usamos rangos de consolidación múltiples, creando referencias a las hojas relevantes de los distintos cuadernos. A diferencia de Datos-Consolidar, creamos las referencias directamente apuntando a los rangos en las hojas de los distintos cuadernos



El resultado es

Technorati Tags: MS Excel