Stundennachweis (Marke sehr schwer)

[siemonski]

Schön guten Abend,

ich habe einige Probleme bei der Erstellung meines Stundennachweises, habe bezüglich Formeln und allem drumm herum eigentlich auch Null Ahnung, will mir aber Mühe geben!

Kurz zur Vorgeschichte:
Habe mir ein Dauer-Kalender zusammengebastelt, pro Quartal eine Tabelle in Spalte D trage ich immer meine Dienste ein (46 verschiedene Dienste, mit zum Teil unterschiedlichen Dienstlängen), am Monatsende setze ich mich dann hin und errechne dann aufwendig die Arbeitszeit in Minuten, wobei ich einen Teil schon Automatisch berechnen lasse und nur die Differenz manuell hinzufüge.
Ich suche jetzt nach einer Lösung wo ich nur den Dienst eingebe und und jedem Dienst einen Vordefinierten Wert zufüge.
Ich hoffe meine Ausführung ist halbwegs nachvollziehbar.
Habe mal eine Tabelle zum Verständnis Hochgeladen.

Vielen Dank und schöne Grüße
siemonski

[MrMurphy]

Hallo

so ganz geht dein Problem aus deiner Beschreibung und deiner Tabelle für mich nicht hervor.

Folgende Fragen zur Lösungsfindung:

- Haben die Dienste jeweils immer die gleiche vorgegebene Minutenanzahl?

- Ist pro Quartal eine Tabelle erforderlich oder benötigst du die Ergebnisse pro Quartal?

- Was ist so kompliziert daran die Arbeitszeit in Minuten zu errechnen, wenn doch die einzelnen Dienste bereits bestimmten Minuten entsprechen, die dann ja nur addiert werden müssten? Oder soll das Ergebnis in Stunden und Minuten ausgegeben werden?

- Können pro Tag mehrere Dienste anfallen? Wenn ja, steht die Höchstanzahl fest?

- Was bedeuten die Werte in Spalte B, vor allem die F?

- Wieso werden in Spalte E Werte subtrahiert und was bedeutet das "°"?

Im Moment tendiere ich zum Einsatz der Funktion SVERWEIS().

Gruss

MrMurphy

[siemonski]

Hallo MrMurphy

-nein die Dienste sind unterschiedlich lang,
so gibt es den Dienst 4311 einmal mit 469 Minuten und einmal mit 422 Minuten je nachdem was ich für eine Tätigkeit ausführe. Das ist ja mein Problem.

-Ich habe den Kalender (Quartal) so aufgebaut weil ich ihn auch Ausdrucke, dieses Format habe ich schon über Jahre und finde es recht Praktisch.
Die Ergebnisse benötige ich Monat für Monat addiere sie zum Quartalsende und übertrage sie ins zweite Quartal (per Formel);

-Das Problem sind die unterschiedlichen Dienstlängen;

-Pro Tag fällt nur ein Dienst an;

-Die Werte in B sind meine Dienste (Beispiele) das F bedeutet nur Frei;

-Das ° vor den Diensten ist nur zur Unterscheidung, das sind Dienste in denen ich eine andere Tätigkeit ausübe und die Dienstlänge sich nach der Tätigkeit richtet; die Werte in Spalte E wurden nicht subtrahiert ich hätte besser Dienst Nr. 9501 bis 9522 schreiben sollen.

schau mal bitte die Formeln in B34 und B35, so hatte ich es bisher gemacht, musste aber wegen der unterschiedlichen Dienstlängen nacharbeiten.

Ich hoffe das ich es jetzt besser rüber gebracht habe.

Muss jetzt Arbeiten schaue Morgen wieder nach und werde noch das eine oder andere erklären.

Gruß siemonski

[balu]

Hallo siemonski,

46 verschiedene Dienste, mit zum Teil unterschiedlichen Dienstlängen

Und, wo ist das Problem!?
Da nehmen wir doch =SUMMENPRODUKT() daher, und schon hat's sich

In dem Bereich B3:B33 lässt Du alles so wie es ist.
Und im Bereich C3:C33 trägst du zu den jeweiligen Diensten die Passenden Minuten ein. z.B.
Dienst 4311 mit 3 x 469 Minuten
Dienst 4311 mit 1 x 422 Minuten

Jetzt legst Du noch zwei zusatzspalten an, in denen Du die Dienste untereinander einträgst, und daneben die Dienstzeiten. z.B. so
9501 442
9501 322
9522 442
9522 322

Und neben den Minuten kommt jetzt die =SUMMENPRODUKT() Formel rein. Die könnte z.B. so aussehen.

Code: Alles auswählen

=SUMMENPRODUKT($B$3:$B$33=F9;$C$3:$C$33=H9)*H9

Einfach ausgedrückt, passiert folgendes.
Multipliziere im Bereich B3:B33 die Anzahl der Dienste die in F9 stehen, mit der Anzahl der Minuten in dem Bereich C3:C33 die in H9 stehen. Das könnten z.B. 3 Übereinstimmungen sein. Anschließend Multipliziere das Ergebnis (3) mit den Minuten aus H9, das könnte z.B. 3 x 322 = 966 sein.

Zur Verdeutlichung habe ich das in den Anhang gepackt.

Gute nacht
balu

[siemonski]

Hallo balu,

erstmal Danke für deine Bemühungen.

"Und, wo ist das Problem!?"

Das Problem ist in meinem Kopf, bekomme das mit den Formeln einfach nicht umgesetzt.
Das es so kompliziert wird hätte ich ja nicht gedacht.
Wo in deiner Beispieldatei werden denn die Minuten Multipliziert und ausgewiesen!

Ursprünglich hatte ich ja die Gesamt-Minuten in Spalte D =ANZAHL(D3:D33)*322 berechnet und hatte jetzt die Hoffnung das die Formel in irgend einer Art und Weise ergänzt werden kann zum Beispiel: wenn in D3:D33 F9501 bis F9522 dann mal 322 Minuten und wenn B9501 bis B9522 dann mal 442 Minuten usw., aber es geht wohl nicht so einfach.Was mich irritiert ist das Calc mit Buchstaben vor der Zahl nichts anfangen kann (so jedenfalls mein Eindruck).
Ich bin aber für alle Schandtaten offen, da ich erst ca.ein Monat mit Calc arbeite bitte ich um Nachsicht, dauert bei mir etwas länger!
Habe jetzt mal mein Kalender mit angehängt (ist übrigens nach deiner Vorlage Jahreskalender) und eine mögliche Bezeichnung der Dienste auf Tabelle1 etwas verständlicher ??? dargestellt.An der Form wollte ich eigentlich nichts ändern, aber es geht wohl auch rechts von der Tabelle mit Spalten verstecken?

Gruß siemonski

[balu]

Hallo siemonski,

nicht nur wenn man neu ist, nein, sondern auch wenn man schon etwas weiter ist, fällt es einem nicht immer leicht seine Gedanken in Formeln umzusetzen. Ich sprech da aus Erfahrung.

Wo in deiner Beispieldatei werden denn die Minuten Multipliziert und ausgewiesen!

Das hatte ich versucht gehabt zu erklären, was aber wohl nicht so richtig rüber gekommen ist.
Also versuch's ich noch mal.

In meiner Beispieldatei siehst Du in der Spalte B die Dienste, und in der Spalte C die Minuten.
Und wenn Du genau hinschaust, dann siehst Du z.B. den Dienst 9522 insgesamt 4 mal in dem Bereich B7:B10 stehen. Daneben, im Bereich C7:C10 stehen 2 verschiedene minutenangaben, 3 x 322 und 1 x 442. Und wenn du jetzt wissen willst wieviele Minuten gesamt der Dienst 9522 zu je 322 minuten ergibt, dann brauchst Du nur in die Spale J zu schauen. Denn dort wird das mittels SUMMENPRODUKT ausgerechnet.

Da ich aber zu dem Zeitpunkt von einer anderen Situation ausgegangen war, ist mein vorschlag nicht mehr so richtig passend.

Was mich irritiert ist das Calc mit Buchstaben vor der Zahl nichts anfangen kann (so jedenfalls mein Eindruck)

Direkt damit rechnen kann man zwar nicht, aber zählen wie oft z.B. B 4301 vorkommt, das geht. Eine Methode hast Du ja schon selber herausgefunden. Und zwar die ZÄHLENWENN Methode. Um jetzt aber den Dienst B 4301 in deiner neuen Beispieldatei zu zählen, musst Du nur den Suchtext in doppelte Anführungszeichen setzen.
Beispiel:

Code: Alles auswählen

=ZÄHLENWENN(D3:D33;"B 4301")

Und schwupp-di-wupp bekommst Du als Ergebnis die 4.

Eine andere Methode ist, wenn Du auf eine Zelle zugreifst, in der der Suchtext steht. Nehmen wir mal an, dass B 4301 in der Zelle C38 steht, dann muss die vorige Formel so aussehen.

Code: Alles auswählen

=ZÄHLENWENN(D3:D33;C38)

Auch in diesem Fall bekommst Du als Ergebnis wiederum die 4.
Es spielt so gesehen keine rolle wie Du den Dienst zählst. Nur um eine Datei leichter zu pflegen (wenn man mal was ändern muss), ist es ratsamer die zweite variante einzusetzen. Denn dadurch erspart man sich viel tipperein, und man kann vieles schneller überblicken.

Ich habe jetzt mal deine aktuelle Datei genommen, und eine zusatzspalte eingefügt, die Du aber ausblenden kannst. In dieser Spalte werden nun automatisch die Minuten eingetragen. Und das geschieht mit SVERWEIS. Infrormationen dazu findest Du im Wiki, so wie auch hier im Forum.
Damit aber die Minuten angezeigt werden können, musste ich eine kleine zusatztabelle anlegen. Diese ist zweigeteilt, mit den Namen "Dienste", und "Minuten".

Bei dieser Tabelle ist strengstens darauf zu achten, dass die Dienste chronologisch sortiert vorliegen!!

Sie fängt mit 4101 an, und geht über B 4301 bis zu Y 1111. Sie ist natürlich nicht komplett, dazu fehlen mir alle deine Dienste. Sie soll jetzt ja aber auch nur als Beispiel dienen. Denn da ist zu sehen, das sie erst mit reinen Zahlen anfängt, und dann alfabetisch mit Zahlen weitergeht. Dies kannst Du wohl selber weiter ausbauen, nur musst Du auf die richtige reihenfolge achten.
Neben den Diensten stehen jetzt die vorgegebenen Minuten.

SVERWEIS geht jetzt in der neuen Spalte E daher, und sucht den Dienst, der links daneben steht, und sucht ihn in der zusatztabelle. Wenn er gefunden wurde, wird aus der zusatztabelle die dazugehörige Minutenvorgabe ausgelesen, und in der zusatzspalte eingetragen.
Sollte nun aber kein Dienst angegeben sein, sondern ein F wie Frei, dann wird halt an dieser Stelle gar nichts gemacht.
Und zu guter letzt, werden dann noch unten die Gesamtminuten addiert.

Die Formel sieht jetzt so aus.

Code: Alles auswählen

=WENN(D3="F";"";SVERWEIS(D3;$C$38:$D$47;2;1))

Und damit Du das ganze auch gleich praktisch vor Augen hast, ist sie im Anhang.


Freut mich, dass Du dank meiner hilfe den Kalender erstellt hast . Ist doch immer wieder schön zu sehen und zu hören, dass die Arbeit nicht umsonst war.

Ich hoffe, dass mein neuer vorschlag nun besser geeignet ist.


Gruß
balu

[siemonski]

Hallo balu,

erstmal Danke,
bin schon wieder auf dem Sprung zur Arbeit, werde mir morgen früh alles in Ruhe ansehen.

Gruß siemonski

[siemonski]

Hallo balu,

einfach GENIAL was du da für mich gezaubert hast, dafür nochmals Danke.
Ich habe erstmal eine weile gebraucht um das halbwegs umzusetzen, habe nun alle meine Dienste so eingetragen so das ich das 1te Quartal fertig habe und schon gibt es wieder ein Problem!!!

Ich muss ja nun einen Querverweis in der Tabelle2 zur Tabelle1 in diese Formel:
=WENN(D3="F";"";SVERWEIS(D3;$C$38:$D$47;2;1))

mit einarbeiten, bei der Jahreszahl habe ich das ja mit INDIREKT gemacht habe aber kein Durchblick ob und wo .
Ansonsten könnte ich auch die kleine Tabelle ( Mitlerweile 200 Zeilen ) in die anderen 3 Tabellen kopieren wenn es nicht anders geht. Über einen Tipp wäre ich Dankbar.

Gruß siemonski

[balu]

Hallo siemonski,

da wollte ich so schön antworten, als sich mein Monitor (10 Jahre, 19") verabschiedete *grrrrr* . Gott sei Dank habe ich noch einen Reserve Moni , den ich eigentlich schon längst verscherbeln wollte.
Mal schaun ob ich den anderen wieder reparieren kann.


Die kleine Zusatztabelle, die ich eingefügt hatte, brauchst Du nur minimal zu ändern und zu erweitern. Da sie, wie schon erwähnt, unbedingt sortiert vorliegen muss, kannst Du dich auf ein minimum an Daten beschränken.

Und warum ist das so!?

Das liegt an SVERWEIS. Dazu mal ein Auszug aus der Online-Hilfe

... Sortierte Spalten lassen sich wesentlich schneller durchsuchen, und sofern der Suchwert zwischen dem höchsten und dem niedrigsten Wert der sortierten Liste liegt, erhalten Sie mit der Funktion immer einen Wert, selbst dann, wenn der genaue Suchwert nicht gefunden werden kann. ...

Ich hatte anfangs auch so meine schwierigkeiten das zu verstehen, bis das unser geliebter Karolus , das mal erklärte. Und um das auch gleich praktisch zu demonstrieren, nehmen wir da mal passende Daten von dir zur Hand.

Code: Alles auswählen

B 4301	111
B 9501	442
F 9501	322

Wie Du siehst habe ich jetzt mal 3 Dienste genommen, die sortiert vorliegen und alle mit ....01 beginnen. Aber es steht kein Dienst dabei z.B. mit der Nummer B 4326. Wir wissen aber, dass dieser Dienst existiert. Und das er, wie der B 4301, auch 111 Minuten hat. Diese Minutenangabe hatte ich extra zur Verdeutlichung genommen, auch wenn sie nicht real ist.
Also, wenn Du jetzt z.B. im Kalender in der Spalte D den Dienst B 4326 eingibst, dann werden in der Spalte E die 111 Minuten ausgegeben. Und das liegt daran, dass SVERWEIS diesen Dienst nicht findet, und stattdessen den nächst kleineren Wert (B 4301) nimmt, und die dazugehörigen Minuten ausgibt. Denn B 4326 ist GRÖßER als B 4301 (niedrigster Wert ), und KLEINER als B 9501 (höchster Wert).
Und wenn Du jetzt mal C 9578 in der Spalte D eingibst, dann bekommst Du 442 Minuten angezeigt. Auch hier gillt wieder, C 9578 ist GRÖßER als B 9501 (niedrigster Wert), und KLEINER als F 9501 (höchster Wert).

Und deshalb ist es so wichtig, dass die Dienste richtig sortiert vorliegen, damit immer die richtigen Minuten zurückgegeben werden können.
Du musst also nicht hunderte von Diensten eingeben, sondern immer nur den ersten aus der Gruppe (B 4301, B 4302, .... B 4326).


Um in Tabelle2 auf die Zusatztabelle in Tabelle1 zuzugreifen, nimmst Du jetzt einfach folgende modifizierte Formel.

Code: Alles auswählen

=WENN(ODER(D3="F";D3="");"";SVERWEIS(D3;$Tabelle1.$C$38:$D$47;2;1))

Ich hab sie jetzt noch um die Funktion ODER ergänzt. Wenn also in D3 ein F steht, ODER sie ist leer, dann wird nichts gemacht.
Und in SVERWEIS habe ich jetzt noch den Namen der ersten Tabelle, Tabelle1, eingefügt. Das ist die Anfänger Methode, die ausreicht und funktioniert. Man kann den Bereich in SVERWEIS auch noch einen Namen geben, aber um das Grundprinzip zu verstehen, reicht das erst mal so wie es ist.



Statt

Code: Alles auswählen

=INDIREKT("Tabelle1.A1")

reicht vollkommen.

Code: Alles auswählen

=Tabelle1.A1

Danke für dein Lob



Viel Spaß beim verstehen und begreifen, wünscht
balu

[Gert Seler]

Hallo zusammen,
ich möchte Euch auf einen unkorrekte Auslegung in der SVERWEIS-Formel hinweisen.
Es ist nicht richtig, wenn "Balu" behauptet, das die Daten in der Verweis-Tabelle aufsteigend sortiert sein müssen.

Es ist richtig das "sortierte-Daten" mit 1 und "unsortierte-Daten" mit 0 (null) bezeichnet werden.
Die Formel :

Code: Alles auswählen

=WENN(ODER(D3="F";D3="");"";SVERWEIS(D3;$Tabelle1.$C$38:$D$47;2;1))

ändert sich in

Code: Alles auswählen

=WENN(ODER(D3="F";D3="");"";SVERWEIS(D3;$Tabelle1.$C$38:$D$47;2;0))

der Vorteil ist, mit "0(null)" setzt "SVERWEIS" immer eine "unsortierte Datenliste" voraus und verarbeitet aber auch eine "sortierte Datenliste".
Mit "1(eins)" werden nur"sortierte Datenlisten" verarbeitet.

mfg
Gert

[balu]

Hallo Gert,

also, das hier

ich möchte Euch auf einen unkorrekte Auslegung in der SVERWEIS-Formel hinweisen.

ist fehl am Platz. Denn ich behaupte mal, dass Du wohl nicht so richtig verstanden hast um was es hier geht.

Es ist nicht richtig, wenn "Balu" behauptet, das die Daten in der Verweis-Tabelle aufsteigend sortiert sein müssen.

Habe ich das generell gesagt, oder behauptet!?
Wohl kaum!
Der Beweis steht in meinen Beiträgen in diesem Thread.

Bei dieser Tabelle ist strengstens darauf zu achten, dass die Dienste chronologisch sortiert vorliegen!!

Und deshalb ist es so wichtig, dass die Dienste richtig sortiert vorliegen, damit immer die richtigen Minuten zurückgegeben werden können.

Meine aussagen beziehen sich ausschließlich auf das hier gestellte Problem, und meine vorgestellte Lösung.

Aber ich lade dich recht herzlich dazu ein, mir zu sagen was passiert, wenn die Formel nach deinen Vorstellungen geändert wird. Übrigens steht es auch ganz klar in der Hilfe, was die Null (0, beziehungsweise FALSCH) als Parameter bewirkt.

In unsortierten Listen werden nur genaue Übereinstimmungen mit dem Suchwert gefunden. Anderenfalls gibt die Funktion die Meldung Fehler: Wert nicht verfügbar zurück.

Gruß
balu

[siemonski]

Hallo balu,

so ist mir ja noch nie was erklärt worden,
als ich deine Erklärung mit (niedrigster Wert ) und (höchster Wert) am Beispiel meiner Dienste gelesen habe machte es bei mir sofort klick und ich dachte dann muss es ja reichen den erste und letzten Dienst einzugeben und drei Zeilen später hast du es mir auch noch bestätigt.
Ich habe die kleine Tabelle (nennt man das dann Matrix???) eben umgeschrieben, nun sind es nur noch 10 Zeilen, weitaus übersichtlicher.
Ich habe morgen Frei und werde dann alles umsetzen.
Zwei Sachen hätte ich dann noch, einmal eine Formel für ein Schichten-Dauer-Dienstplan meiner Frau (ist aber nicht so kompliziert wie meiner, nur 4 verschiedene Dienste) der in meinem Kalender integriert werden soll und eine zur Berechnung meiner Nachtstunden.
Aber dafür lese ich erst einmal etwas im Forum nach und melde mich später nochmal.

Dir balu und allen anderen eifrigen Helfer ein recht herzliches Dankeschön!!!
Wenn du in Berlin Wohnen würdest würde ich dir ein Warsteìn`er vorbei bringen!!!

Gruss siemonski

[balu]

Hallo siemonski,

... dann muss es ja reichen den erste und letzten Dienst einzugeben...

Ich hoffe, dass Du das nicht falsch verstanden hast. Denn ich hatte ja gesagt

Du musst also nicht hunderte von Diensten eingeben, sondern immer nur den ersten aus der Gruppe (B 4301, B 4302, .... B 4326).

Und damit ist gemeint, dass Du nicht

Code: Alles auswählen

B 4301	111
B 4326	111

B 9501	442
B 9526	442

schreibst, sondern einfach

Code: Alles auswählen

B 4301	111
B 9501	442

Ist ein kleiner unterschied, der aber nicht wirklich in's gewicht fällt. Es ist dir überlassen ob Du es so, oder so machst. Beides funktioniert gleichermaßen gut.

Zwei Sachen hätte ich dann noch, einmal eine Formel für ein Schichten-Dauer-Dienstplan meiner Frau (ist aber nicht so kompliziert wie meiner, nur 4 verschiedene Dienste) der in meinem Kalender integriert werden soll...

Na da kann dir doch geholfen werden. Denn so rein zufälliger weise, steht dazu etwas in diesem Thread Formel für 5 Schichten-Dienstplan gesucht.
Ist von mir, aber das siehst Du ja dann. Und wenn es dazu Fragen gibt, ... na Du weisst schon

.... und eine zur Berechnung meiner Nachtstunden.

Auch dazu wird dir hier geholfen, wenn Du damit Probleme haben solltest. Aber ich glaube, da werden dann bestimmt andere helfen wollen


Ich danke dir für die Blumen, und besonders für das Warsteiner *hicks*


Gruß
balu