Como usar funções de pesquisa no excel?

arquivos de amostra

1. Função ENDEREÇO

A função ADDRESS retorna uma referência de célula válida com base no endereço da coluna e da linha. Em termos simples, você pode criar o endereço de uma célula usando o número da linha e o número da coluna.

Sintaxe

ENDEREÇO(núm_linha,núm_coluna,núm_abs,A1,texto_folha)

Argumentos

• row_num: um número para especificar o número da linha.
• column_num: um número para especificar o número da coluna.
• [abs_num]: Tipo de referência.
• [A1]: estilo de referência.
• [sheet_text]: um valor de texto como o nome da planilha.

Comentários

• Por padrão, a função ADDRESS retorna uma referência absoluta no resultado.

Exemplo

No exemplo abaixo, usamos argumentos diferentes para obter todos os tipos de resultados.

Com estilo de referência R1C1 :

• Referência relativa.
• Referência relativa de linha e referência absoluta de coluna.
• Referência absoluta de linha e coluna relativa.
• Referência absoluta.

Com estilo de referência A1:

• Referência relativa.
• Referência relativa de linha e referência absoluta de coluna.
• Referência absoluta de linha e coluna relativa.
• Referência absoluta.

2. Função ZONAS

A função AREAS retorna um número que representa o número de intervalos na referência que você especificou. Em termos simples, na verdade conta as diferentes áreas da planilha que você referenciou na função.

sintaxe

CAMPOS (referência)

Argumentos

• referência: uma referência a uma célula ou intervalo de células.

Comentários

• A referência pode ser uma célula, um intervalo de células ou um intervalo nomeado.
• Se quiser fazer referência a várias referências de células, você deverá colocar todas essas referências entre vários conjuntos de parênteses e usar vírgulas para separar cada referência das outras.

Exemplo

No exemplo abaixo, usamos a função zones para obter a referência numérica em um intervalo nomeado.

Como você pode ver, existem três colunas no intervalo e retornou 3 no resultado.

3. ESCOLHA a função

A função SELECT retorna um valor da lista de valores com base no número da posição especificada. Em termos simples, procura um valor numa lista com base na sua posição e devolve-o no resultado.

Sintaxe

SELECIONE(num_índice,valor1,valor2,…)

Argumentos

• index_num: número usado para especificar a posição do valor na lista.
• valor1: um intervalo de células ou valor de entrada que você pode escolher.
• [valor2]: um intervalo de células ou valor de entrada que você pode escolher.

Comentários

• Você pode fazer referência a uma célula ou também inserir valores diretamente na função.

Exemplo

No exemplo abaixo, usamos a função CHOOSE com uma lista suspensa para calcular quatro coisas diferentes (soma, média, máximo e mix). Então usamos a fórmula abaixo para calcular as quatro coisas:

=ESCOLHER(PROCV(K2,Q1:R4,2,FALSO),SOMA(O2:O9),MÉDIA(O2:O9),MÁX(O2:O9),MIN(O2:O9))

Temos esta tabelinha com os nomes dos quatro cálculos que queremos e um número de série para cada um na célula correspondente.

Depois disso, temos uma lista suspensa para todos os quatro cálculos. Agora, para obter o número de índice na função de escolha desta pequena tabela, temos uma fórmula de pesquisa que retornará o número de série de acordo com o valor selecionado na lista suspensa.

E em vez de valores, usamos quatro fórmulas para 4 cálculos diferentes.

4. Função COLUNA

A função COLUMN retorna o número da coluna para a referência de célula fornecida. Como você sabe, cada referência de célula consiste em um número de coluna e um número de linha. Então ele pega o número da coluna e o retorna no resultado.

Sintaxe

COLUNA([referência])

Argumentos

• referência: uma referência de célula para a qual você deseja obter o número da coluna.

Comentários

• Você não pode fazer referência a múltiplas referências.
• Se você estiver se referindo a um array, a função de coluna também retornará os números das colunas em um array.
• Se você estiver se referindo a um intervalo de múltiplas células, ele retornará o número da coluna da célula mais à esquerda. Por exemplo, se você se referir ao intervalo A1:C10, ele retornará o número da coluna da célula A1.
• Se você pular a especificação de uma referência, ele retornará o número da coluna da célula atual.

Exemplo

No exemplo abaixo, usamos COLUMN para obter o número da coluna da célula A1.

Como já mencionei, se você ignorar a referência da célula, o número da coluna da célula atual será retornado. No exemplo abaixo, usamos COLUMN para criar um cabeçalho com números de série.

5. Função COLUNAS

A função COLUMNS retorna o número de colunas referenciadas na referência fornecida. Em termos simples, conta o número de colunas no intervalo fornecido e retorna esse número.

Sintaxe

COLUNAS(tabela)

Argumentos

• tabela: uma tabela ou intervalo de células do qual você deseja obter o número de colunas.

Comentários

• Você também pode usar um intervalo nomeado.
• A função COLUMNS não se preocupa com valores nas células, ela simplesmente retornará o número de colunas de uma referência.

Exemplo

No exemplo abaixo, usamos COLUMN para obter o número de colunas no intervalo A1:F1.

6. Função FORMULATEXT

A função FORMULATEXT retorna a fórmula da célula referenciada. E se não houver nenhuma fórmula na célula referenciada, um valor ou um espaço em branco, ele retornará um #N/A.

Sintaxe

TEXTFORMULA (referência)

Argumentos

• referência: a referência da célula da qual você deseja obter a fórmula como texto.

Comentários

• Se você estiver fazendo referência a outra pasta de trabalho, essa pasta de trabalho deverá estar aberta, caso contrário, não exibirá a fórmula.
• Se você estiver se referindo a um intervalo com mais de uma célula, ele retornará a fórmula para a célula superior esquerda do intervalo fornecido.
• Ele retornará um valor de erro “#N/A” se a célula que você está usando como referência não contiver nenhuma fórmula, tiver uma fórmula com mais de 8.192 caracteres, uma célula estiver protegida ou uma pasta de trabalho externa não estiver aberta.
• Se você fizer referência a duas células em uma referência circular, os resultados de ambas serão retornados.

Exemplo

No exemplo abaixo, usamos texto de fórmula com diferentes tipos de referência. Quando você se refere a uma célula que não possui fórmula, ela retornará o valor de erro “#N/A”.

7. Função HSEARCH

A função HLOOKUP procura um valor na linha superior de uma tabela e retorna o valor na mesma coluna do valor correspondente usando o número de índice. Em termos simples, ele pesquisa horizontalmente.

Sintaxe

PROCH(valor_procurado, matriz_tabela, núm_índice_linha, [procura_intervalo])

Argumentos

• lookup_value: o valor que você deseja procurar.
• table_array: a tabela de dados ou matriz da qual você deseja encontrar o valor.
• row_index_num: um valor numérico que representa um número de linhas abaixo da linha superior da qual você deseja obter o valor. Por exemplo, se você especificar 2 e seu valor de pesquisa estiver em A10 na tabela de dados, ele retornará o valor na célula B10.
• [range_lookup]: um valor lógico para especificar o tipo de pesquisa. Se você quiser fazer uma pesquisa de correspondência exata, use FALSE e se quiser fazer uma pesquisa de correspondência não exata, use TRUE (padrão).

Comentários

• Você pode usar curingas.
• Você pode fazer correspondência exata e correspondência difusa.
• Ao realizar uma correspondência difusa, certifique-se de classificar os dados em ordem crescente da esquerda para a direita e, se os dados não estiverem em ordem crescente, um resultado impreciso será retornado.
• Se range_lookup for verdadeiro ou omitido, ele executará uma correspondência não exata, mas retornará uma correspondência exata se o valor de pesquisa existir no intervalo de pesquisa.
• Se range_lookup for verdadeiro ou omitido e o valor de pesquisa não estiver no intervalo de pesquisa, ele retornará o valor mais próximo que for menor que o valor de pesquisa.
• Se range_lookup for falso, não há necessidade de classificar o intervalo de dados.

Exemplo

No exemplo abaixo, usamos a função HLOOKUP com MATCH para criar uma fórmula dinâmica e, em seguida, usamos uma lista suspensa para alterar o valor de pesquisa da célula.

O nome do campo da célula C7 é usado como valor de pesquisa. Intervalo B1: F5 como array de tabela e para row_index_num usamos a função match para obter o número da linha.

Sempre que você alterar o valor na célula C9, ele retornará o número da linha da tabela. Você não precisa mudar sua fórmula repetidamente. Basta alterar os valores no menu suspenso e você obterá um valor para isso.

8. Função HIPERLINK

A função HYPERLINK retorna uma string com um hiperlink anexado a ela. Em palavras simples, como a opção HYPERLINK que você tem no Excel, a função HYPERLINK ajuda a criar um hiperlink.

Sintaxe

HYPERLINK(link_location,[nome_amigável])

Argumentos

• Link_Location: o local ao qual você deseja adicionar um HYPERLINK. Pode ainda ser dividido em dois termos.
  1. link: pode ser o endereço de uma célula ou intervalo de células na mesma planilha ou em qualquer outra planilha ou pasta de trabalho. Também podemos vincular um marcador de um documento do Word.
  2. localização: pode ser um link para um disco rígido, um servidor usando o caminho UNC ou qualquer URL da Internet ou intranet. (No Excel online, você só pode usar o endereço da web para a função HYPERLINK). Você pode inserir um link para a função inserindo-o como texto entre aspas ou referenciando uma célula que contém o link como texto. Certifique-se de usar “HTTPS: //” antes de um endereço da web.
• [friendly_name]: Esta é uma parte opcional deste recurso. Ele atua como a face do link de conexão.
  1. Você pode usar qualquer tipo de texto, número ou ambos.
  2. Você também está se referindo a uma célula que contém o nome amigável.
  3. Se você ignorá-lo, a função usará o endereço do link para exibir.
  4. Se friendly_name retornar um erro, a função exibirá um erro.

Comentários

• Vincular um arquivo salvo a um endereço da web: você pode usar um arquivo salvo em um endereço da web. Isso nos ajuda a compartilhar o arquivo com eficiência.
• Vincular um arquivo salvo em um disco rígido: Você também pode usar esta função ao trabalhar offline. Você pode vincular um arquivo armazenado em seu disco rígido e acessá-lo por meio de uma única planilha do Excel, sem necessidade de ir a cada pasta para abri-los.
• Vincular arquivo de documento do Word: Este também é um recurso incrível do recurso HYPERLINK. Você pode vincular um arquivo de documento do Word ou um local específico em um arquivo de documento do Word usando um marcador.
• Vincular um arquivo sem usar um nome amigável: se desejar exibir o link real para o arquivo ou local para o usuário. Nesta situação, basta ignorar a declaração de nome amigável na função HYPERLINK.

9. Função INDIRETA

A função INDIRETO retorna uma referência válida de uma sequência de texto que representa uma referência de célula. Em termos simples, você pode se referir a um intervalo de células usando o endereço da célula como valor de texto.

Sintaxe

INDIRETO(texto_ref, [a1])

Argumentos

• ref_text: texto que representa o endereço de uma célula, o endereço de um intervalo de células, um intervalo nomeado ou o nome de uma tabela. Por exemplo, A1, B10:B20 ou MyRange.
• [a1]: Um número ou valor booleano para representar o tipo de referência de célula especificado em ref_text. Por exemplo, se quiser usar o estilo de referência A1, use TRUE ou 1, e se quiser usar o estilo de referência R1C1, use FALSE ou 0 para o estilo de referência R1C. E se você não especificar o tipo de referência da célula, ele usará o estilo A1 por padrão.

Comentários

• Quando você tiver referenciado outra pasta de trabalho, essa pasta de trabalho deverá ser aberta.
• Se você inserir uma linha ou coluna no intervalo referenciado, o INDIRETO não atualizará essa referência.
• Se quiser inserir texto diretamente na função, você deve colocá-lo entre aspas duplas ou também pode referenciar uma célula que contém o texto que deseja usar como referência.

Exemplo

1. Referência a outra planilha

Você também pode consultar outra planilha usando INDIRETO e precisa inserir o nome da planilha lá. No exemplo abaixo, usamos a função indireta para referenciar outra planilha e ter o nome da planilha na célula A2 e a referência da célula na célula B2.

Na célula C2, usamos a seguinte fórmula para combinar o texto.

=INDIRETO(“’”&A2&”’!”&B2)

Essa combinação cria um texto que é usado pela função INDIRETO para se referir à célula A1 da planilha1 e o melhor é que quando você altera o nome da planilha ou o endereço da célula, a referência muda automaticamente.

A célula A1 da “Planilha1” possui o valor “Sim” e por isso o indireto retorna o valor “Sim”.

2. Referência a outra pasta de trabalho

Você também pode consultar outra pasta de trabalho, da mesma forma que fizemos para outra planilha. Tudo o que você precisa fazer é simplesmente adicionar um nome de pasta de trabalho em seu texto para usar como referência.

No exemplo acima, usamos a seguinte fórmula para obter o valor da célula A1 da pasta de trabalho “Livro1”.

=INDIRETO(“[“&A2&”]”&B2&”!”&C2)

Como temos o nome da pasta de trabalho na célula “A2”, o nome da planilha na célula “B2” e o nome da célula na célula “C2”. Nós os combinamos para usar como texto de entrada em uma função indireta.

Nota: Ao combinar uma referência de célula como texto, certifique-se de seguir a estrutura de referência correta.

3. Usando intervalos nomeados

Sim, você também pode consultar um intervalo nomeado usando a função indireta. É simples. Depois de criar um intervalo nomeado, você deverá inseri-lo como texto em INDIRETO.

No exemplo acima, temos uma lista suspensa na célula E1 que contém uma lista de intervalos nomeados, e na célula E2 usamos esse nome. Como o intervalo B2:B5 é denominado “Quantidade” e o intervalo C2:C5 é denominado “Quantidade”.

Quando você seleciona uma quantidade na lista suspensa, a função indireta faz referência instantaneamente ao intervalo nomeado. E ao selecionar o valor no menu suspenso, você terá a soma do intervalo de células C2:C5.

11. Função PESQUISA

A função SEARCH retorna um valor (que você está procurando) de uma linha, coluna ou tabela . Em termos simples, você pode pesquisar um valor e SEARCH retornará esse valor se estiver naquela linha, coluna ou tabela.

Sintaxe

PESQUISA(valor, intervalo_pesquisa, [intervalo_resultado])

Existem dois tipos de funções SEARCH.

• forma vetorial
• Formulário de tabela

Argumentos

• valor: o valor que você deseja pesquisar em uma coluna ou linha.
• lookup_range: a coluna ou linha na qual você deseja pesquisar o valor.
• [result_range]: a coluna ou linha da qual você deseja retornar um valor. Este é um argumento opcional.

Comentários

• Em vez de usar o formato de array, é melhor usar VLOOKUP ou HLOOKUP.

12. Função MATCH

A função MATCH retorna o número do índice de um valor de array. Em termos simples, a função MATCH procura um valor na lista e retorna o número da posição desse valor na lista.

Sintaxe

MATCH (valor_procurado, array_procurado, [tipo_correspondência])

Argumentos

• lookup_value : o valor cuja posição você deseja obter em uma lista de valores.
• lookup_array : intervalo de células ou matriz contém valores.
• [match_type] : o número (-1, 0 e 1) para especificar como o Excel pesquisa o valor na lista de valores.
  1. Se você usar 1, retornará o maior valor igual ou menor que o valor pesquisado. Os valores da lista devem ser ordenados em ordem crescente.
  2. Se você usar -1, retornará o menor valor igual ou maior que o valor pesquisado. Os valores da lista devem ser ordenados em ordem crescente.
  3. Se você usar 0, retornará a correspondência exata da lista.

Comentários

• Você pode usar curingas .
• Se não houver nenhum valor correspondente na lista, retornará #N/A.
• A função de correspondência não diferencia maiúsculas de minúsculas.

Exemplo

No exemplo abaixo, usamos 1 como tipo de correspondência e procuramos o valor 5.

Como já mencionei, se você usar 1 no tipo de correspondência, ele retornará o maior valor igual ou menor que o valor da pesquisa. Em toda a lista existem 3 valores menores que 5 e 4 é o mais alto.

Por isso no resultado retornou 3 que é a posição do valor 4.

13. Função de mudança

A função OFFSET retorna uma referência a um intervalo que está a um número específico de linhas e colunas de uma célula ou intervalo de células. Em termos simples, você pode se referir a uma célula ou intervalo de células usando linhas e colunas começando em uma célula inicial.

Sintaxe

OFFSET(referência, linhas, colunas, [altura], [largura])

Argumentos

• referência : a referência a partir da qual você deseja iniciar o deslocamento. Pode ser uma célula ou um intervalo de células adjacentes.
• linhas : o número de linhas que informam ao OFFSET para subir ou descer da referência. Para descer você precisa de um número positivo e para subir você precisa de um número negativo.
• cols : O número de colunas diz ao OFFSET para se mover para a esquerda ou para a direita da referência. Para ir para a direita você precisa de um número positivo e para ir para a esquerda você precisa de um número negativo.
• [height] : um número para especificar quais linhas incluir na referência.
• [largura]: Um número para especificar quais colunas incluir na referência.

Comentários

• OFFSET é uma função “volátil”, ela recalcula toda vez que há uma alteração em uma planilha.
• Ele exibe o #REF! valor de erro se o deslocamento estiver fora da borda da planilha.
• Se a altura ou a largura forem omitidas, a altura e a largura de referência serão usadas.

Exemplo

No exemplo abaixo, usamos SUM com OFFSET para criar um intervalo dinâmico que soma os valores de todos os meses de um determinado produto.

14. Função LINHA

A função ROW retorna o número da linha da célula referenciada. Em palavras simples, com a função ROW você pode obter o número da linha de uma célula e se não estiver se referindo a nenhuma célula, ela retorna o número da linha da célula onde você a inseriu.

Sintaxe

LINHA([referência])

Argumentos

• referência: uma referência de célula ou intervalo de células cujo número de linha você deseja verificar.

Comentários

• Incluirá todos os tipos de planilhas (folha de gráfico, planilha ou planilha de macro).
• Você pode consultar fólios mesmo que estejam visíveis, ocultos ou muito ocultos.
• Se você não especificar nenhum valor na função, você receberá o número da planilha na qual aplicou a função.
• Se você especificar um nome de planilha inválido, ele retornará #N/A.
• Se você especificar uma referência de planilha inválida, um #REF! será retornado.

Exemplo

No exemplo abaixo, usamos a função de linha para verificar o número da linha da mesma célula onde usamos a função.

No exemplo abaixo, referenciamos outra célula para obter o número da linha dessa célula.

Você pode usar a função de linha para criar uma lista de números de série em sua planilha. Tudo o que você precisa fazer é simplesmente inserir funções de linha em uma célula e arrastá-la para a célula onde deseja adicionar os números de série.

15. Função LINHAS

A função ROWS retorna o número de linhas no intervalo referenciado. Em palavras simples, com a função ROWS, você pode contar o número de linhas no intervalo ao qual se referiu.

Sintaxe

LINHAS (tabela)

Argumentos

• array: uma referência de célula ou array para verificar o número de linhas.

Comentários

• Você também pode usar um intervalo nomeado.
• Não se preocupa com valores nas células, simplesmente retornará o número de linhas de uma referência.

Exemplo

No exemplo abaixo, referenciamos um intervalo vertical de 10 células e retornou 10 no resultado porque o intervalo possui 10 linhas.

16. Função TRANSPOR

A função TRANSPOSE altera a orientação de um intervalo. Em palavras simples, usando esta função você pode alterar os dados de uma linha para uma coluna e de uma coluna para uma linha.

Sintaxe

TRANSPOR (tabela)

Argumentos

• array: um array ou intervalo que você deseja transpor.

Comentários

• Você precisa aplicar TRANSPOSE como uma função de array, usando o mesmo número de células que possui em seu intervalo de origem pressionando Ctrl+Shift+Enter.
• Se você selecionar células menores que o intervalo de origem, os dados serão transpostos apenas para essas células.

Exemplo

Aqui precisamos transpor os dados do intervalo B2:D4 para o intervalo G2 a I4:

Para isso, primeiro precisamos ir até a célula G2 e selecionar o intervalo de células até I4.

Em seguida, insira (=TRANSPOSE(B2:D4)) na célula G2 e pressione Ctrl+Shift+Enter.

TRANSPOSE converterá dados de linhas em colunas, e a fórmula que aplicamos é uma fórmula de matriz, você não pode alterar uma única célula dela.