Google Sheets : ARRAY_CONSTRAIN, késaco ?

Le but de cet article est de comprendre à quoi sert la méconnue fonction ARRAY_CONSTRAIN de la famille tableau au même titre que SOMMEPROD ou TRANSPOSE. Il est important de ne pas la confondre avec l’indispensable ARRAYFORMULA qui commence pareil mais n’a strictement rien à voir.

Si on jette un coup d’œil rapide dans le centre d’aide, on peut y lire la définition : limite (ou contraint), le résultat d’un tableau à une taille donnée. En effet, dit autrement (car clairement la traduction du centre d’aide Google Sheets laisse parfois à désirer 🧐), le but de la fonction Array_Constrain est de contraindre un résultat de tableau dans un nombre spécifié de lignes et de colonnes. En utilisant cette fonction, nous pouvons limiter le nombre de lignes et aussi les colonnes.

Démonstration en image pour bien comprendre le concept (où je désire 4 lignes et 4 colonnes de mon tableau).

Syntaxe de la fonction ARRAY_CONSTRAIN dans Google Sheets

La Syntaxe de la formule :

ARRAY_CONSTRAIN(plage_entrée; nombre_de_lignes; nombre_de_colonnes)
plage_entrée : plage de valeurs concernée.
nombre_de_lignes : nombre de lignes que le résultat doit contenir.
nombre_de_colonnes : nombre de colonnes que le résultat doit contenir.

Déchiffrage :

Plage_entrée : c’est la plage à contraindre. Par exemple A1:G7 ou ‘data’ (s’il s’agit d’une plage nommée).

Nombre_de_lignes : c’est le nombre de lignes que le résultat de la formule doit afficher. Par exemple, dans la plage d’entrée A1: G7, il y a sept lignes. Nous pouvons limiter ces lignes à quatre (A1: G4) dans le résultat en spécifiant ‘nombre_de_lignes’ comme quatre.

Nombre_de_colonnes : c’est le nombre de colonnes que le résultat de la formule doit afficher. Ici, dans notre exemple, le nombre de colonnes est de sept (A à G). Limitez cela à quatre (A1: G7) en spécifiant ‘nombre_de_colonnes’ comme quatre.

Exemple de base – Contraindre les lignes et les colonnes

=ARRAY_CONSTRAIN(A1:G7;4;4)

J’ai appliqué la formule ci-dessus dans la cellule A10.

Dans cette formule, A1:G7 est la plage de données entière. Le nombre de lignes à renvoyer selon la formule est de 4 et le nombre de colonnes de 4. De toute évidence, la formule renvoie les 4 premières lignes et les 4 premières colonnes.

Voici, sans plus tarder, une copie du fichier pour que vous puissiez jouer un peu avec. « C’est en Sheetant qu’on devient Sheeter »🚬

Plutôt simple, non ? Mais à quoi cela peut-il bien servir ?😂

Quelques cas d’usages de la formule ARRAY_CONSTRAIN avec des exemples pratiques

Vous trouverez ci-dessous deux exemples d’utilisation pratique de la fonction Array Constrain dans Google Sheets.

Cela fait appel à deux autres fonctions magiques de Google Sheets à connaître : QUERY & FILTER. Même si vous n’êtes pas encore familiarisé avec ces deux fonctions, vous pouvez suivre les exemples.

La Fonction ARRAY_CONSTRAIN avec QUERY

Les puristes vous diront qu’il n’est pas nécessaire d’utiliser Array_Constrain avec la formule Query. Parce que nous pouvons limiter les colonnes en utilisant la clause SELECT et les lignes en utilisant la clause LIMIT et OFFSET dans la fonction QUERY elle-même. (Phrase à apprendre et à ressortir pour épater le prochain formateur ou collègue qui viendra vous challenger sur Sheets) 🤓

La formule QUERY renverra les lignes, y compris la ligne d’en-tête qui contient « Poissons » dans la colonne B en remettant dans l’ordre les colonnes, d’abord la B, puis la A, puis la C.

=query(A1:C31;"select B,A,C where B='Poissons'")

Dans la clause limit de Query, nous pouvons spécifier le nombre de lignes à renvoyer. Mais pour les colonnes, nous devons le spécifier individuellement dans la clause SELECT, select A,B,C,D,E,F

Voyons comment nous pouvons utiliser la fonction ARRAY_CONSTRAIN de Google Sheets avec QUERY pour limiter les colonnes. La formule suivante ne renverra que 10 lignes et 2 colonnes.

=array_constrain(query(A1:C31;"select A,C where B='Fruits'");10;2)

Ce type d’utilisation de Array_Constrain sera avantageux lorsque vous devez renvoyer un grand nombre de colonnes et qu’il n’est pas facile de les spécifier une par une dans la clause select de Query. 

Fonction ARRAY_CONSTRAIN avec la fonction de filtre FILTER

Voici encore une autre utilisation de cette fonction. Tout d’abord, voyez cette formule FILTER sans ARRAY_CONSTRAIN. J’ai sur la gauche une base de données et je ne souhaite un filtre que sur la catégorie « Poissons ».

=filter(A1:C31;B1:B="poissons")

Ci-dessous, j’ai limité le résultat de la formule FILTER avec ARRAY_CONSTRAIN

En français : filtre moi la base de données A1:C31, en ne gardant que la catégorie « Poissons », dont le nombre de calories pour 100 g est inférieur à 300, et mets moi juste les 5 premiers résultats avec les 3 colonnes.

=ARRAY_CONSTRAIN(filter(A1:C31;B1:B="poissons";C1:C<300);5;3)

Conclusion sur la formule ARRAY_CONSTRAIN

La fonction Array_Constrain dans Google Sheets peut également être utilisée en combinaison avec d’autres fonctions qui renvoient un résultat de tableau. Nous l’avons vu avec Filter & Query. Sachez également que la puissante et méconnue (on y travaille chez Numeriblog) fonction SORTN peut contraindre les lignes, mais triera également le résultat. À suivre…

N’hésitez pas à nous contacter pour vos besoins en formation. Les modules sur Sheets chez Numericoach, organisme de formation datadocké, sont éligibles au CPF, avec prises en charge FAF et OPCO.