Rappel de données antérieures de 12 mois

[Philgood.001]

Bonjour,

Voici ma problématique :

J'ai un tableau de chiffres de 48 onglets (2016 + 2017) nommés comme suit:

01.16 Tab - 01.16 Gra - 02.16 Tab - 02.16 Gra - 03.16 Tab - 03.16 Gra - ...

Il y a chaque fois 2 onglets/mois :
- 1 onglet pour le tableau de données
- 1 onglet pour les différents graphiques illustrant le tableau correspondant (de l'onglet précédent)

Ça continue sur 2017 et c'est amené à faire de même pour les années à suivre, tout ça sur le même classeur.

Le but du jeu est de faire par exemple un rappel du contenu de la cellule D3 de l'onglet '01.16 Tab' dans la cellule F3 de l'onglet '01.17 Tab' (tableau en PJ), mais en utilisant une formule générique, donc non nominative, que je puisse coller dans tous mes onglets 2017 et +, sans la modifier.

J'ai bien trouvé une formule proposée par Jacques Boisgontier (http://boisgontierjacques.free.fr/pages_site/indirect.htm#ongnongene) que je voudrais adapter à mon fichier, mais elle impose de créer un champ en prélude.

Or je voudrais le même principe mais en plus simple, à savoir sans ajouter de champ, uniquement avec une formule de cellule, sans macro bien sûr, ni passer par un onglet "Récap" couramment proposé pour l'utilisation de la fonction INDIRECT dans ce type de problématique.

Merci pour votre aide.

Philgood.001

 

[CISCO]

Bonjour

Tu peux faire dans 01.17 Tab!F3 (ou dans 02.17 Tab!F3, 03.17 Tab!F3 et ainsi de suite) avec

INDIRECT("'"&DROITE(SUBSTITUE(CELLULE("filename";$A$1);GAUCHE(DROITE(CELLULE("filename";$A$1);6);2);GAUCHE(DROITE(CELLULE("filename";$A$1);6);2)-1);9)&"'!D"&LIGNE())

formule à tirer ensuite vers le bas.

@ plus

P.S : Il faut que les noms des onglets soient tous construits de la même manière, avec 9 caractères, sous la forme mm.aa @@@. Peu importe le contenu de mm et de @@@. Par contre, tout est fait par rapport à aa. Les cas mmm.aa @@@ ou mm.aa @@@@ ne donneront pas les résultats désirés.

 

[Philgood.001]

Bonjour Cisco,

Je suis sincèrement désolé de revenir vers toi aussi tard, j'ai été très occupé ces derniers temps, et je dois reconnaître que plusieurs posts que j'avais laissés sur ce site et d'autres sont restés à l'abandon...
C'est pourquoi j'ai décidé de reprendre tous ces posts et de les solder.
J'en profite donc, même avec retard, pour te remercier de ta réponse qui fut bien plus rapide que la mienne ce soir.

Je l'ai donc testée et elle fonctionne. Donc ça, c'est une excellente chose.
Par contre, pourrais-tu m'expliquer en détail son contenu de telle façon que je la modifie à ma convenance lors d'une prochaine utilisation dans une autre configuration.
En effet, dans cette configuration, on demande d'aller chercher la valeur d'une autre cellule 24 onglets plus avant. Et là il n'y a aucun "24" qui apparaît dans ta formule, donc difficile à déchiffrer...
Il en est de même pour le reste de la formule.

Merci encore à toi pour ta participation.

A très bientôt.

Philgood.001.

 

[CISCO]

Bonjour

Si tu veux aller chercher 24 mois avant, donc deux années avant, il suffit de remplacer dans la formule du post 2, le -1 par -2. Ce qui donne GAUCHE(DROITE(CELLULE("filename";$A$1);6);2)-2 au lieu de GAUCHE(DROITE(CELLULE("filename";$A$1);6);2)-1.

Pour aller plus loin, je t'explique la formule :
INDIRECT("'"&DROITE(SUBSTITUE(CELLULE("filename";$A$1);GAUCHE(DROITE(CELLULE("filename";$A$1);6);2);GAUCHE(DROITE(CELLULE("filename";$A$1);6);2)-1);9)&"'!D"&LIGNE())

CELLULE("filename";$A$1) donne le nom complet de l'onglet contenant la cellule $A$1. Par nom complet, j'entends chemin d'accès + nom du fichier + nom de l'onglet.
Donc à la fin de ce nom, tu as quelque chose du genre 17 Tab. Tu veux aller chercher quelque chose dans un onglet ayant le même nom mais finissant en 16 Tab. Donc on va passer par un SUBSTITUE(nom complet;"17";"16").

DROITE(CELLULE("filename";$A$1);6) renvoie les 6 derniers caractères de ce nom.
GAUCHE(DROITE(CELLULE("filename";$A$1);6);2) renvoie les deux caractères de gauche de ce dernier paquet, soit "17".

La partie en vert fait la même chose, mais y soustrait 1, GAUCHE(DROITE(CELLULE("filename";$A$1);6);2)-1 ce qui donne "17" - 1 = 16.
(Dans ta nouvelle formule, c'est là que tu dois mettre -2 au lieu du -1 final).

On n'a pas besoin de l'adresse complète, juste du nom de l'onglet et de la référence de la cellule. Ce qu'on obtient à l'aide du DROITE(........;9)&"'!D"&LIGNE().

En concaténant avec des &, on obtient du texte, pas une référence. Donc, on passe par INDIRECT("'"&DROITE(........;9)&"'!D"&LIGNE()) pour qu'Excel comprenne qu'il s'agit d'une référence, les "'" étant nécessaires car il y a des espaces dans le nom de l'onglet utilisé.

@ plus

P.S : Pour voir tous ces calculs intermédiaires se faire au fur et à mesure, sélectionner la formule, puis cliquer sur l'onglet Formules, puis sur Evaluation de formule.

 

[Philgood.001]

Bonjour Cisco,

Alors tout d'abord un grand merci pour tes explications hyper détaillées et très précises, exactement ce dont a besoin un noob comme moi!

Cependant j'ai plusieurs questions :

1. J'ai tout compris, également grâce à la fonction "Évaluation de formule" que j'ai découvert grâce à toi.
Sauf le chiffre 9 -> ...;9)&"'!D"&LIGNE()). Je n'arrive pas à comprendre à quoi il sert.

2. Par contre en ce qui concerne le décalage de 24 onglets n'a aucune incidence avec ta formule.
En fait on s'est mal compris. Quand je parle de 24 onglets en arrière, tu comprends 24 mois en arrière.
Or il n'en est rien. Il faut bien le "-1" et non pas "-2", car comme tu l'as si bien expliqué, le "-1"
correspond à "2017-1".
Or qu'il y ait 12, 24, 36 ou 72 onglets entre 01.2016 et 01.2017, Excel va juste chercher l'onglet
correspondant à gauche à 01.2017-1(an). Donc c'est la formule initiale avec -1 qui est la bonne.

3. Pour reprendre ce que je viens de dire plus haut, on s'est mal compris sur un détail.
Ta formule est parfaite, fonctionne très bien, mais uniquement dans ce contexte-ci, avec des onglets
datés.
Or je recherche une formule générique quelque soit le contenu des onglets. J'aurais pu laisser par
exemple l'intitulé d'origine des onglets attribué automatiquement par Excel (Feuil1, Feuil2, ...) ou bien
un autre type d'intitulé composé uniquement de texte, sans numérotation, ni date, ni aucune
corrélation logique entre chaque intitulé qui puisse être utilisé dans une formule.
Une formule qui ressemblerait schématiquement à :
"Va me chercher le contenu de la cellule de telle ligne et telle colonne 12 onglets à gauche
(ou 24 onglets à gauche...)"
Et là ce serait le top, car la formule s'appliquerait du coup à toutes les situations, il ne suffirait que
de celle-ci pour remplacer toutes les autres trop sélectives en terme de contexte.
Vu ton niveau, je suis sûr que tu as ça dans le fonds de ta besace!

En attendant de te lire en retour, un grand chapeau pour cette formule et la qualité de tes explications.

A bientôt.

Philgood.001.

 

[CISCO]

Bonjour


Sujet intéressant. Va falloir que je fouille dans mes archives... Ce soir peut être.

@ plus

 

[CISCO]

Bonsoir

1)

Bonjour Cisco,
....
Cependant j'ai plusieurs questions :
1. J'ai tout compris, également grâce à la fonction "Évaluation de formule" que j'ai découvert grâce à toi.
Sauf le chiffre 9 -> ...;9)&"'!D"&LIGNE()). Je n'arrive pas à comprendre à quoi il sert.
...
A bientôt.
Philgood.001.

Alors, pour ce qui est du 9.
En utilisant l'évaluation de formule, à un moment, tu obtiens l'état intermédiaire suivant

et comme on ne veut que les 9 derniers caractères de ce nom complet (l'espace entre le 6 et le T compte aussi), 01.16 Tab, on fait avec DROITE(...;9).

2) Pour ce qui est d'aller rechercher une information 24 onglets avant, je ne vois qu'une possibilité avec des formules
a) lister quelque part tous les noms des onglets.
On peut le faire de plusieurs manières, comme décrit là.
Dans le fichier en pièce jointe, c'est fait avec la fonction LIRE.CELLULE(1) (fonction XL4, une méthode utilisée avant la création du langage VBA, jusque vers 1992). Cette fonction ne peut pas être utilisée directement sur la feuille, mais uniquement dans un nom défini dans le gestionnaire de noms, ici NO.
Ce nom NO donne tous les noms complets correspondant à chaque onglet. Pour avoir le premier, on peut faire avec INDEX(NO;1), pour le second, avec INDEX(NO;2), et ainsi de suite.
Pour n'avoir que la partie finale qui nous intéresse, après le ],on peut faire avec

SIERREUR(DROITE(INDEX(NO;LIGNE());NBCAR(INDEX(NO;LIGNE()))-CHERCHE("]";INDEX(NO;LIGNE())));"")

J'ai mis cette formule dans la cellule ftre 45!F1, formule que l'on peut tirer vers le bas, assez longtemps pour avoir tous les noms. Cela donne la liste listeonglets définie dans le gestionnaire de noms sous forme d'une plage dynamique (pour que sa hauteur s'adapte automatiquement aux nombres d'onglets).

b) Utiliser une formule donnant la position du nom de l'onglet en cours dans cette liste, ce qui est fait avec

EQUIV(DROITE(nomonglet;NBCAR(nomonglet)-CHERCHE("]";nomonglet));listeonglets;0)

Dans ftre 45!D3, j'ai mis finalement la formule

INDIRECT("'"&INDEX(listeonglets;EQUIV(DROITE(nomonglet;NBCAR(nomonglet)-CHERCHE("]";nomonglet));listeonglets;0)-4)&"'!D"&LIGNE())

qui va chercher la valeur contenue dans le D3, placé 4 onglets avant, à cause du -4)&"'!D"&LIGNE() à la fin. Bien sûr, on peut tirer cette formule vers le bas. Le nom nomonglet, défini dans le gestionnaire de noms, ramène le nom complet de l'onglet en cours.

Ne te reste plus qu'à adapter tout cela à ton fichier réel, en remplaçant le -4 dans cette dernière formule par -24.

@ plus

 

[Philgood.001]

Bonjour Cisco,

Bon finalement je cherchais à simplifier une méthode que j'avais récupérée chez Jacques Boisgontier : http://boisgontierjacques.free.fr/pages_site/indirect.htm#ongnongene comme indiqué dans le post 1, mais je me rends compte que les choses se compliquent.
En fait, ce que tu me proposes (et qui fonctionne), correspond à sa méthode visiblement, sauf que toi tu listes les onglets, lui fait une lecture du classeur, ce qui revient au même.
Lui aussi passe par 2 étapes :
- Lecture du classeur en passant par un nom de champ (bizarre???...)
- Aller chercher tel cellule située à x onglets en arrière

Le but de ma question initiale était de voir si l'on pouvait concaténer les formules de lecture (pour l'exemple de J.B. ou la tienne) et de recherche.
A défaut, je me contenterai de ce que tu m'as donné.

En tous les cas, merci pour le temps passé et la qualité de tes formules et des explications qui vont avec.

Si je trouve ailleurs, je partagerai.

Bonne journée.

A bientôt.

Philgood.001.

 

[CISCO]

Bonjour

On peut faire sans afficher la liste des noms des onglets (mais en demandant à Excel de la déterminer), tout en utilisant des formules similaires. Cf. en pièce jointe, sans oublier de définir les noms dans le gestionnaire de noms.

@ plus