Supportnet Computer
Planet of Tech

Supportnet / Forum / Tabellenkalkulation

Hilfe zu Verweis benötigt...





Frage

Hallo Exel-Fans, in meiner Arbeitszeitentabelle habe ich mehrere Uhrzeitfelder "von" "bis" für die Stundenberechnung der flexiblen Arbeitszeiten der einzelnen Mitarbeiter. Es gibt am Ende jeder Mitarbeiterzeile auch ein Feld, in welches Standardwerte über eine Liste ausgewählt werden können. Dazu zählen z.B. auch Urlaub, Krank, Lehrgang, etc. Diese Standardwerte haben "fest" zugewiesene Arbeitszeiten. Na ja, nicht wirklich fest, weil die Arbeitszeit auch noch abhängig vom Wochentag und von der Tarifgruppe des jeweiligen Mitarbeiters ist. Und genau hier liegt mein Problem! Ich brauche also nach Auswahl einer der Standardwerte den zur Person/Tarifgruppe und Wochentag gehörenden Wert. Das geht vermutlich mit einem verschachtelten Verweis, aber genau hier hapert es. Der tatsächliche Wochentag steht in Feld L1. Der Mitarbeitername steht in Spalte A. Die Tarifgruppe steht auf Blatt "Mitarbeiter" in Spalte E Der Arbeitszeitwert muß aus Blatt "Arbeitszeiten" ermittelt werden. Hier stehen in der verschiedenen Zeilen die Tarifgruppen und in den Spalten die Eintragungen "Montag-Freitag", "Wochenende" und "Feiertag". Wer kann mir hier mit einer Formel weiterhelfen? Ein Vereinfachungsansatz von mir ist, auf dem Arbeitszeitplanungsblatt die entsprechende Tarifgruppe für dem Mitarbeiter mit einem Verweis blind zu übernehmen, damit nur zum Blatt Arbeitszeiten(pro Tarif) ein Bezug wg. des entsprechenden Wochentages hergestellt werden muß. Bin für jede Anregung dankbar! MfG Torsten

Antwort 1 von Saarbauer

Hallo,

da mehrere Kriterien zur Ermittlung erforderlich sind, wird es mit der Verweis-Funktion wahrscheinlich nicht machbar sein. Ich vermute es funktioniert mit Summenprodukt()

Kannst du eine Beispieltabelle vielleicht hier einstellen

http://www.netupload.de/

und den Link hier hinterlegen

Gruß

Helmut

Antwort 2 von fantalight

@Helmut:
Vielen Dank für Deine Hilfe schonmal vorab!
Ich habe die Datei zu netupload hier hochgeladen:
http://www.netupload.de/detail.php?img=006780d8aba00bea7a85b00edf93...

Schau doch mal bitte auf die Zelle U5. Hier habe ich als Formel eingegeben:
=WENN(ISTLEER(T5);REST(D5-B5;1)+REST(G5-E5;1)+REST(J5-H5;1)+REST(M5-K5;1)+REST(P5-N5;1);V5)

Erreicht wird damit, dass wenn kein Eintrag im Feld Standardarbeitszeit (T5) dann werden die anderen Felder "von" "bis" (insgesamt 6x) berechnet. Ansonsten wird der über den Verweis auf V5 hergeholte Standard-Arbeitszeitwert ins Feld geholt. Ich habe mir für den Standard-Arbeitszeitwert in V5 schon mit dem Verweis in W5 geholfen, über welchen der zum Mitarbeiter gehörende Tarif ins Blatt geholt wird. Jetzt fehlt aber noch der Bezug zum Tag. Der Tag soll oben irgendwo eingegeben werden. Ich denke das Datum reicht. Dann kann man doch über eine Funktion den Wochentag darstellen und diese Angaben in die Formel einbeziehen. Vielleicht kannst du mir diesbezüglich weiterhelfen, denn im Moment kommt es mir vor, als ist das alles ein bißchen "von hinten durchs Auge ins Knie" ;-)

Vielen Dank!
Gruß
Torsten

Antwort 3 von Saarbauer

Hallo,

versuch es mal mit

=WENN(ISTLEER(T5);REST(D5-B5;1)+REST(G5-E5;1)+REST(J5-H5;1)+REST(M5-K5;1)+REST(P5-N5;1);VERWEIS(SVERWEIS(A5;Mitarbeiter!$B$2:$E$6;4;FALSCH);Arbeitzeiten!$A$2:$A$8;Arbeitzeiten!$B$2:$B$8))

in Zelle U5 und dann nach unten ziehen

Gruß

Helmut

Antwort 4 von fantalight

Hallo Helmut,

das ist schonmal super und im Prinzip eine Zusammensetzung meiner Formeln (...ich lerne gerade ;-))

Es fehlt aber der Bezug zum Wochentag (siehe mein vorheriges Posting)!!

Nehmen wir an, in Zelle A2 steht das Datum der Tagesplanung... dann muß hieraus der Wochentag ermittelt werden und im Blatt Arbeitszeiten brauche ich dann noch einen zweiten Bezug. Hier muß nicht nur die passende Tarifgruppe sondern auch noch die passende Sollzeit zum Wochentag ermittelt werden!! Das ist im Moment mein Problem. Kannst Du die Formel noch entsprechend ergänzen oder mir einen Ansatz liefern? Mit welcher Formel (VERWEIS??) erhalte ich im Blatt Arbeitszeiten den richtigen Bezug. Ich brauche hier ja einen horizontalen (Tarifgruppe) und vertikalen (Wochentag) Bezug.

Danke und Gruß
Torsten

Antwort 5 von Saarbauer

Hallo,

da ich auf dem Tagesplan keine Angabe zum Wochentag gefunden habe, muss du irgendwo eine entsprechende Angabe mach oder herholen. Ohne diese Angaben funktioniert es nicht.

Dann könnte es mit Summenprodukt() klappen.

Gruß


Helmut

Antwort 6 von fantalight

@Helmut:
Wie gesagt bzw. in geschrieben möchte ich in A2 das erforderliche Datum eintragen.

Ich habe jetzt schon etwas rumprobiert, aber leider bin ich einfach noch nicht durch die Formel SUMMENPRODUKT durchgestiegen. Kannst Du mir hier nochmal helfen?

Die Fakten sind.
Aufgrund des Datums in A2 muß der Wochentag ermittelt werden. Ich hab ja nun schon gelernt, dass dies so geht:
=WOCHENTAG(A2)
Ok... den Wochentag-Index muß ich dann im Blatt "Arbeitszeiten" entsprechend raussuchen. Hier gibt es momentan die Spalten "Mo-Do", Fr" und "WE". Diese kann ich ja dann dem Index der Wochentagformel zuordnen. Ist es hier vielleicht einfacher, doch 7 Spalten für die einzelnen Wochentagsindexe anzulegen? (Dies nur als Zwischenfrage) ..zurück zum eigentlichen. Im Blatt "Arbeitszeiten muß nicht nur der entsprechende Wochentag (Spalte) sondern auch die entsprechende Tarifgruppe(Zeile) gefunden werden.

Wer kann mir Hilfe zur Summenprodukt-Formel geben? Ich probiere natürlich auch selbst noch rum.

Danke und Gruß
Torsten

Antwort 7 von Saarbauer

Hallo,

seh mal die Datei an
http://www.netupload.de/detail.php?img=66c50f252b96b7b3ce1c56fee819...

müsste nach meiner Ansicht passen. Zu beachten Spalte U (geänderter Eintrag) und Blatt Arbeitszeiten

Gruß

Helmut

Antwort 8 von fantalight

Hallo Helmut,

vielen Dank für Dein Engagement. Ich hatte es jetzt bereits geschafft, die INDEX/VERGLEICH-Variante von Rainer entsprechend anzupassen. Dein SUMMENPRODUKT funzt aber auch einwandfrei und ich habe durch diese Bsp. die Formel jetzt schon etwas besser verstanden.

Mein Tagesplan ist noch lange nicht fertig. Ich muß bei den Standardwerten (U,K,...) noch ein paar WENN-Funktionen einbauen. Es gibt dort Auswahlmöglichkeiten, die nicht die Standardarbeitzeit aus dem entsprechenden Register erfordern, sondern einfach 00:00.

Außerdem muß ich das Register Tagesplan dann vervielfältigen, denn es soll 31mal bzw. je nach Monat/Tagesanzahl erscheinen. Kennst Du hierzu eine Funktion (geht vermutlich nur über Makro, oder)?

Die 31 Register sind erforderlich, da ich im 32. Register dann die Monatsübersicht einbinden will. Diese wird derzeit auch extra geführt, indem die Tagesstunden hier wieder händisch eingegeben werden. Das geht dann natürlich auch automatisch durch Verweise aus den einzelnen Tagesregistern.

Die Tagesübersicht bereitet mir noch Probleme. Ich möchte so wenig wie möglich an bisherigen Vorgaben ändern und im Moment sieht die Tagesübersicht für die Führungsriege so aus, dass hier nach verschiedenen Dienstart-Gruppierungen unterschieden wird. So ergeben zum Bsp. die Dienstarten "24/21, 24/23" die Dienstart-Gruppe-A und "24/52 u. 24/53" die Dienstart-Gruppe-B. Es gibt noch weitere Dienstart-Gruppen. Die stehen derzeit alle auf einem A4-Blatt-Ausdruck untereinander und in den einzelnen Gruppen die Zeiten und Namen je nach Tagesplan. Wer keinen Dienst hat ist dann eben in U, K, Lehrgang oder sonstiges Frei. Kann man sowas aus dem Tagesplan ganz ohne Makro erstellen oder ist das hoffnungslos?

Danke schon jetzt für Deine Antwort!
Gruß
Torsten

Antwort 9 von Saarbauer

Hallo,

das ist durchaus auch ohne Makro machbar, aber alles hat seine Grenzen. Im Moment sehe ich nicht, dass seine Anforderungen ein Makro erforderlich machen.

Aus meiner Sicht ist die Einteilung nach dienstartgruppen wahrscheinlich auch mit Summenprodukt hinzukriegen

Gruß

Helmut

Ich möchte kostenlos eine Frage an die Mitglieder stellen:


Ähnliche Themen:


Suche in allen vorhandenen Beiträgen: