Serienbrief - keine Bereichsnamen verfügbar

Hallo S. Hofmann,

Da wir nicht ganz sicher sind ob wir Deinen Sachverhalt 100% korrekt verstehen, bitten wir Dich das Problem zu reproduzieren und dies via dem

Step-Recorder aufzunehmen. Ich habe Dir soeben auch eine private Nachricht geschickt, auf diese Du mit der Datei antworten kannst als Anhang. Bitte vergiss aber nicht danach auch hier im Thread zu bestätigen dass Du uns
die Datei geschickt hast, denn sonst erhalten wir keine Benachrichtigung darüber. Zudem: Bitte bestätige auch Deine Windows Version.

Wir freuen uns auf Deine Rückmeldung!

Liebe Grüße,

Agnethe

Hallo Gert,

eine Pivot-Tabelle lässt sich sehr einfach den Eingabedaten anpassen, wenn man schon beim Erstellen der Original-Tabelle einen dynamischen Bereichsnamen verpasst und diesen Bereichsnamen dann als Quelle für die Pivot-Tabelle nimmt.

Mit freundlichen Grüßen

Die zweite Liste kann automatisch gewählt werden entweder über SVERWEIS oder über einen Bereichsnamen und der Funktion INDIREKT, den Du der Liste vorher gibst.
Weil ich Dir keinen Link schicken kann, hier meine Anleitung als Text: (Ich hoffe Du kannst folgen, weil alle Abbildungen fehlen).
Ich denke, genau wie in der anderen Antwort, dass es SVERWEIS sein wird, ggf. ginge auch INDEX mit VERGLEICH oder XVERWEIS.
Listen, die sich dynamisch erweitern lassen
Klar können sich die Inhalte von Listen durch Neuerungen leicht mal ändern oder sogar erweitern. Bei der beschriebenen Form der Listen ist es relativ aufwändig, diese zu ändern. Besonders dann, wenn Ergänzungen vorgenommen werden müssen.
Deshalb gibt es im jüngeren Excel weitere Verbesserungen.
Wir brauchen eine Kombination aus formatierter Tabelle und einem Bereichsnamen.
Was ist eine formatierte Tabelle?
Eine formatierte Tabelle ist ein Datenbereich, den Excel über einen Tabellennamen
erkennt und in dem Datenlisten einfach und sicher verarbeitet werden können.
Für unsere Datenliste heißt dies, dass die eine Spalte der Liste als Tabelle formatiert
wird.
Dafür wird die Liste mit einem Klick auf eine Zelle markiert und
Start --> Formatvorlagen --> Als Tabelle formatieren
gewählt. Eines der bunten Modelle wird gewählt und die Abfrage von Excel nach dem Datenbereich und der Überschrift richtig bestätigt.
Unsere Listengrundlage hat einen Datenbereich und eine Überschrift. Der Datenbereich wird vom Rechenspeicher mit dem Fließrand
markiert, die Überschrift wird extra abgefragt und muss mit dem Häkchen bestätigt werden. Wird die Überschrift nicht bestätigt, erstellt die Tabelle eine neutrale Überschrift mit dem
Namen „Spalte 1“. Das macht selten Sinn.
Die Tabelle hat eigene Tools, es ist immer gut der Tabellen einen Namen zu geben. Danach kann sie in der gesamten Mappe über das Namenfeld und in
Funktionen gefunden werden. Die Beispieltabelle heißt „tab_Mitarbeitende“. Das Präfix „tab_“ ist sinnvoll, um die Tabellennamen von den Bereichsnamen zu unterscheiden. Wenn die Liste als Tabelle formatiert ist, erhält sie zusätzlich einen Bereichsnamen.
Was ist ein Bereichsname?
Ein Bereichsname ist eine umgangssprachliche Bezeichnung für einen Excel-Bereich. Diese Bereichsnamen sind in der Regel, vergleichbar den absoluten Bezügen, auf einen bestimmten Bereich festgelegt. So aber nicht in einer formatierten Tabelle!
Diese nimmt den Bereichsnamen bei Erweiterung der Tabelle um neue Zeilen einfach mit. Damit werden unsere Listen dynamisch und lassen
sich erweitern oder auch reduzieren. So wird der Bereichsname gegeben Wir markieren mit einem Klick in die Überschrift einer Spalte der Tabelle die Daten der Tabelle. In dem Namenfeld überschreiben wir den dort jetzt erscheinenden Tabellennamen mit „Mitarbeitende“.
Bei Klick auf den Bereichsnamen werden die Daten der Tabelle bzw. der Liste markiert. Bei Klick auf den Namen der Tabelle ebenfalls.
Das macht nichts, wir erstellen jetzt auf der Basis des Bereichsnamens die dynamische Liste. Dabei werden wir die Bereichsnamen mit der
Funktionstaste aufrufen.
Der Namensmanager
Alle Namen, die in Excel gegeben werden, werden im Namensmanager verwaltet. Wir
finden ihn unter
Formeln --> Definierte Namen --> Namensmanager
Im Namensmanager werden sowohl die Tabellen- als auch die Bereichsnamen verwaltet. Tabellennamen lassen sich nicht löschen. Sie sind ein Muss in Excel, vergleichbar den Blattnamen.
Bereichsnamen hingegen lassen sich löschen. Bereichsnamen sind zuerst – vergleichbar den Tabellennamen – in der
gesamten Arbeitsmappe gültig.
Ausnahme: Wenn wir ein Blatt mit Bereichsnamen kopieren, werden in der Kopie des Blattes die Bereichsnamen auf das Arbeitsblatt bezogen und nicht mehr auf die Arbeitsmappe.
Eine Liste auf der Basis eines Bereichsnamens erstellen
Die Liste wird genau wie vor erstellt. Die Quelle der Liste ist aber der Bereichsname in der formatierten Tabelle.
Wenn in der Datenüberprüfung „Liste“ gewählt und die Datenquelle markiert wurde, werden die Namen mit aufgerufen.
Der Name wird mit „OK“ eingefügt und danach die Liste mit „OK“ bestätigt. Jetzt haben wir eine dynamische Liste
erstellt. Also probieren wir es aus: Werden Namen aus der Liste gelöscht, wird die Liste kürzer, werden (nur
innerhalb der Tabelle) Namen hinzugefügt, erweitert sich die Liste.
Eine Liste mit den Funktionen „Indirekt“ und „SVerweis“ optimieren
Mit der Funktion „Indirekt“ können direkte Verweise auf Zellen, Blätter oder auch Bereichsnamen indirekt verwendet werden. In der Beispielliste werden
so Datenquellen für Listen getauscht.
Mit SVERWEIS kann eine zweite Spalte in einer Datenquelle dynamisch zugewiesen werden. Beides kann sehr praktisch sein.
Die Datenquellen der Listen
Im Beispiel soll ein Einsatzplan von Mitarbeitenden erstellt werden, dazu sollen die Tagessätze berechnet werden.
Einsatzpläne können sehr unterschiedlich aussehen, dieser ist stark vereinfacht für die Abrechnung.
Tabellen- und Bereichsnamen in den Datenquellen
Die einspaltigen Tabellen haben jeweils den gleichen Bereichsnamen wie Tabellennamen. In der zweispaltigen Tabelle ist für die Datenquelle der Liste nur die 1. Spalte mit einem Bereichsnamen versehen. In der Abbildung ist der Bereichsname „Leistung“ markiert
Erstellen des Einsatzplans für die Abrechnung der Tagessätze
Der Einsatzplan ohne Listen ist eine formatierte Exceltabelle mit dem Namen „tab_Personalressourcen“.
Die Ergebniszeile ist mit der Funktion „Summe“ eingeblendet.
Die Listen erstellen
In der ersten Spalte wird die Liste eingetippt. Hier ist wichtig, dass die eingetragenen Bezeichnungen exakt den Bereichsnamen entsprechen. So können die Bereichsnamen mit der Funktion „indirekt“ später eingelesen werden.
Beim Kopieren der Zelle mit der erstellten Liste darauf achten, dass nur die Listen ohne Formatierung eingefügt werden.
Die Funktion „Indirekt“ verwenden, um zwei Listen zuzuordnen.
Die Projektnamen in der ersten Spalte entsprechen den Bereichsnamen für die Mitarbeitenden in den ersten beiden Datenquellen.
Im Bereich „Liste“ wird jetzt die Funktion „Indirekt“ als Datenquelle verwendet, damit die Bereichsnamen aus der ersten Spalte indirekt eingelesen werden. Damit ist es möglich die Mitarbeitenden für die Serverumstellungsprojekte von denen der Cloudprojekte zu unterscheiden.
Bei Eingabe von „Serverumstellung“ in der ersten Spalte erscheinen jetzt andere Mitarbeitende als bei der Eingabe von „Cloud“.
In der Spalte „Leistung“ wird die Liste über den Bereichsnamen „Leistung“ zugewiesen.
SVERWEIS verwenden, um die Leistung mit dem Tagessatz zu verknüpfen
Die Leistungen sind in festen Tagessätzen definiert. Deshalb werden die Tagessätze mit SVERWEIS eingetragen. Bei Auswahl einer Leistung wird der Tagessatz sofort erscheinen.
Da Leistung in der ersten Spalte der Tabelle „Tab_Leistung“ steht, kann SVERWEIS den Eintrag „Leistung“ verwenden, um die 2. Spalte „Tagessatz“
einzulesen.
SVERWEIS ergibt den Fehler „#N/V“ (Nicht Vorhanden) wenn kein Eintrag in der Spalte „Leistung“ steht.
Deshalb wird der Fehler mit der Funktion „Wennfehler“ ausgeblendet.
Die Funktion Wennfehler enthält den SVERWEIS und im zweiten Argument zwei Anführungszeichen zum Leerlassen der Zelle in der Spalte „Tagessatz“, falls keine Leistung eingetragen wird.
Die Spalten „Anzahl Tage“ und „Kosten“
Die Anzahl der Tage müssen händisch eingetragen werden, je nach Umfang der Leistung. Die Spalte Kosten enthält die Multiplikation des Tagessatzes und der Anzahl der Tage.
Auch bei der Multiplikation leerer Felder sorgt die Funktion „Wennfehler“ für leere Zellen.
Vielleicht kannst Du einen kleinen Teil davon verwenden. Meiner Erfahrung nach, werden die Listen sehr stabil, wenn formatierte Tabellen und ein Bereichsname auf einer der Tabellenspalten (der für die Liste) verwendet wird. Zusätzlich lassen sich dann die Listen automatisch erweitern, weil die formatierte Tabelle den Bereichsnamen und damit den Namensbereich der Liste automatisch erweitert.
Freundliche Grüße
Viola

Hallo Robert,

erstelle einen Bereichsnamen für die Zutaten in Tabelle1 und verwende diesen als Quelle für die Datengültigkeit. Die anderen Werte kannst du dann mit SVERWEIS aus Tabelle1 holen.

Wie der Bereichsname, die Datengültigkeit und die Formeln erstellt werden, kannst du in dieser Beispielmappe sehen:

https://1drv.ms/x/s!AqMiGBK2qniTgcMgb3ARNWdDO8I_WA

Claus

Vielen Dank!

Edit: Falls es jemanden interessiert:

=WENNFEHLER(WENN(ARCTAN2(deltax.1;deltay.1)*200/PI()>=0;ARCTAN2(deltax.1;deltay.1)*200/PI();ARCTAN2(deltax.1;deltay.1)*200/PI()+400);"")

und man erhält nur positive Winkel.

Vielen Dank nochmal!