Comment utiliser ARRAYFORMULA dans Google Sheets (exemples de formules matricielles)

Comme son nom l'indique, ARRAYFORMULA transforme une formule ordinaire en formule matricielle dans Google Sheets. Avec elle, vous pouvez 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.

Ce guide explique comment fonctionne ARRAYFORMULA, pourquoi l'utiliser, neuf exemples de formules matricielles (dont IF, SUMIF, VLOOKUP et FILTER) et les erreurs que vous risquez le plus de rencontrer.

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, des zéros apparaissent après la dernière cellule remplie de la colonne 'Total'. Pour les masquer, encadrez SUMIF du même test IF que dans l'exemple 2 :

=ARRAYFORMULA(IF(I2:I<>"", SUMIF(B2:B, I2:I, H2:H), ""))

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

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 :

Pour rechercher plusieurs clés à la fois, donnez à VLOOKUP (RECHERCHEV) une plage de clés de recherche au lieu d'une seule cellule. Avec les identifiants d'élèves voulus en K2:K4, cette formule renvoie une ligne de résultats par identifiant :

=ARRAYFORMULA(VLOOKUP(K2:K4, $A$1:$I$52, {1,3,4,5,6}, FALSE))

Pour plus d'informations sur le fonctionnement de la fonction VLOOKUP, consultez cet article sur Fonctionnement de la fonction VLOOKUP dans Google Sheets.

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 créer une formule matricielle étape par étape

Les exemples précédents montrent des formules terminées. Celui-ci construit une formule matricielle à partir de la première cellule et ajoute ARRAYFORMULA à la fin avec le raccourci clavier. Le tableau liste les commerciaux et le montant des ventes de chacun sur le mois. Si le montant est supérieur ou égal à 4000 $, le commercial reçoit une prime de 5 %. Je veux calculer et renvoyer le montant de la prime pour les ventes supérieures ou égales à 4000 $, et laisser la cellule vide si le montant est inférieur à 4000 $.

1. Ouvrez dans Google Sheets la feuille de calcul qui contient vos données.

2. Dans la première ligne de la colonne où vous voulez les résultats, saisissez la Fonction IF.

3. Ajoutez la condition : le montant de la colonne B doit être d'au moins 4000 $ (B2:B6>=4000).

4. Après la virgule, ajoutez le calcul à renvoyer si la condition est TRUE.

5. Ajoutez une autre virgule, puis la valeur à renvoyer si la condition est FALSE.

6. Fermez la parenthèse et utilisez le raccourci ARRAYFORMULA pour ajouter la fonction : appuyez sur Ctrl + Maj + Entrée sous Windows ou sur Cmd + Maj + Entrée sur Mac.

7. Appuyez sur Entrée pour afficher la matrice obtenue.

La formule finale est =ARRAYFORMULA(IF(B2:B6>=4000,B2:B6*0.05,"")). Bob, Janet et Sarah reçoivent leur prime de 5 %, et les cellules de Jane et Tim restent vides.

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.

Erreurs courantes avec ARRAYFORMULA et comment les corriger

Quand une formule matricielle ne fonctionne plus, la cause est généralement l'une des suivantes.

• #REF! Le résultat de la matrice n'a pas été développé, car il écraserait des données : une cellule de la plage de sortie contient déjà une valeur. Effacez les cellules situées sous la formule ou à côté, et elle se développe de nouveau.
• Les arguments matriciels de ADD sont de tailles différentes : les plages de la formule couvrent des lignes différentes, comme A2:A10 + B2:B11. Faites commencer et finir toutes les plages sur les mêmes lignes.
• Un seul résultat au lieu d'une colonne : les fonctions qui résument une plage, comme SUM, AND et OR, renvoient une seule valeur, même dans ARRAYFORMULA. Pour un test ligne par ligne, utilisez * à la place de AND et + à la place de OR : =ARRAYFORMULA(IF((B2:B>=4000)*(C2:C="Yes"), "Bonus", ""))
• #N/A dans certaines lignes : une recherche sans correspondance renvoie #N/A dans cette ligne. Encadrez la recherche avec IFERROR pour afficher une cellule vide : =ARRAYFORMULA(IFERROR(VLOOKUP(K2:K4, $A$1:$I$52, {1,3,4,5,6}, FALSE), ""))
• Une feuille de calcul lente : les plages ouvertes comme D2:D couvrent toutes les lignes de la feuille. Sur les fichiers volumineux, arrêtez la plage à la dernière ligne utile ou ignorez les lignes vides avec IF, comme dans l'exemple 2.

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 ?.