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.

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)

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():

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

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

Et voici mon résultat

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)

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

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

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)

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():

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.

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

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.