Quand une feuille de calcul s’allonge, recopier la même formule ligne après ligne devient vite une tâche répétitive. Dans Google Sheets, ARRAYFORMULA permet d’étendre certains calculs à des plages de cellules depuis une seule formule.
Cette méthode facilite le calcul automatique et limite les oublis lors de l’ajout de nouvelles lignes. Comprendre sa syntaxe, ses usages et ses limites aide à construire des formules dynamiques adaptées à la gestion des données.
A retenir :
- Une formule unique pour traiter plusieurs lignes ou colonnes
- Des plages ouvertes pour accueillir les nouvelles données
- Un test SI pour préserver les cellules sans valeur
- Des plages bornées pour maîtriser les calculs volumineux
ARRAYFORMULA dans Google Sheets : syntaxe et fonctionnement
Cette approche prend tout son sens lorsqu’une formule doit traiter une colonne entière, sans recopie manuelle. Selon l’aide Google Sheets, ARRAYFORMULA affiche les résultats d’une formule matricielle sur plusieurs lignes ou colonnes.
Transformer une formule classique en formule matricielle
Pour appliquer ce principe, il faut généralement remplacer les références à des cellules isolées par des plages correspondantes. Une formule telle que =A2*B2 devient, dans la cellule de résultat, =ARRAYFORMULA(A2:A*B2:B).
La formule unique produit alors une série de résultats alignés sur les données des colonnes A et B. Selon l’aide Google Sheets, la fonction peut également servir à utiliser des fonctions qui ne traitent pas directement les tableaux.
Besoin
Formule
Résultat attendu
Multiplier deux colonnes
=ARRAYFORMULA(A2:A*B2:B)
Produits ligne par ligne
Limiter la plage
=ARRAYFORMULA(A2:A1000*B2:B1000)
Calcul jusqu’à la ligne 1000
Conserver les lignes vides
=ARRAYFORMULA(SI(A2:A= » »; » »;A2:A*B2:B))
Résultats uniquement si A contient une valeur
Multiplier une ligne
=ARRAYFORMULA(A1:E1*2)
Valeurs doublées à l’horizontale
La formule doit disposer d’un espace libre sous ou à côté de sa cellule d’origine pour afficher ses résultats. Cette contrainte explique pourquoi une cellule déjà remplie peut empêcher la propagation de la formule.
Les gestes essentiels pour démarrer sont simples :
- Placer la formule dans la première cellule de résultats
- Remplacer les références isolées par des plages alignées
- Vérifier que les cellules de sortie sont libres
- Tester le calcul sur quelques lignes représentatives
Choisir entre plage ouverte et plage bornée
Une plage ouverte comme A2:A peut inclure les données ajoutées ultérieurement, ce qui réduit les interventions manuelles. Selon l’aide Google Sheets, ARRAYFORMULA peut étendre les résultats sur plusieurs lignes ou colonnes, mais le choix de la plage reste déterminant.
Une plage bornée, par exemple A2:A1000, évite d’étendre inutilement certains calculs à toute la colonne. Pour un tableau léger, la plage ouverte offre de la souplesse ; pour une feuille chargée, une limite explicite facilite le contrôle.
Exemples ARRAYFORMULA : calculs et fonctions imbriquées
Une fois la syntaxe maîtrisée, les calculs deviennent plus utiles lorsqu’ils répondent à un besoin concret. Les fonctions imbriquées permettent notamment de traiter les cellules vides ou d’associer un résultat à chaque référence recherchée.
Combiner ARRAYFORMULA et SI pour gérer les cellules vides
Dans un suivi de ventes, une colonne vide ne devrait pas afficher une suite de zéros sous les lignes renseignées. La formule =ARRAYFORMULA(SI(B2:B= » »; » »;B2:B*10%)) calcule une commission lorsque la cellule B contient une valeur.
Le test SI vérifie d’abord la présence d’une donnée, puis déclenche le calcul seulement lorsque cette condition est remplie. Cette structure rend les résultats plus lisibles et s’applique aussi à des catégories fondées sur l’ancienneté ou des seuils.
Quelques associations courantes peuvent servir de point de départ :
- SI pour conditionner le calcul à une cellule renseignée
- SI imbriqués pour répartir les valeurs en catégories
- SIERREUR pour définir un résultat de remplacement
- RECHERCHEV pour associer des données issues d’une autre table
Associer ARRAYFORMULA et RECHERCHEV
Pour rapprocher une liste de produits et un onglet de stocks, une formule peut rechercher chaque identifiant de la colonne A. Par exemple, =ARRAYFORMULA(SIERREUR(RECHERCHEV(A2:A;Stock!A:B;2;FAUX); »Stock inconnu »)) renvoie le stock correspondant ou un texte de remplacement.
Fonction associée
Usage courant
Point de vigilance
SI
Afficher un résultat selon une condition
Tester les cellules vides
SIERREUR
Remplacer un résultat d’erreur
Choisir un message explicite
RECHERCHEV
Retrouver une valeur dans une table
Vérifier la colonne de recherche
INDEX et EQUIV
Associer une valeur à une recherche
Contrôler les plages utilisées
Un résultat manquant peut être masqué par SIERREUR, mais un message précis aide davantage à repérer les données absentes. Les exemples de formules gagnent ainsi en utilité lorsque les erreurs restent compréhensibles pour les personnes qui consultent la feuille.
Erreurs ARRAYFORMULA et bonnes pratiques de gestion des données
Ces formules automatisent les calculs, mais leur propagation dépend de la structure de la feuille. Avant de chercher une erreur complexe, vérifier les cellules de sortie et l’alignement des plages permet souvent de trouver la cause.
Résoudre les blocages et les résultats inattendus
Si Sheets indique que le résultat ne peut pas s’étendre, des valeurs occupent probablement les cellules nécessaires à sa propagation. Il faut alors libérer ces cellules ou réduire la plage afin que les résultats disposent d’un espace suffisant.
Une erreur de calcul peut aussi venir d’une référence restée isolée, comme B2 dans une formule qui traite A2:A. Remplacer cette référence par B2:B rétablit l’alignement entre les deux plages concernées.
Pour maintenir une feuille lisible et maîtrisable, quelques habitudes réduisent les erreurs :
- Éviter les saisies manuelles dans la plage de propagation
- Limiter les plages lorsque le tableau est volumineux
- Ajouter une note décrivant la logique de la formule
- Contrôler les résultats avant d’élargir le calcul
Adapter les calculs à la taille du tableau
Une formule appliquée à une colonne entière peut recalculer de nombreuses cellules lors des modifications. Borner la plage et éviter d’empiler inutilement plusieurs recherches aide à préserver la réactivité d’un fichier chargé.
Les fonctions qui produisent déjà des tableaux, comme FILTER, QUERY, SORT ou UNIQUE, n’ont pas toujours besoin d’être entourées par ARRAYFORMULA. Selon l’aide Google Sheets, la fonction sert à étendre des résultats matriciels et à traiter certaines fonctions avec des tableaux.
Les formules matricielles d’Excel et ARRAYFORMULA répondent à des besoins proches, mais leur syntaxe et leurs mécanismes diffèrent. Dans Google Sheets, la priorité reste de choisir des plages cohérentes, de réserver l’espace de sortie et de rendre les conditions explicites.
Source : Google, « ARRAYFORMULA », Aide Éditeurs Google Docs.
