Berechnung erforderliche Transportkapazität pro halbe Stunde

[Franz aus Bonn]

Hallo zusammen!

Ich hab mich heute hier angemeldet, aus dem Grund, aus dem es wahrscheinlich die meisten tun: Ich hab eine Frage und noch keine Antwort und hoffe, dass Ihr mir mit der richtigen Idee helfen könnt...

Folgende Fragestellung:
Es geht um die Berechnung der erforderlichen Transportkapazität in einem ziemlich dynamischen Umfeld, nämlich Transporte in einem Krankenhaus. Die Anforderungen kommen zum größten Teil spontan und lassen sich nicht prognostizieren. Die einzelnen Transporte dauern unterschiedlich lang, in der Regel zwischen 15 Minuten und 2,5 Stunden. Je nach Tageszeit kann der gleiche Transport aufgrund verschiedener Einflüsse unterschiedlich lange dauern. Ich möchte deshalb auch mit den echten Transportdauern rechnen und nicht mit Durchschnittswerten.
Also möchte ich aus den vorliegenden Daten der vergangenen Monate den Zeitbedarf pro Intervall berechnen. Da es starke Schwankungen über die Uhrzeit gibt, möchte ich dies in einem 30 Minuten Raster tun.
Ich habe folgende Angaben:
Jeder durchgeführte Transport in einem Datensatz/ einer Zeile mit den erforderlichen Angaben.
Starttermin auf die nächstniedrigeren 30 Minuten abgerundet (also Starttermin um 08:07 wird zu 08:00) 
Transportdauer dieses Transportes auswerten, wann er stattfinden sollte und wie lange er gedauert hat, um so die erforderliche Kapazität pro Intervall (also die 30 Minuten) zu ermitteln und zwar nach Uhrzeit. Dazu habe ich Spalten mit den Zeitfenstern gebildet: 0.00, 0:30, 1:00, 1:30 und so weiter.

In die Zellen kann ich natürlich per Wenn-Funktion eine Abfrage schreiben: also in Zelle mit 8:00: wenn der Starttermin 8:00 ist, dann trage die Transportdauer ein.
Jetzt habe ich aber ein Problem, wenn der Transport länger als 30 Minuten ist. Angenommen der Transport dauert 2:20 Stunden und ich schreibe 2:20 in die Zelle, bekäme ich als Ergebnis, dass ich knapp 5 Mitarbeiter um 8:00 brauche. Das ist aber natürlich Quatsch, denn ich brauche einen Mitarbeiter von 8:00 bis 10:20.
Also müsste ich die Transportdauer entsprechend auf die Zelle mit der Uhrzeit des Starttermins und die Zellen rechts daneben aufteilen, solange bis die Transportdauer verteilt und im letzten Feld der letzte Anteil (im Beispiel 20 Minuten) eingetragen ist.
Das kann man wahrscheinlich über eine verschachtelte Wenn-Funktion lösen, aber das erscheint mir bei langen Transporten kompliziert bzw. aufwendig.
Hat jemand eine Idee für eine schicke Lösung? Gibt es irgendeine Funktion, die so etwas tut? Ach ja, ich arbeite mit Excel 2010.

Vielen Dank!
Grüße aus Bonn
Franz

[WillWissen]

Hi Franz,

schön, dass du den Weg zu uns gefunden hast - herzlich willkommen.

Zu deinem Problem:

Du schreibst sehr viel und beschreibst es auch so, dass du selber es nachvollziehen kannst. Für die (meisten) Helfer jedoch dürfte es schwierig bis nicht möglich sein, daraus eine echte Hilfe geben zu können. Lade doch bitte eine Beispieldatei mit anonymisierten sensiblen Daten, jedoch absolut originalgetreu vom Aufbau her, hier hoch. Wie das geht, kannst du hier http://www.clever-excel-forum.de/thread-326.html nachlesen.

[Franz aus Bonn]

Hallo Günther,

ja, da hast Du wohl recht. Also hier eine reduzierte Ansicht der Tabelle, mit den entsprechenden Feldern.
Im oberen Teil mit einer einfachen wenn-Abfrage und dem unerwünschten Ergebnis, die komplette Transportzeit in einer Zelle zu haben. Unten meine Vorstellung, wie es aussehen soll. Die Daten würde ich dann mit einer Pivottabelle auswerten.
Beim Erstellen des Beispiels fiel mir dann noch auf, dass Transporte ja auch über die Tagesgrenze gehen können. Der Fall findet sich auch in der Tabelle. Dann sollte die Zeit in der gleichen Zeile bei 0:00 und so weiter eingetragen werden.
Die Spalten von 2:30 bis 18:30 habe ich der Übersicht wegen mal ausgeblendet. Der Betrieb geht aber natürlich rund um die Uhr.

Grüße aus Bonn
Franz

[Fennek]

Hallo,

aus den lange vergangenen Tagen der Statistikvorlesung erinnere ich mich an die Poisson-Verteilung, die für diese Art von Fragen gerne benutzt wird. Einfach gesagt, aus dem Fragetext konnte ich nicht eine statistisches Modell erkennen, das, meiner Meinung nach, aber am Anfang der Überlegungen stehen müßte.

Wenn das statistischen Modell gewählt würde, könnte das (auch) in Excel recht gut berechnet werden.

Sofern die Frage auf ein Wahrscheinlichkeitsmodell bassieren würden, hätte ich Spaß daran an Lösungsvorschlägen mitzuarbeiten.

mfg

[Franz aus Bonn]

Guten Morgen,

spannender Ansatz, aber im Moment führt mir das zu weit. Mir geht es hier erst einmal nur um die Beschreibung des Ist-Zustandes. Den Blick in die Zukunft versuch ich dann später.

Grüße aus Bonn
Franz

[lupo1]

BA1: =AZ1+1/48 ca. 10mal nach rechts (23:30 bis 1:45 taucht dann beides am selben Tag auf)
E2: =MAX(;MIN(F$1;$C2+$D2)-MAX(E$1;$C2)) nach rechts und unten
Nullwerte ausblenden

Variante:
E2: =MAX(;MIN(F$1;$C2+$D2)-MAX(E$1;$C2))+BA2, wenn Du die Nach-24:00Uhr-Werte nach vorn holen willst (wie Du schriebst)

oder noch korrekter:
E2: =MAX(;MIN(F$1;$C2+$D2)-MAX(E$1;$C2))+BA1, wenn Du die Nach-24:00Uhr-Werte des Vortages nach vorn holen willst (wie Du schriebst). Dafür musst Du aber eine Leerzeile in Zeile 2 lassen!

[Franz aus Bonn]

Hallo Lupo1,

ganz lieben Dank, es tut genau, was ich mir vorgestellt habe! Ich versteh zwar noch nicht wie es funktioniert, aber da komm ich noch hinter.
An dieser Stelle begnüge ich mich erst mal damit, dass es funktioniert

Grüße aus Bonn
Franz