Como encontrar a última ocorrência de um item em uma lista usando uma fórmula do Excel

0 ações

Resumo: neste tutorial, você aprenderá a usar uma fórmula do Excel para encontrar a última ocorrência de um item em uma lista.

Recentemente, eu estava trabalhando em uma agenda de reuniões.

Eu tenho uma lista no Excel com uma lista de pessoas e as datas em que foram "presidentes da reunião".

Também preciso saber quando uma pessoa foi a última "presidente da reunião" devido à duplicação na lista (o que significa que uma pessoa foi presidente da reunião várias vezes).

Isso ocorre porque preciso garantir que o presidente mais recente não seja transferido.

Então decidi usar alguma mágica de função do Excel para fazer o trabalho.

Abaixo está o resultado final, posso selecionar um nome no menu suspenso e ele me fornece a data da última ocorrência desse nome na lista.

Se você tem um bom entendimento das funções do Excel, sabe que não existe nenhuma função do Excel que possa fazer isso.Aqui fazemos milagres acontecerem.

Neste tutorial, mostrarei três maneiras de fazer isso.

Conteúdo

1 Encontre a última ocorrência - usando a função MAX
2 Encontre a última ocorrência - usando a função LOOKUP
3 Localizar última ocorrência - usando função personalizada (VBA)
4 Oh, olá, prazer em conhecê-lo.

Encontre a última ocorrência - usando a função MAX

Aqui está a fórmula do Excel que retornará o último valor da lista:

=INDEX($B$2:$B$14,SUMPRODUCT(MAX(行($A$2:$A$14)*($D$3=$A$2:$A$14))-1))

Veja como essa 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 porque está na linha 11.Como nossa lista começa na segunda linha, 1 é subtraído.Portanto, a última ocorrência de Glen é 10 em nossa lista.
SUMPRODUCT é usado para garantir que você não precise usar Control + Shift + Enter porque SUMPRODUCT pode manipular fórmulas de matriz.
A função INDEX agora é usada para localizar a data do último nome correspondente.

questão relacionada Correção - isso não funcionou.Certifique-se de que seu dispositivo bluetooth ainda seja detectável e tente novamente

Encontre a última ocorrência - usando a função LOOKUP

Aqui está outra fórmula que faz o mesmo trabalho:

=LOOKUP(2,1/($A$2:$A$14=$D$3),$B$2:$B$14)

Veja como essa fórmula funciona:

O valor de pesquisa é 2 (você verá o porquê .. continue lendo)
O intervalo de pesquisa é 1/($A$2:$A$14=$D$3) - retorna 1 quando encontra um nome correspondente e, caso contrário, um erro.Então você acaba com uma matriz.例如，查找值為 Glen，數組將為 {#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 parâmetro ([result_vector]) é o intervalo em que dá o resultado, neste caso a data.

A razão pela qual essa fórmula funciona é que a função LOOKUP usa uma técnica de correspondência aproximada.Isso significa que, se ele encontrar uma correspondência exata, ele retornará, mas se não puder, ele examinará a matriz inteira até o final e retornará o próximo maior valor que for menor que o valor de pesquisa.

Nesse caso, o valor de pesquisa é 2 e, em nosso array, obtemos apenas 1 ou um erro.Portanto, ele varre todo o array e retorna a posição do último 1 - o último valor correspondente ao nome.

Localizar última ocorrência - usando função personalizada (VBA)

Deixe-me mostrar-lhe outra maneira também.

Podemos criar funções personalizadas (também conhecidas como funções definidas pelo usuário) usando o VBA.

O benefício de criar funções personalizadas é a facilidade de uso.Você não precisa se preocupar em criar fórmulas complexas todas as vezes porque a maior parte do trabalho acontece no back-end do VBA.

Eu criei uma fórmula simples (muito parecida com a fórmula PROCV).

Para criar funções personalizadas, você precisa de código VBA no editor VB.Eu lhe darei o código e as etapas para colocá-lo no editor VB mais tarde, mas deixe-me mostrar como funciona primeiro:

questão relacionada Backup completo do Outlook: exportação de e-mail como arquivo PST

Aqui está a fórmula que lhe dará o resultado:

=ÚltimoItemPesquisa($D$3,$A$2:$B$14,2)

A fórmula leva três parâmetros:

Encontre o valor (este será o nome na célula D3)
Encontre o intervalo (este será o intervalo com nome e data – A2:B14)
número da coluna (esta é a coluna para a 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 de planilha regular do Excel.

Segue o código da fórmula:

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) Exit Function End If Next i End Function

Aqui estão os passos para colocar este código no editor VB:

Vá para a guia do desenvolvedor.
Clique em Opções do Visual Basic.Isso abrirá o editor VB no backend.
No painel Project Explorer do editor VB, clique com o botão direito do mouse em qualquer objeto na pasta de trabalho onde você deseja inserir o código.Se você não vir o Explorador de Projetos, vá para a guia Exibir e clique em Explorador de Projetos.
Vá em Inserir e clique em Módulos.Isso inserirá um objeto de módulo para sua pasta de trabalho.
Copie e cole o código na janela do módulo.

questão relacionada Como parar de sincronizar o Google Fotos?

Agora a fórmula estará disponível em todas as planilhas da pasta de trabalho.

Observe que você precisará salvar a pasta de trabalho no formato .XLSM, pois ela contém macros.Além disso, se desejar que essa fórmula esteja disponível em todas as pastas de trabalho que você usa, você pode salvá-la em sua pasta de trabalho de macro pessoal ou criar um suplemento a partir dela.