Encontre a data do terceiro evento anterior

Question

Basicamente, a fórmula precisa encontrar as 3 conclusões mais recentes de uma tarefa e, em seguida, gerar a data da conclusão mais antiga dessas 3. Ou seja, a data da terceira conclusão mais recente de cada tarefa.

Aqui está um exemplo se minha explicação não estiver clara:

Date    | Task   | Count of Task
--------------------------
6/30/18 | Task A | 2
6/30/18 | Task B | 3
7/01/18 | Task A | 2
7/02/18 | Task B | 1
7/03/18 | Task B | 1
7/03/18 | Task B | 1
7/05/18 | Task A | 2
7/09/18 | Task C | 7

A fórmula precisa retornar o seguinte:

Task A: 7/1/18
Task B: 7/2/18
Task C: 7/9/18

Tarefa A: houve 2 conclusões em 05/07 (mais recente), então a terceira mais recente foi em 01/07.
Tarefa B: houve 2 conclusões em 03/07 (mais recente), então a 3ª mais recente é em 02/07.
Tarefa C: todas estavam na mesma data, então a 3ª mais recente é dia 09/07.

A fórmula precisa ser capaz de lidar com duas linhas separadas na mesma data e com a mesma tarefa, pois o conjunto de dados inclui essas entradas.

Acho que a solução envolverá uma combinação de Vlookup e sumif, mas isso está além do meu conjunto de habilidades atual.

Answer 1

Na célula, C10escreva esta fórmula de matriz, finalize com Ctrl+Shift+Entere preencha.

{=MIN(IF($B$3:$B$8=A10,$A$3:$A$8))}

Answer 2

1. Crie a Tabela Dinâmica.

   • Destaque os dados.
   • Vá em Inserir > Tabelas > Tabela Dinâmica.
   • Escolha onde colocar a mesa.
   • Verifique a data, tarefa e contagem.
   • Arraste “Data” para Linhas, “Tarefa” para Colunas e “Contagem” para Valores. E escolha Soma da contagem (se ainda não estiver).

2. Classifique as datas (rótulos de linha) em ordem decrescente.

3. Crie somas cumulativas: Em F3, digite a fórmula =SUM(B$3:B3). E estenda para H8.
4. Crie booleanos para indicar quando a soma é pelo menos 3: Em I3, digite a fórmula =F3>=3. E estenda para K8.
5. Repita as datas, pois PROCV exige que a pesquisa ocorra à direita: Em L3, digite a fórmula =$A3. E estender para N8.
6. Crie os VLOOKUPs. Em I9, digite a fórmula =VLOOKUP(TRUE, I3:L8,4,FALSE). Estender para K9

A resposta está em I9 a K9.

Esta solução ocupa muitas células, mas é uma solução fácil de configurar. Observe que em vez de estender a fórmula na etapa 5 para N8, pode-se apenas alterar VLOOKUP em J9 para se referir à 3ª coluna e VLOOKUP em K9 para se referir à 2ª coluna.

Planilha com Valores:

Planilha com Fórmulas:

Answer 3

Uma combinação da minha resposta mais antiga e da resposta de Rajesh S.

1. Crie uma soma cumulativa em relação a cada tarefa: em Cell D2escreva =SUMIF($B2:B$9, B2, $C2:C$9) >= 3e preencha até Cell C9.

2. Encontre a data máxima para cada tarefa que foi sinalizada como TRUEna Etapa 1: na Célula D11escreva a seguinte fórmula de matriz (e pressione Ctrl+Shift+Enter):

   =MÁX(SE(($B$2:$B$9=B11)*($D$2:$D$9),$A$2:$A$9))

3. Preencha até D13.

Observação: As tarefas devem estar em ordem crescente por data para que a solução funcione.