Curso de Excel parte 6 – Cálculos con días de la semana

Continuando con el curso de excel, ahora veremos como hacer cálculos con los días de la semana, esto es, usar el nombre del día y hacer cálculos en relación a un rango de fechas, los cálculos con días son muy usuales el aplicaciones contables, algo simple seria obtener el nombre del día de una fecha, también veremos como contar las veces que un día de la semana se repite en un rango de fechas, hacer la suma de las cantidades que corresponden a un día de la semana en un rango de fechas, por ultimo veremos como obtener el porcentaje que la suma de cantidades en relación a un día de la semana ocupa sobre el total general.

Descargar MegaPack Contable en Excel

1.- Obtener el nombre del día de la semana teniendo como base una fecha.

Como ya sabemos, las fechas son en realidad números, esos números inicial el dia 1/01/1900 , esa fecha seria el 1, cada día se va sumando 1, de ese modo tenemos que hoy 18/09/2014 es el 41900 , pero esta representado en formato de fecha.

Ademas de poder representar ese numero en formato de fecha, también se puede representar en el formato del día de la semana al que corresponde ( lunes martes miércoles jueves viernes sábado domingo ), para representar la fecha en ese formato usaremos la siguiente formula.

=TEXTO(A1,"dddd")

La función texto convierte un valor en texto con un formato que nosotros especifiquemos, en este caso el valor el el numero de la fecha y el  formato especificado es “dddd” que representa al día.

2.- Contar las veces que se repite un día de la semana en un rango de fechas.

Ahora damos un paso mas, tenemos un rango de fechas en una columna y se desea obtener el numero de veces que esta el lunes en ese rango.

En este caso usaremos la función SUMAPRODUCTO, esta función suma los productos de un rango o matriz especifico, como el formato del nombre de día no es numero, la función SUMAPRODUCTOno puede hacer la suma, pero podemos convertir cada producto en un 1 cuando la fecha coincida con el día lunes y cero cuando no coincida.

=SUMAPRODUCTO((TEXTO($A$1:$A$18,"dddd")=$E2)*1)

TEXTO($A$1:$A$18,"dddd")=$E2 : En esta parte de la formula estamos convirtiendo cada fecha en el formato de dia de la semana, luego se compara con E2 donde ponemos lunes, asi esa parte nos devolverá falso cuando no coincide y verdadero cuando si coincide.

Aun asi eso no funciona para que SUMAPRODUCTO haga la suma ya que se requieren números, en ese caso multiplicamos por 1 para convertir los falsos en en cero y los verdaderos en 1 , en código binario los 1 son verdadero y los 0 son falso.

En esa imagen se ve como la formula completa =SUMAPRODUCTO((TEXTO($A$1:$A$18,"dddd")=$E2)*1) devuelve cero cuando la fecha no coincide con la que deseamos contar y 1 cuando si coincide, de ese modo sumaproducto puede hacer la suma, en este caso suma cada uno que devuelve.

3.- Hacer suma de acuerdo a un dia de la semana en un rango de fechas.

Esto es algo muy similar al caso anterior, pero en lugar de contar se van a sumar cantidades que estén en otra columna, asi que en este caso no es necesario convertir los verdaderos y falsos en 1 y o , mas bien solo necesitamos hacer referencia a la columna que contiene las cantidades a sumar cuando la fecha coincida con el nombre del dia de la semana especificado.

=SUMAPRODUCTO((TEXTO($A$1:$A$18,"dddd")=$E2)*$C$1:$C$18)

En este ejemplo vemos como la formula devuelve ceros y también ciertas cantidades, los ceros se dan cuando la fecha no coincide con el dia, y las cantidades son las que estan en la columna de a lado cuando la fecha si coincide con el dia, entonces sumaproducto hace la suma de esos productos.

4.- Porcentaje que representa la suma de los valores ligados a un día de la semana en relación al total general.

Aquí se usa la misma formula anterior, pero para saber que porcentaje representa esa suma sobre el total general, simplemente se divide esa suma entre el total general.

=SUMAPRODUCTO((TEXTO($A$1:$A$18,"dddd")=$E2)*$C$1:$C$18)/SUMA($C$1:$C$18)

Observen como la formula convierte cada cantidad en un decimal, esto porque se esta dividiendo entre el total general que seria la unidad o el 100%, después sumaproducto suma cada fracción y nos da la fracción que representa sobre el total.

Aqui lo que se esta haciendo es sumar todas las cantidades de los lunes por ejemplo, luego se divide entre el total, de ese modo sabemos que fracción representa lo que se ha vendido los lunes en ese rango de fechas.

Para convertir la fracción o decimal en porcentaje, solo se debe cambiar el formato de la celda a porcentaje.

Posdata: Hay muchos cálculos mas que se pueden hacer, la clave esta en convertir la fecha en el nombre del día de la semana y luego hacer la comparación con un día de la semana que especifiquemos.