'Bereich.Verschieben' - nur für Namen geeignet?

Computer: Software & Programmierung Office Software
#1

Hallo Excel-Experten,

ich habe eine komplexe Tabelle mit mehreren Tabellenblättern, die ich neu strukturieren will, damit sie besser erweiterbar ist.

Bislang war es so, dass ein dynamischer Bereich mittels „Bereich_Verschieben“ einem Namen zugeordnet wurde, also etwa so:

My_name=BEREICH.VERSCHIEBEN(‚Result‘!$B$16;0;‚Result‘!$E$3;1;‚Result‘!$E$5)

Ich würde gern die Vergabe von Namen zukünftig weglassen und dafür den BEREICH.VERSCHIEBEN direkt in eine Zelle schreiben. Hintergrund: in meinem Sheet gehören immer drei Tabellenblätter zusammen, davon enthält eins die Daten für eine Auswertung, zwei enthalten Diagramme zur Auswertung. Diese „Tabellen-Tripel“ müssen kopierbar sein, so dass man mit ganz wenigen Änderungen in dem Auswerte-Tab automatisch die Diagramm-Tabs ändert. Dazu brauchte ich bislang Namen, aber mit den Namen wird die Tabelle sehr unübersichtlich.

Wenn ich jedoch in der noch nicht geänderten Tabelle statt mit dem Namen direkt mit der Formel arbeite, und diese also statt einem Namen zuzuordnen direkt in eine Zelle schreibe, dann ist das Ergebnis der Formel 0 und weiter passiert nichts.

Ist der Befehl „Bereich.Verschieben“ also nur für einen mit Namen benannten Bereich geeignet?

Oder mache ich mir das ganze zu kompliziert, und auch Namen würden automatisch an neue Tabs angepasst? Also so, dass der neue Name auf das neu per Kopie angehängte Tabellenblatt verweist?

Danke für jeden Hinweis und viele Grüsse,
Crypto.

#2

Hallo Crypto,

Bislang war es so, dass ein dynamischer Bereich mittels
„Bereich_Verschieben“ einem Namen zugeordnet wurde, also etwa
so:

My_name=BEREICH.VERSCHIEBEN(‚Result‘!$B$16;0;‚Result‘!$E$3;1;‚R
esult‘!$E$5)

ja, so und ähnlich wird das gemacht.

Ich würde gern die Vergabe von Namen zukünftig weglassen und
dafür den BEREICH.VERSCHIEBEN direkt in eine Zelle schreiben.

Das warum habe ich noch nicht verstanden.
Namen machen doch grad Tabellen übersichtlicher.
Die Formel muß man ur an einer Stelle bei Bedarf abändern, ansonsten
mußte ja von Formelzelle zu Formelzelle wandern und da ändern.

Wenn ich jedoch in der noch nicht geänderten Tabelle statt mit
dem Namen direkt mit der Formel arbeite, und diese also statt
einem Namen zuzuordnen direkt in eine Zelle schreibe, dann ist
das Ergebnis der Formel 0 und weiter passiert nichts.

Mir nicht verständlich. Lade bitte eine Mappe hoch wo
das so geschieht wenn du in Zelle XY anstatt des Formelnamens
die Formel einträgst. Hochladen mit dem obersten Hochladlink in
FAQ:2606
Du hast ja nur absolute Zellbezüge in der Formel, weshalb sollte
da ein Unterschied sein im Ergebnis zwischen namen bzw. Formel?

Ist der Befehl „Bereich.Verschieben“ also nur für einen mit
Namen benannten Bereich geeignet?

M.W. nein.

Oderr mache ich mir das ganze zu kompliziert, und auch Namen
würden automatisch an neue Tabs angepasst? Also so, dass der
neue Name auf das neu per Kopie angehängte Tabellenblatt
verweist?

Probier sowas doch einfach aus, dann siehste es.
Müßte ich auch machen um zu schauen was Excel in dem Fall
so mitgedacht hat.

Gruß
Reinhard

#3

Grüezi Crypto

Bislang war es so, dass ein dynamischer Bereich mittels
„Bereich_Verschieben“ einem Namen zugeordnet wurde, also etwa
so:

My_name=BEREICH.VERSCHIEBEN(‚Result‘!$B$16;0;‚Result‘!$E$3;1;‚R
esult‘!$E$5)

Ja, und das ist auch gut so und dient der Übersichtlichkeit.

Ich würde gern die Vergabe von Namen zukünftig weglassen und
dafür den BEREICH.VERSCHIEBEN direkt in eine Zelle schreiben.

Da die Funktion immer einen Bereich als Rückgabewert liefert wirst Du damit nicht ‚glücklich‘ werden, da dieser in einer Zelle nicht ausgewertet oder weiter verwendet werden kann.

Er dient ‚nur‘ als Parameter für Funktionen die einen Bereich verarbeiten können (z.B. Datenreihen in Diagrammen).

Hintergrund: in meinem Sheet gehören immer drei
Tabellenblätter zusammen, davon enthält eins die Daten für
eine Auswertung, zwei enthalten Diagramme zur Auswertung.
Diese „Tabellen-Tripel“ müssen kopierbar sein, so dass man mit
ganz wenigen Änderungen in dem Auswerte-Tab automatisch die
Diagramm-Tabs ändert. Dazu brauchte ich bislang Namen, aber
mit den Namen wird die Tabelle sehr unübersichtlich.

Da würde ich das Gegenteil behaupten - wenn die Namen ‚sprechende Namen‘ tragen wird die ganze Sache sehr viel übersichtlicher.

Wenn ich jedoch in der noch nicht geänderten Tabelle statt mit
dem Namen direkt mit der Formel arbeite, und diese also statt
einem Namen zuzuordnen direkt in eine Zelle schreibe, dann ist
das Ergebnis der Formel 0 und weiter passiert nichts.

Ja, eben - wie oben beschrieben liefert die Formel einen Bereich und ist keine Berechnung.

Ist der Befehl „Bereich.Verschieben“ also nur für einen mit
Namen benannten Bereich geeignet?

Wie oben beschrieben kannst Du sie überall dort verwenden wo ein Bereich als Parameter an eine Funktion übergeben werden soll.
Üblicherweise passiert das mit einem Bereichsnamen, damit das Ganze dann übersichtlich(er) wird, weil Du ‚sprechende Namen‘ verwenden kannst.

Oder mache ich mir das ganze zu kompliziert, und auch Namen
würden automatisch an neue Tabs angepasst? Also so, dass der
neue Name auf das neu per Kopie angehängte Tabellenblatt
verweist?

Ja, das sollte IMO klappen, wenn Du die Namen auf Tabellenblatt-Ebene und nicht auf Arbeitsmappen-Ebene anlegst - da gibt es einen klaren Unterschied. Je nach Excel-Version ist das Anlegen eines solchen Namens auf verschiedenen Ebenen ein wenig unterschiedlich - welche Version verwendest Du denn?

Noch besser wäre es allerdings, wenn der Datenbereich und das dazu gehörende Diagramm auf demselben Tabellenblatt liegen würden.

Mit freundlichen Grüssen

Thomas Ramel

  • MVP für MS-Excel -
#4

Hallo Reinhard,

wie schon so häufig ein Dankeschön an Dich für die umfangreiche Antwort.

Ich würde gern die Vergabe von Namen zukünftig weglassen und
dafür den BEREICH.VERSCHIEBEN direkt in eine Zelle schreiben.

Das warum habe ich noch nicht verstanden.
Namen machen doch grad Tabellen übersichtlicher.

Ja, ich hätte das auch so gedacht, dass Namen die Sache übersichtlicher machen.

Aber:
Ich habe mehrere Tabellenblätter, von denen immer drei zusammengehören. Das hat zur Folge, dass ich die Namen nicht auf ein einzelnes Tabellenblatt beschränken kann, da sie sonst im zweiten und dritten dazugehörigen Tabellenblatt nicht sichtbar sind (das zweite und dritte Tabellenblatt enthält jeweils die Diagramme mit der graphischen Aufbereitung der Daten auf dem ersten Tabellenblatt).

Meine Idee war nun, auf die Benamung zu verzichten und stattdessen bei der Kopie der drei Tabellenblätter (um neue Daten und Diagramme unkompliziert hinzufügen zu können) auch die BEREICH.VERSCHIEBEN-Informationen zu kopieren, ohne Namen. Aber das geht offenbar so nicht, zumindest nicht so, wie es bisher war, weil ich noch keine Formel mit einem Bereich benutzt habe, in die man den BEREICH.VERSCHIEBEN-Befehl hätte hineinkopieren können.

Mir nicht verständlich. Lade bitte eine Mappe hoch wo
das so geschieht wenn du in Zelle XY anstatt des Formelnamens
die Formel einträgst.

Leider kann ich das wiedermal nicht machen, da die Tabellen firmeninterne Daten enthalten.

Du hast ja nur absolute Zellbezüge in der Formel, weshalb
sollte
da ein Unterschied sein im Ergebnis zwischen namen bzw.
Formel?

Weil die Formel BEREICH.VERSCHIEBEN nicht ihre sämtlichen Ergebnisdaten in einer Zelle anzeigen kann. In eine Zelle passt immer nur genau eine Zahl, aber nicht ein ganzer Bereich (siehe die andere Antwort zu meinem Ursprungsposting, wo mir erklärt wurde, dass der BEREICH.VERSCHIEBEN-Befehl dann in einer anderen Formel eingesetzt werden kann, wenn diese Formel einen Bereich erwartet bzw. verarbeiten kann. Wenn nur eine Zelle diesen Befehl enthält, kommt nur „0“ oder so heraus. Anders sieht der Fall aus, wenn die BEREICH.VERSCHIEBEN-Informationen in einem Namen abgelegt werden.)

Probier sowas doch einfach aus, dann siehste es.
Müßte ich auch machen um zu schauen was Excel in dem Fall
so mitgedacht hat.

Bin ich dran, ich probiere noch herum.

Gruß
Reinhard

Grüße zurück,
Crypto.

#5

Grüezi Crypto

Ich habe mehrere Tabellenblätter, von denen immer drei
zusammengehören. Das hat zur Folge, dass ich die Namen nicht
auf ein einzelnes Tabellenblatt beschränken kann, da sie sonst
im zweiten und dritten dazugehörigen Tabellenblatt nicht
sichtbar sind (das zweite und dritte Tabellenblatt enthält
jeweils die Diagramme mit der graphischen Aufbereitung der
Daten auf dem ersten Tabellenblatt).

Du kannst diese (lokalen) Bereichsnamen aber dennoch verwenden, indem Du den Namen des Tabellenblattes voranstellst.

Mir nicht verständlich. Lade bitte eine Mappe hoch wo
das so geschieht wenn du in Zelle XY anstatt des Formelnamens
die Formel einträgst.

Leider kann ich das wiedermal nicht machen, da die Tabellen
firmeninterne Daten enthalten.

Es sollte IMO doch nicht allzu schwierig sein die Firmen-Internas zu anonymisieren und die Daten mit unverfänglichen Werten zu ersetzen.

Es geht ja nicht um die Daten und Auswertungen an sich, sondern um den prinzipiellen Aufbau - im Endeffekt tut es auch eine Demo-Mappe mit reinen Dummy-Daten, die dem Aufbau der eigentlichen Mappe möglichst nahe kommt.

Du hast ja nur absolute Zellbezüge in der Formel, weshalb
sollte
da ein Unterschied sein im Ergebnis zwischen namen bzw.
Formel?

Weil die Formel BEREICH.VERSCHIEBEN nicht ihre sämtlichen
Ergebnisdaten in einer Zelle anzeigen kann. In eine Zelle
passt immer nur genau eine Zahl, aber nicht ein ganzer Bereich
(siehe die andere Antwort zu meinem Ursprungsposting, wo mir
erklärt wurde, dass der BEREICH.VERSCHIEBEN-Befehl dann in
einer anderen Formel eingesetzt werden kann, wenn diese Formel
einen Bereich erwartet bzw. verarbeiten kann. Wenn nur eine
Zelle diesen Befehl enthält, kommt nur „0“ oder so heraus.
Anders sieht der Fall aus, wenn die
BEREICH.VERSCHIEBEN-Informationen in einem Namen abgelegt
werden.)

…da hast Du mich missverstanden - ein Bereichsname mit einer dynamischen Formel (dafür diene ja BEREICH.VERSCHIEBEN()) enthält auch nur den Range-Bereich der durch die Funktion ermittelt wird.
Ob Du also die volle Funktion oder den Bereichnamen als Äquivaltent verwendest macht für die Funktion keinen Unterschied.
Allerdings erhöht die Verwendung von Bereichsnamen die Übersichtlichkeit der Mappe enorm.

Mit freundlichen Grüssen

Thomas Ramel

  • MVP für MS-Excel -
#6

Hallo Crypto,

Weil die Formel BEREICH.VERSCHIEBEN nicht ihre sämtlichen
Ergebnisdaten in einer Zelle anzeigen kann.

nein, das so zu formulieren ist falsch.
Und laß mal die nette Funktion Bereich.Verschieben aus’m Spiel,
die hat damit nix zu tun wie alle Excelfunktionen die
keinen Wert zurückliefern sondern eine Zellenadresse.

Schreib mal in eine Zelle
=A1
dann in eine andere
=A1:A5
dann in eine andere
=A0:A0

Du erkennst, beziehst du dich auf EINE Zelle klappt das,
bei einem Bereich gehts schief, wahrscheinlich kommt #Wert o.ä.
Ergo, wenn deine Bereichsformel nur eine Zelladresse zurückliefert
so klappt das wie bei =A1

Also so, ich nehm mal deine Formel ohne Blattnamen:
=BEREICH.VERSCHIEBEN($B$16;0;$E$3;1;$E$5)

Du sagst als Ergebnis wird eine 0 angezeigt. Gut, daß bedeutet
die Formel hat nur EINE Zelladresse zurückgeliefert und deren
Wert ist halt 0.
Damit nur eine Zelleadresse zurückgeliefert wird hat die
Zelle E5 den Wert 1

Steht in E5 eine Zahl größer 1 so erhälst du wie bei
=A1:A5 die Anzeige #Wert
Steht in E5 nix oder 0 so kriegste #Bezugfehler.

Fragt sich jetzt jmd. was denn alles mit deiner Namens-Anfrage
zu tun hat, naja, rein gar nix :smile:
Mir gings um das Verständnis beim Umgang mit Funktionen
die Zelladressen zurückliefern.
Achja, ein vergebener Name für deine Formel verhält sich exakt
genauso.

=my_name
ist identisch zu
=BEREICH.VERSCHIEBEN($B$16;0;$E$3;1;$E$5)

In eine Zelle
passt immer nur genau eine Zahl, aber nicht ein ganzer Bereich

Hat mitm Reinpassen nix zu tun, das geht je nach Version bis
32xxx Zeichen oder so.
Ohne diese Begrenzung passt schon jedes anzeigbare Ergebnis
einer Formel herein.

=A1
hat ein Ergebnis, den Inhalt von A1 der angezeigt wird
=A1:A5
hat so gesehen kein Ergebnis.
=Summe(A1:A5)
=Anzahl2(A1:A5)
haben ein Ergebnis.

(siehe die andere Antwort

Merk dir den Verfasser der „anderen Antwort“, lohnt sich :smile:

erklärt wurde, dass der BEREICH.VERSCHIEBEN-Befehl dann in
einer anderen Formel eingesetzt werden kann, wenn diese Formel
einen Bereich erwartet bzw. verarbeiten kann.

Ja, como siempre richtig, s.o., Summe() ist z.B. so eine Formel.

Gruß
Reinhard