PDA

Vollständige Version anzeigen : Auswertung Endtermin pünktlich oder unpünktlich


Wolf38
04.10.2016, 17:41
Hallo miteinander,

ich habe folgende Aufgabenstellung:

Tabelle auswerten nach Pünktlichkeit und Unpünktlichkeit vom Enddatum.

Folgende Bedingungen sollten erfüllt werden.

Die Auswertung sollte nur in einem gewissen Zeitraum erfolgen, hab das im Beispiel noch näher erklärt.

Dann ist es möglich das es APL gibt, die zwei oder dreimal vorkommen, aber nur als ein Position gezählt werden.

Hoffe mal meine Angaben und die Beispielmappe sich für euch für die Umsetzung hilfreich genug.

Ansonsten bitte einfach die Unklarheit mir mitteilen.

Vielen Dank für die Mithilfe.

Gruss
Wolf

aloys78
04.10.2016, 19:02
Hallo Wolf,
Dann ist es möglich das es APL gibt, die zwei oder dreimal vorkommen, aber nur als ein Position gezählt werden.
Verstehe ich das richtig, dass bei APL auch der Zeitraum mit zu berücksichtigen ist ?
für die Auswertung soll nur der Bereich vom 26.09.2016 - 30.09.2016 alles was früher oder danach sein könnte soll nicht berücksichtigt werden.
Wie ist die Position zu behandeln, wenn der Zeitraum der Zeile sich mit dem vorgegebenen Zeitaum überschneidet ?

Kannst Du Deine Beispieldatei ergänzen, dass auch Werte in den Spalten pünktlich/unpünklich enthalten sind und Zeilen mit Termin-Überschneidungen vorkommen. Und dann aufzeigen, zB durch farbliche Markierung, was zu zählen ist.

Gruß
Aloys

Wolf38
04.10.2016, 20:16
Hallo Aloys,

nun ich habe jetzt versucht, die Tabelle einwenig nachzubessern.

Also meine Liste enthält viele verschiedene Daten und APL sind Arbeitsplätze und von denen gibt es auch viele verschiedene und öfter kommen sie mehr als einmal vor, aber für die Auswertung dürfen sie nur einmal herangezogen werden.

Und der Zeitraum muss ich auch flexibel eingeben können, weil ich immer so eine Woche abfragen muss. Die Daten kommen aus dem SAP und werden dann jede Woche neu in die Excel Tabellen Vorlage geladen.

Ziel von der ganzen Sache wäre, wie gut ist die Termintreue von Aufträgen zum Start zum Enddatum.

Hoffe mal Aloys, dass hilft dir einwenig weiter.

Danke mal.....

Gruss
Wolf

aloys78
04.10.2016, 23:33
Hallo Wolf,

mir fällt an Deiner Beispieldatei auf:
- es sind x und u zu zählen
- es gibt keine pünktliche 100000

Eine mögliche Lösung
<table border='1' cellspacing='0' cellpadding='2' valign='middle' colspan='5' style='font-family:Calibri; color:#000000; background-color:#FFFFFF; font-size:11px; font-weight:normal; font-style:normal; '><colgroup><col width='28pt'><col width='60pt'><col width='60pt'><col width='60pt'><col width='60pt'></colgroup><tr style='background-color:#FAFAFA'><td align='middle' colspan='5'>Arbeitsblatt mit dem Namen 'Auswertung'</td></tr><tr style='background-color:#cacaca'><td>*</td><td align='middle'>A</td><td align='middle'>B</td><td align='middle'>C</td><td align='middle'>D</td></tr><tr><td style='background-color:#cacaca' align='middle'>1</td><td align='right' >*</td><td align='right' >*</td><td align='right' >*</td><td align='right' >*</td></tr><tr><td style='background-color:#cacaca' align='middle'>2</td><td align='right' >*</td><td align='right' >*</td><td align='left' >pünktlich</td><td align='left' >unpünktlich</td></tr><tr><td style='background-color:#cacaca' align='middle'>3</td><td align='left' >Arbeitsplatz</td><td align='right' >100000</td><td align='middle' style='background-color:#66FF33; ' >0</td><td align='middle' style='background-color:#FF0000; ' >4</td></tr><tr><td style='background-color:#cacaca' align='middle'>4</td><td align='right' >*</td><td align='right' >200000</td><td align='middle' style='background-color:#92D050; ' >4</td><td align='middle' style='background-color:#FF0000; ' >1</td></tr></table><br><table border='3' cellspacing='0' cellpadding='2' valign='middle' colspan='2' style='table-layout:fixed; color:#000000; background-color:#FFFFFF; font-family:Calibri; font-size:10px;'><colgroup><col width='40pt'><col></colgroup><tr style='background-color:#eeaaaa'><td>Zelle</td><td>Formel</td></tr><tr><td>C3</td><td><Span style='color:#222222'>=ZÄHLENWENNS</Span><Span style='color:#0000DD'>(Daten!$C$5:$C$34;B3;Daten!$E$5:$E$34;">="&Daten!$H$1;Daten!$E$5:$E$34;"<Span style='color:#0000DD'><</Span>="&Daten!$I$1;Daten!$H$5:$H$34;"x")</Span><Span style='color:#222222'>+ZÄHLENWENNS</Span><Span style='color:#0000DD'>(Daten!$C$5:$C$34;B3;Daten!$E$5:$E$34;">="&Daten!$H$1;Daten!$E$5:$E$34;"<Span style='color:#0000DD'><</Span>="&Daten!$I$1;Daten!$H$5:$H$34;"u")</Span><Span style='color:#222222'></Span></td></tr><tr><td>D3</td><td><Span style='color:#222222'>=ZÄHLENWENNS</Span><Span style='color:#0000DD'>(Daten!$C$5:$C$34;B3;Daten!$E$5:$E$34;">="&Daten!$H$1;Daten!$E$5:$E$34;"<Span style='color:#0000DD'><</Span>="&Daten!$I$1;Daten!$I$5:$I$34;"x")</Span><Span style='color:#222222'>+ZÄHLENWENNS</Span><Span style='color:#0000DD'>(Daten!$C$5:$C$34;B3;Daten!$E$5:$E$34;">="&Daten!$H$1;Daten!$E$5:$E$34;"<Span style='color:#0000DD'><</Span>="&Daten!$I$1;Daten!$I$5:$I$34;"u")</Span><Span style='color:#222222'></Span></td></tr><tr><td>C4</td><td><Span style='color:#222222'>=ZÄHLENWENNS</Span><Span style='color:#0000DD'>(Daten!$C$5:$C$34;B4;Daten!$E$5:$E$34;">="&Daten!$H$1;Daten!$E$5:$E$34;"<Span style='color:#0000DD'><</Span>="&Daten!$I$1;Daten!$H$5:$H$34;"x")</Span><Span style='color:#222222'>+ZÄHLENWENNS</Span><Span style='color:#0000DD'>(Daten!$C$5:$C$34;B4;Daten!$E$5:$E$34;">="&Daten!$H$1;Daten!$E$5:$E$34;"<Span style='color:#0000DD'><</Span>="&Daten!$I$1;Daten!$H$5:$H$34;"u")</Span><Span style='color:#222222'></Span></td></tr><tr><td>D4</td><td><Span style='color:#222222'>=ZÄHLENWENNS</Span><Span style='color:#0000DD'>(Daten!$C$5:$C$34;B4;Daten!$E$5:$E$34;">="&Daten!$H$1;Daten!$E$5:$E$34;"<Span style='color:#0000DD'><</Span>="&Daten!$I$1;Daten!$I$5:$I$34;"x")</Span><Span style='color:#222222'>+ZÄHLENWENNS</Span><Span style='color:#0000DD'>(Daten!$C$5:$C$34;B4;Daten!$E$5:$E$34;">="&Daten!$H$1;Daten!$E$5:$E$34;"<Span style='color:#0000DD'><</Span>="&Daten!$I$1;Daten!$I$5:$I$34;"u")</Span><Span style='color:#222222'></Span></td></tr></table><table style='font-family:Arial; font-size:7pt'><tr><td style='color:#333333'>Diese Tabelle wurde mit Tab2Html (v2.4.1) erstellt. ©Gerd alias Bamberg</td></tr></table>
Gruß
Aloys

Wolf38
05.10.2016, 06:00
Guten Morgen Aloys,

ganz so einfach ist dass dann auch wieder nicht, weil die u und x hab ich als Muster in die Tabelle gegeben. Das du siehst, welcher Zeitraum dann ausgewertet werden soll.

Daher müssen auch keine x oder u in der Tabelle sein, es muss nur die Auswahl getroffen werden, ob pünktlich oder nicht.

Vielleicht kannst du dir mein Beispiel nochmal genauer anschauen.

- Zeitraum der Auswahl müsste ich ja auch noch irgendwo eingeben können

- Auswahl ob pünktlich oder nicht

- Dann soll nur immer ein APL pro (FA) z.B. 250010 gezählt werden

Und das ist für mich ja das schwierige :rolleyes:

Gruss
Wolf

Wolf38
05.10.2016, 06:26
Hallo Aloys,

bin jetzt bei der Arbeit und hab dir mal meine Originaldatei angehängt.

Denke mal hier müsste man besser verstehen was ich möchte.

- Was hier nicht funktioniert ist das unpünktlich

- Und vielleicht kann man die zwischen abfragen verringern

Danke und Gruss
Wolf

aloys78
05.10.2016, 09:15
Hallo Wolf,
ganz so einfach ist dass dann auch wieder nicht, weil die u und x hab ich als Muster in die Tabelle gegeben.
Bisher bin ich davon ausgegangen, dass eine Beispiel-Tabelle die reale Struktur abbildet und ggf nur fiktive Werte enthält. Du gehst einen Schritt weiter und lieferst mit V01 eine fiktive Tabelle. Da sind Missverständnisse vorprogrammiert.
Daher müssen auch keine x oder u in der Tabelle sein, es muss nur die Auswahl getroffen werden, ob pünktlich oder nicht.
Warum gibst Du bei „unpünktlich“ eine Zahl mit Dezimalstellen ein ?
Zeitraum der Auswahl müsste ich ja auch noch irgendwo eingeben können
Hast Du doch in K1 oder was meinst Du damit ?
Was hier nicht funktioniert ist das unpünktlich
Ich nehme an, Du meinst damit die zuletzt bereit gestellte Tabelle V02.
Du nimmst für beide Stati die gleiche Formel, da muss dann auch das gleiche Ergebnis rauskommen.
Warum wird bei der Abfrage der K1-Wert um 5 erhöht ?

Des Weiteren am Beispiel der Zeilen 458 und 459
Was sind die Auswahlkriterien, die Position nur einmal zu zählen ? Der identische Ist-Zeitraum ?

Gruß
Aloys

Wolf38
05.10.2016, 10:19
Hallo Aloys,

sorry, ich habe gestern schnell zuhause die Struktur so einfach wie möglich nachzubauen, aber ich habe dann gesehen, dass es so nicht zielführend ist.

Daher habe ich heute Morgen, dann die Struktur genommen, die bei mir in Bearbeitung ist.

Antworten:

Das mit der Dezimalzahl ist nur ein Formatierungsfehler!

Ja ich habe den Zeitraum in K1 eingegeben!

Also die Idee war, das Datum in Zelle (K1) 26.09.2016 mit der
Formel =WENN(H442<($K$1+5);0;1)
Den Zeitraum einschränkt 26.09.2016 - 30.09.2016 (5 Tage)
für die Auswahl die dann für pünktlich und unpünktlich ausgewertet werden.

Wenn der Auftrag und die APL gleich sind wird die Position nur einmal gezählt! Das hab ich versucht in der Spalte M mit der Formel zu lösen.

Nun das wär ja auch eines meiner Probleme das ich nicht wusste wie ich die Formel für die unpünktlich nicht hinbekommen habe.

Entschuldige die Umstände mit den verschiedenen Strukturen, ich werde in Zukunft keine Nachbauten die nicht mit der eigentlichen Struktur nicht zusammenpassen nicht mehr Posten.

Danke für deine Geduld.

Gruss
Wolf

aloys78
05.10.2016, 15:57
Hallo Wolf,

2 Fragen habe ich noch:

Ist Auftrag = Vorgang ?

Außerdem sprachst Du im ersten Beitrag davon, dass nur Zeilen mit Ende-Datum im Betrachtungs-Bereich 26.9. - 30.9. zu berücksichtigen seien (zumindest habe ich es so verstanden).

Diese Zeilen sind dann alle gültig und können so ermittelt werden
=WENN(UND(H2>=$K$1;H2<($K$1+5));1;0)
Unpünktliche kann es danach aber gar nicht geben, da Du ausschließlich mit Istdaten arbeitest, dazu aber m.E. die Soll-Termine mit herangezogen werden müßten.

Gruß
Aloys

Wolf38
05.10.2016, 17:10
Hallo Aloys,

Ja da sind mir in der schnell hab ich einige Dinge übersehen.

Hab die Tabelle jetzt noch angepasst mit "Soll Ende" so können auch die unpünktlichen ausgewertet werden.

2 Fragen habe ich noch:

Ist Auftrag = Vorgang ?

Außerdem sprachst Du im ersten Beitrag davon, dass nur Zeilen mit Ende-Datum im Betrachtungs-Bereich 26.9. - 30.9. zu berücksichtigen seien (zumindest habe ich es so verstanden).


Also Auftrag ist nicht = Vorgang, weil in einem Auftrag gibt es mehrere Vorgänge.

Jetzt ist der Betrachtungsbereich "Soll Ende" diese sollen dann ausgewertet werden.

Hoffe mal jetzt hab ich alle Unklarheiten aufgeklärt.

Und du kannst damit eine tolle Lösung finden.

Danke und Gruss
Wolf

aloys78
05.10.2016, 17:56
Hallo Wolf,

Aktuelle Aufgabenstellung:

- Betrachtungszeitraum ist L1 bis (L1+5)
- untersucht werden alle Zeilen, deren Soll-Ende in diesem Betrachtungszeitrahmen liegt.
- Ist das Ist-Ende > Soll-Ende, dann unpünktlich, andernfalls pünktlich.
- im Blatt Auswertung ist je APL der Zahl der Pünktlichen und der Unpünktlichen auszuweisen; Doppel werden nur einfach gezählt.
Doppel=APL, Soll-Ende, Ist_Start und Ist-Ende mehrerer Zeilen sind gleich
(Die Doppel liegen immer zusammen).

Habe ich das richtig verstanden ?

Vorab mal die Formel für die Ermittlung pünktlich/unpünktlich.

<table border='1' cellspacing='0' cellpadding='2' valign='middle' colspan='3' style='font-family:Calibri; color:#000000; background-color:#FFFFFF; font-size:11px; font-weight:normal; font-style:normal; '><colgroup><col width='28pt'><col width='60pt'><col width='60pt'></colgroup><tr style='background-color:#FAFAFA'><td align='middle' colspan='3'>Arbeitsblatt mit dem Namen 'Datentabelle'</td></tr><tr style='background-color:#cacaca'><td>*</td><td align='middle'>Q</td><td align='middle'>R</td></tr><tr><td style='background-color:#cacaca' align='middle'>1</td><td align='middle' >Pünktlich</td><td align='left' >Unpünktlich</td></tr><tr><td style='background-color:#cacaca' align='middle'>2</td><td align='middle' >0</td><td align='middle' style='background-color:#FF0000; ' >1</td></tr><tr><td style='background-color:#cacaca' align='middle'>3</td><td align='middle' >0</td><td align='middle' style='background-color:#FF0000; ' >1</td></tr></table><br><table border='3' cellspacing='0' cellpadding='2' valign='middle' colspan='2' style='table-layout:auto; color:#000000; background-color:#FFFFFF; font-family:Calibri; font-size:10px;'><colgroup><col width='40pt'><col></colgroup><tr style='background-color:#eeaaaa'><td>Zelle</td><td>Formel</td></tr><tr><td>Q2</td><td><Span style='color:#222222'>=WENN</Span><Span style='color:#0000DD'>(UND</Span><Span style='color:#222222'>($I2>=$L$1;$I2<Span style='color:#222222'><</Span></Span><Span style='color:#0000DD'>($L$1+5)</Span><Span style='color:#222222'>)</Span><Span style='color:#0000DD'>;WENN</Span><Span style='color:#222222'>($H2<Span style='color:#222222'><</Span>=$I2;1;0)</Span><Span style='color:#0000DD'>;0)</Span><Span style='color:#222222'></Span></td></tr><tr><td>R2</td><td><Span style='color:#222222'>=WENN</Span><Span style='color:#0000DD'>(UND</Span><Span style='color:#222222'>($I2>=$L$1;$I2<Span style='color:#222222'><</Span></Span><Span style='color:#0000DD'>($L$1+5)</Span><Span style='color:#222222'>)</Span><Span style='color:#0000DD'>;WENN</Span><Span style='color:#222222'>($Q2=1;0;1)</Span><Span style='color:#0000DD'>;0)</Span><Span style='color:#222222'></Span></td></tr><tr><td>Q3</td><td><Span style='color:#222222'>=WENN</Span><Span style='color:#0000DD'>(UND</Span><Span style='color:#222222'>($I3>=$L$1;$I3<Span style='color:#222222'><</Span></Span><Span style='color:#0000DD'>($L$1+5)</Span><Span style='color:#222222'>)</Span><Span style='color:#0000DD'>;WENN</Span><Span style='color:#222222'>($H3<Span style='color:#222222'><</Span>=$I3;1;0)</Span><Span style='color:#0000DD'>;0)</Span><Span style='color:#222222'></Span></td></tr><tr><td>R3</td><td><Span style='color:#222222'>=WENN</Span><Span style='color:#0000DD'>(UND</Span><Span style='color:#222222'>($I3>=$L$1;$I3<Span style='color:#222222'><</Span></Span><Span style='color:#0000DD'>($L$1+5)</Span><Span style='color:#222222'>)</Span><Span style='color:#0000DD'>;WENN</Span><Span style='color:#222222'>($Q3=1;0;1)</Span><Span style='color:#0000DD'>;0)</Span><Span style='color:#222222'></Span></td></tr></table><br><table border='3' cellspacing='0' cellpadding='2' valign='middle' colspan='3' style='color:#000000; background-color:#FFFFFF; font-family:Calibri; font-size:10px;'><tr style='background-color:#aaaaee'><td>Zelle</td><td>bedingte Formatierung...</td><td>Format</td></tr><tr><td>Q2</td><td><Span style='color:#222222'>1: =Q2>0</Span></td><td style='color:#keine; background-color:#66FF33; '>abc</td></tr><tr><td>R2</td><td><Span style='color:#222222'>1: Zellwert ist größer gleich 1</Span></td><td style='color:#keine; background-color:#FF0000; '>abc</td></tr><tr><td>Q3</td><td><Span style='color:#222222'>1: =Q3>0</Span></td><td style='color:#keine; background-color:#66FF33; '>abc</td></tr><tr><td>R3</td><td><Span style='color:#222222'>1: Zellwert ist größer gleich 1</Span></td><td style='color:#keine; background-color:#FF0000; '>abc</td></tr></table><table style='font-family:Arial; font-size:7pt'><tr><td style='color:#333333'>Diese Tabelle wurde mit Tab2Html (v2.4.1) erstellt. ©Gerd alias Bamberg</td></tr></table>

Gruß
Aloys

Wolf38
05.10.2016, 20:09
Hallo Aloys,

danke mal für den ersten Teil mit den Formeln, hab es mal ausprobiert und
und ausser das zwei Werte vorkommen, schaut das mal schon gut aus.

Bei der Formatierung hab ich es bei den unpünktlichen geschafft, aber bei den pünktlichen klappt da irgendwas nicht. :rolleyes:

- Betrachtungszeitraum ist L1 bis (L1+5)
- untersucht werden alle Zeilen, deren Soll-Ende in diesem Betrachtungszeitrahmen liegt.
- Ist das Ist-Ende > Soll-Ende, dann unpünktlich, andernfalls pünktlich.
- im Blatt Auswertung ist je APL der Zahl der Pünktlichen und der Unpünktlichen auszuweisen; Doppel werden nur einfach gezählt.
Doppel=APL, Soll-Ende, Ist_Start und Ist-Ende mehrerer Zeilen sind gleich
(Die Doppel liegen immer zusammen).

Habe ich das richtig verstanden ?

Ja du hast alles richtig verstanden, aber es kann schon vorkommen, dass nicht alle Doppelten immer zusammen liegen.

Hab mal die Liste mit den Formeln angehängt.

Gruss
Wolf

aloys78
05.10.2016, 21:21
Hallo Wolf,

anbei eine Lösung mit einer Hilfsspalte (die man ausblenden könnte).

Hierbei wurden alle zuletzt beschriebenen Aspekte berücksichtigt. Da doch eine Reihe von auseinander liegenden Zeilen identische Begriffe haben (APL, Ist-Start, Ist-Ende, Soll-Ende), ist das Ergebnsi erhebllich kleiner als von Dir vorgegeben.

Gruß
Aloys

Wolf38
06.10.2016, 18:36
Hallo Aloys,

hab das ganze heute mal mit realen Daten getestet. Und ich kann dir nur sagen, funktioniert prima.

Daher möchte ich mich bei dir bedanken für die schnelle und erfolgreiche Hilfe.

Gruss
Wolf