La nouvelle fonction Excel LET permet de simplifier l’écriture et la lecture de vos formules, en définissant des variables et en y attribuant une valeur. Il s’agit ensuite d’utiliser ces variables dans le calcul à effectuer. Cet article vise à vous expliquer comment faire.
Les paramètres de la fonction Excel LET
Voici les paramètres de la fonction tel qu’on les voit dans Excel:
- nom1, nom2,… étant le nom des variables
- Nom_valeur1, nom_valeur2,… étant les valeurs ou les calculs associés à ces variables
- calcul_ou_nom2 étant le calcul à effectuer pour afficher le résultat escompté
Ce qu’il faut savoir sur la fonction Excel LET
Il est possible d’identifier jusqu’à 126 variables et nous pouvons assigner soit une valeur fixe (input) à ces variables, comme dans l’exemple 1 de l’image ci-dessus où x = 1, soit un calcul référençant des cellules de notre classeur, comme dans l’exemple suivant: Let(x, A1 + B1, x+1).
Lorsque que l’on met un calcul dans une variable, il est calculé une seule fois à l’intérieur de la variable et c’est le résultat qui est utilisé dans le calcul, il en résulte une amélioration de la performance. Le calcul peut s’exécuter jusqu’à 2 fois plus rapidement, lorsque la variable est utilisée plusieurs fois dans ce calcul.
Particularités à retenir
- Les variables définies dans notre fonction LET n’existent qu’à l’intérieur de cette même fonction. On ne peut pas récupérer la valeur de ces variables dans une autre formule.
- Il faut éviter d’utiliser les nombres pour nommer nos variables ainsi que les noms d’autres fonctions d’Excel. Autrement, on pourrait obtenir un message d’erreur.
- Au moment où j’écris cet article, la fonction LET est disponible seulement dans le version Insider d’Office 365.
Habitué de travailler avec Excel et besoin de vous mettre à jour avec les nouvelles fonctionnalités qu’offre maintenant Excel 365 ? Pour concevoir des solutions en phase avec les développements technologiques actuels, jetez un œil à nos formations sur Excel 365. |
---|
Quelques exemples d’utilisation
Pour ces différents exemples, nous allons utiliser le tableau de données suivant qui comprend les ventes par jour par client :
Exemple de base de la fonction Excel LET
Commençons par calculer la marge pour chacune des lignes de notre tableau:
Si on construit notre formule avec LET:
Si on décortique la formule, nous avons en 1er paramètre: la 1ere variable Vente, pour laquelle on assigne la valeur de la colonne Ventes (2e paramètre) de notre tableau de données. Le @ signifie que l’on prend la valeur de la même ligne que la cellule où on se trouve. Nous avons ensuite le 3e paramètre: la variable Coût pour laquelle on assigne la valeur de la colonne Coût des ventes (4e paramètre) de notre tableau de données. Puis nous avons le calcul (5e paramètre) qui soustrait la variable Coût à la Variable Vente.
Donc pour la formule qui se trouve sur la première ligne ça nous donne:
- 1ere variable = Vente = 1000
- 2e variable = Coût = 800
- Calcul = Vente – Coût = 1000 – 800
- Résultat de la fonction = 200
Pour une formule aussi simple, je laisserais tomber le LET puisqu’il serait plus simple et plus facile de lire la première version. De plus, nous n’obtenons aucun gain au niveau de la performance, puisque chacune des variables n’est utilisée qu’une seule fois.
Exemple plus avancé de la fonction Excel LET
Allons donc un peu plus loin avec un calcul de la marge en pourcentage:
sans LET:
avec LET:
Ici, il est plus raisonnable d’utiliser la fonction LET. C’est plus facile à lire et c’est plus performant. Pour changer de lignes dans l’écriture de la formule, il s’agit d’utiliser les touches ALT + Enter de votre clavier.
La fonction LET dans une formule de SI imbriqués
Essayons maintenant avec l’ajout d’un commentaire, à chaque ligne, afin d’inscrire si la marge est Excellente, Neutre ou Mauvaise selon les critères du tableau ci-bas.
sans LET:
avec LET en utilisant une seule variable pour le calcul de la marge
Alternative d’usage de la fonction Excel LET
On pourrait également diviser la variable Marge en deux autres variables: Vente et Coût
avec LET version 2
Ça devient encore plus intéressant. La formule est beaucoup plus facile à lire et le calcul sera également plus rapide à exécuter. En effet, la Marge n’est calculée qu’une seule fois, puis le résultat est ensuite utilisé dans le calcul à deux reprises.
La fonction LET et les fonctions matricielles dynamiques
On peut également inclure des fonctions matricielles dynamiques à l’intérieur de la fonction LET.
Imbrication de la fonction matricielle UNIQUE
Par exemple, à partir de la fonction UNIQUE, on peut obtenir la liste de clients uniques dans notre tableau de données puis avec l’aide de la fonction SOMME.SI, on peut obtenir le total des ventes pour chacun de ces clients. Voici le résultat :
Imbrication d’un array (liste de nombre)
Supposons qu’à partir de ce résultat, on veuille obtenir le top 2 de nos clients :
Ici, on utilise une liste de valeur (1 et 2) dans la fonction TOP {1;2}
- La variable Client correspond à notre liste de clients de la colonne G (la fonction est insérée en G7).
- La variable Ventes correspond à notre liste de ventes de la colonne H (la fonction est insérée en H7).
- Le calcul se fait alors avec une fonction INDEX. Pour aller chercher le numéro de ligne (2e paramètre), on utilise la fonction GRANDE.VALEUR. Remarquez que le 2e paramètre de la fonction GRANDE.VALEUR, qui correspond au rang, contient la variable TOP, qui elle contient une liste de nombres (1 et 2), ce qui fait en sorte que l’on obtient une liste dynamique de 2 valeurs dans notre top client: GRANDE.VALEUR(Ventes;1) et GRANDE.VALEUR(Ventes;2)
Résultat :
Imbrication d’une fonction matricielle SEQUENCE
Maintenant, on pourrait ajouter une ligne Total Ventes, au bas du tableau, avec la somme des ventes de tous les clients. Comme ceci:
Le but serait qu’à l’ajout d’un nouveau client, le tableau dynamique s’ajuste pour inclure ce nouveau client, en décalant le Total des ventes d’une ligne vers le bas. Donc, en ajoutant le client C dans notre tableau de données, on obtient :
Pour être en mesure d’afficher le Total des ventes au bas de notre tableau, on utilise la fonction SEQUENCE pour afficher le texte “Total Ventes” si NBLigne (nombre de clients) + 1 est plus grand que NBLigne, sinon on affiche le client.
La fonction de la colonne Client:
La fonction de la colonne Ventes :
Pour la colonne Ventes, on utilise le même principe que pour la formule précédente, mais cette fois, on affiche la somme des ventes de tous les clients, si NBLigne (nombre de clients) + 1 est plus grand que NBLigne, sinon on affiche la somme des ventes du client (SOMME.SI).
À l’aide de la fonction LET, on est donc en mesure d’écrire ces formules de façon très épurée et facile à interpréter.
La fonction LET pour investiguer nos erreurs
Finalement, si le résultat de notre fonction donne une erreur ou bien si le résultat n’est pas celui escompté, on peut mettre notre calcul dans une variable, ici Resultat. Ensuite on met en dernier paramètre une autre variable pour laquelle on n’est pas convaincu du résultat. Ici, on a utilisé Top. La fonction retournera alors la liste 1 et 2, tel que montré dans le tableau de droite. On peut changer le dernier paramètre avec les différentes variables sans avoir à modifier ou effacer notre calcul !
Fichier d’accompagnement VIP à télécharger
Pour télécharger le fichier utilisé dans ce tutoriel, devenez membre VIP du CFO masqué.
Formation complémentaire
Afin de mettre à jour vos connaissances sur les fonctions et fonctionnalités les plus récentes d’Excel, via O365, nous vous recommandons notre formation Excel O365 – Bienvenue dans l’ère moderne!
Voici quelques commentaires d’apprenants ayant suivi cette formation :