Neste tutorial, você aprenderá como encontrar a última ocorrência de um item em uma lista usando fórmulas do Excel.
Recentemente, estava trabalhando na definição da agenda de uma reunião.
Eu tinha uma lista no Excel onde eu tinha uma lista de pessoas e as datas em que atuaram como ‘Presidente da Reunião’.
Como houve repetição na lista (o que significa que uma pessoa foi presidente da reunião várias vezes), eu também precisava saber quando foi a última vez que uma pessoa atuou como "presidente da reunião".
Isso porque eu precisava garantir que alguém que havia presidido recentemente não fosse designado novamente.
Portanto, decidi usar um pouco da magia da Função do Excel para fazer isso.
Abaixo está o resultado final em que posso selecionar um nome no menu suspenso e ele me dá a data da última ocorrência desse nome na lista.
Se você tem um bom conhecimento das Funções do Excel, sabe que não existe uma função do Excel que possa fazer isso.
Mas você está na seção Formula Hack, e aqui fazemos a mágica acontecer.
Neste tutorial, mostrarei três maneiras de fazer isso.
Encontre a última ocorrência - usando a função MAX
O crédito a essa técnica vai para um artigo do Excel MVP Charley Kyd.
Esta é a fórmula do Excel que retornará o último valor da lista:
= ÍNDICE ($ B $ 2: $ B $ 14, SUMPRODUTO (MAX (LINHA ($ A $ 2: $ A $ 14) * ($ D $ 3 = $ A $ 2: $ A $ 14)) - 1))
Aqui está como esta fórmula funciona:
- A função MAX é usada para encontrar o número da linha do último nome correspondente. Por exemplo, se o nome for Glen, ele retornará 11, pois está na linha 11. Como nossa lista começa a partir da segunda linha, 1 foi subtraído. Portanto, a posição da última ocorrência de Glen é 10 em nossa lista.
- SUMPRODUCT é usado para garantir que você não precise usar Control + Shift + Enter, pois SUMPRODUCT pode lidar com fórmulas de matriz.
- A função INDEX agora é usada para encontrar a data do último nome correspondente.
Encontre a última ocorrência - usando a função LOOKUP
Aqui está outra fórmula para fazer o mesmo trabalho:
= LOOKUP (2,1 / ($ A $ 2: $ A $ 14 = $ D $ 3), $ B $ 2: $ B $ 14)
Veja como funciona esta fórmula:
- O valor de pesquisa é 2 (você verá por que … continue lendo)
- O intervalo de pesquisa é 1 / ($ A $ 2: $ A $ 14 = $ D $ 3) - retorna 1 quando encontra o nome correspondente e um erro quando não o faz. Então você acaba obtendo um array. Por exemplo, se o valor de pesquisa for Glen, a matriz seria {# DIV / 0!; # DIV / 0!; 1; # DIV / 0!; # DIV / 0!; # DIV / 0!; # DIV / 0!; # DIV / 0!; # DIV / 0!; 1; # DIV / 0!; # DIV / 0!; # DIV / 0!}.
- O terceiro argumento ([result_vector]) é o intervalo a partir do qual ele fornece o resultado, que são datas neste caso.
O motivo pelo qual essa fórmula funciona é que a função LOOKUP usa a técnica de correspondência aproximada. Isso significa que se ele puder encontrar o valor correspondente exato, ele retornará, mas se não puder, ele fará a varredura de todo o array até o final e retornará o próximo maior valor que é inferior ao valor de pesquisa.
Nesse caso, o valor de pesquisa é 2 e, em nossa matriz, obteremos apenas 1 ou erros. Portanto, ele verifica toda a matriz e retorna a posição do último 1 - que é o último valor correspondente do nome.
Encontre a última ocorrência - usando a função personalizada (VBA)
Deixe-me mostrar outra maneira de fazer isso.
Podemos criar uma função personalizada (também chamada de função definida pelo usuário) usando o VBA.
A vantagem de criar uma função personalizada é que ela é fácil de usar. Você não precisa se preocupar em criar uma fórmula complexa todas as vezes, pois a maior parte do trabalho acontece no back-end do VBA.
Eu criei uma fórmula simples (que é muito parecida com a fórmula VLOOKUP).
Para criar uma função personalizada, você precisa ter o código VBA no Editor VB. Eu darei a você o código e as etapas para colocá-lo no Editor VB em um momento, mas deixe-me primeiro mostrar como funciona:
Esta é a fórmula que lhe dará o resultado:
= LastItemLookup ($ D $ 3, $ A $ 2: $ B $ 14,2)
A fórmula leva três argumentos:
- Valor de pesquisa (seria o nome na célula D3)
- Intervalo de pesquisa (este seria o intervalo que tem os nomes e datas - A2: B14)
- Número da coluna (esta é a coluna da qual queremos o resultado)
Depois de criar a fórmula e colocar o código no Editor VB, você pode usá-lo como qualquer outra função regular de planilha do Excel.
Aqui está o código da fórmula:
'Este é um código para uma função que encontra a última ocorrência de um valor de pesquisa e retorna o valor correspondente da coluna especificada' Código criado por Sumit Bansal (https://trumpexcel.com) Função LastItemLookup (Lookupvalue As String, LookupRange As Range, ColumnNumber As Integer) Dim i As Long For i = LookupRange.Columns (1) .Cells.Count to 1 Step -1 If Lookupvalue = LookupRange.Cells (i, 1) Then LastItemLookup = LookupRange.Cells (i, ColumnNumber) Sair da função End If Next i End Function
Aqui estão as etapas para colocar este código no Editor VB:
- Vá para a guia Desenvolvedor.
- Clique na opção Visual Basic. Isso abrirá o editor VB no backend.
- No painel Project Explorer no VB Editor, clique com o botão direito em qualquer objeto da pasta de trabalho na qual deseja inserir o código. Se você não vir o Project Explorer, vá para a guia View e clique em Project Explorer.
- Vá para Inserir e clique em Módulo. Isso irá inserir um objeto de módulo para sua pasta de trabalho.
- Copie e cole o código na janela do módulo.
Agora a fórmula estaria disponível em toda a planilha da pasta de trabalho.
Observe que você precisa salvar a pasta de trabalho no formato .XLSM, pois contém uma macro. Além disso, se você deseja que esta fórmula esteja disponível em todas as pastas de trabalho que você usa, você pode salvá-la na Pasta de trabalho macro pessoal ou criar um suplemento a partir dela.
