Vous travaillez dans une feuille de calcul Google où une formule doit être copiée sur la dernière ligne de la feuille. De plus, vous souhaitez que la formule soit ajoutée automatiquement lorsqu’une nouvelle ligne est ajoutée à la feuille Google.
Il existe plusieurs façons de résoudre ce problème.
Copier la formule sur Google Sheets
La méthode la plus simple pour copier des formules consiste à utiliser la poignée de recopie dans Google Sheets. Écrivez votre formule dans la première ligne de votre feuille de calcul, puis pointez votre souris vers le coin inférieur droit de la cellule contenant la formule.
Le curseur se transforme en poignée de recopie (symbole plus noir) que vous pouvez faire glisser jusqu’à la dernière ligne de la feuille. La poignée de recopie ne se contente pas de copier les formules dans toutes les cellules adjacentes, elle copie également la mise en forme visuelle.
Si vous devez copier les formules sur plusieurs cellules sans aucune mise en forme, sélectionnez la cellule contenant la mise en forme, appuyez sur Ctrl+C pour la copier dans le presse-papiers. Ensuite, sélectionnez la plage où la formule doit être appliquée, faites un clic droit, choisissez « Collage spécial » et « Coller seulement la formule ».
Appliquer une formule sur toute une colonne dans Google Sheets
Si vous avez des centaines de lignes dans une feuille de calcul Google et que vous souhaitez appliquer la même formule à toutes les lignes d’une colonne en particulier, il existe une solution plus efficace que le copier-coller : les formules matricielles.
Sélectionnez la première cellule de la colonne et saisissez la formule comme précédemment. Cependant, au lieu de spécifier une seule cellule en tant que paramètre, nous spécifierons toute la colonne en utilisant la notation B2:B (à partir de la cellule B2 et jusqu’à la dernière ligne de la colonne B).
Ensuite, appuyez sur Ctrl+Shift+Entrée, ou Cmd+Shift+Entrée sur Mac, et Google Sheets entourera automatiquement votre formule de la fonction ARRAYFORMULA.
Ainsi, nous pouvons appliquer la formule à toute la colonne de la feuille de calcul en utilisant une seule cellule. Les formules matricielles sont plus efficaces car elles traitent un groupe de lignes en une seule fois. Elles sont également plus faciles à maintenir, car vous n’avez besoin de modifier qu’une seule cellule pour modifier la formule.
Un problème que vous avez peut-être remarqué avec les formules précédentes est qu’elles s’appliquent à chaque ligne de la colonne, alors que vous ne souhaitez ajouter des formules qu’aux lignes contenant des données et ignorer les lignes vides.
Cela peut être résolu en ajoutant une condition IF à notre ARRAYFORMULA afin qu’il n’applique pas la formule aux lignes vides.
Google Spreadsheet propose deux fonctions pour tester si une cellule est vide ou non.
- ISBLANK(A1) – Renvoie VRAI si la cellule référencée est vide.
- LEN(A1) <> 0 – Renvoie VRAI si la cellule référencée n’est pas vide, sinon FAUX.
Nos formules matricielles modifiées seraient donc les suivantes :
=ARRAYFORMULA(IF(ISBLANK(A1:A), "", [votre formule ici]))Il existe plusieurs autres façons de tester si une cellule est vide ou non.
Utiliser des formules matricielles dans les en-têtes de colonnes
Dans nos exemples précédents, le texte des titres de colonnes (comme « Taxe », « Montant total ») était pré-rempli et les formules n’étaient ajoutées qu’à la première ligne de l’ensemble de données.
Nous pouvons améliorer nos formules pour qu’elles puissent être appliquées à l’en-tête de colonne lui-même. Si l’indice de la ligne actuelle est 1, calculé à l’aide de la fonction ROW(), la formule affiche le titre de la colonne, sinon elle effectue le calcul à l’aide de la formule.
Auto-Remplissage des formules dans les soumissions de Google Forms
Les fonctions ARRAYFORMULA sont particulièrement utiles pour les formulaires Google lorsque les réponses au formulaire sont enregistrées dans une feuille de calcul Google. Vous ne pouvez pas effectuer de calculs en direct dans les formulaires Google, mais ils peuvent être effectués dans la feuille de calcul qui collecte les réponses.
Vous pouvez créer de nouvelles colonnes dans la feuille de calcul Google et appliquer la fonction ARRAYFORMULA à la première ligne des colonnes ajoutées.
Lorsqu’une nouvelle soumission de formulaire est reçue, une nouvelle ligne est ajoutée à la feuille Google, et les formules sont clonées et appliquées automatiquement aux nouvelles lignes sans que vous ayez à copier-coller quoi que ce soit.
Utilisation de VLOOKUP dans ARRAYFORMULA
Vous pouvez combiner la fonction ARRAYFORMULA avec VLOOKUP pour effectuer rapidement une recherche sur toute une colonne.
Disons que vous avez une feuille « Fruits » qui répertorie les noms des fruits dans la colonne A et les prix correspondants dans la colonne B. La deuxième feuille « Commandes » contient les noms des fruits dans la colonne A, la quantité dans la colonne B, et vous devez calculer le montant de la commande dans la colonne C.
En termes simples, si la ligne de la cellule actuelle est 1, affichez le titre de la colonne en texte brut. Si la ligne est supérieure à 1 et que la colonne A de la ligne actuelle n’est pas vide, effectuez une recherche VLOOKUP pour récupérer le prix de l’article à partir de la feuille « Fruits ». Ensuite, multipliez ce prix par la quantité dans la cellule B et affichez la valeur dans la cellule C.
Si votre plage VLOOKUP se trouve dans une autre feuille de calcul Google, utilisez la fonction IMPORTRANGE() avec l’ID de l’autre feuille Google.
Veuillez noter que vous devrez peut-être utiliser des points-virgules dans les formules de feuille de calcul au lieu de virgules pour certaines localisations.
C’est tout pour aujourd’hui ! J’espère que ces astuces vous aideront à copier vos formules dans Google Sheets. Pour en savoir plus sur les feuilles de calcul et d’autres astuces, rendez-vous sur Crawlan.com.
À bientôt pour de nouvelles astuces !
