Table des matières
Toutes les fonctions classiques de recherche et de substitution de type VPR (VLOOKUP), GPR (RECHERCHEH), PLUS EXPOSÉ (RENCONTRE) et ceux qui leur ressemblent ont une caractéristique importante : ils effectuent une recherche du début à la fin, c'est-à-dire de gauche à droite ou de haut en bas dans les données sources. Dès que la première correspondance correspondante est trouvée, la recherche s'arrête et seule la première occurrence de l'élément dont nous avons besoin est trouvée.
Que faire si nous devons trouver non pas la première, mais la dernière occurrence ? Par exemple, la dernière transaction pour le client, le dernier paiement, la dernière commande, etc. ?
Méthode 1 : Recherche de la dernière ligne avec une formule matricielle
Si la table d'origine n'a pas de colonne avec une date ou un numéro de série d'une ligne (commande, paiement …), alors notre tâche est, en fait, de trouver la dernière ligne qui satisfait la condition donnée. Cela peut être fait avec la formule matricielle suivante :
Ici:
- Fonction IF (SI) vérifie toutes les cellules d'une colonne une par une Témoignages et affiche le numéro de ligne si elle contient le nom dont nous avons besoin. Le numéro de ligne sur la feuille nous est donné par la fonction LINE (LIGNE), mais comme nous avons besoin du numéro de ligne dans le tableau, nous devons en plus soustraire 1, car nous avons un en-tête dans le tableau.
- Alors la fonction MAX (MAXI) sélectionne la valeur maximale dans l'ensemble formé de numéros de lignes, c'est-à-dire le numéro de la ligne la plus récente du client.
- Fonction INDEX (INDICE) renvoie le contenu de la cellule avec le dernier numéro trouvé de toute autre colonne de table requise (Code de commande).
Tout cela doit être saisi comme formule matricielle, c'est à dire:
- Dans Office 365 avec les dernières mises à jour installées et la prise en charge des tableaux dynamiques, vous pouvez simplement appuyer sur Entrer.
- Dans toutes les autres versions, après avoir entré la formule, vous devrez appuyer sur le raccourci clavier Ctrl+Shift+Entrer, qui lui ajoutera automatiquement des accolades dans la barre de formule.
Méthode 2 : Recherche inversée avec la nouvelle fonction LOOKUP
J'ai déjà écrit un long article avec une vidéo sur une nouvelle fonctionnalité VOIR (XRECHERCHE), qui est apparu dans les dernières versions d'Office pour remplacer l'ancien VLOOKUP (VLOOKUP). Avec l'aide de BROWSE, notre tâche est résolue de manière assez élémentaire, car. pour cette fonction (contrairement à RECHERCHEV), vous pouvez définir explicitement le sens de la recherche : de haut en bas ou de bas en haut - son dernier argument (-1) en est responsable :
Méthode 3. Rechercher une chaîne avec la dernière date
Si dans les données source, nous avons une colonne avec un numéro de série ou une date qui joue un rôle similaire, alors la tâche est modifiée - nous devons trouver non pas la dernière ligne (la plus basse) avec une correspondance, mais la ligne avec la dernière ( date maximale).
J'ai déjà expliqué en détail comment procéder en utilisant des fonctions classiques, et essayons maintenant d'utiliser la puissance des nouvelles fonctions de tableau dynamique. Pour plus de beauté et de commodité, nous convertissons également la table d'origine en une table "intelligente" à l'aide d'un raccourci clavier Ctrl+T ou des commandes Accueil – Format sous forme de tableau (Accueil — Format sous forme de tableau).
Avec leur aide, ce « couple tueur » résout notre problème de manière très gracieuse :
Ici:
- Fonction d'abord FILTRE (FILTRE) sélectionne uniquement les lignes de notre table où dans la colonne Témoignages – le nom dont nous avons besoin.
- Alors la fonction QUALITÉ (TRIER) trie les lignes sélectionnées par date dans l'ordre décroissant, avec la transaction la plus récente en haut.
- Fonction INDEX (INDICE) extrait la première ligne, c'est-à-dire renvoie le dernier échange dont nous avons besoin.
- Et, enfin, la fonction externe FILTER supprime les 1ère et 3ème colonnes supplémentaires des résultats (Code de commande и Témoignages) et ne laisse que la date et le montant. Pour cela, un tableau de constantes est utilisé. {0;1;0;1}, définissant les colonnes que nous voulons (1) ou ne voulons pas (0) afficher.
Méthode 4 : Recherche de la dernière correspondance dans Power Query
Eh bien, par souci d'exhaustivité, examinons une solution à notre problème de recherche inversée à l'aide du complément Power Query. Avec son aide, tout est résolu très rapidement et magnifiquement.
1. Convertissons notre tableau d'origine en un tableau "intelligent" à l'aide d'un raccourci clavier Ctrl+T ou des commandes Accueil – Format sous forme de tableau (Accueil — Format sous forme de tableau).
2. Chargez-le dans Power Query avec le bouton À partir du tableau/plage languette Données (Données – À partir du tableau/de la plage).
3. Nous trions (via la liste déroulante du filtre dans l'en-tête) notre tableau par ordre décroissant de date, de sorte que les transactions les plus récentes soient en haut.
4… Dans l'onglet De La Carrosserie
choisir une équipe Par groupe (Transformer — Regrouper par) et définissez le regroupement par clients, et en tant que fonction d'agrégation, sélectionnez l'option Toutes les lignes (Toutes les lignes). Vous pouvez nommer la nouvelle colonne comme bon vous semble, par exemple Détails.
Après le regroupement, nous obtiendrons une liste des noms uniques de nos clients et dans la colonne Détails – des tableaux avec toutes les transactions de chacun d'eux, où la première ligne sera la dernière transaction, ce dont nous avons besoin :
5. Ajouter une nouvelle colonne calculée avec le bouton Colonne personnalisée languette Ajouter une colonne (Ajouter une colonne — Ajouter une colonne personnalisée)et entrez la formule suivante :
Ici Détails – c'est la colonne à partir de laquelle nous prenons des tables par clients, et sept {} est le numéro de la ligne que nous voulons extraire (la numérotation des lignes dans Power Query commence à zéro). Nous obtenons une colonne avec des enregistrements (Enregistrement), où chaque entrée est la première ligne de chaque table :
Il reste à développer le contenu de tous les enregistrements avec le bouton à doubles flèches dans l'en-tête de colonne Dernière offre sélection des colonnes souhaitées :
… puis supprimez la colonne qui n'est plus nécessaire Détails en faisant un clic droit sur son titre – Supprimer des colonnes (Supprimer les colonnes).
Après avoir téléchargé les résultats sur la feuille via Accueil — Fermer et charger — Fermer et charger dans (Accueil — Fermer et charger — Fermer et charger dans…) nous obtiendrons un si joli tableau avec une liste des transactions récentes, comme nous le souhaitions :
Lorsque vous modifiez les données sources, il ne faut pas oublier de mettre à jour les résultats en faisant un clic droit dessus – la commande Mettre à jour et enregistrer (Rafraîchir) ou raccourci clavier Ctrl+autre+F5.
- La fonction LOOKUP est un descendant de VLOOKUP
- Comment utiliser les nouvelles fonctions de tableau dynamique SORT, FILTER et UNIC
- Recherche de la dernière cellule non vide d'une ligne ou d'une colonne avec la fonction LOOKUP