ARRAYFORMULA, MAP et MAKEARRAY dans Google Sheets : une seule formule pour toute la colonne (avec LAMBDA)

Le problème : une formule recopiée sur 3 000 lignes

Dans la plupart des fichiers que je vois en formation, chaque colonne calculée contient la même formule recopiée jusqu’en bas. Cela pose trois problèmes : les lignes ajoutées par un formulaire ou un collègue n’ont pas la formule, il suffit d’une saisie par-dessus pour fausser un total sans que personne ne le voie, et le fichier s’alourdit. La solution : une formule unique en tête de colonne, qui « déborde » sur toutes les lignes.

Ces fonctions gardent leur nom anglais dans un fichier réglé sur la France (ARRAYFORMULA, MAP, MAKEARRAY, LAMBDA, BYROW), mais les fonctions qu’elles contiennent restent en français (SI, SOMME…) et les arguments se séparent par un point-virgule. Les exemples utilisent ce tableau de commandes fictif :

	A : Client	B : Région	C : Quantité	D : Prix unitaire (€)	E : Statut	F : Montant HT (€)
2	Dupont	Nord	10	120	Payé	1 200
3	Martin	Sud	3	150	En attente	450
4	Leroy	Nord	20	190	En attente	3 800
5	Petit	Est	2	45	Payé	90

ARRAYFORMULA : une formule pour toute la colonne

ARRAYFORMULA(formule_matricielle) permet d’utiliser des plages entières là où une formule attend normalement une seule cellule. La colonne F ci-dessus est calculée par une seule formule en F2 :

=ARRAYFORMULA(SI(A2:A=""; ""; C2:C*D2:D))

• C2:C*D2:D multiplie les deux colonnes ligne à ligne.
• SI(A2:A=""; ""; …) laisse vides les lignes sans client. Sans ce test, la formule afficherait des 0 jusqu’en bas de la feuille.
• Raccourci : Ctrl + Maj + Entrée pendant la saisie ajoute ARRAYFORMULA( automatiquement.

Astuce : placez la formule dans la ligne d’en-tête, avec le titre, pour que le tri ou l’insertion de lignes ne puisse jamais la déplacer :

={"Montant HT"; ARRAYFORMULA(SI(A2:A=""; ""; C2:C*D2:D))}

Dans un fichier réglé sur la France, le point-virgule entre les accolades empile les éléments verticalement : le titre en F1, les montants en dessous. La même logique fonctionne avec une TVA à 20 % (F2:F*1,2 : la virgule est le séparateur décimal) ou une recherche dans une autre table (voir le guide RECHERCHEV et RECHERCHEX).

Ce qui ne marche pas dans ARRAYFORMULA

Les fonctions qui agrègent une plage (SOMME, MOYENNE, MAX, NBVAL…) renvoient un seul résultat pour toute la plage, pas un par ligne. C’est aussi le cas de ET et OU : ARRAYFORMULA(SI(ET(B2:B="Nord"; F2:F>=1000); …)) ne teste pas chaque ligne. Deux solutions : remplacer ET par une multiplication et OU par une addition, comme expliqué dans l’article sur la formule SI à plusieurs conditions, ou passer à MAP.

LAMBDA en deux minutes

LAMBDA(nom; expression) définit une petite fonction : nom est une étiquette que vous choisissez, expression le calcul qui l’utilise. Seule, elle sert peu ; son intérêt est d’être passée à MAP, BYROW, BYCOL ou MAKEARRAY, qui l’appliquent à chaque élément. Règle à connaître : le nom ne doit pas ressembler à une référence de cellule. montant ou ligne fonctionnent, x1 ou TVA1 non (ce sont des adresses de cellules).

MAP : appliquer une formule à chaque cellule

MAP(tableau1; [tableau2; …]; LAMBDA(nom1; [nom2; …]; expression))

MAP parcourt les plages en parallèle et donne à la LAMBDA une valeur de chaque plage à la fois. Il faut autant de noms dans la LAMBDA que de plages, et les plages doivent avoir la même taille. Comme le calcul se fait cellule par cellule, ET et OU fonctionnent normalement :

=MAP(B2:B; F2:F; LAMBDA(region; montant; SI(montant=""; ""; SI(ET(region="Nord"; montant>=1000); "Prioritaire"; ""))))

Résultat : « Prioritaire » pour Dupont et Leroy, rien pour les autres. Autre usage typique : un calcul par élément d’une liste. Avec la liste des clients en H2:H (par exemple =UNIQUE(A2:A)), le chiffre d’affaires de chacun :

=MAP(H2:H; LAMBDA(client; SI(client=""; ""; SOMME.SI.ENS(F2:F; A2:A; client))))

Chaque appel doit renvoyer une seule valeur. Une LAMBDA qui renvoie plusieurs cellules (un SPLIT, par exemple) provoque une erreur.

BYROW et BYCOL : une valeur par ligne ou par colonne

Pour totaliser chaque ligne, ce que ARRAYFORMULA(SOMME(…)) ne sait pas faire, BYROW passe une ligne entière à la LAMBDA. Avec des quantités mensuelles en G2:I :

=BYROW(G2:I; LAMBDA(ligne; SI(NBVAL(ligne)=0; ""; SOMME(ligne))))

BYCOL fait la même chose colonne par colonne, par exemple pour un total ou un maximum par mois.

MAKEARRAY : construire un tableau case par case

MAKEARRAY(lignes; colonnes; LAMBDA(ligne; colonne; expression))

MAKEARRAY crée un tableau de la taille demandée et calcule chaque case à partir de son numéro de ligne et de colonne (qui commencent à 1). Premier exemple, un calendrier de 5 semaines démarrant le lundi 2 novembre 2026 (cellules au format date) :

=MAKEARRAY(5; 7; LAMBDA(semaine; jour; DATE(2026; 11; 2) + (semaine - 1) * 7 + jour - 1))

Second exemple, plus utile au quotidien : un tableau de synthèse dont vous fixez les lignes et les colonnes. Avec les régions en H2:H4 (Nord, Sud, Est) et les statuts en I1:J1 (Payé, En attente), la formule en I2 remplit toute la grille :

=MAKEARRAY(LIGNES(H2:H4); COLONNES(I1:J1); LAMBDA(ligne; colonne; SOMME.SI.ENS(F2:F; B2:B; INDEX(H2:H4; ligne; 1); E2:E; INDEX(I1:J1; 1; colonne))))

	Payé	En attente
Nord	1 200	3 800
Sud	0	450
Est	90	0

Un tableau croisé dynamique ou un pivot de la fonction QUERY donne un résultat proche, mais la présentation suit les données. Avec MAKEARRAY, la grille reste identique d’un mois à l’autre, zéros compris : pratique pour un tableau de bord où chaque case est reprise ailleurs.

Laquelle choisir ?

Les pièges les plus fréquents

• #REF! : le résultat ne peut pas s’étendre. Une cellule de la zone de débordement contient déjà quelque chose, souvent une ancienne formule recopiée ou une saisie manuelle. Videz la colonne sous la formule.
• Plages de tailles différentes : MAP(B2:B; F3:F; …) renvoie une erreur. Commencez toutes les plages sur la même ligne.
• Nombre de noms incorrect : deux plages dans MAP, deux noms dans la LAMBDA ; MAKEARRAY en attend toujours deux (ligne et colonne), BYROW un seul.
• Lignes vides : sans test SI(…=""; ""; …), les formules remplissent la feuille de 0 ou d’erreurs jusqu’en bas.
• Données saisies dans la colonne calculée : une colonne produite par ARRAYFORMULA ne se modifie pas à la main. Protégez-la (Données > Protéger les feuilles et les plages) si plusieurs personnes travaillent dans le fichier.
• Export vers Excel : ARRAYFORMULA n’existe pas dans Excel, qui gère les plages autrement. LAMBDA, MAP, BYROW et MAKEARRAY existent dans Microsoft 365, mais vérifiez toujours un fichier exporté.

Et les performances ?

Une formule unique est en général plus légère que des milliers de formules recopiées. Deux réserves toutefois. D’abord, une plage ouverte comme A2:A inclut toutes les lignes de la feuille, vides comprises : supprimez les milliers de lignes vides inutiles en bas de l’onglet. Ensuite, MAP et MAKEARRAY recalculent leur LAMBDA pour chaque case : si cette LAMBDA parcourt elle-même une colonne entière (un SOMME.SI.ENS sur F2:F, par exemple), le nombre de calculs est multiplié d’autant. Sur de gros volumes, Sheets peut alors devenir lent, voire refuser le calcul.

Mes règles pratiques : ARRAYFORMULA pour tout ce qui peut s’écrire en opérations colonne par colonne, MAP seulement pour la logique qui ne le peut pas, des plages bornées quand le volume dépasse quelques milliers de lignes, et pas de fonctions recalculées en permanence (MAINTENANT, AUJOURDHUI, ALEA) à l’intérieur d’une LAMBDA. Au-delà de quelques dizaines de milliers de lignes, le problème n’est souvent plus la formule : c’est le moment de déplacer une partie du calcul dans un script, avec la formation Apps Script ou en me confiant l’automatisation.

FAQ

ARRAYFORMULA ou formule recopiée vers le bas : que choisir ?

ARRAYFORMULA dès que le tableau grandit (formulaire, import, saisie par plusieurs personnes) : une seule formule à maintenir, et aucune ligne oubliée. La formule recopiée reste acceptable pour un petit tableau figé.

Quelle différence entre MAP et ARRAYFORMULA ?

ARRAYFORMULA applique des opérations à des plages entières ; MAP appelle une LAMBDA pour chaque cellule. MAP est plus souple (ET, OU et fonctions d’agrégation fonctionnent), ARRAYFORMULA plus simple à lire et généralement plus rapide.

Pourquoi mon ARRAYFORMULA renvoie-t-elle #REF! ?

Parce qu’une cellule de la zone où le résultat doit s’afficher n’est pas vide. Survolez l’erreur : Sheets indique la cellule qui bloque. Videz-la, ainsi que les autres cellules de la colonne.

Peut-on réutiliser une LAMBDA dans plusieurs formules ?

Oui, avec les fonctions nommées (Données > Fonctions nommées) : vous enregistrez le calcul sous un nom, puis vous le passez directement à MAP ou BYROW, sans parenthèses.

ARRAYFORMULA, LET et LAMBDA sont au programme du niveau N3 « Expert » de la formation Google Sheets, consacré aux tableaux de bord et aux modèles robustes, travaillés sur les fichiers de votre équipe. Pour la suite, lisez aussi le guide de la fonction QUERY.