Calculando el día del año con Excel

Dada una fecha Excel cuenta con funciones para calcular el día del mes, la semana del año, el mes o el año de la fecha. Por ejemplo si queremos saber en qué semana del año cae la fecha 25 de marzo del 2015, usamos la función NUM.DE.SEMANA

Excel no tiene una función para calcular el día del año de una determinada fecha (un valor entre 1 y 365 o 366, si el año es bisiesto). Por ejemplo le 25/03/2015 es el día número 84 del año.
Para calcular el número de día del año de una fecha podemos usar esta fórmula:

=B3-FECHA(AÑO(B3),1,0)

Para calcular cuántos días faltan hasta el fin del año, modificamos la fórmula de esta manera:

=FECHA(AÑO(B3),12,31)-B3

Resolver sistemas de ecuaciones lineales con Excel

Estábamos tomando un café a la mañana cuando Eduardo (si, el compañero de trabajo que siempre me viene a consultar sobre temas de Excel) me pregunta:

- Che, ¿te acordás algo del tema de sistemas de ecuaciones lineales?
- No mucho, ¿por qué?
- Porque mi hijo está estudiando el tema en el colegio y cada tanto me pide ayuda; y ¿qué querés que te diga?, no me acuerdo nada.
- Bueno, fijate en la Internet, seguro que hay cantidades industriales de sitios que tratan el tema como Wikipedia.
- Si, ya me fijé. Pero no tengo paciencia para ponerme a aprender todo de nuevo. Todo lo que necesito es ayudarlo a resolver las ecuaciones.
- Ah! bueno...Podés usar Excel para resolver sistemas de ecuaciones lineales.
- ¿Usar Excel para resolver sistemas de ecuaciones lineales? ¿Cómo?
- Te explico

Hay tres métodos para resolver sistemas de ecuaciones lineales:

• algebraicos
• gráficos
• matriciales

Con Excel podemos resolver sistemas de ecuaciones lineales usando el método matricial. Para ésto Excel nos ofrece dos funciones MMULT que nos permite multiplicar matrices y MINVERSA que calcula la inversa de una matriz.

Para resover de sistemas de ecuaciones lineales con el método matricial se pueden emplean dos procedimientos: el de la matriz inversa y el método de eliminación gaussiana. En esta nota vamos a mostrar como hacerlo con el método de la matriz inversa (sin detenernos en la consideraciones teóricas que dejamos libradas a la iniciativa del lector).

Supongamos que tenemos este sistema de ecuaciones lineales

5x+1y+8z=46

4x-2y=12

6x+7y+4z=50

Queremos encontrar los valores de las incógnitas "x", "y" y "z". El primer paso es crear en Excel una matriz con los valores de los coeficientes de las ecuaciones (el rango B2:D4) y una con los términos independientes (el rango F2:F4)

Para encontrar la solución del sistema, si existe, calculamos primero la inversa de la matriz B2:D4 y luego multiplicamos ésta por la matriz F2:F4.
Para obtener la matriz inversa usamos la función MINVERSA. Esta función, como otras funciones matriciales, dan resultados en un rango de celdas por lo que debemos primero seleccionar el rango donde obtendremos los resultados y luego introducir la función apretando simultáneamente las teclas Ctrl-Mayúsculas-Enter

El siguiente paso es multiplicar la matriz inversa (el rango B7:D9 en nuestro ejemplo) por la matriz F2:F4. Para realizar la multiplicacion de matrices usamos la función MMULT. Recordemos que para multiplicar dos matrices debe cumplirse la condición que el número de columnas de la primer matriz debe ser  igual al número de filas de la segunda. La dimensión de la matriz resultante es el número de filas de la primera (3) por el número de columnas de la segunda (1). Es decir, antes de introducir la función MMULT debemos seleccionar un rango de tres filas por una columna e introducir la función con Ctrl-Mayúsculas-Enter

Los valores de la matriz F7:F9 son las soluciones del sistema de ecuaciones,  x = 4; y = 2; z = 3.

Podemos solucionar el sistema en un único paso anidando la función MINVERSA dentro de la fórmula con MMULT

Diagrama Gantt con tablas dinámicas - versión mejorada

En la nota anterior sobre el tema, mostré las ventajas de crear un diagrama Gantt usando tablas dinámicas. Lo que falta en ese modelo es la posibilidad de mostrar gráficamente el avance del proyecto. La idea es mostrar con colores distintos la duración de la tarea y la parte de la tarea realizada.
Por ejemplo en esta situación

si se ha completado el 50% de la Actividad 1 de la Etapa 1, queremos que se vea así

La porción de color verde de la barra representa la parte de la tarea que ya ha sido completada.

Para poder lograr este efecto vamos a tener que agregar algunos campos en la base de datos de nuestro modelo.
En el modelo anterior la tabla tenía cinco campos (columnas): Etapa, Actividad, Principio, Fin y Duración.

La Duración es el resultado de restar el Fin del Principio.

En nuestro nuevo modelo vamos a agregar tres nuevos campos: % Completado, Completado y A Completar.

El campo "% Completado" es la estimación, en porcentaje, del avance de la tarea; el campo completado se calcula multiplicando "Duración" por "% Completado"

y "A Completar" es la diferencia entre "Duración" y "Completado"

A partir de esta tabla creamos esta tabla dinámica

Para sumarizar los valores (Principio, Completado y A Completar) podemos usar cuañquiera de las funciones Suma, Max o Min, ya que cada valor es único.
Sobre este reporte dinámico creamos un gráfico dinámico de barras apiladas, tal como hicimos en la nota anterior. El primer resultado es éste

Como en el ejemplo de la nota anterior vamos a ocultar los botones, volver "invisible" la serie Principio, corregir la escala del eje de las Y (las fechas) y ubicar la leyenda al pie del gráfico. Una vez hechas todas las correcciones obtenemos este gráfico

Este video muestra el proceso



El archivo con el ejemplo se puede descargar aquí.