Résumé : dans ce tutoriel, vous allez apprendre à utiliser une formule Excel pour trouver la dernière occurrence d'un élément dans une liste.
Récemment, je travaillais sur un ordre du jour de réunion.
J'ai une liste dans Excel avec une liste de personnes et les dates auxquelles elles "réunissaient les présidents".
J'ai également besoin de savoir quand une personne a été la dernière "présidente de réunion" en raison d'une duplication dans la liste (ce qui signifie qu'une personne a été présidente de la réunion plusieurs fois).
C'est parce que je dois m'assurer que le président le plus récent n'est pas réaffecté.
J'ai donc décidé d'utiliser une fonction magique Excel pour faire le travail.
Ci-dessous le résultat final, je peux sélectionner un nom dans la liste déroulante et cela me donne la date de la dernière occurrence de ce nom dans la liste.
Si vous avez une bonne compréhension des fonctions Excel, vous savez qu'aucune fonction Excel ne peut le faire.Ici, nous faisons des miracles.
Dans ce tutoriel, je vais vous montrer trois façons de procéder.
Table des matières
Trouver la dernière occurrence - en utilisant la fonction MAX
Voici la formule Excel qui renverra la dernière valeur de la liste :
=INDEX($B$2:$B$14,SUMPRODUCT(MAX(行($A$2:$A$14)*($D$3=$A$2:$A$14))-1))
Voici comment cette formule fonctionne :
- La fonction MAX est utilisée pour trouver le numéro de ligne du dernier nom correspondant.Par exemple, si le nom est Glen, il renverra 11 car il se trouve sur la ligne 11.Puisque notre liste commence sur la deuxième ligne, 1 est soustrait.Par conséquent, la dernière occurrence de Glen est 10 dans notre liste.
- SUMPRODUCT est utilisé pour s'assurer que vous n'avez pas à utiliser Ctrl + Maj + Entrée car SUMPRODUCT peut gérer des formules matricielles.
- La fonction INDEX est maintenant utilisée pour trouver la date du dernier nom correspondant.
Trouver la dernière occurrence - en utilisant la fonction RECHERCHE
Voici une autre formule qui fait le même travail :
=LOOKUP(2,1/($A$2:$A$14=$D$3),$B$2:$B$14)
Voici comment cette formule fonctionne :
- La valeur de recherche est 2 (vous verrez pourquoi... continuez à lire)
- La plage de recherche est 1/($A$2:$A$14=$D$3) - elle renvoie 1 lorsqu'elle trouve un nom correspondant, et une erreur sinon.Donc, vous vous retrouvez avec un tableau.例如，查找值為 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!}。
- Le troisième paramètre ([result_vector]) est la plage dans laquelle il donne le résultat, dans ce cas la date.
La raison pour laquelle cette formule fonctionne est que la fonction RECHERCHE utilise une technique de correspondance approximative.Cela signifie que s'il peut trouver une correspondance exacte, il reviendra, mais s'il ne le peut pas, il analysera tout le tableau jusqu'à la fin et renverra la valeur la plus grande suivante qui est inférieure à la valeur de recherche.
Dans ce cas, la valeur de recherche est 2, et dans notre tableau, nous obtenons simplement 1 ou une erreur.Ainsi, il analyse l'ensemble du tableau et renvoie la position du dernier 1 - la dernière valeur correspondante pour le nom.
Rechercher la dernière occurrence - Utilisation de la fonction personnalisée (VBA)
Permettez-moi de vous montrer une autre façon aussi.
Nous pouvons créer des fonctions personnalisées (également appelées fonctions définies par l'utilisateur) à l'aide de VBA.
L'avantage de créer des fonctions personnalisées est la facilité d'utilisation.Vous n'avez pas à vous soucier de créer des formules complexes à chaque fois, car la majeure partie du travail s'effectue dans le backend VBA.
J'ai créé une formule simple (un peu comme la formule VLOOKUP).
Pour créer des fonctions personnalisées, vous avez besoin du code VBA dans l'éditeur VB.Je vous donnerai le code et les étapes pour l'intégrer à l'éditeur VB plus tard, mais laissez-moi d'abord vous montrer comment cela fonctionne :
Voici la formule qui vous donnera le résultat :
=LastItemLookup($D$3,$A$2 :$B$14,2)
La formule prend trois paramètres :
- Trouvez la valeur (ce sera le nom dans la cellule D3)
- Trouvez la plage (ce sera la plage avec le nom et la date - A2: B14)
- numéro de colonne (c'est la colonne pour laquelle on veut le résultat)
Après avoir créé la formule et placé le code dans l'éditeur VB, vous pouvez l'utiliser comme n'importe quelle autre fonction de feuille de calcul Excel standard.
Voici le code de la formule :
Fonction 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
Voici les étapes pour mettre ce code dans l'éditeur VB :
- Allez dans l'onglet développeur.
- Cliquez sur Options Visual Basic.Cela ouvrira l'éditeur VB dans le backend.
- Dans le volet Explorateur de projets de l'éditeur VB, cliquez avec le bouton droit sur n'importe quel objet du classeur dans lequel vous souhaitez insérer du code.Si vous ne voyez pas l'Explorateur de projets, accédez à l'onglet Affichage et cliquez sur Explorateur de projets.
- Allez dans Insérer et cliquez sur Modules.Cela insérera un objet module pour votre classeur.
- Copiez et collez le code dans la fenêtre du module.
Maintenant, la formule sera disponible dans toutes les feuilles du classeur.
Notez que vous devrez enregistrer le classeur au format .XLSM car il contient des macros.De plus, si vous souhaitez que cette formule soit disponible dans tous les classeurs que vous utilisez, vous pouvez l'enregistrer dans votre classeur de macros personnel ou créer un complément à partir de celui-ci.
