Reexibir todas as planilhas e Ocultar as planilhas novamente  - Excel VBA
Reexibir todas as planilhas e Ocultar as planilhas novamente – Excel VBA
31 de agosto de 2012
Resolver problema no filtro em tabelas dinâmicas Excel
Resolver problema no filtro em tabelas dinâmicas Excel
20 de setembro de 2012

Aging List Excel

Objetivo: Demonstrar como criar um relatório de Aging List de contas a receber.

Este artigo foi uma ideia do amigo Rodrigo Bernardo a quem eu agradeço


O Aging List é uma análise da carteira de clientes com um agrupamento de datas de 30 em 30 dias, permitindo desta forma demonstrar os títulos vencidos ou a vencer para que seja realizada uma análise de um dos principais ativos da empresa.

Para a criação da sua análise de carteira de clientes, é necessário que você tenha no mínimo os campos em uma tabela:

  1. Vencimento: Vencimento da parcela;
  2. Valor: Valor total do vencimento sem multa e juros.

Na nossa planilha exemplo, colocamos ainda outros campos:

  1. Razão social: Razão social do cliente;
  2. Representante: O equivalente ao vendedor que efetuou a venda ao cliente;
  3. Vencimento: Vencimento da parcela;
  4. Valor: Valor total a ser recebido sem multa e juros;
  5. Inferior: Agrupamento inferior das parcelas;
  6. Superior: Agrupamento superior das parcelas.

Os campos 5 e 6 servem para a análise das informações do contas a receber. Vemos que é muito difícil chegar a alguma análise com os dados brutos, por este motivo, criamos um agrupamento destas informações separando estes vencimentos em grupos de 30 em 30 dias.

Para o limite superior foi utilizada a função Excel:

=SE(C2=HOJE();30;ARREDONDAR.PARA.CIMA((C2-HOJE())/30;0)*30)

Aonde temos por partes:

  • =SE(C2=HOJE();30; -> Se a data for igual a hoje então 30 senão…
  • ARREDONDAR.PARA.CIMA((C2-HOJE())/30;0)*30)  -> Esta função realiza o cálculo das diferenças de data através de C2-HOJE() e depois divide este valor por 30, o valor é arredondado para cima e depois é multiplicado novamente por 30, gerando desta forma um múltiplo de 30.

Na função inferior temos:

  • =SE(F2=30;0;SE(F2<0;F2+29;F2-29)) -> Esta função identifica se o valor do limite superior é igual a 30, se for o valor do limite inferior é setado para 0, senão é realizada uma outra verificação para identificar se o valor é menor que 0, se for soma-se 29, senão subtrai-se 29 do número. Isto é necessário para que quando os grupos forem negativos o limite inferior fique setado corretamente.
Com esta base pronta é possível utilizar tabelas dinâmicas para criar análises ricas como as que seguem como exemplos nas imagens:
Veja também como criar tabelas dinâmicas e formatações condicionais nos artigos com vídeos: http://guiadoexcel.com.br/tabelas-dinamicas ou http://guiadoexcel.com.br/formatacao-condicional-excel-2007-e-2010.

Abraço

Marcos Rieper

16 Comments

  1. Professor, muito interessante e útil o arquivo.

    Grato pela atenção.

  2. Rodrigo disse:

    Esse modelo de Aging List é excelente. Você poderia me enviar por favor por e-mail esse modelo?
    Obrigado
    Rubens

  3. Gostaria muito de receber este artigo …estou com muita dificuldade de criar…

  4. Ulisses disse:

    Excelente, nao conhecia aging até hoje que meu chefe pediu a análise. Amanhã cedo irei testar, parabéns pela iniciativa e realização.

  5. Givaldo Oliveira disse:

    Marcos bom dia! poderia me enviar um modelo do aging list.

    no aguardo

  6. Wesley disse:

    Olá, boa tarde!

    Não consegui montar a dinâmica abaixo. Poderiam me ajudar?

    Valores
    Inferior Superior Qtde Valor R$
    -151 -180 659 R$ 3.638.258,00
    -121 -150 661 R$ 3.615.150,00
    -91 -120 626 R$ 3.478.427,00
    -61 -90 661 R$ 3.631.539,00
    -31 -60 638 R$ 3.513.492,00
    -1 -30 1.132 R$ 6.249.072,00
    0 30 772 R$ 4.261.104,00
    31 60 673 R$ 3.654.865,00
    61 90 754 R$ 4.078.461,00
    91 120 711 R$ 3.899.934,00
    121 150 773 R$ 4.269.992,00
    151 180 758 R$ 4.220.261,00
    Total Geral 8.818 R$ 48.510.555,00

  7. Kátia disse:

    Gostaria de baixar a planilha!

  8. O que significa os quadrinhos coloridos em azul com barrinhas na tabela dinamica em quantidade e valor?

Deixe uma resposta

O seu endereço de e-mail não será publicado. Campos obrigatórios são marcados com *

Esse site utiliza o Akismet para reduzir spam. Aprenda como seus dados de comentários são processados.

Inscreva-se no nosso canal do Youtube!


Junte-se ao nosso canal do Youtube. Começamos em abril de 2016, mas já temos mais de 06:00 h de treinamentos gratuitos e este número irá aumentar. Vídeos novos todos os sábados.