Como usar VLOOKUP, INDEX, ... no Excel você deve saber

As funções VLOOKUP, INDEX, MATCH, ... são funções bastante complexas, mas são eficazes na mineração de dados no Excel . Neste artigo, irei guiá-lo em detalhes como usar VLOOKUP e funções relacionadas com eficácia.

1. Função VLOOKUP

Uso : encontra um valor especificado na primeira coluna e extrai dados correspondentes da mesma linha em outra coluna. Esta é uma das funções mais populares e fornece excelente suporte para tarefas complexas de escritório.

Fórmula : = VLOOKUP (lookup_value, table_array, col_index_num, [range_lookup])

Em que :

+ Lookup_value : o valor a ser pesquisado.

+ Table_array : Duas ou mais colunas de dados.

+ Col_index_num : Número de colunas para extrair dados.

+ Range_lookup : Determine se a pesquisa é totalmente precisa (FALSO) ou apenas relativamente precisa (VERDADEIRO ou omitido).

Exemplo : Usamos a função VLOOKUP para encontrar o Bônus de cada funcionário. O valor a ser procurado é Posição da célula C2; A matriz a ser detectada é a matriz F1: G3. Ao inserir a fórmula = VLOOKUP (C2, $ F $ 1: $ G $ 3,2,0) (1) o resultado é STAFF 2000 e ROOMER 5000 (2).

2. Função INDEX

Uso : retorna uma referência a uma célula dentro de uma matriz com base no número de linhas e colunas que você especificar.

Fórmula : = INDEX (matriz, núm_linha, [núm_coluna])

Sendo : Matriz (obrigatório): um intervalo de células ou uma constante de matriz.

Exemplo : Quando precisamos obter o valor da primeira linha, a segunda coluna da tabela, usamos a função INDEX . Array é o array que precisamos detectar é B2: C5; Row_num é a linha 1; Núm_coluna é a coluna 2. Ao inserir a fórmula INDEX (B2: C5,1,2,1) (1), o resultado é "CABEÇA DA SALA" (2).

3. Função MATCH

Uso : pesquisa um valor específico em um intervalo de células e mostra a posição relativa desse valor.

Fórmula : = MATCH (lookup_value, lookup_array, [match_type])

Em que :

+ lookup_value (obrigatório) : o valor que você deseja encontrar na matriz de pesquisa. Alterar o número do valor de pesquisa pode ser um valor (número, texto ou valor lógico) ou uma referência de célula a um número, texto ou valor lógico.

+ lookup_array (obrigatório) : O intervalo de células a serem pesquisadas.

+ match_type (opcional) : Número -1, 0 ou 1. O argumento do tipo de correspondência especifica como o Excel combina o valor de pesquisa com os valores na matriz de pesquisa. O valor padrão para este argumento é 1.

Por exemplo : Quando precisamos saber em que posição CLOTH está, usamos a função MATCH . Ao inserir a fórmula = MATCH (FF2 & "*", "B2: B7", 0) (1), o resultado é 3 (2).

3. Função INDIRETA

Uso : Retorna uma referência de célula ou intervalo especificado por uma string de texto. Use a função INDIRETO quando quiser alterar a referência a uma célula em uma fórmula sem alterar a própria fórmula.

Fórmula : = INDIRETO (ref_text, [a1])

Em que :

+ Ref_text (obrigatório) : Referência a uma célula que contém o estilo de referência A1, o estilo de referência R1C1 ou uma referência à célula como uma string de texto.

+ Se o texto de referência não for uma referência de célula válida, INDIRECT retornará o valor de #REF! Error . .

+ Se ref_text fizer referência a outra pasta de trabalho (uma referência externa), essa pasta de trabalho deve ser aberta. Se a pasta de trabalho de origem não estiver aberta, INDIRECT retornará o valor #REF! Error . .

Por exemplo : quando você só precisa calcular a comissão e deseja arrastar, soltar ou mover a caixa de resultado para outra posição sem alterar a fórmula do dinheiro, usamos INDIRETO . Ao inserir a fórmula

= INDIRETO (“B2”, VERDADEIRO) * INDIRETO (“C2”, VERDADEIRO) (1) dará o resultado 700 * 100 = 70000 (2).

4. Função OFFSET

Uso : retorna a referência a um intervalo por um número especificado de linhas e um número especificado de colunas de uma célula ou intervalo de células. A referência retornada pode ser uma única célula ou um intervalo de células. Você pode especificar o número de linhas e colunas a serem retornadas.

Fórmula : = OFFSET (referência, linhas, colunas, [altura], [largura])

Em que :

+ Referência (obrigatório) : A área de referência na qual você deseja basear a distância de referência. O intervalo de referência deve se referir a uma célula ou a um intervalo de células adjacentes; caso contrário, OFFSET retorna o valor de erro #VALUE !. .

+ Linhas (obrigatório) : O número de linhas, para cima ou para baixo, que você deseja que a célula superior esquerda se refira. As linhas podem ser positivas (o que significa abaixo da referência inicial) ou negativas (o que significa acima da referência inicial).

+ Cols (obrigatório) : Número de colunas, à esquerda ou à direita, que você deseja que a célula no canto superior esquerdo do resultado se refira. As colunas podem ser positivas ou negativas.

+ Height (opcional) : A altura, em número de linhas, que você deseja ter como referência retornada. A altura deve ser um número positivo.

+ Largura (opcional) : A largura, em número de colunas, que você deseja ter como referência retornada. A largura deve ser um número positivo.

Exemplo : usamos OFFSET para localizar valores e, em seguida, usamos a função SUM para calcular a soma. A célula à qual nos referimos é A1; em comparação com A1, as linhas do intervalo de referência estão abaixo de 1 célula, portanto, as linhas são 1; em comparação com A1, o Cols esquerdo tem 1 célula, então Cols é 1; Altura tomamos 3 linhas; Largura, temos 2 colunas. Portanto, o resultado ao somar todos os valores na região é 2025.

5. Função TRANSPOSE

Uso : converte um intervalo horizontal de células em um intervalo vertical e vice-versa, ou seja, converte linhas em colunas e colunas em linhas.

Fórmula : = TRANSPOSE (matriz)

Fazendo

+ Etapa 1 : Selecione células em branco.

+ Etapa 2 : Insira = TRANSPOSE (B2: C4).

+ Etapa 3 : Insira o intervalo de células originais.

+ Etapa 4 : finalmente, pressione CTRL + SHIFT + ENTER.

Exemplo : Quando precisamos converter toda a lista de preços do produto de vertical para horizontal, usamos a função TRANSPOSE com a matriz de valores de B2 a C4. Ao inserir a fórmula = TRANSPOSE (B2: C4) (1) dará o resultado conforme mostrado (2).

6. Função HYPERLINK

Usos : Cria um hiperlink para um documento armazenado na intranet ou na Internet.

Fórmula : = HYPERLINK (link_location, [friendly_name]).

Em que :

Link_location pode se referir a um local em um documento, como uma célula específica ou intervalo nomeado em uma planilha ou pasta de trabalho do Excel, ou a um marcador em um documento do Microsoft Word.

O caminho pode ser para um arquivo armazenado no disco rígido. O caminho também pode ser um caminho de convenção universal de nomenclatura (UNC) no servidor (no Microsoft Excel para Windows).

Exemplo : Links longos e confusos incomodam o usuário. Portanto, use o HYPERLINK para tornar a interface mais fácil de usar. Inserir a fórmula = HYPERLINK (A1, “LINK GOOGLE”) (1) resultará em LINK GOOGLE (2).

O artigo acima orientou VLOOKUP e funções relacionadas. Espero que o artigo acima seja útil para você.