Startseite

Outlook | Excel | Word | PowerPoint | Adobe Acrobat DC | Blog

XING | Facebook |


Pia Bork
Training, Support, Coaching für Office und Filesite


 

Impressum und Datenschutz

Creative Commons License  

Tipps und Tricks rund um Excel

A B C D E F G H I/J K
L M N O P/Q R S T U-W X-Z

 

Blattname verwenden

In einer Arbeitsmappe liegen pro Monat separate Tabellenblätter vor, die nach den Monaten benannt sind: Januar, Februar und so fort. Ziel war es, eine Formel zu entwickeln, die anhand des Blattnamens automatisch eine Datumsreihe für diesen Monat erzeugt.

Aus dem Blattnamen "Januar" soll automatisch die Datumsreihe für den Januar erstellt werden. Wenn ich das Blatt kopiere und "Februar" als Blattname verwende, soll sich die Reihe automatisch für den Februar erstellen.

 

 

Vorgeben werden soll:

Vorab die Formel:

=DATUM(JAHR(HEUTE());SVERWEIS(RECHTS(ZELLE("dateiname";W999);
LÄNGE(ZELLE("dateiname";W999))-FINDEN("]";ZELLE("dateiname";W999)));
Monatsnamen!$A$1:$B$12;2;FALSCH);ZEILE())

Woraus besteht die Formel?

Als Hilfstabelle benötige ich eine Auflistung aller 12 Monate mit den Ziffern daneben.

Diese Tabelle bekommt den Namen "Monatsnamen". Die Monate stehen in Spalte A, die zugehörigen Ziffern in Spalte B - für den SVERWEIS ist das die zweite Spalte.

 

 

Blattnamen ermitteln

Zuerst wird der Name des Blattes herausgefunden.

Die Funktion =ZELLE("dateiname";W1000) ermittelt den kompletten Dateinamen inklusive Pfad für die aktuelle Tabelle. Es ist ratsam eine Zelle anzugeben, falls einmal mehrere Dateien geöffnet sind. Die Zelle sollte eine sein, die vermutlich nie gelöscht wird, damit es keine Fehlermeldungen gibt - darum W1000.

Da ich nur den letzten Teil brauche, muss ich von rechts her alles herauslösen, was nach der eckigen Klammer ] steht.

Mit FINDEN("]";A1) kann ich ermitteln, an welcher Position die eckige Klammer steht. Mit LÄNGE(A1) stelle ich die Gesamtlänge des Pfades fest.
Für das obige Beispiel ergibt sich jetzt die Position von 68 und eine Gesamtlänge von 74. Gesamtlänge minus Position ergeben 6 Zeichen, die ich von rechts her herauslösen muss.

Mit RECHTS(A1;6) erhalte ich den Januar.

Diese Funktionen werden zusammengebaut:

=RECHTS(A1;LÄNGE(A1)-FINDEN("]";A1))

Ich möchte allerdings nicht den gesamten Pfad in einer separaten Zelle stehen haben, also baue ich jeweils anstelle von "A1" die Funktion =ZELLE("dateiname";W1000) ein:

=RECHTS(ZELLE("dateiname";W1000);LÄNGE(ZELLE("dateiname";W1000))-FINDEN("]";ZELLE("dateiname";W1000))).

Nun kann ich auf die Formel in A1 verzichten, ich erhalte direkt den Wert "Januar".

Datum zusammenstellen

Aber mit dem Wort "Januar" kann ich kein Datum bilden, ich brauche die Ziffer "1". Jetzt kommt meine Hilfstabelle zu Ehren. Über SVERWEIS kann ich zum Begriff "Januar" die Ziffer aus der zweiten Spalte herausfinden.

Wenn die oben erstellte Formel in A1 steht, sieht der SVERWEIS so aus:

=SVERWEIS(A1;Monatsnamen!A1:B12;2;FALSCH)

Das Argument "Falsch" wird gesetzt, weil eine genaue Übereinstimmung des Monatsnamens gefunden werden muss.

Auch hier führe ich Formeln wieder zusammen. Anstelle von "A1" im ersten Argument des SVERWEIS setze ich die Formel für die Ermittlung des Monatsnamens ein:

=SVERWEIS(RECHTS(ZELLE("dateiname";V1000);LÄNGE(ZELLE("dateiname";V1000))-FINDEN("]";ZELLE("dateiname";V1000)));Monatsnamen!A1:B12;2;FALSCH)

Ich habe jetzt in A1 die Ziffer 1 stehen und errechne mit der Funktion DATUM daraus das gewünschte Datum. DATUM erfordert zuerst das Jahr, dann den Monat, dann den Tag. Das Jahr soll immer aktuell sein - also das Jahr von HEUTE(). Begonnen werden soll die Reihe immer mit dem Tag 1.

=DATUM(JAHR(HEUTE());A1;1)

 

Erneut führe ich die Formeln zusammen - anstelle des Zellbezugs A1 für den Monat setze ich die gesamte Formel ein:

=DATUM(JAHR(HEUTE());SVERWEIS(RECHTS(ZELLE("dateiname";W1000);
LÄNGE(ZELLE("dateiname";W1000))-FINDEN("]";ZELLE("dateiname";W1000)));
Monatsnamen!A1:B12;2;FALSCH);1)

Ein Problem habe ich noch: Mit Angabe der Ziffer 1 für den ersten Tag kann ich die Formel nicht so einfach nach unten ausfüllen. Sie bleibt immer der "01." eines Monats. Sinnvoller ist es, die Tageszahl an die Zeilenzahl zu koppeln. Wenn meine Liste in der ersten Zeile beginnt (01. Januar steht in A1), dann ist die Zeilenzahl gleich dem Tag.

=ZEILE() gibt die Zeilenzahl aus und das setze ich an der letzten Stelle statt der Ziffer "1":

=DATUM(JAHR(HEUTE());SVERWEIS(RECHTS(ZELLE("dateiname";W1000);
LÄNGE(ZELLE("dateiname";W1000))-FINDEN("]";ZELLE("dateiname";W1000)));
Monatsnamen!A1:B12;2;FALSCH);ZEILE())

Wenn Sie eine Überschrift über dem Datum haben und Ihr erstes Datum in A2 beginnt, dann berechnet sich der erste Tag aus ZEILE()-1.

Noch kann ich aber die Formel nicht nach unten ausfüllen! Meine Bezüge sind relativ und würden sich ändern - also müssen die Bezüge absolut gesetzt werden, die Reihe wird nach unten ausgefüllt und damit bin ich fertig:

=DATUM(JAHR(HEUTE());SVERWEIS(RECHTS(ZELLE("dateiname";$W$1000);
LÄNGE(ZELLE("dateiname";$W$1000))-FINDEN("]";ZELLE("dateiname";$W$1000)));
Monatsnamen!$A$1:$B$12;2;FALSCH);ZEILE())

Im Beispiel zum Download finden Sie zwei Dateien:

Wenn Sie die Formeln verstehen wollen, dann bauen Sie sie so nach, wie ich es in der Entwurfstabelle vormache.

30.05.2013

Download

Beispieltabellen fertig und Entwurf (zwei Dateien, gezippt)