Über Themen mit U Testbericht

Meine bisherigen Berichte zu MS Excel, insbesondere zu den Zahlen und Datumsformaten haben doch einiges Interesse, Nachfragen, Vorschläge und auch Hilferufe - so möchte ich es einmal ausdrücken - nach sich gezogen.
Danke dafür. Bei Fragen und Wünschen, bitte einfach melden. Ich versuche zu helfen ...

Aktuell erhielt ich jetzt folgende Info:

Zitat:

„Hallo Ulrich. Falls dir mal die Ideen für Excel ausgehen. Ich schreibe Rechnungen.
Dabei steht in einer Zelle, die Uhrzeit, wann ich begonnen habe.
Die nächste Zelle, wann ich in Mittag ging.
Die dritte Zelle ist beschriftet mit der Uhrzeit, wann ich nach Mittag gekommen bin.
Und die vierte Zelle ist die Heimgehzeit.

Nun muss ich ausrechnen, wie viele Stunden + Minuten als Dezimal und stundenmäßig ich gearbeitet habe (= 5 + 6. Zelle bzw. Spalte).
In der nächsten Spalte steht der Nettostundenlohn und in der letzten kommt der Betrag für diesen einen Tag.
Da ich dort 2-3 x in der Woche arbeite, brauche ich im Kumulativ unten die Stunden und Beträge.

Mir hat ein ganz netter Mensch (der 2.Chef dort) dafür zwar eine Vorlage geschrieben. Doch die Formeln habe ich nicht kapiert. Also, wenn dir mal die Ideen ausgehen, dann kannst du ja vielleicht einen Bericht mit "Erklärung für Drittklässler" schreiben.
Also so wie deine anderen Berichte, denen ich meist geistig folgen konnte.

Gruß und ein schönes Wochenende.“

Zitat Ende

Diese Info bot sich geradezu an, sie mit der geplanten und bereits in Vorbereitung befindlichen Info zu verbinden. Sie lässt sich auch mit der Info „automatischer Kalender“ gut verknüpfen.

Tja, und daher nun ein „Nachschlag“ mit Möglichkeiten und Beispielen ....

(Ich bitte an dieser Stelle bereits alle diejenigen um Verständnis, die sich besser mit MS Excel auskennen. Die Info wurde gerade für die nicht so Erfahrenen erstellt, um diesen die Komplexität näher zu bringen.)

Gliederung:

1. allgemeine Infos, die hierfür zu bedenken sind, einschließlich der Verknüpfungsmöglichkeit von Zahlen mit Text

2. Lösungsmöglichkeiten (Schritt für Schritt-Anleitung)

3. Fazit / Ergebnis

1. allgemeine Infos, die hierfür zu bedenken sind, einschließlich der Verknüpfungsmöglichkeit von Zahlen mit Text

Aufgrund der zuvor gemachten Informationen will ich hier ein komplettes Stunden- und Abrechnungsprotokoll für einen Monat erstellen.

Benötigt werden neben den diversen Eckinformationen, die im oberen Teil der Tabelle untergebracht werden, 7 Spalten, die sich wie folgt zusammensetzen:

- Spalte A (z.B. Zelle A5) = Beginnzeit morgens
- Spalte B (z.B. Zelle B5) = Endzeit mittags
- Spalte C (z.B. Zelle C5) = Beginnzeit nachmittags
- Spalte D (z.B. Zelle D5) = Endzeit nachmittags
- Spalte E (z.B. Zelle E5) = geleistete Arbeitszeit an dem Tag (dezimal)
- Spalte F (z.B. Zelle F5) = geleistete Arbeitszeit an dem Tag in Stunden und Minuten
- Spalte G (z.B. Zelle G5) = Tagesverdienst, der sich z.B. aus Zelle G1 berechnen könnte

In den Zellen oberhalb wird:
- die Überschrift (Stunden / Abrechnungsübersicht)
- der Abrechnungsmonat (in zwei Zellen hintereinander: November 2008 vom 01.11.2008 bis 30.11.2008)
- die Anrede, der Vorname, der Name (Herr Max Mustermann)
- und der Stundenlohn (10,00 Euro) angegeben.

Unterhalb der Gesamtberechnung sollen dann noch:
- die jeweiligen Gesamtwerte, aufgeschlüsselt in dezimale und Stundenwerte mit den Tageswerten ausgerechnet werden.

Zum Schluss soll noch ein automatisierter Text folgen, der z. B. wie folgt lauten könnten:

Sehr geehrter Herr Mustermann!
Sie haben im Monat November 2008 (vom 01.11.2008 bis 30.11.2008) insgesamt 36:45 Std. geleistet.
Das entspricht 36,75 Dezimal-Stunden und somit einem Gesamtlohn von 367,50 €.

Damit müssten eigentlich alle erforderlichen Infos verfügbar sein.

Es ergeben sich folgende Denkanstöße, die bedacht und natürlich auch umgesetzt werden müssen:

- Es muss eine Umrechnungsbasis von Stunden / Minuten auf dezimale Werte gefunden werden.

- Es muss eine Möglichkeit geschaffen werden, dass die vorhandenen Texte mit den Zahlenwerten in einer oder mehreren Zellen so verbunden werden, dass man es vernünftig lesen kann.

- Es soll selbstverständlich Alles automatisch ausgefüllt und berechnet werden..

a) einheitliche Basis für die Umrechnung von Stunden / Minuten zu Dezimalzahlen.

Es dürfte klar sein, dass ein Stundenlohn von z. B. 10,00 Euro / je Stunde nicht so einfach für MS Excel auf eine halbe, eine viertel, dreiviertel Stunde oder gar 55 Minuten umgerechnet werden kann.

Man kann sich aber helfen. Wenn 1 Std. 10,00 Euro entsprechen, dann sind logischer Weise eine viertel Stunde 2,50 Euro, eine halbe Stunde 5,00 Euro, drei viertel Stunde 7,50 Euro.
Für uns ist dies einfach und logisch. MS Excel muss man es aber erst korrekt beibringen.

Man muss also hergehen und die Stunden und Minuten derartig umrechnen, dass sie in das Dezimalsystem hineinpassen. Hier bieten sich die Minuten an, da sie ja im vorliegenden Fall die kleinste gemeinsame Einheit bilden sollen.

Das konkrete Vorgehen ist unter 2. beschrieben

b) „Das Auge isst bekannter Maßen ja mit“

Neben der korrekten Automatik-Abrechnung möchte man ja auch noch ein vernünftiges und passendes Outfit nutzen. Hier meine ich insbesondere die Zahlenformatierung und die Verweise innerhalb der Zellen.
Leider ist es nicht immer über das sog. benutzerdefinierte Zahlenformat zu händeln.

Ein wenig Abschweifen muss hier nochmals sein.
Für mich ist eine der wichtigsten, wenn nicht sogar die wichtigste Option die Zellformatierung. Sie ist ja bekanntermaßen über das Menü Format -> Zellen ..., eine Tastenkombi (STRG + 1) oder das Kontextmenü mit der Maus zu erreichen.
In diesem Fenster lässt sich mit Hilfe der verschiedenen Registerkarten so ziemlich Alles realisieren. Schaut man sich die Registerkarte Zahlen (links) einmal genauer an, wird man hier schon einen ganze Menge an vorgefertigten Formaten finden, in denen die Zahlen in MS Excel dargestellt werden können.

Ist nun nichts Passendes dabei, muss selbst Hand angelegt werden. Mit dem benutzerdefinierten Zahlenformat lässt sich so ziemlich aller möglicher „Blödsinn“ anstellen, um es einmal salopp zur formulieren.
Man kann erklärende Texte vor oder hinter bzw. vor und hinter der Zahl anzeigen lassen. Entscheidend ist nur der korrekte Aufbau des Zusatzes.

Der ist immer gleich:
- in MS Excel kommen erst die positiven Zahlen
- danach werden die negativen Zahlen definiert
- und zum Schluss gibt es dann die besondere Zahl Null
- Kombiniert man Zahlen mit Text, ist der Text immer in Anführungszeichen unterzubringen !

Mein Tipp bzw. Rat:
Man sollte grundsätzlich immer alle Möglichkeiten definieren, da es immer wieder einmal vorkommen kann, dass das eigentlich Unmögliche doch einmal zutrifft.

Tests sollten (abhängig von den benötigten Zahlen) immer mit möglichst kleinen Zahlen vorgenommen werden, damit man sofort im Kopf bereits überschlagen kann, ob das Ergebnis denn auch richtig ist.

Es gibt für die Zahlenformatierung verschiedene Platzhalter. Diese sollte man kennen.

So ergibt z.B. folgender Eintrag: #.##0,00 eine beliebige, positive Zahl mit Tausendertrennzeichen und zwei Nachkommastellen.
Für alle Fälle sollte dann das benutzerdefinierte Zahlenformat für die drei Zustände wie folgt gewählt werden:
#.##0,00;[ROT]-#.##0,00;0
positive Zahlen werden mit Tausendertrennpunkt und zwei Nachkommastellen in der Standardfarbe dargestellt
negative Zahlen werden genau so dargestellt, allerdings in Roter Farbe und einem führenden - Bindestrich.

Eine abschließende Liste würde hier den Rahmen sprengen. Es gilt: einfach einmal ausprobieren und vorher aus einem ähnlichen Format „klauen“.

Bitte immer daran denken, dass dies nur mit der Ansicht also der Darstellung zu tun hat. Die korrekten Berechnungen werden davon nicht berührt. MS Excel rechnet ohne Vorgaben trotzdem mit der kompletten Zahl weiter. Also Rundungen beachten und auf die entsprechenden Formeln zurückgreifen !!

Wie ja vorab erwähnt, lässt sich zwar so ziemlich alles Mögliche mit dem benutzerdefinierten Zahlenformat anstellen, aber leider doch nicht alles.

So muss man schon einmal eine sehr hilfreiche Funktion bzw. Formel zu Rate ziehen. Diese Formel / Funktion lautet TEXT(Wert;Textformat). Sie bewirkt nur Eines, sie frisiert Zahlen in Texte um und stellt sie entsprechend dar.

2. Lösungsmöglichkeiten (Schritt für Schritt-Anleitung)

a) Die Berechnungen bzw. Umrechnungen

Um nun einen konkreten Tagessatz zu erhalten muss im ersten Schritt nach unserem Beispiel erst einmal ermittelt werden, wie viele Stunden hat der Max Mustermann (Fallbeispiel) denn überhaupt netto gearbeitet.
Zu diesem Zweck wird einfach der als Stunde und Minuten eingetragene Werte in Zelle B5 vom Wert in Zelle A5 abgezogen.
Die Formel würde also lauten =B5-A5
Angenommen Arbeitsbeginn war um 08:00 Uhr, die Pause wurde um 12:30 Uhr eingeläutet. So lässt sich dies mit der v.g. Formel einfach darstellen. Es ergibt den Wert 04:30, den MS Excel auch korrekt anzeigen sollte.

Im zweiten Schritt wird der zweite Teil des Tages berechnet. Die Formel würde lauten =D5-C5.
Wenn hier die Werte 13:00 Uhr bzw. 16:00 Uhr stehen würden, ergäbe sich ein Ergebnis, welches 03:00 lauten müsste.

Diese beiden Ergebnisse werden nun einfach addiert. Die Formel würde ggf. lauten: =E5+F5.
Das Tagesendergebnis lautet danach 07:30.
Jetzt kann man natürlich nicht 07:30 einfach mit dem Stundensatz 10,00 Euro multiplizieren. Es wäre zu wenig. Es muss erst eine Umrechnung erfolgen.

Wie ich ja zuvor mitgeteilt hatte, ist im vorliegenden Fall die Minuten die kleinste gemeinsame Einheit.
Es ist also zweckmäßig erst einmal das Ergebnis komplett in Minuten umzuwandeln. Dazu ist natürlich eine Trennung der Stunden und der Minuten erforderlich.

Hierfür stehen in MS Excel zwei Funktionen zur Verfügung, nämlich STUNDE für die Extrahierung der Stunden und MINUTE für Minute.
(Wenn man mit Sekunden arbeiten würde, könnte dann auch noch SEKUNDE benötigt werden.)

Um aus dem vorläufigen Ergebnis die Stunden zu erhalten, würde die Formel wie folgt lauten:

=STUNDE(G5)

wenn das Ergebnis in Zelle G5 stehe würde. Es müsste sich im vorliegenden Fall der Wert 7 ergeben. Da wir aber mit Dezimalzahlen und einer anderen Einheit rechnen, muss dieser Wert natürlich noch mit 100 multipliziert werden. Bei korrektem Formelaufbau würde sich dann 700 ergeben. Die Formel würde also ergänzt lauten:

=STUNDE(G5)*100

Bei den Minuten ist ähnlich zu verfahren. Die Grundformel lautet:

=MINUTE(G5)

Da aber eine Umrechnung von z.B. 60 Minuten auf 100 Einheiten erfolgen muss, ist auch hier wieder ein wenig mehr zu machen. Auch hier ist die Formel mit 100 zu multiplizieren, aber direkt danach mit 60 zu dividieren, da ja 60 Minuten einer Stunde entsprechen.
Im vorliegenden Fall müsste also die modifizierte Formel für die Minutenberechnung wie folgt lauten:

=Minute(G5)*100/60

Es sollte der Wert 50 nun angezeigt werden. Falls dies nicht der Fall ist, muss entweder das Zahlenformat noch geändert (Einstellung auf Standardzahl) oder aber die Formel überprüft werden.

Es liegen nun die Stunden als auch die Minuten als reine Zahl (dezimal) vor. Die Zwischenergebnisse habe ich in den Zellen H5 und I5 untergebracht. Diese müssen lediglich wieder addiert werden. Das Ergebnis lautet im vorliegenden Fall 750.
Da dieser Wert nun wirklich etwas zu hoch ist (ein Klasse-Stundenlohn), muss er jetzt wieder auf das richtige Maß reduziert, also durch 100 dividiert werden. Die kombinierte Formel würde also lauten:

=SUMME(H5+I5)/100

Spätestens jetzt wird auch begreifbar warum mit dem Wert 100 gerechnet worden ist.

Diese hier vollzogenen Einzelschritte, die ausschließlich der Erläuterung / Erklärung dienen, setze ich natürlich in nur einer Zelle ein. Es ergibt sich notwendiger Weise daraus natürlich eine verschachtelte Formel, die wie folgt aussehen könnte:

(Zur Verdeutlichung und auch weil die komplette Formel zu lang wäre, habe ich sie mit Leezeichen und einem Unterstrich _ getrennt. Diese dürfen beim möglichen Kopieren oder Abtippen natürlich NICHT verwandt werden.)

Formel für die Umrechnung der Stunden und Minuten in Dezimalwerte:

=SUMME((STUNDE(D6-C6+B6-A6)*100) _ +(MINUTE(F6)*100/60))/100

Formel für die Stunden und Minutenanzeige im Klartext:

=SUMME((D6-C6)+(B6-A6))

Formel für den Tagesverdienst (wobei sich in G3 der Stundensatz befindet):

=G3*E6

Jetzt muss das gesamte Konstrukt nur noch entsprechend oft kopiert werden.

Einen kleinen „Fallstrick“ gibt es aber dann doch noch zu beachten. Bei Addieren der „normalen“ Stunden über den Wert 24 hinaus zeigt MS Excel nicht mehr den Gesamtbetrag sondern nur eine Zahl, die zwischen 00:00 und 23:59 liegt, an.
Hier muss mit Hilfe des benutzerdefinierten Zahlenformats bei der Summierung ein wenig nachgeholfen werden, so dass MS Excel auch wirklich den kompletten Wert anzeigt.
Im benutzerdefinierten Zahlenformat muss die Uhrzeit wie folgt formatiert werden:

[hh]:mm

Die Uhrzeitangabe, konkret der Platzhalter h muss in eckige Klammern gesetzt werden, damit MS Excel auch über 24 Stunden hinaus addieren kann !!

b) Die Gestaltung des Outfits

Die Abrechnung soll ja - wie erwähnt - auch ein wenig persönlichen Charakter aufweisen. Wie zuvor ja bereits beschrieben, soll unterhalb der gesamten Berechnung noch folgender Text stehen:

Sehr geehrter Herr Mustermann!
Sie haben im Monat November 2008 (vom 01.11.2008 bis 30.11.2008) insgesamt 36:45 Std. geleistet.
Das entspricht 36,75 Dezimal-Stunden und somit einem Gesamtlohn von 367,50 €.

Nun, dies wird ganz einfach durch verschiedene Formeln realisiert, nämlich:

- Formel: WENN-DANN-SONST
- Formel: TEXT(Wert;Formatierung)

Die WENN-DANN-SONST-Funktion kommt für hier die Formatierung der Anrede zum Einsatz.

Wenn in der entsprechenden Zelle oberhalb der Berechnung der Wert Herr steht, dann soll auch der Passus „Sehr geehrter Herr Mustermann“ eingefügt werden. Handelt es sich um eine Frau soll dort natürlich stehen „Sehr geehrte Frau Mustermann“.

Da man sich schon einmal mit der Eingabe HERR oder HERRN schnell vertun kann, nutze ich persönlich für dergleichen Infos eine fest definierte Auswahlliste. Man kann nur auswählen und klicken.
Eine andere Möglichkeit ist bei der freien Eingabe die entsprechende Abfrage in der Funktion; nämlich: wenn es nicht Frau heißt, dann kann es ja nur ein Herr sein.

Somit würde hier die Formel lauten können:
(Bitte auch hier auf den Unterstrich achten. Dieser und die Leerzeichen NICHT nutzen !!)

=WENN(A4 "Frau";"Sehr geehrter Herr "&C4 _ ;"Sehr geehrte Frau "&C4

Konkret wird einfach die Zelle A4, in der die Anrede eingegeben wird, geprüft. Entspricht der Wert dort nicht dem Text Frau, dann wird im Ergebnis ausgegeben: Sehr geehrter Herr. Da natürlich auch noch der Nachname, der sich in Zelle C4 befindet, erneut ausgegeben werden soll, wird dieser mit einem sog. kaufmännischen Und - Zeichen hinter die Anführungszeichen des Textes gesetzt.
Wenn es denn eine Frau ist, soll einfach nur der SONST - Bereich ausgeführt werden. Den Abschluss bei beiden Optionen bildet ein in Anführungszeichen gesetztes Ausrufezeichen.

Darunter sollen ja nun diverse Informationen ausgegeben werden, nämlich:

Sie haben im Monat November 2008 (vom 01.11.2008 bis 30.11.2008) insgesamt 37:15 Std. geleistet.

Als Formel / Funktion sähe das wie folgt aus (Bitte Leerzeichen und Unterstrich beachten

="Sie haben im Monat "&TEXT _ (B2;"MMMM JJJJ ("&C2&") insgesamt "&TEXT _ (F12;"[hh]:mm Std. geleistet."

Im Einzelnen:
- mit der Funktion Text, dem Wert aus Zelle B2 in dem der Monat November 2008 steht wird dieser erneut als ausgeschriebener Monat mit einer vierstelligen Jahreszahl ausgegeben.

- danach wird eine sich öffnende, runde Klammer als Text angezeigt. An diese schließt sich einfach die Information für den Beginn und das Ende des jeweiligen Monats an. Da für diesen Bereich bereits eine entsprechende Formatierung mit der Funktion Text vorgenommen worden ist, muss hier nur auf den Ursprung verwiesen werden.

- eine erneute runde, schließende Klammer als Text angezeigt, gefolgt von dem Wort insgesamt. Danach wird mit der Textformel der Gesamtwert der Stunden und Minuten angezeigt.
Hier ist zwingend zu beachten, dass die eckigen Klammern auch hier wieder eingebaut werden, da sonst wirklich nur ein Wert unter 24 Stunden angezeigt würde.
Der restliche Bereich ist wieder ganz normaler Text.

In der darunter liegenden Zeile wurde gleichermaßen verfahren:

Die komplette Formel in der Zelle lautet:

="Das entspricht "&TEXT(E12;"#.##0,00") _ &" Dezimal-Stunden und somit einem Gesamtlohn von "&TEXT(G12;"#.##0,00 €."

- wobei der Bereich TEXT(E12;“#.##0,00“) den ermittelten gesamten Dezimalstunden entspricht und der Bereich TEXT(G12;“#.##0,00 €“) dem Gesamtverdienst.

Jetzt muss ich noch kurz die Formelen / Funktionen für den Zeitraum 01. November 2008 bis 30. November 2008 vorstellen, die ja schon Anfangs des Berechnungsbogens automatisch ausgegeben worden ist.

Sie lautet:

="vom "&TEXT(DATUM(JAHR(B2); _ MONAT(B2);1);"TT.MM.JJJJ") _ &" bis“ &TEXT(DATUM(JAHR(B2) _ ;MONAT(B2)+1;1)-1;"TT.MM.JJJJ")

- Aus dem zuvor eingegebenen Datum wird einfach durch die Funktionen JAHR und MONAT das jeweils aktuelle Jahr und der aktuelle Monat herausgezogen.

- Mit der Formel / Funktion DATUM lässt sich jedes x-beliebige, gültige Datum zusammenbasteln. Egal was für ein Datum in der Zelle B2 steht; es wird immer der 1 des jeweiligen Monats und das jeweilige Jahr angegeben.

- Im zweiten Bereich muss man einfach etwas „tricksen“, da die Monate ja unterschiedliche Anzahlen von Tagen haben (31 Tage, 30 Tage, und beim Februar 28 oder 29 Tage). Hier hilft einfach der Trick, dass auf den ermittelten Monat einfach quasi ein Monat aufgerechnet wird. Für MS Excel ist es dann der 1. des Folgemonats. Nun muss nur noch ein Tag wieder abgezogen werden; man erhält automatisch den Monatsletzten.

- Auch hier wurde mit zweistelligem Tages- und Monatswerten, sowie 4-stelligem Jahreswert gearbeitet.

So, das war einmal mehr eine „kleine Einführung“ in MS Excel. Ich hoffe, es war nicht gar so langweilig. Wen es interessiert; ich kann nur empfehlen, einfach einmal ausprobieren bzw. nachvollziehen. Es öffnen sich ungeahnte Möglichkeiten.

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 ...