Graphique dynamique de la plage en utilisant INDIRECT: cette fonction n'est pas valide (en dépit de la plage surlignée)
j'essaie de créer un graphique avec une portée construite dynamiquement en utilisant le INDIRECT fonction. Excel reconnaît la plage que je crée en utilisant INDIRECT car il met en évidence la gamme correspondante sur la feuille:
cependant lors de l'enregistrement du graphique, je reçois un message d'erreur disant que la fonction n'est pas valide:
quelqu'un sait quel est le problème / comment créer une dynamique de plage du diagramme à partir d'un de début à la fin en soi?
PS: vous pouvez télécharger le tableur ci-dessus ici. La formule que j'ai été en utilisant:=INDIRECT("sheet!"&E2&":"&E3)
6 réponses
la mienne est similaire à L'excellente réponse de Sean, mais permet un début et une fin de journée. Tout d'abord, créez deux plages nommées qui utilisent des formules Index/Match pour choisir les jours de début et de fin basés sur E2 et E3:
rngDay
=INDEX(Sheet1!$A:$A,MATCH(Sheet1!$E,Sheet1!$A:$A,0)):INDEX(Sheet1!$A:$A,MATCH(Sheet1!$E,Sheet1!$A:$A,0))
rngValue
=INDEX(Sheet1!$B:$B,MATCH(Sheet1!$E,Sheet1!$A:$A,0)):INDEX(Sheet1!$B:$B,MATCH(Sheet1!$E,Sheet1!$A:$A,0))
Vous pouvez ensuite cliquer sur la série dans le tableau et modifier la formule:
=SERIES(Sheet1!$B,Sheet1!rngDay,Sheet1!rngValue,1)
Voici un beau Chandoo post sur comment utiliser plages dynamiques dans les graphiques.
La façon dont vous essayez de faire, il n'est pas possible. La plage de données doit avoir une adresse fixe.
il y a un moyen de contourner cela, et c'est en utilisant les gammes nommées
mettez le nombre de lignes que vous voulez dans vos données dans une cellule (par exemple, E1)
Donc, en utilisant votre exemple, j'ai mis Number of RowsD1 et 6 E1
dans name manager, définissez les noms de vos données et titres
J'ai utilisé xrange et yrange, et les définit comme:
xrange: =OFFSET (Sheet1!$ A 2,0,0, Sheet1!$E$1)
yrange: = OFFSET (Sheet1!$B$2,0,0,La Feuille Sheet1!$E$1)
maintenant, vers votre graphique - vous devez connaître le nom du classeur (une fois que vous l'aurez configuré, la fonction de suivi des modifications D'Excel vous permettra de vous assurer que la référence reste correcte, peu importe le nom)
Quitter Chart data range vide
pour l' Legend Entries (Series), entrez le titre comme d'habitude, et puis le nom que vous avez défini pour les données (notez que le nom du classeur est pour l'utilisation de plages nommées)
pour les Horizontal (Category) Axis Labels, entrez le nom que vous avez défini pour les étiquettes
maintenant, en changeant le nombre dans E1, vous verrez le changement de graphique:
juste une autre réponse pour les bits et googles..
si vous voulez toujours faire référence à vos cellules de début et de fin, vous devrez ajouter une formule séparée pour votre Intervalle de jour et votre Intervalle de valeurs. Les formules ci-dessous et la capture d'écran montre les formules utilisées.
Intervalle De Jours:
="Sheet1!"&$F&":"&ADDRESS(ROW(INDIRECT($F)),COLUMN(INDIRECT($F)))
Valeurs Plage:
="Sheet1!"&ADDRESS(ROW(INDIRECT($F)),COLUMN(INDIRECT($F)))&":"&$F
puis ajouter deux plages de valeurs faisant référence au INDIRECT les valeurs de ces les cellules
appuyez sur Ctrl+F3, cliquez sur Nouveau, et ajoutez une nouvelle plage avec le nom "chart_days", se référant à =INDIRECT(Sheet1!$F); et une nouvelle gamme avec le nom "chart_values", en référence à =INDIRECT(Sheet1!$F)
enfin, dans votre tableau, ajoutez une série qui fait référence à =nameOfYourWorkbook!chart_values
et éditez la catégorie pour faire référence à =nameOfYourWorkbook!chart_days
j'utilise OFFSET pour créer une formule de nom définie de sorte que je puisse définir toutes les plages pour les données, me permettant d'avoir un début une date de fin (ou la position de début et de fin de n'importe quel ensemble de données).
pour un graphique simple, je définis le nom CategoryLabels comme suit:
= OFFSET($A; (InicitialMonth-1); 0; LastMonth - (InitialMonth-1))
et DataCars comme suit:
= OFFSET($B; (InicitialMonth-1); 0; LastMonth - (InitialMonth-1))
vous devrez définir autant de noms que de séries que vous voulez inclure, en suivant la même procédure. Dans ce cas simple, je n' inclus les ventes de Voitures.
le mois Initial et le dernier mois sont des noms d'intervalle définis pour des cellules individuelles utilisées pour indiquer les mois du graphique qui seront inclus (du début à la fin des mois).
rappelez-vous, comme L'a expliqué Sean Cheshire et d'autres, que pour utiliser les noms des valeurs de la carte, le nom du tableur doit être inclus.
selon la formule que vous avez montrée: = INDIRECT ("sheet!"Et E2&":"&E3) vous ne nommez pas la feuille correctement.
j'aurais pensé que ce serait Sheet1! ou Feuil2! etc. Votre formule résout =feuille!E2:E3 qui n'est pas une adresse valide. Le message d'erreur que vous recevez signifie que Excel ne peut pas résoudre L'entrée à INDIRECT. INDIRECT une fonction valide donc l'argument que vous offrez, il doit être valide.
toutes les réponses ci-dessus qui indiquent le nom de la feuille a corrigé votre erreur mais ne la mentionnez pas... ;)
la formule nommée avec des fonctions indirectes ne fonctionne pas dans les graphiques. Il fonctionne dans d'autres froms comme source dynamique sera mis en évidence, mais quand vous en graphique, il ne serait pas évaluée. Hope Microsoft a mis un correctif à cela.