Imbriquez des formules en fonction de vos besoins

Adaptez les formules à vos besoins

Vous savez désormais comment trouver les fonctions qui répondent à votre besoin. Malheureusement, il arrive qu’aucune d’entre elles ne réponde à votre besoin. 😕

Rassurez-vous, il y a toujours un moyen de trouver une méthode, la plus classique est de combiner les fonctions entre elles ! 😉

Téléchargez ce fichier, il contient le tableau des ventes de votre tableau de bord.

À partir de ces données, vous devez donner un avis, basé sur plusieurs critères :

• Si les ventes des 4 derniers trimestres sont supérieures aux 4 précédents, alors vous affichez “Bravo !”

• Sinon, vous afficher un tiret “-”.

Impossible de trouver dans Excel une fonction qui permette d’obtenir ce résultat, à vous de la créer ! 😅 Ici, vous devez combiner les fonctions SI() et SOMME() en suivant plusieurs étapes :

Définissez la condition

En image, la condition de la fonction SI() est celle-ci :

Sélectionnez la fonction principale

En utilisant l'icône “fx” :

• À gauche de la barre de formules, cliquez sur l’icône “fx”, qui ouvre l’assistant “Insérer une fonction” .

• Recherchez la fonction SI() dans la liste.

• Cliquez sur “OK”.

• Excel affiche la fenêtre de sélection des arguments.

Dans la zone “Test_logique”, au lieu de saisir une valeur, vous souhaitez effectuer un calcul, l’écart entre les ventes de l’année passée et celles de l‘année précédente :

• Cliquez dans la zone “Test_logique”.

• Cliquez dans la zone de nom, à gauche de la barre de formule : cette zone contient maintenant la liste des fonctions d’Excel.

Sélectionnez la fonction secondaire

C’est là que vous allez sélectionner la formule à imbriquer, ici la fonction SOMME().

• Cliquez sur “SOMME”.

• Excel vous affiche la fenêtre des arguments de la fonction SOMME().

• Sélectionnez les cellules des 4 derniers trimestres, ici G6:J6.

• Cliquez sur le texte “SI” de la barre de formule pour retourner dans la fenêtre d’arguments de la fonction SI().

Vous revenez sur la fenêtre d’arguments de la fonction SI(), qui contient maintenant la fonction SOMME() comme premier argument.

• Dans “Test_logique”, saisissez le terme “>”, afin d’effectuer la comparaison entre la dernière année et l’année précédente.

• Cliquez à nouveau dans la zone de nom, afin d’ajouter la deuxième somme, celle de l’année précédente.

• Sélectionnez maintenant la plage de cellules C6:F6.

• Cliquez sur le texte “SI” de la barre de formule, afin de revenir sur la fenêtre d‘arguments de la fonction SI().

• Dans la zone “Valeur_si_vrai”, saisissez “Bravo !”

• Dans la zone “Valeur_si_faux”, saisissez “-”.

• Cliquez sur OK.

Corrigez les formules en erreur

Identifiez les différents types d’erreur

Parfois, lorsque vous utilisez une fonction, Excel renvoie une erreur.

Il existe plusieurs types d’erreurs, que vous reconnaitrez aisément avec leur nom curieux. En voici les principales valeurs : #NOM?, #REF!, #VALEUR!, #N/A ou encore #DIV/0!

Voyons comment corriger ces types d’erreurs, en affichant les 5 formules utilisées :

L’erreur “#NOM?”

Cette erreur est due à une mauvaise écriture de la fonction RECHERCHEV(). Ici, la lettre V a été doublée.

• Dans ce cas, corrigez la formule.

L’erreur “#REF!”

Ce message indique que la formule fait appel à une référence de cellule qu’elle ne trouve pas. Dans notre exemple, le 3e argument de la fonction fait appel à la colonne numéro 3, qui n’existe pas dans la plage de données “$A$4:$B$22”.

• Dans ce cas, vérifiez les références de cellules dans votre formule.

L’erreur “#DIV/0!”

L’erreur vous indique qu’Excel calcule une division par zéro, ce qui est impossible.

• Dans ce cas, vérifiez les divisions de votre formule, et cherchez les zéros.

L’erreur “#N/A”

Elle apparaît quand votre formule ne trouve pas ce qu’elle cherche. C’est un cas classique avec la fonction RECHERCHEV(). Ici, le vêtement recherché, “Jupee”, contient une erreur de syntaxe, le “e” est doublé. Excel ne peut donc pas trouver cette valeur.

• Dans ce cas, vérifiez dans votre formule la donnée qui est recherchée.

L’erreur “#VALEUR!”

Cette erreur peut être plus complexe, car il s’agit d’une erreur générale, non spécifique à un problème particulier. Dans notre cas, c’est le 3e argument de la fonction RECHERCHEV(), “-2”, qui cause cette erreur. Ici on attend une valeur positive, et pas négative.

• Dans ce cas, vérifiez chaque argument de votre formule.

Trouvez les cellules en erreur

La première étape est de trouver les cellules en erreur, et de les comprendre.

Pour cela Excel vous aide, grâce à l'icône , de l’onglet “Formules”.

• Sélectionnez une feuille contenant des formules.

• Cliquez sur ce bouton.

• Excel vous aide à analyser les erreurs, en les trouvant et en donnant une explication.

• Cliquez sur le bouton Suivant pour passer à l’erreur suivante.

Anticipez les erreurs

Parfois, vous pouvez obtenir une erreur dans une formule, et cela est tout à fait normal, par exemple :

• en cas de valeurs à zéro possibles (ventes, évolutions, etc.) ;

• en cas de fonction RECHERCHEV() ne trouvant pas de correspondance.

Dans notre exemple, vous souhaitez calculer une évolution des ventes entre le dernier trimestre et le trimestre de l 'année précédente.

Vous devez prévoir le cas d’un trimestre sans ventes, qui donnerait une erreur “#DIV/0!”.

Vous devez gérer le cas d’une formule en erreur, en utilisant la fonction SIERREUR() :

• En français, la formule est : “Si l’évolution est une erreur, alors j’affiche un tiret, sinon j’affiche l’évolution”.

• La syntaxe de base de la formule d’évolution est “J7/F7-1”.

• Affichez un tiret en cas de résultat en erreur : =SIERREUR(J7/F7-1;”-”).

À vous de jouer !

Téléchargez ce fichier et réalisez les opérations suivantes :

• Renseignez la colonne “Avis” avec le texte “Bien joué !” quand le maximum des ventes des 6 derniers trimestres dépasse 5,2. Sinon, affichez “-”. Pour ce faire, utilisez la fonction SI() imbriquée avec la fonction MAX(), dans les cellules jaunes.

• Corrigez les 2 cellules en erreur (cellules avec le fond orange). La première doit donner 119 % et la deuxième 139 %.

• Modifiez la cellule en erreur (cellule avec le fond bleu), afin qu’elle renvoie la valeur “-” en cas d’erreur. Indice : fonction SIERREUR().

Corrigé

Vous pouvez consulter ce corrigé et regarder la vidéo ci-dessous pour vérifier votre travail.

En résumé

• Vous pouvez imbriquer des fonctions entre elles, en remplaçant un argument par une fonction. Cela vous permet de les utiliser en fonction de vos besoins.

• N’oubliez pas de vérifier les formules de votre fichier, et de corriger les erreurs.

Vos tableaux sont maintenant remplis de formules sans erreurs, et le format des cellules s’adapte aux valeurs. Il ne vous reste plus qu’à passer à la partie graphique ! 😊