Software de Computador - Fórmula Excel

0

Como colocar observação em coluna separada para dar intervalo de datas. por exemplo. Eu tenho várias datas na coluna A e quero a seguinte resposta na coluna B, por exemplo,

  • Se date > = 01/04/2017 e < = 30/06/2017, a resposta deverá ser Q1
  • Se date > = 01/07/2017 e < = 30/09/2017, a resposta deverá ser Q2 e assim por diante.
por Laxman Ghavate 22.06.2018 / 11:27

2 respostas

2

A resposta à sua pergunta depende dos seus requisitos completos .

A resposta de @ Gary'sStudent ajudará se o número Q aumentar a cada 3 meses, independentemente do ano (indo para Q5, Q6, Q7 ...). A única coisa é que o intervalo de datas que ele pode manipular é finito.

Você poderia melhorar isso (conforme mostrado abaixo). Ambos funcionam, só depende do método que você deseja usar.

Se o que eu suspeito, o que não é explicitado na sua pergunta, é que você deseja ir do primeiro para o quarto trimestre e depois voltar ao primeiro trimestre, representando os trimestres financeiros do ano, tenho uma segunda abordagem também mostrada abaixo. / p>

Versão de intervalo indefinido de resposta fornecida por @ Gary'sStudent

Vamos passar por isso em etapas ...

Vamos começar inserindo as datas em Coluna A @ Gary'sStudent fornecidas para fins de teste.

Precisamoscomeçardescobrindooiníciodotrimestre1paraoanodaprimeiradatainformada.Afórmulanecessáriaparaissoé:

=IF(MONTH(MIN(A:A))>=4,DATE(YEAR(MIN(A:A)),4,1),DATE(YEAR(MIN(A:A))-1,4,1))

ColoqueissoemCellD1porenquanto.

Agora,precisamoscalcularonúmerodetrimestres(trêsintervalosmensais)entreadatadeinícionacélulaD1eadatanacélulaA1

Paraisso,precisamosde=ROUNDDOWN((DATEDIF($D$1,A1,"M"))/3,0) (Número de meses dividido por 3 - arredondado para baixo)

Digite isto em Célula B1

Em seguida, precisamos adicionar "Q" no início e adicionar 1 para nos fornecer o número Q.

="Q"&ROUNDDOWN((DATEDIF($D$1,A1,"M"))/3,0)+1

Agora você pode substituir $D$1 pela fórmula na célula D1 . A fórmula na célula B1 é agora

="Q"&ROUNDDOWN((DATEDIF(IF(MONTH(MIN(A:A))>=4,DATE(YEAR(MIN(A:A)),4,1),DATE(YEAR(MIN(A:A))-1,4,1)),A1,"M"))/3,0)+1

Você pode limpar a célula D1

Para fornecer o número do trimestre financeiro no ano para uma determinada data (Q1 a Q4 e Q1), você precisa de instruções IF () aninhadas. Faça isso em etapas ou será complexo.

***** Etapa 1 *****

Para fins de teste, crie uma lista de datas no primeiro dia de cada mês na coluna A

Na célula B1 , digite a seguinte fórmula:

=IF(OR(A1="",A1=" "),"",IF(AND(MONTH(A1)>3,MONTH(A1)<7),"Q1",""))

Se você receber "Q1" na célula B1 , e agarrar a alça inferior direita da célula e arrastá-la para baixo, cole a fórmula progressiva nas células abaixo com o resultado de dar Q1 onde a data está no trimestre financeiro 1 e deixando os outros em branco.

*****Explicaçãodafórmula*****

AprimeirainstruçãoIF()é

IF(OR(A1="",A1=" "),"",.....)

Isso não verifica nada ou um espaço na célula A1 primeiro. Se houver, não queremos um número Q.

A segunda parte da primeira instrução IF() (o bit ..... acima) é a segunda instrução IF() que é

IF(AND(MONTH(A1)>3,MONTH(A1)<7),"Q1","")

Que verifica se o mês da data na célula A1 é maior que 3 (depois de março) e menor que 7 (antes de julho). Se estiver, a fórmula entrará com "Q1" na célula B2

***** Etapa 2 *****

Para obter o trimestre 2, é necessário copiar a segunda instrução IF () e colá-la nas aspas duplas no final da fórmula na célula B1 . alterar a nova parte para se adequar. (verificação de mês > 6 e < 10 para fornecer "Q2")

Isso significará que na célula B2 você terá;

=IF(OR(A1="",A1=" "),"",IF(AND(MONTH(A1)>3,MONTH(A1)<7),"Q1",IF(AND(MONTH(A1)>6,MONTH(A1)<10),"Q2","")))

Você pode provar que isso funciona novamente, fazendo o que você fez antes e arrastando o formua para baixo nas células abaixo.

***** Etapa 3 *****

Continue indo para os outros trimestres, exceto para o terceiro trimestre, você só precisa de IF(MONTH(A1)>9,"Q3","")

A fórmula resultante no final será

=IF(OR(A1="",A1=" "),"",IF(AND(MONTH(A1)>3,MONTH(A1)<7),"Q1",IF(AND(MONTH(A1)>6,MONTH(A1)<10),"Q2",IF(MONTH(A1)>9,"Q3",IF(AND(MONTH(A1)>0,MONTH(A1)<4),"Q4","")))))

e arrastando a fórmula para baixo o restante das células lhe dará o seguinte

    
por 23.06.2018 / 15:15
1

Primeiro, crie uma pequena tabela de pesquisa nas colunas C por meio de E :

emqueE1contémafórmula:

=Q1

EmB1digite:

=IF(A1="","",VLOOKUP(A1,C$1:E$11,3,TRUE))

e copie para baixo. Em seguida, insira suas datas na coluna A :

NOTA:

  • a coluna D está lá apenas para fins visuais; não é exigido pelas fórmulas.
por 22.06.2018 / 13:50