Indirekt/Sverweis/Nettoarbeitstage

[Winny]

Hi Jockel,

Alles gut! Ich will gar nichts von dir, es war freundlich und nett gemeint, ob du es glaubst oder nicht! Mein 'wie komme ich darauf...' war die Antwort auf deinen Beitrag, dass du selbst in der Lage seist, die Zusammenhänge zu erkennen. Irgendwie dachte ich aber zu früher Stunde und auch fälschlicherweise(!) ich könnte/müsste Dir auf die Sprünge helfen. Deine Einlassung zur 'bösen' Indirekt() Formel klang für mich ehrlich gesagt so, als sei es doch ein Leichtes diesen Teil durch eine 'gute' Lösung zu ersetzen und ich hatte mich gewundert warum du es dann nicht getan hast. Ich bin zwar nicht ganz unbeleckt in Excel, möchte mich aber nicht mit Dir messen müssen, da kann ich nur verlieren, auf jeden Fall was Formeln anbelangt. Der Fortgeschrittene bist da sicherlich Du. Kommunikation ist halt sehr schwierig, und offensichtlich helfen auch Worte wie SeniorMoment(=eigene Schuldzuweisung an mich selbst) und ein Smiley nicht wirklich. Das tut mir leid und ich werde 2017 nutzen daran zu arbeiten. Grundsätzlich nutze ich Foren nicht, um andere Leute anzugreifen oder 'niederzumachen', sollte das mal anders rüberkommen dann täte mir das ehrlich leid. 

Was die Herangehensweise von Formellaien anbelangt so habe ich dazu ja bereits etwas geschrieben, oft sind die Leute doch froh, überhaupt ein (scheinbar) funktionierendes Gebilde erstellt zu haben, und auch noch stolz darauf wenn kein #WERT erscheint! Anderenfalls müssten sie doch auch nicht hier und anderswo anfragen. Mit meinen Beiträgen hierzu möchte ich euch Formel-Profis auch daran erinnern dass es nicht für alle so einfach ist, oft wird eine Frage gestellt und als Antwort kommt harsche Kritik die der Fragesteller gar nicht versteht(verstehen kann!). Boskos Antwort werde ich natürlich auch noch beantworten, aber erst nächstes Jahr, und hoffentlich mit einer eigenen Lösung


In diesem Sinne einen guten Rutsch und ein erfolgreiches Jahr 2017

[BoskoBiati]

Hey Wenny,


Hättest mir auch gleich antworten können, jetzt muss ich noch bis nächstes Jahr warten. :22:

[Winny]

Hättest mir auch gleich antworten können, jetzt muss ich noch bis nächstes Jahr warten. :22:

na, ich hoffe doch das hast du ganz locker hinbekommen :15:

eigentlich habe ich ja schon fast alles gesagt, ein Danke noch für deine Lösung mit der Indirekt() Formel, welches mich jetzt persönlich sehr interessiert hatte. Im Prinzip stimme auch ich dir zu, denke aber dass viele Fragesteller einfach überfordert sind

Ich habe auch versucht die Formeln und ihren Sinn zu verstehen und habe meine Vermutung zum ersten Formelteil ja hier schon geschrieben. Da sind aber so viele Ungereimtheiten(KW's fangen Sonntag an?, DATUM(Jahr;;)=30.11.??, etc), dass der TE die an ihn gestellten Fragen erstmal beantworten sollte bevor ich weiter spekuliere

[BoskoBiati]

Hallo Winny,

KW's fangen Sonntag an?

wo?

[Winny]

Hallo Winny,

wo?

Fehlalarm, sorry, geklärt   Aber ich glaube ich habs.... teilweise....

[Winny]

da der 12.08.2016 der Anlieferungstag ist, darf dieser Tag nicht mehr gezählt werden. 

in dieser Spalte sind immer Freitage, und die sollen nie mitgezählt werden? Einen 12.08. hast Du aber nicht in WE stehen, du meinst doch WT? Man könnte in den Nettoarbeitstagen() jetzt grundsätzlich einen Tag abziehen, dann zieht man aber im Fall, dass einer der Betriebsfeiertage auf einen Freitag fällt, doppelt ab. Das ist in Zeile9(12.08.) der Fall. Eine geschickte Lösung ist es, den Freitag(Spalte G) gänzlich auf den nächsten Montag zu verlegen, so wird er nicht mitgezählt und auch kein evtl. Feiertag abgezogen. In Spalte O kann deshalb (verkürzt, aber für die Testdaten immer gültig!)

=NETTOARBEITSTAGE(G2+3;K2;INDIREKT(SVERWEIS($B2;Kalender!$P$2:$Q$18;2;0)))

Daher muss im zweiten Teil der Formel -1 gerechnet werden. Ausgehend zu der Berechnung wird im Feld WT/Datum der Freitag als letzter Anlieferungstag in einer Woche als spätester Liefertermin angenommen (dieses Datum wird per Formel ermittelt).

Dieser zweite Teil wird mit deinen Testdaten nie angesprungen und wenn liefert dieser Teil nur Unsinn. Unter welchen Bedingungen soll dieser Teil abgearbeitet werden?

In allen anderen Konstellationen mit Daten in den Feldern WT/Datum und WE/Kunde funktioniert der erste Teil der Formel. Nur wenn das Datum im Feld WT/Datum nicht mit dem Datum in der Tabelle Kalender identisch ist, wird nicht der richtige Wert ausgegeben.

das scheint nur zu funktionieren weil in dem Zeitraum ein Feiertag ist

Den zweiten Fall habe ich mir nicht angeschaut, auch ist der Index() Teil von Bosko nicht berücksichtigt, damit es 'einfach' bleibt. Nur der rote Teil wurde geändert

=WENN(UND(B2>0;G2>0;K2>0);NETTOARBEITSTAGE(G2+3;K2;INDIREKT(SVERWEIS($B2;Kalender!$P$2:$Q$18;2;0)));WENN(NICHT(IDENTISCH(G2;INDIREKT(SVERWEIS($B2;Kalender!$P$2:$Q$18;2;0))));NETTOARBEITSTAGE(G2;K2;INDIREKT(SVERWEIS($B2;Kalender!$P$2:$Q$18;2;0))-1)))

[BoskoBiati]

Hallo winny,

sehr abenteuerlich! Was ist, wenn mal der Montag Anlieferungstag ist?
Mit meiner Formel ziehe ich schon die Feiertage aus der Liste ab. Wenn der Anlieferungstag generell nicht berücksichtigt werden soll, dann reicht es doch, am Schluß 1 abzuziehen, dann ist der erste Tag weg.
Freie Tage sind durchaus ein Problem, wenn sie am Anfang des Zeitstrangs stehen. Es gibt zwei Möglichkeiten, wovon hier wohl die Spalte D eher in Frage kommt:

Arbeitsblatt mit dem Namen 'Tabelle1'
	A	B	C	D	E	F	G	H	I
1			Starttag inkl.	Starttag exkl.			Winny		FT
2	05.01.2017	13.01.2017	6	5	5	5	5		01.01.2017
3	06.01.2017	13.01.2017	5	5	5	4	5		06.01.2017
4	07.01.2017	13.01.2017	5	5	4	4	4		14.04.2017
5	08.01.2017	13.01.2017	5	5	4	4	3
6	09.01.2017	13.01.2017	5	4	4	4	2

Zelle	Formel
C2	=NETTOARBEITSTAGE(A2;B2;FT)
D2	=NETTOARBEITSTAGE(A2+1;B2;FT)
E2	=NETTOARBEITSTAGE(A2;B2;FT)-(ZÄHLENWENN(FT;A2)=0)
F2	=NETTOARBEITSTAGE(A2;B2;FT)-1
G2	=NETTOARBEITSTAGE(A2+3;B2;FT)

Diese Tabelle wurde mit Tab2Html (v2.4.1) erstellt. ©Gerd alias Bamberg

Sollte der Freitag ausgeschlossen werden, dann gäbe es noch diese Möglichkeiten:

Arbeitsblatt mit dem Namen 'Tabelle1'
	A	B	C	D
8	05.01.2017	13.01.2017	5	6
9	06.01.2017	13.01.2017	4	5
10	07.01.2017	13.01.2017	4	5
11	08.01.2017	13.01.2017	4	5
12	09.01.2017	13.01.2017	4	5

Zelle	Formel
C8	=NETTOARBEITSTAGE.INTL(A2;B2;"0000111";FT)
D8	=NETTOARBEITSTAGE(A8;B8;FT)-(REST(A8;7)=6)*(ZÄHLENWENN(FT;A8)=0)

Diese Tabelle wurde mit Tab2Html (v2.4.1) erstellt. ©Gerd alias Bamberg

in B generell, in C nur als 1.Tag


Aber vielleicht äußert sich der TE ja mal selbst!

[Winny]

Hi Bosko

jetzt hast Du die Datenlage aber umgedreht! Im Originalbeispiel ist der WA(mein Verständnis!) immer ein Freitag, welcher(lt. meiner Interpretation des TE) ausgeschlossen werden soll. In deinem Beispiel ist der WE immer ein Freitag. So können wir nicht 'arbeiten', sprich vergleichen :-)

Das Ganze macht eh (ohne nähere Erläuterungen des TE) recht wenig Sinn bzw. ist sehr kompliziert und ohne näheres Wissen über die Bedingungen nicht lösbar

Nun ja, einen hätt ich noch, wieder spekuliert: Wenn WA gar nicht WA ist sondern der letzte (Werk)Tag einer zugesicherten Lieferwoche(WT=WarenTermin?), dann machte auch der Spaltentext AT überzogen Sinn. In diesem Fall wäre nach meiner Logik aber die Berechnung dieses letzten Tages in die Spalte G zu verlegen und wäre dann Donnerstag, nicht Freitag(falls da Feiertag***), oder gar Mittwoch im Fall einer Weihnachtswoche. Unser ERP würde hier allerdings Montag danach errechnen, wobei es aber tatsächlich nicht den letzten Werktag einer Lieferwoche berechnet sondern den ganz genauen Termin, basierend auf sehr vielen Planungsdaten(Handling beim WA, Werkskalender, Kundenkalender, Speditionskalender(meist generell auf das Lieferland bezogen), generelle Leadtime Versandort zu Empfängerort mit gewünschtem oder geleisteten Service). Oder in kurz, wenn bei uns WT ein Freitag der 05.08. ist, dann ist da auch kein Feiertag :-) Insofern müsste man auch die Feiertage in der Nettoarbeitstage() gar nicht erst angeben und die Anzahl der Tage -1 wäre immer korrekt. Einen tatsächlichen WE(Spalte K) in einen Zeitraum mit Feiertagen zu legen(Zeilen 2,4,6,8 etc) macht natürlich auch keinen Sinn, da kann nur Unsinn bei rauskommen. Für diejenigen welche bis hierher folgen konnten und sich fragen was passiert wenn ein fixer WT vorgegeben ist: Dann wird entsprechend der o.g. Daten ein entsprechender Produktionstermin errechnet. 

In Kurz: WT mit Werkskalender anpassen(in den Beispielen immer Spalte L) , gleiches mit WE(Spalte K), und Nettoarbeitstage(WT;WE;)-1

Nun, der TE ist gefragt, aber ich tendiere jetzt dazu dass WT nicht WA ist sondern wie eben beschrieben. Und bessere Daten, bitte!!!


***Feiertag steht für ein Datum in Tabelle Kallender