Formules avancées dans Excel

Créer des Formules composées en Excel


Dans Excel, vous avez plusieurs centaines de fonctions. Mais parfois, il est nécessaire d’utiliser une combinaison de plusieurs de ces fonctions pour obtenir la formule qui vous donnera le résultat souhaité. Ces formules composées en Excel peuvent être assez complexes et leur création (et lisibilité) peut être intimidante. Nombreux utilisateurs préfèrent alors laisser tomber.

La pratique courante… mais inefficace

La plupart des gens utilisent la pratique qui consiste à utiliser chaque formule séparément avec des résultats intermédiaires. Pour cela, ils ajoutent plusieurs colonnes avec une formule par colonne où la formule utilise le résultat intermédiaire de la colonne précédente.

Exemple pratique

Prenons un exemple concret. Vous avez un tableau de produits avec la référence, le prix et la quantité en stock suivant:

Formules complexes dans Excel: exemple 1

Maintenant, disons que l’on veut extraire une partie de la référence: le premier chiffre. Donc "47" de "IB-47-AB-273" et ainsi de suite. Si le nombre est supérieur à 100, le résultat à afficher sera "acier", sinon "aluminium".

Normalement, l’utilisateur aura tendance à ajouter une colonne pour chaque étape ce qui donnera probablement quelque chose comme ça:

Formules complexes dans Excel: méthode inefficace

  • L’avantage principal de cette méthode: vous obtiendrez votre résultat.
  • Les désavantages de cette méthode: besoin d’ajouter plusieurs colonnes à votre table (parfois, cela peut être 3, 4, 5 ou même plus de colonnes – imaginez alors le nombre de colonnes supplémentaires si vous devez utiliser 3 ou 4 calculs plus complexes! ) , l’impact négatif sur la lisibilité du fichier (des colonnes intermédiaires, donc "inutiles"), sa taille sur le disque et l’impact négatif sur les performances (Excel doit calculer beaucoup plus de formules).

Les formules composées en Excel: la manière efficace

Rassurez-vous, décomposer la formule complexe EST la bonne solution. L’astuce consiste en le fait de "recomposer" ensuite les différentes formules intermédiaires en une seule formule complète. Car si vous pouvez décomposer votre formule, il y a nécessairement un moyen de l’écrire "d’un coup" et obtenir le même résultat.

En reprenant l’exemple mentionné plus haut, on peut voir les étapes intermédiaires:

Formules complexes dans Excel: formules intermédiaires

Voici donc une méthode facile qui vous permettra d’utiliser des formules très complexes avec un seul calcul.

Comment faire

Et voici la démarche à suivre: une fois que vous avez les formules intermédiaires qui mènent au résultat souhaité, prenez la formule de départ et insérez la dans la suivante (sans le signe de "=") en remplaçant l’adresse de la cellule où la première formule se trouve. Dans les formules intermédiaires suivantes, vous remplacez l’adresse de la formule précédente par son contenu.

  1. vous prenez donc la formule de D2, c’est à dire TROUVE("-";A2) et dans la formule en E2, vous remplacez "D2" par cette formule. La formule dans E2 ( =TROUVE("-";A2;D2+1) ) deviendra donc =TROUVE("-";TROUVE("-";A2);TROUVE("-";A2)+1). Comme vous voyez, cette nouvelle formule ne fait plus référence qu’à la cellule A2 ce qui est le but.
  2. vous répétez le même procédé pour les formules intermédiaires suivantes…
  3. dans la formule F2, la nouvelle formule dans E2 viendra remplacer la référence "E2" et la formule dans D2 remplacera la référence "D2" ce qui donnera: =STXT(A2;TROUVE("-";A2)+1;TROUVE("-";A2;TROUVE("-";A2)+1)TROUVE("-";A2)-1) à la place de =STXT(A2;D2+1;E2D2-1)
  4. idem pour G2: =VALEURNOMBRE(STXT(A2;TROUVE("-";A2)+1;TROUVE("-";A2;TROUVE("-";A2)+1)-TROUVE("-";A2)-1)) à la place de =VALEURNOMBRE(F2)
  5. et enfin pour le H2: =SI(VALEURNOMBRE(STXT(A2;TROUVE("-";A2)+1;TROUVE("-";A2;TROUVE("-";A2)+1)-TROUVE("-";A2)-1))>100;"acier";"aluminium") à la place de =SI(G2>100;"acier";"aluminium")

 

Formules avancées: remplacement des formules intermédiaires
Formules avancées: remplacement des formules intermédiaires

Comme vous voyez, la formule devient de plus en plus compliquée MAIS elle ne fait plus référence qu’à la cellule A2 qui contient les données de départ.

Formules avancées
Formules avancées: toutes les références aux formules intermédiaires ont été remplacées par les formules intermédiaires elles-mêmes

À la fin, vous vous retrouvez avec une seule formule qui englobe toutes les étapes du calcul. Vous n’aurez donc besoin que d’une seule colonne (et vous pouvez alors supprimer les colonnes intermédiaires).

Formules avancées: résultat final - 1 seule colonne en plus
Formules avancées: résultat final – 1 seule colonne en plus

 

Le résultat final est le même et le fichier est beaucoup plus lisible car il ne contient que les colonnes nécessaires!

L’utilisation en pratique des formules composées dans Excel

La décomposition et recomposition des formules décrites plus haut peut sembler fastidieuse dans les fichiers contenant beaucoup de données. Pas de panique: ils vous suffit de le faire pour une seule ligne de votre tableau et une fois que vous avez la formule complète, vous la copiez dans les autres lignes de votre tableau!

Astuce pour la fin

  • quand vous remplacez l’adresse d’une cellule contenant une formule intermédiaire par cette même formule intermédiaire, vous verrez que tout s’est bien passé si le résultat de la formule modifié est le même avant ET après la modification.
  • à la fin, peu importe la complexité du calcul, les seules adresses de cellules utilisées dans la formule devraient/pourraient être celle qui pointent vers les données nécessaires au calcul (input)
  • faites attention aux parenthèses: si vous vous y perdez un peu, sachez qu’en cliquant dans la barre de formule, Excel colorie des couples "(" et ")" correspondant par la même couleur
  • n’oubliez pas: ne pas copier les signes "=" des fonctions temporaires. Copiez/collez les sans le "=".

Conclusion

Ceci est une technique relativement simple qui donne des résultats impressionnants. Attendez de voir la réaction de vos collègues ou de votre chef quand ils verront vos "super-formules"… Même un débutant peut faire ceci, le seul point d’attention est de rester bien attentif lors de la "recomposition" de la formule finale…



1 réflexion sur “Créer des Formules composées en Excel”

  1. Bonjour

    Merci pour votre blog sur le calcul de mode.
    Je suis un lecteur régulier et j’apprécie la qualité des articles.
    J’ai suivi cette formation pour excel elle est génial http://bit.ly/SuperFormationExcel
    Je me permet de la poster ici car elle a aidé beaucoup de personne.

    Merci et à bientôt sur d’autre articles

Laisser un commentaire

Votre adresse e-mail ne sera pas publiée. Les champs obligatoires sont indiqués avec *

Ce site utilise Akismet pour réduire les indésirables. En savoir plus sur comment les données de vos commentaires sont utilisées.