Office: Bitte um Hilfe: Datum abhängig von 2 Zellen

Da ich neu in diesem Forum bin, erstmal ein Hallo an alle!

Ich benutze Office 2007 und möchte einen Wochenplan mit verschiedenen Aufgaben für Mitarbeiter erstellen. Der Wochenplan soll durch einen Verantwortlichen einer jeweiligen Schicht erstellt werden.

Da der Verantwortliche in der Schicht auch nach dem Schichtrythmus arbeitet, soll in einer Spalte einer Excel-Tabelle das Datum der darauf folgenden Schichttage automatisch eintragen werden.

Beispiel:

Am 01.12.2008, fangen die Mitarbeiter am Mittag an und arbeiten bis 18:00 Uhr. Am 02.12. geht die Schicht von Früh bis Mittag und von 18:00 bis um 06:00 Uhr am 3.12. Die neue Schichtfolge beginnt am 05.12.2008.

Wenn der Plan nun in der Mittagsschicht erstellt wird, sind jedoch mehr Tage bis zur nächsten Schichtfolge, als wenn er z.B. in der Nachtschicht erstellt wird.

Ich habe mir nun eine Exel-Tabelle erstellt, in der eine Art Kalender angezeigt wird.

Eine Spalte enthält das Datum, die 2. Spalte enthält die Textkürzel:

"M" "F-N" "N" "-". Die Kürzel werden automatisch zu den jeweiligen Schichttagen generiert.

Es soll nun abgeprüft werden, ob das heutige Datum im "Kalender" enthalten ist. Wenn es enthalten ist, soll eine Prüfung stattfinden, welches Kürzel sich in der 2. Spalte zu diesem Datum befindet. Abhängig von Kürzel und dem Datum, soll das Datum des Beginns der nächsten Schichtfolge in der Haupttabelle berechnet werden.

Ich habe eine Beispieldatei mit angehängt.

Ich hoffe hier kann mir jemand helfen!

 

Hallo Mav_Lrey,

wenn dieser 4-Tagerhythmus das ganze Arbeitsjahr hindurch strikte eingehalten wird, ist das Ganze relativ einfach zu realisieren:
Ausgehend von Deiner Beispieldatei im Arbeitsblatt 'Tabelle2' folgende Formeln eingeben:
in Zelle B8: =HEUTE()-REST(HEUTE();4)
in Zelle B9: =TEXT(B8;"TTTT")
in Zelle B18: =B8+1
in Zelle B19: =TEXT(B18;"TTTT")
in Zelle B27: =B18+1
in Zelle B28: =TEXT(B27;"TTTT")
Die Formel in Zelle B8 bewirkt Folgendes: am Tag der 1. Schicht (M) stellt sich das Datum automatisch auf diesen Tag ein und bleibt 4 Tage (M / F-N / N / - ) lang auf diesem Datum stehen, erst nach Ablauf dieser 4 Tage am 1. Tag (M) der nächsten Schichtperiode springt es quasi 4 Tage weiter auf das Datum dieses Tages.
Da die Formeln in B18 und B27 von B8 abhängen (die jeweiligen Folgetage darstellen), bleibt der Inhalt von Spalte B vier Tage lang automatisch konstant; man braucht daher dafür nicht den Inhalt von Arbeitsblatt 'Tabelle1'.
Den Zellen B8, B18 und B27 nötigenfalls noch ein Datumsformat verpassen - das ist alles.

 

Danke erstmal für die Hilfe!

Ich muss leider noch bis morgen warten, bis ich weiß ob dein Vorschlag funkioniert! :-)

MFG

Ich

 

Hallo Mav_Lrey,

es ist egal, zu welchem Zeitpunkt die Formel in die Zelle B8 eingegeben wird. Die Formel setzt allerdings voraus, dass der 1. Tag (M) einer Schichtperiode zB. der 30.12.2008 ist (und nicht 31.12.2008) oder der 3.1.2009 oder der 7.1.2009 etc.
Sollte die Formel in Zelle B8 auf einen anderen Schichtperiodenbeginn umzustellen sein, zB. Schichtperiodenbeginn (M) am 31.12.2008 statt wie vorhin am 30.12.2008, dann lautet die Formel in B8: =HEUTE()-REST(HEUTE()-1;4).
Sollte dagegen der 1.1.2009 der 1. Tag (M) einer Schichtperiode sein, müsste die Formel lauten: =HEUTE()-REST(HEUTE()-2;4).
Bei einem Beginn am 2.1.2009 lautet sie: =HEUTE()-REST(HEUTE()-3;4).
Das sind dann auch schon alle Varianten, die auftreten können.

Zur Erklärung: Excel zählt einfach die Tage der Reihe nach durch, beginnend beim 1.1.1900: dieser hat daher die Nummer 1; der 30.12.2008 bekommt auf diese Weise die Nummer 39812.
Die HEUTE()-Funktion liefert einfach die Nummer des heutigen Tages (ausgehend vom Systemdatum des PCs).
Die REST(Wert;4)-Funktion liefert den Rest, wenn man Wert ganzzahlig mit 4 durchdividiert. Dem 30.12.2008 entspricht die fortlaufende Zahl 39812, dividiert man diese durch 4, erhält man als Rest 0, somit ergibt sich für Zelle B8 am 30.12.2008: 39812-0=39812
Steht das Systemdatum des PCs auf 31.12.2008, so liefert die HEUTE()-Funktion als fortlaufende Zahl 39813; dividiert man diese ganzzahlig durch 4, so bleibt ein Rest von 1; es ergibt sich in Zelle B8 folgender Wert: 39813-1=39812; und 39812 ist, wie wir vorhin sahen, die fortlaufende Zahl für den 30.12.2008. Excel speichert intern stets nur die fortlaufende Zahl. Dass diese wie ein Datum angezeigt wird, ist einzig und allein nur durch die für die jeweilige Zelle eingestellte Formatierung verursacht.

 

Nochmal danke! Hast mir sehr geholfen!

MFG

Ich

 

Hallo ich bin es nochmal!

Habe am 30.12.2008 die Fomel "=HEUTE()-REST(HEUTE();4)" in die Zelle B8 eingefügt. Heute beginnt auch die Mittagsschicht um 12:00 Uhr.
Jetzt habe ich das Problem, dass er anstelle des Datums der nächsten Schicht das Heutige Datum anzeigt.

Habe Versucht, dass Problem mit der Formel "=WENN(=HEUTE()-REST(HEUTE();4)=HEUTE();HEUTE()+4;HEUTE()-REST(HEUTE();4))" zu lösen.

Er zeigt jetzt auch den 2.12.2008 als Beginn der neuen Schicht an. Allerdings ist mir unklar, ob er morgen immer noch das Korrekte Datum der nächsten Schicht anzeigt.

Hast du vielleicht einen anderen Lösungsvorschlag als meine "WENN-Formel"?

MFG

Ich

 

Hallo Mav_Lrey,

Deine obige Formel löst leider eine Fehlermeldung aus, weil das 2. "="-Zeichen nach der 1. öffnenden Klammer zuviel ist. Entfernt man dieses, ergibt sich folgende Situation:
falls HEUTE(), dann Anzeige für (M) in B8:
30.12.2008 03.01.2009
31.12.2008 30.12.2008
01.01.2009 30.12.2008
02.01.2009 30.12.2008
03.01.2009 07.01.2009
04.01.2009 03.01.2009 usw.
Diese Situation wirst Du wahrscheinlich nicht wollen.
Wenn Du bereits während der laufenden Schichtperiode die Daten der folgenden Schichtperiode angezeigt haben willst, wenn also
falls HEUTE(), dann Anzeige für (M) in B8:
30.12.2008 03.01.2009
31.12.2008 03.01.2009
01.01.2009 03.01.2009
02.01.2009 03.01.2009
03.01.2009 07.01.2009
04.01.2009 07.01.2009 usw.
sein soll, dann musst Du als Formel in B8
=HEUTE()+4-REST(HEUTE();4)
eingeben (das 2. "+4" nach der 2. HEUTE()-Funktion kann man sich schenken aufgrund der mathematischen Eigenschaften der REST-Funktion, denn REST(a+4;4)=REST(a;4)). Die restlichen Formeln bleiben unverändert.

 

Habe noch ein Problem! Wollte kein neues Thema eröffnen!

In unten angehängtem Beispieldatei befinden sich 3 Tabellen.

Eine Tabelle soll eine Art Eingabemaske sein. Die andere, eine Maske zum Ausdrucken (Tabelle 2). Die 3. Tabelle ist ein Beispiel, wie die 2. Tabelle am Ende aussehen soll.

In Tabelle 1 kann ausgewählt werden, wer mit wem zusammenarbeitet.

z. B. Der Fritz soll mit dem Schorsch an der Maschine "K" zusammenarbeiten. Das Ganze in der Frühschicht.

Per Dropdown wird nun der Wert "K" beim Fritz und der Wert "K" beim Schorsch gesetzt (in der Spalte mit der Überschrift "Früh").

In der Tabelle 2 sollen nun die Namen der beiden Arbeiter im umrandeten Bereich für die Frühschicht stehen. Der eine Name soll in der 1. Zelle neben dem "K" also in der Zelle C7 stehen, der andere Name soll in der Zelle D7 Stehen.

Wenn in der Eingabemaske die Maschine V ausgewählt wird, sollen die 2 Personen in der Tabelle 2 neben dem V stehen usw.

Dies soll in allen Kombinationen funktionieren. So das die Druckansicht wie in der Tabelle 3 aussieht.

Das Ganze soll natürlich auch für die anderen Schichten funktionieren. Wenn ich z. B. in der Eingabemaske bei der Nachtschicht etwas eintrage!

Danke für die Hilfe!

MFG

Ich

 

Hallo!

Schreibe diese Formel in Tabelle2!C3, schließe die Eingabe mit Strg+Shift+Enter ab und fülle sie bis D8 aus:

=WENN(SPALTE(A$1)>ZÄHLENWENN(Tabelle1!$B:$B;$B3);"";INDEX(Tabelle1!$A:$A;KKLEINSTE(WENN(Tabelle1!$B$2:$B$999=$B3;ZEILE($2:$999));SPALTE(A$1))))

Für die Mittags- und die Nachtschicht änderst du die fette Spalte B entsprechend in C bzw. D.

 

Was muss ich in der Formel verändern, wenn die Zellenbereiche wie unter folgenden Download links aussehen.

Tabelle 1



Tabelle 2



Was macht die Formel eigentlich genau?

Sorry! Ich bin Excel Einsteiger!

MFG

Ich

 

Hallo Mav_Lrey,

ich habe die Formel von <Schatzi> an Deine neuen Verhältnisse angepasst: bitte Formel als Matrixformel in Zelle Ausgabe!D7 eingeben:
=WENN(SPALTE(A$1)>ZÄHLENWENN(Eingabe!$C$13:$C$999;$C7);"";INDEX(Eingabe!$A:$A;KKLEINSTE(WENN(Eingabe!$C$13:$C$999=$C7;ZEILE($13:$999));SPALTE(A$1))))
Eingabe als Matrixformel heißt wiederum: wenn Du die Formeleingabezeile verlässt, die Tasten Strg+Umschalt+Eingabe drücken statt bloß Taste Eingabe. Die Formel erscheint dann in der Zelle in geschweiften Klammern.
Diese Formel aus D7 in die restlichen Zellen des Bereiches D7:E14 kopieren.
Für den Bereich Ausgabe!D15:E22 genauso vorgehen, jedoch die beiden Formelstellen $C$13:$C$999 durch $D$13:$D$999 ersetzen.
Für den Bereich Ausgabe!D23:E32 genauso vorgehen, jedoch die beiden Formelstellen $C$13:$C$999 durch $E$13:$E$999 ersetzen.

Was die Funktionalität betrifft:
1) Des Pudels Kern des Ganzen ist die Funktion: INDEX(Eingabe!$A:$A;ZeilenNr); Diese Formelpassage bewirkt, dass im Arbeitsblatt 'Eingabe' in der Spalte A (Mitarbeiterspalte) in der Zeile mit der Nr. 'ZeilenNr' nachgeschaut wird und der Inhalt dieser Zelle (also der gesuchte Mitarbeiter) in Zelle 'Ausgabe!D7' gestellt wird.
2) Diese 'ZeilenNr' wird aber ihrerseits wieder durch eine Funktion ermittelt: KKLEINSTE(WerteBereich;SPALTE(A$1)); sie tut nichts anderes, als dass sie im 'WerteBereich' nachschaut und sich von dort den (erst)kleinsten Wert herausfischt. Denn der Wert des 2. Parameters 'SPALTE(A$1)' ist gleich 1; dieser gibt nämlich an, der wievielt-kleinste Wert aus 'WerteBereich' herausgeholt wird. Diese auf den 1. Blick aufgeblähte Schreibweise für die Konstante 1 wurde gewählt, weil sich die Formel bequem in die Spalte E kopieren lässt (sie lautet dort SPALTE(B$1) und hat den Wert 2: Somit wird für Spalte E der zweitkleinste Wert aus 'WerteBereich' geholt).
3) Und damit es nicht zu einfach wird, wird auch 'WerteBereich' durch eine Formel ermittelt, wobei hier der Grund verborgen ist, warum die ganze Formel unbedingt als Matrixformel einzugeben ist: WENN(MittagBereich=$C7;ZEILE($13:$999));
Dabei geht der 'MittagBereich' im Arbeitsblatt 'Eingabe' in Spalte C von Zeile 13-999 (= ein Spaltenvektor), die Funktion ZEILE($13:$999) gibt die Spaltenvektorkonstante {13;14;15;...;998;999} zurück und in Zelle Ausgabe!$C7 steht der Wert ('Ü'), nach dem gesucht wird im 'MittagBereich'. An all den Stellen in 'MittagBereich', an denen der Wert von $C7 gefunden wird, wird die entsprechende ZeilenNr. aus der Spaltenvektorkonstante {13;14;...;999} zurückgegeben; an den restlichen Stellen der Wert 'FALSCH'.
4) Man erhält somit durch die WENN-Funktion von vorhin eine Liste mit Zeilennummern, in denen Ausgabe!$C7 in der Spalte 'MittagBereich' gefunden wurde, wobei die erstkleinste Zeilennummer für die Spalte Ausgabe!$D bestimmt ist und die zweitkleinste Zeilennummer für die Spalte Ausgabe!$E. Mit der nun bekannten Zeilennummer kann jetzt mittels der INDEX-Funktion im Spaltenbereich Eingabe!$A:$A der gesuchte Mitarbeiter lokalisiert werden und in Spalte D bzw. E von Arbeitsblatt 'Ausgabe' gestellt werden.
5) Würde man die Formel so belassen, steht man jedoch vor der unschönen Situation, dass im Arbeitsblatt 'Ausgabe' eine Reihe von "Fehlern" angezeigt werden, nämlich dann in Spalte Ausgabe!$D, wenn kein Mitarbeiter bzw. in Spalte Ausgabe!$E, wenn kein oder nur 1 Mitarbeiter eingeteilt wurden.
Um dieses Problem zu lösen, dient dazu die äußerste WENN-Funktion: WENN(SPALTE(A$1)>ZÄHLENWENN(MittagBereich;$C7);"";Mitarbeiter); Sie zählt einfach im 'MittagBereich' nach, wie oft 'Ausgabe!$C7' dort vorkommt; und ist die gezählte Zahl zu klein, wird eine leere Zeichenkette in Ausgabe!$D bzw. Ausgabe!$E ausgegeben und somit eine Fehleranzeige unterdrückt.

 

Danke! Gibts auch einen Möglichkeit einen Namen 2x in einer Spalte anzeigen zu lassen? Abhängig vom Wert, der angegeben wird?

Eingabe:
z. B. Fritz, ÜVS
, ÜVS

Ausgabe

ÜVS, Fritz

ÜVS, Fritz


MFG

Ich

 

Hallo Mav_Lrey,

Du meinst, deshalb hast Du im Arbeitsblatt 'Eingabe' im Beispiel vom 1.1. plötzlich 2 Zeilen pro Mitarbeiter vorgesehen gegenüber 1 Zeile im Beispiel vom 31.12. ?
1) Diese erweiterte Möglichkeit funktioniert ab sofort ohne jede Änderung an den Formeln im Arbeitsblatt 'Ausgabe'.
Was jedoch etwas anzupassen ist, ist Dein Arbeitsblatt 'Eingabe' in Spalte A (bzw. B). Um zB. die 2. Zeile des 1. Mitarbeiters (zB. Fritz) im Arbeitsblatt 'Ausgabe' sichtbar zu machen, genügt es, wenn Du zB. den Namen des 1. Mitarbeiters (im Bereich Eingabe!A13:B13) in den darunterliegenden freien Bereich Eingabe!A14:B14 kopierst. Weil nämlich dort bisher ein leerer Bereich war, deshalb hast Du im Arbeitsblatt 'Ausgabe' in den Spalten D bzw. E keinen Mitarbeiter vorgefunden.
2) Wenn Du die doppelte Namensanführung in Spalte A(bzw.B) von Arbeitsblatt 'Eingabe' nicht schön findest, so gibt es die Möglichkeit, den 2. Namen mittels der benutzerdefinierten Formatierung ';;;' optisch wegzublenden.
Falls Du nicht weißt, was das benutzerdefinierte Zellformat ';;;' bewirkt, hier die Kurzerklärung:
Jede Arbeitsblattzelle kann man formatieren und zwar mit maximal 4 Formatvarianten, die jeweils durch einen Strichpunkt voneinander zu trennen sind: die 1.Variante gilt für positive Zahlen, die 2. für negative, die 3. für Null-Werte und die 4. für Text. Indem alle 4 Formatvarianten leer belassen werden, werden alle Wertvarianten optisch weggeblendet.
3) Das Gleiche wie in 1)+2) auch mit den anderen Mitarbeitern machen.
4) Falls (die) Namen gelegentlich zu ändern sind, kann man in den 2. Namensbereich, der optisch weggeblendet wurde, vorsichtshalber auch eine Formel geben (statt des simplen Kopierens), um die Änderung in beiden Namensbereichen zu garantieren: zB. wenn der 1. Mitarbeitername in Eingabe!A13:B13 steht, der weggeblendete 2. Name in Eingabe!A14:B14 steht, kann man in Zelle Eingabe!A14 die Formel =A13 geben (Anm.: Die Formel bzw. der Wert steht in verbundenen Zellbereichen in der Zelle, die sich in der linken oberen Ecke des Bereiches befindet.)

 

Danke nochmal für deine Hilfe! Ich war mehrere Tage nicht da, deshalb melde ich mich jetzt.

Mittlerweile funktioniert mein Excel projekt einigermaßen!

Trotzdem hätte ich noch eine letzte Frage:

Gibt es eine möglichkeit, abhängig von einer DropDown Zelle die Namen angeben zu lassen?

Im Formular eingabe stehen wie sonst auch in der Spalte A die Namen.

In der Spalte I gibt es zu jedem Namen ein DropDown feld.
Bereich: I13:I40

In diesem Feld sind mehrere Möglichkeiten zur Auswahl:

AZA
AZAF
AZAN
AZAMF
AZAFN
AZAMN


Mein Ausgabeformular sieht nun folgendermaßen aus:

Der relevante Bereich im Ausgabeforumalar ist wieder in Mittag/Früh/Nacht angeordnet

Bereich Mittag:
In der Zelle H9 steht AZA

Nun sollen in den Zellen I9, J9, K9 wieder die Namen stehen.

Bereich Früh:

In der Zelle H16 steht AZA

Nun sollen in den Zellen I16, J16, K16 wieder die Namen stehtn.

Bereich Nacht

siehe Oben

Ich möchte nun, dass wenn in der Tabelle Eingabe z.B. der Wert AZAMF gewählt wird, automatisch in der Tabelle Ausgabe der Name sowohl im Bereich Mittag, als auch im Bereich Früh erscheint.

Wenn der Wert AZAFN in der Tabelle ausgewählt wird, soll der Name des Mitarbeiters im Bereich Früh und im Bereich Nacht stehen.

Wenn der Wert AZAMN ausgewählt wird soll der Name in den Bereichen Mittag und Nacht stehen.

AZA=Nur Bereich Mittag
AZAF= Nur Früh
AZAN = Nur Nachts

Vielleicht kannst du mir ja nochmals helfen!

 

Hallo Mav_Lrey,

ich hoffe, ich habe Dich richtig verstanden, wenn ich Folgendes vorschlage - Du hast außerdem angegeben, dass Du Excel2007 hast; das macht es etwas einfacher.

1) Du sollst Dir 3 freie Zellen auswählen und in diese jeweils 1 Zeichenkette eingeben (das sind Suchzeichenketten zur Identifizierung von 'Mittag', 'Früh' und 'Nacht'). Ich schlage dafür vor:
Zelle Eingabe!L13 mit der Zeichenkette '|AZA|AZAMF|AZAMN|'
Zelle Eingabe!L14 mit der Zeichenkette '|AZAF|AZAMF|AZAFN|'
Zelle Eingabe!L15 mit der Zeichenkette '|AZAN|AZAFN|AZAMN|'
Bitte jeweils ohne die Hochkommas eingeben.

2a) Dann gibst Du im Arbeitsblatt 'Ausgabe' in Zelle Ausgabe!I9 folgende Matrixformel ein:
=WENNFEHLER(INDEX(Eingabe!$A:$A;KKLEINSTE(WENN(WENNFEHLER(FINDEN(VERKETTEN("|";Eingabe!$I$13:$I$999;"|");Eingabe!$L$13)>0;FALSCH);ZEILE($13:$999));SPALTE(A$1)));"")
Bitte die Eingabe der Matrixformel wieder mit den Tasten Strg+Umschalt+Eingabe abschließen.
Diese Formel in den Zellbereich Ausgabe!J9:K9 kopieren.

b) Dann gibst Du im Arbeitsblatt 'Ausgabe' in Zelle Ausgabe!I16 folgende Matrixformel ein:
=WENNFEHLER(INDEX(Eingabe!$A:$A;KKLEINSTE(WENN(WENNFEHLER(FINDEN(VERKETTEN("|";Eingabe!$I$13:$I$999;"|");Eingabe!$L$14)>0;FALSCH);ZEILE($13:$999));SPALTE(A$1)));"")
Bitte die Eingabe der Matrixformel wieder mit den Tasten Strg+Umschalt+Eingabe abschließen.
Diese Formel in den Zellbereich Ausgabe!J16:K16 kopieren

c) Dann gibst Du im Arbeitsblatt 'Ausgabe' in Zelle Ausgabe!I24 folgende Matrixformel ein:
=WENNFEHLER(INDEX(Eingabe!$A:$A;KKLEINSTE(WENN(WENNFEHLER(FINDEN(VERKETTEN("|";Eingabe!$I$13:$I$999;"|");Eingabe!$L$15)>0;FALSCH);ZEILE($13:$999));SPALTE(A$1)));"")
Bitte die Eingabe der Matrixformel wieder mit den Tasten Strg+Umschalt+Eingabe abschließen.
Diese Formel in den Zellbereich Ausgabe!J24:K24 kopieren.

3) Die 3 fett gekennzeichneten Formelstellen enthalten die Zelladressen für die 3 Suchzeichenketten von Punkt 1). Falls Du diese woanders hingibst, musst Du die fetten Formelstellen entsprechend anpassen. Die 3 Formeln sind völlig identisch bis auf die fetten Stellen. Du kannst die Suchzeichenketten statt ins Blatt 'Eingabe' auch als Direktkonstanten anstelle der fetten Formelstellen eingeben (dann allerdings unbedingt in "..." einschließen, zB. "|AZA|AZAMF|AZAMN|" ).
Falls Du statt des Trennzeichens "|" ein anderes lieber hast, ist auch das möglich; wichtig dabei ist, dass Du es in der Suchzeichenkette und in der dazugehörigen Formel änderst (in den Formeln kommt es jeweils 2x vor, in den Suchzeichenketten jeweils 4x !!).