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, exibir a data da conclusão mais antiga dessas 3. Isto é, 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 7/5 (mais recentes), então a 3ª mais recente é em 7/1. Tarefa B: houve 2 conclusões em 7/3 (mais recentes), então a 3ª mais recente é em 7/2. Tarefa C: todos estavam na mesma data, então o terceiro mais recente é em 7/9.

A fórmula precisa ser capaz de lidar com duas linhas separadas na mesma data 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 C10 escreva esta fórmula de matriz, termine com Ctrl+Shift+Enter & preencha.

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

    

Answer 2

1. Crie a tabela dinâmica.

   • Realce os dados.
   • Ir para Inserir > Tabelas > Tabela Dinâmica.
   • Escolha onde colocar a mesa.
   • Verifique a data, a tarefa e a contagem.
   • Arraste "Data" para Linhas, "Tarefa" para Colunas e "Contagem" para Valores. E escolha Soma da Contagem (se ainda não o foi).

2. Classifique as datas (rótulos de linha) como descendentes.

3. Criar somas cumulativas: na F3, digite a fórmula =SUM(B$3:B3) . E estender para H8.
4. Crie booleanos para indicar quando a soma é pelo menos 3: em I3, digite a fórmula =F3>=3 . E se estende ao K8.
5. Repita as datas, porque o VLOOKUP requer que a pesquisa ocorra à direita: Em L3, digite a fórmula =$A3 . E estender para o N8.
6. Crie os VLOOKUPs. No 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 a N8, pode-se alterar apenas a VLOOKUP em J9 para se referir à terceira coluna e a VLOOKUP em K9 para se referir à segunda coluna.

Planilha com valores:

Folhadecálculocomfórmulas:

    

Answer 3

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

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

2. Encontre a data máxima para cada tarefa marcada como TRUE na Etapa 1: na célula D11 , escreva a seguinte fórmula de matriz (e pressione Ctrl+Shift+Enter ):

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

3. Preencha-o até D13.

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