Ceci est une page optimisée pour les mobiles. Cliquez sur ce texte pour afficher la vraie page.

Renvoyer adresse de cellule

Créer une discussion
  • Initiateur de la discussion Initiateur de la discussion Calvus
  • Date de début Date de début

Boostez vos compétences Excel avec notre communauté !

Rejoignez Excel Downloads, le rendez-vous des passionnés où l'entraide fait la force. Apprenez, échangez, progressez – et tout ça gratuitement ! 👉 Inscrivez-vous maintenant !

S'inscrire
Trier par date Trier par votes
Re : Renvoyer adresse de cellule

Je cherche. J'y arrive pas pour le moment.
Ca me renvoie NA#
Si vraiment c'est désespéré, je t'enverrai le classeur complet.
Il doit y avoir un os quelque part, j'espère bien finir par trouver.

Merci Tibo
 
Vote positif 0 Vote négatif
Re : Renvoyer adresse de cellule

re,

On va bien finir par y arriver.

J'ai modifié la formule en A2 :

Code: 
SI(COLONNES($A2:B2)>NBVAL(Temp);"";INDEX(Temp;COLONNES($A:A)+1))
en A41 :

Code: 
=SI(LIGNES($1:1)>SOMME(test_présence*1);"";INDEX(DECALER(Index!$A$2;0;0;;
NBVAL(Index!$2:$2));PETITE.VALEUR(SI(test_présence;ESTNUM(CHERCHE(A$39;tablo))*
COLONNE(DECALER(Index!$A$1;0;0;;NBVAL(Index!$2:$2))));LIGNES($1:1)))&" : "&
INDEX(DECALER($A$1;0;0;24;NBVAL($2:$2));MOD(PETITE.VALEUR(SI(test_présence;
test_présence*COLONNE(DECALER(Index!$A$1;0;0;;NBVAL(Index!$2:$2)))+
(test_présence*LIGNE($A$9:$A$32))/100);LIGNES($1:1));1)*100;PETITE.VALEUR(SI(
test_présence;test_présence*COLONNE(DECALER(Index!$A$1;0;0;;
NBVAL(Index!$2:$2))));LIGNES($1:1))))

CTRL + MAJ + ENTREE

Je joins le fichier

@+
 

Pièces jointes

Vote positif 0 Vote négatif
Re : Renvoyer adresse de cellule

Hello Tibo,

On ( enfin plutôt tu ! ) y est presque.

Ca fonctionne maintenant sur presque tout le tableau.Ca prend en effet les colonnes, mais il y a un souci avec le nombre de lignes sur la colonne A.
Fais le test en entrant "cd" en A38, et tu verras que #Ref est renvoyé après 6 entrées.
J'ai rajouté des lignes dans les autres feuilles, en Boite 4 par exemple, ça ne fait pas le même bug.
Je suis évidemment incapable de trouver de quoi ça vient.

Merci.

Bonne nuit et à demain certainement.
 
Vote positif 0 Vote négatif
Re : Renvoyer adresse de cellule

Re,
une erreur s'était glissée dans la formule que j'avais communiquée.
Ci-joint nouvel essai :
=SI(LIGNES($1:1)<=SOMMEPROD((N($A$9:$D$32<>"")*(ESTNUM(CHERCHE($A$39;$A$9:$D$32)))));"Boîte "&PETITE.VALEUR(SI(ESTNUM(CHERCHE(A$39;$A$9:$D$32));ESTNUM(CHERCHE(A$39;$A$9:$D$32))*COLONNE($A$1:$D$39));LIGNES($1:1))&" : "&INDIRECT(ADRESSE(MOD(PETITE.VALEUR(SI(ESTNUM(CHERCHE($A$39;tablo));COLONNE(tablo)+ESTNUM(CHERCHE($A$39;tablo))*LIGNE(tablo)/100);LIGNES($1:1));1)*100;PETITE.VALEUR(SI(ESTNUM(CHERCHE($A$39;tablo));COLONNE(tablo));LIGNES($1:1))));"")
Formule matricielle

Tibo, j'ai pas tout suivi mais es-tu sûr de DECALER($A$1;0;0;24;NBVAL($2:$2)) ?
Chez moi il affiche une ligne "#REF" et cela disparaît lorsque je modifie comme suit :
DECALER($A$1;0;0;32;NBVAL($2:$2))
Je te le précise au cas où...
Ceci-dit, lorsque j'affiche le résultat entre ta nouvelle formule et que je les compare à ta 1ère proposition et celle que je donne ci-dessus les résultats sont les mêmes (sur qq tests uniquement) donc je n'ai pas vraiment compris ce que cela amène de plus mais comme précisé ci-dessus je n'ai pas tout suivi😱
A+
 
Vote positif 0 Vote négatif
Re : Renvoyer adresse de cellule

David,

J'avais remarqué en effet le 24 dans la formule Decaler, mais j'avais testé et ça n'avait rien changé.
Je viens de retester après ton post, idem.
Ca fonctionne jusqu'à la valeur 30, sans rien changer d'ailleurs puisque #ref est toujours renvoyé, mais par ailleurs, au delà de 30, c'est NA# qui est renvoyé.

As tu une idée ?

Pour ma part j'ai du mal car je n'arrive pas à décrypter vos formules.

Merci
 
Vote positif 0 Vote négatif
Re : Renvoyer adresse de cellule

Re,

David, ta formule fonctionne nickel, mais ne prend en compte que 3 ou 4 colonnes.
La formule de Tibo gère tout le tableau, ne reste que le problème évoqué plus haut.

D'ailleurs Tibo, dans le dernier fichier que tu m'as envoyé, tu as entré des formules en col B, pour test j'imagine, qui elles ne font pas de bug. Peut être une piste.

A+
 
Vote positif 0 Vote négatif
Re : Renvoyer adresse de cellule

Re,
comme j'ai pas tout suivi, j'ai du mal à "raccrocher les wagons".
Fais le test sur le fichier joint où je t'ai mis les 3 formules et dis-nous ce qui ne va pas (enregistre le fichier en prenant un exemple où cela bogue que nous comprenions le problème).
Je verrai cela tout à l'heure...ou demain si Tibo n'est pas passé entre temps.
 

Pièces jointes

Vote positif 0 Vote négatif
Re : Renvoyer adresse de cellule

Voilà je renvoie le fichier avec plusieurs feuilles.
Ca ne fonctionne pas, bizarre ! Maintenant si j'entre CD ça marche, le reste bogue.
Je comprends de moins en moins.

Cordialement
 

Pièces jointes

Vote positif 0 Vote négatif
Re : Renvoyer adresse de cellule

Re,
ci-joint fichier (cf feuille "index(2)") modifié avec ma formule à tester (je laisse à Tibo le soin d'examiner la sienne) déposé sur Cijoint.fr (> à 55 kg malgré la compression).
Cijoint.fr - Service gratuit de dépôt de fichiers
Bonne nuit
 
Vote positif 0 Vote négatif
Re : Renvoyer adresse de cellule

Merci David,

Cela a l'air de fonctionner.
Je vais tester tout ça tranquillement et te dirai demain ce qu'il en est. Mais à priori ça a l'air d'être au poil.

J'ai hâte de voir la solution de Tibo.

Si ça t'est possible, pourras tu m'expliquer en langage humain le fonctionnement de ta formule ?

Merci encore.

Bonne nuit
 
Vote positif 0 Vote négatif
Re : Renvoyer adresse de cellule

Bonjour,

le DECALER($A$1;0;0;24;NBVAL($2:$2)) devait effectivement être corrigé en remplaçant le 24 par 31

Le DECALER était destiné à gérer la largeur du tableau en fonction du nombre de valeurs (noms des onglets) en ligne 2.

Après quelques menues corrections, je pense que ma formule aurait donné les mêmes résultats que celle de David.

La formule de David est plus courte et je la préconise.

Il va falloir que je mette à utiliser cette fonction ADRESSE, ce qui me permettra ainsi de bien comprendre et assimiler la formule de David (merci David pour cet exercice 😛)

Bonne journée

@+
 
Vote positif 0 Vote négatif
Re : Renvoyer adresse de cellule

Bonjour,

A priori, après d'autres tests, je n'ai plus de bogue.
Merci et un grand bravo à tous les deux !

Bonne journée à vous.

Cordialement

Calvus

PS: David, si tu te sens l'âme d'un traducteur en langage humain....
 
Vote positif 0 Vote négatif
Re : Renvoyer adresse de cellule

Re,
=SI(LIGNES($1:1)<=SOMMEPROD((N(tablo<>"")*(ESTNUM(CHERCHE($A$38;tablo)))));DECALER($A$1;1;PETITE.VALEUR(SI(ESTNUM(CHERCHE($A$38;tablo));COLONNE(tablo));LIGNES($1:1))-1)&" : "&INDIRECT(ADRESSE(MOD(PETITE.VALEUR(SI(ESTNUM(CHERCHE($A$38;tablo));COLONNE(tablo)+ESTNUM(CHERCHE($A$38;tablo))*LIGNE(tablo)/100);LIGNES($1:1));1)*100;PETITE.VALEUR(SI(ESTNUM(CHERCHE($A$38;tablo));COLONNE(tablo));LIGNES($1:1))));"")

LIGNES($1:1)<=SOMMEPROD((N(tablo<>"")*(ESTNUM(CHERCHE($A$38;tablo))))) permet de ne faire apparaître que les valeurs répondant au critère sélectionné en A38 et donc d'éviter les valeurs d'erreurs (#NOMBRE #N/A,...
Donc au-delà de ce nombre de critères, la cellule ne marque rien (=si(le nombre de lignes déjà incrémentées est inférieur ou = au nombre de valeurs répondant au critère la valeur est inscrite dans la cellule sinon la cellule reste vide ("")

DECALER($A$1;1;PETITE.VALEUR(SI(ESTNUM(CHERCHE($A$38;tablo));COLONNE(tablo));LIGNES($1:1))-1) permet de rechercher et d'inscrire l'intitulé de colonne inscrit à la ligne 2 en se servant notamment du n° de colonne de l'adresse de la cellule répondant au critère inscrit en A38 (en gras).

INDIRECT(ADRESSE(MOD(PETITE.VALEUR(SI(ESTNUM(CHERCHE($A$38;tablo));COLONNE(tablo)+ESTNUM(CHERCHE($A$38;tablo))*LIGNE(tablo)/100);LIGNES($1:1));1)*100;PETITE.VALEUR(SI(ESTNUM(CHERCHE($A$38;tablo));COLONNE(tablo));LIGNES($1:1))))

La fonction adresse permet de ramener l'adresse des cellules répondant au critère en A38. Pour cela il faut ramener son n° de ligne :MOD(PETITE.VALEUR(SI(ESTNUM(CHERCHE($A$38;tablo));COLONNE(tablo)+ESTNUM(CHERCHE($A$38;tablo))*LIGNE(tablo)/100);LIGNES($1:1));1)*100
et son n° de colonne : PETITE.VALEUR(SI(ESTNUM(CHERCHE($A$38;tablo));COLONNE(tablo));LIGNES($1:1))
Ex pour "boite" inscrit en A38, l'adresse des 3 cellules ramenées est respectivement :
C3 (12ème ligne et 3ème colonne), C13 (13ème ligne et 3ème colonne) et G9 (9ème ligne et 7ème colonne).
L'utilisation de la fonction indirect permet dans cet exemple de ramener la valeur inscrite dans ces 3 cellules (boite Nokia, boite GPS,...)

Je tiens à signaler que 2 personnes sont à l'origine de mon utilisation de cette fonction :
- Monique qui l'a utilisée (à ma connaissance) dans un fil antérieur en la mariant aux fonction Min et NB.SI, ce qui lui permettait de ramener les valeurs dispersées dans un tableau dans une colonne et sans les doublons (Tibo, si cela t'intéresse je peux te fournir le fichier et la manière dont je l'ai décortiqué) ;
- Tibo qui a su dans un fil récent dégager les n° de ligne et de colonne sans colonne intermédiaire en se servant de la fonction petite.valeur mariée à la fonction index (j'étais sur le même type de recherche et avait dégagé le n° de ligne mais bloquais encore sur le n° de colonne).
Ensuite, je n'ai fait qu'assembler le tout pour donner la formule présentée qui, à mon sens ouvre certaines perspectives que la formule de Monique ne permettait pas (ou du moins que je n'ai pas su adapter à cet effet) mais cela est une autre histoire...
Pour terminer 2 conseils lorsque tu veux comprendres une formule :
- se servir de la touche F1 (aide d'Excel)
- la décomposer en sous-formules : ex (valable dans la version 2007, pour les versions antérieures, je ne sais pas) :dans la barre de formule, cliquer à droite de la parenthèse placée après "adresse". Apparaît alors la fonction (adresse(n°_lig,n°_col;...). Sélectionner n°_lig et recopier ce qui est surligné en ajoutant le"=" devant et valider (matriciellement dans le cas présent).
Faire de même pour sélectionner le n° de colonne et tu obtiens ainsi les n° de ligne et de colonne de chaque valeur sélectionnée.
Cordialement
 
Vote positif 0 Vote négatif
Re : Renvoyer adresse de cellule

Tout d'abord David, merci infiniment. Ton aide a été précieuse.

J'ai commencé à décortiquer la formule, mais j'avoue, malgré la précision de tes explications, que c'est encore ardu. Mais je ne désespère pas.

Tu parles de l'aide excel (F1), sache que je l'utilise constamment, notamment avant de poster sur le forum. Ceci étant, bien que constatant que cette aide ait largement évolué avec les différentes versions du logiciel, elle reste néanmoins souvent obscure, soit quant à la formulation de la syntaxe, ce qui est devenu assez rare, mais surtout quant à l'imbrication des différentes formules.

Exemple avec :
Fonction MOD
Renvoie le reste de la division de l'argument nombre par l'argument diviseur. Le résultat est du même signe que diviseur.
Syntaxe
MOD(nombre;diviseur)
nombre représente le nombre à diviser pour obtenir le reste.
diviseur représente le nombre par lequel vous souhaitez diviser le nombre.

Avec ça, on est bien avancés !

Alors comment passer de ça à ça :

MOD(PETITE.VALEUR(SI(ESTNUM(CHERCHE($A$46;tablo));COLONNE(tablo)+ESTNUM(CHERCHE($A$46;tablo))*LIGNE(tablo)/100);LIGNES($1:1));1)*100

C'est la que réside la difficulté, et qui justifie évidemment l'existence de ce forum.
Ce qui est compliqué également, c'est d'arriver (pas mon cas pour le moment ! ) à faire la synthèse de ce qu'on souhaite obtenir, et ensuite le formuler. 🙄 Surtout quand on doit utiliser des formules dont on ignore l'existence, ce qui a été le cas sur ce post.

Merci pour l'astuce, je ne la connaissais pas, je vais m'y atteler.

Merci.

Cordialement.

PS: je ne suis pas mécontent, bien malgré moi, d'avoir contribué à faire progresser une formule qui posait souci, ou du moins qui pouvait évoluer. 😎
 
Vote positif 0 Vote négatif
Re : Renvoyer adresse de cellule

Re,
Le problème c'est que ce n'est pas l'exemple idéal pour comprendre cette fonction parce que là, c'est du grand art.
Pour comprendre une fonction, il faut dans un 1er temps aller au plus simple.
Pour Mod par exemple, place toi dans un fichier et tape en A1 =MOD(LIGNE();1)
(la fonction ligne te ramène le n° de ligne, donc 1 si tu la tape en A1 B1 ou F1, 2 si tu la tape en A2, B2 ou F2, ...) et tire ta formule vers le bas.
Place-toi ensuite en B1 et tape =MOD(LIGNE();2) et tire ta formule vers le bas.
Place-toi ensuite en C1 et tape =MOD(LIGNE();3) et tire ta formule vers le bas.

Tu remarqueras cela te créé une suite qui se répète, que la suite varie en fonction du diviseur (en gras ci-dessus) et que le diviseur correspond à l'intervalle de ligne où il est inscrit 0.

Place-toi dans une autre feuille, place-toi ensuite en A1 et tape =MOD(colonne();1) et tire ta formule vers la droite.
puis en A2 et tape =MOD(colonne();2) et tire ta formule vers la droite,etc.
et constate les résultats.

L'utilisation de cette fonction te permet donc entre autre de ne ramener des valeurs que dans certaines lignes ou colonnes, par exemple donc 1 ligne sur 2 si tu inclus dans ta formule =MOD(LIGNE();2)=1(pour les lignes impaires) ou =MOD(LIGNE();2)=0 (pour les lignes paires), de ne colorer qu'une ligne sur 2 dans le cadre d'une mise en forme conditionnelle :
=MOD(LIGNE();2)=1 pour colorer les lignes impaires
=MOD(LIGNE();2)=0 pour les lignes paires,
etc.
Je ne sais pas si j'ai été très clair, mais la familiarisation d'une fonction passe avant tout par des tests personnels.
A+
 
Dernière édition:
Vote positif 0 Vote négatif
Identifiez-vous ou inscrivez-vous pour participer.
Rejoins nos membres "Supporter XLD" et booste ton expérience Excel !
- Navigue sans publicité
- Accède à Cléa, notre assistante IA experte Excel... et pas que...
- Profite de fonctionnalités exclusives
Ton soutien permet à Excel Downloads de rester 100% gratuit et de continuer à rassembler les passionnés d'Excel.
Je deviens Supporter XLD

Discussions similaires

H
  • Question Question
XL 2019 masquer des colonne sous condition d'une cellule en vba
Réponses
5
Affichages
251
sylvanu
C
  • Résolu(e)
XL 2013 Renvoyer 2 dans la cellule
Réponses
2
Affichages
382
candido57
C
G
  • Question Question
Microsoft 365 Formule qui retourne le contenu de la cellule sélectionnée
Réponses
5
Affichages
487
Guy_M
G
N
  • Question Question
XL 2021 Affecter automatiquement une valeur dans une cellule en fonction d'une saisie dans une autre cellule
Réponses
9
Affichages
303
Nico Gre
N
W
  • Résolu(e)
XL 2019 Placer ou remplacer une photo .jpg au-dessus de la cellule [B6] contenant du texte à conserver
2
Réponses
21
Affichages
381
TooFatBoy
L
  • Résolu(e)
Autres Lier une cellule texte a une cellule valeur ou utiliser la formule max avec du texte
Réponses
4
Affichages
323
LBE
L
  • Question Question
Microsoft 365 renseigner un userform
Réponses
5
Affichages
537
Dranreb
M
  • Question Question
Autres Problème de sélection
Réponses
14
Affichages
484
fanch55
  • Question Question
XL 2021 création d'onglets en masse depuis un modele
Réponses
6
Affichages
325
Phil69970
S
  • Résolu(e)
XL 2013 Questions déjà traitée ici mais... (Insérer cellule dans nom de PDF)
Réponses
10
Affichages
521
StéphaneDeLà
S
  • Résolu(e)
XL 2019 Trouver la dernière heure de Log
Réponses
6
Affichages
562
Titof06
  • Résolu(e)
Microsoft 365 Comment supprimer un espace entre deux chiffres ?
2
Réponses
18
Affichages
785
Staple1600
S
  • Résolu(e)
Microsoft 365 [EXCEL] Chercher dans une cellule s'il y a un mot clef de ma liste et renvoyer une information
Réponses
4
Affichages
716
sylvain G
S
M
  • Question Question
XL 2021 Générateur Tournoi Ecole de Rugby
Réponses
5
Affichages
865
Magis
M
  • Résolu(e)
XL 2021 Extraction images depuis commentaire de cellule vers dossier.
11 12 13
Réponses
182
Affichages
4 K
patricktoulon
P
  • Résolu(e)
XL pour MAC Problème de changement de couleur de la valeur d'une cellule
Réponses
2
Affichages
578
pvander
P
  • Résolu(e)
XL 2016 Supprimer "Rechercher dans les menus" du menu contextuel de cellule Excel
Réponses
3
Affichages
468
jurassic pork
A
  • Question Question
XL 2021 récupérer la valeur d'une variable en fin de boucle dans une autre procédure
Réponses
5
Affichages
586
Attila451
A
S
  • Question Question
Microsoft 365 [Outlook] Macro pour récupérer les données dans propriétés outlook
Réponses
2
Affichages
938
Sarah_Croche
S
V
  • Question Question
Microsoft 365 Je ne sais plus ou j'en suis....
3 4 5
Réponses
62
Affichages
3 K
ValDorier
V
Menu
Connexion
S'inscrire
Les cookies sont requis pour utiliser ce site. Vous devez les accepter pour continuer à utiliser le site. En savoir plus…