Frase da Semana #39

Sabe-se que o típico do gênio é fornecer idéias aos cretinos cerca de vinte anos depois. L. Aragon

Frase da Semana #38

Viver bem é a melhor vingança. George Herbert

Frase da Semana #37

O mundo está cheio de pessoas honestas. São reconhecidas porque dão golpes baixos com a maior falta de habilidade. C. Péguy

Frase da Semana #36

Nem sempre informação é poder - basta perguntar a um bibliotecário. David Brake

O problema dos múltiplos intervalos simultâneos no Excel

Alguns meses atrás, postaram um problema da comunidade de Excel, que achei muito interessante, sobre calcular o tempo total de funcionamento de bombas d’água, sendo que as mesmas operam simultaneamente. Isto é, digamos que existam 5 bombas, elas trabalham independente umas das outras e se deseja obter o tempo total de funcionamento delas, porém não faz diferença se apenas uma funcionou sozinha, ou duas em conjunto, ou três ou todas.

Logo me ocorreu que se tratava de analisar intervalos simultâneos, e a maior dificuldade parecia ser desconsiderar as interseções entre os períodos. Após as primeiras tentativas de cabeça e sem muito esforço, não achei nada fácil de resolver :-) e decidi planejar mais e fazer menos. Para isso, nada melhor do que um quadro e algumas canetas:

multiplos-intervalos

Claro que folha A4 e lápis também servem, mas afinal de contas assistir filmes na tela gigante do cinema geralmente é melhor não é mesmo? :-) O bom de fazer esses esboços e diagramas é que você pode parar, ficar olhando e analisando, apagar, refazer, complementar, etc… as possibilidades são infinitas.

Nas linhas temos os horários (períodos) e nas colunas temos as horas. Comecei a inventar aleatoriamente alguns intervalos, para servirem de exemplos na análise do problema. Ter exemplos sempre ajuda ;-). No caso destes intervalos, a soma total deve ser de 14 horas, pois no período entre 15 e 17 horas não existe intervalo cadastrado, e ao mesmo tempo, nos outros períodos existem alguns intervalos simultâneos.

Com este desenho conseguir organizar um pouco melhor as ideias de como atacar o problema. Partindo para o Excel, comecei com os lançamentos, algo bem típico, neste estilo tabular:

planilha

O objetivo geral era o de controlar uma lista dos intervalos, processando-os um de cada vez. Se um intervalo possuir interseção com outro, eles devem ser unidos num intervalo único, como por exemplo em 12:00-14:00 com 09:00-15:00. Mas se não possuir interseção, ambos os intervalos devem ser considerados. Só que ao unir dois intervalos com interseção, o novo intervalo talvez tenha interseção com outro(s) intervalo(s), então isto requer um novo processamento, o que parece ser algo meio recursivo…

Antes de planejar o processamento principal, comecei a tentar resolver problemas menores (periféricos), que já imaginava que iriam ocorrer durante a diversãológica toda; coisas como saber se dois intervalos têm interseção ou não e unir dois intervalos em um único intervalo. Aqui é um exemplo típico de dividir-e-conquistar, ou seja, transforme (quebre, divida) um problema grande e complexo em vários problemas menores e simples, preocupando-se com um de cada vez.

Então as duas primeiras funções que criei foram a temIntersecao e a uniaoIntervalo, conforme abaixo:

' Retorna FALSO/VERDADEIRO se entre dois intervalos existir interseção.
Function temIntersecao(ByVal inicio1 As Date, ByVal fim1 As Date, _
                       ByVal inicio2 As Date, ByVal fim2 As Date) As Boolean
   Dim ret As Boolean
   ret = True

   If fim1 < inicio2 Or fim2 < inicio1 Then
      ret = False
   End If

   temIntersecao = ret
End Function
' A partir do parâmetro do tipo (I-Início ou F-Fim) retorna a hora a ser utilizada
' para calcular a união de dois intervalos.
Function uniaoIntervalo(ByVal d1 As Date, ByVal d2 As Date, tipo As String) As Date
   Dim ret As Date

   If tipo = "I" Then
      If d1 < d2 Then
         ret = d1
      Else
         ret = d2
      End If
   Else
      If d1 > d2 Then
         ret = d1
      Else
         ret = d2
      End If
   End If

   uniaoIntervalo = ret
End Function

Para processar a lista de intervalos, criei um array do VBA, inicialmente contendo apenas 100 posições (mais do que o suficiente para a primeira versão da solução do problema):

Dim intervalos(1 To 100) As String

onde cada elemento contem a hora inicial e final, no formato 99:99:99|99:99:99 (uma string contendo as duas horas separadas por um pipe). O preenchimento deste array é feito pela subrotina abaixo:

Private Sub populaIntervalos()
   Dim inicio As Date, fim As Date
   Dim contaElemento As Long

   contaElemento = 1
   [H3].Select

   While ActiveCell.Value <> ""
      inicio = ActiveCell.Value
      fim = ActiveCell.Offset(0, 1).Value

      intervalos(contaElemento) = CStr(inicio) + "|" + CStr(fim)
      ActiveCell.Offset(1).Select

      contaElemento = contaElemento + 1
   Wend
End Sub

Em conjunto com este formato/padrão de gravar as horas iniciais e finais nos elementos do array, precisei criar mais uma função auxiliar, que converte duas datas (horas inicial e final) para a string:

Function intervalo2String(ByVal inicio As Date, ByVal fim As Date) As String
   Dim ret As String
   ret = CStr(inicio) & "|" & CStr(fim)
   intervalo2String = ret
End Function

Feito isso, parti para o laço de repetição principal, onde utilizei dois “for .. next” para comparar cada elemento com todos os outros elementos (menos ele próprio). Para cada iteração, verifica se existe interseção entre dois intervalos; se existir faz a união entre eles.

A última etapa, ao concluir a iteração principal, é gerar uma lista única dos intervalos e finalmente então somar os intervalos, para obter o total de horas. A planilha do Excel pode ser baixada aqui.

Frase da Semana #35

Não se deve lutar contra o futuro. O tempo está do nosso lado. William Gladstone