Vous connaissez l’utilité de la formule ARRAY_CONSTRAIN dans Google Sheets ? Cependant, cette fonction n’est pas très connue et il existe des alternatives disponibles.
La fonction ARRAY_CONSTRAIN s’avère très utile dans des cas spécifiques. Il est important de ne pas la confondre avec la fonction ARRAYFORMULA de Google Sheets, car elles ont des objectifs différents, la seconde étant plus puissante.
Laissez-moi vous expliquer l’utilisation de la formule ARRAY_CONSTRAIN dans Google Sheets.
Syntaxe de la fonction ARRAY_CONSTRAIN dans Google Sheets
ARRAY_CONSTRAIN(INPUT_RANGE, NUM_ROWS, NUM_COLS)
Arguments :
input_range: La plage à restreindre, par exemple, A1:C20 ou ‘source’ (nom de la plage).num_rows: Le nombre de lignes que la formule devrait contenir en sortie. Par exemple, dans la plageA1:C20ci-dessus, il y a vingt lignes. Vous pouvez limiter ces lignes à 10 (A1:C10) dans le résultat en spécifiant lenum_rowscomme 10.num_cols: Le nombre de colonnes que la formule doit contenir en sortie. Dans la plage ci-dessus, le nombre de colonnes est de trois. Vous pouvez le limiter à 2 (A1:B20) en spécifiant lenum_colscomme 2.
Exemple basique : Restriction des lignes et des colonnes
Voici une image qui affiche une formule ARRAY_CONSTRAIN dans Google Sheets et son résultat. J’ai également marqué tous les paramètres utilisés dans la formule pour votre référence rapide.
Formule utilisée dans la cellule A14 : =ARRAY_CONSTRAIN(A1:H12, 5, 4)
Dans cette formule, A1:H12 représente la plage de données entière, également connue sous le nom de plage input_range. La formule est configurée pour renvoyer 5 lignes et 4 colonnes. Par conséquent, le résultat comprend les cinq premières lignes et les quatre premières colonnes.
J’ai appliqué la formule ci-dessus dans la cellule A14, comme illustré dans l’image.
Cet exemple devrait clarifier l’utilisation de la fonction ARRAY_CONSTRAIN dans Google Sheets.
Apprenez l’utilisation de la formule ARRAY_CONSTRAIN avec des exemples pratiques
Ci-dessous, vous trouverez deux exemples démontrant l’utilisation pratique de la fonction ARRAY_CONSTRAIN dans Google Sheets.
Ces exemples impliquent deux fonctions populaires : QUERY et FILTER. Même si vous n’êtes pas encore familier avec ces fonctions, vous pouvez suivre les exemples en vous concentrant simplement sur la partie ARRAY_CONSTRAIN.
La fonction ARRAY_CONSTRAIN en conjonction avec QUERY
En général, il n’est pas nécessaire d’utiliser la fonction ARRAY_CONSTRAIN avec la formule QUERY, car vous pouvez limiter les colonnes en utilisant la clause SELECT et les lignes en utilisant les clauses LIMIT et OFFSET dans la formule QUERY elle-même.
Considérez la formule QUERY suivante, qui récupère les lignes contenant le groupe d’âge ‘5-10’ dans la colonne B :
=QUERY(source, "select A, B, C, D, E, F where B='5-10' limit 1", 1)
Note : ‘source’ est une plage nommée contenant plusieurs lignes et colonnes (A1:Z). Le résultat comprendra 6 colonnes et deux lignes, y compris la ligne d’en-tête.
Dans la clause LIMIT de la QUERY, vous pouvez spécifier le nombre de lignes à renvoyer. Cependant, pour les colonnes, vous devez les spécifier individuellement dans la clause SELECT (par exemple, select A, B, C, D, E, F), sauf si vous êtes un utilisateur avancé de QUERY.
Pour les utilisateurs avancés de QUERY, vous avez la possibilité d’automatiser la sélection des colonnes en utilisant la méthode expliquée dans « Comment obtenir une référence de colonne dynamique dans Google Sheets QUERY ».
Maintenant, explorons comment utiliser la fonction ARRAY_CONSTRAIN de Google Sheets avec QUERY pour limiter les colonnes. La formule suivante ne renvoie que deux lignes et six colonnes :
=ARRAY_CONSTRAIN(QUERY(source, "select * where B='5-10' "), 2, 6)
Dans la QUERY, j’ai utilisé l’astérisque (*) dans la clause SELECT pour afficher toutes les colonnes de la plage ‘source’. Ensuite, j’ai limité la sortie à 6 colonnes et 2 lignes en utilisant ARRAY_CONSTRAIN.
Ce type d’utilisation d’ARRAY_CONSTRAIN est utile lorsque vous avez besoin de renvoyer un grand nombre de colonnes qui ne sont pas facilement spécifiées une par une dans la clause SELECT de la QUERY. De même, vous pouvez contrôler la sortie du filtre à un nombre spécifique de lignes.
La fonction ARRAY_CONSTRAIN en conjonction avec la fonction FILTER
Voici une autre application de cette fonction. Tout d’abord, observez cette formule standard Filter :
=FILTER(FichierPrincipal!A2:F16, FichierPrincipal!B2:B16="5-10")
Ci-dessous, j’ai restreint la sortie de la formule Filter avec ARRAY_CONSTRAIN :
=ARRAY_CONSTRAIN(FILTER(FichierPrincipal!A2:F16, FichierPrincipal!B2:B16="5-10"), 2, 6)
Paramètres dynamiques NUM_ROWS et NUM_COLS
Dans tous les exemples ci-dessus, nous avons utilisé des nombres prédéfinis tels que 2 et 6 pour num_rows et num_cols dans les formules ARRAY_CONSTRAIN.
Explorons comment les utiliser de manière dynamique, en d’autres termes, en utilisant des expressions pour num_rows et num_cols.
Considérez une feuille nommée « Feuille1 » contenant 1000 lignes et 26 colonnes, c’est-à-dire A1:Z1000.
La première ligne contient des en-têtes, et les données correspondantes se trouvent en dessous dans chaque colonne. Nous ne connaissons pas le nombre total de colonnes et de lignes remplies par les données.
S’il n’y a pas de cellules vides dans la ligne d’en-tête A1:Z1 et dans la première colonne A1:A1000, vous pouvez utiliser la formule ARRAY_CONSTRAIN suivante dans une autre feuille de ce fichier pour filtrer uniquement les données, en excluant les lignes et les colonnes vides.
=ARRAY_CONSTRAIN(Feuille1!A1:Z, COUNTA(Feuille1!A1:A), COUNTA(Feuille1!A1:Z1))
Si « Feuille1 » est vide, cette formule renverra une erreur #REF! et vous ne pourrez pas l’éliminer à l’aide de la fonction IFERROR. Dans ce cas, utilisez la mise en forme conditionnelle, comme expliqué dans ce tutoriel : Comment supprimer les erreurs #REF! dans Google Sheets (même lorsque IFERROR échoue).
Conclusion
La fonction ARRAY_CONSTRAIN dans Google Sheets peut être utilisée en conjonction avec d’autres fonctions qui renvoient un résultat de tableau.
Une autre fonction à noter, qui n’a pas été mentionnée initialement, est la fonction SORTN dans Google Sheets. SORTN peut aider à limiter le nombre de lignes en sortie, bien qu’il trie également le résultat.
Lectures connexes :
- Trois façons différentes de limiter le nombre de lignes dans Google Sheets Query.
- Comment effectuer un décalage correspondant à l’aide de la requête dans Google Sheets.
