Calcular días por años entre dos fechas

Supongamos una lista de fechas de inicio y finalización de tareas (o contratos o cualquier otra actividad) que se extienden a más de una año, como en este ejemplo:

Podemos ver, por ejemplo, que la Tarea 1 empieza en el 2013 y termina en el 2015. ¿Cómo hacemos para calcular cuántos días caen en cada año?

Para resolver este problema vamos a usar una técnica no tan conocida: crear dentro de la fórmula un vector implícito (o "virtual"). El concepto quedará más claro cuando expliquemos la fórmula.

La columna "Total de días" muestra el total entre ambas fechas (incluida el día del comienzo) usando la fórmula =SIFECHA(C4,D4,"d")+1

Para poder calcular cuantos días caen dentro de cada año (2013,2014,2015), vamos a agregar tres columnas en nuestra tablam una para cada año, y en la celda F4 ponemos esta fórmula (que luego copiamos al resto de la tabla):

=SUMAPRODUCTO(--(AÑO($C4+FILA(INDIRECTO("1:"&($D4-$C4+1))))=F$3))

Vamos a "disecar" la fórmula de adentro hacia afuera:

• La expresión "($D4-$C4+1)" calcula el total de días transcurridos entre el principio y el fin de la tarea. Es equivalente al cálculo en la celda E4.
• La expresión INDIRECTO("1:"&($D4-$C4+1)) crea una referencia al rango "1:750". Al usar esta expresión como argumento de la función FILA en forma matricial (al estar dentro de la función SUMAPRODUCTO), obtenemos un vector con los valores 1,2,3,...,750
• Al sumar a $C4 este vector en ($C4+FILA(INDIRECTO("1:"&($D4-$C4+1))), obtenemos un vector de fechas día por día que comienza el 06/07/2013 y termina con el 25/07/2015
• La función AÑO extrae de cada fecha el año correspondiente, por lo que obtenemos un vector de años con 750 puntos.
• Este vector los comparamos con el año que aparece en la celda F$3, por lo que obtenemos un vector de valores VERDADERO o FALSO (si se cumple o no la condición).
• La doble negación a la izquierda de la función AÑO convierta los valores VERDADERO en 1 y los FALSO en 0. Igualmente podríamos haber multiplicado la expresión por 1.
• Finalmente SUMAPRODUCTO suma todos los valores del vector que cumplen con la condición. Recordemos, como puse más arriba, que SUMAPRODUCTO se comporta en forma matricial.

Podemos visualizar el proceso del cálculo usando la herramienta "Evaluar fórmula" en "Fórmulas-Auditoría de fórmulas"

En este video comento el proceso

Gráficos Excel - Fijar máximos del eje vertical con macro

En la nota anterior sobre el tema mencionamos que la solución tradicional para coordinar el máximo del eje de las Y (eje de los valores en términos de Excel) es usar una macro.
El método manual, con fórmulas y series auxiliares, tiene la ventaja que puede ser usado también en entornos donde las macros están bloqueadas, por ejemplo,  por el administrador de la red. Las deventajas son menores pero existen:

• el valor máximo es determinado por el algoritmo de Excel, por lo que siempre será cercano al valor deseado pero no siempre exactamente igual;
• hay que modificar cada uno de las gráficos que queremos coordinar.

Las macros son más flexibles y eficientes, ya que podemos establecer exactamente el valor que queremos que aparezca como máximo y podemos aplicarlas a todos los gráficos de la hoja.

El código es muy sencillo y podemos usarlo de dos maneras distintas: como sub (macro común) o como evento. Al usarlo como sub, tenemos que crear también la forma de disparar el código, por ejemplo, ligando la macro a un botón; al usar eventos podemos hacer que la macro corra cuando, por ejemplo, el usuario cambia algún dato en la tabla de los datos.

Como en el ejemplo de la nota anterior, tenemos dos gráficos basados en dos tablas de datos y en la celda A3 calculamos el valor máximo de ambas tablas (hemos creado el nombre "cellValMax" que se refiere a la celda A3)

Como podemos ver en la imagen, la escala del eje de las Y es diferente en cada gráfico y por lo tanto la comparación visual es engañosa.
Como mencionamos más arriba podemos usar el código como sub (macro) o como evento; el código es el mismo pero la forma de hacerlo correr es distinta.

Uso del código como sub (macro)

En un módulo compun del editor de Vb, ponemos este código

Sub coordinar_max_graficos()
   Dim dbMax As Double
   Dim objCht As ChartObject

   dbMax = Range("cellValMax").Value
  
   For Each objCht In ActiveSheet.ChartObjects
      With objCht.Chart.Axes(xlValue)
        .MaximumScale = dbMax
        .MinimumScale = 0
      End With
   Next objCht
End Sub

En la hoja que contiene los gráficos agregamos un botón ligado a la macro




Uso del código como evento

Para usarlo como evento el código debe ir en un módulo de la hoja que contiene los gráficos. Una de las formas de acceder a este módulo es apunta con el mouse a la pestaña con el nombre de la hoja y activar la opción Ver código

En nuestro caso vamos a crear un evento de manera que cuando el usuario haga algún cambio en la hoja, se dispare el código. Para esto usamos un evento del tipo Change

Como puede apreciarse, el código es el mismo que en la rutina sub.

Coordinar el valor máximo del eje vertical en varios gráficos de Excel

Una participante en el foro Expertos en Excel en Español consultaba si se puede condicionar el valor máximo de un grupo de gráficos de manera que viniese determinado por el gráfico que tenga la cifra mayor.

Para ejemplificar, digamos que tenemos dos gráficos que muestran dos series de valores (val1 y val2 en la imagen)

Sin entrar en la la cuestión si poner un gráfico al lado del otro o crear un único gráfico con dos series de datos, podemos ver que en nuestro ejemplo los máximos del eje de los valores (el eje Y) son distintos en cada gráfico. Esto hace que la comparación visual sea engañosa; lo que buscamos es que en los dos gráficos el eje de los Y tenga el mismo valor máximo.

Como los valores van cambiando no podemos fijar la definición del máximo del eje. La solución tradicional es programar un evento (Vba), de manera que cuando algún valor cambia en alguna de las tablas el evento cambia el valor del máximo en ambos gráficos.

Sin embargo, existe la posibilidad de lograr el mismo efecto sin macros. El truco consiste en incluir en ambos gráficos una serie auxiliar con un sólo punto cuyo valor sea el máximo de los los valores de ambas tablas.
Además de tener el valor máximo de todos los valores de las tablas esta serie debe cumplir con otros dos requisitos:

1. ser "invisible";
2. ser "inmaterial", con lo que quiero decir que no influye en como el resto de los puntos de las series se muestran en los gráficos (este concepto quedará más claro en las subsiguientes explicaciones).

En nuestro ejemplo empezamos por crear la serie auxiliar poniendo esta fórmula en una celda: =MAX(D3:E7,L3:M7)

Como puede verse, ponemos el fórmula en la columna A, dejando la columna B libre. El motivo es poder luego ocultar la columna A para no recargar nuestro modelo mostrando datos auxiliares.

El próximo paso es agregar la serie auxiliar en los gráficos. Seleccionamos el primer gráfico y en el menú Diseño de la opción Herramienta de gráficos activamos "Seleccionar Datos-Agregar". y en el formulario que se abre ponemos estas definiciones

Apretamos "Aceptar" y en formulario que se abre apretamos el botón "Celdas ocultas y vacías"

En el formulario de configuración de celdas ocultas y vacías marcamos la opción "Mostrar datos..."

Ahora tenemos que ocuparnos de los requisitos que debe cumplir la serie auxiliar. Cambiamos el tipo de serie a "Línea" sin marcadores

Esto hace que la la serie "desaparezca" (al tener un solo punto no aparece la línea ni el marcador; tampoco "comparte" lugar en el eje de las X con los otros puntos de la serie).

Repetimos el proceso para el segundo gráfico. Ahora podemos ver que en ambos gráficos el eje de las Y tiene el mismo valor máximo

Cada vez que cambiemos algún valor en alguna de las tablas, Excel calculará el mismo máximo para el eje de las Y en ambos gráficos.

En esta nota pueden ver la solución usando macros.