Google Sheets : les limites de ARRAYFORMULA, ou pas !

ARRAYFORMULA en quelques mots

La définition de ARRAYFORMULA selon Google est la suivante :

“Permet d’afficher des valeurs issues d’une formule de tableau sur plusieurs lignes et/ou colonnes, et d’utiliser avec des tableaux des fonctions ne prenant pas en charge ces derniers.”

Google (lien vers le support)

Autrement dit, la formule réside dans une seule cellule, mais la résultante est affichée / diffusée sur l’ensemble de la plage devant contenir le résultat ; c’est comme si nous tirions la formule vers le bas et / ou vers la droite, mais sans la tirer réellement. Cette fonction est dynamique, c’est-à-dire si une valeur est modifiée dans la plage d’origine, le résultat de la fonction utilisant ARRAYFORMULA sera automatiquement mis à jour.

Les fonctions qui ne fonctionnent pas avec ARRAYFORMULA

Notez que de nombreuses formules de la catégorie Tableau sont étendues automatiquement aux cellules voisines, ce qui évite d’avoir à utiliser explicitement ARRAYFORMULA ; par exemple, UNIQUE, FILTER, QUERY, SEQUENCE, TRANSPOSE, FLATTEN.

Prérequis : vous connaissez et utilisez déjà ARRAYFORMULA, vous savez que si vous appuyez sur Ctrl + Maj + Entrée pendant que vous modifiez une formule, ARRAYFORMULA est ajouté automatiquement au début de la formule. Mais vous avez également été frustré à plusieurs reprises, car ARRAYFORMULA ne fonctionne pas toujours.

Faire une somme avec ARRAYFORMULA

Nous souhaitons coupler une fonction qui travaille sur une plage horizontale, par exemple SOMME, avec un ARRAYFORMULA à la verticale, cela ne donne pas le résultat escompté.

Le résultat de =ArrayFormula(SOMME(B2:D8)) est 1307, c’est-à-dire la somme de toutes les valeurs de la plage et non la somme de la ligne 2 en E2, suivi de la somme de la ligne 3 en E3, etc. jusqu’à la ligne 8.

La première façon de résoudre ce problème, est bien sûr d’effectuer la =SOMME(B2:D2) puis de tirer la formule vers le bas ; ce n’est pas le propos de cet article.

Pour pouvoir utiliser la puissance de ARRAYFORMULA, le premier palliatif est d’écrire une formule qui fonctionne à la verticale ; la somme de B2 à D2 est en fait B2+C2+D2.

Nous avons trois plages verticales qui s’additionnent. La formule =ArrayFormula(B2:B8+C2:C8+D2:D8) est écrite seulement dans la cellule E2, mais les résultats de chaque ligne sont bien indiqués dans les cellules allant de E2 à E8.

Pour pleinement tirer parti de ARRAYFORMULA, il faut indiquer une plage semi-ouverte, c’est-à-dire sans limite de fin de ligne ; la formule devient donc =ArrayFormula(B2:B+C2:C+D2:D), mais par contre cela génère des zéros pour toutes les lignes qui ne comportent pas de valeurs numériques.

Pour pallier l’affichage de ces zéros inutiles, il ne nous reste plus qu’à indiquer à Sheets que si, par exemple, la colonne des produits, la colonne A, est vide, alors la formule ne s’applique pas. Pour cela, nous allons faire appel à la fonction ESTVIDE imbriquée avec la fonction SI (voir l’article Nettoyer et optimiser une feuille de calcul).

La formule globale est : =ArrayFormula(SI(ESTVIDE(A2:A);;B2:B+C2:C+D2:D))

Utiliser GOOGLETRANSLATE avec ARRAYFORMULA

C’est bien gentil tout ça, mais il n’y a pas toujours d’alternative à la fonction initiale ; prenons par exemple la fonction GOOGLETRANSLATE qui permet de traduire du texte dans une langue de son choix, cette dernière aussi, n’est pas compatible avec ARRAYFORMULA.

La formule =ArrayFormula(GOOGLETRANSLATE(A2:A8; »auto »; »fr »)) en B2, ne traduit en français que la cellule A2, alors que nous souhaitons obtenir la traduction du texte de la cellule A2 en B2, puis la traduction du texte de la cellule A3 en B3 et ainsi de suite.

Pour résoudre cette problématique, nous allons utiliser une des dix nouvelles fonctions apparues dans Google Sheets en août 2022 : BYROW qui signifie par ligne.

Selon le support Google, cette fonction regroupe un tableau par ligne en appliquant à chaque ligne une fonction LAMBDA.

Le principe d’une fonction LAMBDA est nouveau pour un tableur ; il s’agit de déclarer une variable puis d’utiliser cette variable dans une fonction spécifique.

Quand nous appelons la fonction BYROW, en tapant le signe égal suivi de BYROW, apparaît alors l’aide relative à cette fonction.

Les arguments attendus par la fonction BYROW sont donc une plage et une fonction personnalisée LAMBDA..

La fonction LAMBDA quant à elle, nécessite un [nom; …], c’est-à-dire une variable et une formule dans laquelle on retrouve cette variable.

Dans l’exemple donné par l’aide, LAMBDA(x; x+1) x est donc la variable et la formule indique que pour chaque x d’une plage, la fonction va ajouter 1.

Nous souhaitons donc obtenir une traduction en français dans la colonne B à chaque fois qu’il y a du texte dans une cellule de la colonne A. Le nombre de lignes avec du texte dans la colonne A n’est pas connu et peut évoluer à tout moment.

En B2, la formule =GOOGLETRANSLATE(A2; »auto »; »fr ») traduit bien le texte de la cellule A2, mais nous voulons avoir le même résultat pour chacune des lignes non vides de la colonne A. Nous allons donc utiliser la fonction GOOGLETRANSLATE au sein d’une fonction LAMBDA.

Voici la formule uniquement en B2
=BYROW(A2:A8;LAMBDA(texte_à_traduire;GOOGLETRANSLATE(texte_à_traduire; »auto »; »fr »)))
et le résultat :

Pour la fonction LAMBDA, nous avons donc commencé par déclarer la variable texte_à_traduire, que nous utilisons dans la formule qui consiste à traduire ce texte depuis la langue d’origine “auto” vers le français “fr” : GOOGLETRANSLATE(texte_à_traduire;”auto”;”fr”).

Ensuite nous indiquons à Google Sheets qu’il faut appliquer la formule précédente ligne par ligne depuis A2 et jusqu’à A8, avec BYROW(A2:A8);

Les cellules A6 et A7 ne contiennent pas de texte à traduire, alors la formule renvoie #VALEUR!.
Comme précédemment, pour pallier l’affichage de cette valeur d’erreur, nous pouvons utiliser la fonction ESTVIDE associée à la fonction SI et le tout encapsulé dans un ARRAYFORMULA ; il faut dans ce cas-là, positionner ces fonctions avant le BYROW.

De plus, encore une fois pour pleinement bénéficier de la puissance de ARRAYFORMULA, nous allons définir une plage semi-ouverte A2:A, donc sans limite de fin, pour le texte à traduire.

La formule est uniquement en B2 :
=ArrayFormula(SI(ESTVIDE(A2:A);;
BYROW(A2:A;
LAMBDA(texte_à_traduire; GOOGLETRANSLATE(texte_à_traduire; »auto »; »fr »)))))

Le résultat est :

Nous pouvons dorénavant entrer un texte à traduire dans n’importe quelle cellule de la colonne A et la traduction en français apparaîtra automatiquement sur la même ligne mais dans la colonne B.

Combiner plusieurs conditions avec ARRAYFORMULA (ET, OU)

Retrouvez dans cet article des solutions pour combiner des conditions sans utiliser ET ou OU dans vos formules.

Liste des fonctions inactives avec ARRAYFORMULA

Nous avons commencé à compléter un tableau avec les fonctions qui ne fonctionnent pas avec ARRAYFORMULA et les alternatives possibles.

Vous pouvez bien sûr nous aider à compléter ce tableau en nous signalant les fonctions que vous avez testées et / ou les difficultés que vous avez rencontrées.

J’espère que cet article vous permettra de ne plus être bloqué par le non-fonctionnement de ARRAYFORMULA pour certaines fonctions !

N’hésitez pas à nous contacter pour vous accompagner dans votre formation ou pour des projets spécifiques autour des outils Google. Si vous voulez en savoir plus sur Google Sheets, nous vous invitons à vous rendre sur Numeriblog ! À bientôt !