0216-Dica sobre fórmulas: “PROCV” trazendo diferentes ocorrências

Uma pergunta recorrente de usuários é se é possível retornar várias ocorrências de um valor numa lista utilizando o PROCV.

A resposta é não. A função PROCV só é capaz de retornar a primeira ocorrência de um valor, pesquisando-o na primeira coluna de uma referência. O mesmo vale para a função PROCH (pesquisa na primeira linha).

Já vimos aqui no blog duas alternativas para essa questão utilizando VBA: 0030-Turbinando o PROCV

Hoje veremos como é possível obter esse tipo de resultado utilizando funções de planilha. Essa não é única alternativa, podendo ser desenvolvidas outras abordagens.

Partiremos desse conjunto de dados para iniciarmos a análise.

0216_01

Iremos pesquisar o valor “João”, na primeira coluna e retornar o valor correspondente na coluna “Valor”.

O primeiro valor que temos que conhecer é o número de vezes que “João” aparece no intervalo pesquisado.

Podemos obter facilmente esse resultado utilizando a função CONT.SE.

0216_02

Esse parâmetro é importante, pois se quisermos buscar uma 6ª ocorrência no conjunto de dados obteremos uma mensagem de erro.

Em seguida, temos que estabelecer qual a ocorrência que desejamos retornar. No nosso exemplo, um número entre 1 e 5. Escolhemos o valor 3 aleatoriamente.

0216_03

A próxima etapa é definir a coluna que será retornada (“Valor”).

0216_04

Sabendo a partir de qual coluna o valor será retornado, precisamos definir a posição desta coluna no intervalo de cabeçalhos.

0216_05

Agora passaremos à construção da fórmula:

O primeiro passo é determinar o número das linhas nas quais o valor pesquisado ocorre.

=(A1:A13=F1)*LIN(A1:A13)

Essa fórmula retorna um vetor que mostra os números das linhas nas quais o valor pesquisado ocorre:

={0;2;0;0;5;6;0;0;9;0;0;12;0}

Ou seja, o valor “João” ocorre nas linhas 2;5;6;9;12 (5 ocorrências)

Se queremos a 3ª ocorrência, iremos procurar o 3º menor valor da sequência acima. Poderíamos utilizar a função MENOR, mas teríamos que utilizar um artifício para descontar os zeros. Ao invés disso, iremos pesquisar o 3º maior valor.

Deixaremos que o Excel faça essa conversão: (Nº Ocorrências – Ocorrência desejada + 1)

=MAIOR((A1:A13=F1)*LIN(A1:A13);F2-F3+1)

Se tentarmos aplicar a fórmula acima obteremos o valor de erro #NÚM!. Isso ocorre por que estamos lidando com fórmula matricial. Para evitarmos o uso de fórmulas matriciais, contornaremos o problema utilizando SOMARPRODUTO.

=SOMARPRODUTO(MAIOR((A1:A13=F1)*LIN(A1:A13);F2-F3+1))

A fórmula acima retorna o valor 6, indicando que a 3ª ocorrência do valor “João” ocorre na linha nº 6.

Obtendo a linha da coluna A e sabendo que o valor pesquisado se encontra na coluna de índice 3 (Coluna C), podemos utilizar a função DESLOC para retornar o valor desejado:

=DESLOC(A1;SOMARPRODUTO(MAIOR((A1:A13=F1)*LIN(A1:A13);F2-F3+1))-1;F5-1)

0216_06

Criamos então uma fórmula capaz de retornar a k-ésima ocorrência de um valor num conjunto de dados.

Compreendido o raciocínio, incorporaremos à fórmula os valores intermediários calculados em F3 e F5, resultando em:

=DESLOC(A1;SOMARPRODUTO(MAIOR((A1:A13=F1)*LIN(A1:A13);CONT.SE(A1:A13;F1)-F2+1))-1;CORRESP(F3;A1:C1)-1)

A versão definitiva está disponível para download aqui.

Anúncios

In Excel We Trust

Marcado com: , , , , ,
Publicado em Nível: Avançado
36 comentários em “0216-Dica sobre fórmulas: “PROCV” trazendo diferentes ocorrências
  1. Monica disse:

    Olá Boa tarde

    Eu tenho uma duvida, se em vez de texto o que necessito é de uma Imagem.
    Já fiz uma tabla onde coloquei uma coluna com o caminho das imagens que necessito que apareça.
    Como devo proceder para que elas apereçam.

    Obg

  2. Sílvio da Silva Lima disse:

    Como posso criar um fórmula para retornar algum nome da lista após identificar nesta mesma lista qual é o maior valor entre os valores da Coluna “Valor”. Pergunto utilizando a função MÁXIMO(C2:C13).

  3. Frederico disse:

    Olá! sua fórmula é muito boa e resolveu um grande problema. Porém me deparei com outro; quando a ocorrência procurada não existe ou é zero (0), o resultado da erro de referência: #REF!.Neste caso, preciso que o resultado também seja zero. estou tentando corrigir mas não estou conseguindo, será que poderia me ajudar?
    Desde já parabéns e obrigado!

  4. Marcus Fenix disse:

    ola queria tirar uma duvida.. na seguinte tabela tenho os dados a1= 3 e b2= 5 c3= 6 sendo que devo substituir o menor valor entre a1 e b2 por c3 aquele de menor valor e multiplicar ele por 2
    desde ja agradeço a ajuda

  5. Adalto Sarraff disse:

    ola, estou procurando uma ou umas formulas (uma dentro da outra) que me ajude em uma planilha que quero automatiza-la. É seguinte: Tenho uma planilha com varios itens inseridos um logo abixo do outro dos os dias, essa planilha tem, numero do item, nome, codigo, modelo, data, tipo de serviço, data de execução, pessoa que executou, horas de execução. O que quero e que a(s) formula (s) procure os 10 itens que tem maior numero de ocorrencias ( um de cada vez, do maior para o menor em celular separadas) e que me retorne na celula qual e esse item.
    Exemplo:
    item tipo de serviço qtd ocorrencia
    425 correção 8
    128 preventiva 10
    555 preventiva 6
    450 modificação 7
    o o maior é 10, entao a formula identifica esse valor e me retorna de quem e esse valor, no exemplo o valor e do item 128, entedeu??
    Se puder ajudar agradeço muito.

  6. Edson disse:

    Olá tudo bom?

    Estou precisando de uma ajuda, tenho uma planilha no qual estou controlando o faturamento da minha empresa, este controle preciso saber qual foi o melhor mês do ano e o valor.

    Minha planilha esta assim

    na linha 4 tenho os meses de janeiro a dezembro, na coluna B tenho Janeiro e assim por diante nas colunas até Dezembro.

    na linha 5 tenho o meu faturamento dos meses.

    preciso de uma formula em linha 6 columa (A) que me indique qual o mês de melhor faturou no ano com o valor.

    Desde já agradeço a vossa atenção.

  7. Arthur Versoza disse:

    Boa tarde Adilson, parabéns pela publicação!
    O meu caso é parecido, mas a dificuldade é que preciso que a formula que retorne o maior valor dentre os encontrados com o nome de João.
    Existe alguma saída?

    Abraço!

  8. Boa tarde, Arthur.
    Você pode fazer assim: =SOMARPRODUTO(MÁXIMO((A2:A13=”João”)*(C2:C13)))
    A fórmula retornará o maior valor para o qual há uma correspondência com o nome João.

  9. Hemerson disse:

    so dei uma passada e nao estou por dentro do inicio ou final mas coloque *1 depois de f1 que ele entende=MAIOR((A1:A13=F1)*1*LIN(A1:A13);F2-F3+1)

  10. Carolina Costa D'Assumpção disse:

    Caso o joão tenho mais de um valor por ano, como faço para contar quantos valores por ano ele tem?

  11. Precila disse:

    Olá amigos. Tenho uma planilha com 30 abas (todos os dias do mês) e preciso implementar um PROC para buscar um determinado código ID (q não se repete). Preciso que esse PROC procure em todas as 30 abas. Alguma dica para criar algo dinâmico?

  12. Bom dia, Precila. Tem que ser uma macro para poder realizar esse tipo de tarefa. Como os dados estão organizados e de que forma quer retornar o resultado?
    [ ]s

    • Precila disse:

      Na planilha temos 30 abas que são os 30 dias no mês. Em cada aba (dia) tem de hora em hora um registro de produção. Nas colunas são digitadas… codigo lote, expessura, Kg, etc. Em uma planilha chamada consulta fiz um PROCV para localizar o codigo do lote, independente do dia. Mas tenho que fazer um PROCV para cada aba (30 Procs) por isso queria algo mais dinâmico. Não sei se consegui explicar certo.

  13. Já melhorou a visualização do caso.
    Algumas perguntas mais específicas:
    – Como são nomeadas as abas de planilha?
    – Em que coluna é digitado o código?
    – Você precisa retornar o dia e hora em que ocorreu o lote?
    [ ]s

  14. Boa tarde, Precila.

    A maneira mais fácil é inserindo evento a seguir na planilha CONSULTA RJ

    Private Sub Worksheet_Change(ByVal Target As Range)

    ‘Declarar variáveis
    Dim wksSheet As Worksheet
    Dim intCont As Integer
    Dim blnTest As Boolean

    ‘Testar de a célula E9 foi modificada
    If Target.Address = “$E$9$” Then

    ‘Variável de teste
    blnTest = False

    ‘Desabilitar os eventos
    Application.EnableEvents = False

    ‘Loop para percorrer as planilhas
    For Each wksSheet In ThisWorkbook.Sheets

    With wksSheet

    ‘Loop para percorrer as células preenchidas da coluna A
    For intCont = 1 To wksSheets.cell(Rows.Count, 1).End(xlUp).Row

    ‘Se a célula contiver o valor digitado em E9
    ‘transferir os valores para as células correspondentes
    If .Cells(intCont, 1) = Target Then
    ‘Papel
    [C3] = .Cells(intCont, 5)
    ‘COBB
    [G3] = .Cells(intCont, 22)
    ‘Gramatura
    [C5] = .Cells(intCont, 4)
    ‘Gramatura média
    [C7] = .Cells(intCont, 16)
    ‘Umidade
    [G5] = .Cells(intCont, 23)
    ‘Porosidade
    [C9] = .Cells(intCont, 19)
    ‘Tração longitudinal
    [C11] = .Cells(intCont, 20)
    ‘Tração transversal
    [C13] = .Cells(intCont, 21)
    ‘Dia
    [C15] = .Name
    ‘Variável de teste
    blnTest = True
    End If
    Next intCont
    End With

    Next

    ‘Habilitar os eventos
    Application.EnableEvents = True

    ‘Exibir mensagem se não for encontrado o valor
    If blnTest = False Then MsgBox “Não foi possível localiza o valor ” & Target

    End If
    End Sub

    Comentários:
    1. O arquivo deve ser salvo como Pasta de Trabalho Habilitada para Macros
    2. Para inserir o evento, clique com o botão direito na aba da planilha CONSULTA RJ, selecione a opção Exibir código, copie o cole o código acima.

    [ ]s

  15. jose ricardo disse:

    quero trazer cada operacao pelo nome, com varias possibilidades

  16. Edcley disse:

    Bom dia

    gostaria de saber porque o procv só retornar o valor da 1 linha mesmo que tenha 2 ou mais linhas associadas a referência na qual estou a buscar?

    • Olá, Edcley.
      Essa é uma questão a ser direcionada para os desenvolvedores do programa…
      A função PROCV foi criada para trabalhar dessa maneira, trazendo o primeiro registro em uma lista que satisfaça à condição de busca.
      Quanto precisamos retornar os registros de outra forma, temos que lançar mão de alguns truques que abordamos em outros artigos do blog…

  17. Lucas Gouvea disse:

    Caro Adilson,
    Bom dia,
    estou com um problema na utilização do PROCV. Utilizo a fórmula em formato padrão, dando um dado (texto com letras e números) como input para a função (lookup value), pedindo para procurar este dado em uma tabela (table array) em outra aba e me retornando o valor (texto) correspondente (col index number). Minha tabela é bem extensa, cerca de 50k linhas, e a fórmula funciona perfeitamente em cerca de 40k. Nos outros 10k ela me retorna o erro #VALUE!. Já comparei o texto das células de input com os textos da coluna de busca do PROCV e o excel retorna TRUE, logo acredito que realmente sejam iguais. Já retirei os ~ dos textos, pois já tive o mesmo problema com eles, entretanto quando os retirei funcionou. Já verifiquei a escrita da fórmula com todos que conheço e está escrita corretamente e mesmo assim o erro persiste.
    Abaixo segue um dos textos escritos em célula que está apresentando problema:

    COSME IMPORTACIONES SOCIEDAD COMERCIALCHAQUETA, MARCA:YD NEW YOUNG DESIGN, DY NEW COLLECTION, MOD.:AP2900,AP15001L,AP910718L 55% POLIESTER 45% POLIURETANO PARA VARON, 01 PIEZA, DIF:COLORES, CON Y SIN CAPUCHA, T/CUERINA, DIF:MEDIDAS, EN BOLSA ART:AP2900,AP15001L,AP910718LYD NEW YOUNG DESIGN DY N

    Alguma ideia do que pode ser?

    Desde já muito obrigado pela atenção e desculpe o incomodo.
    Grande abraço.
    Att,

    Lucas.

  18. Roseli disse:

    Ola, bom dia!
    Preciso fazer o cruzamento de dados de vários assuntos.

    Preciso dinamizar esse processo de forma tal que quando chegar o relatório mensal eu só insiro e consigo fazer os cruzamentos, quase que automaticamente.

    O procv nao funciona pois um colaborador repete varias vezes na mesma planilha.

Deixe um comentário

Preencha os seus dados abaixo ou clique em um ícone para log in:

Logotipo do WordPress.com

Você está comentando utilizando sua conta WordPress.com. Sair / Alterar )

Imagem do Twitter

Você está comentando utilizando sua conta Twitter. Sair / Alterar )

Foto do Facebook

Você está comentando utilizando sua conta Facebook. Sair / Alterar )

Foto do Google+

Você está comentando utilizando sua conta Google+. Sair / Alterar )

Conectando a %s

Visitas dos usuários
  • 2,077,129

Clique para assinar este blog e receber notificações de novos artigos por email.

Junte-se a 1.537 outros seguidores

Aumente sua interação

Siga-nos do twitter (@usuariosdoexcel).

Acesse o nosso fórum e compartilhe as suas dúvidas.

Curta nossa página no Facebook.

Junte-se ao grupo do LinkedIn.

Visite-nos utilizando o seu smartphone ou tablet.

Recomende este Blog
Visualizar notícias
Site monitorado:
Real Time Web Analytics

Clicky

%d blogueiros gostam disto: