Discussion :

[Peanut]

Bonjour,

je cherche à accéder au contenu d'une cellule en fonction de sa position par rapport à une autre.

Bon, ça sera plus simple avec un cas concret je crois :
- dans la cellule A10 j'indique une référence à une autre cellule quelconque (et variable), mettons '=A1'
- dans la cellule B10, je voudrais récupérer la valeur de la cellule située 3 lignes plus bas que A1, soit A(1+3)=A4 en l'occurrence.

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
5
6
7
8
9
10
11
   A	B
1  a	
2  b	
3  c	
4  k	
5  l	
6  m	
7 
8 
9 
10 a	k

Et donc si j'indique '=A2' dans ma cellule A10, je veux qu'automatiquement ce soit la valeur de A(2+3)=A5 qui soit affichées en B10.

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
5
6
7
8
9
10
11
   A	B
1  a	
2  b	
3  c	
4  k	
5  l	
6  m	
7 
8 
9 
10 b	l

Comment faire ça avec des fonctions Excel ?


Edit : ah oui, sous Excel 97...

[jfontaine]

Formule a mettre en B10

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

=DECALER(INDIRECT($A$10;1);3;0)

[Peanut]

Presque...

Ca marche si j'écris la chaîne de caractères "A1" en A10,
mais pas si j'écris la formule "=A1" en A10.

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
5
6
7
8
9
10
11
   A	B
1  a	
2  b	
3  c	
4  k	
5  l	
6  m	
7 
8 
9 
10 A1	k

[jfontaine]

La, je ne vois pas comment on peut retrouver une référence a une cellule si cette référence est dans une formule

[Peanut]

Ok, ça doit sûrement pas être possible, je vais faire autrement en passant par une 3e cellule dans laquelle j'indique le numéro de ligne et générer les formules de mes 2 cellules A10 et B10 à partir de cette 3e...
Merci.

[Alqualonde]

Bonsoir,

voici une solution, en admettant que tu connaisses ta plage initiale (ici A1:A7)et que tes données se trouve toujours en colonne A (pour la concaténation) :

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

=DECALER(INDIRECT(CONCATENER("$A$";INDEX(EQUIV(A10;A1:A7;0);1));1);3;0)

[Peanut]

Très fort !
Et ça marche sur du multi-onglets ?

J'essaye.

[ullan]

Bonjour,

Une autre solution :

=INDEX(A1:A9;EQUIV(A10;A1:A9;0)+3)

[Maxence HUBICHE]

Pas mal, mais encore faut-il que la valeur soit unique dans la colonne !
Je t'ai fait une petite fonction, qui marche pas mal.
Mais elle n'est pas intéressante vraiment.
Pour une raison que je ne perçois pas, la fonction ne se calcule qu'une seule fois.
Il faudrait que tu vérifies si tu a le même fonctionnement sur ta machine.

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
'---------------------------------------------------------------------------------------
' Objet     : Renvoyerla valeur d'une cellule décalées (en Nb de lignes et colonnes) en
'               fonction de la référence à une cellule mentionnée dans une autre cellule
' Arguments : CelluleFormule ==> Cellule contenant la formule cers la cellule de
'               référence. Cette cellule doit contenir une référencce sous la forme
'               = REFERENCEUNIQUE
'             NbLignes ========> Nbre de lignes de décalage     Défaut : 0
'             NbColonnes ======> Nbre de colonnes de décalage   Défaut : 0
' Retour    : La fonction renvoie la valeur de la cellule décalée. Si la référence n'est
'               pas valide, la fonction renvoie la valeur d'erreur #Ref!
'---------------------------------------------------------------------------------------
'
Function CelluleDecalee(CelluleFormule As Range, _
                        Optional NbLignes As Long = 0, _
                        Optional Nbcolonnes As Long = 0) As Variant
    On Error GoTo CelluleDecalee_Error
    Dim sTemp As String
    sTemp = Mid(CelluleFormule.Formula, 2)
    CelluleDecalee = Range(sTemp).Offset(NbLignes, Nbcolonnes).Value
    Exit Function
CelluleDecalee_Error:
    CelluleDecalee = CVErr(XlCVError.xlErrRef)
End Function

[ullan]

Bonjour Maxence,

Tu as tout à fait raison de le préciser, mais comment savoir à quelle cellule il faut se référer lorsqu'il y a des doublons ?

Le problème n'est pas dans la formule, mais dans la question.
A défaut de précision, j'ai supposé qu'il ny avait pas de doublons ou que c'était la première occurence.

[Alqualonde]

Oui j'ai supposé de même pour ma formule (inutilement bien plus compliquée que la tienne ullan !) étant donné l'exemple fourni...

[Tdeny]

Bonjour,
Pour obtenir ton k, tu mets :

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

=INDIRECT(ADRESSE(LIGNE()-6;1))

Tu peux aussi introduire une colonne supplémentaire pour entrer le 6 et pouvoir le changer.
En C1 par exemple.

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

=INDIRECT(ADRESSE(LIGNE()-C1;1))

[Tdeny]

Post

Si tu arrive à récupérer la formule entrée en A10 sous forme de texte pour mettre à jour automatiquement B10 en reprenant l'adresse entrée modifiée par extraction de lettres chiffres dans celle-ci, dis le moi.

[ullan]

Salut Alqualonde,

Ta formule pouvait être simplifiée comme suit :
=INDIRECT("A"&EQUIV(A10;A1:A6;0)+3)

Pour répondre à jfontaine et à Tdeny, il y a moyen de passer par les macros XL4
Menu Insertion / Nom / Définir
Nom dans le classeur : formule (ou autre chose, au choix)
Fait réference à : =LIRE.CELLULE(6;Feuil1!$A$10)
Dans la cellule : =DECALER(INDIRECT(STXT(formule;2;7));3;0)
le 0 final (zéro) peut être supprimé,
le 2 parce que formule renvoie =A1, donc il faut supprimer le =
le 7 parce que la longueur maximum d'une référence est de 7 caractères (IV65536)

[Peanut]

Houlala, que de réponses !

J'avais donné un exemple simplifié pour que ce soit plus clair, mais voilà précisément mon besoin :

- j'ai 1 onglet ('exemples 10.07.2007') avec 1 donnée (texte) sur chaque ligne (a priori pas de doublon, mais rien ne l'interdit)
- ces données sont réparties en 2 groupes ordonnés : A2:A47 et A52:A97
- A(n) correspond à A(n+50) (donnée avant vs donnée après)

- dans un autre onglet (décomposition VV134 10.07.2007) j'indique la référence à 1 donnée du 1er groupe (='exemples 10.07.2007'!A7) dans une 1ère cellule
- dans une 2e cellule de décomposition VV134 10.07.2007 j'indique la référence correspondante du 2e groupe de données qui se trouve donc 50 lignes plus bas (='exemples 10.07.2007'!A57)
- ensuite des calculs sont évidemment faits en utilisant les valeurs de ces 2 cellules

Ce que je voudrais, c'est ne pas avoir à saisir la référence dans la 2e cellule quand je change celle dans la 1ère, mais que ça prenne automatiquement la référence de la donnée située 50 lignes plus bas.


Merci pour vos posts, je regarderai ça en détail mais en début de semaine.