Função DESLOC e CORRESP Excel

167
45127
Faça buscas com a função Desloc e Corresp
Planilhas Prontas em Excel

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.

Planilhas Prontas em Excel

167 COMENTÁRIOS

  1. Boa noite. Trabalho com base de dados em Excel e preciso de uma fórmula que me informe a última linha onde se encontra uma determinada referência. Sendo que na plan1 estão todos os dados de recebimento (referência e data de recebimento) na plan2 as referências que estou recebendo na data de hoje. Preciso saber ao lado da referência na plan2 a data da última entrada que está na plan1 (existem várias datas de entrada, mas preciso só da mais recente). Alguém consegue me dar uma luz?

  2. Olá! a explicação do seu post foi ótima e me ajudou demais! Parabéns e muito obrigada!!

  3. É possível, mas você vai ter que criar uma fórmula que mostre quantos meses (células) existem nesse intervalo selecionado

  4. Bom Dia

    Como eu faço para somar um acumulado deste valor.
    Por exemplo ter dois campos de datas para selecionar JAN à MAR e o desloc + corresp somar o intervalo destes meses? possível?

    Abraços!!

  5. Oi Andressa, nesses casos você precisa primeiro de uma tabela que tenha o nome de todos os funcionários e seus espectivos setores. Tendo isso, você pode usar o PROCV que é até mais fácil que o DESLOC para encontrar qual é o setor do funcionário selecionado

  6. Boa tarde!

    Essa sua explicação é excelente, porém não estou conseguindo aplicá-la na minha planilha, por exemplo: Existem duas colunas com os nomes “SETOR” e “FUNCIONÁRIO”, quero escrever o nome da funcionária “Andressa” e na coluna ao lado aparecer o setor dela, ex: Coluna1 Andressa e na Coluna2 automaticamente preencher “Financeiro”. Poderia por gentileza me dá uma luz?

  7. Você pode usar uma função de manipulação de texto como ESQUERDA ou DIREITA para extrair apenas o valor desejado sem a idade

  8. Boa Tarde! Tenho uma dúvida, estou tentando igualar duas colunas que possuem quase os mesmos dados, por exemplo, na coluna A possui vários nomes em ordem alfabética e na B possui alguns destes nomes com a idade na frente de forma embaralhada. Estou usando a seguinte fórmula: =SE(CONT.SE(A:A;B1)=1;B1;””), ela compara os nomes e os equivale mas quando não há a idade. Como faço para os equiparar usando uma fórmula que não leve em consideração a idade, mas também não posso tirá-la pois é uma informação importante?? Poderia me ajudar??
    Desde já Obrigado!

  9. Oi Leila, vamos lá, eu faria os seguintes passos

    1 – criaria coluna auxiliar para ver o mês da venda
    2 – criaria coluna auxiliar para ver o ano da venda
    3 – criaria uma fórmula SOMASES para ver o valor vendido no mês atual do ano atual
    4 – criaria 11 fórmulas auxiliares SOMASES pegando os 11 meses anteriores sequencialmente
    5 – criaria a MÉDIASE dessa tabela auxiliar para cada loja

  10. Não entendi muito bem Gilberto, mas você pode usar a funcionalidade de copiar e colar + transpor para modificar linhas para colunas

  11. Bom dia,

    Estou tentando calcular a média dos útimos 12 meses de venda, sendo que na planilha os códigos das lojas estão na vertical e os meses na horizontal, então preciso que o excel encontre a venda da loja de código 01 no mês de dezembro de 2018 e tire a média dos 12 últimos meses de venda, para esse exemplo seria a média dos valores de janeiro de 2018 até dezembro de 2018 e esse intervalo iria se adaptando de acordo com o mês selecionado, sempre os 12 meses para trás do mês selecionado.

    Minha base está em outra planilha, usei a seguinte fórmula:
    =MÉDIASE(‘Mapa 1’!$G$19:$G$572;$C6;DESLOC(‘Mapa 1′!$H$19:$H$572;0;CORRESP(G$4;’Mapa 1’!$H$19:$EN$19;0)-1;1;-12))
    só que esta fórmula está me trazendo a venda de janeiro de 2018 e não a média dos 12 meses como eu gostaria.

    Poderia me ajudar?

  12. Parabéns,
    Tentei achar algo para atender, mas é muita informação,
    É possivel pegar um relatório que emitido com o nome repetido com varias datas sequencial em linha de 05 a 30 linhas, transferindo para uma unica linha, pegando a primeira data em uma coluna e a ultima data em outra coluna?

  13. Oi Muriela, não entendi a dificuldade, se for só colocar tudo que está em linhas para as colunas você pode seecionar esses dados, copiar e colar especial (transpor) para ficar nas colunas corretamente

  14. Boa tarde, tenho uma questão.

    tenho uma planilha com os casos em questão, em que os dados estão por linhas e não por colunas.
    Como faço para trocar?
    quero ter o caso 1/dado 2/dado 3 tudo por colunas para facilitar no excel.
    Podias me ajudar?

  15. Oi Daiane, até é possível fazer o que você quer, mas é um pouco complicado. Você precisaria fazer o seguinte:
    1 – na aba clientes criar uma coluna auxiliar no começo com a inicial de cada cliente (usando a fórmula ESQUERDA)
    2 – na aba de pesquisa, precisaria criar uma função matricial para buscar os valores de acordo com uma letra. Já expliquei em um post como fazer, veja aqui – https://blog.luz.vc/excel/variacoes-da-funcao-procv-no-excel-mais-de-uma-condicao-e-valores-repetidos/

  16. Olá Rafael, perfeito suas explicações!

    Estou com um problema!

    tenho a planilha clientes – onde salvo as informações colunas – (EMPRESA/CONTATO/TEL./EMAIL … )
    cada linha contem as informações, porém é muito grande a quantidade de clientes.

    Eu queria em uma Nova Planilha “Pesquisa”
    onde eu digitasse por ex.: A letra “A”
    e abaixo trouxesse da coluna empresa – todas as empresas que comecem com a INICIAL A (no caso as linhas com as informações) outro caso se digitar R que venha todas as empresas que comece com a letra R

    Desde já Obrigada pela atenção.

  17. Fantastico!

    Tem algum jeito para eu conseguir “transpor” os dados de uma coluna para abas? cada aba ter o nome/valor de cada linha que há

    Coluna A
    Linha 1 – SP
    Linha 2 – RJ
    Linha 3 – MG

    há como levar os dados das linhas para as abas?

  18. Oi Gabriel, a lógica é a mesma, a busca será feita para um termo específico, se você quiser pegar dois termos, pode usar a primeira parte do post, que usa o CONCATENAR para pegar um termo x e unir ao termo y, que no seu caso seria o valor procurado + status

  19. Olá, Rafael! Muito obrigado por responder, acho que irá atender! Seria possível condicionar o resultado apresentado? Por exemplo, caso um dos serviços já tenha sido atendido, poderíamos colocar o status “concluído” e, assim, o resultado o excluiria, uma vez que o trabalho já foi realizado.

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

  21. Oi ALdjonson, a solução é criar uma tabela única auxiliar que pegue todos esses intervalos e depois aplique a função referida

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

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

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

  25. poxa, me ajudou muito!
    tenho umas planilhas com cerca de 2000 nomes e fiz o teste e funcionou com a formula ext.texto.
    muito obrigado tu és fera!

  26. Oi Luiz, se não for igual, eu desconheço uma forma de fazer. Agora, é possível gerar os nomes na plan1 sem os números, basta usar EXT.TEXTO com LOCALIZAR (você vai tirar a quantidade de caracteres do começo)

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

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

  29. Boa tarde utilizando o exemplo da explicação, como eu faria pra encontrar o mês? por exemplo eu recebi 2,10 pela banana em qual mês?

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

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

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

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

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

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

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

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

  38. Oi Denise, ficou um pouco confuso e eu não entendi nem o que você fez exatamente e nem o que gostaria de fazer.

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

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

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

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

  44. Oi Mônica, você precisa criar regras de formatação condicional diferentes para cada um das cores

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

  46. Acredito que você possa reorganizar essa tabela para ter a coluna com o percentual primeiro e usar um PROCV com VERDADEIRO para o valor 1

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

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

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

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

  51. Olá, boa tarde.

    Sabe dizer por qual razão o argumento de “altura” da função desloc não funciona no excel? No google spreadsheets funciona perfeitamente.

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

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

  54. Oi Diego, acredito que temos sim (não lembro quais), mas muitas vezes utilizamos o PROCV mesmo para fazer buscas de maneira automática.

  55. Rafael show, eu comprei o pacote completo de planilhas de vocês, alguma dela usa essa técnica?

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

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

  58. Oi Felipe, nesse caso você precisa usar a função INDIRETO dentro da validação de dados da segunda lista

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

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

  61. Oi Pedro, o DESLOC com MAIOR deveria funcionar, um problema possível é se você tiver contratos com valores iguais (isso pode gerar um problema) ou se você estiver usando a função DESLOC de maneira equivocada.

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

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

  64. Oi João, nesse caso você pode usar a condicional SE para dizer sempre que B estiver vazio, retornar o valor vazio nas outras células.

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

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

  67. Prezo,
    Primeiramente, parabéns e obrigado pelo conteúdo de qualidade.
    Estou tentando fazer o inverso do DESLOC conforme imagem do link, a ideia é buscar os valores nas colunas e colocar nas linhas de acordo com a posição 1, 2 e 3 por estado.
    O problema á que já tentei de tudo e não achei uma forma de fazer.
    https://www.dropbox.com/s/emtabberg4xurt3/imagem.PNG?dl=0

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

  69. Oi Renato, não existe um limite, mas de maneira geral, você poderá usar a CORRESP para cada argumento que existe na DESLOC

  70. Oi Lucas, não entendi o que você precisa fazer. Se puder explicar com um pouco mais de detalhe posso tentar ajudar. Abraços

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

  72. 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?”

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

  88. Oi César, agora complicou um pouco mais. Nesse caso não estou conseguindo pensar em uma solução simples

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

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

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

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

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

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

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

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

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

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

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

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

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

  102. Boa tarde Fabiano, tente fazer assim:
    =SOMARPRODUTO(ÉPAR(COL(23:23))+0;23:23)

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

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

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

  106. Como disse Fabiano, também não consegui chegar em uma solução, o DESLOC foi só uma sugestão, mas não tinha certeza se iria funcionar ou não.

  107. Oi Rafael, desculpe a minha ignorância, mas não consegui aplicar o seu conselho. Como ficaria a fórmula conforme o exemplo que dei.

  108. Oi Fabiano, pensei aqui e não consegui chegar em uma solução. Talvez se você usar o DESLOC +2 sucessivamente pode ter um resultado, mas não cheguei a tentar usar esse caminho

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

  110. Oi Elton, acho que foi apenas um erro na fórmula na hora de gerar as imagens

  111. Oi Guilherme, você já tentou usar o filtro? Fica na guia DADOS e permite que você ordene uma determinada coluna do maior para o menor ou vice versa

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

  113. Boa noite,

    Por quê há a operação *1,ao fim da fórmula DESLOC() ?

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

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

  116. Bom dia

    preciso de ajuda , não sei como duplicou, alguém pode me ajudar, sffv

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

  136. Basicamente quando você utiliza a função PROCV, precisa informar o número de colunas que serão deslocadas para a direita e, se tiver a necessidade de arrastar ou copiar a fórmula, esse número será estático.

    Já com a DESLOC e CORRESP, por só utilizar referências, esses números ficam relativos, sendo mais fácil de utilizar em casos de necessidade de cópia

  137. Qual a diferença de aplicabilidade da combinação das funções =DESLOC(CORRESP()) e da função =PROCV()?

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

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

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

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

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

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

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

DEIXE UMA RESPOSTA

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