none
Cálculo com Datas RRS feed

  • Pergunta

  • Prezados:

    Mais uma vez venho pedir de vocês, tenho que montar uma planilha e não tenho ideia por onde começar.

    Pode ser com fórmulas ou macros.

    Para facilitar utilizarei números no lugar de datas.

    Minha planilha precisa de 100 períodos e os multiplicadores variam.

    Agradeço muito quem puder me ajudar.

    O que necessito é retirar concomitância entre períodos de datas de acordo com os melhores multiplicadores conforme segue:


    Dt Inicial          Dt Final     Multiplicador            Resultado Esperado

    10                    20                  3                        08 dias x 3 = 24 -> ou seja, do dia 10 ao dia 17

    18                    40                  4                        23 dias x 4 = 92 -> ou seja, do dia 18 ao dia 40

    20                    30                  1                        nenhum dia aproveitado, os demais períodos tem multiplicadores maiores

    35                    70                  2                       20 dias x 2 = 40 -> ou seja, do dia 41 ao dia 50 e do dia 61 ao 70

    50                    60                  8                       11 dias x 8 = 88 -> ou seja, do dia 50 ao dia 60

    80                    90                  2                       11 dias x 8 = 88 -> ou seja, do dia 80 ao dia 100

    85                    87                  2                       nenhum dia aproveitado,os dias 85 a 87 já foram utilizados

    Grato

    Adriano

    sexta-feira, 3 de junho de 2016 01:45

Respostas

  • Acho que se você alterar

    Dim iCurrentMultiplier As Long


    Para:

    Dim iCurrentMultiplier As Single

    Será suficiente.

    EDIT: mude no módulo de classe também para: 

    Public TopMultiplier As Single


    http://www.ambienteoffice.com.br - http://www.clarian.com.br


    sexta-feira, 3 de junho de 2016 17:20
    Moderador

Todas as Respostas

  • Por que do dia 10 ao 20 existem apenas 8 dias e por que do dia 50 ao 60 vocês considerou todos os 11 dias?

    http://www.ambienteoffice.com.br - http://www.clarian.com.br

    sexta-feira, 3 de junho de 2016 09:12
    Moderador
  • Felipe, obrigado pela atenção

    O segundo período tem um multiplicador maior e começa no dia 18, então, do primeiro período, de 10 a 20 só aproveito do dia 10 ao dia 17.

    Do dia 50 ao 60, o multiplicador do quinto período é o maior, então, aproveito todos os dias.

    Veja que os dias 50 a 60 também estão no quarto período, mas tem um multiplicador menor.

    Grato

    Adriano

    sexta-feira, 3 de junho de 2016 09:46
  • Na verdade, o seu problema é um pouco complexo e eu resolveria por VBA. É necessário fazer uma rotina que verifique em todas as linhas quais são os melhores multiplicadores de cada dia.

    Considere que seu cabeçalho comece na linha A1.

    Crie uma classe chamada cDateElement e ponha o código a seguir:

    Option Explicit
    
    Public TopMultiplier As Long
    

    Num módulo regular, coloque o código abaixo:

    Option Explicit
    
    Sub Main()
        Dim ws As Worksheet
        Dim iRow As Long
        Dim iDay As Date
        Dim iKey As String
        Dim iDateElement As cDateElement
        Dim iCurrentMultiplier As Long
        Dim DateElements As Object 'Scripting.Dictionary
        Dim iValidDays As Long
        
        Set ws = ActiveSheet
        Set DateElements = CreateObject("Scripting.Dictionary")
        
        With ws
            For iRow = 2 To .Cells(.Rows.Count, "A").End(xlUp).Row
                For iDay = .Cells(iRow, "A") To .Cells(iRow, "B")
                    iKey = CStr(CLng(iDay))
                    iCurrentMultiplier = .Cells(iRow, "C")
                    
                    If DateElements.Exists(iKey) Then
                        Set iDateElement = DateElements(iKey)
                        
                        If iDateElement.TopMultiplier < iCurrentMultiplier Then
                            iDateElement.TopMultiplier = iCurrentMultiplier
                        End If
                    Else
                        Set iDateElement = New cDateElement
                        iDateElement.TopMultiplier = iCurrentMultiplier
                        DateElements.Add iKey, iDateElement
                    End If
                    
                    Set iDateElement = Nothing
                Next iDay
            Next iRow
        
            For iRow = 2 To .Cells(.Rows.Count, "A").End(xlUp).Row
                For iDay = .Cells(iRow, "A") To .Cells(iRow, "B")
                    iKey = CStr(CLng(iDay))
                    iCurrentMultiplier = .Cells(iRow, "C")
                    
                    If DateElements.Exists(iKey) Then
                        Set iDateElement = DateElements(iKey)
                        
                        If iDateElement.TopMultiplier = iCurrentMultiplier Then
                            iValidDays = iValidDays + 1
                            DateElements.Remove iKey
                        End If
                    End If
                
                Next iDay
                
                .Cells(iRow, "D") = iValidDays
                iValidDays = 0
            Next iRow
        
        End With
    End Sub
    

    Agora, basta escrever a fórmula =C2*D2 na célula E2, copiá-la e colar abaixo pela coluna.


    http://www.ambienteoffice.com.br - http://www.clarian.com.br

    sexta-feira, 3 de junho de 2016 10:54
    Moderador
  • Felipe, obrigado mesmo pela grande ajuda e atenção, mas está dando erro.

    Só de preencher as células, nada acontece, se mando executar a macro dá erro na linha

    Dim iDateElement As cDateElement

    E aparece a caixa de erro :

    Erro de compilação:

    "O tipo definido pelo usuário não foi definido"

    O que fazer?

    Acho que o problema é no nome da classe, não estou conseguindo colocar o nome cDateElement, e o nome atribuido é Classe1, se tento mudar a parece a mensagem "É impossível executar a operação solicitada".

    Coloquei o nome DateElement, e alterei todos os cDateElement do código para DateElement, ou seja, retirei o "c" e parece que funcionou, algum problema no que fiz?

    Grato

    Adriano




    • Editado AdrianoPires sexta-feira, 3 de junho de 2016 15:22
    sexta-feira, 3 de junho de 2016 15:07
  • Provavelmente você não nomeou a classe com esse nome.

    De qualquer forma, escrevi um artigo deste problema porque achei bom em http://ambienteoffice.com.br/blog/otimizar-datas-considerando-peso/


    http://www.ambienteoffice.com.br - http://www.clarian.com.br

    sexta-feira, 3 de junho de 2016 16:26
    Moderador
  • Felipe,

    Muito obrigado pela ajuda, era isso mesmo que precisava, e parabéns pelo artigo.

    Mas tem um problema,

    utilize os seguintes dados

    61   70     1

    61   80    1,4

    ou entao

    61   80   0,6

    61   70   1

    os resultados dão errados, a impressão que tenho é que o excel não reconhece os multiplicadores menores que 1,5.

    Entretanto, se utilizar

    61   80   1,6

    61   70   2

    o resultado também dá errado, sendo os multiplicadores superiores a 1,5

    Teria solução para isso? Para mim, isso seria um problema, pois meus multiplicadores não são inteiros.

    Grato

    Adriano



    • Editado AdrianoPires sexta-feira, 3 de junho de 2016 17:00
    sexta-feira, 3 de junho de 2016 16:48
  • Acho que se você alterar

    Dim iCurrentMultiplier As Long


    Para:

    Dim iCurrentMultiplier As Single

    Será suficiente.

    EDIT: mude no módulo de classe também para: 

    Public TopMultiplier As Single


    http://www.ambienteoffice.com.br - http://www.clarian.com.br


    sexta-feira, 3 de junho de 2016 17:20
    Moderador
  • Isso mesmo, mais uma vez, muito obrigado e parabéns pelo artigo.

    Adriano

    sexta-feira, 3 de junho de 2016 17:25
  • Felipe, sem querer abusar, seria possível esse código trabalhar com o ano comercial de 360 dias e meses de 30?

    Adriano

    sexta-feira, 3 de junho de 2016 21:08
  • Como seria isso? Não consigo visualizar.

    http://www.ambienteoffice.com.br - http://www.clarian.com.br

    sexta-feira, 3 de junho de 2016 22:24
    Moderador
  • Não sei como funciona, mas tenho uma planilha com macro que faz isso, acho que exclui todos os dias "31" e inclui "29" e "30" de fevereiro.

    Como faço para postar a planilha aqui no fórum?

    Grato

    Adriano

    sexta-feira, 3 de junho de 2016 23:08
  • Para disponibilizar um arquivo aqui, entre em http://www.onedrive.com. Você será redirecionado para um gerenciador de arquivos na sua conta Microsoft. Esse é o seu diretório virtual do OneDrive.

    Selecione o arquivo que quer disponibilizar e carregue no site. Depois que o arquivo for carregado, selecione-o e clique em 'Compartilhar'. Clique, em seguida no botão "Obter um link" e selecione a opção "Somente exibir". Copie esse link e cole aqui.


    http://www.ambienteoffice.com.br - http://www.clarian.com.br

    sábado, 4 de junho de 2016 10:57
    Moderador
  • Felipe, segue o link

    https://onedrive.live.com/redir?resid=56E619E4FCA08F9F!119&authkey=!AF1COSF4plvFmAw&ithint=file%2cxls

    Para o meu uso, necessito que os anos tenham 360 dias e os meses 30.

    Grato

    Adriano


    sábado, 4 de junho de 2016 15:24
  • Quando baixa o arquivo a macro está sendo excluída, mas é esse o código que fica em um módul.

    Nesta eu retiro as concomitancias na mão.

    Function ELAPSED(StartDate As Date, EndDate As Date, ReturnType As Integer)
        Dim StartYear As Integer
        Dim StartMonth As Integer
        Dim StartDay As Integer
        Dim EndYear As Integer
        Dim EndMonth As Integer
        Dim EndDay As Integer
        StartYear = Year(StartDate)
        StartMonth = Month(StartDate)
        StartDay = Day(StartDate)
        EndYear = Year(EndDate)
        EndMonth = Month(EndDate)
        EndDay = Day(EndDate)
        
        If EndDay < StartDay Then
            EndDay = EndDay + 30
            EndMonth = EndMonth - 1
        End If
        If EndMonth < StartMonth Then
            EndMonth = EndMonth + 12
            EndYear = EndYear - 1
        End If
        Select Case ReturnType
        Case 1 '\ return year
            ELAPSED = EndYear - StartYear
        Case 2 '\ return month
            ELAPSED = EndMonth - StartMonth
        Case 3 '\ return day
            ELAPSED = EndDay - StartDay + 1
                    End Select
        
        
    End Function


    sábado, 4 de junho de 2016 15:30
  • Adriano,

    Essa adaptação precisa de um código que teria que considerar que certos períodos de início e de fim não são numéricos, aumentando a complexidade. Não disponho de tempo para implementar, lamento.


    http://www.ambienteoffice.com.br - http://www.clarian.com.br

    sábado, 4 de junho de 2016 16:57
    Moderador
  • Entendo, posso entrar em contato contigo fora do fórum?
    sábado, 4 de junho de 2016 18:19
  • Felipe, como posso entrar em contato com você? Gostaria que me indicasse alguém que pudesse fazer, de forma particular, esse código para mim.

    Grato

    Adriano

    segunda-feira, 6 de junho de 2016 14:47
  • Opa, me desculpe.

    Envie um e-mail para felipe arroba ambienteoffice ponto com ponto br que posso te indicar.


    http://www.ambienteoffice.com.br - http://www.clarian.com.br

    segunda-feira, 6 de junho de 2016 16:08
    Moderador
  • Email enviado.

    Grato pela atenção e no aguardo da ajuda.

    Adriano

    segunda-feira, 6 de junho de 2016 18:50
  • Felipe, recebeu meu email?

    quarta-feira, 8 de junho de 2016 13:45