Correspondência de índice com valores duplicados e células em branco

Question

Estou tentando fazer referência a uma única linha, status diferente e a data correspondente. Cada um desses valores é armazenado em colunas com referências afiliadas (= duplicadas) e, em cada status, há células em branco. A única coisa boa, os valores são declarados do maior para o menor sob cada status correspondente. Portanto, preciso corresponder a uma condição específica para recuperar o valor N (menor ou maior) em vez da primeira correspondência.

    

Answer 1

Aqui está uma maneira de usar fórmulas de matriz. Em vez de usar os valores de cabeçalho (iniciados, retirados, etc), criei fórmulas separadas para cada coluna. Isso simplifica a abordagem e não deve ser um fardo a menos que você tenha muitas colunas de status. Eu também suponho que você iria querer essas fórmulas começando na linha 3.

Nota:AmbassãofórmulasdearrayevocêprecisaráentrarcomoCtrl+Shft+Enter(CSE).

Coloque-osnacéluladesignada,CSE,earraste-osparabaixo.

K3:

{=IFERROR(INDEX($E$2:$E$14,MATCH(1,(I3=$C$2:$C$14)*(MIN((IF($E$2:$E$14="",1000000,$E$2:$E$14))*(IF(I3=$C$2:$C$14,1,1000000)))=$E$2:$E$14),0),0),"")}

Para obter o MIN para funcionar corretamente em uma fórmula de matriz, tive que usar as declarações IF que retornam 1,000,000 quando ele não corresponde à sua referência ou está em branco. Esse valor é apenas um número arbitrário e só precisa ser maior que qualquer valor de data. (Lembre-se, datas são armazenadas como números e 1,000,000 seria uma data em algum momento do ano 4637!) Sem isso, a função MIN encontra o menor valor na matriz, que será 0 .

L3 :

{=INDEX($F$2:$F$14,MATCH(1,(I3=$C$2:$C$14)*(MAX(($F$2:$F$14)*(I3=$C$2:$C$14))=$F$2:$F$14),0),0)}

Observe que a fórmula na coluna L retornará 0 se nada for encontrado. Em vez de complicar a fórmula, basta definir a formatação como dd.mm.yy;;;@ , o que ocultará o valor zero. A fórmula na coluna K retornará um erro se nada for encontrado, então coloquei em uma declaração IFERROR .

    

Answer 2

Comofunciona:

1. EscrevaestafórmulanacélulaG2&preencha.

   {=IFERROR(INDEX($A$2:$F$11,MATCH(0,COUNTIF($G$1:G1,$A$2:$A$11),0)),"")}
   

N.B. Esta é uma fórmula de matriz e           deve ser confirmado com            Ctrl+Shift+Enter .

2. Na célula H2 escreva essa fórmula de matriz, confirme com Ctrl + Shift + Enter e preencha.

    {=Text(MIN(IF(($A$2:$A$11=G2)*($C$2:$C$11>0),$C$2:$C$11," ")),"dd.mm.yy;;;@")}
   

3. Escreva esta fórmula de matriz na célula I2 termine com Ctrl + Shift + Enter & preencha.

    {=Text(MAX(IF(($A$2:$A$11=G2)*($D$2:$D$11>0),$D$2:$D$11,"")),"dd.mm.yy;;;@")}
   

4. A fórmula da matriz na célula J2 deve ser concluída com Ctrl + Shift + Enter & preencha.

   {=IFERROR(INDEX($E$2:$E$11, MATCH(0,COUNTIF($J$1:J1, $E$2:$E$11), 0)),"")}
   

Nota:

1. Ajustar referências de célula na fórmula conforme necessário.

Answer 3

Dears, obrigado por suas respostas, descobri uma maneira mais fácil de combinar e extrair esses dados.

Fórmula para recuperar o menor valor: = IFERROR (LOOKUP (1000000; 1 / (($ A $ 2: $ A $ 3169 = G2) * ($ B $ 2: $ B $ 3169 < > "")); $ B $ 2: $ B $ 3169); "" )

Fórmula para recuperar o maior valor = IFERROR (ÍNDICE (D $ 2: D $ 3149; PARTIDA (1; - ($ A $ 2: $ A $ 3149 = $ G2) * - (D $ 2: D $ 3149 < > "); 0); 1 ); "")

Atenciosamente, Solução