Extrai valores de um intervalo que estão ausentes de outro

Question

Eu tenho duas colunas no Excel, "ROSTER" e "PRESENT", mostradas abaixo:

Existeumafórmulaparaalcançaracoluna"NOT HERE"? Eu tentei usar VLOOKUP() e link sem sucesso = (

Qualquer ajuda seria apreciada!

Obrigado!

    

Answer 1

Não há nenhuma função interna que possa realizar essa tarefa sozinha.

Você pode tentar essa fórmula de matriz na coluna "Não aqui" (MS Excel 2007 +)

=IFERROR(INDEX(roster,SMALL(IF(COUNTIF(present,roster)=0,ROW()-1,""),ROW()-1),1),"")

Onde (no meu exemplo) roster é um intervalo nomeado que se refere a $A$2:$A$21 e present é um intervalo nomeado que se refere a $B$2:$B$21

Para inserir a fórmula, selecione as células na coluna Not Here (no meu caso, é C2 até C21 ), digite a fórmula e pressione Ctrl + Deslocamento + Enter

    

Answer 2

Isso pode ser um pouco exagerado, mas funciona. Espero que você não se importe em ter uma coluna intermediária 'Not Here' com espaços, antes de chegar ao resultado final (Not Here 2).

Nosbastidores:

Intervalosnomeadosemuso:

• Lista:(B3:B19)
• Presente:(C3:C19)
• NotHere:(F3:F19)

Fórmuladematrizinseridanointervalo(D3:D19)...

{=IF(ISERROR(MATCH(Roster,Present,0)),Roster,"")}

Fórmulas de matriz inseridas nas células (E3: E19) ...

{=IFERROR(INDEX(NotHere,SMALL(IF(FREQUENCY(IF(NotHere<>"",MATCH(ROW(NotHere),ROW(NotHere)),""),MATCH(ROW(NotHere),ROW(NotHere)))>0,MATCH(ROW(NotHere),ROW(NotHere)),""),ROW(A1)),COLUMN(A1)),"")}

{=IFERROR(INDEX(NotHere,SMALL(IF(FREQUENCY(IF(NotHere<>"",MATCH(ROW(NotHere),ROW(NotHere)),""),MATCH(ROW(NotHere),ROW(NotHere)))>0,MATCH(ROW(NotHere),ROW(NotHere)),""),ROW(A2)),COLUMN(A2)),"")}

etc ...

Embora isso pareça uma solução prolixa, funcionará independentemente de onde a tabela esteja inserida na planilha. Também remove #num erros no Excel 2007, caso você esteja usando essa versão.