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).

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

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.

 

 

Nota :

+ Se o array tiver muitas linhas e mais de uma coluna e apenas row_num ou column_num for usado, o índice retornará um array de toda a linha ou coluna na array.

+ Row_num (obrigatório, a menos que column_num esteja presente) : Seleciona a linha na matriz da qual retornar um valor. Se row_num for omitido, column_num será solicitado.

+ Coluna_num (opcional) : Selecione a coluna na matriz da qual retornar um valor. Se column_num for omitido, row_num será solicitado.

 

 

 

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).

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

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).

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

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 . .

 

 

Nota :

Referências externas não são suportadas no Excel Online.

+ Este comportamento é diferente das versões do Excel anteriores ao Microsoft Office Excel 2007, que irá ignorar o limite excedente e retornar um valor.

 

 

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).

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

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.

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

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).

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

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).

 

 

 

Nota :

+ Link_location pode ser uma sequência de texto entre aspas ou uma referência a uma célula que contém um link como uma sequência de texto.

+ Friendly_name pode ser um valor, string de texto, nome ou célula contendo texto ou valor de salto. Se friendly_name retornar um valor de erro (por exemplo #VALUE!), A célula exibe um erro em vez do texto de salto.

 

 

 

 

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).

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

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

Sign up and earn $1000 a day ⋙

Leave a Comment

Copie música do computador para o iPhone usando 3uTools

Copie música do computador para o iPhone usando 3uTools

Você pode copiar música do computador para o iPhone rapidamente com a ferramenta 3uTools.

Importar contatos do sim para Samsung Galaxy J7 Prime

Importar contatos do sim para Samsung Galaxy J7 Prime

Aprenda a <strong>importar contatos do SIM para o telefone</strong> Samsung Galaxy J7 Prime com nosso guia passo a passo fácil e rápido.

Instruções sobre como usar e remover a pulseira de relógio de pulso corretamente

Instruções sobre como usar e remover a pulseira de relógio de pulso corretamente

Uma das principais coisas a se ter em mente ao usar um relógio de pulso é como usá-lo e removê-lo adequadamente para um uso prolongado. Aqui estão dicas essenciais para fazer isso corretamente.

Deve usar um relógio solto ou apertado, como ele se encaixa?

Deve usar um relógio solto ou apertado, como ele se encaixa?

Depois de descobrir as informações, a escolha de comprar um relógio está completa. Neste artigo, abordamos a dúvida: Como devo usar um relógio corretamente?

5 etapas para alterar a vibração do botão Home no iPhone 7, 7 Plus

5 etapas para alterar a vibração do botão Home no iPhone 7, 7 Plus

Descubra como alterar a vibração do botão Home no iPhone 7 e 7 Plus com este guia prático e útil.

Como instalar o desbloqueio por impressão digital no Samsung Galaxy J7 Prime mais rápido

Como instalar o desbloqueio por impressão digital no Samsung Galaxy J7 Prime mais rápido

Aprenda a instalar e habilitar o desbloqueio por impressão digital no Samsung Galaxy J7 Prime para maior segurança e conveniência.

Configuração de brilho da tela do Samsung Galaxy J7

Configuração de brilho da tela do Samsung Galaxy J7

A configuração de brilho da tela do Samsung Galaxy J7 ajuda a ajustar a hora em que o dispositivo desligará automaticamente quando não estiver em uso.

3 etapas rápidas para iniciar a câmera no Samsung Galaxy J7 Prime

3 etapas rápidas para iniciar a câmera no Samsung Galaxy J7 Prime

Aprenda a abrir rapidamente a câmera no Samsung Galaxy J7 Prime com nosso guia prático. Capture momentos incríveis sem complicações!

O que é a tecnologia Virus Doctor nas geladeiras Samsung? Qual é o benefício?

O que é a tecnologia Virus Doctor nas geladeiras Samsung? Qual é o benefício?

A tecnologia Virus Doctor da Samsung transforma a maneira como você percebe a qualidade do ar em sua casa, proporcionando ambientes mais saudáveis e confortáveis. Descubra seus benefícios e funcionamento.

Os 7 melhores softwares de emulador iOS em computadores Windows que você deve usar

Os 7 melhores softwares de emulador iOS em computadores Windows que você deve usar

Descubra os melhores emuladores de iOS para Windows, incluindo iPadian, Appetize.Io e outros, que oferecem funcionalidades para desenvolvedores e usuários em geral.

Corrigir erro de conexão do Samsung com computador

Corrigir erro de conexão do Samsung com computador

Encontre soluções eficazes para o erro de conexão do telefone Samsung com o computador. Este guia fornece passos práticos para facilitar a transferência de dados entre os dispositivos.

O que é iPhone CPO? Devo comprar? Instruções sobre como identificar com precisão

O que é iPhone CPO? Devo comprar? Instruções sobre como identificar com precisão

Você sabia que com os telefones iPhone, além do New Brand (100% novinho), a Apple também oferece outros tipos de iPhones a preços mais baixos, como o iPhone Certified Pre-Owned (CPO)? Descubra tudo sobre essa opção e como identificá-la!

Código KH / A do iPhone de que país? Feito onde? Use bem?

Código KH / A do iPhone de que país? Feito onde? Use bem?

Através do código KH / A, aprenda sobre a origem e a qualidade dos iPhones. Entenda se o modelo é de boa qualidade e descubra mais informações.

Código TA / A do iPhone de que país? Feito onde? Use bem?

Código TA / A do iPhone de que país? Feito onde? Use bem?

Cada iPhone possui seu próprio código de origem. Se o seu iPhone tem um código TA / A e você está se perguntando em que país é, leia o artigo abaixo para obter a resposta!

O que é a câmera infravermelha em um laptop?

O que é a câmera infravermelha em um laptop?

Reconhecer o rosto com a câmera IR em laptops como Asus aumenta a segurança do Windows Hello. Descubra como funciona a câmera infravermelha!