Excel-Quickies (Vol 48) 6

Drei kleine Excel-Tipps, die dein Anwenderleben etwas leichter machen können
 

Das Leben ist manchmal auch ohne Excel schon schwer genug. Dabei wären manche Aufgaben mit diesem tollen Programm ganz einfach zu lösen. Wenn man halt nur wüsste, wie.

In den heutigen Excel-Quickies zeige ich dir Tipps zu folgenden Themen:

  • Eine eigene Tabellenformatvorlage in eine andere Arbeitsmappe übertragen
  • Einen dynamischen 4-Wochen-Kalender mit einer einzigen Formel erstellen
  • Bei einer Eingabeprüfung nur bestimmte Großbuchstaben erlauben

Da ist sicher auch für dich etwas dabei.

Excel-Quickie Nr. 154: Eine eigene Tabellenformatvorlage in eine andere Arbeitsmappe übertragen

Wie du vielleicht weißt, kann man aus den vorgegebenen Tabellenformatvorlagen eine eigene Vorlage erstellen und diese danach an seine Vorstellungen anpassen. Dazu sucht man sich eine Vorlage aus, die möglichst nah am gewünschten Layout dran ist und dupliziert sie mit einem Rechtsklick:

Eine vorhandene Formatvorlage duplizieren

Eine vorhandene Formatvorlage duplizieren


Danach taucht die neue Vorlage ganz oben unter der Kategorie „Benutzerdefiniert auf“, wo man sie nach Belieben ändern kann…
Die duplizierte Vorlage kann geändert werden...

Die duplizierte Vorlage kann geändert werden…


… bis sie eben dem gewünschten Ergebnis entspricht:
... bis das Ergebnis passt

… bis das Ergebnis passt

Aber wie bekomme ich diese Vorlage jetzt in eine andere Arbeitsmappe? Schließlich möchte man sich die Arbeit ja nur einmal machen. So sehr man die Menüs auch durchforstet und Rechtsklicke bemüht, es findet sich keine Funktion dafür.

Dabei geht es ganz einfach. Man muss nur die Tabelle, die man mit der gewünschten Vorlage formatiert hat, temporär in die neue Arbeitsmappe kopieren. Dadurch wird auch die Formatvorlage übertragen. Und sie bleibt auch dann erhalten, wenn man die kopierte Tabelle danach wieder löscht:

Vorlage per Copy & Paste übernehmen

Vorlage per Copy & Paste übernehmen

Excel-Quickie Nr. 155: Einen dynamischen 4-Wochen-Kalender mit einer einzigen Formel erstellen

Frage: Kann man mit nur einer einzigen Formel einen dynamischen Kalender für 4 Wochen erstellen?
Antwort: Ja, wenn man Microsoft 365/Office 365 nutzt und die dort verfügbare SEQUENZ-Funktion kennt.

Allgemein gesprochen gibt die SEQUENZ-Funktion einfach eine Sequenz von Zahlen wieder. Die Syntax ist sehr einfach:
=SEQUENZ(Zeilen;[Spalten];[Anfang];[Schritt])
Nur der erste Parameter ist zwingend notwendig, die drei anderen sind optional. Gibt man beispielsweise =SEQUENZ(4;7) ein, wird eine fortlaufende Zahlenreihe über 4 Zeilen und 7 Spalten erstellt:

Die SEQUENZ-Funktion

Die SEQUENZ-Funktion


Da es sich bei dieser Funktion um eine dynamische Arrayfunktion handelt, muss man die Formel nur in eine einzige Zelle eingeben und sie „dehnt“ automatisch auf so viele Zellen aus, wie für das Ergebnis notwendig sind.

Wenn man jetzt noch ein Datum als Startwert angibt, dann hat man fast schon seinen Kalender:

Zusätzlich mit Angabe eines Startwertes

Zusätzlich mit Angabe eines Startwertes


Man muss nur noch das Zahlenformat in ein Datumsformat ändern:
Das Zahlenformat als Datum einstellen

Das Zahlenformat als Datum einstellen

Es gibt allerdings noch einen Schönheitsfehler. Denn ein „richtiger“ Kalender beginnt immer mit dem gleichen Wochentag, während unsere Version immer mit dem angegebenen Startdatum beginnt. Mit Hilfe der WOCHENTAG-Funktion lässt sich aber auch dieses Problem lösen:
=SEQUENZ(4;7;B1-(WOCHENTAG(B1;11))+1)

Immer an einem Montag beginnen

Immer an einem Montag beginnen


Die Formel bestimmt den laufenden Wochentag des Datums in B1 und zieht diesen Wert vom Datum ab. Und wenn man den Montag als ersten Tag haben möchte, addiert man noch wie in meinem Beispiel den Wert 1. Soll jede Woche am Dienstag beginnen, dann addiert man den Wert 2.

Fertig!

Excel-Quickie Nr. 156: Bei einer Eingabeprüfung nur bestimmte Großbuchstaben erlauben

Dieser Quickie geht wieder auf eine Leserfrage zurück. Die Aufgabe: Wie kann man mit einer Datenüberprüfung sicherstellen, dass in einer Zelle nur ganz die Großbuchstaben A oder B eingegeben werden können. Aber eben nicht als Dropdown-Liste, sondern als freie Eingabe.

Der erste Versuch war, die erlaubten Großbuchstaben mit einer einfachen ODER-Funktion einzuschränken:

Datenüberprüfung auf Großbuchstaben (fehlerhaft)

Datenüberprüfung auf Großbuchstaben (fehlerhaft)

Das Ergebnis ist leider enttäuschend. Es werden damit zwar alle anderen Zeichen verhindert, aber Excel erlaubt trotzdem auch die Eingabe als Kleinbuchstaben:

Auch Kleinbuchstaben werden akzeptiert

Auch Kleinbuchstaben werden akzeptiert

Die Lösung führt über die IDENTISCH-Funktion. Denn die berücksichtigt auch Groß- und Kleinschreibung:
=ODER(IDENTISCH(A1;"A");IDENTISCH(A1;"B"))

Korrekte Version mit der IDENTISCH-Funktion

Korrekte Version mit der IDENTISCH-Funktion

Datenprüfung akzeptiert nur Großschreibung

Datenprüfung akzeptiert nur Großschreibung

Wer’s nicht glaubt, kann einfach den Vergleich direkt in der Tabelle machen:

Unterschied zwischen einfachem Vergleich und IDENTSICH

Unterschied zwischen einfachem Vergleich und IDENTSICH

 

So, das war’s wieder für heute. Viel Spaß beim Ausprobieren!

Wenn dir der Artikel gefallen hat: Bitte weitersagen!

Das könnte dich auch interessieren:

Und immer daran denken: Excel beißt nicht!

P.S. Die Lösung ist immer einfach. Man muss sie nur finden.
(Alexander Solschenizyn)

P.P.S. Das Problem sitzt meistens vor dem Computer.



Schreibe einen Kommentar zu Dieter Fettel Antworten abbrechen

Deine E-Mail-Adresse wird nicht veröffentlicht. Erforderliche Felder sind mit * markiert.

6 Gedanken zu “Excel-Quickies (Vol 48)

  • Dieter Fettel

    Hoi Martin
    Danke für Deine Quick Tipps, die mir immer wieder Inspriation sind, auch wenn ich Excel heute, mit 61, nicht mehr so intensiv nutze und wohl das Meiste schon wieder vergessen habe.
    Für mich neu, ich hab das noch nie gebraucht, ist die Funktion SEQUENZ() bei der mir nur noch der Hinweis auf die Kombi mit der Bedingte Fomatierung und der Regel =ODER(WOCHENTAG(B3;2)=6;WOCHENTAG(B3;2)=7) gefehlt hat, mit welcher man die WE hervorhebn kann … 🙂

    • Martin Weiß Autor des Beitrags

      Hallo Dieter,

      freut mich, wenn ich etwas Neues vermitteln konnte. Und ja, mit der bedingten Formatierung wird es natürlich perfekt 🙂
      Danke für die Ergänzung.

      Schöne Grüße,
      Martin

  • Peter und Paul

    Hallo Martin, die Sache mit der Funktion IDENTISCH reicht mir nicht ganz. Wir suchen folgende Möglichkeit:
    Einfügen muss ich ca. 18 verschiedene Kenn-Buchstaben, um eine flüssige Schreibweise zu erhalten möchte ich die Buchstaben als „kleine Buchstaben“ eingeben, die dann automatisch in Großbuchstaben umgewandelt werden sollen! Damit würde die nervige „Umschaltung“ entfallen.
    MfG Peter und Paul

    • Martin Weiß Autor des Beitrags

      Hallo Peter und Paul,

      eine automatische Umwandlung ist leider nicht möglich (zumindest nicht ohne VBA-Programmierung). Ein möglich Workaround wäre, in der Eingabezelle nur die erlaubten Buchstaben zuzulassen (in beiden Schreibweisen) und dann in einer Zelle daneben alles per GROSS-Funktion in Großbuchstaben umzuwandeln.

      Schöne Grüße,
      Martin

  • Carola Lück

    Tabellenformat-Vorlage lässt sich auch übertragen, indem man die Datei – ohne Daten als Vorlage speichert.
    Vielleicht gibt’s da schon eine, die ohnehin alle eigenen Formate enthält. 😉
    Selbes Verfahren, wie für alle Formatvorlagen.

    • Martin Weiß Autor des Beitrags

      Hallo Carola,

      das stimmt, eine eigene Vorlage ist natürlich die eleganteste Lösung.

      Schöne Grüße,
      Martin