XL 2016 chronologie TDC avec formule

[Donsimend]

Bonjour à tous
je souhaite créer un champ calculé dans mon TDC me donnant l'année correspondant au début d'un numéro de serie (champ COMPT_NUMERO est une chaine standard puisqu'elle contient des lettres)
ex : 09KA457 ---> 2009
mais 93LA5845 --> 1993

le but étant ensuite d’insérer une chronologie à mon TDC
voici la formule que j'essayais:
=SI(CODE(GAUCHE(COMPT_NUMERO ))<50;SI(VALEURNOMBRE(GAUCHE(COMPT_NUMERO;2))>VALEURNOMBRE(DROITE(AUJOURDHUI();2));CONCATENER("19";GAUCHE(COMPT_NUMERO;2));CONCATENER("20";GAUCHE(COMPT_NUMERO;2)));"9999")

Mais le champ calculé n'accepte pas AUJOURDHUI donc j'essaie avec
=SI(CODE(GAUCHE(COMPT_NUMERO ))<50;SI(VALEURNOMBRE(GAUCHE(COMPT_NUMERO;2))>18;CONCATENER("19";GAUCHE(COMPT_NUMERO;2));CONCATENER("20";GAUCHE(COMPT_NUMERO;2)));"9999")

mais le résultat est "200" pour toute les lignes

Des suggestions ?

merci

 

[kan3pz]

Salut,
Serait-il possible d'avoir un fichier exemple sans données confidentielle ?
Merci,

 

[chris]

Bonjour

Par conception un champ calculé d'un TCD ne calcule que sur la somme d'un champ numérique.

Donc il ne peut faire ce que tu demandes.

Tu dois donc le faire en amont dans les données sources, ou bien en manipulant tes données sources en passant par PowerPivot (si tu as le version pro plus d'Excel) ou par PowerQuery.

 

[Donsimend]

Bonjour

Par conception un champ calculé d'un TCD ne calcule que sur la somme d'un champ numérique.

Donc il ne peut faire ce que tu demandes.

Tu dois donc le faire en amont dans les données sources, ou bien en manipulant tes données sources en passant par PowerPivot (si tu as le version pro plus d'Excel) ou par PowerQuery.

Merci Chris,
Du coup autre problème,
Dans les données sources, La formule fonctionne bien. Elle me donne 2006 1997 etc. Mais ça suffit pas pour que ça soit une date utilisable comme chronologie. c'est surtout ça mon problème : que on champ soit compatible avec la fonction Insérer une chronologie. Faut -il des valeurs brutes (sans formules) ? si oui comment automatiser le remplacement de ces formules par leur valeurs ? Sachant que j'ai 28000 lignes et une connexion ODBC..

 

[chris]

Bonjour

Mets le 1/1/ de l'année : genre =DATE(texte(GAUCHE(COMPT_NUMERO;2)+2000;1;1)

Edit : voir aussi PowerPivot si tu as la version pro plus...

 

[Donsimend]

Bonjour

Mets le 1/1/ de l'année : genre =DATE(texte(GAUCHE(COMPT_NUMERO;2)+2000;1;1)

Edit : voir aussi PowerPivot si tu as la version pro plus...

Pas mal, ça me donne des dates la plupart du temps
=SI(CODE(GAUCHE([@[COMPT_NUMERO]];2))<50;SI(VALEURNOMBRE(GAUCHE([@[COMPT_NUMERO]];2))>VALEURNOMBRE(DROITE(ANNEE(AUJOURDHUI());2));DATE(GAUCHE([@[COMPT_NUMERO]];2)+1900;6;10);DATE(GAUCHE([@[COMPT_NUMERO]];2)+2000;6;10));DATE(9999;12;31))

Mais j'ai numero de serie qui n'ont pas la forme 17KA584298
pq la formule donne 31/12/9999 pour un num serie 92081074 (uniquement chiffre)
et donne #valeur pour 1AB

comme tous les résultats ne sont pas des dates cest pas compatible
en bref, je veux calculer l'annee quand COMPT_NUM commence par 2 chiffres peu importe ce qu'il y a derriere
et afficher 31/12/9999 dans tous les autres cas.
je pense que mon premier si() peut etre améliorer

 

[Donsimend]

cette formule me donne que des dates. dans le tableau de données, le filtre les regroupe par année et mois
=SI(ESTNUM(VALEURNOMBRE(GAUCHE([@[COMPT_NUMERO]];2)));SI(VALEURNOMBRE(GAUCHE([@[COMPT_NUMERO]];2))>VALEURNOMBRE(DROITE(ANNEE(AUJOURDHUI());2));DATE(GAUCHE([@[COMPT_NUMERO]];2)+1900;6;10);DATE(GAUCHE([@[COMPT_NUMERO]];2)+2000;6;10));DATE(1920;12;31))

Mais dans le TDC je ne peux pas grouper le champ par année ni insérer une chronologie
"Microsoft excel ne peut pas créer de chronologie car il ne peut pas récupérer les informations relatives au champ de date nécessaires"