XL 2013 Automatiser le décallage de cellules dans une formule

[sr94]

Bonjour

J'ai créé un fichier afin d'avoir une planification des menus en fonction de menus récurrents.
Le principe : je récupère le numéro de la semaine et en fonction de ce numéro j'ai un numéro de menu récurrent sur une feuille qui fait la correspondance entre les 2, ensuite je vais chercher le plat qui correspond pour chaque numéro de menu (1 2 3 4 5 ) et la cellule qui correspond.

Actuellement ma formule et la suivante mais doit être décalée pour chaque jour :

=SI(RECHERCHEV((RECHERCHEV($A$9;semaines!$B:$D;3));'Menu récurrent'!$A$4:$O$8;2;FAUX)="";"";RECHERCHEV((RECHERCHEV($A$9;semaines!$B:$D;3));'Menu récurrent'!$A$4:$O$8;2;FAUX))

J'aimerais en fait avoir une formule unique pour tout la tableau, est ce possible ? Ce serait plus pratique en cas d'erreur de manip.

Je mets le fichier exemple en pièce jointe

Merci !

 

[CISCO]

Re : Automatiser le décallage de cellules dans une formule

Bonjour

Si j'ai bien compris, tu peux remplacer le 2 de RECHERCHEV((RECHERCHEV($A$9;semaines!$B:$D;3));'Menu récurrent'!$A$4:$O$8;2;FAUX) par COLONNES($A:B).

@ plus

P.S : D'ailleurs, si tu utilises une version d'Excel >= à 2010, tu peux simplifier ta formule sous la forme

SIERREUR(RECHERCHEV((RECHERCHEV($A9;semaines!$B:$D;3;FAUX));'Menu récurrent'!$A$4:$O$8;COLONNES($A:B);FAUX);"")

en utilisant le format personnalisé standart;; pour faire disparaître les 0.

 

[sr94]

Re : Automatiser le décallage de cellules dans une formule

Il y a 2 décallages en fait
Exemple de ma formule en E10 :

=SI(RECHERCHEV((RECHERCHEV($A10;semaines!$B:$D;3));'Menu récurrent'!$A$4:$O$8;3;FAUX)="";"";RECHERCHEV((RECHERCHEV($A10;semaines!$B:$D;3));'Menu récurrent'!$A$4:$O$8;3;FAUX))

et en I11 par exemple :

=SI(RECHERCHEV((RECHERCHEV($A11;semaines!$B:$D;3));'Menu récurrent'!$A$4:$O$8;7;FAUX)="";"";RECHERCHEV((RECHERCHEV($A11;semaines!$B:$D;3));'Menu récurrent'!$A$4:$O$8;7;FAUX))

LE 2 devient 3 puis 4 etc... suivant la colonne
le $A10 devient $A11 etc ... suivant la ligne

J'utilise excel 2013

 

[sr94]

Re : Automatiser le décallage de cellules dans une formule

Ca marche bien avec la formule suivante et la recopie vers la droite et le bas est ok :

=SIERREUR(RECHERCHEV((RECHERCHEV($A10;semaines!$B:$D;3));'Menu récurrent'!$A$4:$O$8;COLONNES($A:B);FAUX);"")

Je ne comprends pas toute la formule mais ça marche !

Je m'étais coincée toute seule avec le $A$10...

Est ce possible d'aller plus loin et de plus avoir les références à des cellules précises, ce qui ferait qu'on collant la formule dans n'importe quelle cellule ça marcherait ?

 

[CISCO]

Re : Automatiser le décallage de cellules dans une formule

Bonjour

Il y a quelque chose que je ne dois pas comprendre.

Il y a 2 décallages en fait
...
Le 2 devient 3 puis 4 etc... suivant la colonne
J'utilise excel 2013

Ma proposition automatise ce changement

le $A10 devient $A11 etc ... suivant la ligne

Comme tu n'as pas mis de $ devant le 10, cela devient automatiquement un 11 lorsque tu tires (=copies-colles) cette formule vers le bas.

Essayes avec ma proposition, donnée dans le PS, en D9, et tu verras le résultat.

@ plus

 

[sr94]

Re : Automatiser le décallage de cellules dans une formule

oui oui c'est bon mais en fait je voulais savoir si c'est possible d'avoir une formule "générique" qui sans la recopier serait fonctionnelle dans n'importe quelle cellule ? Mais oui sinon ça marche déjà beaucoup plus pratique !

 

[Victor21]

Re : Automatiser le décallage de cellules dans une formule

Bonjour.

Remplacez en D9 :

$A$4:$O$8;2;FAUX)

par

$A$4:$O$8;colonnes($a:b);FAUX)

Et $a$9 par $a9

 

[sr94]

Re : Automatiser le décallage de cellules dans une formule

par exemple dans la formule

J'aimerais remplacer $A9 par "$A"&Ligne() mais ça ne marche pas j'ai un résultat vide
peut être aussi remplacer le COLONNES($A:B) par le "numéro de la colonne actuel" - 2

ça permettrait de pouvoir coller la formule n'importe où

mais je ne sais pas du tout comment faire

 

[Victor21]

Re : Automatiser le décallage de cellules dans une formule

Re,

Avez-vous pris la peine de tester la proposition de 11:56, complétée à 11:59 ?

=$a9 remplacé par =INDIRECT("$a"&LIGNE()) sur la ligne 9 renverra la même chose. Toutes deux peuvent être tirées vers le bas.
=colonne($a:b) et colonne()-2 en colonne D renverra la même chose. Toutes deux peuvent être tirées vers la droite.

 

[sr94]

Re : Automatiser le décallage de cellules dans une formule

oui j'ai testé j'ai dit plusieurs fois que c'était bon ...

=INDIRECT("$a"&LIGNE()) c'est ce que je cherchais merci ! et colonne ()-2 aussi !

ainsi où que je place ma formule elle sera fonctionnelle (les résultats de la formule vont être parfois modifiés par autre chose d'où ma préférence d'utiliser une formule générique)

ce qui donne :

=SIERREUR(RECHERCHEV((RECHERCHEV(INDIRECT("$a"&LIGNE());semaines!$B:$D;3));'Menu récurrent'!$A$4:$O$8;COLONNE()-2;FAUX);"")

Merci beaucoup !

 

[CISCO]

Re : Automatiser le décallage de cellules dans une formule

Bonjour

Pour le fun, une possibilité complètement différente en pièce jointe.

Il faut remplir D9 et E9, tirer la formule (en rouge) en F9 vers la droite, la formule en D10 (en bleue) vers la droite et vers le bas.

@ plus

 

[CISCO]

Re : Automatiser le décallage de cellules dans une formule

Bonjour

par exemple dans la formule

J'aimerais remplacer $A9 par "$A"&Ligne() mais ça ne marche pas j'ai un résultat vide
peut être aussi remplacer le COLONNES($A:B) par le "numéro de la colonne actuel" - 2

ça permettrait de pouvoir coller la formule n'importe où

mais je ne sais pas du tout comment faire

Je n'en vois pas l'intérêt. On peut très bien coller la formule que j'ai proposée dans mon premier post n'importe où dans les colonnes D à Q, elle s'adapte automatiquement...

@ plus

 

[sr94]

Re : Automatiser le décallage de cellules dans une formule

oui si on la recopie de gauche à droite et de bas en haut, mais si on recopie dans la celulle J13 par exemple directement il faudra quand même l'adapter, c'est une question de choix et de point de vue personnel, ta fonction marche en effet très bien et j'ai appris aujourd'hui la fonction COLONNE que je ne connaissais pas
Merci beaucoup :

 

[CISCO]

Re : Automatiser le décallage de cellules dans une formule

RE

Si, si, cela fonctionne même en faisant du collage un peu n'importe où, à la condition de copier toute la cellule (On sélectionne la cellule et on la copie, tout simplement) et pas si on ne copie que la formule dans la barre de formule.

@ plus

 

[sr94]

Re : Automatiser le décallage de cellules dans une formule

exact, je confirme que ça marche aussi !

Merci !