Qu'est-ce que la fonction ARRAYFORMULA ?
ARRAYFORMULA te sert dès que tu en as marre de recopier la même formule sur des centaines de lignes à chaque nouvelle donnée, ou que tu veux qu'un tableau partagé se mette à jour tout seul sans que personne y retouche. C'est le réflexe à prendre avant qu'un fichier collaboratif ne devienne ingérable à force de copier-coller.
Mieux : quand de nouvelles lignes arrivent (une commande, une vente, un lead), le calcul se fait tout seul, sans que tu aies à étirer quoi que ce soit. C'est ce qui transforme un simple tableur en système qui tourne sans entretien.
Syntaxe
=ARRAYFORMULA(formule_tableau)Clique sur un argument pour aller à son explication.
La formule passée à ARRAYFORMULA doit référencer des plages (par ex. A2:A) et non une cellule unique : c'est ce qui déclenche la propagation sur toute la colonne.
Comprendre chaque paramètre de la fonction ARRAYFORMULA
formule_tableau
: la formule que tu veux appliquer à une plage entièreAu lieu de référencer une cellule unique comme A2, tu références une plage comme A2:A1000, et ARRAYFORMULA exécute ta formule sur chaque ligne automatiquement.
Elle accepte la plupart des types de formules : les opérations mathématiques (A2:A * B2:B, A2:A + 100), la concaténation (A2:A & " " & B2:B), les fonctions conditionnelles (SI(A2:A > 100; "Élevé"; "Normal")), les fonctions texte (MAJUSCULE(A2:A), GAUCHE(A2:A; 5)) et les fonctions de date (MOIS(A2:A), ANNEE(B2:B)).
Les fonctions d'agrégation comme SOMME ou MOYENNE font exception : enveloppées dans ARRAYFORMULA, elles renvoient un total global et non un résultat par ligne.
Attention : ARRAYFORMULA remplit toutes les cellules sous ta formule. Si ces cellules contiennent déjà des données (titres, totaux, notes), elles sont écrasées sans avertissement. Place toujours la formule dans une colonne vide.
Exemples pratiques pas à pas
Comment appliquer un calcul à toute une colonne avec la fonction ARRAYFORMULA
La réunion de fin de mois approche et chaque vendeur veut connaître sa commission avant d'y entrer. Tu as les ventes de toute l'équipe dans une colonne et tu veux la commission de 8 % en face de chacune, sans saisir cinq fois le même calcul. La fonction ARRAYFORMULA de Google Sheets traite la colonne entière depuis une seule cellule.
- 1Dans une cellule, écris
=ARRAYFORMULA(. - 2En 1er argument : écris le calcul à propager,
B2:B6 * 0,08. C'est le fait de citer une plage, et non la seule celluleB2, qui déclenche le calcul sur chaque ligne. Le taux, lui, reste un nombre isolé face à cette plage, et Google Sheets le multiplie alors par chacune des cinq ventes. - 3Ferme la parenthèse et appuie sur Entrée. La colonne Commission se remplit depuis cette seule cellule, sans que tu aies à étirer la formule vers le bas.
=ARRAYFORMULA(B2:B6 * 0,08)C2 remplit toute la colonne Commission. Les cellules C3 à C6 restent vides à la saisie (tu ne peux rien y écrire) parce que c'est la formule du haut qui les alimente.
Comment ignorer les lignes vides avec les fonctions ARRAYFORMULA et SI
Le formulaire de commandes se remplit tout au long de la journée et le total de chaque ligne doit suivre sans que personne y pense. Tu as la quantité et le prix unitaire, et tu veux le total en face, y compris pour les commandes qui n'existent pas encore. Le piège, c'est que la colonne se couvrirait de 0 sur les milliers de lignes encore vides du bas : la fonction SI, imbriquée dans ARRAYFORMULA, les laisse tranquilles.
- 1Dans une cellule, écris
=ARRAYFORMULA(. - 2En 1er argument : écris le calcul à propager et referme-le,
SI(B2:B=""; ""; B2:B * C2:C). Les plages s'écrivent sans numéro de fin,B2:Bet nonB2:B6, pour que la colonne soit couverte jusqu'en bas et que les commandes encore à venir entrent d'elles-mêmes dans le calcul. Le test placé en tête regarde ligne par ligne si la quantité manque, et les deux guillemets sans rien entre eux sont ce qui s'affiche dans ce cas, à la place du0qui couvrirait sinon tout le bas de la feuille. - 3Ferme la parenthèse de la fonction ARRAYFORMULA et appuie sur Entrée. Celle de la fonction SI a déjà été refermée à l'étape précédente.
=ARRAYFORMULA(SI(B2:B=""; ""; B2:B * C2:C))B2:B n'a pas de fin, la commande #1045 sera calculée dès sa saisie sans que tu retouches D2.
Comment remplir une colonne entière avec les fonctions ARRAYFORMULA et RECHERCHEV
Les commandes arrivent avec un nom de produit, mais les prix vivent dans une feuille Tarifs qu'une autre équipe met à jour. Tu veux le tarif en face de chaque commande, et il te le faut sur la colonne entière plutôt que ligne par ligne. La fonction ARRAYFORMULA fait tourner la recherche sur toute la liste en une seule saisie.
- 1Dans une cellule, écris
=ARRAYFORMULA(. - 2En 1er argument : écris la recherche à propager et referme-la,
RECHERCHEV(B2:B6; Tarifs!A:B; 2; FAUX). Tout se joue sur sa première valeur : là où la fonction RECHERCHEV attend un seul produit, tu lui donnes la plage des cinq produits commandés,B2:B6. Ses trois autres valeurs ne changent pas d'une ligne à l'autre, puisque c'est le même catalogue qu'on interroge à chaque fois, dans la même colonne de tarifs et avec la même exigence de correspondance exacte. - 3Ferme la parenthèse de la fonction ARRAYFORMULA et appuie sur Entrée. Celle de la fonction RECHERCHEV a déjà été refermée à l'étape précédente.
=ARRAYFORMULA(RECHERCHEV(B2:B6; Tarifs!A:B; 2; FAUX))
Comment gérer les erreurs #N/A avec les fonctions ARRAYFORMULA et SIERREUR
Un client t'appelle pour valider sa commande et la colonne des tarifs affiche #N/A en plein milieu. La feuille Tarifs a pris du retard, deux produits récents n'y figurent pas encore, et la recherche renvoie une erreur pour eux. La fonction SIERREUR remplace ces erreurs par un message clair, ligne par ligne, sans abîmer le reste de la colonne.
- 1Dans une cellule, écris
=ARRAYFORMULA(. - 2En 1er argument : écris la recherche de l'exemple précédent enveloppée dans une fonction SIERREUR et referme le tout,
SIERREUR(RECHERCHEV(B2:B6; Tarifs!A:B; 2; FAUX); "Tarif à créer"). La recherche elle-même n'a pas bougé d'un caractère, seul le traitement de son résultat change. Le texte de secours,"Tarif à créer", prend la place du#N/Aque renvoie un produit absent de la feuille Tarifs, et il dit à celui qui lit le tableau ce qu'il reste à faire. - 3Ferme la parenthèse de la fonction ARRAYFORMULA et appuie sur Entrée. Celle de la fonction SIERREUR a déjà été refermée à l'étape précédente.
=ARRAYFORMULA(SIERREUR(RECHERCHEV(B2:B6; Tarifs!A:B; 2; FAUX); "Tarif à créer"))#N/A au milieu de la colonne. Le filet se pose ligne par ligne (et non sur la colonne entière) : les tarifs connus s'affichent normalement, et les deux produits inconnus signalent eux-mêmes le travail à faire.
Les erreurs fréquentes avec la fonction ARRAYFORMULA
Presque tous les ratés d'ARRAYFORMULA tournent autour d'une seule chose : la façon dont elle propage le calcul ligne par ligne. Dès que tes plages n'ont pas la même longueur, tu récoltes des #N/A sur les lignes orphelines ; et comme elle remplit tout ce qui est en dessous, elle peut écraser tes titres ou tes totaux sans rien te demander.
L'autre faux pas classique, c'est de glisser un SOMME ou une MOYENNE dedans en espérant un résultat par ligne : ces fonctions regroupent les données et te renvoient un total unique.
Tailles de plages incompatibles
Si tu combines plusieurs plages de tailles différentes (par exemple A2:A10 * B2:B20), ARRAYFORMULA renvoie #N/A pour les cellules sans correspondance. C'est l'erreur la plus courante avec cette fonction.
Solution : Solution : assure-toi que toutes les plages référencées ont exactement la même taille. Le plus simple est d'utiliser des plages ouvertes (A2:A) qui s'ajustent seules, ou de fixer la même fin partout (A2:A100 * B2:B100).
La formule écrase les cellules en dessous
ARRAYFORMULA remplit automatiquement toutes les cellules sous la formule. Si elles contiennent déjà des données (titres, totaux, notes, commentaires), elles sont écrasées sans avertissement.
Solution : Solution : place toujours ta formule dans une colonne vide, ou borne la propagation avec LIGNE() : =ARRAYFORMULA(SI(LIGNE(A2:A)<=100; A2:A*B2:B; "")) limite le calcul aux 100 premières lignes.
Résultats parasites sur les lignes vides
Si certaines lignes de la plage source sont vides, ARRAYFORMULA peut renvoyer 0, des cellules vides ou des erreurs pour ces lignes, ce qui rend le tableau incohérent et difficile à lire.
Solution : Solution : encadre la formule d'un test sur la cellule source : =ARRAYFORMULA(SI(A2:A=""; ""; ta_formule)). Les lignes vides restent ainsi vides, sans 0 ni erreur.
Une fonction d'agrégation ne se calcule pas ligne par ligne
Les fonctions comme SOMME, MOYENNE, MAX ou MIN ne fonctionnent pas par ligne dans ARRAYFORMULA. =ARRAYFORMULA(SOMME(A2:A)) renvoie un seul total global, pas une somme par ligne.
Solution : Solution : pour additionner plusieurs colonnes ligne par ligne, écris l'opération directement : =ARRAYFORMULA(A2:A + B2:B + C2:C). Pour des calculs plus complexes, oriente-toi vers SOMMEPROD ou QUERY.
ARRAYFORMULA vs formules manuelles vs Excel 365 dynamique
ARRAYFORMULA brille sur Google Sheets pour automatiser des calculs sans écrire de code. Sur Excel 365, les formules dynamiques offrent la même puissance sans syntaxe spéciale ; sur Excel classique (2016, 2019), seule la recopie manuelle est possible.
| Critère | ARRAYFORMULA | Formules manuelles | Excel 365 dynamique |
|---|---|---|---|
| Vitesse de configuration | Instantané | Lent (copier-coller) | Instantané |
| Adaptation aux nouvelles lignes | Oui (plages ouvertes) | Non (recopie manuelle) | Oui (natif) |
| Maintenance | 1 seule formule à modifier | Chaque cellule à modifier | 1 seule formule |
| Performance sur grandes données | Correct (< 50k lignes) | Très bon | Excellent |
| Disponibilité | Google Sheets | Partout | Excel 365 |
| Complexité d'apprentissage | Moyenne | Facile | Facile (automatique) |
ARRAYFORMULA vs MAP vs BYROW vs SCAN vs REDUCE
Sur Excel 365, plusieurs fonctions à base de LAMBDA recouvrent les usages d'ARRAYFORMULA en offrant plus de souplesse. Voici à quoi sert chacune et son avantage face à ARRAYFORMULA.
| Fonction | Utilisation principale | Avantage vs ARRAYFORMULA |
|---|---|---|
| MAP | Appliquer une LAMBDA personnalisée sur un tableau | Plus flexible, logiques complexes réutilisables |
| BYCOL | Appliquer une fonction sur chaque colonne | Spécialisée par colonne, plus lisible |
| BYROW | Appliquer une fonction sur chaque ligne | Spécialisée par ligne, idéale pour agréger |
| SCAN | Totaux cumulés et calculs séquentiels | Bien plus simple pour les running totals |
| REDUCE | Réduire un tableau à une valeur unique | Idéale pour des agrégations sur mesure |
Questions fréquentes
Non, ARRAYFORMULA est exclusive à Google Sheets. Excel 365 propose des formules dynamiques équivalentes qui se propagent automatiquement, sans fonction spécifique à écrire. Si tu utilises Excel classique (2016, 2019), cette mécanique n'existe pas : tu dois recopier ta formule sur chaque ligne ou passer par d'autres approches.
Oui, c'est tout son intérêt. ARRAYFORMULA peut envelopper la quasi-totalité des formules de Google Sheets. C'est particulièrement utile avec SI, CONCATENER, TEXTE et les opérations mathématiques. Fais juste attention aux fonctions d'agrégation comme SOMME ou MOYENNE : elles regroupent les données et demandent une approche différente si tu veux un résultat par ligne.
Si tout va bien, les résultats se remplissent d'eux-mêmes sur toutes les lignes concernées : tu vois les valeurs calculées apparaître instantanément.
Si tu vois #N/A ou des cellules vides inattendues, vérifie d'abord que toutes tes plages ont la même taille : c'est la cause la plus fréquente.
Sur de très grandes plages (plus de 50 000 lignes), elle peut ralentir légèrement le recalcul. Le moteur doit en effet réévaluer tout le tableau à chaque changement.
Pour la plupart des usages professionnels (moins de 10 000 lignes), la différence est négligeable et largement compensée par le temps gagné en configuration et en maintenance.
Si tu utilises des plages ouvertes (comme A2:A au lieu de A2:A100), ARRAYFORMULA s'adapte automatiquement aux nouvelles lignes. C'est la meilleure pratique pour les bases de données évolutives.
Avec une plage fermée, tu devras au contraire élargir la plage à la main dans ta formule pour inclure les nouvelles données.
Enveloppe ta formule dans un test SI sur la cellule source : =ARRAYFORMULA(SI(A2:A=""; ""; A2:A*B2:B)). Tant que la ligne source est vide, le résultat reste vide.
C'est la technique de référence pour garder un tableau propre, sans 0 ni erreur sur les lignes encore vides en bas de la plage.
Pour aller plus loin
Continue sur ta lancée après la fonction ARRAYFORMULA : la leçon associée, un modèle prêt à l'emploi et le guide pour progresser.
Tout voir
