XL 2010 aide sur la fonction decaler

fb62840

XLDnaute Impliqué
Bonjour à toutes et tous,

j'ai besoin de votre aide pour utiliser plus efficacement une formule utilisant la fonction decaler.

Une info présente dans un classeur externe est récupérée grâce à cette fonction :

Dans la Cellule B2 Sur la feuille Semaine 1 :
=X:UNITES\UNITE 1\Lignes\Ligne 1\Suivi production\2016\[2016.xls]MRH22'!$H$8

Sur la feuille semaine 2 :
=DECALER('\X:UNITES\UNITE 1\Lignes\Ligne 1\Suivi production\2016\[2016.xls]MRH22'!$H$8;6;0)

J'ai un travail équivalent à faire pour toutes les semaines de l'année...
Je préfèrerai alors utiliser le contenu de la cellule B2 pour obtenir les données sur les feuilles de Semaine 2 à Semaine 52

Comment pourrais-je alors rédiger la formule à saisir en B2 sur la feuille Semaine 2 pour obtenir
le même résultat qu'avec la fonction décaler mais en utilisant la référence à la cellule B2 sur la feuille Semaine 1 à la place

La "clé" pour obtenir les données qui conviennent est d'incrémenter selon un rythme croissant de 6 (du moins pour Semaine 1, Semaine 2, Semaine 3, Semaine 4 - Mais à chaque Semaine 5 (multiple de 5) ce n'est plus 6 lignes qu'il faut ajouter mais 7, ensuite on revient au rythme de + 6 lignes de Semaine 6, Semaine 7, Semaine 8, Semaine 9 (7ème ligne pour Semaine 10)... etc

Merci pour votre aide ça m'aidera considèrablement.
 

fb62840

XLDnaute Impliqué
Ok, merci,

je suis sur une piste, avec cette formule je peux obtenir un nom d'onglet.
=STXT(CELLULE("filename");TROUVE("]";CELLULE("filename");1)+1;NBCAR(CELLULE("filename")))

Peut être pourrait-on l'utiliser pour faire quelque chose du genre :

"chercher" dans le classeur 2016 sur la feuille MRH22 si on trouve :
=STXT(CELLULE("filename");TROUVE("]";CELLULE("filename");1)+1;NBCAR(CELLULE("filename")))
avec pour valeur de =stxt... l'expression Semaine 2

et, si on trouve alors on inscrit comme résultat, la valeur de la cellule dans la colonne adjacente, même ligne que là où l'on trouve Semaine 2

Ainsi je pourrais écrire la même formule sur toutes les cellules de calcul puisque le nom d'onglet Semaine 2, Semaine 3, Semaine x serait utilisé comme clé de recherche.

Merci beaucoup.
 

CISCO

XLDnaute Barbatruc
Bonsoir

Effectivement, tu avais une partie de la réponse.

Certaines fois, il faut imposer un décalage de 6 lignes et d'autre fois, de 7 lignes. Pour ne pas avoir de difficulté avec cela, on peut ne pas faire avec un DECALER, mais avec un INDEX(....; EQUIV(....)).
Code:
INDEX([2016.xlsx]MRH22!$B$1:$B$314;EQUIV(STXT(CELLULE("filename");TROUVE("]";CELLULE("filename");1)+1;NBCAR(CELLULE("filename")));[2016.xlsx]MRH22!$A$1:$A$314;0))

@ plus
 

fb62840

XLDnaute Impliqué
Bonsoir

Effectivement, tu avais une partie de la réponse.

Certaines fois, il faut imposer un décalage de 6 lignes et d'autre fois, de 7 lignes. Pour ne pas avoir de difficulté avec cela, on peut ne pas faire avec un DECALER, mais avec un INDEX(....; EQUIV(....)).
Code:
INDEX([2016.xlsx]MRH22!$B$1:$B$314;EQUIV(STXT(CELLULE("filename");TROUVE("]";CELLULE("filename");1)+1;NBCAR(CELLULE("filename")));[2016.xlsx]MRH22!$A$1:$A$314;0))

@ plus
Bonjour,

Et, si au lieu de devoir récupérer la valeur sur la même ligne, je dois la récupérer sur la ligne juste au dessus ? Y-aurait-il une possibilité ?
 

fb62840

XLDnaute Impliqué
Bonjour

A la fin de la partie correspondant au EQUIV(.....;0), tu mets -1. Donc ici, cela donne
Code:
;0)-1)
. C'est tout, il ni a rien d'autre à faire.

@ plus


Bonjour,

J'ai un souci...
C'est étrange mais en copiant la formule sur les différents onglets du classeur quand j'actualise le calcul sur l'une des feuilles, toutes les autres formules identiques présentes sur les autres feuilles me renvoie les valeurs de la page à partir de laquelle j'ai actualisé

Donc si j'actualise les calculs sur la feuille Semaine 1 le résultat de la formule sur les feuilles Semaine 2, Semaine 3, Semaine 4 etc seront les mêmes (en apparence) que sur la feuille Semaine 1...

C'est incompréhensible pour moi car la formule fait bien référence au nom donné à l'onglet à savoir Semaine 1, Semaine 2, Semaine 3 etc...

Une idée ?
 

CISCO

XLDnaute Barbatruc
Bonjour

La fonction CELLULE comprend deux paramètres, le premier indique le type d'information que tu veux obtenir, filename dans ton cas, le second, la référence de la cellule sur laquelle tu veux une information. Le problème que tu signales vient apparemment du fait que tu as précisé uniquement le premier. Si tu mets CELLULE("filename";$A$1), cela semble fonctionner correctement. Cela donne donc
Code:
INDEX([2016.xlsx]MRH22!$B$1:$B$314;EQUIV(STXT(CELLULE("filename";$A$1);TROUVE("]";CELLULE("filename";$A$1);1)+1;NBCAR(CELLULE("filename";$A$1)));[2016.xlsx]MRH22!$A$1:$A$314;0))

@ plus

P.S 1 : Bien sûr, on peut faire avec une autre cellule que $A$1, du moment qu'on indique ce second paramètre.

P.S 2 : Ici, on ne peut pas donner un nom dans le gestionnaire de noms, du style
nomfeuille = STXT(CELLULE("filename";$A$1);TROUVE("]";CELLULE("filename";$A$1);1)+1;NBCAR(CELLULE("filename";$A$1))),
car Excel mettra automatiquement le nom de la feuille (en cours lors de la définition) devant $A$1, et cela donnera par exemple CELLULE("filename";Semaine 1!$A$1) (si on est sur la feuille Semaine 1 lors de la définition), et on obtiendra sur toutes les feuilles le résultat correspondant à la semaine 1, et pas ceux correspondant aux autres semaines.
On ne peut donc pas faire avec
Code:
INDEX([2016.xlsx]MRH22!$B$1:$B$314;EQUIV(nomfeuille;[2016.xlsx]MRH22!$A$1:$A$314;0))
 
Dernière édition:

fb62840

XLDnaute Impliqué
Bravo ! cela solutionne mon problème, je vous avoue que je me disais bien que si j'obtenais la même chose sur toutes feuilles c'est que la valeur renvoyée par STXT devait bien être toujours la même que le nom de l'onglet sur lequel je lançais les calculs.

Merci énormément
 

Discussions similaires