Ao lado da PROCV, a função PROCH compõe um conjunto de funções do Excel responsáveis por realizar buscas de dados dentro de uma planilha através de um dado referencial. Enquanto a PROCV permite realizar pesquisas verticais (quando os dados estão organizados em colunas), a PROCH realiza pesquisas horizontais (quando os dados estão organizados em linhas).
A partir de um identificador (como um CPF ou outro código exclusivo, por exemplo), essa função buscará outros dados relacionados àquele termo (como nome, idade, data de nascimento, ou mesmo características e valores, no caso de produtos) usando os dados já cadastrados.
A sintaxe da PROCH é relativamente simples, mas há cuidados que devem ser tomados. Neste artigo, vamos usar a PROCH para realizar procuras horizontais tanto em um intervalo de dados quanto em colunas inteiras. Além disso, você irá conferir algumas dicas importantes para não se confundir ao montar sua fórmula e também ao expandir a função para células adjacentes.
Versão do Excel utilizada na aula: Microsoft Excel Professional Plus [versão 2019]
Versão do Excel utilizada neste tutorial: Microsoft Excel Professional Plus [versão 2019]
Funcionamento da função PROCH
Para entender o conceito de busca horizontal no Excel, vamos tomar como exemplo a planilha abaixo, em que os dados são organizados nas linhas, ou seja, os cabeçalhos das informações encontram-se nas linhas, não nas colunas, como é mais habitual.
Note que temos um identificador (representando um valor exclusivo) e os dados vinculados a ele, como a data, a filial e o valor.
Vamos supor que nossa intenção seja digitar um identificador a fim de buscar as demais informações atreladas a ele. É isso que faz a função PROCH.
O ideal é que a informação que será usada para identificar os registros seja um dado exclusivo, ou seja, único para cada registro. No nosso caso, temos uma sequência numérica crescente, cujos números jamais são repetidos, portanto são dados exclusivos. Outros tipos de dados exclusivos podem ser um número de documento (CPF), um código de barras, um código de registro, um e-mail, etc.
Caso haja dois ou mais registros idênticos, a PROCH retornará apenas o primeiro registro encontrado.
No exemplo acima, nossa pesquisa pelo ID de número 5 retornou as três informações atreladas a ele.
Função PROCH usando intervalo de dados
Primeiramente, vamos aprender a como fazer uso dessa função através de um intervalo de dados, ou seja, usando um intervalo fixo como nossa matriz de registros.
Observe que desejamos buscar três dados (valor, filial e data) utilizando o ID como parâmetro de busca. Assim sendo, a célula onde digitaremos esse valor não receberá nenhuma fórmula. As células que recebem as fórmulas são aquelas que irão retornar os dados baseados no registro inserido na célula acima.
Analisemos então a estrutura da função PROCH:
=PROCH(valor_procurado; matriz_tabela; núm_índice_lin; procurar_intervalo)
O valor_procurado diz respeito ao valor a ser localizado como base da pesquisa, ou seja, o termo de pesquisa. No nosso caso, refere-se ao ID. Esse dado pode ser um valor numérico, um texto ou uma referência. É mais comum que seja uma referência, já que podemos, assim, usar uma célula para digitar esse termo e alterá-lo sempre que necessário para se fazer uma nova pesquisa.
Já a matriz_tabela é propriamente o intervalo que contém todos os dados que passarão pela busca, ou seja, sua tabela de registros.
O parâmetro núm_índice_lin identifica o número da linha de sua tabela de dados (matriz) que faz referência ao tipo de dado que você quer retornar. Por exemplo, para que retornemos o nome da filial em nossa planilha, devemos usar o número 3, já que esse é o terceiro dado da nossa tabela de registros.
Por fim, procurar_intervalo caracteriza o método de procura, ou seja, se o Excel deve buscar valores aproximados quando não encontrar um valor exato, ou se ele deve apenas pesquisar exatamente o valor indicado em valor_procurado.
Vamos começar montando uma sintaxe para o retorno do valor movimentado. Dentro da célula que receberá esse dado, vamos digitar a seguinte fórmula:
=PROCH(B8;B1:T4;4;0)
B8 refere-se à célula onde digitaremos nosso dado de pesquisa. Já B1:T4 é o intervalo de nossa tabela de dados onde estão contidos todos os registros (você pode ou não incluir os cabeçalhos; aqui não estamos incluindo essa parte). O número 4 refere-se à linha que contém o dado a ser retornado (note que o valor é a quarta informação de nossa matriz de dados). O número 0 (zero) representa o valor lógico FALSO, usado para buscar uma correspondência exata em nossa pesquisa. Você também pode digitar FALSO em vez de 0, se preferir.
Uma observação importante a ser mencionada refere-se ao número do índice da linha. O número 4 aqui refere-se à quarta linha de nossa tabela de dados, e não à quarta linha da planilha. Se sua tabela de dados estivesse localizada mais abaixo, iniciando-se em outra linha que não fosse a linha 1 da planilha, ainda assim você deveria usar o número 4, porque as informações de valor correspondem à quarta linha da sua tabela, não da planilha toda.
O dado relacionado ao ID digitado foi encontrado e retornado. Vale lembrar, claro, que o Excel apenas busca os dados, não a formatação dos mesmos, portanto, cabe a você definir o formato de exibição desses dados na célula de retorno. Por exemplo, como estamos retornando valores monetários em B9, essa célula foi formatada para exibir valores monetários.
Expandindo a PROCH por autopreenchimento
Para retornar apenas um único valor, essa sintaxe já é suficiente, apesar de não ser a mais adequada. No entanto, se nossa intenção é usar o autopreenchimento para completar a busca pelos demais dados, precisamos usar referência absoluta.
Se você tentar estender a fórmula que acabamos de aplicar para as células abaixo dela, notará um erro #N/D (não definido).
Isso ocorre porque o autoprenchimento desloca automaticamente as células referenciadas, trocando tanto a célula de pesquisa quanto a localização da matriz de dados nas demais células.
Resolver esse impasse é bem simples: basta que travemos tanto a célula quanto a matriz, usando o sinal $ entre as linhas e colunas ou pressionando F4 quando o cursor estiver localizado nessas posições.
A fórmula ideal seria então:
=PROCH($B$8;$B$1:$T$4;4;0)
Dessa maneira, ao se usar o autopreenchimento, veremos que o erro deixa de ser exibido.
Apesar disso, o Excel exibe agora dados idênticos, e isso é normal, porque o autopreenchimento desloca automaticamente células não travadas, mas não números fixos. O número do índice da linha continua sendo 4 também nas demais células de retorno. Nossa tarefa agora é alterar esses índices nas duas últimas fórmulas: 3 para a filial e 2 para a data.
Pronto! Agora as demais funções funcionam perfeitamente, mantendo a matriz de dados e a célula de busca intactas e alternando as linhas de pesquisa. Mas lembre-se de formatar corretamente suas células de acordo com os dados que elas receberão (data, dinheiro, hora, texto, número decimal, etc.).
É possível também usar o autopreenchimento na direção horizontal. A sintaxe continua sendo a mesma, com referência absoluta aplicada à célula de pesquisa e à matriz de dados. Entretanto, o autopreenchimento, nesse caso, deve ser arrastado para o lado.
Função PROCH usando linhas inteiras
Em vez de um intervalo de dados específico, limitado a determinadas células, você pode usar linhas inteiras como matriz de dados. O ponto positivo é que, nesses casos, você não precisará alterar sua fórmula caso adicione mais dados que extrapolem o limite do intervalo definido dentro da PROCH inicialmente. Trocando em miúdos, se seus dados chegam até a coluna S, e sua PROCH abrange a busca até a coluna S, todos os novos dados que vierem depois da coluna S não serão abrangidos pela busca. Ao se usar linhas inteiras, isso não acontece, porque não há limite de colunas: o Excel buscará dados na planilha toda.
Vamos analisar novamente a estrutura da tabela de dados de nosso exemplo. Veja que ela vai da linha 1 até a linha 4.
Para o argumento matriz_tabela, basta usarmos a notação referente a essas linhas. Um intervalo iniciado na linha 1 e finalizado na linha 4 é notado como 1:4. No entanto, é recomendável que você trave esse intervalo por referência absoluta, principalmente se seu intuito é usar o autopreenchimento em seguida.
=PROCH($B$8;$1:$4;4;0)
Aplicando então as devidas alterações nos números de índice das linhas para as demais fórmulas, temos agora uma PROCH que abrange dados de quatro linhas de forma completa.
O lado negativo de abranger linhas inteiras na busca é que, caso você tenha dados nessas linhas que não façam parte da sua tabela de dados (como anotações e células auxiliares), eles serão levados em consideração para a pesquisa.
- Uma restrição da função PROCH é que ela sempre deve usar o primeiro dado da matriz de dados como referência de busca. Por exemplo, se o identificador estiver na segunda linha da tabela, a PROCH usando o identificador como pesquisa não funcionará. O Excel sempre atrela o valor_procurado à primeira linha da tabela de registros. Para pesquisas que usem como termo de busca um registro que não seja o primeiro, existem outras alternativas, como a função PROCX.
- Para evitar erros quando os dados pesquisados não existirem ou para quando a célula de pesquisa não contiver termo a ser buscado, você pode combinar a função PROCH com a função função SEERRO.
- Em vez de um intervalo fixo ou linhas inteiras como sua matriz_tabela, você pode usar um nome definido. Assim, toda vez que seus dados precisarem ser aumentados, tanto em quantidade de registros como em cabeçalhos, você pode editar seu nome definido no Gerenciador de Nomes do Excel.
