Há algum tempo atrás, escrevi um ebook ensinando 7 Fórmulas do Excel que Podem Salvar sua Vida. Ou seja, uma coletânea das 7 funções mais utilizadas no Excel pela maioria dos usuários e nos mais diversos segmentos. O profissional que domina essas fórmulas essenciais da ferramenta está um passo a frente dos demais usuários, pois, garante mais agilidade no desenvolvimento de suas planilhas, atinge os resultados que seus gerentes e chefes imediatos necessitam com maior eficiência e rapidez. E o motivo disso é que boa parte do processo, que em muitas tarefas seriam árduas, repetitivas e passíveis de erros, são totalmente automatizadas. Para aqueles que trabalham com muitos dados, com uma vasta quantidade de informações e com uma frequência de alteração bem rápida e dinâmica, é fundamental que domine, de forma mais avançada, algumas destas principais funcionalidades do Excel.
Meu ebook está a venda aqui no site e vale o investimento. E para aqueles que querem aprender um pouco mais, indico este curso online aqui, que possui até certificado.
Bom… dito isto… vamos ao assunto de hoje, que será uma amostra do conteúdo do eBook 7 Fórmulas do Excel que Podem Salvar sua Vida. Vou postar o conteúdo do Capítulo 5 do livro que fala sobre as funções de procura PROCV e PROCH. Aproveitem.
Uso da função PROCV
Esta é, sem dúvida, a função mais utilizada nas fórmulas da maioria das planilhas que usamos em nosso dia a dia. A função PROCV é muito eficaz quando precisamos fazer um busca de informações em alguma tabela na qual não sabemos em que linha iremos obter o valor desejado, o que temos apenas é um dado identificador para procurar nesta tabela e retornar o valor adjacente a linha onde for encontrado. Por exemplo, tenho uma lista contendo o boletim dos alunos de determinado curso. Nesta lista temos na primeira coluna, o nome de cada aluno, na segunda coluna temos as notas do 1º bimestre, na coluna seguinte, as notas do 2º bimestre, e, nas próximas colunas, as notas dos 3º e 4º bimestres, respectivamente. Se quisermos saber a nota que José da Silva obteve no 1º bimestre, sabemos que, essa informação deverá ser procurada na segunda coluna da tabela, mas não sabemos em qual linha estará escrita as notas deste aluno. Nesse momento é que recorreremos ao PROCV.
PROCV pesquisará o valor informado na 1ª coluna do intervalo fornecido e retornará o valor adjacente na coluna da posição informada dentro do mesmo intervalo. No caso do exemplo acima, a função irá procurar José da Silva na 1ª coluna (B), e retornará o valor correspondente, ou seja, na mesma linha da coluna (C). Veja pela figura a seguir.
A partir desta tabela base com as notas de todos os alunos podemos criar resumos em outras planilhas buscando informações nesta tabela de origem. Observe a figura a seguir.
Deixe-me explicar como funciona a função PROCV. Sua estrutura básica é composta da seguinte maneira:
PROCV(valor_procurado; matriz_tabela; núm_índice_coluna; procurar_intervalo)
Assim:
Onde:
valor_procurado: Significa o termo que vamos procurar na tabela de dados. Essa informação será pesquisada sempre na primeira coluna do intervalo de dados passados no segundo parâmetro.
matriz_tabela: É o intervalo de células que corresponde a tabela de dados de origem. Ou seja, nesta tabela deverá conter o termo pesquisado e os valores que serão retornados.
núm_índice_coluna: Deve ser informado um valor numérico que represente, dentre do intervalo informado, a posição da coluna que contém os dados que serão retornados.
procurar_intervalo: Se quiser que a procura retorne valor apenas se a correspondência for exata, informe 0 (FALSO). Se quiser uma correspondência aproximada, digite 1 (VERDADEIRO).
Então, neste primeiro exemplo, para conseguir as notas bimestrais de um aluno específico, utilizei a construção das fórmulas assim:
- Para as notas do 1º Bim:
=PROCV(I7;B7:F18;2;FALSO)
- Para as notas do 2º Bim:
=PROCV(I7;B7:F18;3;FALSO)
- Para as notas do 3º Bim:
=PROCV(I7;B7:F18;4;FALSO)
- Para as notas do 4º Bim:
=PROCV(I7;B7:F18;5;FALSO)
Perceba que a 3ª parte de cada fórmula (itens em vermelho) foram aumentados a cada bimestre. O que significa? Cada bimestre foi informado na tabela base em uma coluna diferente, e esta parte da informação corresponde as posições de tais colunas no intervalo. Você deve ter notado também que o nome do aluno foi informado da célula I7, portanto, quando o nome do aluno é trocado naquela célula, a fórmula é automaticamente recalculada com base no novo nome de aluno.
A função PROCV é bem simples de ser usado e muito funcional. Ela tem sua variação, a função PROCH, que faz pesquisas pela horizontal nos intervalos de dados. No arquivo de exemplos, anexo deste livro, há um exemplo de sua utilização.
Vamos mostrar mais alguns exemplos de uso do PROCV. Para isso, aproveitaremos a base de dados na planilha BaseVendaPorDia, já utilizada nos exemplos anteriores para construirmos nossas fórmulas.
A. Exibir Totais de Produtos Vendidos Por Mês.
Neste exemplo, usamos uma combinação de Soma condicional e PROCV para alcançarmos o resultado da soma de todas as vendas de cada produto em determinado mês. A soma condicional, como vimos nos capítulos anteriores, consegue nos retornar, pela tabela base que temos, os totais de itens vendidos em cada mês. Basta agora conseguirmos saber quanto custa cada produto para, então, multiplicarmos por esse total vendido. Para isso, usaremos PROCV.
Relembre a construção da fórmula para saber o total de itens vendidos no mês:
SOMA(SE(MÊS(BaseVendaPorDia!$C$2:$C$649)=MÊS(C$27); SE(BaseVendaPorDia!$B$2:$B$649=$B28;BaseVendaPorDia!$D$2:$D$649;0);0))
Veja, agora, a construção da fórmula PROCV para identificarmos o preço do produto.
PROCV($B28; TabelaPreços!$B$3:$C$20; 2; FALSO)
Agora é só multiplicar pelo total de itens vendidos.
SOMA(SE(MÊS(BaseVendaPorDia!$C$2:$C$649)=MÊS(C$27); SE(BaseVendaPorDia!$B$2:$B$649=$B28; BaseVendaPorDia!$D$2:$D$649; 0); 0)) * PROCV($B28; TabelaPreços!$B$3:$C$20; 2; FALSO)
Não se esquecendo que, devido a utilização de fórmula matricial, é necessário finalizar com Ctrl+Shift+Enter. Resultado final:
B. Exibir Percentual de Produtos Vendidos Por Mês.
Para este exemplo, vamos utilizar como intervalo base a tabela gerada no exemplo anterior. Partindo dela, precisamos obter os totais mensais e anuais de vendas de cada produto para conseguirmos calcular os percentuais mensais. Utilizaremos o PROCV para retornar estes valores.
Veja como ficaria a fórmula para a coluna do mês de Janeiro:
Perceba que temos 14 colunas em nosso intervalo, e a coluna 2 corresponde ao mês de janeiro em nosso intervalo. A referência da coluna de retorno de dados será incrementada a cada mês até a última coluna do intervalo que corresponde ao total anual. Ou seja, para o mês de fevereiro, utilizaremos 3, para março, 4, e assim por diante. Nesta fórmula, obtemos o valor de venda do mês de Janeiro e dividimos pelo total anual de vendas ($O54), com isso, conseguimos o percentual de vendas daquele produto naquele mês.
A fórmula da coluna Total por Produto ficou assim:
Depois de preencher a fórmula de todos os meses desta primeira linha, basta arrastá-la para as demais linhas.
C. Exibir Valor e Percentual Vendidos Por Mês. (Um pouco de PROCH)
Neste exemplo veremos um pouco da utilização do PROCH, muito semelhante ao PROCV, diferencia-se apenas pelo sentido da procura que será na horizontal do intervalo, ao contrário da busca vertical que ocorre na PROCV. Vejamos.
O intervalo base especificado possui 20 linhas, e na última linha encontra-se o valor que precisamos: os totais de vendas em cada mês. Portanto, a linha referência que desejamos retornar será a 20.
Desta vez a busca será feita pelas colunas (meses) e retornará um valor adjacente a coluna encontrada na linha que definimos na fórmula.
E aí… Gostaram?
Me sigam no twitter clicando neste botão aqui: Follow @exceldoseujeito
E façam o download dos arquivos utilizados neste artigo. O link para baixar está no final desta matéria, ok. Se puderem, cadastrem-se para receber as novidades do site direto seu email. Abração.
Legal, esse artigo!
Sempre utilizo essa função, mas ela tem um pequeno problema, somente localiza a expressão “EXATA”, pois a aproximada fica ruim demais. Em alguns casos já fiquei na mão em virtude desse problema, pois tive que comparar duas planilhas onde a coluna continham nomes; o diferencial foi que uma tinha o nome correto, e a outra com partes abreviadas. Não deu muito certo não, infelizmente. Ainda estou tentando encontrar uma maneira para fazer isso. Mas voltando ao artigo, essa função facilita muito a pesquisa e interação entre planinhas, para fins de relatorio.
Att.
Isso mesmo Cristiano.
O PROCV não atende a algumas necessidades mais específicas mesmo, além das que você citou, ainda tem a necessidade de os dados da lista estarem ordenados.
Uma alternativa a está fórmula seria o uso das funções ÍNDICE e CORRESP juntas. São poderosas e ainda permitem mais personalizações.
Já escrevi algo sobre o assunto aqui. O artigo é extremamente didático mas ensina bem o uso das funções que falei.
No meu ebook 7 Fórmulas do Excel que Podem Salvar sua Vida abordo sobre o assunto e explico melhor o uso do ÍNDICE e CORRESP. Vale a pena o investimento. Você pode adquirí-lo clicando aqui.
Valeu pelo comentário,
Um Abraço,
Reinaldo Coral
———————–
Assine nossa newsletter gratuitamente e receba todas as nossas novidades. Clique aqui.
Siga @exceldoseujeito
Gostaria de saber como faço para usar a função Procv para ler varias planinhas, tenho uma tabela que tem 5 planilhas dentro, cadastro, cadastro1, cadastro2, cadastro3, mas não consigo fazer o procv ler todas, só uma. pode me ajudar
Renato, não tem como fazer um procv com várias planilhas.
Uma alternativa que te apresento é agrupar vários PROCV em conjunto com SEERRO, assim:
=SEERRO(PROCV(A2;cadastro!A1:B3;2;0);SEERRO(PROCV(A2;cadastro1!A1:B3;2;0);SEERRO(PROCV(A2;cadastro2!A1:B3;2;0);SEERRO(PROCV(A2;cadastro3!A1:B3;2;0);SEERRO(PROCV(A2;cadastro4!A1:B3;2;0);”Valor não encontrado”)))))
O que acontece?
Se não for encontrado algum resultado na primeira pesquisa, executa a pesquisa na próxima planilha, caso também não retorne resultado, vai para a planilha seguinte e assim sucessivamente.
Teste aí e veja se dá certo pra vocÊ.
Abçs
Tentei sua lógica de PROCV mas não funciona, talvez porque as informações que quero estão na mesma planilha, ou seja:
estou em plan1 do arquivo(pasta) de nome tabela de preços;
E quero atualizar preços de outra plan1 da pasta de nome preços;
Nesta plan1 tem a1:a3, a4:A6 E A7:a9 de informações que quero tirar para
minha pasta “tabela de preços.
Se fui claro! Qual fórmula devo usar?
Joevvaldo,
Realmente não entendi o que você está querendo.
Vou arriscar o entendimento que você queira puxar informações de arquivos diferentes.
Você teria que informar o nome do arquivo onde estão os dados que você quer recuperar.
Mais ou menos assim:
Na célula da Plan1 do arquivo Tabela de Preços onde você deseja obter o resultado, digite uma fórmula similar a que segue:
=PROCV(A1;’C:\Arquivos\[Preços.xls]Plan1′!$A$1:$B$10;2;0)
Coloque as referências das linhas e colunas que atendam suas necessidades.
E não se esqueça de informar o caminho completo do arquivo onde estão os preços que você vai recuperar.
Espero que tenha entendido.
Abç
Obrigado pela atençao, mas está dando erro após eu alterar para minha base
=SEERRO(PROCV(B3;cadastro!A:E;2;0);SEERRO(PROCV(B3;cadastro1!A:E;2;0);SEERRO(PROCV(B3;cadastro2!A:E;2;0);SEERRO(PROCV(B3;cadastro3!A:E;2;0);SEERRO(PROCV(B3;cadastro4!A:E;2;0);”Valor não encontrado”)))))
mas da erro #NOME?
Olá Renato.
Qual a versão do Excel que você está utilizando?
Se estiver usando a versão 2003, a função SEERRO não existe, ok.
Por isso, use:
=SE(NÃO(ÉERROS(PROCV(B3;cadastro!A:E;2;0)));PROCV(B3;cadastro!A:E;2;0);SE(NÃO(ÉERROS(PROCV(B3;cadastro1!A:E;2;0)));PROCV(B3;cadastro1!A:E;2;0);SE(NÃO(ÉERROS(PROCV(B3;cadastro2!A:E;2;0)));PROCV(B3;cadastro2!A:E;2;0);SE(NÃO(ÉERROS(PROCV(B3;cadastro3!A:E;2;0)));PROCV(B3;cadastro3!A:E;2;0);SE(NÃO(ÉERROS(PROCV(B3;cadastro4!A:E;2;0)));PROCV(B3;cadastro4!A:E;2;0);”Valor não encontrado”)))))
Abraços
Olá Reinaldo
muito obrigado pela ajuda meu excel é 2003, o comando funcionou direitinho, valeu mesmo.
Obrigadão
Olá Reinaldo! Estou usando a função PROCV no seu sentido clássico (procurar dados pela coluna à esquerda) utilizando esta função: PROCV(D1;$B$1:$C$960;2;0). A coluna “D” é uma coluna com períodos de 05 minutos (00:05, 00:10… até 23:55)e a B também, porém com alguns números entre eles de forma diferente. Porém, ele funciona muito bem até um determinado ponto (linha 30 as 07:00) e a partir daí ele só retorna #ND. Mas, as 07:00 eu tenho um evento nesse horário na coluna B e mesmo assim ele da erro. Já verifique com a função “SE Verdadeiro” se estes numeros são iguais e o excel diz que são. Você pode me ajudar por gentileza, se necessário posso enviar o arquivo para vc?
Muito Obrigado!
Marcelo,
Geralmente ocorre #N/D quando não são encontrados resultados para a sua pesquisa.
Já falei sobre este assunto aqui no site.
Como solucionar problemas de erros – Parte 1: #N/D
Ou seja, pode estar ocorrendo que alguns períodos não estejam presentes na coluna B, ok. Verifique se é isto. Tenho quase certeza que é. Mas, de qualquer maneira, vou te passar uma fórmula que vai te ajudar a controlar a exibição do #N/D, como já falei neste artigo aqui
=SE(ÉERROS(PROCV(D1;$B$1:$C$960;2;0));””;PROCV(D1;$B$1:$C$960;2;0))
Um abraço
Eu entendi Reinaldo, porém existe o valor procurado e mesmo assim ele retorna este erro. Ele tinha que retornar, por exemplo, o valor de -3,23 que é o valor associado as 07:00. Se não for lhe incomodar, poderia envia-lo pra vc da uma olhada?
Muito Obrigado.
Ok.
Manda para contato@exceldoseujeito.com.br
Olá Reinaldo!
Você conseguiu dar uma olhada no arquivo?
Obrigado!
Identifiquei o problema na sua planilha, que se deve a interpretação e entendimento do valor procurado na função PROCV.
Acontece algumas vezes por diversos motivos que precisam ser analisados e identificados caso a caso.
No caso da sua planilha, em algumas células, não estava sendo possível identificar o valor do tipo Horas, minutos e segundos da maneira correta, entõ, a solução foi converter, através de fórmula, para um valor válido para o tratamento na função PROCV. Fico assim a nova fórmula:
=PROCV(TEMPO(HORA(D1);MINUTO(D1);SEGUNDO(D1));$B$1:$C$960;2;0)
O valor procurado agora é convertido para TEMPO de acordo com as informações desmembradas (Hora/Minuto/Segundo) de cada célula.
Um abraço
Com toda a certeza é muito importante conhecer e saber utilizar as funções de pesquisa no excel visto que sao muito utilizadas no ambito empresarial. Muito boa a sua abordagem sobre esta função.
Bom dia,
Estou tentando montar um quadro de indicadores onde preciso informar em qtdd quantas visitas o consultor fez no mes e depois em outro campo informar os nomes das empresas que ele visitou, em qtdd usei a cont.ses, porem para informar os nomes nao estou conseguindo casar o procv com outra formula para buscar o nome da Empresa e data da visita pelo nome do consultor, sabe me auxiliar quais formulas poderia usar???
Olá Renata, respondi no email que você me enviou, ok.
Para os demais leitores que tenham uma dúvida parecida, segue uma luz para que vocês possam adaptar a realidade de seus projetos.
Digamos que o intervalo da tabela-base fosse A1 até E17 e os parâmetros fossem passados nas células A21, A22 e A23, referentes a consultor, empresa e data, respectivamente.
Seria algo mais ou menos assim:
=SOMA(SE(B2:B17=A21;SE(C2:C17=A22;SE(MÊS(E2:E17)=MÊS(A23);1;0);0);0))
A fórmula é matricial. Finalize teclando CTRL+SHIFT+ENTER.
Abç
Olá,
estou com o seguinte problema para fazer a função PROCV, a planilha é o seguinte:
– tenho uma tabela com quatro coluna e n linhas, com a seguintes classificação
Número Nome Sexo Idade
O problema que o numero que coloco para procurar na função existe mais de uma opção, então aparece o primeiro nome que contem o numero procurado. Mas quando acontecer isso quero fazer a clasificação por idade, assim na primeira celula aprecera o meis velho e assim sucessivamente.
Obrigado.
Olá Adriano,
Antes de fazer o PROCV, experimente classificar a tabela, por Número e Idade. Assim:
—————————
Excel 2003
===========================
—————————
Excel 2010
===========================
Agora você pode fazer o PROCV tranquilamente.
Abraço
Bom dia Reinaldo,
Muito bom seu texto! Veja se consegue me ajudar: tenho uma planilha com 11000 itens, sendo que do lado de cada item tem outra coluna com uma porcentagem, preciso que, ao digitar esse número, ele busque a porcentagem relacionada ao número. Sendo assim fiz essa função:
=PROCV(‘2-Mercadorias’!A7;’1-Tabela IBPT por NCM’!A2:A11442;4;FALSO)
Porém não está dando certo. Sabe me dizer o que está errado?
Muito obrigado!
Gustavo
Olá Gustavo, beleza?
No parâmetro do PROCV destinado a informar a tabela com os dados de referência, ou seja, a tabela que contém o valor que você procura bem como a coluna com os dados relacionados que você quer retornar na função, você especificou apenas 1 coluna – a coluna da pesquisa.
Faltou você expandir o intervalo até a quarta coluna, como você informou (4).
Deveria ficar assim:
=PROCV(‘2-Mercadorias’!A7;’1-Tabela IBPT por NCM’!A2:D11442;4;FALSO)
Abraço