Découvrez la nouvelle fonction Excel que 90% des experts ne connaissent pas !

Dans ce tutoriel, je vais vous présenter une nouvelle fonction étonnante d'Excel qui simplifie grandement les calculs complexes : la fonction SCAN. Que vous soyez débutant ou utilisateur avancé, cette vidéo vous guidera pas à pas pour la maîtriser, au travers d’exemples concrets.

Dans ce tutoriel, nous allons découvrir comment SCAN fonctionne pour effectuer des calculs cumulés dynamiques au travers d’exemples pratiques de calculs du simple au plus élaboré.

Téléchargement

Vous pouvez télécharger le fichier d'exemple de cet article en cliquant sur le lien suivant :

Tutoriel Vidéo

1. Introduction

Pour illustrer notre tutoriel, nous allons utiliser le tableau des ventes mensuelles de différents produits dans une boutique en ligne :

2. Introduction à la fonction SCAN

Commençons par découvrir la fonction SCAN.

Il s’agit d’une nouvelle fonction très puissante d'Excel qui permet de réaliser des calculs sur une série de valeurs de manière cumulative et dynamique. Cette fonction est d’ailleurs tellement récente que Microsoft n’a même pas encore eu le temps de traduire tous les arguments en français.

La fonction SCAN peut être particulièrement utile pour des tâches diverses, telles que le calcul de totaux cumulatifs, de maximums progressifs et bien plus encore.

3. Les arguments de la fonction SCAN

Voici les arguments attendus par la fonction SCAN :

=SCAN(valeur_initiale; tableau; fonction)

Où :

• « valeur_initiale » est le point de départ du calcul.
• « tableau » est la plage de cellules sur laquelle le calcul sera effectué.
• « fonction » est l'opération à appliquer à chaque élément du tableau.

Mais avant d’aller plus loin, nous devons revenir sur le dernier argument « fonction ». En effet, celui-ci correspond au résultat retourné par la fonction « LAMBDA », elle aussi très récente dans Excel.

Il s’agit de ce que l’on appelle une fonction anonyme, que nous pouvons définir directement dans une formule Excel.

La fonction LAMBDA prend généralement deux paramètres : le résultat accumulé jusqu'à présent et l'élément courant du tableau.

Cela nous permet de réaliser des calculs complexes directement dans nos formules Excel sans avoir besoin de créer des fonctions nommées séparées.

Prenons un exemple concret pour mieux comprendre l'utilisation de SCAN et LAMBDA ensemble.

Imaginons que nous souhaitions calculer le total cumulatif des ventes pour un produit. Pour cela, nous pourrons saisir la formule suivante dans la cellule F9 :

=SCAN(0;B9:B20;LAMBDA(a;b;a+b))

Ici, les arguments utilisés sont les suivants :

• 0 comme valeur_initiale, ce qui signifie que notre calcul commence à zéro.
• B9:B20 comme tableau, ce qui spécifie la plage de cellules contenant les valeurs des ventes que nous souhaitons cumuler.
• LAMBDA(a; b; a+b) comme fonction, ce qui indique que nous souhaitons additionner le résultat accumulé (a) avec la valeur courante (b) pour chaque cellule dans la plage spécifiée.

Pour mieux comprendre ce qui se passe, voyons comment SCAN fonctionne étape par étape avec notre exemple.

La fonction SCAN va procéder de la manière suivante :

• Étape 1 – Le mois de janvier:
  • Initialisation : a est initialisé à 0 (valeur_initiale), b est la valeur de janvier 150.
  • Calcul : 0 + 150 = 150
• Étape 2 – Le mois de février :
  • a est maintenant 150 (résultat précédent), b est 180.
  • Calcul : 150 + 180 = 330
• Étape 3 – Le mois de mars :
  • a est 330 (résultat précédent), b est 170.
  • Calcul : 330 + 170 = 500
• Et ainsi de suite pour chaque mois jusqu'à décembre.

En fin de compte, dans notre colonne E, nous aurons une série de totaux cumulatifs représentant les ventes accumulées mois après mois. Il est important de comprendre que la fonction SCAN lit chaque valeur du tableau spécifié et applique la fonction définie.

De plus, comme vous pouvez le constater, la beauté de la fonction SCAN réside dans sa capacité à traiter les données de manière dynamique. En effet, il nous a suffi de saisir la formule dans la colonne F9 pour qu’Excel la propage automatiquement sur toutes les lignes du tableau.

Par exemple, vous aurez peut-être remarqué que notre tableau est en réalité incomplet, étant donné qu’il manque le mois d’octobre !

Nous allons donc ajouter une nouvelle ligne après le mois de septembre, puis ajouter les valeurs de vente correspondantes :

Vous pouvez alors constater que les résultats se mettent à jour de manière totalement automatique !

4. Calcul du maximum cumulatif des ventes

La fonction SCAN devient particulièrement puissante lorsqu'elle est combinée avec des fonctions LAMBDA.

Cela nous permet de réaliser des calculs plus complexes et de personnaliser les opérations effectuées sur nos données.

Imaginons maintenant que nous souhaitions voir le maximum cumulatif des ventes pour le produit B. En d’autres termes, pour chaque mois de l’année, nous allons souhaiter connaître le montant maximal de ventes réalisées au cours d’un mois.

Nous utiliserons alors la fonction SCAN combinée à une fonction LAMBDA pour ce faire, en saisissant la formule suivante dans la cellule H9 :

=SCAN(0;C9:C20;LAMBDA(a;b;MAX(a;b)))

Ici, les résultats retournés par la fonction vont commencer avec 0 et, pour chaque valeur de la colonne C, elle calcule le maximum entre la valeur actuelle et la valeur précédente.

Ainsi, en janvier, le maximum entre zéro et 510 est bien de 510.

510 devient alors la référence.

En février, les ventes s’élèvent à 290, lequel est inférieur à 510.

Par contre, en mars, le produit B ayant été vendu à hauteur de 530 unités, c’est cette valeur qui sera retournée.

5. Calcul cumulatif avec condition

Maintenant, voyons comment effectuer le même calcul, en ajoutant toutefois une condition, dans laquelle nous souhaitons connaître le montant maximal cumulatif, mais uniquement pour les valeurs inférieures à 500.

Pour cela, nous pourrons intégrer la fonction conditionnelle SI directement dans la fonction LAMBDA :

Dans la cellule J9, nous saisissons la formule suivante :

=SCAN(0;D9:D20;LAMBDA(a;b;SI(b<500;MAX(a;b);a)))

Explication de la formule

• 0 est la valeur initiale du calcul.
• D9:D20 est la plage de cellules contenant les valeurs des ventes du produit C.
• LAMBDA(a; b; SI(b < 500; MAX(a; b); a)) définit l'opération de condition :
  • a est le résultat accumulé jusqu'à présent.
  • b est la valeur courante du tableau spécifié.
  • SI(b < 500;MAX(a; b); a) vérifie si b est inférieur à 500. Si c'est le cas, la fonction retourne le maximum entre a et b, sinon elle retourne a (le résultat accumulé sans changement).

Nous obtenons ainsi dans la colonne J les montants maximaux cumulatifs pour les ventes du produit C, mais uniquement pour les valeurs inférieures à 500.

Cette colonne montrera le montant maximal cumulé des ventes pour chaque mois, en respectant la condition spécifiée.

6. Calcul des ventes cumulées par trimestre

Voyons maintenant un exemple avec une formule plus poussée.

Ici, nous allons souhaiter calculer les ventes cumulées par trimestre. Cela signifie que nous allons additionner les ventes mensuelles pour chaque trimestre et réinitialiser le cumul au début de chaque nouveau trimestre.

Pour cela, nous utiliserons la fonction SCAN combinée à des conditions et des fonctions de texte pour identifier le début de chaque trimestre.

Nous voulons calculer les ventes cumulées pour chaque trimestre, en réinitialisant le cumul à chaque début de trimestre (avril, juillet, octobre).

Voici la formule pour calculer les ventes cumulées par trimestre :

=SCAN(0;B9:B20;LAMBDA(a;b;SI(ESTNUM(TROUVE(DECALER(b;0;-1);"AvrilJuilletOctobre"));b;a+b))) 

Voici maintenant l’explication de cette fonction LAMBDA :

• Comme d’habitude, nous retrouvons « a » pour le résultat accumulé jusqu'à présent et « b » pour la valeur courante (ventes pour le mois courant).
• DECALER(b;0;-1) : Étant donné que dans la fonction LAMBDA, nous ne pouvons pas faire appel directement à une cellule par ses coordonnées, nous allons devoir partir de la valeur « b », et décaler celle-ci d’une cellule vers la gauche pour obtenir le mois correspondant à chaque vente.
• TROUVE(…;"AvrilJuilletOctobre") : Ensuite, nous allons chercher à savoir si ce mois correspond à l’un des trois mois de début d'un trimestre (avril, juillet, octobre). Inutile de tester le mois de janvier, étant donné que la valeur de « a » est initialisée à zéro.
• ESTNUM(...) : Vérifie si la fonction TROUVE a trouvé une correspondance (début de trimestre).
• SI(ESTNUM(...); b; a+b) : Pour finir, si effectivement le mois courant est le début d'un trimestre, alors la fonction SI réinitialise le cumul en utilisant la valeur courante b. Sinon, elle ajoute la valeur courante b au cumul a.

7.Manipulation de chaînes de caractères

Pour finir, sachez qu’il est également possible d’utiliser la fonction SCAN pour manipuler des textes de manière très rapide.

Imaginons que nous souhaitions obtenir une liste cumulative des mois en abrégé.

Nous pouvons utiliser la fonction SCAN combinée avec des fonctions de texte pour atteindre cet objectif.

Pour cela, nous saisissons la formule suivante dans la cellule N9 :

 =SCAN("";A9:A20;LAMBDA(a;b;a&GAUCHE(MAJUSCULE(b);3)&",  "))

Où :

• "" est la valeur initiale de la chaîne (chaîne vide).
• A9:A20 est la plage de cellules contenant les noms des mois.
• LAMBDA(a, b, a & GAUCHE(MAJUSCULE(b), 3) & ", ") définit l'opération de manipulation de texte :
  • a est la chaîne de caractères accumulée jusqu'à présent.
  • b est le texte courant (nom du mois).
  • GAUCHE(MAJUSCULE(b), 3) prend les trois premières lettres du nom du mois, converties en majuscules.
  • a &GAUCHE(MAJUSCULE(b), 3) & ", " concatène la chaîne accumulée avec l'abréviation du mois et une virgule suivie d'une espace.

Ici, nous avons donc commencé notre chaîne directement sans aucun texte, mais il suffirait d’initialiser la valeur de a avec une valeur pour la faire apparaître directement dans la chaîne :

=SCAN("Liste des mois :  ";A9:A20;LAMBDA(a;b;a&GAUCHE(MAJUSCULE(b);3)&", "))