Enigme "Indirect"

Bonjour tout le monde ;

En voulant comprendre la fonction INDIRECT j’ai suivi un Tuto qui explique comment réaliser des liste déroulante en cascade avec 3 méthode

L’une des méthodes consiste à appliquer une formule pour que le choix de la liste du 2eme niveau se présente dans la cellule .

Dans notre exemple il s’agit des pays qui s’affichent selon le continent choisi dans la première cellule.

La cellule de choix de continents est validée tout simplement en se référant à son nom de liste connue et fixe.

Pour se référer à une liste de pays qui sont arrangés sur des colonnes à côté de la colonne de des continent (voir feuile DON) on a tapé dans validation des données la formule suivante

=DECALER(DON!$A:$A;0;EQUIV($C$13;DON!$1:$1;0)-1)

Ici :

• Réf= DON!$A:$A : la colonne ‘toute entière’ des continents
• Décalage de ligne= 0
• Décalage de colonne = EQUIV($C$13;DON!$1:$1;0)-1 : le numéro de colonne où se trouve ce qui est écrit en $C$13 (le non du continent choisis) à lequel on soustrait 1 (on décale pas du némero de colonne renvoyé mais de 1 de moins pour ne pas comptabilisé la colonne actuelle) , de plus le tableau de recherche de equiv est la première ligne DON!$1:$1 c’est elle qui contient les nom des continents.

Tout est à présent logique en validant (voir la formule dans la ligne jaune) on aura les inconvénients suivants :

1 – la listes des payé contient à sa fin des vides cela est logique parce que la colonne source qui a été décalé est infinie DON!$A:$A

2- la liste des pays commence par le non du continent parce que , aussi, la colonne décalé commence par A1 et on peut pas faire autrement je pense ( sauf astuce q j’ignore) car il faut dans EQUIV chercher sur la première ligne.

Maintenant ce que je considère comme un énigme !!:

En voulant éliminer ces inconvénients j’ai utilisé -hasardeusement- dans la validation des données - dans une autre cellule ‘voir ligne en vert’- la fonction INDIRECT appliqué à la formule précédente càd :

INDIRECT(DECALER(DON!$A:$A;0;EQUIV($C$13;DON!$1:$1;0)-1))

Et le problème des vides ainsi que de l’apparition du titre ‘continent’ dans la liste des pays est résolu !

Mais je ne sais pas pourquoi ; Je ne comprends pas le rôle joué par cette INDIREDT ; ce n’est pas son rôle non ?!!!

Ça ne parait peut être un problème ; mais comme mon caractère me dicte qu’il faut comprendre la philosophie des choses, ça ne me laisse pas tranquille.

NB :

à droite test de la formule -en matrice- directement sur la feuille dans mon excel ça renvoi juste la première valeur de la matrice.

En bas en gris j’ai essayé sans « INDIRECT » et j’ai éliminé les vides en introduisant dans la formule le NBVAL (qui a été imbriqué dans la première formule comme hauteur ) ; mais le problème de titre continent je ne sais pas comment l’éliminer sauf « INDIRECT » qui l’a éliminé !

MERCI

Bonjour,

Pour ne pas avoir le titre, il suffit de remplacer le 0 en second argument de DECALER par un 1.

(et, par la même occasion, supprimer le -1 après EQUIV)

A+

Hello,

Juste la question pour être sur c’est quoi ?

tu veux qu’en fonction du continent choisi ça te propose une liste des pays associés on est d’accord ? Mais qu’en premier choix ça te propose du vide ? Avec une macro de type événement change on peut également vider la sélection quand le continent change.

ou est ce plutôt des explications sur la fonction indirect que tu cherches ?
@+

re

Oui des explications comment INDIRECT a pu résoudre ce problème? ; mais des astuces pour faire sans INDIRECT sont les bienvennue comme celle de de OOeist que je vais essayé

crdl

Hello,

En fait la fonction INDIRECT permet d’interpréter différemment une chaîne de caractère de manière à faire comprendre à Excel qu’on veut traiter une plage de données ou chercher un onglet variabilisé etc etc il y a pas mal d’utilisation.

Dans ton cas tu peux l’utiliser pour faire le nbval de la colonne ou tu as ta correspondance ;)

re

la formule avec équiv n'est même pas necessaire

voir en bleu en bas; la simple formule INDIRECT($C$13) fait tout.

crdl

Hello,

C'est normal... Tu as des zones nommées qui sont définies et donc ont été créées à la main. Essaie de rajouter un continent test ou un pays supplémentaire dans une des listes et ça ne fonctionnera pas..

Dans ton cas le indirect va interpréter le contenu de ta cellule ou tu sélectionnes le continent comme une plage nommée et donc va te ressortir le contenu de cette plage nommée qui lui est définit en dur.

Donc si rajoutes des pays et continents sans mettre à jour tes plages manuellement ça ne fonctionnera pas.

Ci-joint un exemple de listes déroulantes complètement dépendantes et si tu rajoutes des pays avec des villes ainsi que des continents tu verras que tout s'incrémente automatiquement. Sans le Indirect pour lui faire interpréter le nbval, typiquement excel ne comprendrait pas que c'est un chemin construit sur la base "NomOnglet!Plage"

J'espère que c'est un peu plus clair =D

@+

Bonjour;

Oui C claire pour INDIRECT (d’une cellule qui est un nom défini),

Mais voyant ce que c passé :

On a $C13 contient un nom défini

Au début on a INDIRECT (d’une formule contenant $C$13)

A la fin on a INDIRECT ($C13) tout court.

Dans les deux cas on a le juste résultat

Apparemment comme si INDIRECT a ignoré la formule EQUIV ou a ignoré ces défaut (inclusion du titre et des vide) il lui suffit de ce nom défini, même trouvé à l’intérieure d’une formule, il l’a interprété directement comme s’il était seul, (est ce que pourvu que cette formule contenant ce nom defini ne donne pas quelque chose de tout à fait loin ??) Tant mieux si cette INDIRECT est si compréhensive et si souple !

Même si je ne suis pas à 100% convaincu de la logique de ce comportement de INDIRECT ; je dirai que peut-être que son développeur a prévu de tels comportements.

Merci

Bonjour;

Examinant comment INDIRECT a interprété les choses dans la formule

=INDIRECT(DECALER(DON!$A:$A;0;EQUIV($C$13;DON!$1:$1;0)-1))

Voyant cette formule DECALER(DON!$A:$A;0;EQUIV($C$13;DON!$1:$1;0)-1)

• La partie EQUIV($C$13;DON!$1:$1;0)

Du fait qu’il y a $C$13 qui est un nom défini d’une liste, dans cette formule il est interprété comme tel càd de dimension de la liste. On lui demande de chercher le num de la colonne qui contient cette liste (même si le tableau de recherche DON!$1:$1 est la ligne des titre en dessus des éléments de cette liste (je parais que même si on lui demande de chercher dans la DON!$1000:$1000 elle va ne renvoyer tj le numéro de la colonne correspondant à la liste).

Même on peut dire que EQUIVE ne va pas renvoyer le numéro de la colonne mais les numéros de la même colonne (à la dimension de la liste) càd s’il s’agit de la colonne 3 elle va nous renvoyer

(3;3;3;3;3) si la dimension de la liste est 5

De même pour le DECALER :

DECALER(DON!$A:$A;0;EQUIV($C$13;DON!$1:$1;0)-1)

Même s’il s’agit d’une référence DON!$A:$A qui est une plage infinie, elle ne va considérer que la dimension de ce qui est renvoyé par EQUIV et qui est imposé par l’interprétation de INDIRECT d’un nom de liste trouvé dans cette formule.

Ça veut dire que INDIRECT a influencé à l’intérieur de la formule pour remettre tous les aiguilles de DECALER et de EQUIV à la dimension du nom de la liste.

C ça mon interprétation et ça peut être prévu dans le développement de cette fonction INDIRECT comme il est probable aussi qu’il soit un «effet secondaire» bénéfique de cette fonction.

Maintenant je veux si vous êtes d’accord ; de m’indiquer comment mettre un poste au statut résolu parce que je ne trouve pas le bouton.

Merci pour l’intérêt que vous avez porté à mon post.

Hello, normalement tu as une coche de ce style ✔️ sur les messages des différentes personnes qui ont répondu et il faut que tu cliques sur la coche du message qui a aidé à résoudre ton sujet.

Je t’invite à regarder la formule que jai faite pour la deuxième liste déroulante pour comprendre encore le fonctionnement de cette fonction.

@+

Bonsoir ;

@ BAROUTE

Je constate ceci :

1/ la formule INDIRECTE($C$13) que j mise dans la ligne 21 en bleu dans la feuille CAL ne permet pas d’étendre la liste en dehors de sa dimension définie au départ ( j ajouté Argentine dans la colonne AMERIQUE et ne l’a pas affiché)

2/ pour inclure une nouvelle saisie il est impératif d’introduire NBVAL « dynamique » dans la formule de validation de données, pour ne pas se limiter à la dimension de la liste définie au départ (exemple ce que tu as fait pour le nom PaysBaroute) =>mon explication dans la zone verte de la feuille Baroute.

3/ton utilisation de INDIRECT pour évaluer NBVAL n’a rien de nouveau par rapport à son rôle naturel qu'on lui connaît , elle a consisté à reconstituer La colonne où se trouve le continent choisi, et ce, en passant par EQUIV pour renvoyer le num colonne ( 5 pour AMERIQUE) puis ADRESSE pour renvoyer la cellule (E1 pour AMERIQUE) puis GAUCHE pour enlever le 1 ( E1à E) puis concaténer avec les ‘’: ‘’ pour enfin reconstituer DON!E:E ou DON!C:C … selon le continent choisis mais ça, avec INDIRECT, qui comprend cette démarche càd on fait tout ça à l’intérieur de INDIRECT pour qu’elle nous reconstitue quelque chose comme DON!E:E puis lui applique NBVAL . donc tout ça, pour arriver à écrire NBVAL(DON!E:E ) ou NBVAL(DON!C:C ) selon ce qui est reconstitué selon le continent choisi.

Donc en conclusion INDIRECT que tu a mis ici a juste le rôle de reconstituer à quoi appliquer NBVAL pour avoir une hauteur qui tient compte d’une saisie en dehors de la dimension défini pour la liste d’un continent. AMERIQUE par exemple, va rester toujous dim= 4 mais ton nom ‘’PaysBaroute’’ peut aller au-delà pour inclure une nouvelle saisie.

4/ce que j'ai dis en 2/ est vrai juste pour la façon dont sont définis les plages dans ''DON"; il y a une façon (en mettant les plages sous forme de tableau : (sélectionner et cliquer sur tableau dans le menu insertion dans mon Excel 2010) et on n’aura pas besoin de NBVAL voir dans la feuille F1 le nom Tableau1 s’agrandi et tient compte automatiquement de la nouvelle saisie

5/ je tiens toujours à mon explication du message précèdent, car il porte sur la différence entre -il faut bien se concentrer ici- ce qui est en ligne jaune et ce qui est en ligne verte de la feuille CAL, le comportement de INDIRECT de ce cas n’est pas plausible.

• Une autre remarque : la formule en jaune dans ‘CAL’ peut être corrigé pour ne pas démarrer par le titre, et ce, sans INDIRECT: en écrivant ça : =DECALER(DON!$A1:$A1000;1;EQUIV($C$13;DON!$1:$1;0)-1)

En mettant un décalage ligne 1 avec DON!$A:$A ça marche pas , ça bloque !!

Voir en bas de ‘CAL’

Cordialement

Hello,

Donc ton interrogation n'est pas sur le fonctionnement de la fonction INDIRECT.

Quelle est ta question du coup ? Si il y en a une bien évidemment

@+

Bonjour;

c une nouvelle branche : la phylosophie exceliènne

On peut cosidérer que le pb est resolu.

@+