Um leitor me enviou um problema de fórmula interessante esta semana, então pensei em compartilhá-lo como um desafio de fórmula. O problema é este:
Você tem um pagamento mensal fixo, uma data de início e um determinado número de meses. Que fórmula você pode usar para somar o total de pagamentos por ano, com base na seguinte planilha:
Em outras palavras, qual fórmula funciona em E5, copiada para I5, para obter uma soma para cada ano mostrado?
Eu mesmo criei uma fórmula, mas adoraria ver suas idéias também. Se estiver interessado, deixe um comentário com a fórmula que você propõe.
Você pode usar os seguintes intervalos nomeados em sua fórmula se desejar: mos (C5), quantidade (C6), início (C7), fim (C8).
Você pode baixar a planilha abaixo.
Resposta (clique para expandir)Tantas fórmulas excelentes! Obrigado a todos que dedicaram seu tempo para enviar uma resposta. Abaixo estão minhas idéias desconexas sobre o problema e algumas das soluções abaixo.
Nota: Eu nunca esclareci como os limites dos meses devem ser tratados. Eu estava apenas seguindo outra planilha como exemplo. As principais informações são 30 pagamentos, começando em 1º de março: 10 pagamentos em 2017, 12 pagamentos em 2018 e 8 pagamentos (o saldo) em 2019.
Portanto, se você está tendo dificuldade para entender como pode tentar resolver um problema como este, concentre-se primeiro nos pagamentos. Depois de saber quantos pagamentos são em um ano, você pode simplesmente multiplicar esse número pelo valor e pronto.
Então, como você pode encontrar o número de pagamentos em um determinado ano? Nos comentários abaixo você encontrará muitas boas ideias. Existem vários padrões que percebi e listei alguns abaixo. Este é um trabalho em progresso…
Padrões de design
IF + AND/OR + YEAR + MONTH
IF é um recurso confiável em muitas fórmulas e é usado em muitas das fórmulas sugeridas para descobrir se o ano de interesse está "dentro dos limites" das datas de início e término. Em muitos casos, IF é combinado com OR ou AND para manter as fórmulas compactas.
IFERROR + DATEDIF + MAX + MIN
DATEDIF pode retornar a diferença entre duas datas em meses, então a ideia aqui é usar MAX e MIN (por brevidade, em vez de IF) para calcular uma data de início e uma data de término para cada ano, e deixar DATEDIF obter os meses intermediários. DATEDIF lança um erro #NUM quando a data de início não é menor que a data de término, então IFERROR é usado para capturar o erro e retornar zero. Veja as fórmulas de 闫 强, Arun e David abaixo.
MAX + MIN + YEAR + MONTH
As fórmulas de Robert e Peter fazem quase todo o trabalho com MAX e MIN, sem IF à vista. Surpreendente. Se a ideia de usar MAX e MIN para substituir IF for nova para você, este artigo explica o conceito.
DAYS360
A função Excel DAYS360 retorna o número de dias entre duas datas com base em um ano de 360 dias. É uma forma de calcular os meses a partir da ideia de que todo mês tem 30 dias.
SUM + DATE
Esta é minha abordagem ineficiente (mas elegante!) Usando a função DATE e uma constante de matriz com um número para cada mês. a função DATE acelera uma data para cada mês de um ano usando uma constante de matriz e a lógica booleana é usada para verificar a sobreposição.
