Suma condicional de fechas en Excel

Este post viene a colación de una consulta sobre cómo totalizar valores por fechas con más de una condición, más específicamente, entre dos fechas.
Excel pone a nuestra disposición varias herramientas: fórmulas, tablas y tablas dinámicas. Hacer el cálculo con tablas o tablas dinámicas no ofrece mayores inconvenientes. Por ejemplo, si hemos organizado los datos en forma de Tabla, podemos usar el filtro y la opción de Fila de Totales para ver el total de los valores del primer semestre del año

En una tabla dinámica creada con los mismos datos aplicamos también el filtro de fechas

Si queremos o tenemos que usar fórmulas, nos toparemos con dos "bemoles":

1. la forma en que Excel maneja la fechas dentro de las fórmulas (tema que ya tratamos en el pasado remoto de este blog);
2. el uso de operadores del tipo "mayor que", "igual", etc. dentro de fórmulas.

Si queremos hacer con funciones el cálculo mostrado más arriba podemos usar SUMAPRODUCTO o SUMAR.SI.CONJUNTO, ya que tenemos dos condiciones.

Con SUMAPRODUCTO usamos 

=SUMAPRODUCTO((B3:B62>=FECHA(2016,1,1))*(B3:B62<=FECHA(2016,3,31))*C3:C62)

La función FECHA convierte la fecha del criterio, por ejemplo 01/01/2016 en su número de serie (42370) ya que esa es la forma en que Excel maneja las fechas.

Si queremos usar SUMAR.SI.CONJUNTO usamos

=SUMAR.SI.CONJUNTO(C3:C62,B3:B62,">="&FECHA(2016,1,1),B3:B62,"<="&FECHA(2016,3,31))

donde podemos ver que concatenamos el operador ">=" con el numeral de la fecha usando el operador &.

Código para lista de valores del operador IN de SQL en Excel

En un post reciente en PowerPivot(Pro), Matt Allington recomienda adquirir conocimientos básicos de SQL para agilizar y mejorar nuestro trabajo con el PowerPivot. Sin lugar a dudas un buen consejo pero no sólo para los usuarios de PowerPivot sino para todo usuario de Excel (en esta nota he mostrado un uso de SQL con Excel).

En este post no voy a hablar de como usar SQL y supongo que sólo interesará a aquellos lectores que ya hacen algún uso de este lenguaje.

En la nota mencionada Matt publica un código de Vba para crear la lista de valores de un operador IN (devuelve  aquellos registros cuyo campo indicado coincide con alguno de una lista).

Para usar este operador hay que crear una lista de valores, texto o números, separados por comas.

Una de las formas de hacerlo es usando la función CONCATENAR, pero cuando se trata de varios miembros a unir, la tarea se vuelve irritántemente tediosa.

Así que, como Matt, también yo he creado un código que uso en mi trabajo diario y que hasta ahora no había pensado en publicarlo. El código es el siguiente

Sub cadena_for_SQL()
  
    Dim cell As Range, strTmp As String, string_for_SQL As String
    Dim SQLString As DataObject
    Dim Answ
  
    Answ = MsgBox("Yes para texto - No para numeros", vbYesNo, "SQL String")
  
    Set SQLString = New DataObject
  
    For Each cell In Selection
        If Answ = vbYes Then
            strTmp = strTmp & "'" & cell.Value & "',"
        Else
            strTmp = strTmp & cell.Value & ","
        End If
    Next cell
  
    string_for_SQL = "IN " & "(" & Left(strTmp, Len(strTmp) - 1) & ")"
  
    With SQLString
        .SetText string_for_SQL
        .PutInClipboard
    End With
  
End Sub

Empezamos por seleccionar el rango que contiene los valores a integrar a la lista del operador IN y
luego activamos la macro; aparecerá un mensaje que nos pide determinar si los valores deben integrarse como texto o como números

Elegimos la opción deseada y la macro copiará la lista al Clipboard. De esta manera todo lo que nos queda por hacer es pegar la lista en el lugar deseado (por lo general, el editor de SQL).

Este video muestra el uso del código en sus dos versiones (texto y valores numéricos)

Formato condicional y precisión de cálculo de Excel

En el pasado he tocado el tema de la precisión de cálculo en Excel y los problemas que puede generar. Para evitar una ola de pánico entre mis lectores señalemos que en la práctica estos problemas aparecen muy raramente, pero que existen…existen.

El caso que voy a presentar está basado en una consulta de una amiga, investigadora en el área de la biología. Supongamos esta tabla de mediciones de lapsos

Como puede observarse, en la columna “Lapso transcurrido” se ha calculado cuántos minutos transcurrieron entre una observación y la anterior. Queremos controlar que no hayan transcurrido más de 15 minutos entre cada observación, por lo que aplicamos formato condicional con la siguiente fórmula

Al hacerlo surge un problema: a pesar que todos los intervalos son de 15 minutos, como podemos controlar a ojo desnudo, Excel marca ciertas filas como si el intervalo hubiera excedido el tiempo permitido

El problema reside en la precisión de cálculo de Excel (problema del floating point que no desarrollaré aquí, pero pueden consultar este artículo de la base de conocimientos de Microsoft). Para hacer visible el problema voy a copiar los valores de la columna C a la columna D aplicando el formato numérico General

Aparentemente todos los valores son idénticos (15/1440 = 0.10416667), pero si agregamos más decimales a la presentación en pantalla de los números vemos lo siguiente

¡Los números, que expresan el intervalo en formato decimal, son distintos a pesar que los intervalos son idénticos! Como Excel utiliza la forma decimal para hacer los cálculosde tiempo y fechas, nuestro Formato Condicional falla ( los marcados con la X, son un poco más grandes y por lo tanto son señalados por el formato condicional).

Una solución posible es reducir la precisión de cálculo a 10 lugares decimales usando REDONDEAR

Otra solución, más precisa, es usar la función MINUTO para expresar el intervalo como número entero

y en el formato condicional usar =$C4>15