Tutoriels > Tutoriels & Astuces Excel > Excel Débutants et Généralités > Ecrire des formules complexes en pas à pas

Ecrire des formules complexes en pas à pas

Publié par Mth le 25-05-2015 (3214 lectures)

A moins d’avoir une maîtrise certaine des formules, il n’est pas évident d’écrire des formules compliquées à main levée d’un seul trait.

 

Que ce soit pour décrypter une formule, ou pour l’écrire, il est très souvent utile (voire indispensable ?)  de la décomposer en plusieurs morceaux, quitte à la réassembler après.

 

Un exemple avec ce tableau, j’ai dans la première colonne un libellé correspondant à une date mais qui n’est pas écrit sous forme de date, je veux obtenir une vraie date comme en colonne C.

 

Excel Ecrire des formules complexes pas à pas

 



Je sais que les 4 premiers caractères indiquent l’année, les deux suivants le mois, les deux derniers le jour.
J’ai en tête les fonctions GAUCHE() et DROITE() permettant d’extraire les premiers ou derniers caractères d’une chaîne (voir ici), la fonction STXT() permettant d’extraire des caractères au milieu d’une chaîne (voir ici) ces fonctions me permettrons de reconstituer l’année, le mois et le jour. Au final la fonction DATE() sait transformer ces informations Année mois jour en une vraie date.


Ma formule impliquant plusieurs fonctions, je vais la rédiger pas à pas dans des colonnes séparées.

Pour extraire l’année, j’utilise la formule ci-dessous qui extrait les 4 premiers caractères à gauche

=GAUCHE(A2 ;4)

 

Excel Ecrire des formules complexes


J’obtiens 2012, mais sous forme de texte (aligné à gauche), dans la colonne d’à coté je transforme ce résultat en numérique grâce à la fonction CNUM():

 

Excel écrire des formules complexes

 

J’obtiens bien une année "numérique" alignée à droite.

Je peux maintenant imbriquer les deux formules pour n’en faire qu’une

 

Excel imbriquer plusieurs fonctions


Et voici mon résultat

 

Excel imbriquer plusieurs fonctions


Les colonnes C et D ne sont pas utilisées dans la formule en colonne E, pour ne pas créer trop de colonnes intermédiaires qui rendraient mon travail un peu confus, je peux les supprimer sans risque, je ne garde de coté que ma dernière formule.


En appliquant la même démarche je vais maintenant calculer le mois, en extrayant à partir du 5ème caractère, les deux caractères correspondant au mois, grâce à la fonction STXT() que j’inscris ci-dessous en D2:

=STXT(A2 ;5 ;2)

 

Excel imbriquer plusieurs fonctions

 

Je transforme en numérique grâce à CNUM() comme ci-dessus. Pour aller plus vite, je place mon curseur juste après le signe = (égal) dans la barre de formule et j’écris CNUM( puis je place une parenthèse fermante tout à la fin, j’obtiens ma formule qui me renvoie 1:

=CNUM(STXT(A2 ;5 ;2))

 

Excel imbriquer plusieurs fonctions

 

Même chose pour le jour, en imbriquant les fonctions DROITE() et CNUM():

 

Excel imbriquer plusieurs fonctions


J’ai maintenant 3 cellules différentes me donnant l’année le mois et le jour, que je vais utiliser pour ma fonction DATE().


La syntaxe de cette fonction est DATE(Année ;mois ;jour), ce qui me donne comme formule en F2

=DATE(C2 ;D2 ;E2)

 

Excel écrire des formules complexes


J’ai bien la date désirée, je peux conserver le tableau tel quel avec mes colonnes intermédiaires, mais je vais faire en sorte de n’avoir qu’une seule formule. Je vais remplacer les arguments de ma fonction DATE() par les formules des cellules C2 D2 et E2.


Par exemple, je prends la formule de la cellule E2 sans recopier le signe = (égal) soit CNUM(DROITE(A2 ;2)), que j’inscris à la place de E2 dans ma formule, qui devient alors

=DATE(C2 ;D2 ;CNUM(DROITE(A2 ;2))

 

Je procède de même pour les deux autres arguments de ma fonction DATE():

 

Excel écrire des formules complexes

 


En ayant décomposé ma formule pas à pas en sous formules avant de l’écrire en entier, j’ai pu sans problème rédiger une formule complexe, je n’ai plus d’autre référence dans ma formule que la colonne A, je peux donc supprimer mes colonnes intermédiaires.

 

Excel Ecrire des formules complexes

 

 

Remarques:

1- en cas de doute, vous pouvez tout à fait conserver tous vos calculs intermédiaires, et simplement masquer les colonnes.
2- La démarche inverse est également souvent nécessaire, pour comprendre une formule complexe il est souvent nécessaire de la décortiquer en plusieurs sous-formules

 

 

Trait bleu

 

 

Ce tutoriel est terminé, volontairement très détaillé vous irez plus vite dans vos tableaux réels; mais il est important de retenir la démarche de décomposition des formules, elle vous aidera tout au long de votre pratique Excellienne.

 

Pour toutes vos questions ou si vous rencontrez des difficultés, n'hésitez pas à rejoindre nos Forums de Discussions !

 

Autres articles dans cette catégorie Publié le Vues
Ajouter - supprimer des lignes ou colonnes dans un Tableau 24-04-2016 1755
Sélectionner des lignes ou colonnes d'un Tableau 24-04-2016 760
Mettre en forme un Tableau 24-04-2016 1470
Créer ou supprimer un Tableau 24-04-2016 699
L'outil Tableau 24-04-2016 895
Ecrire des formules complexes en pas à pas 25-05-2015 3215
Renseigner les arguments d'une fonction 25-05-2015 2242
Premiers pas: Les opérateurs de calculs 27-08-2014 4002
Premiers pas: Ma première formule 27-08-2014 6843
Premiers pas: Ma première saisie dans Excel 27-08-2014 3506
Premiers pas: Présentation générale d'Excel 27-08-2014 3291
L'aide à la saisie des formules 27-10-2012 2288
Installer/désinstaller un complément ou macro complémentaire 03-02-2008 65233