Comment utiliser la fonction ARRAYFORMULA dans Google Sheets

Dans Google Sheets, les fonctions non matricielles telles que IF, SUM, SUMIF, MAX, AVERAGE, VLOOKUP, etc., ne renvoient qu’une seule valeur en résultat. Cependant, si vous souhaitez obtenir des résultats sous forme de matrices à partir de ces fonctions, Google Sheets propose une fonction dédiée appelée ARRAYFORMULA.

La syntaxe de la fonction ARRAYFORMULA est la suivante: =ARRAYFORMULA(formule_matricielle). L’argument peut être un intervalle de cellules, une fonction renvoyant plus d’une valeur ou une expression mathématique de la même taille.

Dans cet article, nous allons passer en revue tous les exemples d’utilisation de la fonction ARRAYFORMULA.

ARRAYFORMULA avec SORT

Lors de calculs, il peut arriver que vous ayez besoin de trier les nombres par ordre croissant ou décroissant. Au lieu de trier manuellement les nombres et d’utiliser des formules de calcul, vous pouvez effectuer toutes ces opérations en une seule fois.

Exemple:
Je souhaite calculer un tableau de bénéfices et les trier par ordre croissant. Pour cela, ma formule sera la suivante:

=SORT(ARRAYFORMULA(1-B3:B10/A3:A10), 1, TRUE)

Dans cette formule, nous calculons d’abord le bénéfice. ARRAYFORMULA renvoie le tableau de résultats, puis la fonction SORT le réordonne.

ARRAYFORMULA et IF

Avec la fonction IF de Google Sheets, vous pouvez renvoyer un élément spécifique lorsque la condition est remplie et lorsque ce n’est pas le cas.

Utilisons ARRAYFORMULA avec IF pour obtenir plusieurs éléments en fonction de la condition.

Exemple:
Supposons que nous devons vérifier si la quantité d’un produit est supérieure à 15 ou non. Si nous utilisons la formule =IF(C6:C13 > 15, "Oui", "Non"), nous obtenons une seule valeur et devons la copier pour le reste des colonnes.

Mais si nous convertissons simplement la formule en tableau, elle renverra immédiatement les résultats pour toutes les entrées. C’est tellement plus rapide et plus pratique pour effectuer des calculs.

Pour cela, voici ma formule :

=ARRAYFORMULA(IF(C6:C13 > 15, "Oui", "Non"))

ARRAYFORMULA avec SUMIF

La fonction SUMIF est utilisée pour ajouter une plage de cellules selon certains critères. Mais nous pouvons calculer la somme conditionnelle pour plusieurs valeurs en une seule fois.

Cette méthode est particulièrement utile lorsque vous avez les mêmes critères et la même plage de cellules.

Exemple:
Supposons que nous ayons une colonne pour les produits, les prix et les quantités. Nous voulons additionner les quantités pour tous ces produits en une seule fois.

Pour cela, nous utilisons la formule suivante :

=ARRAYFORMULA(SUMIF(A6:A, G6:G8, C6:C13))

Dans SUMIF, notre critère est d’ajouter les plages de cellules identiques à la colonne des produits.

ARRAYFORMULA avec VLOOKUP

Nous savons tous que la fonction VLOOKUP elle-même ne peut renvoyer qu’un seul élément de la table de recherche.

Mais si vous utilisez ARRAYFORMULA imbriqué, vous pouvez renvoyer plusieurs éléments, tout comme avec la fonction XLOOKUP.

Exemple:
En utilisant la table de recherche VLOOKUP, renvoyons l’identifiant du produit, le stock et le prix pour notre valeur recherchée. J’ai entré cette formule :

=ARRAYFORMULA(VLOOKUP($G$4, $A$4:$E$8, {2, 4, 5}, FALSE))

Comme vous pouvez le voir dans la formule VLOOKUP, j’ai spécifié que les valeurs doivent être renvoyées à partir de 3 colonnes, qui sont incluses entre les crochets {2, 4, 5}. Ensuite, la fonction ARRAYFORMULA renvoie ces éléments.

ARRAYFORMULA avec SUM et IF

Contrairement à Excel, Google Sheets ne calcule pas les formules avec SUM et IF imbriqués.

Par exemple, si vous entrez la formule =SUM(IF(A3:B10 < 15, A3:B10,0)), vous obtiendrez une erreur #VALUE!.

Mais si vous placez ARRAYFORMULA au début, les résultats seront renvoyés.

=ARRAYFORMULA(SUM(IF(A3:B10 < 15, A3:B10,0)))

La formule renvoie 68 comme résultat.

ARRAYFORMULA avec UNIQUE, FILTER, COUNTIF

Supposons que nous voulions renvoyer uniquement les valeurs uniques des colonnes contenant des doublons.

Pour cela, nous pouvons utiliser ARRAYFORMULA avec UNIQUE, FILTER et COUNTIF pour extraire facilement les valeurs uniques requises.

=FILTER(UNIQUE(C6:C13), ARRAYFORMULA(COUNTIF(C6:C13, UNIQUE(C6:C13))>1))

La formule renvoie 3 valeurs uniques de la colonne I.

Comment utiliser ARRAYFORMULA pour extraire des données d’une autre feuille ?

ARRAYFORMULA ne se limite pas à renvoyer un tableau de résultats. Vous pouvez également l’utiliser pour extraire des données d’une autre feuille.

Exemple:
Supposons que je souhaite extraire la plage B3:B10 de la Feuille 1. Pour cela, ma formule serait :

=ARRAYFORMULA(Feuille1!B3:B10)

Ce que vous devez savoir lorsque vous utilisez ARRAYFORMULA

• Il existe un raccourci clavier pour activer ARRAYFORMULA. Vous n’avez donc pas à entrer =ARRAYFORMULA() à chaque fois. Après avoir saisi la formule, appuyez sur les touches Ctrl + Shift + Entrée au lieu de simplement Entrée.
• Une accolade { au début de la formule signifie également que la formule est une ARRAYFORMULA.
• Google Sheets ne vous permet pas d’exporter ARRAYFORMULA.
• Pour éviter le comportement ‘#SPILL!’ lors de l’utilisation de cette fonction, assurez-vous qu’il y a suffisamment d’espace pour renvoyer le tableau de résultats dans votre feuille de calcul.
• Utilisez la fonction ARRAY_CONSTRAIN avec ARRAYFORMULA pour contrôler le calcul inutile des résultats de tableau.

Quand ne pas utiliser ARRAYFORMULA ?

Certaines fonctions de Google Sheets telles que SUMIFS, IFS et AND ne fonctionnent pas avec ARRAYFORMULA.

Donc, si vous essayez d’utiliser ARRAYFORMULA avec ces fonctions, cela entraînera une erreur ou aucun changement.