Wie kann ich in Excel folgendes Problem lösen?
Folgendes Szenario:
Ich habe eine Tabelle, in welcher ich eine Datumstabelle, mit Schichtarten, und Arbeitszeiten eingetragen sind. Die Schichten sind abgekürzt, mit N, SN, N1, F, SF etc. Nun möchte ich, dass er mir in Spalte E die Arbeitszeiten der entsprechenden Schichten autmatisch einträgt. Also es sollte zum Beispiel so aussehen:
Wenn in Spalte D4 bis D369 Wert N steht, schreibe in E 22.00h. Steht dort SN, schreibe in E 00.00h, steht dort N1, schreibe 18.00h, steht dort F, schreibe 06.00h und so weiter und so fort. Wie kann man das realisieren? Anbei die Tabelle, damit man es vielleicht besser erkennt, was ich gerne möchte.
3 Antworten
Mache eine Hilfstabelle irgendwo, ich gehe mal davon aus, dass du die in J4:L10 hast (wo du die hinmachst ist aber egal, musst das dann nur in der Formel anpassen, kann auch auf ein anderes Blatt):
J4: Bezeichnung, K4: Start, L4: Ende; J5: N, K5: 6:00; L5: 14:00 (die Zeiten eben, da drunter für die anderen Schichten, ich gehe mal davon aus, dass die bis Zeile 10 geht).
In E4 schreibst du dann =SVERWEIS(D4; $J$5:$L$10; 2; FALSCH);
In F4 analog: =SVERWEIS(D4; $J$5:$L$10; 3; FALSCH);
Die Formel dann aus Zeile 4 iun die anderen Zeilen kopieren.
Spart im Vergleich zu vielen WENN Tipparbeit und lässt sich einfacher ändern/erweitern.
Nein, ich habe keine Hilfstabelle. Mit sowas hab ich mich noch nie beschäftigt :-) Für mich ist es mein privater Schichtplan,den ich jährlich im Voraus bekomme.Ich möchte aber eben nicht ständig die Zeit eintragen, sondern möchte eigtl. nur, dass er automatisch bei der entsprechenden Schichtkennzeichnung automatisch in die Spalten E und F im jetzigen Fall, die Zeit einträgt, von wann bis wann die Schicht geht etc. Mit dem vielen tippen hab ich genrell weniger ein Problem, nur will er mir halt einfach die Bedingung nicht anerkennen. Ich will halt gern, dass ich quasi in Spalte D nur die Schichtkennzeichnung eintippen muss (F,SF,LF,N1,N,SLN usw.) und er mir dann automatisch die Startuhrzeit einträgt, bzw. dann in Spalte F die Enduhrzeit.
ich versuch mich mal mit so einer Hilfstabelle. Generell versteh ich den Ansatz, kappier es zwar noch nicht so ganz, aber versuche es mal. Meine "Programmierkenntnisse" beschränken sich auf damals Qbasic, und selbst da hatte ich oft Probleme. Bisher bekam ich eigtl. alles immer ganz gut hin, aber da bin ich jetzt echt mal überfragt.
Die Hilfstabelle ist einfach "nur" eine Tabelle der Zuordnungen von Schichtname zu Zeiten. Wichtig ist nur, dass der Schichtname, also das was gesucht werden soll, in der ersten Spalte steht. Die ganze Arbeit mach SVERWEIS. Das bekommt als erstes, was es suchen soll, also die Zelle mit der Eingabe. Dann den Tabellenbereich, in dem es suchen soll, wobei nur die erste Spalte durchsucht wird. Der ist in der Formel mit $ festgelegt, damit er beim Kopieren der Formel gleich bleibt (absoluter Bezug). Dann die Nummer der Spalte, die als Ergebnis ausgegeben werden soll. Zum Schluss "FALSCH", weil nach genauer Übereinstimmung gesucht werden soll. Mit "WAHR" dort könnte man auch Tabellen mit von 0 bis 1, 1 bis 3, etc als Kriturium machen, dann muss die Hilfstabelle aber sortiert sind. Das ist hier nicht nötig (z.B. bei Punkten in Noten umrechnen habe ich das schon verwendet).
Die Funktion WENNS() ist Dein Freund (Die Formel "schlägt zu", sobald von links nach rechts gelesen eine Bedingung erfüllt ist. Daher ist manchmal die Reihenfolge der Bedingungen von großer Bedeutung):
=WENNS(D5="N";"22:00";D5="SN";"....";...)
Achtung: Du schreibst das nur in die erste Zeile 5 und kopierst dann die Formel nach unten. Du gibst nicht den ganzen Bereich D5:D300 an. Das ist in Deiner WENN-Formel schon falsch (bzw. funktioniert evtl. nur, weil Excel eine Matrix-Evaluierung draus macht).
Ich habs jetzt mal probiert und umgewandelt in folgende Formel:" =WENNS(D6="N1";"18:00h";D6="NF";"22:00h";D6="SLN";"22:00h";D6="F";"06:00h";D6="SF";"08:00h";D6="FL";"18:00h";D6="N";"22:00h";D6="S";"14:00h";D6="W";"07:00h";D6="T";"15:45h") " (Ja ich habe die Zeile in 6 geändert, weil ich noch was einfügen musste. Nun sagt er mir überall "#NAME?"
Meine Herren und Damen. Du knallst in Deiner Frage ein Bild hin, ohne zu sagen, was da sonst noch an Formeln, Berechnungen und Formatierungen und Zellinhalten steht, und schreibst dann blind ab. Soweit so gut und normal hier: Und dann beschwerst Du Dich mit der lapidaren Meldung. "Nun sagt er mir überall "#NAME?". Schönen Tag noch ... ich bin aber dann mal weg, denn bei dieser "Mach' mal"-Haltung habe ich keine weiteren Hilfe-Ambitionen.
Was ist denn mit Dir? Ich verstehe einfach nicht, warum er jetzt überall "#NAME?" schreibt, obwohl laut -meiner- logig eigtl. klar sein sollte, dass er Spalte D6 bis in meinem Falle 492 abfragt, ob dort ein N, ein F, ein SLN oder anderer Schichteintrag steht, und mir dann im Gegenzug in Spalte E den entsprechenden Text schreibt, wie z.B. 06:00h, oder 22.00h, oder welche Zeit auch immer. Excel ist halt nicht meine Stärke, für mich wäre quasi ein Befehl sinnvoll, wie z.B. "Wenn in Spalte D6 N steht, schreibe 22.00h in Spalte E6, steht jedoch ein F, dann schreibe 06:00h in spalte E6 und so weiter. Den Rest der Tabelle, hab ich mir schon raussuchen können, wie z.B. eine Trennung nach Wochen, Sonntage und Samstage extra markieren usw..
Ich hab sogar mal versucht, das ganze ins englische zu übernehmen mit "IFS". Ich verstehe in dem Falle einfach den Logikfehler nicht, warum er mir eben jetzt #NAME? anzeigt. Aber okay, dann schau ich einfach mal weiter..Werd mich mal mit der unten aufgeführten Hilfstabelle versuchen zu beschäftigen. Excel-Programmierung ist halt nicht meine Stärke.
#Name bedeutet, dass Excel die Formel unbekannt ist, hier vermutlich WENNS, ich tippe mal du hast eine Version älter 2019
z.BG. so
=WENN($D5="N";"22:00";WENN($D5="SLN";"23:00";WENN($D5="SF";"13:00";WENN($D5="F";"14:45";WENN($D5="FL";"18:00";WENN($D5="S";"22:45";WENN($D5="N1";"03:45";WENN($D5="NF";"05:45";WENN($D5="SN";"00:00";"")))))))))
... usw. Formel kopieren und die Zerit für das Ende entsprechend anpassen
Ich würde folgendes vorziehen
Danke, das werd ich in der nächsten Variante meines Schichtplanes mal austesten. Jetzt hab ich es mit dem Hinweis von @iQa1x erst mal mit einer Hilfstabelle lösen können. Hab diese Hilfstabelle einfach um einige Zeilen erweitert, z.B. für freie Tage etc. damit nicht in dem Plan zwischendrin "#NV" steht. so langsam komm ich ein wenig dahinter :)
Die Formel um Wennfehler ... ergänzen siehe meine Antwort
=WENNFEHLER(SVERWEIS($D5;$H:$J;2;FALSCH);"")
VIELEN lieben Dank. Ich hab zwar jetzt ein bisschen gebraucht, aber nun hab ich es geschafft. das ist SO toll.... das einzige was mir -noch- nicht gefällt, ist, dass an Tagen wo keine Schicht ist, er eben dann #NV einträgt :)