Fórmula do Excel: filtrar valores na fórmula de matriz -

Fórmula genérica

(=IF(ISNUMBER(MATCH(data,filter,0)),data))

Resumo

Para filtrar dados em uma fórmula de matriz (para excluir ou exigir determinados valores), você pode usar uma fórmula de matriz com base nas funções IF, MATCH e ISNUMBER.

No exemplo mostrado, a fórmula em H5:

(=COUNT(IF(ISNUMBER(MATCH(data,filter,0)),data)))

onde "dados" é o intervalo nomeado B4: D11 e "filtro" é o intervalo nomeado F4: F6.

Observação: esta é uma fórmula de matriz e deve ser inserida com control + shift + enter.

Explicação

  1. A função MATCH verifica todos os valores no intervalo nomeado "dados" em relação aos valores em "filtro"
  2. ISNUMBER converte os valores correspondentes em TRUE e os valores não correspondentes em FALSE
  3. IF usa a saída da matriz em # 2 acima para filtrar valores, excluindo valores em "filtro"

A matriz final é semelhante a esta:

(1, FALSE, 3; FALSE, 4, FALSE; FALSE, FALSE, FALSE; 1, FALSE, 3; FALSE, FALSE, FALSE; 3, FALSE, FALSE; FALSE, 4, FALSE; FALSE, FALSE, 4)

COUNT é usado apenas para verificar o resultado.

Filtrar para excluir

Na célula H6, a fórmula foi modificada para excluir valores usando 1-ISNUMBER assim:

=COUNT(IF(1-ISNUMBER(MATCH(data,filter,0)),data))

Isso reverte efetivamente a saída do array na etapa 2 acima.

Artigos interessantes...