XL 2010 Tirer une formule qui s'incrémente sur les feuille.

[Kapioss]

Bonjour,
Je rencontre actuellement un problème sur excel.
J'ai un fichier d'analyse chimique avec plusieurs feuille qui corresponde à différent échantillon.
Sur la première feuille j'ai la formule : "=$F$4*'spl 1'!$H$30" et lorsque je l'étends vers le bas j'aimerais qu'excel me renvoie "=$F$4*'spl 2'!$H$30" et ainsi de suite lorsque j'étire la formule afin de parcourir tout mes échantillons qui correspondent chacun à une feuille (échantillon 3 est sur spl 3 etc).
Seulement lorsque je l’étire il me renvoi "=$F$4*'spl 1'!$H$30", du coup j'ai rempli à la main sur mes 10 premier échantillon mais bon j'aimerais éviter de faire ça lorsque j'ai plus de 200 échantillons...
Serait-il possible de faire cela sans macro ? car je n'ai que très très peu de connaissance la dessus (un cours de 1h en école d'ingé de chimie...).
J'ai essayer avec la fonction indirect car j'ai vu sur un forum que quelqu'un avait un problème similaire mais je n'ai pas réussi (je comprend mal/pas cette fonction honnêtement)

Bonne journée et merci de vos réponse.
Cordialement

 

[mapomme]

Bonjour @Kapioss,
Bienvenye sur XLD

Tirez les formules des cellules O4 à V4 jusqu'à la ligne 42.

 

[Kapioss]

Bonjour,
Je vous remercie ! Je vois que cette formule dépasse complètement mes compétences sur excel !
Si vous avez le temps de m'expliquer comment fonctionne la formule utilisé je suis preneur !

En tout cas mes datation de sédiment marin vous remercie ! J'aurais enfin un fichier propre et pas avec une feuilles de 15000x15000 avec toutes les valeurs.

Encore merci et bonne journée !

 

[mapomme]

Si vous avez le temps de m'expliquer comment fonctionne la formule utilisé je suis preneur !

Hé bien, prenez ce qui suit

Tout d'abord ,une autre formule qui selon moi est plus simple puisque j'utilise la colonne N qui contient le nom des feuilles. Je donnerai les explications pour ce fichier. Mais le principe reste le même pour le premier fichier fourni.

La formule est basé sur la fonction Indirect(xxx)
Cette fonction prend le texte en paramètre et le transforme en référence.
Exemple :
Supposons que F1 = 12,34
=Indirect("F1") renvoie une référence à la cellule F1 et donc renvoie la valeur de F1 c'est à dire 12,34
C'est exactement comme si on avait écrit directement la formule =F1

Jusqu'ici aucun intérêt pour la fonction Indirect. Supposons maintenant qu'on veuille renvoyer une cellule de la plage AA1 à AA100 de manière aléatoire.
On pourrait vouloir écrire la formule =AA & ALEA.ENTRE.BORNES(1;100)
Essayez, ça ne donnera rien.
On change donc pour la formule ="AA" & ALEA.ENTRE.BORNES(1;100)
Essayez encore une fois, ça ne donnera rien si ce n'est le texte AAxx ou xx est un nombre entre 1 et 100
Avec la fonction indirect(), c'est une autre histoire.
=Indirect("AA" & ALEA.ENTRE.BORNES(1;100))
Ce qu'il y dans la fonction indirect va renvoyer le texte AAxx (supposons que xx = 23)
La fonction Indirect("AAxx") va renvoyer une référence à la cellule AA23.
Donc Indirect("AA23") va renvoyer la valeur de la cellule AA23.

On a donc entré une expression texte calculée dans la fonction Indirect et cette expression texte a été transformée en référence.
Le gros avantage, c'est que cette expression texte est une expression qu'on peut paramétrer.

Dans votre cas, vous avez en O4 la formule : =$F$4 * 'spl 1'!$H$30
Quand on tire cette formule vers le bas, il y a des choses invariantes ($F$4 et $H$30) et une chose variable qui est le nom de la feuille spl 1, spl 2, spl 3
Or justement sp1, sp2, sp3, sont les éléments de la colonne N.
Il faut donc construite un texte qui sera paramétré par la colonne N.
Ce texte sera injecté dans la fonction Indirect() pour renvoyer la bonne référence.
sp1 est envoyé par la cellule N4 ce qui suggère : = N4 & "'!$H$30"
Deux remarques :

• par rapport à votre formule initiale en O4, on voit qu'on a maintenant un texte (d'où les guillemets)
• quand on tirera cette expression vers le bas, N4 prendra successivement les valeurs spl 1 puis spl 2 puis spl3, etc.

ce qui donnera : "'spl 1'!$H$30" puis "'spl 2'!$H$30" puis "'spl 3'!$H$30", etc.
Injectées dans indirect, ces expressions pointeront donc bien vers les bonnes cellules
La formule complète en O4 à tirer vers le bas sera donc : =$F$4 * INDIRECT("'" & N4 & "'!$H$30")

Le SiErreur(...) a été ajouté ensuite pour afficher une cellule vide si la feuille spl nn n'existe pas.


Pour la formule du premier fichier, je ne me suis pas servi de la colonne N (ce qui est bien dommage ).
Donc à la place de "'" & N4, on a mis une expression (plus compliquée) qui donne la même chose : "'spl " & LIGNES($1:1)
On remarquera que LIGNES($1:1) renvoie le nombre de lignes depuis la ligne 1.
Quand on tire vers le bas la formule LIGNES($1:1), elle devient devient LIGNES($1:2) puis LIGNES($1:3), ...
donc renvoie successivement 1 puis 2 puis 3, ...
On a donc ainsi reconstruit la suite spl 1, spl 2, spl 3, etc.

Voilà, voilou. N'hésitez pas à demander des éclaircissements complémentaires .