References absolues et relatives

andré · 5 Août 2004

Bonjour à vous tous,

A mon tour de vous demander un peu d'aide.

J'ai un petit tableau de calcul que je dois copier plus de 100 fois, voire 300 fois dans certains cas, sur la même feuille.
De plus, je dois pouvoir agrandir chacun de ces tableaux, de façon individuelle, selon mes besoins, ceci pouvant aller jusque 50 lignes par tableau.

Pour pouvoir copier des lignes dans un tableau je suis obligé de mettre certaines cellules en référence absolue.
Mais pour copier mes tableaux, certaines ne peuvent pas l'être.

De plus si j'insère une ligne en-dessous de la dernière ligne d'un tableau la formule du total ne s'incrémente pas automatiquement.

Tout cela a l'air d'être compliqué, mais la petite pièce jointe vous le fera comprendre rapidement.

La seule chose compliquée est une solution simple (lol).
La seule chose aussi qui m'importe est le résultat, peu importe la façon d'y parvenir.

J'ai déjà essayé de me réferer aux lignes, mais je n'y parviens pas.

Alors, comme d'habitude, vous connaissez mon cadeau en cas de réussite.

Ândré.

Jean-Marie · 5 Août 2004

Bonjour André

Essaye cette formule =SI(OU(A5<>"";E5=0);SOMME(DECALER(E$3;ENT(LIGNE()/14)*14:E4)-SOMME(DECALER(F$3;ENT(LIGNE()/14)*14:F3);"")

Si le tableau n'a pas le même nombre de lignes, cette formule ne fonctionnera pas

@+Jean-Marie

Jean-Marie · 5 Août 2004

Re...

Fonction matricielle quelque soit la longueur du tableau
=SI(OU(A5<>"";E5=0);SOMME(INDIRECT(ADRESSE(MAX((A$1:A4="Localisation")*LIGNE(A$1:A4));5)):E4)-SOMME(INDIRECT(ADRESSE(MAX((A$1:A4="Localisation")*LIGNE(A$1:A4));6)):F3);"")

Mais à 300 tableaux, embauche des rameurs pour l'aider.

@+Jean-Marie

andré · 5 Août 2004

Merci Jean-Marie,

C'est là le problème : les tableaux sont de longueurs différentes.

J'avais déjà concocté la solution suivante, à défaut de mieux :
Je place un petit tableau vierge en références relatives en en-tête de feuille.
Lorsque j'ai besoin d'un nouveau tableau, c'est celui-là que je copie.
Il est donc exact, peu importe où je le place.
Puis je fais un rechercher de la cellule en question / remplacer par la même cellule en référence absolue
Dès lors je puis ajouter des lignes avant la dernière

Pour empêcher d'ajouter une ligne après la dernière, j'avais songé de cacher la dernière

En mettant tout cela sous une macro, cela devrait marcher, mais c'est pas aussi simple que cela car il y beaucoup d'autres formules à copier dans d'autres colonnes qui présentent des problèmes similaires

Alors j'essaie de les résoudre les uns après les autres, avant de tout prendre ensemble

Si la vie n'était pas aussi compliquée, peut-être ne vaudrait-elle pas la peine d'être vécue, car là où il n'y a pas surpassement de soi-même, il n'y a pas de vrai bonheur

Ândré

andré · 5 Août 2004

Je n'avais pas actualisé, excuse-moi !

Mais je vois que ta dernière formule tu as encore des valeurs absolues, donc je crains, mais je n'ai pas encore essayé.

Le problème est double : il faut pouvoir copier le tableau et ajouter des lignes en copiant les formules, tout en se diasnat qu'il y aura peu-être des lignes vides à l'intérieur du tableau.

A bientôt, avec la solution (lol).

Ândré.

Pascal76 · 5 Août 2004

Bonjour André

En gros André il nous faudrait une formule où on cherche le mot "Localisation" dans la colonne A se trouvant au-dessus de la cellule où vient la référence (cela nous donnerait ta ligne)??

Tu me dis si je me trompe

Pascal

Jean-Marie · 5 Août 2004

Re...

La valeur absolue en A$1 n'est pas un problème en soit, elle ne serait que comme pour de référence, même si tu rajoutes des lignes avant la ligne localisation.

=SI(OU(A5<>"";E5=0);SOMME(INDIRECT(ADRESSE(MAX((Indirect("A$1"):A4="Localisation")*LIGNE(A$1:A4));5)):E4)-SOMME(INDIRECT(ADRESSE(MAX(((indirect("A$1"):A4="Localisation")*LIGNE(A$1:A4));6)):F3);"")

@+Jean-Marie

andré · 5 Août 2004

Salut à vous deux, ... et aux autres qui cherchent (lol),

J'ai une nouvelle piste !

Décaler les cellules F1, F2 et F15, F16 d'une colonne vers la droite et les problèmes sont résolus.

La mise en page est moins belle (dommage, mais on trouvera bien quelque chose).

Le tout étant maintenant de voir la vitesse de calcul si je me réfère toujours aux cellules E$3 et F$3, mais normalement cela devrait aller.
Avez-vous quelque expérience sur des sommes reprenant 2000 lignes ?

Ândré.

Abel · 5 Août 2004

Bonjour Ândré,

Oulà ! On s'absente deux minutes et c'est l'avalanche.

Je essayé quelque chose (à mon niveau). C'est un peu tiré par les cheveux.

Il reste l'insertion de ligne. Il faut penser à insérer (cellules ou lignes, y faire attention si jamais il y a des tableaux côte à côte avec des longueurs différentes) avant d'arriver à la fin puis recopier les cellules.

En espérant que cela serve (un peu) à quelque chose.

Abel

Monique · 5 Août 2004

Bonjour,

Pourquoi pas du Sommeprod ?
En F2, pour ne pas avoir de références circulaires :
=SOMME(E4:E13)
En F4 et idem toute la colonne F :
=SI(OU(A5<>"";E5=0);SOMME(E$1:E4)-SOMMEPROD((ESTNUM(E$1:E3))*1;F$1:F3);"")

andré · 5 Août 2004

Je ne sais pas ce qui c'est passé, mais ma réponse a fait l'objet d'un nouveau post, en dehors de ma bonne volonté !

<http://www.excel-downloads.com/html/French/forum/messages/1_107315_107315.htm>

Excusez-moi pour le petit détour !

Ândré.

Abel · 6 Août 2004

Bonjour Ândré,

Je na sais pas si tu suis encore ce fil mais, bon, à tout hasard.

Une nouvelle mouture de mon truc d'hier.

Il n'y a plus les colonnes à droite.

Mais il reste une colonne masquée (comme le concombre. Hi hi !).

L'avantage est que ça se copie vraiment n'importe où.

D'ailleurs, en relisant le complètement le fil, juste avant d'envoyer, je m'aperçois que tu as déjà pensé a ce que je t'envoie.
Je le mets quand même pour le principe.

Dis-moi si j'ai fait la même chose que toi.
De plus je pense que mes formules sont optimisables. Alors je suis preneur de tous les exemples.

Bonne journée.

Abel

andré · 6 Août 2004

Salut Abel,

J'apprécie beaucoup ton approche.

Comme tu dis, il doit y avoir moyen de simplifier ... et même de supprimer la colonne supplémentaire.

La formule en G4 peut être écrite : =SI(OU(A5<>"";E5=0);F4;"-"))

Si dès lors on remplace F4 par sa formule, on devrait pouvoir supprimer la colonne supplémantaire.
Je n'ai pas encore essayé.

Bien que cette formule soit plus longue que celle concoctée avec Monique, je la conserve bien au chaud, au cas où, lors de la mise en place définitive, il s'avererait que son calcul soit plus rapide.

Merci encore.
Ândré.

Abel · 6 Août 2004

Re,

Merci pour l'astuce. C'est tellement logique en plus.

J'ai beau le retourner dans tous les sens (le tube d'aspirine), sans colonne masquée je n'arrive pas à le faire sans une ou des adresses absolues (en fonction de l'approche).

Il reste la solution d'un petit code évènementiel mais ....

A+

Abel

andré · 6 Août 2004

Salut,

Je crois bien que j'y parviendrais, mais pour le moment j'ai d'autres chats à fouetter : chez nous c'est l'époque du froment, donc 2.000 ballots de 15 kg = 30 tonnes de paille à rentrer, ... et le fenil c'est haut !

Ândré.

References absolues et relatives

andré

Guest

Pièces jointes

Jean-Marie

Guest

Jean-Marie

Guest

andré

Guest

andré

Guest

Pascal76

Guest

Jean-Marie

Guest

andré

Guest

Abel

Guest

Pièces jointes

Monique

Guest

andré

Guest

Abel

Guest

Pièces jointes

andré

Guest

Abel

Guest

andré

Guest

Discussions similaires