Dans ce didacticiel, vous apprendrez à trouver la dernière occurrence d'un élément dans une liste à l'aide de formules Excel.
Récemment, je travaillais sur l'établissement de l'ordre du jour d'une réunion.
J'avais une liste dans Excel où j'avais une liste de personnes et les dates auxquelles elles ont agi en tant que « président de la réunion ».
Comme il y avait des répétitions dans la liste (ce qui signifie qu'une personne a été présidente de réunion plusieurs fois), j'avais également besoin de savoir à quand remonte la dernière fois qu'une personne a agi en tant que « président de réunion ».
C'était parce que je devais m'assurer que quelqu'un qui a récemment présidé n'a pas été affecté à nouveau.
J'ai donc décidé d'utiliser la magie de la fonction Excel pour y parvenir.
Ci-dessous se trouve le résultat final où je peux sélectionner un nom dans la liste déroulante et il 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.
Mais vous êtes dans la section Formula Hack, et ici, nous réalisons la magie.
Dans ce tutoriel, je vais vous montrer trois façons de le faire.
Trouver la dernière occurrence - Utilisation de la fonction MAX
Le mérite de cette technique revient à un article du MVP d'Excel Charley Kyd.
Voici la formule Excel qui renverra la dernière valeur de la liste :
=INDICE($B$2:$B$14,SUMPRODUCT(MAX(ROW($A$2:$A$14)*($D$3=$A$2:$A$14))-1))
Voici comment fonctionne cette formule :
- 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 renverrait 11, car il se trouve dans la ligne 11. Puisque notre liste commence à partir de la deuxième ligne, 1 a été soustrait. La position de la dernière occurrence de Glen est donc 10 sur notre liste.
- SUMPRODUCT est utilisé pour s'assurer que vous n'avez pas à utiliser Control + Shift + Enter, 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 - Utilisation de la fonction LOOKUP
Voici une autre formule pour faire le même travail :
=RECHERCHE(2,1/($A$2:$A$14=$D$3),$B$2:$B$14)
Voici comment fonctionne cette formule :
- La valeur de recherche est 2 (vous verrez pourquoi… continuez à lire)
- La plage de recherche est 1/($A$2:$A$14=$D$3) - Cela renvoie 1 lorsqu'il trouve le nom correspondant et une erreur dans le cas contraire. Vous obtenez donc un tableau. Par exemple, si la valeur de recherche est Glen, le tableau serait {#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 argument ([result_vector]) est la plage à partir de laquelle il donne le résultat, qui sont des dates dans ce cas.
La raison pour laquelle cette formule fonctionne est que la fonction RECHERCHE utilise la technique de correspondance approximative. Cela signifie que s'il peut trouver la valeur correspondante exacte, il la renverra, mais s'il ne le peut pas, il analysera l'ensemble du 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 n'obtiendrons que des 1 ou des erreurs. Il scanne donc l'ensemble du tableau et renvoie la position du dernier 1 - qui est la dernière valeur correspondante du nom.
Trouver la dernière occurrence - Utilisation de la fonction personnalisée (VBA)
Permettez-moi également de vous montrer une autre façon de procéder.
Nous pouvons créer une fonction personnalisée (également appelée fonction définie par l'utilisateur) à l'aide de VBA.
L'avantage de créer une fonction personnalisée est qu'elle est facile à utiliser. Vous n'avez pas à vous soucier de créer une formule complexe à chaque fois, car la plupart du travail se fait dans le backend VBA.
J'ai créé une formule simple (qui ressemble beaucoup à la formule RECHERCHEV).
Pour créer une fonction personnalisée, vous devez avoir le code VBA dans l'éditeur VB. Je vais vous donner le code et les étapes pour le placer dans l'éditeur VB dans un moment, 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 arguments :
- Valeur de recherche (ce serait le nom dans la cellule D3)
- Plage de recherche (ce serait la plage qui a les noms et les dates - A2:B14)
- Numéro de colonne (il s'agit de la colonne à partir de laquelle nous voulons le résultat)
Une fois que vous avez créé la formule et mis le code dans VB Editor, vous pouvez l'utiliser comme n'importe quelle autre fonction de feuille de calcul Excel standard.
Voici le code de la formule :
'Il s'agit d'un code pour une fonction qui trouve la dernière occurrence d'une valeur de recherche et renvoie la valeur correspondante de la colonne spécifiée 'Code créé par Sumit Bansal (https://trumpexcel.com) 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) Quitter la fonction Fin si suivant i Fin de la fonction
Voici les étapes pour placer ce code dans l'éditeur VB :
- Allez dans l'onglet Développeur.
- Cliquez sur l'option 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 le 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 Module. Cela insérera un objet module pour votre classeur.
- Copiez et collez le code dans la fenêtre du module.
Maintenant, la formule serait disponible dans toute la feuille de calcul du classeur.
Notez que vous devez enregistrer le classeur au format .XLSM car il contient une macro. De plus, si vous souhaitez que cette formule soit disponible dans tous les classeurs que vous utilisez, vous pouvez soit l'enregistrer dans le classeur de macros personnelles, soit créer un complément à partir de celui-ci.
