Référencement Dynamique Plage Nommée dans la Formule Excel
J'ai un tableau dans Excel avec des en-têtes de colonnes qui correspondent à la partie du dynamisme de la plage nommée ailleurs dans mon classeur. Par exemple, j'ai ces en-têtes de colonne: "10", "20", etc., et la dynamique de ces plages nommées: "ExampleRange10", "ExampleRange2", etc. J'aimerais entrer dans une Recherchev formule qui fait référence à ExampleRange10 par la concaténation de la chaîne "ExampleRange" et l'intitulé de la colonne "10". Cela me permettrait simplement d'étendre la formule à travers toutes les colonnes dans le tableau, au lieu de taper manuellement "ExampleRange10", "ExampleRange20", etc. dans chaque colonne de la formule.
Je suis conscient de l'INDIRECTS de la fonction, et l'ont utilisée avec succès dans le passé avec des plages nommées, mais il ne semble pas être de travailler avec les dynamique plage nommée dans ce cas. Je devine que c'est une nuance qui a quelque chose à voir avec la façon dynamique des plages nommées sont définies par Excel (ils n'apparaissent pas dans la plage nommée déroulant à gauche de la barre de formule, et ils ont des propriétés intéressantes en VBA, par exemple). Est-il une manière que je peux utiliser l'INDIRECTS formule en conjonction avec une dynamique plage nommée, ou est-il un autre moyen que je peux résoudre ce problème?
Modifier: Voici les formules exactes utilisées.
C'est la principale formule: =VLOOKUP(B2,INDIRECT("ExampleRange"&C1),2,FALSE)
où C1 contient "10" et la formule de mon dynamiques plage nommée appelé "ExampleRange10" est: =OFFSET(Sheet1!$F$2,0,0,COUNTA(Sheet1!$F$2:$F$25),2)
. La principale formule renvoie "#REF!", mais il fonctionne correctement lorsque je retire la dynamique de la plage nommée formule et de définir simplement "ExampleRange10" comme statique de la gamme.
Vous pourriez peut-être utiliser une combinaison de
offset()
et de colonne(), en fonction de votre mise en page exacte.Voir modifier.
=VLOOKUP(B5,INDIRECT("range"&C1),1,0)
fonctionne très bien pour moi dans Excel 2007. Êtes-vous sûr que le reste de la formule ne contient pas d'erreurs, par exemple, 3e paramètre - numéro de la colonne dans la plage de revenir?Hein. Et votre "range10" est dynamique? J'ai vérifié mes formules et des plages, et je ne pouvais pas trouver toutes les erreurs. Lorsque je vais dans Gestionnaire de noms et cliquez sur "se Réfère à" la zone de la plage dynamique et la statique de la plage de mettre en évidence la même région, donc je ne pense pas que le problème est avec le DÉCALAGE de la formule de la gamme dynamique...et comme je l'ai mentionné avant, exactement la même formule RECHERCHEV fonctionne quand je remplacer le début de la statique de la plage de texte pour "ExampleRange", qui est le début du texte de la gamme dynamique, de sorte que la formule RECHERCHEV semble être trop...
OriginalL'auteur Kyle Wurtz | 2013-02-13
Vous devez vous connecter pour publier un commentaire.
Mieux que j'ai pu dire après avoir fait d'autres recherches, Excel
INDIRECT
fonction ne fonctionne pas avec des plages dynamiques. Il y a peut être une manière intelligente de se déplacer enINDIRECT
et le coller à la non-VBA Excel monde, mais je suis pas au courant d'une telle façon. Au lieu de cela, j'ai fini par créer une fonction définie par l'utilisateur très similaire à celle décrite ici. J'ai changé mon principal formule à lire=VLOOKUP(B2,DINDIRECT("ExampleRange"&C1),2,FALSE)
, oùDINDIRECT
est le nom de la fonction VBA que j'ai créé.Seuls inconvénients (qui peuvent ou peuvent ne pas être des inconvénients en fonction de la façon dont vous le regardez) à cette alternative est que le classeur doit être enregistré en tant que macro-enabled classeur et l'utilisation d'une fonction personnalisée n'est pas très auto-documentation et nécessite une petite explication pour les autres utilisateurs. Toutes les choses ont considéré, cependant, c'était une solution acceptable pour moi.
Pour le lien d'aversion, voici le code:
Remarque: Bien que cette solution a fonctionné, je ne vais pas accepter ma réponse parce que je ne veux pas décourager les autres de l'affichage de meilleures solutions. Aussi, je suis nouveau sur le site, donc désolé si je suis en rupture de toute étiquette codes par répondre à ma propre question...j'ai juste pensé que je devais partager la solution exacte que j'ai utilisé dans le cas où d'autres trouvent cela utile.
OriginalL'auteur Kyle Wurtz
J'ai frappé exacte de ce mur de briques récemment et la réponse, comme vous l'avez déjà deviné, c'est simplement que vous ne pouvez pas faire référence dynamique des plages nommées INDIRECTS.
Toutefois, vous pouvez utiliser la gamme dynamique de la formule lui-même INDIRECTE de l'argument, mais ce n'est pas pour ce que vous voulez faire. Un peu de pain PITA, puisque c'est le genre de fonctionnalité qui pourrait être très utile.
INDIRECT
ne fonctionne pas avec la dynamique des plages nommées. Je suis entièrement d'accord que cette fonctionnalité serait utile, et c'est particulièrement frustrant compte tenu de la simplicité de la solution de contournement est de code (donc, il devrait être très facile pour eux de les intégrer) et que, souvent, le problème vient (au moins pour moi).Exactement. Il est doublement frustrant quand vous avez à venir avec une solution qui n'utilise pas de fonctions définies par l'utilisateur (ou de VBA en quelque sorte) comme je suis souvent nécessaire de le faire.
OriginalL'auteur blackworx
Si vos données ont des en-têtes de l'ordre de 10, 20, etc., ensuite, vous n'avez pas besoin d'utiliser de façon Indirecte. Pourquoi ne pas simplement utiliser les Index/Match pour sélectionner les données dont vous avez besoin?
Nom de toute la table ExampleRanges par exemple et l'utilisation de cette formule:
OriginalL'auteur Cool Blue
Pas testé, mais je pense que cela fonctionnerait:
fonction définie par l'utilisateur à retourner à l'adresse de votre dynamiquement plage nommée:
alors vous devriez être en mesure d'utiliser votre formule recherchev:
RangeName As String
ici, et qui a bien fonctionné pour moi.OriginalL'auteur scott
Je sais que c'est assez vieux, mais je viens de tomber sur ce et j'ai pensé ajouter une solution qui évite toute VBA de codage dans le cas où il aide à quelqu'un d'autre qui bute sur ce:
C'est en supposant que la convention de nommage en cours de 10,20,30,etc et ne sera pas idéale pour des centaines de plages.
OriginalL'auteur Dave
Aujourd'hui j'ai été bricoler avec des plages nommées Excel, et j'ai découvert que, même si il est vrai que l'on ne peut pas calculer le nom de la plage dans le
INDIRECT()
appel lui-même, vous pouvez encore le faire dans un pur "Excel" par l'ajout d'une étape intermédiaire: il suffit de créer certains secrets de la cellule dans laquelle vous calculez la plage nommée.Par exemple, dire que
A1
contient la "dynamique" du nom de la gamme, puis, dansA2
utiliser la formule= "ExampleRange" & A1
, et maintenant vous avez le nom de la gamme, que vous pouvez utiliser comme= INDIRECT(A2)
.OriginalL'auteur Matteo Tassinari
L'ajout d'un nouveau tournant, il est possible d'utiliser une plage nommée, avec l'Adresse et Indirects fonctions. J'ai un cas où je suis en train de plages nommées pour une série de tableaux et je suis en utilisant les éléments suivants:
pour obtenir l'adresse:
$T$56: =ADRESSE(MATCH(S56,Linéaire!Un:,0),1,1,1,"Linéaire")
Puis à l'aide de la fonction offset copié plusieurs fois pour créer un tableau croisé dynamique:
Donc, l'Adresse de la fonction peut être incorporée (ou retour à la ligne) dans le Indirectes de fonction pour créer une dynamique de l'adresse de la cellule.
OriginalL'auteur rgo
Je sais que c'est un très vieux thread, mais j'ai eu le même problème, alors peut-être que ma solution peut aider les gens dans le futur.
En gros, j'ai créé une Macro qui permettrait de supprimer et de re-définir la plage à enregistrer, et de lui donner un nom. Par conséquent, l'INDIRECTS fonction du travail que la plage n'était pas dynamique. Tout ce que vous devez faire est de sauvegarder le classeur après l'ajout de toutes les valeurs à l'plages nommées
OriginalL'auteur DavePenn