La función DIA.LAB (WORKDAYS) mejorada

En la nota anterior mencionamos que la función DIA.LAB (WORKDAY) de Excel adolece del mismo problema como su hermana mayor DIAS.LAB (NETWORKDAYS). Si bien ambas aceptan un rango de fechas como feriados, los días sábado y domingo son considerados fin de semana y por lo tanto no laborales.
No hay forma de cambiar esta definición y si nos ha tocado vivir en alguna parte del mundo donde el fin de semana no es el sábado y el domingo (como en gran parte de los países musulmanes e Israel), estas funciones dan resultados inexactos
.
Ayer mostramos dos soluciones a este problema en lo que respecta a DIAS.LAB. Las fórmulas matriciales propuestas por Chip Pearson y la una UDF (función definida por el usuario) del autor de estas líneas.

Hoy veremos cómo resolver el problema opuesto al planteado ayer: dada la fecha inicial y la cantidad de días hábiles necesarios para completar una misión, cuál será la fecha final.

Si vivimos en cualquier país de Occidente y los días de labor corren de lunes a viernes, podemos usar la función DIA.LAB nativa de Excel. Por ejemplo, si queremos saber cuál será la fecha después de cinco días hábiles empezando el 06/04/2009, DIA.LAB nos da estas respuestas





La celda C4 da la respuesta sin tomar en cuenta los feriados; la celda C5 es la fecha calculada tomando en cuenta los feriados (en España).

Pero ¿qué pasa si en nuestro proyecto no se trabaja los martes y jueves en lugar de los sábados y domingos?

Para solucionar este problema usaremos una función definida por el usurario (UDF), FechaFinal

Function FechaFinal(fecha_inicial As Date, _
dias_habiles As Long, _
diasIncluidos As Range, _
Optional feriados As Range)

'JLD Excel blog en Castellano
'Desarrollada por Jorge Dunkelman
'Junio 2009

Dim daysCounter As Long, tmpDays As Date, dateCheck

tmpDays = fecha_inicial + 1
daysCounter = 1

On Error GoTo ErrorHandler


If feriados Is Nothing Then
Do
tmpDays = tmpDays + 1
dateCheck = " "
dateCheck = WorksheetFunction.Match(Weekday(tmpDays, vbMonday), diasIncluidos, 0)
If IsNumeric(dateCheck) Then
daysCounter = daysCounter + 1
End If

Loop Until daysCounter = dias_habiles

Else

Do
tmpDays = tmpDays + 1
dateCheck = " "
dateCheck = WorksheetFunction.Match(CLng(tmpDays), feriados, 0)
If Not IsNumeric(dateCheck) Then
dateCheck = " "
dateCheck = WorksheetFunction.Match(Weekday(tmpDays, vbMonday), diasIncluidos, 0)
If IsNumeric(dateCheck) Then
daysCounter = daysCounter + 1
End If
End If
Loop Until daysCounter = dias_habiles
End If

FechaFinal = tmpDays

Exit Function

ErrorHandler:
dateCheck = "A"
Resume Next


End Function

Los argumentos de la función FechaFinal son


Fecha_Inicial: la fecha de incio del proyecto escrita con formato de fecha o una referencia a una celda que contiene esta fecha

Dias_Habiles: un número entero que indica la cantidad de días hábiles a contar desde elprimer día del proyecto

Dias_Incluidos: los días de la semana a tomar en cuenta (1 = lunes, 2 = martes, etc.)

Feriados (opcional): un rango que contiene las fechas de los feriados relevantes.

Los resultados de nuestro ejemplo son:





Technorati Tags: MS Excel

La función DIAS.LAB versión mejorada

Excel viene provista con la función DIAS.LAB (NETWORKDAYS) que permite calcular la cantidad de días hábiles transcurridos entre dos fechas. En Excel 2007 esta función figura en la lista que muestra el asistente de funciones. En las versiones previas, hay que tener instalado el complemento Analysis Toolpak para que la función esté disponible.





Esta función permite tomar en cuenta las fechas de los feriados como argumento opcional, lo que la hace muy útil e “internacional”.

Pero también tiene una seria desventaja: en algunos países la semana de trabajo no comienza el lunes y no necesariamente los días de descanso son el sábado y el domingo. Por ejemplo en Egipto, Jordania Irak y Siria la semana laboral va de domingo a jueves y el fin de semana incluye el viernes y el sábado. En Israel la semana laboral corre de domingo a jueves o al mediodía del viernes. La función no permite cambiar estas definiciones lo que hace que en algunos países los resultados sean inexactos.

Lo mismo sucede si queremos calcular cuántos días tomará realizar un determinado proyecto cuando ciertos días de la semana no se realizan tareas relacionados con éste.

Empecemos por plantear el problema. Queremos saber cuántos días hábiles hay en el mes de abril del año 2009.





El resultado, que no toma en cuenta los feriados de abril, es 22 días. Para los lectores escépticos he agregado un calendario dinámico que muestra el mes de la fecha que aparece en la celda D2 (construido con la técnica que muestro en esta nota).

Tomemos como ejemplo España y agreguemos a nuestra fórmula los feriados del año 2009 (que aparecen en el rango G3:G14 de la hoja)



Vemos que el número de días hábiles es ahora 20 (el 12 de abril cae un domingo).

Nuestro problema empieza si vivimos en Jordania o Israel, o si en nuestro proyecto no se trabaja los martes y jueves, por ejemplo.

Chip Pearson propone dos soluciones a este problema usando fórmulas matriciales. Una fórmula que no toma en cuenta los feriados y una segunda que sí los incluye.

Tomemos el ejemplo anterior y supongamos que los días no laborales en nuestro proyecto son el martes y el jueves (días 2 y 4 de la semana respectivamente). La siguiente fórmula matricial permite calcular los días laborales, donde el sábado y el domingo lo son

=SUMA(SI(ESERROR(COINCIDIR(DIASEM(FILA(INDIRECTO(inicio&":"&fin)),2),dias_excluidos,0)),1,0))




Para que la fórmula sea más legible usamos los nombres

inicio = $C$2

fin = $C$3

días_excluidos = =DESREF(matricial!$F$4,0,0,CONTARA(matricial!$F$4:$F$35),1)

Para tomar en cuenta también los feriados debemos complicar un poco (bastante) la fórmula. Siguiendo con nuestro ejemplo, creamos un rango llamado “feriados” (B8:B19 en nuestro ejemplo) y lo incluimos en un nombre usando la función DESREF para que sea dinámico:

feriados =DESREF('matricial feriados'!$B$8,0,0,CONTARA('matricial feriados'!$B$8:$B$372),1)

La fórmula matricial es

=SI(O(inicio<=0,fin<=0,inicio>fin,ESNUMERO(inicio)=FALSO,ESNUMERO(fin)=FALSO),NOD(),SUMA(SI(ESERROR(COINCIDIR(DIASEM(FILA(INDIRECTO(inicio&":"&fin))),dias_excluidos,0)),SI(ESERROR(COINCIDIR(FILA(INDIRECTO(inicio&":"&fin)),feriados,0)),1,0)),0))



Recordemos una vez más que las fórmulas matriciales deben ser introducidas apretando simultáneamente Ctrl+Mayúsculas+Enter.

En lugar de las fórmulas matriciales propuestas por Pearson podemos crear una UDF (user defined functions, funciones definidas por el usuario).

En esta UDF el argumento “feriados” es opcional.


Function DiasLabMejorada(fecha_inicial As Date, _
                            fecha_final As Date, _
                            diasExcluidos As Range, _
                            Optional feriados As Range)
                            
'JLD Excel blog en Castellano
'Desarrollada por Jorge Dunkelman
'Junio 2009

    Dim daysCounter As Long, tmpDays As Date, dateCheck
    
    tmpDays = fecha_inicial + 1
    daysCounter = 0
    
    On Error GoTo ErrorHandler
    
    If feriados Is Nothing Then
    Do While tmpDays < fecha_final
        dateCheck = " "
        dateCheck = WorksheetFunction.Match(Weekday(tmpDays, vbMonday), diasExcluidos, 0)
        If Not IsNumeric(dateCheck) Then
            daysCounter = daysCounter + 1
        End If
        tmpDays = tmpDays + 1
    Loop
    
    Else
    
    Do While tmpDays < fecha_final
    dateCheck = " "
    dateCheck = WorksheetFunction.Match(CLng(tmpDays), feriados, 0)
    If Not IsNumeric(dateCheck) Then
        dateCheck = " "
        dateCheck = WorksheetFunction.Match(Weekday(tmpDays, vbMonday), diasExcluidos, 0)
        If Not IsNumeric(dateCheck) Then
            daysCounter = daysCounter + 1
        End If
    End If
        tmpDays = tmpDays + 1
    Loop
    End If
    
    DiasLabMejorada = daysCounter
    
   Exit Function
  
ErrorHandler:
dateCheck = "A"
Resume Next

    
End Function



Para usar esta UDF debemos guardarlo en un módulo común del editor de VB. Si lo hacemos en el cuaderno Personal.xls, podemos usar la función en todo cuaderno abierto.


En la próxima nota veremos como calcular la fecha final dados la fecha inicial y la cantidad de días hábiles a tomar en cuenta. Como en el caso de DIAS.LAB, también la función DIA.LAB toma en cuenta los sábados y domingos como días no laborales, parámetros que no pueden ser cambiados.

Technorati Tags: MS Excel

La función JERARQUIA en tablas dinámicas.

Un lector me consulta cómo usar la función JERARQUIA en un campo calculado de una tabla dinámica.

Supongamos este ejemplo

Como pueden apreciar, el campo Jerarquía no es parte integral de la tabla dinámica. Sencillamente, lo hemos creado usando la función JERARQUIA.

Al no estar ligado a la tabla, si agregamos o quitamos vendedores en la lista de origen, éste campo no se adaptará a los cambios.

Lo ideal sería crear un campo calculado usando esta función. Pero lamentablemente no se puede hacer.

Como en casi todas lo que queremos hacer con Excel, existe una solución. Pero primero tenemos que definir qué es lo que queremos hacer.

Si la tarea es ordenar los vendedores por el total de ventas, podemos usar las funcionalidades del botón Avanzado del menú de Configuración de campo

En las opciones de Autoordenar señalamos Descendiente (o Ascendiente).



En la parte derecha del formulario se encuentra el comando Automostrar que nos permite mostrar los n valores superiores (o inferiores).

Después de aplicar Autoordenar la tabla se verá así

Si queremos agregar un campo Jerarquía que sea parte integral de la tabla dinámica tendremos que agregar dos campos auxiliares en la lista de origen.

El primer problema es que no podemos usar JERARQUIA en la lista de origen, ya que queremos calcular el orden por el total de ventas. Por lo tanto creamos una columna auxiliar que en cada línea de cada vendedor nos muestre el total de ventas de ese vendedor.

Esto lo solucionamos fácilmente con SUMAR.SI

Como pueden apreciar, las celdas en la columna C siempre muestran el total de ventas del vendedor.

El próximo paso es calcular el orden de rango de cada vendedor en base a la columna auxiliar. No podemos usar JERARQUIA ya que en caso de valores repetidos todos reciben el mismo número de jerarquía y el número siguiente es omitido.

En nuestro caso pueden ver que el 2 y el 3 no existen, tampoco el 5 y el 6, el 8, el 9, el 11 y el 12

Para superar este problema usaremos la fórmula que mostramos en la reseña sobre la función JERARQUIA

={SUMA(1/SI($C$2:$C$16>C2,CONTAR.SI($C$2:C16,$C$2:$C$16),9.999999999E+307))+1}

Se trata de una fórmula matricial que debe ser introducida apretando Ctrl+Mayúsculas+Enter. La explicación sobre la fórmula puede leerse en la nota mencionada.

Ahora podemos construir la tabla dinámica incluyendo el campo Jerarquia

Para obtener el resultado correcto en el campo Jerarquia, usamos la función MAX en la definición del campo



El último retoque es ocultar el total del campo Jerarquia (que no tiene ningún sentido), dándole a la fuente el mismo color del fondo de la celda

Technorati Tags: MS Excel