Comment utiliser l'ARRAYFORMULA de Google Sheets ?

Comme son nom l'indique, ARRAYFORMULA est une fonction de tableau. Elle permet de ajouter des formules qui se développent automatiquement; Elles sont appliquées au fur et à mesure que de nouvelles données sont introduites dans la feuille de calcul. Cela signifie que vous n'aurez pas à copier et coller les formules manuellement lorsque de nouvelles lignes de données apparaîtront.

Fondamentalement, il prend les valeurs d'une plage de cellules et les restitue sur plusieurs lignes et colonnes. Cela signifie qu'il vous permet utiliser des tableaux dans des formules qui ne les acceptent généralement pas, y compris SUMIF, VLOOKUP ou FILTER.

Voyons comment fonctionne l'ARRAYFORMULA et pourquoi vous en avez besoin dans votre vie.

Qu'est-ce que l'ARRAYFORMULA ?

L'ARRAYFORMULA est une fonction de tableau extensible. Cela signifie qu'il suffit d'insérer la fonction dans une cellule pour qu'elle s'applique automatiquement aux lignes suivantes sur toute la longueur d'une colonne.

Par conséquent, vous pouvez renvoyer plusieurs valeurs à partir d'une plage de cellules avec une seule ARRAYFORMULA, plutôt que de renvoyer une seule valeur à partir d'une formule qui n'est pas un tableau.

Examinons la syntaxe pour comprendre comment elle fonctionne et comment la combiner avec d'autres fonctions.

Syntaxe

La syntaxe de l'ARRAYFORMULA est la suivante :

formule_de_tableau - ce paramètre peut être :

• une gamme
• une expression mathématique utilisant une plage de cellules ou plusieurs plages de même taille
• une fonction qui renvoie un résultat supérieur à une cellule

Vous pouvez également ajouter la fonction ARRAYFORMULA à une fonction existante qui n'est pas un tableau dans votre feuille de calcul. Il vous suffit d'appuyer sur le raccourci clavier Ctrl/Cmd + Shift + Entrée pour que Google Sheets intègre automatiquement ARRAYFORMULA() à votre formule.

Comme vous pouvez le constater, la syntaxe ARRAYFORMULA est plus simple que prévu. Explorons d'autres avantages de cette fonction.

Pourquoi utiliser ARRAYFORMULA dans Google Sheets ?

L'ARRAYFORMULA permet non seulement d'économiser du temps et des efforts manuels en offrant une alternative aux calculs manuels, mais elle offre également d'autres avantages.

Examinons-en quelques-uns :

• Calculs de la taille des lots : Au lieu de devoir utiliser des fonctions similaires qui calculent les valeurs individuellement, vous pouvez utiliser une seule formule pour l'ensemble de vos données, quelle que soit leur taille. Vous évitez ainsi le risque de feuilles de calcul lourdes contenant plusieurs formules pour un seul calcul.
• Flexibilité : Un simple changement ou une modification de n'importe quelle cellule affectera automatiquement la valeur du calcul ARRAYFORMULA. Cela signifie que vous pouvez facilement modifier les valeurs des cellules et que toutes les valeurs reposant sur ces cellules seront automatiquement ajustées en conséquence.
• Dynamique : Lorsque vous ajoutez une nouvelle ligne dans votre plage de cellules, l'ARRAYFORMULA lui est automatiquement appliquée, de sorte que vous n'avez pas à saisir manuellement une autre fonction.

Bien que Google Sheets puisse proposer un “remplissage automatique suggéré” pour appliquer la fonction au reste de vos lignes, le remplissage automatique ne pourra pas offrir les mêmes avantages que ceux décrits ci-dessus. Par exemple, si vous modifiez vos valeurs, le remplissage automatique risque de ne pas pouvoir mettre à jour correctement toutes les données de vos cellules. Vous vous retrouveriez alors avec des informations périmées et inexactes.

Maintenant que vous comprenez les nombreux avantages de l'utilisation de l'ARRAYFORMULA dans les calculs de masse, voyons comment utiliser l'ARRAYFORMULA de Google Sheets dans les exemples suivants.

Comment utiliser l'ARRAYFORMULA de Google Sheets ?

Supposons que je sois enseignant et que je dispose d'un ensemble de données contenant une liste d'étudiants et leurs notes sur différents projets. Je souhaite calculer la note moyenne de chaque étudiant pour l'ensemble des projets.

Habituellement, j'additionne les valeurs des cellules individuellement, comme indiqué ci-dessous :

Cependant, cette méthode va rapidement poser problème dans deux circonstances récurrentes :

• Augmentation des données et des formules : Si je décide d'augmenter la taille des données ou d'utiliser davantage de formules, ma feuille de calcul ne fonctionnera pas aussi facilement.
• Mises à jour régulières : Toute modification de mes données implique la modification de toutes les formules utilisées.

En utilisant ARRAYFORMULA, je peux rapidement additionner les résultats de tous les élèves, quelle que soit la taille de l'ensemble de données ou le nombre de formules utilisées. Je peux ajouter, supprimer ou modifier le contenu à ma guise, et la formule s'appliquera automatiquement aux données.

Exemple 1 : ARRAYFORMULA de base

Dans le prolongement de l'exemple précédent, pour calculer les totaux, vous pouvez utiliser la FORMULE ARRAY comme indiqué ci-dessous. Remarquez que j'ajoute maintenant les plages, alors qu'auparavant j'ajoutais des cellules individuelles.

Cette nouvelle formule devrait ressembler à ceci :

Pour que l'ARRAYFORMULA fonctionne correctement, assurez-vous que toutes les cellules situées en dessous sont vides.

A noter : En terminant la plage de chaque colonne par la lettre (D2:D), vous pouvez ajouter la colonne entière en tant que tableau. Cela signifie que si vous ajoutez de nouvelles lignes de données dans les colonnes, la fonction ARRAYFORMULA sera automatiquement appliquée.

Comme vous pouvez le constater, l'utilisation de la formule de base est très simple.

Exemple 2 : IF avec ARRAYFORMULA

Vous avez probablement remarqué une séquence de zéros à la fin de la colonne après avoir appliqué la formule de l'exemple précédent. Vous pouvez éviter cela en ajoutant une fonction IF à la formule.

Cette instruction IF indique à la formule de laisser la colonne ‘Total’ vide si la colonne A ne contient aucun nom.

Il n'y a plus que des cellules vides, sans zéros, après la dernière ligne de données de la colonne A. Incluons une ligne pour un autre élève après la ligne 50 pour tester son caractère extensible.

Dès que vous commencez à saisir du contenu, la dernière colonne comprendra automatiquement un zéro. Il s'agit là d'un avantage important de l'utilisation d'ARRAYFORMULA. Lorsque vous travaillez avec un ensemble de données qui change souvent, ARRAYFORMULA vous permet d'apporter des modifications sans avoir à ajuster constamment la feuille de calcul.

Exemple 3 : SUMIF avec ARRAYFORMULA

Supposons que j'ai réparti les étudiants en trois groupes différents et que je souhaite connaître le total du groupe pour le ‘projet D’. Je peux utiliser ARRAYFORMULA en conjonction avec la fonction SUMIF pour obtenir le total de ces 3 groupes.

La syntaxe de SUMIF et ARRAYFORMULA est la suivante :

Comme vous pouvez le voir, j'ai maintenant le total des points par groupe pour le projet D, comme indiqué dans la colonne J. Je peux utiliser ce nouveau résultat pour calculer n'importe quelle autre mesure d'évaluation de manière rapide et efficace.

Comme précédemment, les zéros apparaissent dans l'ordre après la dernière cellule de contenu de la colonne ‘Total’. Voyons comment la combinaison de SUMIFS et d'ARRAYFORMULA peut permettre de résoudre rapidement ce problème, comme nous l'avons fait avec l'instruction IF.

Pour plus d'informations sur le fonctionnement de la fonction SUMIF, consultez l'article de blog suivant Comment utiliser la fonction SUMIF dans Excel.

Exemple 4 : VLOOKUP avec ARRAYFORMULA

Supposons que je veuille rechercher des données spécifiques sur les performances d'un élève donné. Normalement, je pourrais utiliser la fonction VLOOKUP pour ce faire. Cependant, je devrais saisir la fonction plusieurs fois pour chaque critère, car VLOOKUP ne renvoie qu'une seule valeur. En revanche, en combinant cette fonction avec ARRAYFORMULA, je peux trouver plusieurs valeurs en même temps.

La syntaxe de VLOOKUP avec ARRAYFORMULA est la suivante :

Dans cet exemple, je veux trouver le prénom, le nom et les résultats des projets A et B de l'étudiant numéro 10. Avec l'identifiant de l'étudiant comme clé, j'utiliserais la formule suivante :

Exemple 5 : FILTRE avec ARRAYFORMULA

L'une des utilisations les plus courantes de la combinaison de FILTER et de ARRAYFORMULA est l'identification des doublons. Pour que cela fonctionne, vous devez incorporer deux formules de base : COUNTIF et UNIQUE.

Supposons que je veuille trouver du contenu en double pour les noms de famille des étudiants afin de rechercher d'éventuels parents. Je devrais saisir la formule suivante :

Comme vous pouvez le constater, ‘Dimmer’ est apparu dans la cellule. Cela signifie que la formule a trouvé plus d'une cellule dans la colonne ‘Nom de famille’ contenant le nom Dimmer. Cela signifie que j'ai deux étudiants portant le même nom de famille.

Vous pouvez également utiliser cette fonction pour trouver et supprimer les données en double. Pour en savoir plus sur le fonctionnement individuel de ces fonctions, lisez ces articles de blog sur le site web de la UNIQUE, COUNTIF et FILTRE fonctions.

Exemple 6 : Tableaux horizontaux

Jusqu'à présent, les exemples ont tous été appliqués à des tableaux verticaux. L'ARRAYFORMULA peut tout aussi bien être utilisée pour les tableaux horizontaux, ce qui vous permet de l'utiliser quelle que soit la structure de vos données.

Calculons le ‘Total’ de tous les projets pour chaque élève, mais dans un format horizontal. Pour ce faire, j'introduis la formule suivante :

Au lieu de demander à la fonction de descendre dans la liste (par exemple, from A2:A, ce qui signifie commencer à A2 et descendre dans la colonne A), je lui ai demandé de parcourir les données (par exemple, from B5:5, ce qui signifie commencer à B5 et parcourir la ligne 5).

Grâce à cette simple modification, l'ARRAYFORMULA fonctionne désormais horizontalement. Et comme dans l'exemple précédent, je peux ajouter une instruction IF pour me débarrasser des zéros dans les colonnes vides :

Exemple 7 : Tableaux multidimensionnels

Vous avez maintenant vu que l'ARRAYFORMULA est utilisé à la fois dans les orientations verticale et horizontale. Mais qu'en est-il d'un tableau qui comprend ces deux types de tableaux ? Vous pouvez également utiliser l'ARRAYFORMULA pour ces tableaux !

Ici, j'ai créé une table de multiplication qui multiplie la colonne A par la ligne 1, en tapant simplement ce qui suit :

Exemple 8 : Utilisation de ARRAYFORMULA pour combiner des colonnes

L'ARRAYFORMULA permet non seulement de calculer des données numériques, mais aussi de combiner du texte provenant de différentes colonnes.

Supposons que je veuille combiner les colonnes ‘Prénom’ et ‘Nom’ pour créer une colonne ‘Nom complet’. J'ai inséré une colonne ‘Nom complet’ qui contient la formule suivante :

A noter : Cette formule ne fonctionnera que si les deux tableaux ont la même taille (par ex.‘C2:C52&” “&D2:D52’). Si ce n'est pas le cas, Google Sheets ne pourra pas effectuer le calcul.

Comment ajouter un nom de colonne même s'il contient un ARRAYFORMULA ?

Il est parfois nécessaire d'ajouter un nom de colonne au-dessus des données traitées par une fonction ARRAYFORMULA. Il est essentiel de nommer correctement les colonnes pour des raisons de clarté et de collaboration. Heureusement, vous pouvez le faire sans modifier la structure de données existante.

Par exemple, supposons que vous ayez deux listes de valeurs numériques dans les colonnes A et B, et que vous souhaitiez les additionner en incluant un nom de colonne. Pour ce faire, vous pouvez utiliser la formule suivante :

Il y a quelques points à garder à l'esprit lors de cette opération :

• Veillez à décaler les plages de cellules dans ARRAYFORMULA par rapport à la ligne inférieure. Sinon, il essaiera d'appliquer la formule aux en-têtes de colonne.
• Les parenthèses curieuses contiennent des tableaux, les éléments séparés par des points-virgules se développent verticalement. Dans cet exemple, si vous utilisez une virgule au lieu d'un point-virgule, la formule sera interrompue.

Comment créer un ARRAYFORMULA dans Google Sheets ?

Et voilà ! L'ARRAYFORMULA est un outil extrêmement puissant et polyvalent qui peut vous aider à rationaliser considérablement les données de votre feuille de calcul.

Utilisez-la pour renvoyer plusieurs valeurs à l'aide d'une seule fonction. Combinez-la également avec d'autres fonctions telles que IF, VLOOKUP et SUMIF afin de créer des formules avancées.

Vous souhaitez limiter les résultats d'un tableau à une taille spécifique ? Apprenez Comment utiliser la formule ARRAY_CONSTRAIN dans Google Sheets ?.