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:
- Quickie 154: Eine eigene Tabellenformatvorlage in eine andere Arbeitsmappe übertragen
- Quickie 155: Einen dynamischen 4-Wochen-Kalender mit einer einzigen Formel erstellen
- Quickie 156: 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
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…
… bis sie eben dem gewünschten Ergebnis entspricht:
… 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
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
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
Man muss nur noch das Zahlenformat in ein Datumsformat ändern:
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
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.
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)
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
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
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
So, das war’s wieder für heute. Viel Spaß beim Ausprobieren!
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.
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 … 🙂
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
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
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
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.
Hallo Carola,
das stimmt, eine eigene Vorlage ist natürlich die eleganteste Lösung.
Schöne Grüße,
Martin