La fonction CHOOSEROWS dans Google Sheets génère un nouvel ensemble de données en sélectionnant les numéros de ligne spécifiés à partir de la plage existante.
Vous pouvez utiliser des numéros de ligne positifs ou négatifs.
Lorsque des numéros de ligne négatifs sont fournis, le comptage commence à partir de la dernière ligne de la plage (du bas vers le haut), alors qu’il commence à partir du haut lorsque des numéros de ligne positifs sont utilisés.
Cette fonction offre différentes possibilités de manipulation des données dans Google Sheets.
Par exemple, la fonction CHOOSEROWS peut être utilisée en combinaison avec XMATCH et TOCOL pour pallier une limitation de la fonction XLOOKUP.
Quelle est cette limitation ?
Dans les situations impliquant plusieurs clés de recherche, XLOOKUP récupérera une valeur à partir d’une seule colonne, même si la plage de résultats contient plusieurs colonnes.
Nous allons approfondir cela plus en détail ci-dessous.
Fonction CHOOSEROWS – Syntaxe et arguments
Syntaxe:
CHOOSEROWS(plage, num_ligne1, [num_ligne2, …])
Arguments de la fonction CHOOSEROWS dans Google Sheets:
- plage : Le tableau ou la plage source.
- num_ligne1 : Le numéro de ligne (dans le tableau) de la première ligne à renvoyer.
- num_ligne2, … : Le(s) numéro(s) de ligne supplémentaire(s) à renvoyer, le cas échéant.
Les numéros de ligne peuvent être des entiers positifs ou négatifs. Si le numéro de ligne n’est pas un entier, la fonction l’arrondira vers le bas.
Utilisation de base
Supposons que nous ayons un tableau contenant les noms, les notes et les classements des étudiants, triés en fonction du classement, où les premiers étudiants en tête de liste ont le meilleur classement.
Pour récupérer les détails de l’étudiant classé 5ème, en excluant la ligne d’en-tête, nous pouvons utiliser la fonction CHOOSEROWS dans Google Sheets.
=CHOOSEROWS(B3:D12, 5)
Cette fonction a été introduite en 2023, mais nous avions des solutions alternatives par le passé. Voici ces formules:
- Index.
=INDEX(B3:D12, 5, 0)
- Query.
=QUERY(B3:D12, "SELECT * LIMIT 1 OFFSET 4", 0)
- Offset.
=OFFSET(B3, 4, 0, 1, 3)
Maintenant, supposons que nous voulions obtenir les détails des étudiants classés 5ème, 6ème et 7ème. Pour ce faire avec les formules ci-dessus, nous pouvons utiliser une constante de tableau comme {5, 6, 7} ou SEQUENCE(3, 1, 5) dans la fonction CHOOSEROWS de Google Sheets.
Exemples :
=CHOOSEROWS(B3:D12, {5, 6, 7})
=CHOOSEROWS(B3:D12, SEQUENCE(3, 1, 5))
Cependant, la fonction INDEX n’est pas adaptée à cette utilisation. Néanmoins, QUERY et OFFSET peuvent gérer cette exigence de manière efficace.
=QUERY(B3:D12, "SELECT * LIMIT 3 OFFSET 4", 0)
=OFFSET(B3, 4, 0, 3, 3)
Fonction CHOOSEROWS pour retourner un tableau inversé dans Google Sheets
Dans mon dernier tutoriel, nous avons exploré comment retourner un tableau de droite à gauche en utilisant la fonction CHOOSECOLS dans Google Sheets.
De manière similaire, nous pouvons obtenir le même résultat en inversant un tableau de bas en haut en utilisant la fonction CHOOSEROWS. Voici comment procéder :
Supposons que A2:D7 soit la plage à inverser de bas en haut, vous pouvez utiliser la formule suivante :
=CHOOSEROWS(A2:D7, SEQUENCE(ROWS(A2:A7), 1, -1, -1))
La formule SEQUENCE(ROWS(A2:A7), 1, -1, -1) génère une série de nombres négatifs de -1 à -7. En conséquence, la formule renvoie les lignes de bas en haut.
Formule alternative :
=SORT(A2:D7, SEQUENCE(ROWS(A2:A7)), 0)
Cette formule alternative obtient le même effet d’inversion en utilisant la fonction TRIER en conjonction avec la séquence pour trier les lignes dans l’ordre décroissant.
Vous pourriez également être intéressé par : Comment inverser une colonne dans Google Sheets – Colonnes finies et infinies.
Limitation de XLOOKUP à deux dimensions et formule alternative
Rechercher des informations requises manuellement dans un grand ensemble de données peut prendre du temps. Les applications de tableur fournissent des fonctions de recherche intégrées pour simplifier ce processus, et XLOOKUP est l’un des choix populaires.
Avec XLOOKUP, nous pouvons rechercher une clé dans une colonne spécifiée d’un tableau et récupérer les informations nécessaires de toutes les colonnes ou de colonnes spécifiques. Cependant, lorsqu’il s’agit de rechercher plusieurs clés, XLOOKUP présente une limite car il ne renvoie des informations que d’une seule colonne.
Actuellement, la fonction XLOOKUP ne prend pas en charge le renvoi d’un tableau à deux dimensions. Pour contourner cette limitation, nous pouvons utiliser une combinaison de XMATCH, TOCOL et CHOOSEROWS.
Voici un exemple illustrant cette approche alternative.
La fonction CHOOSEROWS pour résoudre le problème du tableau à deux dimensions avec XLOOKUP dans Google Sheets
Problème :
Recherchez « fruits » et « pulses » dans la première colonne du tableau et renvoyez le résultat de la ligne entière (sauf la première colonne), en recherchant de la dernière entrée à la première.
=ArrayFormula(XLOOKUP(B13:B14, B2:B7, C2:E7, "", 0, -1))
Cette formule renverra UNIQUEMENT « strawberry » et « split peas » de la première colonne de la plage de résultat.
Dans cette formule :
- search_key : B13:B14 (deux clés de recherche)
- lookup_range : B2:B7 (la plage à rechercher)
- result_range : C2:E7 (tableau à deux dimensions)
- missing_value : « » (la valeur à renvoyer en cas de non-correspondance)
- match_mode : 0 (correspondance exacte des clés de recherche)
- search_mode : -1 (recherche de la dernière entrée à la première)
Solution :
Utilisons la fonction CHOOSEROWS avec TOCOL et XMATCH pour résoudre le problème ci-dessus.
=ArrayFormula(CHOOSEROWS(B2:E7, TOCOL(XMATCH(B13:B14, B2:B7, 0, -1), 3)))
Voici la syntaxe de XMATCH :
XMATCH(search_key, lookup_range, [match_mode], [search_mode])
XMATCH renvoie les numéros de ligne pour les clés de recherche correspondantes et #N/A pour celles qui ne correspondent pas.
Le rôle de TOCOL ici est d’éliminer les erreurs #N/A. Bien sûr, nous pouvons utiliser IFNA pour cela. Cependant, cela laisse une cellule vide qui pourrait empêcher CHOOSEROWS de fonctionner.
Nous avons utilisé ces numéros d’index correspondants dans la fonction CHOOSEROWS pour renvoyer les lignes pertinentes.
