☰

Fórmula de matriz de data Countif

tag-icon • tag-icon microsoft-excel conditional-formatting array countif
Fórmula de matriz de data Countif

Estou tentando criar uma planilha para monitorar a conformidade com o treinamento. Estou precisando construir alguns campos de resumo, que mostrem quantos procedimentos uma pessoa está em dia, quantos estão desatualizados e quantos tiveram um novo procedimento emitido desde que foram treinados.

Portanto, para os dados mostrados abaixo, preciso:

  • um cálculo que analisa as três datas próximas à pessoa A e descobre quantas delas são posteriores à Data 1 e à Data 2 acima delas
  • um cálculo que analisa as três datas próximas à pessoa A e descobre quantas delas estão entre a Data 1 e a Data 2 acima delas
  • um cálculo que analisa as três datas próximas à pessoa A e descobre quantas delas são anteriores à Data 1 e à Data 2 acima delas

Tenho tentado uma fórmula de matriz usando o exemplo abaixo, mas não parece funcionar como esperava. Só funciona se a data próxima à pessoa A for maior do que todas as datas próximas à Data 1 e Data 2, não apenas na coluna acima dela.

=COUNTIFS($G65:$GX65,">="&$G$10:$GX$10,$G65:$GX65,">="&$G$6:$GX$6)

Também tentei adicionar um caractere à data próxima à pessoa A usando formatação condicional, mas a função CONT.SE não encontra esse caractere se adicionado por formatação.

Alguma dica?

insira a descrição da imagem aqui

Responder1

Se for o caso que, como no seu exemplo, Date 1é sempre posterior a Date 2, você pode usar estas fórmulas:

  Later:  =SUMPRODUCT(--($E6:$G6>$E$2:$G$2))
Between:  =SUMPRODUCT(($E6:$G6<=$E$2:$G$2)*($E$6:$G$6>=$E$3:$G$3))
Earlier:  =SUMPRODUCT(--($E$6:$G$6<$E$3:$G$3))

insira a descrição da imagem aqui

Se a Data 1 forNÃOsempre a data posterior, as fórmulas só precisariam ser expandidas um pouco para comparar a data adequada

Responder2

Como OP mencionou calcular, três datas ao lado de Person Ae descobre quantas delas são later, between and earlieralém de ambas Date 1e Date 2acima delas

Portanto, usei COUNTIF& COUNTIFSpara comparar datas G69:I69com date 1& 2in G65:I66.

insira a descrição da imagem aqui

  • Fórmula de matriz (CSE) na célula D69:

    {=COUNTIF($G$69:$I$69,">"&$G$65:$I$66)}

As datas são 01-Jan-20& 15-Oct-19.

  • Fórmula de matriz (CSE) na célula E69:

    {=COUNTIF($G$69:$I$69,"<"&$G$65:$I$66)}

As datas são 01-Sep-19.

  • Fórmula de matriz (CSE) na célula F69:

    {=COUNTIFS($G$69:$I$69,">="&MIN($G$65:$I$66),$G$69:$I$69,"<="&MAX($G$65:$I$66))}

As datas são 01-sep-19& 15-Oct-19.

Observação

  • Finalize a fórmula D69, E69 & F69comCtrl+Shift+Enter.

  • Ajuste as referências de células na fórmula conforme necessário.

informação relacionada