Numeriblog Hors-sujet Comment gérer les formules non compatibles avec ArrayFormula sur Google Sheets ?

Comment gérer les formules non compatibles avec ArrayFormula sur Google Sheets ?

Si vous êtes un utilisateur avisé de Google Sheets, vous savez qu’un de ses gros point fort est l’absence du besoin de tirer vos formules vers le bas. En effet, la super formule ArrayFormula permet […]

personnes ont consulté cet article

3 minutes

Rédigé par Christopher Heintz - il y a 2 semaines et modifié le 21/10/2024 à 15:23

Ce que vous allez découvrir

  • Présentation des fonctions LAMBDA
  • La famille des fonctions LAMBDA
  • Place à la démonstration !
  • Conclusion

Comment gérer les formules non compatibles avec ArrayFormula sur Google Sheets ?

Si vous êtes un utilisateur avisé de Google Sheets, vous savez qu’un de ses gros point fort est l’absence du besoin de tirer vos formules vers le bas.

En effet, la super formule ArrayFormula permet en quelques clics d’appliquer une formule à toute une colonne, en une seule fois. Vous gardez ainsi une seule formule au lieu de plusieurs centaines et, surtout, votre formule prend en compte les données futures du tableau !

Mais malheureusement, il reste un problème… Comment faire lorsqu’une formule ne fonctionne pas avec ArrayFormula ? En effet, toutes les formules matricielles (qui prennent en compte ou renvoie un tableau) ne peuvent pas être combinées avec ArrayFormula.

Pour donner quelques exemples : SOMME, FILTER, JOIN et bien plus encore. Du coup, cela veut dire qu’on doit tirer la formule comme à l’ancienne ?

Dans cet article, je vais vous montrer comment, en un clin d’oeil, contourner ce problème grâce aux fonctions LAMBDA ! Accrochez-vous, ça va secouer !

Présentation des fonctions LAMBDA

Sur Google Sheets, une fonction LAMBDA correspond tout simplement à la création d’une fonction personnalisée.

Prenons un exemple : =LAMBDA(x;x*2)(10) veut dire la chose suivante : 

Crée moi une fonction qui prend un paramètre appelé x (j’aurai pu l’appeler machin, bidule ou encore tartempion). Multiplie ce paramètre x par 2. Enfin, (10) veut dire que x vaut 10.

A ce moment précis, il y a de fortes chances que vous ayez déjà votre souris sur la croix de la fenêtre. Ce cas d’usage est complètement vide de sens, autant écrire =5*2 !

La fonction LAMBDA à elle seule ne sert à rien. Cette introduction vous permet de comprendre la technologie pour pouvoir l’appliquer à de réels cas d’usages dans la suite de cet article ! 

La famille des fonctions LAMBDA

Maintenant que nous sommes prêts à aller dans le vif du sujet, Google Sheets regorge de plusieurs fonctions qui utilisent la technologie LAMBDA. En voici une liste non exhaustive : 

  • BYROW
  • BYCOL
  • MAP
  • MAKEARRAY
  • etc.

Et c’est précisément cela qui nous intéresse. C’est grâce à ces fonctions qui utilisent LAMBDA que nous allons pouvoir passer à la vitesse supérieure ! 

La fonction qui vous sera la plus utile est BYROW. Je vous montrerai un exemple juste après. Mais si vous parlez anglais et que vous avez compris le principe, voici en français ce que fait BYROW : 

  • Prend moi chaque ligne d’un tableau que je te donne
  • Pour chaque ligne, définit une fonction LAMBDA
  • Cette fonction LAMBDA prend en paramètre la ligne en cours
  • Applique une fonction ou un calcul de ton choix à cette ligne

Place à la démonstration !

Nous commençons maintenant à voir la lumière au bout du tunnel. C’est parti pour quelques cas d’usage.

BYROW avec une SOMME

SOMME est l’exemple parfait d’une fonction non compatible avec ArrayFormula.


Dans mon exemple, j’écris la formule suivante : =BYROW(B2:C6;LAMBDA(ca;SOMME(ca)))

Donc, voici ce que je demande : 

Pour chaque ligne de la plage B2:C6, exécute moi une fonction LAMBDA personnalisée. Je nomme la variable “ca” qui correspond à la ligne en cours. Effectue moi la somme de cette ligne.


Et voilà ! En un seul calcul, j’obtiens toutes mes sommes

NB : si vous êtes un afficionados du tableur, vous savez déjà qu’il existe des méthodes plus simples pour une somme (utiliser l’opérateur + avec ArrayFormula). Le but de cette démonstration est de vous montrer la puissance de la technologie LAMBDA.

SPARKLINE avec BYCOL

Sparkline est une fonction permettant de créer un graphique miniature dans une cellule. Un graphique pouvant prendre en compte une plage de données, elle n’est pas compatible avec ArrayFormula.

Dans cet exercice, je souhaite connaître en une seule formule la progression des tâches de chaque collaborateur.

J’utilise donc la fonction BYCOL. Cette fonction applique une fonction LAMBDA à toutes les colonnes de mon tableau. Cette fonction LAMBDA crée un SPARKLINE qui compte le nombre de cases cochées en spécifiant un graphique à barres avec un maximum à 5.

Le tour est joué !

Une traduction avec MAP

Cette fois-ci, je vous propose un cas d’usage un peu plus créatif ! 

La fonction GOOGLETRANSLATE permet de traduire une valeur dans une langue souhaitée. Là aussi, elle n’est pas compatible avec ArrayFormula.

Dans mon exercice, je souhaite traduire un tableau à deux dimensions en une seule formule. J’ai donc plusieurs colonnes et plusieurs lignes.

BYCOL ne traiterait que les colonnes et BYROW que les lignes, je suis donc bloqué ! C’est là qu’entre en jeu MAP, qui permet quant à elle d’appliquer une fonction LAMBDA sur toutes les cellules d’un tableau.

Je passe donc en argument deux tableaux : les mots et les codes de langue. Pour chaque cellule de ces tableaux, une fonction LAMBDA s’exécute et prend en paramètre le mot en cours et la langue en cours.

GOOGLETRANSLATE s’occupe de finir le job : génial !

La formule : =MAP(A9:C10;E9:G10;LAMBDA(mots;langues;GOOGLETRANSLATE(mots;langues; »fr »)))

Conclusion

ArrayFormula reste la fonction de base à utiliser le plus possible sur tous vos tableaux Google Sheets. En revanche, lorsque vous êtes confrontés à des cas spécifiques rendant son usage impossible, il reste une solution ! 

Les fonctions LAMBDA vous permettront, peu importe le cas, d’exécuter une fonction spécifique en un seul coup sur toutes les lignes, colonnes ou cellules d’un tableau.

D’ailleurs, j’ai déjà animé un webinaire complet sur les fonctions LAMBDA ! Pour obtenir le replay, cliquez juste ici.

Si vous aussi vous souhaitez devenir un as de Google Sheets, Numericoach vous propose des parcours personnalisés et adaptés à vos besoins. Que ce soit sur la technologie lambda, les regex, query ou encore des notions plus simples, il y en aura pour tout le monde !

Pour discuter de votre projet autour d’un café, c’est par ici !

Articles similaires

  • Articles connexes
  • Plus de l'auteur

Rédacteur

Photo de profil de l'auteur
Christopher HEINTZ

Formateur Google Workspace chez Numericoach

S’abonner
Notification pour
0 Commentaires
Le plus ancien
Le plus récent Le plus populaire
Commentaires en ligne
Afficher tous les commentaires