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:

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:

• 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:

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;E2–D2-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")

 

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.

À 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).

 

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…