Fórmula genérica
=FILTER(list1,COUNTIF(list2,list1))
Resumo
Para filtrar dados para extrair valores correspondentes em duas listas, você pode usar a função FILTER e a função CONT.SE ou CONT.SE. No exemplo mostrado, a fórmula em F5 é:
=FILTER(list1,COUNTIF(list2,list1))
onde lista1 (B5: B16) e lista2 (D5: D14) são intervalos nomeados. O resultado retornado por FILTER inclui apenas os valores na lista1 que aparecem na lista2 .
Nota: FILTER é uma nova função de matriz dinâmica no Excel 365.
Explicação
Esta fórmula depende da função FILTER para recuperar dados com base em um teste lógico construído com a função CONT.SE:
=FILTER(list1,COUNTIF(list2,list1))
trabalhando de dentro para fora, a função CONT.SE é usada para criar o filtro real:
COUNTIF(list2,list1)
Observe que estamos usando lista2 como o argumento do intervalo e lista1 como o argumento dos critérios. Em outras palavras, estamos pedindo CONT.SE para contar todos os valores em lista1 que aparecem em lista2. Como estamos dando CONT.SE vários valores para os critérios, obtemos uma matriz com vários resultados:
(1;1;0;1;0;1;0;0;1;0;1;1)
Observe que a matriz contém 12 contagens, uma para cada valor na lista1 . Um valor zero indica um valor em lista1 que não foi encontrado em lista2 . Qualquer outro número positivo indica um valor na lista1 que é encontrado na lista2 . Esta matriz é retornada diretamente para a função FILTER como o argumento de inclusão:
=FILTER(list1,(1;1;0;1;0;1;0;0;1;0;1;1))
A função de filtro usa a matriz como um filtro. Qualquer valor em list1 associado a um zero é removido, enquanto qualquer valor associado a um número positivo sobrevive.
O resultado é uma matriz de 7 valores correspondentes que se estendem no intervalo F5: F11. Se os dados forem alterados, FILTER recalculará e retornará uma nova lista de valores correspondentes com base nos novos dados.
Valores não correspondentes
Para extrair valores não correspondentes da lista1 (ou seja, valores na lista1 que não aparecem na lista2 ), você pode adicionar a função NÃO à fórmula desta forma:
=FILTER(list1,NOT(COUNTIF(list2,list1)))
A função NOT reverte efetivamente o resultado de CONT.SE - qualquer número diferente de zero torna-se FALSO e qualquer valor zero torna-se TRUE. O resultado é uma lista dos valores em lista1 que não estão presentes em lista2 .
Com INDEX
É possível criar uma fórmula para extrair valores correspondentes sem a função FILTER, mas a fórmula é mais complexa. Uma opção é usar a função INDEX em uma fórmula como esta:
A fórmula em G5, copiada é:
=IFERROR(INDEX(list1,SMALL(IF(COUNTIF(list2,list1),ROW(list1)-ROW(INDEX(list1,1,1))+1),ROWS($F$5:F5))),"")
Observação: esta é uma fórmula de matriz e deve ser inserida com control + shift + enter, exceto no Excel 365.
O núcleo desta fórmula é a função INDEX, que recebe list1 como o argumento da matriz. A maior parte da fórmula restante simplesmente calcula o número da linha a ser usado para valores correspondentes. Esta expressão gera uma lista de números de linha relativos:
ROW(list1)-ROW(INDEX(list1,1,1))+1
que retorna uma matriz de 12 números que representam as linhas na lista1 :
(1;2;3;4;5;6;7;8;9;10;11;12)
Eles são filtrados com a função IF e a mesma lógica usada acima em FILTER, com base na função CONT.SE:
COUNTIF(list2,list1) // find matching values
A matriz resultante é semelhante a esta:
(1;2;FALSE;4;FALSE;6;FALSE;FALSE;9;FALSE;11;12) // result from IF
Essa matriz é entregue diretamente à função SMALL, que é usada para buscar o próximo número de linha correspondente à medida que a fórmula é copiada para baixo na coluna. O valor k para PEQUENO (pense n-ésimo) é calculado com um intervalo de expansão:
ROWS($G$5:G5) // incrementing value for k
A função IFERROR é usada para detectar erros que ocorrem quando a fórmula é copiada e fica sem valores correspondentes. Para outro exemplo dessa ideia, consulte esta fórmula.
