Função DESLOC e CORRESP Excel

149
29429
Faça buscas com a função Desloc e Corresp

Como se sabe, as funções no Excel foram desenvolvidas com o objetivo de facilitarem o trabalho dos usuários em qualquer que seja a tarefa a ser desempenhada.

O que poucas pessoas sabem é que estas funções podem ser combinadas umas com as outras quando se deseja realizar operações de maior complexidade ou ainda quando se pretende criar determinados mecanismos para facilitar ainda mais os trabalhos no Excel. Um exemplo disso são as funções DESLOC e CORRESP. Acompanhe a seguir maiores detalhes sobre o assunto.

Antes de conhecermos melhor o uso das funções desloc e corresp juntas, vamos analisar e compreender a finalidade e o uso de cada uma delas individualmente.

Função Desloc:

A função Desloc faz uma referência para um intervalo que é especificado por linhas e colunas de certa célula ou intervalo de células.

Para melhor compreensão, pode-se pensar na função desloc como um conjunto de coordenadas para se chegar a determinado destino. Neste caso, iremos utilizar um exemplo prático para melhor análise:

Faça buscas com a função Desloc e Corresp

Na imagem acima temos uma planilha com uma célula Início e outra de Fim como se fossem um caminho a ser tracejado para se chegar ao destino. Sendo assim, utilizando a função Desloc, iremos tracejar o caminho para se chegar da célula Início a célula Fim.

Insira a função DESLOC em um local da planilha conforme sintaxe abaixo:

Faça buscas com a função Desloc e Corresp

Ref: Represente o início a ser percorrido;

Lins: Representa o número de linhas a ser percorrida para se chegar ao destino;

Cols: Representa o número de colunas a ser percorrida para se chegar ao destino;

[altura]: Número de linhas que o destino procurado ocupa;

[largura]: Número de colunas que o destino procurado ocupa;

Para o exemplo citado, temos:

ref = célula Início

lins= 11 linhas a serem percorridas

cols= 4 colunas a serem percorridas

Altura= 1

Largura=1

Faça buscas com a função Desloc e Corresp

Ao final da função, tecle Enter e verifique que o resultado será Fim. Ou seja, com a função desloc demos uma rota e um destino para se chegar em determinada célula.

Função Corresp:

O princípio básico para compreensão da função corresp é entender que esta função trabalha buscas em listas de dados, ou seja, nomes, códigos, números entre outros dados em forma de listas.

Para melhor entendimento, vamos utilizar um exemplo prático.

Abaixo temos uma lista de nomes, e queremos saber, por exemplo, em que posição da lista se encontra o nome Aline. Neste caso, utilizando a função corresp, que por sua vez faz uma busca em toda a tabela para encontrar o nome desejado e informar ao usuário sua posição na lista.

Faça buscas com a função Desloc e Corresp

Sendo assim, em qualquer local da planilha, vamos inserir a função corresp cuja sintaxe é:

 Faça buscas com a função Desloc e Corresp

Valor_procurado: Deve-se informar o valor que se deseja procurar, no caso do exemplo citadobasta adicionar entre aspas o nome “Aliene”

Matriz_procurada: Representa toda a lista de nomes, e neste caso basta selecionar toda a tabela.

[tipo_correspondência]: Este campo não é obrigatório, porém pode ser preenchido com -1, 0 ou 1.

-1: o Excel irá procurar um valor semelhante ou aproximado ao desejado, e caso não encontre irá retornar um valor abaixo do valor procurado;

0: irá procurar o valor exato desejado pelo usuário;

1: irá procurar um valor igual ao procurado e não encontrando ele retornará um valor acima do valor procurado;

Em nosso caso, queremos que o Excel encontre o valor exato de nossa busca, deste modo iremos utilizar 0 como tipo de correspondência.

Faça buscas com a função Desloc e Corresp

Ao final da operação basta teclar Enter e conferir o resultado.

Faça buscas com a função Desloc e Corresp

Função Desloc e Corresp

Agora que aprendemos a finalidade e o modo de uso de cada uma das funções apresentadas, vamos utiliza-las em conjunto. Para isso temos o seguinte exemplo para melhor compreensão:

Observe a imagem abaixo onde temos uma planilha de controle de frutas e seus respectivos preços em cada mês. O dono de um mercado pretende alimentar a planilha mensalmente para obter consultas das variações de preços de cada fruta em um trimestre.

Faça buscas com a função Desloc e Corresp

Com base nestas informações desejamos obter a consulta de preços ao inserir em campos diferentes o nome da fruta e o mês desejado conforme imagem abaixo:

Faça buscas com a função Desloc e Corresp

Deste modo, inicialmente criaremos listas suspensas para facilitar o preenchimento dos campos “Fruta” e “Mês”. Acessando a aba Dados>Validação de Dados>Validação de Dados e escolhendo a opção Lista, conforme mostram imagens abaixo:

Faça buscas com a função Desloc e Corresp

Faça buscas com a função Desloc e Corresp

A figura abaixo mostra a fórmula que será inserida na célula B13, que no caso será exibido o preço de acordo com o mês e a fruta selecionada.

Faça buscas com a função Desloc e Corresp

Entendendo a função:

Inicialmente observe as duas funções CORRESP.

A primeira está buscando a posição relativa da fruta que está inserido na célula B11. Por exemplo, a fruta Abacaxi está na posição 1 da matriz Frutas.

A segunda função CORRESP busca a posição relativa do Mês que está inserido na célula B12. Por exemplo, o mês de Janeiro está na posição 1 da matriz Mês.

Deste modo, a função DESLOC terá que basicamente cruzar as informações com base no resultado das duas funções CORRESP.

Por exemplo, se quisermos saber o preço da fruta Laranja no mês de Março temos que compreender que a função DESLOC irá cruzar o resultado obtido nas duas Funções CORRESP conforme mostra abaixo:

Faça buscas com a função Desloc e Corresp

Observações:

Um ponto importante que podemos destacar na aplicação destas funções está na possibilidade de existir valores repetidos na lista.

Por exemplo, digamos que exista duas vezes a fruta Laranja na lista conforme mostra imagem abaixo:

Faça buscas com a função Desloc e Corresp

Deste modo, para definirmos qual será a Laranja que estamos nos referindo na busca, devemos ajustar o tipo de correspondência na função CORRESP que faz a busca de na lista de frutas. Ou seja:

Se inserirmos o tipo de correspondência como zero (0), a função retornará o primeiro na lista que está sendo procurado. No caso, apontaria para a posição 3 na lista. Se inserirmos o tipo de correspondência como um (1), a função retornará o maior valor menor, ou o valor procurado, caso a matriz esteja em ordem crescente. No caso, apontaria para a posição 4 na lista.

Assim, com base no ajuste destes parâmetros é possível determinar qual valor será buscado quando existe nomes repetidos em uma lista. Desta maneira, é notável que com o auxílio das duas funções (Desloc e Corresp) podemos realizar buscas em tabelas, complementando assim as necessidades de cada uma das funções.

Vale ressaltar também a possibilidade de implementar outros outras funções CORRESP dentro da função DESLOC de modo a poder trabalhar com outros campos de busca.

COMPARTILHAR
Artigo anteriorAprenda a fazer uma planilha inteligente com respostas automatizadas
Próximo artigoSaiba como fazer segmentação de dados no Excel
Rafael Ávila
Administrador e turismólogo, apaixonado pelas possibilidades que o Excel dá para gestores, empreendedores e estudantes. É sócio diretor da área de planilhas na LUZ - Planilhas Empresariais (luz.vc) e é o responsável pelo desenvolvimento das mais de 150 planilhas com foco empresarial e pessoal comercializadas em seu site. Também ministra os cursos online de Excel (cursos.luz.vc) e gosta de ajudar milhares de planilheiros por meio do Fórum e Blog da LUZ.

149 COMENTÁRIOS

  1. Olá! A explicação deste post ficou 10. Excelente! Apenas, percebi que na figura onde visualizamos a fórmula para resolução do exemplo tem algumas variações que não foram explicadas. No caso: No primeiro CORRESP, referente ao “lins” do DESLOC, a tabela procurada se encontra com referências absolutas e aonde certamente deveria conter o sinal ” ; ” para a inserção do argumento [altura] existe um sinal ” * “. Bom, no entanto, parece que mesmo com estas variações ou não a fórmula funciona corretamente mas, admito que fiquei com algumas dúvidas por que foram usadas. Abraços!

    • Oi João,

      fico feliz de saber que o post te ajudou e que a fórmula funciona perfeitamente,

      no caso das referências absolutas ou móveis, isso vai variar de fórmula para fórmula. Como nesse caso só utilizamos uma vez, seria indiferente colocar o cifrão $ ou não. Pois isso só vai influenciar no uso da fórmula depois de você arrastar ou copiar e colar a mesma. Em casos que você necessite fazer isso, precisa ficar atento o que terá referências absolutas e o que não terá

      no caso do sinal de *, confesso que tem tanto tempo que escrevemos o post que eu não lembro exatamente o motivo de ter posto o * na fórmula. Aparentemente é um pequeno erro mesmo que pode ser corrigido simplesmente colocando o ;

  2. todos os dias libero ordens de serviços, como faço uma planilha onde eu possa acompanhar quantas ja foram devolvida e quantas ainda esta pendente, isso por tecnicos, me ajudem, pois estou ficando sem controle de quantas O.S estão paradas na mão de cada um.

    • Oi Jucileide, tudo bom?

      Imagino que você consiga criar um relatório utilizando a função CONT.SES. Ela ficaria mais ou menos assim (imaginando que você tenha 2 colunas, uma para dizer se a ordem de serviço foi devolvida ou se está pendente (coluna A) e outra com o nome dos técnicos (coluna B))

      =CONT.SES(A2:A1000;”Devolvida”;B2:B1000;”nome do técnico”)

      assim você pode aplicar essa função para as OS devolvidas. Depois é só criar uma para as OS pendentes e customizar a parte de nome do técnico para ter os nomes utilizados em sua tabela

  3. Olá! Estou, já algum tempo, tentando descobrir como faço para transferir uma lista de números de uma planilha para outra, sendo que, não pode repeti-se números e estes tem que ficar em ordem crescente.

    • Oi John, tudo bom?

      Você pode utilizar a funcionalidade de remover duplicatas (guia DADOS), assim retira os valores repetidos da planilha, depois pode usar uma classificação normal (em FILTROS) para colocar em ordem crescente. Depois é só ir na outra planilha e colocar a referência das células com =Plan1!A1 e assim por diante.

      Essa forma não é muito automática, mas para fazer da forma mais automática possível você teria que usar a função MAIOR ou MENOR com uma condicional SE para dizer que se o número for duplicado ele não aparecer (provavelmente terá que criar uma coluna auxiliar para informar os números duplicados)

    • Utilize o ASAP Utilities (Addon para Excel apenas de 32bits). Ele tem funções pra remover duplicadas de um determinado range de celulas e colunas que você selecionar, alem de muitas outras funções automatizadas.

    • Oi Robson, a DESLOC com CORRESP serve tanto para buscas em colunas como em buscas em linhas. No caso do PROCV a busca é em colunas apenas, para linhas você precisa usar a PROCH. Além disso, o DESLOC permite que você selecione intervalos ao invés de um único valor.

      De maneira geral a DESLOC com CORRESP permite maiores possibilidades

  4. Bom dia Rafael, gostaria de saber como faço para fazer a função DESLOC me retornar uma coluna inteira ( p.Ex: retornar toda coluna C ). Seria usando como a referência toda a coluna A por exemplo? Aí colocaria para linha o valor 0 e para coluna o valor 2 ? Desde já obrigado.

    • Oi Rafael, na verdade a referência é onde o DESLOC começa, as linhas e colunas indicadas mostram para onde a célula vai se deslocar a partir da referência e, por fim, você tem dois campos para falar o quanto quer selecionar. No caso de pegar uma linha inteira teria que marcar o número de linhas que selecionaria e 1 para pegar apenas uma coluna

  5. Boa tarde!
    Uma orientação, por favor!
    Tenho duas colunas. uma contém os nomes, a outra contém os valores. Estão em ordem aleatório e precisa que fique assim. Usando a função MAIOR para a coluna de valor, classifico o 1º, 2º e 3º lugar, porém, tenho dois nomes diferentes com o mesmo valor na posição de 2º lugar, então, preciso de uma fórmula que retorne para o 2º lugar os dois nomes concatenados. Será que existe solução?
    Obrigado!

    Exemplo:
    1º BELTRANO 56
    2º FULANO / CICLANO 49
    3º SUJEITO 37

    • Oi David, não saberia como fazer essa funcionalidade. Uma possibilidade é somar um valor aleatório 0,00000001 e depois 0,00000002 e assim por diante para criar diferenciações entre números iguais e não ter dois valores ocupando um mesmo espaço

  6. Bom dia,

    Uma orientação por favor.

    Tenho um intervalo na planilha, em uma mesma linha, que serão preenchidos gradativamente, dessa forma preciso em outra planilha retornar o último valor da range.

    Exemplo:

    A B C D E F G H
    1 10 20 30 40 50
    2
    3

    Preciso que retorne nesse caso o valor 50, caso venha a preencher a célula F1, traga o valor da F1 e assim sucessivamente.

    Atenciosamente,
    Jeferson

    • Oi Jeferson,

      se essa lista for crescendo sempre e o último valor sempre for o maior, você pode criar uma linha abaixo dessa, com a função ORDEM para cada valor por coluna e na aba qu você quiser retornar o valor, bastará utilizar um PROCV em cima do resultado maior.

      se o último valor, não necessariamente for o maior, eu recomendaria uma macro simples com os seguintes passos.
      – escolhe a célula A2 (onde está o 1)
      – aperta e mantém pressionado o CTRL
      – aperta direcional para o lado direito
      – CTRL + C (para copiar o valor)
      – CTRL + V na célula de destino (para colar o valor)

      Depois é só parar de gravar a macro

    • Da pra fazer usando a função desloc da seguinte maneira (supondo que os números estão sendo digitados na linha 1):

      =DESLOC(A1;0;CONT.VALORES(1:1)-1)

  7. Boa tarde! Gostaria de auxílio para uma situação especifica:

    Tenho duas planilhas uma base que consta código e nome e outra que consta apenas o código. Preciso preencher essa segunda com o nome referente ao código que está disponível na planilha base (que constam as duas informações).

    Qual fórmula usar?

    • Oi Francielle, tudo bom?

      Você pode usar uma validação de dados de lista para puxar os valores dos códigos da primeira planilha,

      para isso basta ir na guia dados > validação de dados > na janela escolher a opção de lista > e marcar a referência da lista que quer usar.

      Se quiser melhorar ainda mais a planilha, você pode usar um PROCV para buscar os valores dos nomes automaticamente de acordo com o código escolhido

  8. Boa tarde amigo, estou fazendo uma tabelinha e estou com o seguinte problema, o DESLOC me retorna uma célula do campo com numeros, mas não consigo trabalhar com estes numeros, fiz o DESLOC na G2 por exemplo e me resultou um numero de onde busquei. quando faço em outra célulaG3( =G2*2) da erro, tem alguma forma de eu conseguir fazer isso? Obrigado

    • Talvez seja algum problema com a formatação da célula. Se a célula G2 estiver com formato de texto, pode gerar o erro de VALOR quando você tenta fazer a multiplicação

    • Oupas…
      Basta definires que o resultado da procura é um número.
      Para isso acrescenta “VALOR.NÚMERO” (isto em português, em brasileiro não sei) ou seja, se queres que G2 seja igual a C2, escreves algo do género =VALOR.NÚMERO(C2).
      O excel passa a saber que essa célula possui um número e não um texto.
      Depois a célula G3 já consegue multiplicar sem problemas.

  9. Amigo, estou com o seguinte problema: Necessito retornar um valor em função de 2 condições horizontais e 1 vertical, conforme a imagem que coloquei abaixo. Haverá 3 campos para preenchimento, sendo 2 deles estáveis e 1 variável. No primeiro campo ele seleciona a primeira referência (A1, B1…) que estão na segunda linha. O segundo campo é pra selecionar a segunda referência (2, 3…). Em função destas 2 primeiras condições, a busca deve ser feita somente na coluna condicionada às duas referências juntas. A busca então procura o valor igual maior (=>) mais próximo do valor preenchido no campo da 3ª condição. Encontrando então este valor, o resultado final da fórmula deverá ser o valor na mesma direção presente na primeira coluna da tabela.

    Para exemplificar o que foi dito acima, a imagem mostra como deveria ser o funcionamento. Conforme preenchido nos campos da lateral direita, “B2” e “3”, a busca somente poderia ser feita na coluna “I” ou 9ª coluna da tabela. No campo da 3ª condição foi preenchido com o valor 35 (que poderá ser qualquer valor de 0 ao infinito, inclusive valores com vírgula), então a fórmula encontra o valor “46” (I13), que é o valor mais próximo acima do valor digitado. Em função deste valor encontrado, a fórmula retorna o valor presente na primeira coluna da tabela e na mesma direção da célula.

    Consegui elaborar uma fórmula utilizando CORRESP, INDICE, SE, LIN e COL, que retorna o exato, porém preciso do valor mais próximo acima. Também não consegui retornar o valor da primeira coluna.

    É possível desenvolver tal fórmula?

  10. Oi Rafael tudo bem, eu estou tentando usar a função Desloc+Corresp no lugar da Procv, só por curiosidade rsrs, e não estou tendo sucesso,ela não retorna os valores corretos segue tela!!!

    Como faço para que ela me traga os valores corretos?

    Desde já Agradeço!

  11. Boa Tarde, estou fazendo uma planilha bem parecida com essa que esta abaixo e vou construir outra aba para poder digitar somente o codigo do produto e ja aparecer todas as especificacoes dele, como o nome, o codigo da materia prima, o nome da materia prima e assim por diante. Voces poderiam me ajudar?

    • Oi Nathalia, basta usar o PROCV buscando o código do produto. Pelo que eu vi você tem códigos iguais. Se for esse o caso, vale a pena usar o CONCATENAR para juntar essas colunas e criar um id único para ser buscado pelo PROCV

  12. Bom dia!
    Estou com um pequeno problema que estou com dificuldade para resolver.
    A questão é a seguinte:
    Tenho uma folha de base de dados:
    Moxico e Lundas CAPENDA C – GERADOR Nº 1
    Moxico e Lundas CAPENDA C – GERADOR Nº 2
    Moxico e Lundas CAPENDA G
    Moxico e Lundas CUANGO – GERADOR Nº 1
    Moxico e Lundas CUANGO – GERADOR Nº 2
    Zaire SOYO – GERADOR Nº 1
    Zaire SOYO – GERADOR Nº 2
    Zaire SOYO C G
    Zaire MBANZA CONGO – GERADOR Nº 1

    Numa outra folha queria que ao fazer uma lista na validação de dados, queria que ao colocar Zaire, aparecesse nas celulas abaixo:
    SOYO – GERADOR Nº 1
    SOYO – GERADOR Nº 2
    SOYO C G
    MBANZA CONGO – GERADOR Nº 1

    • Oi Lucio,

      basta nomear o intervalo dessas células com o nome Zairo e depois utilizar a validação de dados para criar uma lista com esses nomes (Zairo, etc). Depois disso você vai criar uma outra lista, com validação de dados tamb~em, mas agora utilizando a função INDIRETO para a primeira lista, assim terá exatamente a lista condicionada ao item selecionado

  13. Olá, pessoal.
    Preciso de uma ajuda.
    Tenho a planilha abaixo:
    https://uploads.disquscdn.com/images/eb715e2393c7d8284675136da1d27c5a52abeddb649940fff92c037502e192e9.png

    Gostaria de uma fórmula que me retornasse o dia (linha cinza superior), mês e ano (coluna rosa) do maior número.

    Sei que Proc, Desloc e Corresp precisam que informem o número de colunas/linhas distantes da célula ref., porém neste caso não tem como eu saber, pois ela pode estar no dia 1 de janeiro, ou seja, 1 linha e 1 coluna, ou no dia 30 de dezembro…

    Esta planilha contem vários anos … e o maior valor pode mudar a medida que novos eventos acontecem.

    Existe uma maneira?

    Obrigada.

    • Talvez você consiga isso fazendo o uso de algumas fórmulas em conjunto. Não tenho certeza porque estou sem o Excel nesse momento, mas primeiro você precisa descobrir em qual célula está o maior valor de maneira automática. Você pode conseguir isso utilizando a função MAIOR e ORDEM de forma complementar e depois a ENDEREÇO para saber exatamente onde o valor está. Talvez dê certo

  14. Olá, eu preciso criar primeiramente um caixa geral, onde terei uma coluna com a data da movimentação.. preciso colocar em ordem essas datas.. mas que recoloque toda a linha correspondente pra posição adequada.. ou seja.. quero ordenar todas as linhas de acordo com as datas inseridas em uma coluna. tem como?

  15. Bom dia,

    Preciso de ajuda com uma planilha:

    Preciso somar na célula B23, de forma infinita e sempre em um intervalo constante na linha 23 e pulando sempre uma coluna.

    =soma(D23,F23,H23,J23,L23,N23…..)

    Sempre continuando na mesma linha, mas pulando uma coluna.

    Como faço isso?

    Obrigado.

  16. Olá estou com duvida na seguinte planilha:
    Há duas opções na mesma;
    Produtos e categorias.
    Sendo que há produtos da categoria (nacional) e (internacional)
    Abri uma segunda planilha de “pedidos” aplicando na mesma a validação de dados em categorias, permitindo escolher entre “nacional” e “internacional”.
    Porém gostaria que produtos dependesse da categoria, se eu clico em nacional
    aparecer apenas os produtos dessa categoria, e vice e versa.
    Estou usando desloc+ corresp e sem êxito, qual função usar?

  17. Oi Rafael, estou com dificuldade para fazer esta planilha.
    Em uma aba, tenho um vendedor, e em outra aba tenho um totalizador com o valor mensal da venda de todos os vendedores..
    Preciso que, quando eu seleciono o mês e preencho o valor de venda(na aba joão), este valor seja replicado na planilha totalizadora. E quando tiver mais do que uma venda mensal, seja unificado todos os valores na planilha totalizadora, ref ao mês escolhido. https://uploads.disquscdn.com/images/cf6c4269ef2d6d395d9a2ba48936bcc92c1bc82994b158a17ee80e7b237273b1.png https://uploads.disquscdn.com/images/55f408612be981624f6a2f924d2e207b9fc79ff507d1dcf77cecc7ea8ef5c204.png

    Tentei fazer de diversas maneiras, mas não consigo.

    • Não entendi muito bem exatamente o que você quer, mas se você tem em uma aba os valores lançados e em outra quer totalizar, basta utilizar a função SOMASE ou SOMASES.

      A primeira se for só buscar os valores referentes ao João e a segunda se precisa de mais uma variável, como por exemplo o mês

    • Dá para fazer tabela dinâmica e fazer segmentação de dados ou linha do tempo, facilita muito a organização das informações de acordo com a ordem de cada seleção.

  18. Oi Rafael, estou com dificuldade para fazer esta planilha.
    Em uma aba, tenho um vendedor, e em outra conta com um totalizador com o valor mensal da venda de todos os vendedores ..
    Preciso que, quando eu seleciono o dia e preenchimento de valor de venda (na aba joão), este valor Replicado na planilha totalizadora. E quando teve mais de uma faixa mensal, todos os valores na planilha totalizadora, ref ao mês escolhido. https://uploads.disquscdn.com/images/cf6c4269ef2d6d395d9a2ba48936bcc92c1bc82994b158a17ee80e7b237273b1.png https://uploads.disquscdn.com/images/55f408612be981624f6a2f924d2e207b9fc79ff507d1dcf77cecc7ea8ef5c204.png

  19. Olá.

    A função CORRESP., funciona em outra aba? Exemplo: =CORRESP(I11;BD!$B$11:$B$198;0)

    Quero um numero de pedido que será inserido em outra célula (I11), cuja coluna está em outra aba, porém só dá erro..

    • Não vejo razão para não funcionar Rodrigo. Vale a pena verificar os outros argumentos da CORRESP para ver se eles não são a razão do erro. VocÊ pode usar a funcionalidade de verificar a função para tentar encontrar onde o erro está ocorrendo

  20. Olá galera estou precisando de uma ajuda , sou argente de saúde e estou fazendo uma planilha aonde eu possa facilitar meu trabalho. Já tenho um banco de dados completo com número de prontuário, nome etc., porém em outra aba estou construindo um painel de controle aonde eu possa coletar as informações de cada família pelo numero de prontuário que esta no banco de dados. Se eu conseguir mostrar todos os nomes do grupo familiar da para fazer o resto da planilha, mais não estou conseguindo.
    O que estou conseguindo é apenas chamar 2 membros através da seguinte formula :

    Linha Pessoa 1 =PROCV(B8;’Banco de dados’!A11:I700;2;0)
    Linha Pessoa 2 =PROCV(B8;’Banco de dados’!A11:I700;2;1)
    Linha Pessoa 3
    Linha Pessoa 4
    Linha Pessoa 5

    ou seja, se eu colocar por exemplo prontuário numero “2” e a família for composta por 5 membros , ela esta me retornando apenas 2 primeiras pessoas e não as 5 . Estou usando essa formula =PROCV(B8;’Banco de dados’!A11:I700;2;0)
    para o primeiro nome da família como posso fazer para os demais membros ?

    obs no banco de dados , eu coloquei para cada membro o mesmo pronturario !
    ex : 2 nome 1
    2 nome 2
    2 nome 3
    etc

    Estava pensando em usar um único número para o prontuario através do botão mesclar e centralizar porém não sei se pode ser feito assim na hora de fazer a busca.

    nome 1
    2 nome 2
    nome 3
    etc

    Alguém pode me dar uma luz ?
    Desde já agradeço e parabéns pelo site .

    • Você pode mesclar e fazer a busca apenas para a célula mesclada sem problemas, acredito que terá os dados normalmente.

      Outra opção é criar mais PROCVs para buscar os valores de mais campos

    • Oi Marcelo, não entendi se você está tentando arrastar os dados da coluna D ou da coluna K. Vou tentar explicar os 2.

      1 – no caso da coluna D, se você selecionar os valores de 1 a 5 e arrastar, o seguinte será o 6 e assim por diante.

      2 – no caso da coluna K estamos falando de uma fórmula, que no caso só serve para pegar o maior valor (MÁXIMO), por isso os valores se repetem. Se quiser variar do maior, segundo maior e assim por diante precisará usar a função MAIOR

      Espero ter ajudado, abraços

  21. Prezado colega,

    Eu tenho uma lista de nomes (aprox. 300) e uma planilha enorme com milhares de linhas e 8 colunas (nomes estao na primeira coluna). Qual seria a maneira adequada de selecionar na planilha grande, apenas os casos dos nomes da minha lista?

    • Oi Rodrigo, que tipo de seleção você quer fazer? Selecionar mesmo eu não saberia dizer como, mas se você quiser uma forma de destacar os nomes repetidos, pode criar uma coluna auxiliar com uma fórmula de contagem condicional CONT.SE para contar caso aquele nome existir na aba de lista de nomes. Depois disso é só você criar uma formatação condicional para a linha ou célula de nome mudando de cor caso o valor dessa coluna seja diferente de zero

  22. Tenho um problema na qual eu acredito que envolva as funções CORRESP e LIN, mas não sei como montá-la.
    Tenho uma coluna com vários números naturais (mais de 1500) e eu preciso de uma função que busque toda a coluna e me retorne a linha aonde ocorre o último valor pesquisado (por exemplo, a linha que está o último valor “6” digitado). Alguém poderia me dar uma LUZ(rs)!? Obrigado!

    • Supondo que a sua coluna com vários números seja A, no intervalo A1:A1500 e você tenha na célula B1 o último valor digitado, esse 6

      Basta usar =CORRESP(B1;A1:A1500;0)

      • Rafael, antes de mais nada gostaria de agradecer a atenção dispensada! O problema na sua solução é que na coluna A vão ter, além de vários números, algumas palavras ocupando as linhas, bagunçando o índice considerado pelo CORRESP e fazendo confusão com a linha; além disso o CORRESP retorna a posição da 1ª ocorrência, mas eu gostaria da linha da última linha no intervalo!

          • Eu até posso excluir as letras da coluna, diminuindo as ocorrências, mas o problema persiste: Uma fórmula que busque na coluna qual a LINHA aonde ocorre o ÚLTIMO valor. Parece-me que terei que utilizar VBA, ferramenta da qual não tenho NENHUM conhecimento…
            Mesmo assim muito obrigado pela ajuda Rafael!

    • Oi Luis,

      uma maneira bem simples é utilizar o PROCH buscando o valor do mes na matriz dos clientes e usando uma linha auxiliar ao final com os períodos. Ficaria algo como

      =PROCH(B9;D3:I6;4;verdadeiro)

      Nesse caso a fórmula buscaria o valor de janeiro na linha 3 e, ao encontrar o valor aproximado, correria 4 colunas para baixo, chegando na linha 6 que estaria com os valores dos períodos e te daria a resposta

      • Rafael, bom dia!

        Consegui resolver, aplicando a seguinte fórmula na célula A9:

        =SE(E(B9>=ÍNDICE($A$2:$I$4;CORRESP(C9;$A$2:$A$4;0);COL(D$1));B9=ÍNDICE($A$2:$I$4;CORRESP(C9;$A$2:$A$4;0);COL(F$1));B9<=ÍNDICE($A$2:$I$4;CORRESP(C9;$A$2:$A$4;0);COL(G$1)));F$1;H$1))

  23. https://uploads.disquscdn.com/images/cf1ed57fbdc2cd7f4e9601364794bcb4d4da4d043dee73198439227b56637d74.png Bom dia para todos. Estou com um problema e gostaria da ajuda de vocês: Tenho uma planilha na qual alimento todos os dias o valor do “horímetro” da empilhadeira que usamos e também temos a a informação de quando será feita a próxima manutenção. Quero acrescentar em uma célula, uma fórmula que me diga, baseado no dia atual, quanto falta para a próxima manutenção.
    Seria tipo um radar que chamaria a atenção para este valor.
    Segue abaixo o print da tela

    Desde já agradeço a ajuda

    • Você pode usar uma função para buscar o valor da próxima revisão e, baseado nele, retornar o dia em que ela deverá ocorrer.

      Com esse dia, basta criar uma fórmula para subtrair o dia atual – função HOJE() – desse valor. Se for positivo, faltarão a quantidade de dias do resultado. Se for zero, é para fazer a revisão naquele dia e, se for negativo, é que já passou o prazo para a manutenção

    • Oi Paolo, a lógica é criar uma coluna auxiliar à sua coluna de valores (que nesse caso me parecem ser várias, mas como é uma por mês, você pode separá-las se achar necessário). Nessa coluna auxiliar você pega o valor em questão e soma um valor ínfimo, 0,00000001, algo assim. Depois você já tem uma diferenciação mínima para o Excel identificar os valores como valores diferentes.

  24. Olá Rafael,

    Tentei aplicar as orientações na planilha com imagem em anexo, porém nas colunas “T” e “X” onde deve aparecer as dezenas contidas na linha “5” a fórmula “=CORRESP(U12;$B$6:$Z$6;0)” não consegue distinguir a 2ª e 3ª Dezenas, aparecendo somente a primeira da ordem crescente da lista nas duas posições, sendo que na verdade deveria sair assim a lista: 1ª dezena – 24 (18 vezes sorteadas), 2ª dezena – 14 (16 vezes sorteada) e 3ª dezena – 20 (16 vezes sorteada).

    E esse problema se repete sempre quando temos dezenas diferentes com o mesmo numero de vezes sorteadas. Como resolver este problema. https://uploads.disquscdn.com/images/b6e9cae06b1e78f2a2d8459bc2ddc8d45bd4fafad1c2b0d5b263011814c37731.jpg

    • Oi Paolo, realmente algumas funções do Excel acabam tendo limitações quando você tem repetições ou contagens iguais. Nesse caso eu costumo criar uma coluna ou linha auxiliar pegando esses valores e fazendo uma adição ínfima crescente, que não altere o resultado, assim os valores nunca serão iguais e você terá respostas mais consistentes

      • Primeiramente Rafael, muito agradecido por responder-me tão prontamente.
        Entendi sua sugestão e até o fiz mas de forma manual olhando nos resultados na linha 5 e digitando os valores nas colunas “v” e “z”, mas abusando de sua boa vontade e expertise, poderia me dizer como faço isso em fórmula, para que fique automático.

        Desde já agradeço.

  25. Bom dia, preciso da ajuda de Alguém! Sou Policial Civil e trabalho na delegacia de roubos e furtos de veículos, pretendo fazer uma planilha que necessita de alguma função mais complexa para meu nível de conhecimento. Irei listar os veículos que são roubados por dia em uma planilha. Cada dia será uma aba de uma planilha. A planilha constará placa do veículo roubado ou recuperado, bairro, modelo do veículo. Como faço para a medida que as novas planilhas estiverem sendo elaboradas, reconheça que aquela placa que estou digitando já existe em alguma aba? Exemplo: Fiat Stilo deu entrada na planilha como roubado com placa ORA 3212 no dia 11AGO, na elaboração da aba do dia 20AGO, a mesma placa foi digitada. Queria saber se era possível e como faço para que a célula mostre em que ligar essa placa se encontra! Desde já, agradeço

    • Oi Matheus, tudo bom? Como você está utilizando uma série de abas diferentes, fazer essa análise pode ser um pouco mais complicado. Pensando rápido aqui eu imagino que você teria que criar um banco de dados com todas as informações de todas as abas para poder fazer essa análise.

      Não sei qual foi a motivação para criar uma aba por dia, mas eu tentaria fazer uma aba única e adicionar uma coluna a mais para cadastrar os dias. Assim você conseguiria usar o recurso de formatação condicional para destacar duplicatas.

  26. Amigos bom dia! Preciso de ajuda para fazer com que minha planilha sinalize quando uma informação de célula se repita duas vezes, três,quatro, etc. A função de formatação condicional só existe para célula duplicada. É exatamente o que quero, que a célula fique colorida de acordo com a quantidade de vezes que se repete (no caso são endereços), mas quero que mude de cor se repetir, 2, 4 ou 6 vezes, chamando a atenção para endereços que muito se repetem (se repetir duas vezes fica verde, três vezes amarelo, seis vezes vermelho). Não sei se me fiz entender, não quero contar um determinado endereço, quero que quando eu lance algum endereço a planilha já identifique se ele está se repetindo e quantas vezes está se repetindo, dentro das condições que coloquei. Alguém pode me ajudar?? Obg.

    • Oi Vinícius, nesse caso eu acho que você terá que criar uma tabelinha auxiliar com os endereços e uma contagem de quantas vezes cada endereço aparece. Depois, basta criar as formatações sendo aplicadas nas células em questão, estabelecendo cores de acordo com valores definidos por você.

  27. Olá, pode me ajudar
    Já procuro a algum tempo uma maneira de pegar um dado em uma matriz, ou seja, encontrar um dado selecionado em uma tabela na primeira coluna, e o valor nesta linha, cruzando com uma coluna e trazer somente este dado no cruzamento destas coordenadas, linha x coluna.
    Agradeço pela ajuda.

    • Oi Marco, tudo bom? A função que você vai usar vai depender dos dados que você possui. No caso de você ter a matriz e saber (ou ter disponível) os valores da linha e da coluna, pode usar a função ÍNDICE. Se o valor buscado estiver sempre na primeira coluna, poderá usar o PROCV.

  28. Rafael, muito boa a explicação, parabéns. Estou com um problema para adaptar essa solução para um caso que eu preciso, veja se consegue me ajudar. Eu preciso transpor e deslocar, comparando com um valor inicial. Tendo uma planilha como:
    dado 1 a
    dado 1 b
    dado 1 c
    dado 2 q
    dado 2 w
    dado 2 e
    dado 2 r
    dado 3 0
    dado 3 1

    Preciso que ela fique:
    dado 1 a b c
    dado 2 q w e r
    dado 3 0 1

    • Oi Pedro, talvez só com a função TRANSPOR você consiga chegar nesse resultado, mas como essa não é uma função que eu tenha o costume de usar, não saberia te dar o passo a passo. Você pode usar o F1 para buscar a ajuda do Excel e ver como ela funciona para tentar aplicar na sua situação.

  29. Boa tarde

    Gostaria de saber se tem como eu puxar essas duas informações numa única cédula da mesma forma que o espelho ponto de um RH. Não consigo achar uma formula que consiga me auxiliar. Mesmo concatenando, não mostra o resultado que eu desejo

    No qual seria selecionar o funcionários e ele me dar todas as informações que eu preciso do mesmo e no B3 qual eu selecionar ele me passar a informação de outros.

    Poderia me ajudar?”

  30. Bom dia Rafael,

    Adoro suas dicas e gostaria de saber se pode me ajudar. Tenho uma tabela com os seguintes dados:
    Coluna A: Código do procedimento
    Coluna B: Descrição do Procedimento
    Coluna C: Porte do Procedimento
    Coluna D: Valor da UCO

    Como faço para que em uma outra aba ele traga para mim todos os códigos, portes e UCO sempre que na coluna B aparecer o nome “técnica” por exemplo?

    Se puder me ajudar agradeceria muito.

    At.te

    Will

    • Oi Elson, eu pensei em uma maneira diferente de chegar nesse resultado usando PROCV. Nesse caso, você teria que criar uma coluna auxiliar que concatenasse a posição com o estado, assim você teria algo como 1-SP, 2-SP,1-PR, etc nessa coluna (ela deve estar no começo, na posição da coluna A).

      Na tabela dos resultados, você também vai precisar fazer a concatenação do valor da linha 1 com os estados. Assim você conseguirá fazer um PROCV buscando os nomes e valores relacionados a essa concatenação.

      Qualquer coisa, tem um passo a passo nesse post – https://blog.luz.vc/excel/variacoes-da-funcao-procv-no-excel-mais-de-uma-condicao-e-valores-repetidos/

  31. A B C D E F
    Ref Produto Efic. MP Efic. Maq.
    Cod % Cod %
    01 PENTE (*) 112 97 102 85
    02 “VAZIO”
    1- Argumentos: Dependo de outra planilha para trazer os dados-A, B, D e F
    As colunas C e E são preenchidas manualmente e automaticamente as colunas
    mencionadas acima são preenchidas;
    2-Problema : Quando a coluna “B” estiver “VAZIO”, as colunas “C”e “E” também deverão estarem “VAZIO”.
    3-Dependência: As colunas “D” e “F”, fazem a busca através do preenchimento manual das colunas “C” e
    “F”;
    4-Objetivo : Quando a coluna “B” estiver “VAZIO”, as colunas “C” e “E” não poderão ser preenchidas e deverão estar “VAZIOS”.
    Obrigado antecipadamente

  32. Olá, gostei muito do seu texto, entretanto ainda tenho uma dúvida em relação a essa função.
    Eu tenho uma determinada planilha. Nela, ficam o nome dos clientes, algumas informações adicionais, e o valor do contrato. Fiz uma tabela abaixo para exibir na planilha quais os contratos de maior valor, usando a função =MAIOR(). Entretanto, a função só passa o valor do contrato, logo tentei pegar, pelo valor do contrato, encontrar o nome do cliente por =DESLOC(MAIOR()), entretanto não está funcionado. Você tem alguma ideia de o porquê do erro, ou de alguma outra forma para encontrar o nome e informações do contrato de maior valor?

  33. Ola,
    eu gostaria de saber se existe (e aprender se sim) uma maneira de delimitar uma matriz usando uma fórmula. para, por exemplo, poder buscar um procv sempre abaixo da linha célula em questão

    • Oi Fernando, você pode usar a função DESLOC para isso. Supondo que você tenha uma tabela com 1000 linhas e quer começar a partir da linha 300. Nesse caso, você vai usar como referência a primeira linha da tabela e colocar que quer que essa referência seja deslocada 300 linhas para baixo (ideal que esse valor seja dinâmico) e, na própria DESLOC, você pode colocar o tamanho em linhas e colunas da nova matriz

  34. Olá caros Amigos, estou com uma dificuldade e preciso de uma ajuda.

    Então, estou elaborando uma planilha de orçamento de artes correntes e Basicamente ela ficaria assim:

    Tipo do Bueiro | Tipo do Serviço | Preço Unit. | Comprimento

    Na opção Tipo do Bueiro, entraria uma lista suspensa para eu clicar e definir se o tipo é: Simples,Duplo ou Triplo.
    Porém, para o Tipo “Simples” eu tenho uma lista de serviços tais como: Corpo bueiro simples capeado 1,0 x 1,0m – via simples ( e mais uns 20 tipos de serviços a serem cobrados).

    No tipo “Duplo eu tenho: Corpo bueiro duplo capeado 1,0 x 1,0 m – via simples

    e no triplo eu vou ter os serviços correspondente..
    Porém, eu queria fazer o seguinte: Quando eu selecionar/informar na coluna Tipo, e dizer q vai ser “Simples” na coluna de serviço aparecer na lista suspensa apenas os itens do tipo simples. Se eu escolher o “Duplo” só vou poder selecionar os serviços do tipo duplo na coluna de serviços.
    Porque depois eu ainda vou ter de fazer a operação que dos preços x comprimento.

  35. Ola equipe do Luz.
    Estou tentando montar um fórmula que faça o seguinte.

    Trabalho com trade e tenho uma tabela onde cadastro várias informações diariamente das operações que realizo na bolsa, ou seja um mesmo dia pode ter vários registros, em outra planilha eu tenho um relatório usando formula CONT.SE E SOMASES trazendo dados agrupados para saber por mês itens como:

    – Quantas operações efetuei no mês;
    – Quanto tempo fiquei em operação no mês;
    – Quantos LOSS;
    – Quantos GAINS;

    E assim por diante.

    O que “não” estou conseguindo fazer é trazer quantos dias de fato foi operado dentro de um mês, não consigo agrupar dias iguais dentro do mês para gerar um contagem de dias distintos, já consegui fazer isto com tabela dinâmica, mais não com fórmula. Existe como fazer isto com fórmula?

    Desde já Obrigado pelo auxilio

  36. Ola equipe do Luz.
    Estou tentando montar um fórmula que faça o seguinte.

    Trabalho com trade e tenho uma tabela onde cadastro várias informações diariamente das operações que realizo na bolsa, ou seja um mesmo dia pode ter vários registros, em outra planilha eu tenho um relatório usando formula CONT.SE E SOMASES trazendo dados agrupados para saber por mês itens como:

    – Quantas operações efetuei no mês;
    – Quanto tempo fiquei em operação no mês;
    – Quantos LOSS;
    – Quantos GAINS;

    E assim por diante.

    O que “não” estou conseguindo fazer é trazer quantos dias de fato foi operado dentro de um mês, não consigo agrupar dias iguais dentro do mês para gerar um contagem de dias distintos, já consegui fazer isto com tabela dinâmica, mais não com fórmula. Existe como fazer isto com fórmula?

    Tentei montar uma fórmula como esta mais sempre retorna ZERO:

    =SOMARPRODUTO(SUBTOTAL(102;DESLOC(‘OP DIÁRIA’!B8:B1033;LIN(‘OP DIÁRIA’!B8:B1033)-LIN(‘OP DIÁRIA’!B8);;1));–(‘OP DIÁRIA’!B8:B1033>=”01/01/2018″);–(‘OP DIÁRIA’!B8:B1033<="31/01/2018"))

    Desde já Obrigado pelo auxilio

  37. Olá Rafael, estou precisando de uma ajuda, já procurei e não encontrei.
    Tenho uma tabela de dados, gostaria de “encontrar um numero especifico nessa matriz, em uma linha tbm especifica”. Por exemplo: Eu tenho a seguinte matriz abaixo em uma pasta de trabalho e em outra pasta eu preciso buscar o número “10” na linha ‘3’, ou seja, usando o procv eu consigo buscar a linha 3 com a referencia da primeira coluna pelo valor “1602”, por tanto eu precisaria indicar o numero indice da coluna referente o numero 10, que no caso seria a coluna ‘8’.
    Porém nas demais linhas o numero “10” varia de colunas.
    Como eu faço para encontrar um numero especifico que eu quero independente da coluna que ele está, usando procv ou outra função pra definir a linha especifica tambem?

    1600 18/12/2017 2 3 4 5 7 9 13 14 16 17 18 20 22 23 24
    1601 20/12/2017 2 4 6 8 9 10 11 12 13 14 15 18 20 22 25
    1602 22/12/2017 1 3 4 8 9 10 11 13 14 15 17 19 21 23 24
    1603 26/12/2017 1 2 3 4 7 9 10 13 14 16 19 20 21 23 24
    1604 27/12/2017 3 4 5 6 7 8 9 10 12 13 15 19 21 22 24
    1605 29/12/2017 2 4 5 8 9 10 11 14 16 17 18 19 21 23 25
    1606 02/01/2018 1 3 5 7 8 9 10 11 14 15 19 20 22 23 25
    1607 03/01/2018 1 2 3 6 7 8 9 11 12 13 14 15 17 18 24
    1608 05/01/2018 1 2 3 5 6 9 10 14 17 18 20 21 22 23 25
    1609 08/01/2018 5 6 7 8 9 11 13 14 17 18 20 21 23 24 25
    1610 10/01/2018 3 6 7 9 10 11 13 14 16 17 19 20 21 22 24

    • Oi Marcela, você pode usar o CORRESP para buscar onde está um valor correspondente em determinada matriz, nesse caso, provavelmente você terá que usar o DESLOC para definir essa matriz (linha correspondente) de forma dinâmica

    • Oi Bruno, em tese, também funciona no Excel, talvez você esteja escrevendo a fórmula com algum erro e, por isso, não esteja vendo o resultado que gostaria

  38. Boa tarde!

    Excelente texto! Ele me ajudou em uma situação que eu não conseguia prosseguir, mas me travou em outra. Vamos lá:
    Eu possuo uma planilha para apontar defeito em vagões. Basicamente eu coloco o número do vagão e na coluna ao lado eu coloco o componente defeituoso (lista suspensa normal) e na coluna ao lado o subcomponente (que é uma lista suspensa condicionada ao componente). Como possuo vários componentes, no meu jeito antigo de fazer criei nome pra cada um e usei diversas funções SE para validar a lista. O problema é que o campo “Fonte” tem limites de caracteres e não aceitou todos os SE escritos. Com a sua fórmula desloc+corresp eu consegui puxar todos os subcomponentes.

    O problema: Eu gero mais de 1000 defeitos por mês, o que me gera 1000 linhas e 1000 listas suspensas. No modo antigo (quando eu tinha poucos componentes e cabia dentro do campo “Fonte” as funções SE) eu simplesmente arrastava pra baixo e a fórmula na “Fonte” ia se ajustando. Como eu faço com esse seu modo? Teria que criar um nome pra cada célula? No caso teria que criar 1000 nomes? Porque o nome criado é ligado diretamente a uma célula especifica. (no seu caso ele é ligado a célula G4. Se eu quisesse que a célula G5 eu teria que criar outro nome e colocar a célula G5?)

    Preciso muito resolver esse problema!! Desde já, agradeço!

    • Não consegui entender muito bem a sua dúvida e como essas listas são geradas conforme você alimenta a planilha, se puder especificar mais, posso tentar pensar em alguma solução

  39. Boa tarde!

    Quero fazer uma planilha para informar status. Por exemplo, terei 2 colunas: uma coluna de atividades e uma com o progresso das mesmas.

    ATIVIDADE PROGRESSO

    ATIVIDADE1 100%
    ATIVIDADE2 100%
    ATIVIDADE3 50%
    ATIVIDADE4

    Gostaria de elaborar uma célula onde assim que uma atividade esteja com progresso 100%, esta célula informe a atividade da linha de abaixo da completada, caso não esteja 100%, ela deverá informar a atividade que está sendo executada (mesma linha).

    Desde já agradeço!

  40. Bom dia!!!
    Tenho uma planilha de controle de lote e validade e gostaria de aplicar uma regra no excel, porém não venho conseguindo.
    Por padrão, temos que pintar manualmente na cor vermelha a linha cujo item tenha validade inferior a 12 meses, na cor azul validade próxima a 12 meses, na cor roxa os itens que não são padronizados (sem código) e, por fim, na cor verde os itens que não possuam lote e/ou validade na Nota Fiscal.
    Poderia me auxiliar?
    Muitíssimo obrigada.

  41. Trabalho com sistema industrial, todo dia salvo um relatório em CSV e importo para o Access, do Access importo para uma Tabela dinâmica do Excel, na frente dessa tabela inseri parâmetro e duas coluna com fórmulas tendo como números para o cálculo dados da tabela dinâmica, mas como atualizo ela todos os dias direto do Banco de Dado, altera o número de linhas e a fórmula não acompanha automaticamente essas mudanças, ou seja tenho que arrasta ou diminuir essa fórmula de forma manual, queria muito saber usar a função DESLOC para essa tabela.

  42. Olá rafael,

    Gostei muito deste site e aprendi a criar a planilha de pesquisa de preço cruzada com a de frutas e mês. Muito bom. meu interesse é criar uma fórmula onde: 1. Tendo uma lista de municípios (184) somente alguns (espalhados na lista) tem ao lado o numeral “1”, indicandoq ue compareceram. Gostaria de montar uma lista somente com estes que compareceram.

  43. Olá Rafael, procurei soluções em outros lugares, porém não achei o que eu preciso, e ninguém melhor que você para me ajudar, pois a minha dúvida é relacionada à outra fórmula sua, juntamente com esta.

    Eu utilizei a formula deste post seu: https://blog.luz.vc/excel/variacoes-da-funcao-procv-no-excel-mais-de-uma-condicao-e-valores-repetidos/

    Eu tenho uma matriz, que coloco diversas informações, uma delas é um STATUS, e de acordo com o STATUS a informação é copiada para uma outra guia, daquele mesmo status.

    Porem uma destas guias, após a informação ser copiada para la, eu tenho mais duas colunas para inserir outras informações necessárias para aquele status específico. Porem, quando a informação entra na guia, no meio da tabela (tem outras já que ficarão em baixo), estas duas colunas não acompanham e não descem junto.

    Como faço?

    • Oi Denise, não entendi muito bem o que você quis dizer. Para começar, essa função que é uma variação do PROCV é matricial (pode ser que você não tenha adicionado ela apertando CTLR + SHIFT + ENTER). Se não for isso, é necessario compreender quais cálculos deveriam ser feitos

      • Vamos lá, estou colocando um link com imagens da planilha para você entender.

        https://1drv.ms/f/s!AvQLcDxSTPaTcAcT5Sf8by955F4

        Eu tenho uma planilha, com diversas guias (figura 1) onde utilizei da fórmula do PROCV que você postou. Quando mudo o STATUS na Matrix aquela informação é copiada para a guia de referência: Em andamento vai para a guia EM ANDAMENTO.

        Porém quando a aquela linha da Matriz é colocada em status FINALIZADO (figura 2), eu preciso inserir a mão mais duas informações: ABORDAGEM E RESPOSTA (figura 3)

        só que como o exemplo eu coloquei um em status finalizado, que na matriz estava antes do último já, anteriormente finalizado, ai, a planilha desce as informação, MENOS as duas colunas que não são matriciais, a informação que esta nas células 176 O e P na realidade são do dos dados da linha 177, porem não acompanhou.
        Vim neste seu post DESLOC procurando solução para isso, porem não consigo.

        E outra coisa, já aproveitando, você salvou muito com aquela fórmula do PROCV, porem eu gostaria de, se a linha esta em andamento e eu finalizei, eu não precisar ir até a matriz para alterar o status (para não perder a formula da celula), gostaria de poder mexer nesta informação na planilha inteira, entendeu? rs….

  44. Obrigado pelo post. Mas preciso numa lista de nomes que se repetem diariamente…várias vezes na semanas e preciso o valor máximo de alguma variável relacionada a esse nomes. O que faço para conseguir visualizar na célula o nome e o máximo encontrado nas variáveis.
    Obrigado

    • Oi Tiago, para achar o valor máximo você pode usar as funções MAIOR ou MÁXIMO e, depois de encontrar esse valor, basta usar o PROCV para achar o nome relacionado a ele

  45. Oi Rafael, tenho procurado uma resposta para minha questão e não encontro, será que suas fórmulas conseguem elucidar essa questão?
    Em uma planilha com apenas 1 linha e muitas colunas, preciso saber quantas vezes “alguns” números aparecem nessas colunas.
    Ex:
    Célula A1: NOME
    Célula B1: 9
    Célula C1: 30
    Célula D1: 1
    Célula E1: 3
    Célula F1: 0
    Célula G1: 8
    Célula H1: 2
    Célula I1: 40
    etc
    Célula AC1: 2
    Preciso de uma fórmula que me diga quantas vezes, nessa linha única com diversas colunas, aparecem os números 1, 2, 3, 4 e 5.
    Existe uma maneira? rs
    Obrigado

    • Você pode usar o CONT.SE para contar a quantidade de vezes condicionada ao número em questão. Basta criar uma tabela com os números 1,2,3,4 e 5 e depois fazer um CONT.SE para cada número. Supondo que o número 1 esteja em A2, em B2 você colocaria =CONT.SE(B1:AC1;A2)

  46. Rafael, boa tarde!

    Existe alguma formula no excel que se assemelha a contém? Vou dar um exemplo. Tenho na célula A1 Duplicata nº 95281. Eu gostaria de colocar na célula B1 determinado valor com base em uma tabela, sempre que ele encontrasse em A1 “duplicata nº”. Estou fazendo com procv, porém ele procura “Duplicata nº 95281” e não somente “duplicata nº”, aí não dá certo.

    • Você pode usar uma coluna extra com a função ESQUERDA, buscando sempre os 12 caracteres (que corresponde a duplicata No) – assim o procv vai encontrar o valor relacionado ao termo que você quer

  47. Rafael,
    boa tarde! Primeiramente, parabéns pelo trabalho .
    Estou com uma situação que não consigo resolver, gostaria de saber se você poderia me ajudar.
    Dados as informações abaixo:
    PLANILHA I
    Paciente data internaçao
    JOAO 01/01/2018
    MARIA 01/01/2018
    FRANCISCO 02/02/2018
    JOAO 04/03/2018
    MARIA 06/02/2018

    PLANILHA II
    Paciente data alta
    JOAO 06/03/2018
    MARIA 03/01/2018
    FRANCISCO 03/02/2018
    JOAO 05/01/2018
    MARIA 06/02/2018

    Crio uma nova planilha para conciliar as entradas e saidas (internação e alta)
    Paciente data internaçao data alta
    JOAO 01/01/2018 ????????
    MARIA 01/01/2018
    FRANCISCO 02/02/2018
    JOAO 04/03/2018
    MARIA 06/02/2018

    Preciso extrair na planilha II a data de alta mais próxima a data de internação para calcularmos as diárias de determinado atendimento. Só um PROCV não resolve uma vez que temos mais de uma internação em alguns casos. Então preciso de uma formula que me retorne a menor data (maior ou igual a data de internação). Entendeu?
    no caso do joao a resposta seria:

    paciente INTERNAÇÃO ALTA
    JOAO 01/01/2018 05/01/2018

    • Realmente, nesses casos o PROCV sozinho não resolve. Talvez se você usar uma coluna auxiliar para calcular as datas e usar o MENOR pode ser uma alternativa

    • Eu usaria o seguinte caminho:
      1 – para cada linha, criaria uma corresp ao final, buscando o valor desejado
      2 – se os valores forem únicos, apenas uma dessas fórmulas vai funcionar e retornar o valor da posição dela na linha
      3 – depois é só pegar esse valor e usar em um DESLOC na parte dos meses para saber o mês correspondente

  48. olá Rafael meu nome é luiz, tenho um arquivo com 2 planilhas(plan 1 – plan 2). na plan1 tenho uma lista com nomes de clientes ex: (1008 – V C DE AMORIM ARAUJO ME) etc. e cada cliente existe um código ex na plan1 CELULA A1 O CODIGO É *855*. E CELULA B1 O CLIENTE É *1008 – V C DE AMORIM ARAUJO ME*. ESSA LISTA TEM UNS 500 CLIENTES COM SEUS DEVIDOS CODIGOS. ENTÃO, NA PLAN 2 TENHO OUTRA LISTA COM DIVERSOS NOMES DE CLIENTES, SÓ QUE ESSES CLIENTES NÃO TEM O REFERIDO CODIGO QUE OS CLIENTES DA PLAN1 TEM(SÃO OS MESMOS CLIENTES MAIS OS CLIENTES DA PLAN2 ESTÃO SEM OS CODIGOS). O que eu preciso é que quando eu atualize os clientes da PLAN2, haja uma vinculação de códigos com os da PLAN1, eu estou usando a formula =ÍNDICE(PLAN1$A$1:$B$25;CORRESP(A1;PLAN1B:B;0);1) essa formula funciona perfeitamente quando os nomes dos clientes são idênticos tanto na PLAN1 quanto na PLAN2,pois ela só tem 3 retornos possíveis (1).(0).(-1), estou usando o zero como retorno, mais ele só retorna o valor exato!!! só que há um problema, os clientes da PLAN2 não tem uma numeração na frente do nome. por exemplo na PLAN1 todos os Clientes tem uma numeração na frente do nome (1008 – V C DE AMORIM ARAUJO ME), já na PLAN2 os nomes quando importo vem assim (V C DE AMORIM ARAUJO ME). MINHA necessidade é que eu possa fazer a vinculação dos códigos, mais preciso de uma formula que busque o nome do cliente mais que esse nome não tem que exatamente igualzinho ao da PLAN1. me da uma luz, se precisar eu pago.

  49. Olá Rafael.
    Estou tentando de todas as formas encontrar uma função para o cálculo de VALORES ÚNICOS/EXCLUSIVOS só que em intervalos SEPARADOS, EX: de A10:A24 – de A50:A64
    A função =SOMARPRODUTO/CONT.SE, só funciona em um único intervalo. Até que consegui montar uma que chegou bem perto mais o valor final não bateu. Apliquei a seguinte FUNÇÃO nos intervalos abaixo mencionados:
    FUNÇÃO: =SOMARPRODUTO((A10:A24″”)/CONT.SE(A10:A24;A10:A24&””)+(A50:A64″”)/CONT.SE(A50:A64;A50:A64&””))
    CÉLULAS: A10:A25 (1-1-2-3-3) Células de A50:A64 (3-3-4-3)
    O valor final da contagem foi igual a 5. O correto deveria ser igual a 4.
    Note que o item ( 3 ) foi somado novamente como valor único no segundo intervalo (A50:A64), o que não deveria acontecer.
    Se possível gostaria de contar com sua ajuda.

  50. OLÁ RAFAEL, EU DE NOVO. te agradeço muito pelo ensino da redução de texto e do indice e corresp.
    estou com mais uma questão, tenho uma lista de clientes que preciso localizar seus códigos em outra lista, só que na PLan1 tenho os nomes completos EX:( LUIZ FEIJO DA SILVA NETO) desses clientes, e na plan2 os nomes estão incompletos ex:( LUIZ FEIJO DA SI ). EU USEI a formula =ÍNDICE(MATRIZ;CORRESP(D2;Plan1!B:B;1);1) NÃO usei o zero como retorno pois, só retorna se o nome for exatamente igual ao da PLAn1, usei o 1 como retorno, e percebi que ele esta me retornando exatamente o valor da celula anterior, que está na Plan1(que são os codigos) sempre! então eu pensei, já que ele está sempre me retornando o valor da celular vizinha anterior, então deve haver um comando por exemplo, que se a formula encontrar o valor anterior, me mostre entao o valor da proxima, ex: a formula está me retornando o valor da celula A10, então me mostre o valor da proxima, que seria A11, entendi!
    Se voce tiver, outro modo de relacionar o nome para com o codigo, fico grato. ah, já tentei com PROCV e deu a mesma coisa do indice.
    espero q voc e possa me ajudar. vlw

    • Oi Luiz, não sei se entendi muito bem o seu problema. Mas eu usaria o próprio PROCV, mas buscando de uma coluna auxiliar que você vai criar na Plan2. Use a fórmula ESQUERDA para pegar x valores da esquerda e depois faça o PROCV buscando essa mesma quantidade de caracteres

  51. Ola, amigo, MUITO obrigado pelas explicações, foram de grande ajuda!
    Queria, por gentileza, sua opinião sobre como fazer uma planilha que atenda a necessidade abaixo:
    – Trabalhos com atendimento a varias lojas varejistas e, as vezes, mais de uma solicitação de serviço é feita pra mesma loja. Assim, precisaríamos de uma planilha que, quando digitássemos o número de determinada loja, ela mostrasse TODOS os serviços que foram solicitados para essa, ao mesmo tempo, para que pudéssemos ter uma noção de quantidade e possamos ver as descrições, datas e etc, como em um índice ou algo parecido, compreende? Por exemplo:
    * LOJA (BUSCA): 0000
    * SERVIÇOS RELACIONADOS (RESPOSTA):
    – SERVIÇO 01 / DATA XX/XX / SOLICITANTE YY
    – SERVIÇO 02 / DATA XX/XX / SOLICITANTE II
    – SERVIÇO 03 / DATA PP/PP / SOLICITANTE ÇÇ
    – …
    Lembrando que já tenho uma outra planilha, que usamos como banco de dados, mas aplicamos a função PROCV, portanto, ela só nos mostra uma linha dos dados relacionados a determinada loja.

    Desde já, muito obrigado por todo auxílio prestado, Rafael. Forte abraço!

DEIXE UMA RESPOSTA

Por favor, escreva o seu comentário
Por favor, digite seu nome