PROCV reutilizável (PROCV)

Conteúdo

Temos uma lista de pedidos com números e nomes de mercadorias. Eu gostaria, por exemplo, de retirar da tabela por número de pedido todos os bens que estão incluídos nela. Mais ou menos assim:

 

Recurso maravilhoso PROCV (PROCV) em tal situação, isso ajudará apenas parcialmente, pois é capaz de extrair dados apenas pela primeira correspondência encontrada, ou seja, apenas nos dará Maçãs. Para localizar e extrair todos os itens da tabela, é melhor usar uma fórmula de matriz. Como este:

=ÍNDICE($B$2:$B$16;MENOS(IF($ E $ 2=A2: A16;LINHA(B2:B16)-1;»»);LINHA()-5))

Deve ser inserido da seguinte forma:

  1. selecione as células onde os resultados devem ser exibidos (no nosso exemplo, este é o intervalo D6:D20)
  2. digite (copie a fórmula na primeira célula) do intervalo
  3. imprensa Ctrl + Shift + Entrar

Subtração de unidade no fragmento CADEIA(B2:B16)-1 é feito por causa do cabeçalho da tabela. Pela mesma razão, para compensar a mudança no intervalo resultante em relação ao original, o número cinco no fragmento é subtraído CADEIA()-5

Para ocultar o #NUM! erro que aparecerá em células vazias no intervalo resultante D6:D20, você pode usar as funções de verificação de erros IF e EOSH, substituindo nossa fórmula por uma um pouco mais complexa:

=SE(EOSH(ИНДЕКС($B$2:$B$16;НАИМЕНЬШИЙ(ЕСЛИ($E$2=A2:A16;СТРОКА(B2:B16)-1;»»);СТРОКА()-5)));»»;ИНДЕКС($B$2:$B$16;НАИМЕНЬШИЙ(ЕСЛИ($E$2=A2:A16;СТРОКА(B2:B16)-1;»»);СТРОКА()-5)))

No Excel 2007, apareceu uma função SEERRO mais conveniente - ela permite que você resolva o problema de forma mais compacta:

=SEERRO(ИНДЕКС($B$2:$B$16;НАИМЕНЬШИЙ(ЕСЛИ($E$2=A2:A16;СТРОКА(B2:B16)-1;»»);СТРОКА()-5));»»)

PS

Na versão em inglês do Excel, essas funções ficarão assim:

=INDEX($B$2:$B$16,SMALL(IF($E$2=A2:A16,ROW(B2:B16)-1,»»),ROW()-5))

=IF(ISERR(INDEX($B$2:$B$16,SMALL(IF($E$2=A2:A16,ROW(B2:B16)-1,»»),ROW()-5))),»»,INDEX($B$2:$B$16,SMALL(IF($E$2=A2:A16,ROW(B2:B16)-1,»»),ROW()-5)))

=IFERROR(INDEX($B$2:$B$16,SMALL(IF($E$2=A2:A16,ROW(B2:B16)-1,»»),ROW()-5)),»»)

  • Usando a função PROCV para localizar dados em uma tabela
  • Uma versão melhorada da função VLOOKUP2 que pode pesquisar em qualquer coluna e não apenas no primeiro valor
  • Funções VLOOKUP2 e VLOOKUP3 do complemento PLEX
  • O que são fórmulas de matriz e para que servem?

 

Deixe um comentário