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:
- selecione as células onde os resultados devem ser exibidos (no nosso exemplo, este é o intervalo D6:D20)
- digite (copie a fórmula na primeira célula) do intervalo
- 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?