Comment utiliser ARRAYFORMULA dans Google Sheets : guide complet

ARRAYFORMULA fait fonctionner une formule sur toute une plage à la fois et déverse ses résultats dans plusieurs cellules, au lieu de recopier la formule ligne par ligne. Une seule formule dans la ligne d'en-tête peut remplir toute une colonne et continuer à la remplir au fur et à mesure que des lignes s'ajoutent.

Syntaxe

=ARRAYFORMULA(array_formula)

array_formula : Une formule qui utilise des plages, des tableaux ou des fonctions qui renvoient des tableaux. Là où vous écririez normalement A2, vous écrivez A2:A.

L'idée de base

Sans ARRAYFORMULA, une formule travaille sur des cellules isolées. Pour multiplier deux colonnes, vous écrivez =A2*B2 en ligne 2 et vous l'étirez vers le bas. Avec ARRAYFORMULA, vous l'écrivez une seule fois :

=ARRAYFORMULA(A2:A * B2:B)

Elle multiplie chaque paire de cellules et remplit les résultats vers le bas. Si une ligne est ajoutée aux données, le résultat s'agrandit avec elle. Placez-la dans la ligne d'en-tête de la colonne de résultat pour que la colonne s'entretienne toute seule.

Exemples

Calculer toute une colonne

=ARRAYFORMULA(B2:B * C2:C)

Combiner le texte de deux colonnes

=ARRAYFORMULA(A2:A & " " & B2:B)

Réunit prénom et nom pour chaque ligne.

Éviter de remplir les lignes vides

Une plage ouverte comme A2:A inclut les cellules vides du bas : la formule renverrait 0 ou des erreurs pour celles-ci. Protégez-la avec IF :

=ARRAYFORMULA(IF(A2:A = "", "", B2:B * C2:C))

C'est le schéma standard : si la cellule clé est vide, n'affichez rien ; sinon, calculez.

Ajouter un en-tête et la formule dans une seule cellule

=ARRAYFORMULA({"Total"; IF(A2:A = "", "", B2:B * C2:C)})

Les accolades construisent un tableau qui empile l'en-tête au-dessus des résultats. Le point-virgule empile verticalement ; la virgule place les éléments côte à côte.

Logique conditionnelle sur toute une colonne

=ARRAYFORMULA(IF(C2:C > 100, "Élevé", "Faible"))

Recherches pour chaque ligne

=ARRAYFORMULA(IFERROR(VLOOKUP(A2:A, Prix!A:B, 2, FALSE)))

Cette formule recherche chaque clé d'un seul coup. Des fonctions comme VLOOKUP, IF, SUMIF et REGEXEXTRACT acceptent des plages dans ARRAYFORMULA et traitent chaque valeur.

Nettoyage de texte sur une colonne

=ARRAYFORMULA(TRIM(PROPER(A2:A)))

Totaux cumulés

=ARRAYFORMULA(SUMIF(ROW(B2:B), "<=" & ROW(B2:B), B2:B))

Pour chaque ligne, cette formule additionne toutes les valeurs depuis le haut jusqu'à cette ligne.

Quelles fonctions ont besoin d'ARRAYFORMULA ?

De nombreuses fonctions renvoient déjà des tableaux et n'en ont pas besoin : FILTER, SORT, UNIQUE, SEQUENCE, TRANSPOSE, QUERY, SPLIT, REGEXEXTRACT sur une seule cellule, et XLOOKUP sur une plage. Les fonctions qui renvoient normalement une seule valeur par appel ont besoin de l'enveloppe pour traiter une plage : IF, VLOOKUP, LEN, TRIM, LEFT, les opérateurs arithmétiques et &.

Quelques fonctions ne se déploient jamais sur des tableaux dans ARRAYFORMULA, comme SUM, AVERAGE et MAX, car elles réduisent une plage à un seul nombre par conception. Pour des sommes ligne par ligne, utilisez MMULT ou BYROW.

Erreurs courantes

#REF! : le résultat de la matrice n'a pas été développé car il écraserait des données. Quelque chose se trouve dans les cellules où le résultat doit se déverser. Videz-les ou déplacez la formule.
#VALUE! : tailles de matrices différentes. Les plages combinées dans la formule n'ont pas la même hauteur. A2:A * B2:B50 mélange une plage ouverte et une plage bornée.
Résultats dans les lignes vides (0 ou #N/A). Ajoutez la protection IF(A2:A = "", "", ...).
La feuille devient lente. Les plages ouvertes (A2:A) se recalculent sur des milliers de lignes vides. Bornez la plage (A2:A5000) dans les très grandes feuilles.
La formule disparaît quand on l'étire. N'étirez pas une ARRAYFORMULA. Une seule formule couvre déjà toute la colonne. L'étirer place d'autres formules sur le chemin du déversement.

ARRAYFORMULA ou formule étirée vers le bas ?

Étirer est simple et permet de modifier des lignes individuellement, mais les nouvelles lignes ajoutées en bas n'ont pas la formule et il est facile de laisser des trous. ARRAYFORMULA garantit la cohérence et gère les nouvelles lignes, mais une seule formule contrôle toute la colonne : on ne peut pas remplacer une cellule sans casser le déversement.

Le raccourci : appuyer sur Ctrl+Maj+Entrée (ou Cmd+Maj+Entrée sur Mac) pendant la saisie d'une formule l'entoure automatiquement de ARRAYFORMULA.

Questions fréquentes

ARRAYFORMULA ralentit-elle ma feuille ? Seulement quand elle couvre de très grandes plages. Bornez-les si les performances en souffrent.

Peut-on l'utiliser avec QUERY ? QUERY gère déjà les tableaux. N'utilisez ARRAYFORMULA autour d'elle que si vous lui fournissez des tableaux calculés.

Pourquoi ma formule fonctionne-t-elle en ligne 2 mais pas avec ARRAYFORMULA ? Une fonction qui attend une seule valeur (comme SUM) ne se déploie pas. Cherchez des alternatives ligne par ligne comme BYROW ou MMULT.

Fonctions associées

FILTER : Renvoie les lignes qui remplissent une condition.
QUERY : Filtrer et agréger une plage avec une syntaxe de type SQL.
SEQUENCE : Génère un tableau de nombres.
IFERROR : Remplace les erreurs par une valeur personnalisée.
VLOOKUP : Recherche verticale, souvent utilisée dans ARRAYFORMULA.

ARRAYFORMULA