encontra a próxima data de vencimento depois de hoje dentro de um grupo em uma tabela dinâmica do Excel

Question

Eu tenho uma tabela configurada em uma planilha com "transações". Cada linha contém um nome de um fornecedor, o valor devido ou pago, dependendo do tipo de transação, e a data de vencimento / data da transação. Aqui estão alguns dados de amostra simplificados:

Vendor      Date    Invoice  Payment
Vendor A    6/30    $200       
Vendor A    6/30            ($200)  
Vendor B    7/5     $500    
Vendor B    7/5             ($500)
Vendor C    10/28   $50
Vendor A    10/30   $100
Vendor C    11/15   $50       

Eu já construí uma Tabela Dinâmica a partir dessa tabela para agrupar essas transações por fornecedor e somar o restante devido. O que eu estou tentando descobrir é como, para cada fornecedor, obter a próxima data de vencimento (data mínima do grupo, excluindo datas < Hoje ()), ou se não houver data de vencimento seguinte, então eu quero ver a data máxima para esse grupo.

Aqui está a aparência da minha tabela dinâmica, além da coluna de data que gostaria de adicionar (assumindo Today () = 10/23):

Vendor      Date    Owed
Vendor B    7/5     -    
Vendor C    10/28   $100
Vendor A    10/30   $100

Eu sei que a próxima data de vencimento pode não ser tão precisa se eu acabar com a data de um pagamento nessa coluna, mas estou bem com isso.

tl: dr: Eu quero encontrar a próxima data mais próxima dentro de cada grupo ou a última data. Como faço isso?

    

Answer 1

Bem, talvez não seja a maneira mais fácil de abordar isso, mas eu usaria o VBA para criar uma função que retornasse a próxima data de vencimento (ou a maior data se a próxima data de vencimento < Hoje). Fórmulas de matriz são outra opção, mas são bastante ineficientes. / p>

1. Salve sua pasta de trabalho como um arquivo .xlsm (habilitado para macro).
2. Selecione todos os valores na coluna Fornecedor e defina um intervalo nomeado para eles (o código abaixo pressupõe que você tenha chamado o intervalo nomeado "Fornecedor"). Não recomendo selecionar a coluna inteira porque isso fará com que o código seja executado muito lentamente. Basta selecionar as células com datas; o intervalo nomeado expandirá dinamicamente se você inserir novas linhas. Para estar seguro, você pode selecionar algumas células extras na parte inferior; incluindo células em branco não vai doer nada.
3. Pressione ALT + F11 para abrir o editor do VBA.
4. No menu "Inserir", selecione "Módulo".

5. Cole esta função do VBA:

   Public Function NextByVendor(ByVal rngVendor As Range)
   
   Dim c As Range
   Dim strVendorName
   Dim lngToday As Long
   Dim lngNextDate As Long
   
   lngToday = Int(CDbl(Now()))
   lngNextDate = 0
   
   strVendorName = rngVendor.Value
   
   'Get largest date value
   For Each c In Range("Vendor")
       If c.Value = strVendorName And _
       c.Offset(0, 1).Value > lngNextDate Then
           lngNextDate = c.Offset(0, 1).Value
       End If
   Next c
   
   'If largest date is > today, get next available date
   If lngNextDate > lngToday Then
       For Each c In Range("Vendor")
           If c.Value = strVendorName And _
           c.Offset(0, 1).Value < lngNextDate And _
           c.Offset(0, 1).Value > lngToday Then
               lngNextDate = c.Offset(0, 1).Value
           End If
       Next c
   End If
   
   NextByVendor = lngNextDate
   
   End Function
   

6. Salve e feche a janela do VBA. Volte para sua planilha e adicione uma nova coluna à direita de seus dados. Na primeira linha de dados (digamos 2), insira a fórmula "= NextByVendor (A2)".

7. Copie a fórmula até o fim. Você deve acabar com algo assim:

   Vendor          Date            Invoice   Payment   Next Payment Due
   Vendor A        6/30/2012       $200.00             10/30/2012
   Vendor A        6/30/2012                 ($200)    10/30/2012
   Vendor B        7/5/2012        $500.00             7/5/2012
   Vendor B        7/5/2012                  ($500)    7/5/2012
   Vendor C        10/28/2012      $50.00              10/28/2012
   Vendor A        10/30/2012      $100.00             10/30/2012
   Vendor C        11/15/2012      $50.00              10/28/2012
   

8. Atualize sua tabela dinâmica; os novos dados devem agora estar disponíveis para ele. Você pode definir o tipo de valor para "Próximo pagamento devido" na tabela dinâmica para "Média" ou "Min".

Espero que isso ajude!

- jm