Excel: komplexer Schichtplan (Excel Nachhilfe für Anfänger)

dödel

New member
Registriert
11 Apr. 2016
Beiträge
102
MoinMoin,

ich stehe gerade bei Excel auf dem Schlauch:

Ich habe eine Tabelle und möchte in einem Feld die Summer der Felder eines Bereichs berechnen die eine bestimmte Farbliche Markierung haben (das ganze für 5 unterschiedliche Farben separat).

Habt ihr da eine Idee?
 
Zuletzt bearbeitet:
OK, das mit den Farben habe ich hinbekommen, danke dir.

Aber ich merke gerade dass ich dringend mal eine Excel-Schulung machen muss :huh:

31-10-_2016_04-53-06.png

Mein nächstes Problem:

Für die Zeilen B3 bis B18 soll in Spalte AH eine Summe berechnet werden. Und zwar aus den jeweiligen Zeilen. Wenn in der Zeile eine 1 oder 2 steht soll jeweils eine 14 addiert werden, bei einer 3, 4, 5 oder einem U eine 8. Die Summe soll dann in Spalte AH in der jeweiligen Zeile ausgegeben werden.

Die Zuordnung 1=14 etc. bekomme ich nicht hin :wacko:
 
Ich verstehe jetzt nicht so ganz, was du machen willst. Ich nutze für solche Umwandlungen gerne eine Hilfsspalte mit Wenn-Funktion, bei der die "Freitextzelle" in eine "addierbare" Zelle übersetzt wird: Wenn C3 gleich 1 oder 2, dann schreibe 14 in <xyz>, ansonsten schreibe 8 in <xyz>. Das dann für jede deiner Freitextzellen. Wenn du das geschickt machst (z. B. in ein separates Tabellenblatt auslagern), musst du die Formel nur einmal eintippen und kannst den Rest per Auto-Ausfüllen machen lassen - die Bezüge wandern dann jeweils von C3 zu C4 bzw. von C3 zu D3 mit.

Wenn dus nicht hinbekommst, kann ich mich auch mal per Teamviewer draufschalten => PN bei Bedarf.
 
Du solltest wirklich mit Hilfs-Spalten arbeiten, ich würde in diesem Falle zwei Hilfs-Spalten vorschlagen, in der ersten zählst du entsprechend die 1er und 2er und in der 2. dann die 3en,4en,5en und U's.
Das machst du wie Phil schon geschrieben hat mit "wenn dann"

Abschließend kannst du dann die gezählten 1er und 2er sowie die 3,4,5 und U's entsprechend multiplizieren mit 14 und 8 ;)
 
Danke nochmal!

Das Ganze soll ein Dienstplan werden, 1, 2, 3, 4, 5 und U stehen für die Unterschiedlichen Schichten. In Spalte "AH" sollen die entsprechenden Stunden addiert ausgegeben werden.

OK, mit WENN und SVERWEIS habe ich auch das hinbekommen, super Sache!!


Jetzt geht's zur Fehleranalyse des Ganzen :eek:

Die Tabelle habe ich mit etlichen "bedingten Formatierungen" bearbeitet, z.B. ändert sich die Füllfarbe der Zellen je nachdem was für eine Zahl eingegeben wird. Bei Ausfüllen des Plans funktioniert das auch wunderbar, wenn ich aber Änderungen vornehme harkt es. Wenn ich beispielsweise via Leertaste eine Schicht lösche, bleibt das Feld in der ursprünglichen Farbe und die Summe kann nicht mehr berechnet werden (#NV). Mit "bedingter Formatierung" habe ich das nicht weg bekommen...
 
Wenn ich beispielsweise via Leertaste eine Schicht lösche
Mit einer Leertaste löscht du nichts sondern schreibst ein Leerzeichen in die Zelle. Das behandelt Excel dann als Text und berechnet nicht mehr. Also über die Entf.-Taste löschen oder eine Lösung basteln, dass dann z.B. eine Null eingetragen wird.
 
Mit einer Leertaste löscht du nichts sondern schreibst ein Leerzeichen in die Zelle. Das behandelt Excel dann als Text und berechnet nicht mehr. Also über die Entf.-Taste löschen oder eine Lösung basteln, dass dann z.B. eine Null eingetragen wird.

Ja klar, das wusste ich auch mal. Excel blöd halt :facepalm:

- - - Beitrag zusammengeführt - - -

und weiter geht's...mittlerweile glaube ich, dass es schneller gegangen wäre wenn ich dafür ein kleines Programm geschrieben hätte, in einer Programmiersprache die ich zumindest halbwegs beherrsche :huh:


Als nächstes möchte ich, auf separaten Tabellenblättern (benannt nach den Mitarbeitern), für jeden seinen individuellen Dienstplan ausgeben, in dieser Form:

05-11-_2016_04-47-50.png


In Spalte C soll dann die entsprechende Schichtbezeichnung zB "1400-2200", wenn keine Schicht in Tabelle1 eingetragen ist, soll die Zeile leer bleiben.

Als Beispiel am besten Mitarbeiter "Philip", da bei ihm alle 6 Möglichkeiten vorkommen.
Wenn ich in Tabellenblatt "Philip" in Spalte "C" nur nach einem Kriterium suchen müsste, würde die Formel so aussehen: "=WENN(Tabelle1!C5=1;"früh";"")"
(Nebenfrage: wie kann man diese Formel in der Gesamten Spalte "C" auf "Philip" erweitern? Problem: Wenn ich die Formel nach unter erweitere (durch ziehen am kleinen +) dann übernimmt er die vertikalen Werte aus "Tabelle1 Spalte C", es soll aber Horizontal in "Tabelle Reihe5" abgeglichen werden. Da gibt es doch sicherlich etwas einfacheres als das händisch zu machen??)
Zurück zum Eigentlichen:
Mein Problem ist nun die Verschachtellung der Wenn-Oder Funktion um die 6 verschiedenen Werte abzufragen. Ich habe jetzt verschiedene Versionen ausprobiert (Hauptsächlich die Klammern verschoben, Runde statt Eckige und umgekehrt, aber ich komme zu keiner Lösung

"=WENN(ODER(Tabelle1!C5=1;"1700'-'0700"[Tabelle1!C5=2;"2000'-'1000!];[Tabelle1!C5=3;"0600'-'1400"];[Tabelle1!C5=4;"1400'-'2200"];[Tabelle1!C5=5;"2200'-'0600"];[Tabelle1!C5=U;"Urlaub"];"";))"

"=WENN(ODER(Tabelle1!C5=1;"1700'-'0700"(Tabelle1!C5=2;"2000'-'1000!);(Tabelle1!C5=3;"0600'-'1400");(Tabelle1!C5=4;"1400'-'2200");(Tabelle1!C5=5;"2200'-'0600");(Tabelle1!C5=U;"Urlaub");"";))"

Hier nochmal Tabelle 1.

05-11-_2016_05-11-43.png

Wenn es erlaubt ist, kann ich auch gerne die gesamte Tabelle hochladen, das könnte es für die freundlichen Helfer einfacher machen die Übersicht zu bekommen.
 
Ich habe mal den Titel angepasst und pushe hier mal...
 
Hast du schonmal über das Makro Rekording nachgedacht? Ich war/bin nie ein Freund von diesen verschachtelten, langen Formeln gewesen und hab das immer in VBA gemacht wenns etwas komplexer wurde. Dabei könnte ich Dir helfen.
 
Nebenfrage: wie kann man diese Formel in der Gesamten Spalte "C" auf "Philip" erweitern? Problem: Wenn ich die Formel nach unter erweitere (durch ziehen am kleinen +) dann übernimmt er die vertikalen Werte aus "Tabelle1 Spalte C", es soll aber Horizontal in "Tabelle Reihe5" abgeglichen werden. Da gibt es doch sicherlich etwas einfacheres als das händisch zu machen??
mit einem absoluten Bezug, also vor der 5 ein $, sollte dann auch mit dem ausfüllen funktionieren.


Mein Problem ist nun die Verschachtellung der Wenn-Oder Funktion um die 6 verschiedenen Werte abzufragen.
dazu die WENN Funktion verschachteln ohne oder, (wenn: erfüllt;Dann=>Wert eintragen;sonst wenn:nächster Wert; Dann ...;sonst wenn: nächster Wert ...) allerdings gibt es eine Grenze wieviele Wenns man schachteln kann und ich weis jetzt nicht wieviel das waren:rolleyes:

Ansonsten geb ich Poekelwurst recht das sich hier auch VBA anbietet.

Gruß seaman
 
Nein, mit VBA habe ich mich bisher noch auseinander gesetzt. Aber ich suche mir da mal ein Tutorial raus, könnt ihr da was empfehlen?
 
Zur Problemlösung könnte eventuell Dienstplan mit Excel 3 helfen, zu finden hier. Sehr durchdacht mit guter Dokumentation und auch noch kostenlos :) Ich hab für unser kleines Team eine eigene Excel-Lösung angefangen, bin dann aber doch auf die bereits entwickelte Umgebung ausgewichen, ein Blick lohnt sich!

Gruß,
Nommo
 
Nein, mit VBA habe ich mich bisher noch auseinander gesetzt. Aber ich suche mir da mal ein Tutorial raus, könnt ihr da was empfehlen?

Starte erstmal den Makro Recorder (Entwicklungs-Tools müssen dafür glaube ich aktiviert sein im Menüband) befülle ein paar Zeilen, beende die Makroaufzeichnung.
Drücke ALT + F11 und schaue unter "Module" das Modul1 an. Wenn du dann in die Sub Makro1() klickst, kannst du mit F8 Schritt für Schritt nachvollziehen was du eben per Hand gemacht hast und wie es in VBA aussehen kann. (Die Fenster der Excelmappe und der VBA Entwicklungsumgebung nebeneinander anzeigen lassen)

Die Programmierung mit dem Rekorder für komplexere Sachen ist umständlich und nicht zu empfehlen. Für den Einstieg kannst du damit aber kleine Aufgaben ohne Programmierkenntnisse direkt lösen oder mindestens einen Ausgangspunkt erzeugen.
 
Das Problem bei Excel ist, dass mit der "konventionellen" Methode irgendwann Aufwand und Ergebnis massiv auseinanderdriften. Ich hatte auch schon ein paar dieser Aufgaben, die ganz einfach anfingen ("mach mal eine Auswertung von..."). Am Anfang ist alles ganz leicht, aber irgendwann steht man plötzlich in Zelle EF 134, hat eine auf fünf Ebenen verschachtelte Wenn-Funktion und irgendwas funktioniert nicht, weil man an einer Stelle vergessen hat, eine Klammer zu setzen.

Aus der Hüfte geschossen: Versuch es mal ohne "oder" => https://support.office.com/de-de/ar...r-Formel-9d7c966d-6030-4cd6-a052-478d7d844166.
Wenn du mir die Excel-Arbeitsmappe zukommen lässt, kann ich mal versuchen, mich da reinzudenken. Solltest du solche Späße zukünftig aber öfter machen müssen, empfehle ich auch VBA.
 
Danke euch!

Ich glaube mein Dienstplan ist für einen Excel-Kenner gar nicht mal so schwer zu bewerkstelligen, vor allem wenn ich mir das Ungetüm anschaue das Nommo verlinkt hat.
Im Grunde brauche ich nur ein Tabellenblatt mit einem Gesamtplan, auf dem Soll und Haben der jeweiligen Schichten überprüft wird, die Gesamtstunden der einzelnen Mitarbeiter angezeigt werden, das Ganze dann möglichst übersichtlich. Das ist mir so wie es jetzt ist schon halbwegs gut gelungen, das passt so.
Jetzt fehlen "nur" die Tabellenblätter auf denen die Dienstpläne der einzelnen Mitarbeiter dargestellt werden, ausgehend vom Gesamtplan natürlich.

Die nächste Stufe wäre dann dass Ganze so zu erweitern dass man so einen Plan für jeden Monat hat, aber das sollte ich wohl hin bekommen :huh:

Mir fehlen hier glaube ich einfach die Basics da ich mich mit Excel noch nie so recht auseinander gesetzt habe.

In dem Arbeitsblatt arbeite ich derzeit auf Tabellenblatt "Philip" um die Verknüpfungen der einzelnen Mitarbeiter hin zu kriegen. (Auf diesem Tabellenblatt einfach weil bei Philip im Gesamtplan alle Schichtmöglichkeiten vorkommen.

Anhang anzeigen Dienstplan Dezember.zip
 
Was passiert denn hinterher mit den Monatsblättern und den Plänen der einzelnen Mitarbeiter? Frage zielt darauf ab ob man am Ende eine Datei mit allen Monatsplänen hat (zur Archivierung) oder ob nur der aktuelle Plan relevant ist und die alten in die Tonne wandern? Was ist mit einer Jahresplanung? Werden die Monatspläne gedruckt oder bekommt die jeder einfach digital, was für einzelne Datein sprechen könnte? oder bekommt jeder auch die Pläne der anderen was für Monatsdateien spricht mit Blick auf eine mögliche Jahresplanung?

Bevor ich mir da Gedanken zu mache würde ich gern wissen ob ihr einen Jahresplan braucht, also alle Blätter erstellen mit Feiertagen, Urlaub etc. oder ob du das für jeden Monat immer per Hand machen willst?
 
Was passiert denn hinterher mit den Monatsblättern und den Plänen der einzelnen Mitarbeiter? Frage zielt darauf ab ob man am Ende eine Datei mit allen Monatsplänen hat (zur Archivierung) oder ob nur der aktuelle Plan relevant ist und die alten in die Tonne wandern? Was ist mit einer Jahresplanung? Werden die Monatspläne gedruckt oder bekommt die jeder einfach digital, was für einzelne Datein sprechen könnte? oder bekommt jeder auch die Pläne der anderen was für Monatsdateien spricht mit Blick auf eine mögliche Jahresplanung?

Bevor ich mir da Gedanken zu mache würde ich gern wissen ob ihr einen Jahresplan braucht, also alle Blätter erstellen mit Feiertagen, Urlaub etc. oder ob du das für jeden Monat immer per Hand machen willst?

Gute Fragen!

Hintergrund der ganzen Aktion ist, dass die Firma immer miserable Dienstpläne hinbekommt in denen die Wünsche der Kollegen, wenn überhaupt, nur im Ansatz beachtet werden. Einerseits liegt das an einer völlig veralteten, unübersichtlichen Software die benutzt wird, an Lieblosigkeit der Bediener und hauptsächlich wohl daran dass zwei Köpfe, neben ihren anderen Aufgabenbereichen, Dienstpläne für ca. 300 Objekte und 800 Mitarbeiter erstellen müssen.
Daher dachte ich mir, dass ich den Jungs in der Zentrale etwas Arbeit abnehme und den Plan für "mein" Objekt, möglichst übersichtlich, selbst erstelle.

Zurück zum Thema:

- Wenn man die einzelnen Monatspläne als PDF ausgeben könnte, wäre das perfekt! Ich habe aber auch kein Problem das händisch für jeden einzelnen zu machen.
- Jeder Mitarbeiter soll nur seinen eigenen Plan bekommen.
- Der alte Plan kann nach Beginn des neuen Monats in die Tonne wandern, da reicht es wenn er als PDF abgespeichert ist.
- Jahresplan braucht es in dem Sinn nur, dass man möglichst einfach die einzelnen Monate aufrufen kann, Feiertage braucht es aber nicht, die Anforderungen sind immer gleich, egal was für ein Tag (es kommen lediglich ab und an zusätzliche Dienste hinzu)
- Urlaub kann von Hand eingegeben werden, da braucht es keine langfristige Planung, das geht bei uns eher "ich brauch nächsten Monat mal 5 Tage Urlaub"
 
Und wo kommt das Objekt Fantasia oder Pik As her und was hat es damit auf sich?

Ich habe mal den Anfang gemacht, mir ging eben die Zeit aus und es gibt noch ein paar Fragen. Ich soviel kommentiert wie die Zeit es mir zuließ.
Du kannst den mittleren Button mal ausprobieren und über ALT+F11 kannst du dir die Module und den Code anschauen, der etwas umfangreicher ist als eventuell erforderlich (aber nur um später etwas freier sein zu können)

Ich habe ein Steuerungsblatt eingefügt mit allgemeinen Informationen wie: für welches Jahr ist der Plan, für welchen Monat, wieviele Tage hat der Monat, einer Liste mit den für die Mitarbeiter die aktuell auf Zelle B20 fixiert ist (todo das variable zu gestalten, nicht sonderlich schwierig aber eben noch nicht geschehen). Daneben habe ich schonmal die Legende eingefügt die noch für die Pläne der Mitarbeiter benötigt wird (auch noch nicht fertig) Du siehst Dummywerte "asd" die noch anders befüllt werden müssen.

Über den Button "erstelle Mitarbeiterpläne" wird für jeden Mitarbeiter ein Tabellenblatt erzeugt mit seinem Namen beschriftet und anschließend befüllt. Existiert das Blatt schon gibt es die Frage ob es gelöscht werden soll. Ich war so frei und habe einige überflüssige Tabellenblätter entfernt. Das Layout stand bei mir nicht im Fokus, erstmal die Infos in die Blätter zu buxieren.

Die Hauptarbeit wird von dem Modul "main" angestoßen, über das Modul "Mitarbeiter", das das Modul "Plan" anspricht.

Sehe gerade das ich doch viel noch nicht kommentiert habe, lass dich nicht entmutigen das sieht am Anfang komplizierter aus als es ist.

Tipp: öffne die Datei, öffne die VBA IDE mit Alt+f11, öffne die main, klicke mit der Maus in die main und drücke F8, dem gelben Balken folgen (am besten beide fenster sichtbar nebeneinander haben.

Vllt. will es ja auch jemand anderes weitermachen, er ist aufs herzlichste dazu eingeladen.
 

Anhänge

  • Dienstplan Dezember_poe.zip
    54,9 KB · Aufrufe: 21
Zuletzt bearbeitet:
  • ok1.de
  • thinkstore24.de
  • Preiswerte-IT - Gebrauchte Lenovo Notebooks kaufen
Zurück
Oben