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.
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.
RICARDO dice
Quiero hacer que se compare un dia de la semana en especifico para hacer descuentos dobles osea si el trabajador falta lunes, viernes, sabado sus descuentos no son normales si no dobles el importe del descuento lo tengo en otra celda y lo he pensado asi =SI(E13=lunes_viernes_sabado, H13=A2*2, H13=a2*1) y asi =SI(E13=lunes or viernes or sabado, H13=A2*2, H13=A2*1)
pero me marca herror de nombre, por favor ayudeme
Carlos Rodriguez dice
Profesor Nolberto vi su video
y entendí perfectamente como hacer por ejemplo los días que más vendo.
yo tengo una tabla así:
A B C
1 Fecha Día Cliente se Hace
2 17/02/2016 miercoles Manicure
3 14/03/2016 lunes Pedicure
4 15/03/2016 martes
5 16/03/2016 miercoles Pedicure
6 17/03/2016 jueves Manicure
7 18/03/2016 viernes
Como se ve hay días que se hacen el servicio y otros que no, le pregunto como seria una formula para saber que dias son lo que mas se hacen servicios y otra que me diga que dias se hacen mas el pedicure por ejemplo
yo he intentado con esta formula y me funciona para saber los dias donde si se presta el servicio, pero lo quiero hacer directamente desde la columna “A”, Sin necesidad de “traducir” los días de la semana en “B”
=CONTAR.SI.CONJUNTO($B$2:$B7;G2;$C$2:$C7;”*”)
Agradezco de antemano su ayuda
danilo efren dice
Excelente ejercicio.
Saludos y gracias por compartirlo.
Danilo
danilo efren dice
Excelente ejemplo.
Muy completo
Saludos.
Mario Alberto Lucio Ruvalcaba dice
Norberto, creo que los vídeos que realizas enseñan a aplicar funciones que de otra manera no se utilizarían, regularmente uno utiliza fórmulas comunes, suma, sumar.si, contar, buscarv, etc. A mi en lo personal me ha servido mucho algunas aplicaciones que enseñas para mi trabajo diario, ya que tengo un negocio y la administración la llevo en Excel, así que tengo la venta diaria, bancos, cuentas x pagar, crédito, facturas, todo en Excel, en mi archivo de cuentas por pagar apliqué algunas fórmulas que mostraste en uno de tus vídeos para llevar el saldo a fin de mes , o en alguna fecha específica. En estos últimos vídeos veo que cuando editas la fórmula presionas alguna tecla y se ven todos los valores que esta llamando la fórmula suma producto, ¿cómo haces esto?. Gracias por tu gran aportación.
Nolberto Lopez dice
Saludos Mario.
Me da gusto que le estén sirviendo los ejemplos para aplicarlos en su trabajo.
Lo que hago para ver lo que la formula o función esta calculando internamente, es primero seleccionar la parte de la formula que quiero verificar y luego se presiona la tecla de función F9
Mario Alberto Lucio Ruvalcaba dice
OK muy amable y disculpa que puse Norberto y no Nolberto
danilo efren dice
Excelente ejemplo.
saludos!!
Danilo
Antonio Torres dice
Estimado Profesor:
usted no tiene algo como se puede hacer en excel algo como tienen los bancos para ver el estado de los depositos ,retiros balance a la fecha ,Pagos o retiros mensual fijos,Ej Todos los meses pago mi telefono $30.00 el dia primero de mes.Y me pagan o depositan mi sueldo todos los meses $200.00. El balance debe salir con el signo de (+) o (-) Ejm. +$200.00 0 -$200.00