Excel Avançado Submódulo: Funções de Pesquisa e de Referência Manual do Formando
Um Projeto desenvolvido para:
www.novaetapa.com
Índice
D. Funções de Pesquisa e de Referência...................................................................................... 3 O que pretende fazer?......................................................................................................................... 3 Utilizar o argumento matriz_tabela numa função de procura............................................................ 3 PROCV (função PROCV) ....................................................................................................................... 5 Problemas Comuns ............................................................................................................................ 10 Práticas recomendadas ..................................................................................................................... 11 PROCH (função PROCH) ..................................................................................................................... 12
2
D. Funções de Pesquisa e de Referência Suponha que pretende procurar a extensão telefónica de um empregado utilizando apenas o número do distintivo, ou que pretende encontrar a taxa de comissão de um determinado valor de vendas. O Excel dá-lhe várias formas de carregar dados em listas de forma rápida e eficiente.
O que pretende fazer? - Procurar valores verticalmente numa lista utilizando uma correspondência exata; - Procurar valores verticalmente numa lista utilizando uma correspondência aproximada; - Procurar valores verticalmente numa lista de tamanho desconhecido utilizando uma correspondência exata; - Procurar valores horizontalmente numa lista utilizando uma correspondência exata; - Procurar valores horizontalmente numa lista utilizando uma correspondência aproximada.
Utilizar o argumento matriz_tabela numa função de procura Quando cria uma função PROCV ou PROCH, está a introduzir um intervalo de células, como D2:F39. Esse intervalo chama-se um argumento matriz_tabela e um argumento é apenas um pedaço de dados de que uma função precisa para ser executada. Neste caso, a função procura nestas células os dados que o utilizador está a tentar encontrar. O argumento matriz_tabela é sempre o segundo argumento de uma função PROCV ou PROCH (o primeiro argumento é o valor que está a tentar encontrar) e, sem ele, as funções não funcionam.
3
O primeiro argumento, o valor que pretende encontrar, pode ser um valor específico, como "41" ou "silva", ou pode ser uma referência de célula como F2. Então, o primeiro argumento teria o seguinte aspeto: =PROCV(F2,... O argumento matriz_tabela segue sempre o valor de procura, assim: = PROCV (F2, B4:D39, ... O intervalo de células listado no argumento matriz_tabela pode utilizar referências relativas ou absolutas de células. Se vai copiar a função, tem de utilizar referências absolutas, assim: = PROCV (F2, $B$ 2: BD$ 39, ... Além disso, as células do argumento matriz_tabela podem existir noutra folha de cálculo do seu livro. Se assim for, o argumento inclui o nome da folha e a sintaxe tem este aspeto: = PROCV (F2, Folha2! $C$ 14: E$ 42, ... Assegure-se de que coloca um ponto de exclamação a seguir ao nome da folha. Finalmente, introduza um terceiro argumento - a coluna que contém os valores que está a tentar encontrar. Esta é designada por coluna de procura. No primeiro exemplo, foi utilizado o intervalo de células de B4 a D39, que atravessa três colunas. Imagine que os valores que pretende ver estão na coluna D — a terceira coluna desse intervalo de células — pelo que o terceiro argumento é um 3. =PROCV(F2,B4:D39,3) Pode utilizar um quarto argumento opcional: Verdadeiro ou Falso. Na maioria das vezes, deverá querer utilizar Falso.
4
Se utilizar Verdadeiro ou deixar o quarto argumento em branco, a função devolve uma correspondência aproximada ao valor do seu primeiro argumento. Para continuar com o exemplo, se o seu primeiro argumento for "silva" e utilizar Verdadeiro, a função devolve "Silva", "Silvas" e assim sucessivamente. Contudo, se utilizar Falso, a função só devolve "Silva", uma correspondência exata, e geralmente é isso que as pessoas querem. Para tornar a utilização de Verdadeiro ainda mais complicada, se a sua coluna de procura — a coluna que especifica no terceiro argumento — não estiver ordenada por ordem ascendente (A a Z ou número menor para maior), a função pode devolver o resultado errado. Para saber mais informações, consulte Valores de procura com a função PROCV e outras funções.
PROCV (função PROCV) Utilize a função PROCV, uma das funções de consulta e referência, quando precisar de encontrar algo numa tabela ou num intervalo por linha. Por exemplo, procure o apelido de uma empregada através do respetivo número de empregado, ou encontre o número de telefone fazendo uma busca pelo apelido (tal como numa lista telefónica). O segredo do PROCV é organizar os seus dados para que o valor que pesquisa (apelido do empregado) esteja à esquerda do valor de retorno que pretende encontrar (número de telefone do empregado). Sintaxe PROCV (valor_proc, matriz_tabela, núm_índice_coluna, [intervalo_pesquisa])
Por exemplo: -
=PROCV(105;A2:C7;2;VERDADEIRO)
-
=PROCV("Rodrigues";B2:E7;2;FALSO)
5
Descrição
Nome do argumento
valor_proc
O valor que pretende pesquisar. O valor que pretende pesquisar tem
(obrigatório)
de estar na primeira coluna do intervalo de células que especificou na matriz-de-tabela. Por exemplo, se a matriz-de-tabela dá origem às células B2:D7, então o seu valor_proc tem de estar na coluna B. Consulte o gráfico abaixo. O valor_proc pode ser um valor ou uma referência a uma célula.
matriz_tabela
O intervalo de células em que o PROCV irá pesquisar o Valor_proc e
(obrigatório)
o valor de retorno. A
primeira
coluna do
intervalo
de
células
deve
conter
o Valor_proc (por exemplo, Apelido no gráfico abaixo). O intervalo de células também precisa de incluir o valor de retorno (por exemplo, Nome Próprio no gráfico abaixo) que pretende encontrar.
núm_indice_colun
O número da coluna (a começar em 1, na coluna mais à esquerda
a (obrigatório)
da matriz-de-tabela) que contém o valor de retorno.
intervalo_pesquisa
Um valor lógico que especifica se pretende que PROCV localize uma
(opcional)
correspondência exata ou uma correspondência aproximada:
VERDADEIRO presume que a primeira coluna da tabela está ordenada por ordem numérica ou alfabética e irá procurar o valor mais próximo. Este é o método predefinido, se não especificar nenhum.
FALSO procura o valor exato na primeira coluna.
6
A
imagem
seguinte
mostra
como
configurar
a
sua
folha
de
cálculo
com
=PROCV("Barbosa",B2:D5,2,FALSE) para devolver Isabel.
Exemplos Para utilizar estes exemplos no Excel, copie os dados na tabela abaixo e cole-os na célula A1 de uma nova folha de cálculo.
7
ID
Nome
Apelido
Cargo
próprio
Data de nascimento
101
Teixeira
Sónia
Resp. de Vendas
8/12/1968
102
Rodrigues
Luís
VP de Vendas
19/2/1952
103
Alves
Luísa
Resp. de Vendas
30/8/1963
104
Cunha
Diogo
Resp. de Vendas
19/9/1958
105
Almeida
Bruno
Gestor de Vendas
4/3/1955
106
Vaz
Mateus
Resp. de Vendas
2/7/1963
Fórmula
Descrição
=PROCV("Rodrigues";2:E7;2;FALSO)
Procura o valor Rodrigues na primeira coluna (coluna B) de B2:E7 de matriz_tabela e devolve o valor Luís localizado na segunda coluna (Coluna
C)
da
intervalo_pesquisa
matriz_tabela. FALSO
devolve
O uma
correspondência exata. =PROCV(102;A2:C7;2;FALSO)
Procura uma correspondência exata do apelido para o valor_proc102 na coluna A. É devolvido Rodrigues. Se o valor_proc for 105, o valor devolvido é Almeida.
8
=SE(PROCV(103;A1:E7;2;FALSO)=
Verifica se o apelido do empregado com
"Vaz","Encontrado","Não
ID103 é Sousa. Utiliza a função se para devolver
encontrado")
um valor se uma condição for verdadeira e outro valor se for FALSO. Uma vez que 103 é efetivamente Leal, o resultado é não encontrado. Se alterar "Sousa" para "Leal" na fórmula, o resultado é Located.
=INT(FRAÇÃOANO(DATA(2014;6;
Para o ano fiscal de 2014, encontra a idade do
30), PROCV(105;A2:E7;5; FALSO),
empregado com o ID 105. Utiliza a função
1))
FRAÇÃOANO para subtrair a data de nascimento à data de fim do ano fiscal e apresenta o resultado 59 como um número inteiro com a função INT.
=SE(É.NÃO.DISP(PROCV(105;A2:E
Se existe um empregado com o ID 105, apresenta
7;2;FALSO)) = VERDADEIRO,
o apelido do empregado, que é Almeida. Caso
"Empregado não encontrado",
contrário, apresenta a mensagem Empregado não
PROCV(105;A2:E7;2;FALSO))
encontrado.
A
(consulte funções
função É)
devolve
É.NÃO.DISP um
valor
VERDADEIRO quando a função PROCV devolve o valor de erro #N/D. =PROCV(104;A2:E7;3;FALSO) & "
Para o empregado com o ID 104, concatena
" & PROCV(104;A2:E7;2;FALSO) &
(combina) os valores das três células na frase
" é um " &
completa Diogo Cunha é um Resp. de Vendas.
PROCV(104;A2:E7;4;FALSO)
9
Problemas Comuns Problema
O que correu mal
Valor
Se o intervalo_pesquisa é VERDADEIRO ou foi deixado de fora, a primeira
devolvido
coluna tem de ser ordenada por ordem alfabética ou numérica. Se a primeira
errado
coluna não está ordenada, o valor de retorno pode ter um resultado inesperado. Ordene a primeira coluna ou utilize FALSO para obter uma correspondência exata.
#N/D
na
célula
Se o intervalo_pesquisa for VERDADEIRO e se o valor do valor_proc for mais pequeno do que o valor mais baixo da primeira coluna da matriz_tabela, irá obter o erro #N/D.
Se o intervalo_pesquisa for FALSO, o valor do erro #N/D indica que não foi encontrado um número exato.
#REF!
na
Se o núm_índice_coluna for maior do que o número de colunas na matriz-
célula
de-tabela, obterá o valor de erro #REF!.
#VALOR! na
Se a matriz_tabela for menor que 1, obterá o valor de erro #VALOR!
célula #NOME? na
O valor de erro #NOME? normalmente significa que faltam aspas à fórmula.
célula
Para procurar o nome de uma pessoa, certifique-se de que utiliza aspas no nome na fórmula. Por exemplo, introduza o nome como "Rodrigues" em =PROCV ("Rodrigues";B2:E7;2;FALSO).
10
Práticas recomendadas Faça o seguinte
Por que motivo
Utilize referências
A utilização de referências absolutas permite-lhe preencher uma
absolutas no
fórmula de forma a pesquisar sempre dentro do mesmo intervalo de
intervalo_pesquisa
pesquisa exato.
Não armazene
Ao procurar valores de números ou datas, certifique-se de que os dados
valores de
na primeira coluna da matriz_tabela não estão armazenados como
números ou datas
valores de texto. Caso contrário, o PROCV poderá devolver um valor
como texto.
incorreto ou inesperado.
Ordene a primeira
Ordene a primeira coluna da matriz_tabela antes de utilizar PROCV,
coluna
quando o intervalo_pesquisa for VERDADEIRO. Se procurar_intervalo for FALSO e valor_proc for texto, pode utilizar os carateres universais — ponto de interrogação (?) e asterisco (*)— no valor_proc. Um ponto de interrogação corresponde a qualquer
Utilizar carateres
caráter individual; um asterisco corresponde a qualquer sequência de
universais
carateres. Se pretender localizar mesmo um ponto de interrogação ou asterisco, escreva um til (~) antes do caráter. Por exemplo, =PROCV("Rodrigue?";B2:E7;2;FALSO) irá procurar todas as ocorrências de Rodrigues em que a última letra pode variar. Ao procurar valores de texto na primeira coluna, certifique-se de que
Certifique-se de que os seus dados não contêm carateres inválidos.
os dados da primeira coluna não têm espaços à esquerda, espaços à direita, utilização inconsistente de plicas ( ' ou " ) e aspas ( ‘ ou “) ou carateres não imprimíveis. Nestes casos, o PROCV pode apresentar um valor inesperado. Para obter resultados precisos, tente utilizar a função LIMPARB ou a função COMPACTAR para remover os espaços à direita após os valores de tabela numa célula.
11
PROCH (função PROCH) Procura um valor específico na linha superior de uma tabela ou matriz de valores e devolve um valor na mesma coluna de uma linha especificada na tabela ou matriz. Utilize PROCH quando os valores de comparação estiverem localizados numa linha ao longo da parte superior de uma tabela de dados e quiser observar um número específico de linhas mais abaixo. Utilize PROCV quando os valores de comparação estiverem localizados numa coluna à esquerda dos dados que deseja localizar. O H em PROCH significa "Horizontal." Sintaxe PROCH (valor_proc, matriz_tabela, núm_índice_lin, [procurar_intervalo])
A sintaxe da função PROCH tem os seguintes argumentos: -
Valor_proc Obrigatório. É o valor a ser localizado na primeira linha da tabela. Valor_proc pode ser um valor, uma referência ou uma cadeia de texto.
-
Matriz_tabela Obrigatório. É uma tabela de informações onde os dados são procurados. Utilize uma referência para um intervalo ou um nome de intervalo. o Os valores na primeira linha de matriz_tabela podem ser texto, números ou valores lógicos. o Se procurar_intervalo for VERDADEIRO, os valores da primeira linha de matriz_tabela têm de ser colocados por ordem ascendente: ...-2, -1, 0, 1, 2,... , A-Z, FALSO, VERDADEIRO; caso contrário, PROCH pode não dar o valor correto. Se procurar_intervalo for FALSO, matriz_tabela não precisa ser ordenada. o Textos em maiúsculas e minúsculas são equivalentes. o Ordene os valores por ordem ascendente, da esquerda para a direita. Para obter mais informações, consulte Ordenar dados num intervalo ou numa tabela.
12
-
Num_índice_lin Obrigatório. É o número da linha na matriz_tabela de onde o valor correspondente deve ser retirado. Um núm_índice_lin equivalente a 1 devolve o valor da primeira linha na matriz_tabela, um núm_índice_lin equivalente a 2 devolve o valor da segunda linha na matriz_tabela e assim por diante. Se núm_índice_lin for menor do que 1, PROCH devolve o valor de erro #VALOR!; se núm_índice_lin for maior do que o número de linhas na matriz_tabela, PROCH devolve o valor de erro #REF!.
-
Procurar_intervalo Opcional. É um valor lógico que especifica se deseja que o PROCH procure uma correspondência exata ou uma correspondência aproximada Se VERDADEIRO ou omisso, é devolvida uma correspondência aproximada. Por outras palavras, se não for encontrada uma correspondência exata, é devolvido o maior valor seguinte, inferior ao valor_proc. Se FALSO, o PROCH localiza uma correspondência exata. Se não for encontrada uma, o valor de erro #N/D é devolvido.
Comentário -
Se PROCH não localizar valor_proc e procurar_intervalo for VERDADEIRO, é utilizado o maior valor que seja menor que o valor_proc.
-
Se valor_proc for menor que o menor valor na primeira linha de matriz_tabela, PROCH devolve o valor de erro #N/D.
-
Se pesquisar_intervalo for FALSO e o valor_pesquisa for texto, pode utilizar os carateres universais, ponto de interrogação (?) e asterisco (*), em valor_pesquisa. Um ponto de interrogação corresponde a qualquer caráter individual, um asterisco corresponde a qualquer sequência de carateres. Se pretende localizar um ponto de interrogação ou asterisco, escreva um til (~) antes do caráter.
Exemplo Copie os dados de exemplo na tabela seguinte e cole-os na célula A1 de uma nova folha de cálculo do Excel. Para que as fórmulas mostrem resultados, selecione-as, prima F2 e, em seguida, prima ENTER. Se pretender, pode ajustar as larguras das colunas para ver todos os dados.
13
Eixos
Rolamentos
Parafusos
4
4
9
5
7
10
6
8
11
Fórmula
Descrição
Resultado
=PROCH("Eixos";
Procura "Eixos" na linha 1 e devolve o valor da linha 2
A1:C4; 2;
que está na mesma coluna (coluna A).
4
VERDADEIRO) =PROCH("Rolamentos
Procura "Rolamentos" na linha 1 e devolve o valor da
"; A1:C4;3; FALSO)
linha 3 que está na mesma coluna (coluna B).
=PROCH("R"; A1:C4;3;
Procura "R" na linha 1 e devolve o valor da linha 3 que
VERDADEIRO)
está na mesma coluna. Como não é encontrada uma correspondência exata de "R", é utilizado o valor maior
7
5
na linha 1 menor que "R": "Eixos," na coluna A. =PROCH("Parafusos";
Procura "Parafusos" na linha 1 e devolve o valor da
A1:C4; 4)
linha 4 que está na mesma coluna (coluna C).
=PROCH(3;
Procura o número 3 na constante da matriz de três
{1;2;3;"a";"b";"c";"d";
linhas e devolve o valor da linha 2 na mesma coluna
"e";"f"}, VERDADEIRO)
2, (neste caso, terceira). Existem três linhas de valores na constante da matriz, cada uma delas separada por ponto e vírgula (;). Como "c" está na linha 2 e na mesma coluna que 3, é devolvido "c".
14
11
c
Função
Descrição
Função ENDEREÇO
Devolve uma referência a uma única célula numa folha de cálculo como texto
Função ÁREAS
Devolve o número de áreas numa referência
Função SELECCIONAR
Seleciona um valor a partir de uma lista de valores
Função COL
Devolve o número da coluna de uma referência
Função COLS
Devolve o número de colunas numa referência
Função FÓRMULA.TEXTO
Devolve a fórmula da referência especificada como texto
Função OBTERDADOSDIN
Devolve dados armazenados num relatório de Tabela Dinâmica
Função PROCH
Procura na linha superior de uma matriz e devolve o valor da célula indicada
Função HIPERLIGAÇÃO
Cria um atalho ou hiperligação que abre um documento armazenado num servidor de rede, numa intranet ou na Internet
Função ÍNDICE
Utiliza um índice para escolher um valor de uma referência ou de uma matriz
Função INDIRETO
Devolve uma referência indicada por um valor de texto
Função PROC
Procura valores num vetor ou numa matriz
Função CORRESP
Procura valores numa referência ou numa matriz
Função DESLOCAMENTO
Devolve o deslocamento de referência de uma determinada referência
Função LIN
Devolve o número da linha de uma referência
Função LINS
Devolve o número de linhas numa referência
Função RTD
Devolve dados em tempo real de um programa que suporte a automatização COM
Função TRANSPOR
Devolve a transposição de uma matriz
Função PROCV
Procura na primeira coluna de uma matriz e percorre a linha para devolver o valor de uma célula
15