Pare de usar o PROCV. Use ÍNDICE CORRESP.
Para esse tutorial, tanto faz usar Excel ou Google Planilhas – o resultado é o mesmo.
Vou deixar em anexo, lá no fim, um arquivo do excel com os exemplos utilizados nessa postagem!
Por Que Parar de Usar o PROCV?
Então você escreve o código do serviço no campo Código e espera que a Descrição e o Preço Unitário sejam preenchidos como os valores correspondentes a partir de uma lista de preço.
Pra isso você vai lá e mete aquele PROCVzão nos campos Descrição e Preço Unitário. Talvez você esteja mais familiarizado com algo assim:
Mas digamos, que tal fazer desta forma?
Tá, mas por que você usaria um raio de uma fórmula mais complexa usando duas funções para fazer exatamente a mesma coisa?!
Então, assim:
- No PROCV o valor procurado tem que sempre estar na primeira coluna da tabela onde você está procurando os valores. Então…
- Como você faria para procurar diferentes valores de retorno em diferentes colunas de procura em uma mesma tabela? Vai duplicar ela todas as vezes que precisar?
- Como você procuraria um valor na tabela se o que você está procurando está na coluna da direita e as respostas estão na coluna da esquerda? Dependendo da tabela, você não pode simplesmente trocar as colunas de posição.
- Digamos que você tenha que adicionar ou remover uma coluna na sua tabela. Boa sorte. Agora você terá que sair catando todos PROCVs e atualizar o número da nova coluna em todas as fórmulas. Elas não se alteram sozinhas.
Ainda não pegou as vantagens? Veja se assim vai:
- Você não precisa contar as colunas, principalmente nos casos em que sua tabela for um monstro e tiver muitas delas.
- Não se preocupe em adicionar ou remover colunas na tabela de procura. PROCV não se atualiza sozinho, mas ÍNDICE CORRESP sim, pois ele não é atrelado a uma número e sim ao local da coluna e nas planilhas referências à celulas são dinâmicas – se atualizam sozinhas.
- Não interessa a disposição das colunas. Você pode procurar valores na quarta coluna e retornar valores da segunda.
- PROCV e PROCH é a mesma coisa para ÍNDICE CORRESP. Menos fórmulas e restrições para lembrar.
- Sua planilha fica muito mais foda 😉
Entendi. Como que Usa o Outro?
Mais ou menos assim:
=ÍNDICE([coluna onde estão os valores de retorno]; CORRESP([célula com valor para procurar]; [coluna onde procurar este valor; FALSO))Apenas memorize isso que você está pronto. “Falso” significa o mesmo que no PROCV: retornar o valor exato.
Pode ser falso ou 0. O que preferir. A função pode retornar valores aproximados, mas isso é conversa para outra hora.
Agora vem a mandinga. Basicamente você usa duas funções:
=ÍNDICE(referência; [linha]; [coluna])
Retorna o conteúdo de uma célula baseado na usa posição relativa em um intervalo. Então, se você possui o intervalo [10, 20, 30, 40, 50] e pede para ÍNDICE retornar a posição 4, ele devolve 40: [10, 20, 30, 40, 50].
Isso funciona em ambas direções (linhas e/ou colunas), mas aqui só estamos interessados em uma. Tanto faz qual, pois você pode usar a combinação ÍNDICE CORRESP na horizontal ou na vertical.
=CORRESP(termo procurado; intervalo; falso)
Funciona de forma contrária. Ele retorna a posição relativa de um ítem em um intervalo.
Se o seu intervalo for [10, 20, 30, 40, 50] e você pede a CORRESP qual é a posição do número 40, ele retorna 4, significando a quarta posição.
Tá. Não parece muita esperteza combinar duas funções opostas. Por quê?
Vamos ler de dentro pra fora:
=ÍNDICE($M:$M;CORRESP(D7;$J:$J;falso))Presta atenção na imagem ali em cima.
- CORRESP procura pela posição relativa do que estiver dentro da célula D7 lá no intervalo J:J, neste caso, J:J significa a própria coluna J
- O conteúdo de D7 é 1, então procurando a sua posição relativa em J:J é 4. Quarta linha
- Falso significa que você precisa da correspondência exata
- Então você pede para o ÍNDICE catar no intervalo M:M (ou coluna M) quem é o 4º elemento, ou seja, o valor 4 lá do CORRESP
- Ele vai lá e volta com 15, a sua resposta.
Observe um detalhe: Assim como em PROCV, ele irá sempre retornar a PRIMEIRA ocorrência de um valor na planilha, obviamente.
Entendeu? Qualquer coisa deixa um comentário ali embaixo!
Ah! Deixa eu te falar. Tem um curso bem interessante de Excel que eu recomendo pra você ficar um ninja-jedi-padauã-master. Dá uma olhada clicando aqui, ó! 😉
Bruxaria Advanced 1 – Nomear Colunas – Usar TABELAS.
Há! Isso aqui é o melhor esquema para tabelas gigaaaaaaaaaaaaaaantes. Por que procurar na coluna K o valor que está em F quando você pode procurar por Preço e Carro?
Digamos que sua planilha tenha muitas, mas muitas colunas.
Digamos, também, que seja uma coluna com modelos de veículos, diversas características, taxas, valores e láááá pela coluna BK está o preço do bendito carro. Meu caro. Minha cara. Transforma esta porcaria em TA-BE-LA e seja feliz.
É um pouco fora do contexto desta postagem mas é uma baita mão na roda porque geralmente quem usa ÍNDICE CORRESP ou mesmo PROCV costuma ter problemas com tabelas gigantes.
Veja esta tabela de veículos top de linha. Tem um monte de colunas e só lá na coluna BK está o preço que nos interessa. Não, não.
Muito trabalho ficar correndo de um lado pra outro nas tabelas pra ficar descobrindo onde está a coluna de preço pra cada formuleta que tu criar na tua pasta de trabalho.
- Seleciona esta tabela toda
- Vai na aba PÁGINA INICIAL
- Clica em Formatar como Tabela
- Escolhe qualquer um dos modelos
- Agora dê um nome bonito pra ela
- Provavelmente o Excel já estará na aba FERRAMENTAS DE TABELA
- Vai ao campo Nome da Tabela e coloca o nome SÓ_CARRÃO
Sua tabelona vai ficar com esta cara agora e se chama SÓ_CARRÃO:
Pronto. Agora em vez de você se referenciar às colunas como A2, B4 etc. refira-se a ela de como pessoas normais fariam:
=ÍNDICE(SÓ_CARRÃO[Preço]; CORRESP("Fusca"; SÓ_CARRÃO[Carro]; FALSO))
Entendeu? Então, você se refere a uma coluna desta tabela assim: NOME_DA_TABELA[Campo].
Ainda tem um detalhe, quando você começa a escrever, o Excel já começa a dar uma lista de possibilidades – assim não precisa nem ficar indo lá ver o nome do campo.
Infelizmente até agora este recurso não está diretamente disponível no Google Planilhas. Só através de plug-ins…
Bruxaria Advanced 2 – Buscar o preço da Kombi Marrom
“Véio, posso combinar dois ou mais valores? Tipo assim, quero catar o preço da kombosa marrom nessa lista de preços que eu deixei ali embaixo lá da loja do Robauto. Mó relíquia, tá ligado? Dá liga?”
Claro, pequeno Padauã.
Inclusive você vai usar uma magia mais harrypotteriana, as fórmulas matriciais que explico neste link. Vai abrir em outra aba para você aprender depois.
Agora, foco aqui.
| Carro | Cor | Preço |
| Fusca | Vermelho | R$ 2.500 |
| Fusca | Branco | R$ 2.200 |
| Fusca | Azul | R$ 2.550 |
| Kombi | Branco | R$ 5.500 |
| Kombi | Marrom | R$ 5.230 |
| Kombi | Amarelo | R$ 4.800 |
| Chevette | Verde | R$ 3.200 |
| Chevette | Azul | R$ 3.000 |
Vamos supor que a coluna Carro é a A, a coluna Cor é B e a coluna Preço é C. Se você leu o tópico acima, pode aplicar seus novos aprendizados nessa mandinga também.
Use a fórmula: (E para usar no Excel e G para usar no Google Planilhas)
E: =ÍNDICE($C:$C; CORRESP("Kombi" & "Marrom"; $A:$A & $B:$B; FALSO))G: =ARRAYFORMULA(ÍNDICE($C:$C; CORRESP("Kombi" & "Marrom"; $A:$A & $B:$B; FALSO)))
Agora, veja, no caso do Excel irá aparecer #VALOR! na célula. Isso acontece porque esta fórmula acima é uma fórmula matricial, lembra?
No Excel você precisa dizer para ele isso. Então, em cima da célula com o erro aperte F2 para editar a fórmula e em seguida, Ctrl+Shift+Enter.
Ela será envolvida por chaves {isso são chaves} significando que se trata de uma fórmula matricial.
E sim, você pode usar referências a células no lugar do “Kombi” e “Marrom” sem problemas.
E: =ÍNDICE($C:$C; CORRESP(D3 & E3 ; $A:$A & $B:$B; FALSO))G: =ARRAYFORMULA(ÍNDICE($C:$C; CORRESP(D3 & E3; $A:$A & $B:$B; FALSO)))
Considerando que o nome do veículo procurado está em D3 e a cor em E3.
E o pulo do Gato: Busca em 2D – Tabelas com Duas Dimensões
Como buscar uma informação cruzando uma linha com uma coluna?
Às vezes acontece de suas informações serem matriciais, onde você tem que combinar uma linha com uma coluna para achar um determinado valor.
Veja no exemplo abaixo a tabela de preços para veículos só que desta vez, em forma matricial.
Qual é o preço da Kombi Marrom?!
Você usará um dois CORRESP dentro de um mesmo ÍNDICE. Mas, a diferença aqui é que usaremos o segundo argumento da função, o [coluna]. Relembrando a função ÍNDICE:
=ÍNDICE(matriz; núm_linha; [núm_coluna])
Repara que o terceiro argumento, núm_coluna, está entre colchetes, o que nos indica que é opcional. Por isso, não o utilizávamos até agora nas outras fórmulas acima.
Mas, como no caso agora precisamos deslocar linhas E colunas, agora vamos meter informação ali também.
Além disso, não indicaremos mais a coluna de busca, e sim a tabela inteira, afinal, estamos procurando por toda ela. Veja o exemplo para entender na prática!
=ÍNDICE([tabela onde estão os valores de retorno]; CORRESP([célula com valor para procurar na primeira coluna]; [coluna onde procurar este valor; FALSO);CORRESP([célula com valor para procurar na linha do cabeçalho]; [linha do cabeçalho onde procurar este valor; FALSO))Qual o preço da Kombi marrom?!
=ÍNDICE(B2:D8;CORRESP(B13;A2:A8;FALSO);CORRESP(B12;B1:D1;FALSO))No Google Planilhas funciona da mesma forma!
Clique aqui para baixar o arquivo do Excel com os exemplos.
Se você gostou dessa postagem, compartilha com os seus amigos! E tira onda deles quando estiverem ainda no PROCV. 😉
0 Comentários