Appliquer des formules aux colonnes avec ARRAYFORMULA

Normalement, si vous voulez effectuer un calcul sur chaque ligne, vous écrivez une formule et la recopiez vers le bas. Cela fonctionne, mais vous vous retrouvez avec des centaines de formules copiées qu’il faut prolonger chaque fois que de nouvelles données arrivent.

Google Sheets propose une meilleure solution : ARRAYFORMULA. Une seule formule dans une seule cellule calcule toute la colonne à la fois et s’agrandit automatiquement.

ARRAYFORMULA indique à Sheets d’appliquer une formule à des plages entières plutôt qu’à des cellules individuelles. Le résultat se répartit vers le bas pour remplir autant de lignes que la plage d’entrée en contient.

Au lieu de répéter =A2*B2 100 fois, vous écrivez une seule formule faisant référence à A2:A et B2:B, et les résultats se remplissent automatiquement.

Supposons que la colonne A contienne les quantités et la colonne B les prix. Pour calculer le total de chaque ligne, placez cette formule unique dans C2.

Elle multiplie chaque valeur de A par la valeur correspondante de B jusqu’en bas, et vous n’avez plus à toucher aux cellules situées à partir de C3.

Remarquez A2:A au lieu de A2:A100. Cette forme ouverte signifie toutes les lignes à partir de la ligne 2, de sorte que les nouvelles données sont incluses automatiquement.

C’est là que réside la véritable puissance d’ARRAYFORMULA : votre feuille reste évolutive. Ajoutez une nouvelle commande, et le total apparaît sans travail supplémentaire.

Les plages ouvertes incluent des milliers de lignes vides sous vos données, et la formule essaie également de les calculer, ce qui affiche souvent 0 ou des valeurs indésirables.

Placez le calcul dans une instruction IF qui vérifie si la ligne est vide. Si A est vide, renvoyez une chaîne vide ; sinon, effectuez le calcul.

Voici une astuce pratique : incluez l’en-tête de colonne dans la formule matricielle afin que toute la colonne soit gérée depuis une seule cellule. Placez cette formule dans C1.

La première partie affiche le texte de l’en-tête ; le reste calcule chaque ligne de données. Le ; les empile verticalement dans une seule colonne déployée.

ARRAYFORMULA ne sert pas uniquement aux calculs. Elle fonctionne également avec les fonctions de texte. Pour créer une colonne de noms complets à partir des prénoms et des noms, concaténez les deux plages.

Cette formule associe chaque prénom de A au nom correspondant de B, avec un espace entre les deux, pour toutes les lignes en une seule fois.

Certaines fonctions gèrent elles-mêmes les matrices et n’ont pas besoin d’ARRAYFORMULA. SUMIF, FILTER, UNIQUE et QUERY fonctionnent déjà sur des plages.

Utilisez principalement ARRAYFORMULA lorsqu’une opération normalement limitée à une cellule, comme *, & ou LEFT, doit être exécutée sur chaque ligne.

Les fonctions telles que LEFT, UPPER et TRIM agissent normalement sur une seule cellule. Dans ARRAYFORMULA, elles agissent sur une plage entière.

Cette formule convertit en majuscules toutes les adresses e-mail de la colonne A en une seule opération. Sans cette enveloppe, vous ne transformeriez que la première cellule.

Vous n’avez pas toujours besoin de saisir le nom de la fonction. Dans Google Sheets, saisissez votre formule avec des références de plage, puis appuyez sur Ctrl, puis Maj, puis Entrée (Cmd, puis Maj, puis Entrée sur Mac).

Sheets entoure automatiquement la formule avec ARRAYFORMULA. C’est un moyen rapide de transformer une formule recopiée en une seule formule qui se déploie.

Pour les opérations élément par élément, les plages doivent avoir la même hauteur. Mélanger A2:A et B2:B50 peut provoquer des erreurs ou des résultats mal alignés.

Gardez les deux côtés ouverts (A2:A et B2:B) ou fixez-les tous les deux à la même taille. La cohérence permet d’aligner les résultats ligne par ligne.

Vérifiez vos connaissances sur ARRAYFORMULA.

Vous avez appris à calculer des colonnes entières avec une seule formule :

• ARRAYFORMULA applique une opération à des plages entières
• Les plages ouvertes comme A2:A incluent automatiquement les nouvelles lignes
• Entourez-la de IF(A2:A = "", "", ...) pour ignorer les cellules vides
• Elle fonctionne avec les calculs, les concaténations de texte et des fonctions comme UPPER
• Ctrl/Cmd + Maj + Entrée ajoute automatiquement cette enveloppe

Une cellule, une colonne vivante.