Início / coding / Perguntas / 79561715
Accepted
Stephan
Asked: 2025-04-08 17:58:49 +0800 CST

Obter o valor do 4º trimestre anterior de um determinado valor (trimestre)

  • 772

Tenho uma string "2023-1" que representa o primeiro trimestre de 2023. No Excel, tenho uma fórmula:

=IF(AND($A2 => [value to calculate];$A2 <= "2023-1");$B2;0)

Agora [valor a ser calculado] deve ter o valor "2022-2" (sempre o 4º trimestre anterior ao segundo valor na fórmula (neste caso 2023-1).

Mais exemplos:

  • 2023-4 deve resultar em 2023-1
  • 2023-3 deve resultar em 2022-4
  • 2023-2 deve resultar em 2022-3

e assim por diante.

Não quero usar VBA, apenas fórmulas comuns do Excel. Qual seria uma maneira de fazer isso?

Já experimentei usar TEXTBEFORE e TEXTAFTER e subtrair 4 da segunda parte da string. Mas ainda preciso fazer alguns ajustes, ou seja, 4-4 vira 0, mas deveria ser 1 (primeiro trimestre). 3-4 = -1, mas deveria ser o 4º trimestre do ano anterior.

Existem soluções melhores para resolver isso sem algumas instruções IF aninhadas?

excel

5 respostas

  1. Finalmente cheguei à fórmula abaixo:

    =LET(BaseDate;SWITCH(INT(TEXTAFTER(A7;"-"));1;"1-1-" & TEXTBEFORE(A7;"-");2;"1-4-" & TEXTBEFORE(A7;"-");3;"1-7-" & TEXTBEFORE(A7;"-");4;"1-10-" & TEXTBEFORE(A7;"-")); QuarterStart;EDATE(DATE(YEAR(BaseDate);FLOOR.MATH(MONTH(BaseDate)-1;3)+1;1);-6);YearValue;YEAR(QuarterStart);QuarterValue;INT((MONTH(QuarterStart)+2)/3);YearValue & "-" & QuarterValue )

    Na célula A7, o valor é algo como 2023-2 (segundo trimestre de 2023). Eu "converti" isso para a data de início do trimestre. Nesse caso, a data de início se torna 04/01/2023 e, na fórmula EDATE, subtraí 6 meses dessa data de início.

    O resultado é 2022-3.

    Se eu subtrair 9 meses, o resultado será 2022-2.

    • 2
  2. Best Answer

    Você pode tentar isto:
    Observe que o trimestre atual é codificado, mas você pode torná-lo uma referência de célula :

    =LET(
    cq, "2023-1",
    cy, TEXTBEFORE(cq, "-"),
    cm, (TEXTAFTER(cq, "-") - 1) * 3 + 1,
    cd, DATE(cy, cm, 1),
    pd, EDATE(cd, -9),
    pq, YEAR(pd) & "-" & INT((MONTH(pd) - 1) / 3) + 1,
    res, IF(AND($A2 >= pq, $A2 <= cq), $B2, 0),
    res
)

    Algoritmo

    • Dividir o trimestre atual em ano e mês
    • crie a data atual (assumindo o primeiro dia do trimestre)
    • calcular a data do trimestre anterior subtraindo nove (9) meses (3 trimestres) da data atual
    • crie a sequência de texto do trimestre anterior a partir da data do trimestre anterior.
    • insira essas variáveis ​​( cqe pq) na sua fórmula original.
    • 2

  3. Eu estava pensando algo assim

    =LET(y,TEXTBEFORE(A2,"-"),q,TEXTAFTER(A2,"-"),y-(q<"4")&"-"&MOD(q,4)+1)

    O pequeno problema é que q é uma string e você precisa fazer uma comparação de strings com ela. A função mod produz zero se a moeda for quatro, caso contrário, o valor original da moeda, então você só precisa aumentá-lo em 1.

    • 2

  4. Eu sei que o OP encontrou uma solução, mas queria tentar com funções antigas disponíveis no Excel 2007.

    insira a descrição da imagem aqui

    Observe que o que você deseja segue um padrão. Sua produção esperada é sempre ano-1 e trimestre+1. A única exceção a isso é se o trimestre for 4, onde a produção esperada é sempre o mesmo ano, mas o 1º trimestre.

    =IF(VALUE(RIGHT(A2;1))=4;LEFT(A2;4)&"-1";VALUE(LEFT(A2;4))-1&"-"&VALUE(RIGHT(A2;1))+1)

    • 2

  5. Aqui está uma maneira que você pode tentar:

    Use uma tabela com todas as datas (coluna A). A vantagem de uma tabela é que a próxima fórmula se ajustará automaticamente se você adicionar linhas.

    Na célula E2 apliquei:=INDEX(Table1[Column1],XMATCH($D2,Table1[Column1])-3)

    As células verdes são de entrada manual.

    Na célula F2 apliquei:=IF(AND($C2<=E2,$C2>=F2),$B2,0)

    Resultado

    • 1

relate perguntas