Formeln

In dieser Themengruppe sind Anleitungen zur Anwendung von einzelnen Formeln eingeordnet.

SVERWEIS verbessern

Roman Formeln , , , , , ,

In diesem Beitrag möchte ich Dir zeigen, wie Du Deinen SVERWEIS verbessern kannst. Bei etwaigen Änderungen in der Datenquelle soll unsere Formel weniger fehleranfällig sein. Wir fokussieren uns dabei auf den sogenannten "Spaltenindex". Zum Anfang hier einmal die einzelnen Argumente der Formel "SVERWEIS" im Überblick:

Spaltenindex

Der Spaltenindex ist also das dritte Kriterium. Zur Illustration verwenden wir die nachfolgende Auflistung von Tieren in unserem Zoo:

ZooDaten

Mit einem SVERWEIS können wir demzufolge die Anzahl eines bestimmten Tieres abrufen. In dieser Formel würde eine "2" als Spaltenindex angegeben. Soweit so gut. Falls nun jedoch zwischen den Spalten "Tier" und "Anzahl" eine weitere Spalte "Standort" eingefügt wird, gibt unser SVERWEIS leider nicht mehr die Anzahl sondern eben diesen Standort aus - der Spaltenindex wird nicht automatisch auf 3 geändert. Wir können uns in dieser Hinsicht absichern und den SVERWEIS verbessern, indem wir beim Spaltenindex die Formel "SPALTEN" anwenden. Konkret: Als Matrix (Datenquelle) wird beispielsweise "A:B" verwendet. Demnach lautet die Formel für den Index "SPALTEN(A:B)". Fügst Du nun eine neue Spalte in der Mitte der Datenquelle ein, wird der Bezug direkt auf "A:C" erweitert und Dein SVERWEIS liefert stets den gewünschten Output.

P.S.: Beim WVERWEIS verwendest Du ganz simpel die Formel "=ZEILEN".

Einen eingefärbten Kalender erstellen

Roman Allgemein, Formeln , , , , , , , , , , , , , ,

Wer kennt das nicht: Es beginnt ein neues Kalenderjahr und sämtliche Dateien wie Personaleinsatzplanung, Ferienpläne, Projektübersichten, Terminlisten, etc. müssen für das neue Jahr aufgesetzt werden. In diesem Blog zeige ich Dir, wie Du dies mithilfe von vereinzelten Formeln sowie bedingten Formatierungen ganz schnell hinbringst!

Du kannst Dir dafür gerne direkt diese Datei (Farbiger_Kalender.xlsx) herunterladen. Alternativ kannst Du nachfolgendes Bild selbst nachkonstruieren (Beschreibungen dazu folgen unmittelbar):

In der Zelle "B1" gibst Du das gewünschte Jahr ein, in "B2" den entsprechenden Monat. Unterhalb wird Dir mittels der Formel "DATUM" die Kombination aus Tag (fortlaufende Nummer im Bereich  "D3:D33") sowie dem Monat und dem Jahr in "B2" respektive "B1" erstellt. Daraus können wir in der Spalte "E" mittels der Formel "TEXT" den jeweiligen Wochentag ermitteln (im Beitrag Dynamische Titel (Formel "TEXT") haben wir bereits angeschaut, wie man den Wochentag von einem beliebigen Datum auslesen kann).

Hier zusammengefasst noch einmal die bereits erwähnten Formeln:

[In der Zelle "B3" und dann entsprechend angewendet auf "B3:B33"]
=DATUM($B$1;C3;D3)
[In der Zelle "C3" und dann entsprechend angewendet auf "C3:C33"]
=MONAT(1&$B$2)
[In der Zelle "E3" und dann entsprechend angewendet auf "E3:E33"]
=TEXT(B3;"TTTT")

Die bedingte Formatierung zur farblichen Hervorhebung von Samstagen und Sonntagen

Das einzige, was jetzt noch fehlt, ist die Markierung von den Wochenenden. Hierzu verwenden wir bedingte Formatierungen. Falls Du die oben bereitgestellte Datei verwendest, klicke beispielsweise die Zelle "E3" an und gehe dann via "Start" in der Menüleiste auf "Bedingte Formatierung" und dort auf "Regeln verwalten...". Auf den Bereich "$D$3:$K$33" wenden wir zwei Formeln an. Falls der jeweilige Wert in der Spalte "E" ein Samstag respektive Sonntag ist, werden die Zellen von "D" bis "K" grau eingefärbt. Weitere Informationen findest Du, wenn Du auf "Regel bearbeiten" klickst.

Wie erstellt man man so eine Formel selbst? Falls Du das gerne nachkonstruieren möchtest, gehst Du wie folgt vor: Selektiere zum Beispiel die Zelle "E3" und gehe dann via "Start" → "Bedingte Formatierung" auf "Neue Regel...". Nimm dann die Einstellungen in der Grafik unten vor. Diese sind: Wähle einerseits "Formel zur Ermittlung der zu formatierenden Zellen verwenden" als Regeltyp aus und gebe dann folgende Formel ein:

=$E3="Samstag"

Danach kannst Du via dem Button "Formatieren..." dein gewünschtes Format auswählen.

Formatierungsregel

Wichtig zu erwähnen ist an dieser Stelle, dass der Bezug unbedingt "$E3" sein muss. Wir setzen dadurch die Spalte "E" absolut. Dies bedeutet, dass die Regel in beispielsweise der Zelle "K3" ebenfalls überprüft, ob eben in dieser Zelle "E3" "Samstag" steht. Die Zeilennummer ist nicht fixiert, was bedeutet, dass in der Zelle "K6" zu "E6" referenziert wird. Nach dem Erstellen dieser Regel können wir via "Start" → "Bedingte Formatierung" → "Regeln verwalten..." bei "Wird angewendet auf" den Bereich =$D$3:$K$33 eintragen. Diese Schritte wiederholst Du dann für Sonntag, indem Du eine weitere Regel anlegst.

Falls Du weniger oder mehr Spalten eingefärbt haben möchtest (da Du beispielsweise mehrere Mitarbeitende oder Projekte erfassen möchtest), kannst Du einfach Deine Regeln verwalten und den Bereich von =$D$3:$K$33 auf =$X$3:$X$33 erweitern!

... und nun kannst Du in den Zellen "B1" und "B2" andere Monate und Jahre eingeben und hast direkt die Wochenende eingefärbt!

Wie kann ich das nun verwenden?

Diese Frage ist natürlich abhängig davon, was Du genau für eine Anwendung dafür hast. Falls Du nicht alle Informationen von diesem Tabellenblatt beibehalten möchtest, kannst Du entweder vereinzelte Zellen/Spalten ausblenden oder wie folgt vorgehen: Der einfachste Weg, damit Du diese Datei verwenden kannst, ist, den gewünschten Monat auszuwählen und dann mit einem Rechtsklick auf das Tabellenblatt via "Verschieben oder kopieren..." eine Kopie zu erstellen. Wenn Du nun den Bereich "B3:E33" auswählst und kopierst und im Anschluss darüber nur "Werte" einfügst, bleiben die Formatierungen erhalten! Du kannst die übrigen Spalten "A" bis "C" sowie die Zeilen eins und zwei löschen. Danach wiederholst Du den Vorgang für weitere Monate/Jahre. Zum Beispiel für den Monat Februar wirst Du zusätzliche vorige Zeilen unterhalb haben, diese kannst Du natürlich ebenfalls entfernen.

P.S.: Falls Du eine englische Version von Excel verwendest, müsstest Du selbstverständlich die Monate in "B2" auf Englisch eingeben, die Formel "TEXT" leicht anpassen ("TTTT" wird zu "DDDD") und die bedingte Formatierung von "Samstag" auf "Saturday" und von "Sonntag" auf "Sunday" ändern.

SVERWEIS nach links

Roman Formeln , , , , , , ,

Mit der Formel "SVERWEIS" wird ja bekanntlich die erste Spalte Deiner Matrix nach Deinem Suchkriterium durchsucht. Sobald ein Treffer gefunden wurde, wird der Wert aus derjenigen Spalte laut Deines "Spaltenindex" ausgegeben. Schauen wir uns hierzu ein konkretes Beispiel an:

SVERWEIS1

Suchen wir also nach "Eisbär" und verwenden als Spaltenindex die 2, wird "Arktis" als Ergebnis unserer Formel angegeben. Die Formel "SVERWEIS" verläuft also stets nach rechts. In diesem Beitrag möchten wir jedoch eine Möglichkeit anschauen, wie wir beispielsweise nach "Arktis" suchen können und dann "Eisbär" als Resultat erhalten - also einen "SVERWEIS nach links"!

Folgendes können wir im Vornherein definieren: Unsere Suchspalte ist in diesem Fall B, die Ergebnisspalte ist A. Unser Suchbegriff lautet "Arktis". Wir verwenden nun jedoch nicht die Formel "SVERWEIS" (Einen negativen Spaltenindex wie beispielsweise -1 wird leider nicht toleriert.) sondern die flexiblere Version "=INDEX". Anders als bei der herkömmlichen Formel müssen wir zuerst die Ergebnisspalte definieren - "A:A". Für die Ermittlung der korrekten Zeile verwenden wir "VERGLEICH("Arktis";B:B;0)". Zusammengesetzt und vollständig lautet die Formel demnach:

=INDEX(A:A;VERGLEICH("Arktis";B:B;0))

... und das Resultat: "Eisbär"! Mittels diesen beiden Formeln kannst Du also auch eine Suche nach links durchführen.

Hattest Du schon einmal nach dieser Methode gesucht oder gar Deine Tabelle umgestellt, damit Du nach wie vor den "SVERWEIS" anwenden konntest? Fragen oder Anregungen kannst Du wie immer sehr gerne in die Kommentare schreiben.