Über Themen mit Q Testbericht

Schelm ist, wer hierbei Falsches denkt (gemeint ist die Überschrift) * grins, grins *.

Aufhänger dieser MS-Excel-Info waren einige Anfrage und Hilferufe und natürlich diverse - tja, wie sagt man - Danksagungen zu den bislang bereits erschienen Tipps & Tricks zu MS Excel.
Auch die Anfragen zu den „Datumsspielereien“ waren natürlich ein Kriterium.

Es spielte auch ein in meinen Augen hervorragend gemachter Bericht über die Nutzung der Wenn-Dann-Sonst-Funktion eine Rolle, die ein Community-Mitglied veröffentlicht hat.

Tja, und schlussendlich hat mich eine etwas umfangreichere Aufgabe, die mich dienstlich beschäftigt hat, dazu bewogen, wieder einmal einige Tipps, Tricks und mögliche Kniffe aufzuzeigen.

Diesmal gibt es diverse Tipps & Tricks - so hoffe ich wenigstens - in geballter Kurzform. Das hat zur Folge, das es auch nur wirkliche Kurzerklärungen dazu gibt.

Die Tipps & Tricks sind in diesem Falle nicht alphabetisch sortiert, sondern werden so angegeben, wie sie mir interessant erschienen. Ich bitte dafür um Verständnis ...

Übersicht der diversen Tipps & Tricks ...

allgemeine Anmerkung vorab:

Teilweise ist die Darstellung der Formeln bzw. die Verknüpfung der Formeln zu lang für eine ordnungsgemäße Darstellung. Aus diesem Grunde sind dann Leerzeichen und Unterstriche eingebaut worden. Diese dürfen natürlich nicht berücksichtigt werden !!
Beispiel:
korrekte Formel =WENN(A1Leerzelle“;“Berechnung“),
Darstellungsmöglichkeit =WENN _ (A1=““ _ ; “Leerzelle“; _ “Berechnung“)

nun die diverse Funktionen und Möglichkeiten ...

1. logische Fehler unterdrücken ...

2. Kommentare, schnell alle Kommentare anzeigen lassen und wieder ausblenden

3. ganz bestimmte Tage mit Hilfe der bedingten Formatierung formatieren
- Alternative a) mit der Wochentagsfunktion
- Alternative b) ggf. mit Hilfsspalten und der Wenn-Dann-Sonst-Funktion
- Alternative c) einfach durch Abzählen vom aktuellen Tag

4. Berechnung der Sommerzeit

5. Berechnung der Winterzeit

1. logische Fehler unterdrücken ...

Also ich hasse es, wenn in einem Vordruck, in dem noch keine Werte erfasst worden sind, plötzlich die sog. logischen Fehler auftauchen.
Doch zuvor eine kurz Erklärung für die Leserinnen und Leser, die mit einem logischen Fehler nichts anfangen können.
Als logischer Fehler wird eine zwar korrekt erstellte aber durch fehlende oder falsche Eingabe fehlerhafte Formel / Funktion bezeichnet.
So führt z. B. eine Division durch Null zu einem logischen Fehler. Die Formel ist zwar korrekt aufgebaut, aber die Basiswerte sind alle mit Null eingegeben worden. Tja, und Null dividiert durch Null ist halt „nüscht“ bzw. führt in MS Excel zu einem solchen logischen Fehler. Dieser Fehlercode lautet konkret #DIV/0!.

Daneben gibt es noch weitere logische Fehlercodes, die einfach durch noch fehlende oder falsche Eingaben ausgelöst werden können.
#NV bedeutet z.B., das ein erforderlicher Wert nicht verfügbar oder gar nicht vorhanden ist.
#WERT! bedeutet z.B., das ein falsches Argument verwandt worden ist
#NAME? bedeutet z. B., das Text in einer Formel nicht korrekt erkannt / interpretiert wird
#ZAHL! bedeutet z. B., das ein Problem mit einer Zahl aufgetreten ist

Es gibt noch andere ...

Um nun diese Fehler auszuschalten, konkret sie unsichtbar werden zu lassen, nutze ich eine entsprechende Funktion.

Angenommen eine Division aus den Zellinhalten der Zellen A1 und B1, dessen Ergebnis in C1 ausgegeben werden soll, führt zu einem logischen Fehler.
Die korrekte Formel würde grundsätzlich ja lauten =A1/C1

Ich „verfeinere“ diese und andere Formeln gerne wie folgt:

=WENN(ISTFEHLER(A1/B1A1/B1)

Der „Trick“ besteht einfach darin, dass die Funktion ISTFEHLER(WERT) im Falle eines logischen Fehlers den Wert WAHR ausgibt. Liegt kein Fehler vor wird der Wert auf FALSCH gesetzt.

Dies lässt sich ganz einfach wie folgt testen:
gibt man z.B. in die Zelle C1 die Formel

=ISTFEHLER(A1/B1)

ein, wird als Ergebnis dieser Division (siehe Werte in Klammern) richtig ausgegeben WAHR. Ja, die korrekte Formel verursacht einen logischen Fehler. Wird nun in beide Zellen eine gültige Zahl eingegeben erfolgt die Meldung FALSCH. Auch wieder richtig, da man ja nun korrekt dividieren konnte.

Nun zurück zum „Trick“. Durch die Wenn-Dann-Sonst-Funktion wird einfach geprüft, ob die Funktion ISTFEHLER das WAHR bei einem Fehler ausgibt. Ist dies der Fall wird einfach eine leere Zeichenfolge durch die beiden Anführungszeichen ausgegeben, ist das Ergebnis der ISTFEHLER-Funktion aber logisch FALSCH, so wird einfach die korrekte Berechnung, in diesem Fall die Division ausgeführt.

Ich kann nur sagen: einfach, klein, schnell und effizient.

2. Kommentare, schnell alle Kommentare anzeigen lassen und wieder ausblenden

Wer kennt sie nicht, die kleinen - üblicherweise - gelben Kommentare, die man an jede Zelle im Tabellenblatt „anheften“ kann und die sich nur mit einem kleinen roten, unscheinbaren Dreieck am oberen rechten Zellrand bemerkbar machen.

Wer sie wirklich nicht kennen sollte, hier die kurze Info, wie man an sie herankommt:
a)
Menü EINFÜGEN -> Kommentar
b)
Klick rechte Maustaste -> Kommentar einfügen

Dumm ist nur, dass man grundsätzlich immer nur einen Kommentar sieht, nämlich den jeweils aktiven.

Da ich relativ häufig mit Kommentaren arbeite, habe ich mir die Anzeige ALLER KOMMENTARE in eine der vorhandenen Symbolleisten geschoben.

So funktioniert das Verfahren:
a)
Im Menü EXTRAS die Option ANPASSEN aufrufen. Dort im Fenster die mittlere Registerkarte BEFEHLE aktivieren. im linken Bereich die Kategorie ANSICHT aktivieren und danach im rechten Bereich den Befehl KOMMENTARE mit gedrückter linker Maustaste in eine neue oder eine vorhandene Symbolleiste ziehen. Danach das Anpassen - Fenster schließen; das Ein- und Ausschalten aller Kommentare ist künftig kein Problem mehr.

b)
Im Menü ANSICHT die Symbolleisten auswählen. Dort in die Unterkategorie verzweigen und die Symbolleiste ÜBRARBEITEN aktivieren. Ist sie auf dem Bildschirm sichtbar bei bedrückter STRG+ALT - Taste mit der Maus das Symbol „Alle Kommentare anzeigen“ in eine gewünschte Leiste ziehen und dort „fallen lassen“. Die Ansichten - Symbolleiste kann danach wieder deaktiviert werden.

3. ganz bestimmte Tage mit Hilfe der bedingten Formatierung formatieren

- Alternative a) mit der Wochentagsfunktion
- Alternative b) mit Hilfsspalten und der Wenn-Dann-Sonst-Funktion
- Alternative c) einfach durch Abzählen vom aktuellen Tag

Man stelle sich vor, in der Spalte A befinden sich sämtliche Datumswerte des lfd. Jahres vom 01.01. bis zum 31.12.
Nun sollen alle Samstage und Sonntage farblich gekennzeichnet werden. Bekannt dürfe sein, dass die Woche in MS Excel mit Sonntag als 1. Tag beginnt und mit Samstag als 7. Tag endet.

Alternative a) mit der Wochentagsfunktion

Die erste Zelle, in der sich z.B. das Datum 01.01.2008 befindet, wird markiert. Nun muss die Bedingte Formatierung aufgerufen und dort folgende Auswahl getroffen werden:
In Bedingung 1 wird ausgewählt

1. Formel ist

die einzutragende Formel lautet z.B.

2. =WOCHENTAG(A1)=1

die Formatierung der Zelle soll im Falle des Zutreffens rot werden.

In Bedingung 2 wird ausgewählt

1. Formel ist

die einzutragende Formel lautet z.B.

2. =WOCHENTAG(A1)=2

die Formatierung der Zelle soll im Falle des Zutreffens Gelb werden.

In beiden Fällen ist auf die absolute Adressierung zu achten (also die Dollarzeichen vor der Spalten- und Zeilenadresse entfernen damit nachher z.B. mit Hilfe des Formatübertragungspinsels diese Formatierungen auf die restlichen Zellen übertragen werden können.

Eine Alternative könnte genutzt werden, in dem eine Hilfszelle (z. B. B1) für eine freie Eingabe der Wochentage herhalten kann.

In diesem Falle würde die bedingte Formatierung wie folgt aussehen:

In Bedingung 1 wird ausgewählt

=WOCHENTAG(A1)=$B$1

die Formatierung der Zelle soll im Falle des Zutreffens rot werden. Erfolgt eine Eingabe in Zelle B1 werden die entsprechenden Zellen, die diesem Ergebnis entsprechen, entsprechend formatiert.

Alternative b) ggf. mit Hilfsspalten und der Wenn-Dann-Sonst-Funktion

Während die erste Variante eigentlich nur für Wochentage gut ist, kann mit Hilfe der Wenn-Dann-Sonst-Funktion auch entsprechend gearbeitet werden.
Benötigt werden z. B. zwei Hilfsspalten (B für die Abfrage mit Wenn-Dann, C für die Eingabe des Prüfwertes). Sie sind aber nicht unbedingt erforderlich, da auch alle erforderlichen Parameter in der bedingten Formatierung untergebracht werden könnten. Nur, mit Hilfsspalten ist man flexibler.

Der Prüfwert in C1 z.B. lautet 15, weil jeder 15. Tage gekennzeichnet werden soll. Somit müsste in der bedingten Formatierung erfasst werden:

In Bedingung 1 wird ausgewählt

=Tag(A1)=15

auch möglich:

=Tag(A1)=$C$1

die Formatierung der Zelle soll im Falle des Zutreffens rot werden. Auch hier ist wieder auf absolute und freie Adressierung der Zellen zu achten.

Die Funktion TAG(PARAMETER) wird für das Herauslösen des Tages aus dem Datum benötigt.

Möglich ist auch die Verwendung der Wenn-Dann-Sonst-Funktion z. B. wie folgt:

In der Hilfsspalte B1 und den restlichen wird die Funktion erfasst, die wie folgt lauten könnte:

=WENN(TAG(A1)=$C$1;"X

Es wird bewirkt, dass - nach dem entsprechenden Kopieren - immer dann ein X in die Nachbarzelle eingetragen wird, wenn die Vorgabe, die sich in Zelle C1 verbirgt, erfüllt ist.
Die bedingte Formatierung müsste dann z.B. wie folgt lauten:

In Bedingung 1 wird ausgewählt

Formel ist =B1 ""

die Formatierung der Zelle soll im Falle des Zutreffens rot werden.

Anmerkung:
Ich selbst nutze gerne den Ungleich-Operator , da durch die Abfrage „gleich =“ ggf. zu viele Ungereimtheiten und auch Fehler auftreten können. Man stelle sich vor, die Abfrage steht auf =X und jemand gibt ein y ein. Oder, die Abfrage steht auf o und jemand gibt ein ö oder eine 0 Null ein. Es lässt sich zwar alles abfangen, aber ein Restrisiko bleibt grundsätzlich.

Alternative c) einfach durch Abzählen vom aktuellen Tag

Möchte man z. B. Tage kennzeichnen, die eine gewisse Anzahl von Tagen vor dem heutigen Datum liegen, lässt sich dies ganz einfach - wie beispielhaft zuvor beschrieben - realisieren.

Die Formel der bedingten Formatierung könnte dann z. B. wie folgt lauten:

In Bedingung 1 wird ausgewählt

Formel ist =A1<=HEUTE()-30

die Formatierung der Zelle soll im Falle des Zutreffens rot werden.

Anstelle des Bereichs -30 könnte natürlich auch hier wieder eine Hilfszelle gute Dienste leisten.

Berechnung der Sommerzeit

Bekannter Maßen wird am letzten Wochenende (Sonntag) im März die Uhr auf Sommerzeit umgestellt.
Somit lässt sich mit MS Excel auch immer berechnen, an welchem Tage dies genau geschieht.

Dazu sind folgende Infos wichtig. Die Woche beginnt in MS Excel mit dem Sonntag und endet mit dem Samstag. Die entsprechende Formel / Funktion dafür lautet
=WOCHENTAG(Zelle, in das Datum enthalten ist)
Steht z. B. in der Zelle A1 das Datum 04.12.2008 wäre das Ergebnis aus der Formel
=WOCHENTAG(A1)
die 5 für Donnerstag.

Somit kann man mit den Funktionen DATUM, JAHR und WOCHENTAG relativ einfach das Einläuten der Sommerzeit berechnen.

Beispiel:
In Zelle A1 steht z. B. das gültige Datum 04.12.2008. Das Ergebnis soll in Zelle B1 ausgegeben werden.
So kann jetzt mit der Formel:

=DATUM(JAHR(A1);3;31) erst einmal immer der 31.3. eines jeden Jahres bestimmt werden.

Die Formel

=WOCHEN _ TAG(DATUM(JAHR(A1);3;31))

gibt den tatsächlichen Wochentag des 31.3. als Zahl heraus aus. Jetzt muss diese nur noch vom 31.3. abgezogen werden. Da jedoch der 1. Tag benötigt wird, muss natürlich auch wieder um 1 erhöht werden.
Die komplette Formel könnte daher wie folgt aussehen:

=DATUM(JAHR(A1);3;31)-WOCHEN _ TAG(DATUM(JAHR(A1);3;31)-1)

oder auch so:

=DATUM(JAHR(A1);3;31)-WOCHEN _ TAG(DATUM(JAHR(A1);3;31))+1

oder auch so:

=DATUM(JAHR(HEUTE3;31)-WOCHEN _ TAG(DATUM(JAHR(HEUTE3;31))+1

Es gibt natürlich wieder mehrere Möglichkeiten.

Berechnung der Winterzeit

Ebenso lässt sich natürlich auch der Beginn der Winterzeit bestimmen. Das Verfahren ist identisch zur Sommerzeit. Lediglich die Formel ist natürlich leicht zu modifizieren und würde lauten:

=DATUM(JAHR(A1);10;31)-WOCHEN _ TAG(DATUM(JAHR(A1);10;31)-1)

oder auch so:

=DATUM(JAHR(A1);10;31)-WOCHEN _ TAG(DATUM(JAHR(A1);10;31))+1

oder auch so:

=DATUM(JAHR(HEUTE10;31)-WOCHEN _ TAG(DATUM(JAHR(HEUTE10;31))+1

Es gibt natürlich wieder mehrere Möglichkeiten.

So, das war einmal mehr ein „kleiner Ausflug“ in MS Excel.

Ich hoffe, es war nicht gar so langweilig. Wen es interessiert; ich kann jedem nur empfehlen, das Eine oder Andere einfach einmal auszuprobieren bzw. nachzuvollziehen. Es öffnen sich ungeahnte Möglichkeiten, die man hier gar nicht alle beschreiben kann.

So, zum Schluss ... (mein Standardsprüchlein)

Wenn Sie nun hier angelangt sind (nach dem vielen Lesen, oder nur Scrollen wünsche ich Ihnen ein angenehmes Lesen meines Berichts gehabt zu haben.

Mir wünsche ich viele Kommentare und natürlich möglichst viele positive Bewertungen.
(Aber nur dann, wenn Ihnen der Bericht wirklich gefallen hat. Frei nach dem Motto: der Bericht hat Ihnen gefallen, dann sagen Sie es weiter. Der Bericht hat Ihnen nicht gefallen, dann sagen Sie es mir!).

Bis zum nächsten Bericht, man liest sich.

PS: meine Berichte erscheinen auch auf anderen Plattformen …

PPS: ich meine, auch ältere Berichte sind interessant und beachtenswert ...