Avec plusieurs niveaux de tri, vous pouvez trouver la dernière ligne de chaque groupe dans Google Sheets. J’utilise une combinaison de formules SORT et SORTN pour extraire la dernière ligne de chaque groupe. Cela fonctionne également avec des groupes à plusieurs colonnes.
Nous pouvons identifier le groupe à partir d’une colonne de valeurs identiques. Regardez l’exemple de jeu de données ci-dessous en portant une attention particulière à la colonne B.
Dans cet exemple de données, Apple, Mango et Orange dans la colonne B représentent trois groupes.
La dernière ligne de chaque groupe de ces données est la ligne n° 5 pour Apple, la ligne n° 9 pour Mango et la ligne n° 14 pour Orange. Je veux seulement extraire ces lignes de l’ensemble de données.
J’ai une formule simple combinant SORT et SORTN pour trouver la dernière ligne de chaque groupe dans Google Sheets.
Dans la ligne n° 15, si vous avez une nouvelle entrée pour Apple, la formule considérera cette entrée comme la dernière, et non la ligne n° 5.
Cela signifie que les enregistrements du groupe peuvent ne pas être nécessaires dans les lignes adjacentes. Ils peuvent être dans des lignes distantes.
La formule pour trouver la dernière ligne de chaque groupe dans Google Sheets
J’ai utilisé la combinaison suivante de SORT et SORTN dans la cellule F2 pour extraire la dernière ligne de chaque groupe d’éléments dans Google Sheets.
=SORTN(SORT(B2:D,ROW(B2:B),0),COUNTA(UNIQUE(B2:B)),2,1,TRUE)
Avant d’expliquer comment coder une formule comme celle-ci, laissez-moi vous expliquer comment modifier cette formule pour répondre à vos besoins en matière d’ensemble de données.
Vous pouvez vouloir regrouper les données en fonction d’une autre colonne, et non de la colonne B.
Dans ma formule, B2:B contient les noms de fruits. C’est la première colonne de la plage, et je veux regrouper cette plage de colonnes.
Le chiffre 1 dans la formule indique la colonne B. Vous devez changer ce chiffre en fonction du numéro de colonne de regroupement dans votre plage de données.
De plus, la plage de colonnes de regroupement B2:B apparaît dans la formule. Remplacez-la par votre plage de colonnes de regroupement.
Modifiez donc ma formule Google Sheets en conséquence pour obtenir la dernière entrée de chaque groupe dans votre ensemble de données.
Pouvez-vous m’expliquer comment cette formule extrait la dernière ligne de chaque groupe ?
Bien sûr !
Voici la logique derrière la formule qui trouve la dernière entrée de chaque groupe dans Google Sheets.
Étape 1
J’ai les données existantes dans la plage B2:D (un total de trois colonnes).
Nous devons trier cela dans la plage en fonction des numéros de lignes. Et l’ordre de tri dans la fonction SORT doit être décroissant. Cela placera les dernières entrées en haut de chaque groupe.
Je suppose que les dernières entrées sont en bas de la feuille, et non au début.
Syntaxe : SORT(plage, colonne_de_tri, est_croissant, [colonne_de_tri2, …], [est_croissant2, …])
Formule : SORT(B2:D,ROW(B2:B),0)
Le tri peut contenir un tas de lignes vides en haut. Cela ne posera aucun problème.
Étape 2 (Formule finale)
Après la requête, SORTN est l’une de mes fonctions préférées dans Google Sheets.
En utilisant cette fonction, nous pouvons former une formule de combinaison fantastique dans Google Sheets pour supprimer les doublons dans les plages de données.
La formule SORTN ci-dessous renvoie la dernière entrée dans chaque ligne de Google Sheets.
Syntaxe : SORTN(plage, [n], [mode_affichage_des_égalités], [colonne_de_tri], [est_croissant], [colonne_de_tri2, …], [est_croissant2, …])
Formule : SORTN(SORT(B2:D,ROW(B2:B),0),COUNTA(UNIQUE(B2:B)),2,1,TRUE)
Où :
- n :
COUNTA(UNIQUE(B2:B))
Selon mes données d’exemple, nous avons trois groupes. Nous pouvons donc spécifier le nombre 3 à la place du COUNTA ci-dessus. Mais ce n’est pas une méthode dynamique.
Le COUNTA ci-dessus renvoie le nombre de valeurs uniques dans la colonne de groupe. C’est égal au nombre de lignes que nous voulons dans le résultat.
- mode_affichage_des_égalités : 2
Utilisez ce mode d’égalité pour récupérer les premiers enregistrements d’une plage donnée après avoir supprimé les enregistrements en double.
Nous avons appliqué SORTN sur une plage de données triée par ordre décroissant en fonction des numéros de lignes.
Ainsi, la sortie de SORTN sera la dernière entrée dans chaque groupe.
- colonne_de_tri : 1
La formule supprime les doublons en fonction de la colonne de tri. Elle doit donc être l’index de la colonne de groupe. C’est 1 dans notre cas.
- est_croissant : TRUE
Cela ne fera pas beaucoup de différence car il s’agit du tri de la colonne du groupe. Vous pouvez également spécifier FALSE.
Et en cas de plusieurs colonnes de regroupement ?
C’est également possible. Regroupons-nous sur les colonnes B et D dans l’exemple de données ci-dessus.
Vous pouvez alors utiliser la formule suivante :
=LET( srtd, SORT(B2:D,ROW(B2:B),0), LET( result, SORTN(srtd,9^9,2,CHOOSECOLS(srtd,1)&CHOOSECOLS(srtd,3),1), FILTER(result,CHOOSECOLS(result,1)<>"") ) )
Cette fois, je vais expliquer les changements que vous devez apporter pour adapter la formule ci-dessus, qui renvoie la dernière entrée de chaque groupe dans un groupe à plusieurs colonnes.
La partie du tri, c’est-à-dire SORT(B2:D,ROW(B2:B),0), est la même que précédemment (étape 1).
Je groupe les données en fonction des colonnes 1 (B2:B) et 3 (D2:D). Voici comment je les ai spécifiées dans la formule.
CHOOSECOLS(srtd, 1) – détermine la première colonne de groupe.
CHOOSECOLS(srtd, 3) – détermine la deuxième colonne de groupe.
Vous voudrez peut-être modifier 1 et 3 dans ces formules en fonction des index de colonnes de groupe dans vos données.
Astuce :
Le but de LET est d’assigner un nom au tri (formule de l’étape 1), c’est-à-dire srtd, et CHOOSECOLS est de sélectionner des colonnes spécifiques à partir du résultat trié.
Et voilà ! Vous savez maintenant comment trouver la dernière ligne de chaque groupe dans Google Sheets. Amusez-vous bien avec vos données et explorez les nombreuses possibilités qu’offre la combinaison des formules SORT et SORTN. Si vous souhaitez en savoir plus sur Google Sheets et d’autres astuces SEO, rendez-vous sur Crawlan.com.
Related:
