PDA

Vollständige Version anzeigen : kürzeste Restlaufzeit anhand von Art.Nr.


FelixG
22.08.2017, 09:31
Hallo zusammen,

anbei eine Test-Datei.

Die Formel soll mir für die jeweilige Art.Nr. die kürzeste Restlaufzeit mit zugehöriger Charge wiedergeben.
Die Formel sollte am besten keine lange Berechnung haben, da es sehr viele Datensätze gibt.
Ich denke am besten geht es mit einer Hilfsspalte, die mir die jeweile Zeile wiedergibt, jedoch bin ich bis jetzt noch nicht selbst auf eine Lösung gekommen.


Danke schon mal im Vorraus.

Gruß Felix

lupo1
22.08.2017, 09:57
H2: =INDEX(C:C;J2)
I2: =INDEX(A:A;J2)
K2: =VERGLEICH(E2;B:B;)

Jonas0806
22.08.2017, 10:05
Hallo Felix,

mein Vorschlag:
H2: =VERWEIS(9;1/($A$2:$A$7=I2)/($B$2:$B$7=E2);$C$2:$C$7)
I2: =AGGREGAT(15;6;$A$2:$A$7/($B$2:$B$7=E2);1)3

Die Formeln dann runterziehen.

Übrigens, kompliment an deine Beispieldatei :top:

FelixG
22.08.2017, 10:50
H2: =INDEX(C:C;J2)
I2: =INDEX(A:A;J2)
K2: =VERGLEICH(E2;B:B;)

Hallo Lupo,

deine Lösung funktioniert leider nur wenn die Restlaufzeit absteigend sortiert wäre.

FelixG
22.08.2017, 10:51
Hallo Felix,

mein Vorschlag:
H2: =VERWEIS(9;1/($A$2:$A$7=I2)/($B$2:$B$7=E2);$C$2:$C$7)
I2: =AGGREGAT(15;6;$A$2:$A$7/($B$2:$B$7=E2);1)3

Die Formeln dann runterziehen.

Übrigens, kompliment an deine Beispieldatei :top:


Hallo Jonas,

leider haben wir immernoch Excel 2007 und deshalb kann ich die AGGREGAT-Funktion nicht nutzen.
Aber Ende des Jahres wird auch auf Office 2016 umgestellt.:)

Jonas0806
22.08.2017, 11:43
Hallo Felix,

in xl2007 komme ich hier bis jetzt leider um eine Matrixformel nicht herum. Bin aber noch am probieren...

{=MIN(WENNFEHLER($A$2:$A$7/($B$2:$B$7=E2);""))}

Achtung, Matrixformel! Die {} nicht mit eingeben, dafür die Formel mit Strg+Shift+Enter abschließen.

lupo1
22.08.2017, 11:55
[...] nur, wenn die Restlaufzeit absteigend sortiert wäre.
Dann hast Du meine VERGLEICH-Formel nicht korrekt abgeschrieben, RLZ aufsteigend hier angenommen, wie im Beispiel.

Jonas0806
22.08.2017, 11:55
gelöscht.

WS-53
22.08.2017, 12:37
Hallo Felix,

anbei eine Lösung, die gleich 2 Lösungen beinhaltet. Einmal so wie von dir gewünscht in einer separaten Tabelle und einmal direkt in deine Bestandstabelle integriert.

Die integrierte Lösung hat den Vorteil, dass du bspw. auch jeweils die zweit- und drittkleinste Restlaufzeit abfragen kannst. Ist also wesentlich flexibler.

Bei beiden Lösungen wird mit ZÄHLENWENNS() gezählt, wie viele Artikel es mit einem gleich/kleineren Datum gibt. Die mit "1" sind die mit der kürzesten Restlaufzeit.

Die Tabelle habe ich als "Tabelle formatiert!" (ab Excel 2007 verfügbar) und kann so beliebig filtern (Spalte D).

Für die separate Liste wird in Spalte E die Artikelnummer mit dem Zählergebnis verkettet. Somit kannst du dann mit der Kombination INDEX/VERGLEICH, jeweils die Artikel mit 1 abfragen.

lupo1
22.08.2017, 13:07
Das Ausgangsbeispiel war AUFSTEIGEND, nicht ABSTEIGEND und nicht GAR NICHT sortiert.

Und wenn es das noch nicht wäre, muss man dafür sorgen, dass es das ist, auch bei wenig Datensätzen! Das ist der Teil, den TE zu einer gedeihlichen Lösung von selbst beizusteuern hat.

Merke: Der Mensch hat dem Computer zu folgen, nicht umgekehrt! Der umgekehrte Spruch ist einfach falsch! Wie der Spruch: "Kinder im Straßenverkehr sind unberechenbar!" Das stimmt nicht. "Kinder sind berechenbar." Sie werden nämlich auf jeden Fall irgendetwas Ungeplantes tun.

Als Anforderung stand Performance explizit sogar in der Aufgabe. Dann muss der TE auch sein Stück dazu liefern, nämlich die Sortierung so durchhalten/erzeugen, wie er sie im Beispiel angedeutet hat.

Sonst entstehen völlig unnötige Monsterformeln wie bei WS-53, die bei 10.000 Zeilen bei jeder Aktualisierung 400.000.000 Vergleiche aufgrund der beiden ZÄHLENWENNS durchführen.

WS-53 ist dafür bekannt (http://www.ms-office-forum.net/forum/showthread.php?t=340713) (über 160 Beiträge in dem Thread), dass er den - auch ungerechtfertigten! - Anforderungen des Fragenstellers versucht, bis zum letzten nachzukommen. Das ehrt ihn, aber es bringt am Ende niemanden weiter.

So funktioniert logisches Denken und EDV nicht. Das ist einfach nur Kinderkram und Ferrari im 1. Gang.

WS-53
22.08.2017, 13:23
Hallo Lupo,

natürlich hast du da schon auf der einen Seite Recht. Auch ist es so, dass das Beispiel aufsteigend sortiert ist. Dies kann aber Zufall sein.

Aber, zum einen wird ZÄHLENWENNS tatsächlich nur einmal benötigt und wird du die als Tabelle formatierte Tabelle, immer nur auf den aktuellen Bereich angewendet. Und zum anderen bin ich der Meinung, dass wenn ein Ergebnis falsch wird, wenn zufällig einmal nicht aufsteigend sortiert ist, der normale Anwender dies aber nicht bemerkt, dann ist dies schlimmer, als ein ZÄHLENWENNS, das etwas mehr Zeit benötigt.

Solange ich also ein falsches Ergebnis nicht verhindern kann, nur weil einmal zufällig falsch sortiert wurde, stehe ich zu meiner Lösung, auch wenn diese den Rechner etwas mehr abverlangt.

lupo1
22.08.2017, 13:30
Er wollte eine schnell rechnende Lösung. Punkt.

Und wenn Du behauptest, dass Du mit ZÄHLENWENNS eine mögliche Fehlerquelle beseitigst, warten nach Murphy schon 10 andere. Zum Beispiel ein als Text eingetragener Wert, den niemand erkennt, und der durch eine beteiligte Funktion nicht umgewandelt wird.

Du machst den Computer wieder zu Deinem Erfüllungsgehilfen! Ich habe doch gesagt, dass man das niemals darf.

FelixG
23.08.2017, 07:22
Danke ihr habt mir geholfen, ich habe es jetzt so hinbekommen, dass es funktioniert.

lupo1
23.08.2017, 08:09
Wie denn?

Hast Du eingesehen, dass man sortiert?

Jonas0806
23.08.2017, 11:19
Hallo,

ich habe noch eine matrixfreie Lösung für "MIN bzw. KKLEINSTE nicht 0" in xl2007

=MIN(INDEX(($B$2:$B$7=E2)*$A$2:$A$7+(($B$2:$B$7<>E2)*99^9);))

WS-53
23.08.2017, 12:16
Hallo Jonas,

zuerst einmal, wie kommt man auf so eine Formel und kannst du diese auch erläutern?

Ein Hinderungsgrund für die Formel, kann aber die Artikelnummer sein. Im Beispiel sind alle Artikelnummern numerisch. Aber in sehr vielen Unternehmen kommen alphanumerische Artikelnummern zum Einsatz.

Jonas0806
23.08.2017, 12:41
Hallo WS-53,
zuerst einmal, wie kommt man auf so eine Formel
Ich wollte halt versuchen eine matrixfreie Lösung zu liefern


und kannst du diese auch erläutern?

Nunja, das Problem mit "MIN() bzw. KKLEINSTE() mit Bedingung ohne Nullwerte" ist ja bei xl2007 bekannt. Ab xl2010 lässt sich das bequem mit AGGREGAT() lösen, da hier Fehlerwerte (Division durch FALSCH/0-Werte) übersprungen werden können.

Ich kannte da für xl2007 bislang nur die Matrixformel, welche ich hier schon gepostet habe. #6 (http://www.ms-office-forum.net/forum/showpost.php?p=1825270&postcount=6)

Die Crux ist, dass die 0-Werte mittels der Addition einer hohen Zahl (z.B. 99^9) höher sind als die gesuchten Werte. Damit kann man dann wieder mit MIN() arbeiten.

Reicht das als Erläuterung? Falls nein, kann ich die Formel auch Stück für Stück erklären.


Ein Hinderungsgrund für die Formel, kann aber die Artikelnummer sein. Im Beispiel sind alle Artikelnummern numerisch. Aber in sehr vielen Unternehmen kommen alphanumerische Artikelnummern zum Einsatz.
Weil? Wo sollte da das Problem liegen?

FelixG
23.08.2017, 12:49
Ich habe die Restlaufzeiten mit der Funktion =WENNFEHLER(KKLEINSTE(K:K;ZEILEN($N$2:N2));"") aufsteigend sortiert und vorher habe ich noch eine Hilfsspalte erstellt, dass sich die Restlaufzeiten immer unterscheiden mit der Funktion =WENNFEHLER(F2+ZEILE(A1)/10000000000;"").
Danach dann einfach über INDEX-VERGLEICH die Daten wiedergegeben.

Jonas0806
23.08.2017, 12:55
Hallo Felix,

hast Du denn meinen letzten Vorschlag ausprobiert?

Wenn Du sowieso sortierst und mit Hilfsspalte arbeitest, hast Du Lupo's Vorschlag probiert?

Für was macht man sich eigentlich die Mühe...

WS-53
23.08.2017, 18:36
Hallo Jonas,

MIN, MAX, KKLEINSTE, KGRÖSSTE und RANG.GLEICH funktionieren leider nur für Zahlenwerte. wobei ich nicht verstehe, warum Excel bei diesen Funktionen keine Texte berücksichtigen kann. Sortieren geht ja auch.

Wenn due die Formel:

=MIN(INDEX(($B$2:$B$7=E2)*$A$2:$A$7+(($B$2:$B$7<>E2)*99^9);))

Stück für Stück erläutern kannst, dann ist das eine ganz tolle Sache.

Ich kann dir nur leider nicht versprechen, dass ich es dann verstehe. Ich werde mich aber auf jeden Fall bemühen, deine Erläuterungen zu verstehen.

Vielen Dank!!!

Jonas0806
23.08.2017, 19:42
Hallo WS-53,

MIN, MAX, KKLEINSTE, KGRÖSSTE und RANG.GLEICH funktionieren leider nur für Zahlenwerte

Zunächstmal, hier der Beweis, dass es nichts mit der Materialnummer oder nummerischen Werten zu tun hat

<b>MIN_xl2007</b><br /><br /><table border="1" cellspacing="0" style="font-family:Calibri,Arial; font-size:11pt; background-color:#ffffff; "> <colgroup><col style="font-weight:bold; width:30px; " /><col style="width:80px;" /><col style="width:104px;" /><col style="width:80px;" /><col style="width:80px;" /><col style="width:80px;" /><col style="width:80px;" /></colgroup><tr style="background-color:#cacaca; text-align:center; font-weight:bold; font-size:8pt; "><td >&nbsp;</td><td >A</td><td >B</td><td >C</td><td >D</td><td >E</td><td >F</td></tr><tr style="height:18px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >1</td><td style="font-weight:bold; ">Datum</td><td style="font-weight:bold; ">Artikelnummer</td><td >&nbsp;</td><td style="background-color:#f8cbad; ">Aufgabe: &Auml;ltestes Datum zu dem Artikel in D3</td><td style="background-color:#f8cbad; ">&nbsp;</td><td style="background-color:#f8cbad; ">&nbsp;</td></tr><tr style="height:18px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >2</td><td style="text-align:right; ">15.10.2017</td><td >Artikel_A</td><td >&nbsp;</td><td >&nbsp;</td><td >&nbsp;</td><td >&nbsp;</td></tr><tr style="height:18px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >3</td><td style="text-align:right; ">17.10.2017</td><td >Artikel_B</td><td >&nbsp;</td><td style="font-weight:bold; ">Artikel</td><td style="font-weight:bold; ">Datum</td><td >&nbsp;</td></tr><tr style="height:18px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >4</td><td style="text-align:right; ">16.10.2017</td><td >Artikel_X</td><td >&nbsp;</td><td style="background-color:#ffff00; ">Artikel_X</td><td style="text-align:right; ">14.07.2017</td><td >&lt;- L&ouml;sung ab xl2010</td></tr><tr style="height:18px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >5</td><td style="text-align:right; ">29.08.2017</td><td >Artikel_D</td><td >&nbsp;</td><td >&nbsp;</td><td style="text-align:right; ">14.07.2017</td><td >&lt;- Matrixl&ouml;sung in xl2007</td></tr><tr style="height:18px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >6</td><td style="text-align:right; ">25.09.2017</td><td >Artikel_X</td><td >&nbsp;</td><td >&nbsp;</td><td style="text-align:right; ">14.07.2017</td><td >&lt;- Matrixfreie L&ouml;sung in xl2007</td></tr><tr style="height:18px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >7</td><td style="text-align:right; ">19.10.2017</td><td >Artikel_B</td><td >&nbsp;</td><td >&nbsp;</td><td >&nbsp;</td><td >&nbsp;</td></tr><tr style="height:18px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >8</td><td style="text-align:right; ">23.08.2017</td><td >Artikel_X</td><td >&nbsp;</td><td >&nbsp;</td><td >&nbsp;</td><td >&nbsp;</td></tr><tr style="height:18px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >9</td><td style="text-align:right; ">14.07.2017</td><td >Artikel_X</td><td >&nbsp;</td><td >&nbsp;</td><td >&nbsp;</td><td >&nbsp;</td></tr><tr style="height:18px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >10</td><td style="text-align:right; ">05.08.2017</td><td >Artikel_D</td><td >&nbsp;</td><td >&nbsp;</td><td >&nbsp;</td><td >&nbsp;</td></tr></table><br /><table style="font-family:Arial; font-size:10pt; border-style: groove ;border-color:#00ff00;background-color:#fffcf9; color:#000000; "><tr><td ><b>Formeln der Tabelle</b></td></tr><tr><td ><table border = "1" cellspacing="0" cellpadding="2" style="font-family:Arial; font-size:9pt;"><tr style="background-color:#cacaca; font-size:10pt;"><td >Zelle</td><td >Formel</td></tr><tr><td >E4</td><td >=AGGREGAT(15;6;$A$2:$A$<span style=' color:008000; '>10/($B$2:$B$10=D4)</span>;1)</td></tr><tr><td >E5</td><td >{=MIN(<span style=' color:008000; '><span style=' color:#0000ff; '>WENN(<span style=' color:#ff0000; '>((B2:B10=D4)</span>*A2:A10)</span>;A2:A10;"")</span>)}</td></tr><tr><td >E6</td><td >=MIN(<span style=' color:008000; '><span style=' color:#0000ff; '>INDEX((B2:B10=D4)</span>*A2:<span style=' color:#0000ff; '>A10+<span style=' color:#ff0000; '>((B2:B10&lt;&gt;D4)</span>*9^99)</span>;)</span>)</td></tr></table></td></tr><tr><td ><span style=' font-family:Arial,Arial; font-size:9pt; font-weight:bold;'>Enth&auml;lt Matrixformel:</span><span style=' font-family:Arial,Arial; font-size:9pt;'><br />Umrandende </span><span style=' font-family:Arial,Arial; font-size:9pt; font-weight:bold;'>{ }</span><span style=' font-family:Arial,Arial; font-size:9pt;'> nicht miteingeben,<br />sondern Formel mit STRG+SHIFT+RETURN abschlie&szlig;en!</span></td></tr><tr><td ><a href="http://www.online-excel.de/excel/singsel.php?f=26" style="font-family:Arial; font-size:8pt;font-weight:bold;color:#000080;" target='_blank'>Matrix verstehen</a></td></tr></table> <br /><br /><span style="font-family:Arial; font-size:9pt; font-weight:bold;background-color:#ffffff; color:#000000; ">Excel Tabellen im Web darstellen &gt;&gt; </span><a style ="font-family:Arial; font-size:9pt; color:#fcf507; background-color:#800040; font-weight:bold;" href="http://www.excel-jeanie-html.de" target="_blank"> Excel Jeanie HTML 4.8 </a>


...Stück für Stück erläutern kannst, dann ist das eine ganz tolle Sache.

Dann versuchen wir es mal. (Bezogen auf mein Beispiel von eben)

Lösen wir die Formel mal von innen nach außen auf.

Zunächst werden hier die Datumsangaben mit dem Wahrheitswert multipliziert:
=MIN(INDEX((B2:B10=D4)*A2:A10+((B2:B10<>D4)*9^99);))

Trifft es zu, dass die Artikelnummer in B2:B10 mit der gesuchten in D4 übereinstimmt, wird das Datum als Ergebnis geliefert, andernfalls 0. Aufgelöst sieht das dann also so aus:
=MIN(INDEX({0;0;43024;0;43003;0;42970;42930;0}+((B2:B10<>D4)*9^99);))

Danach multiplizieren wir die Werte, in denen B2:B10 nicht mit D4 übereinstimmt mit 9^99:
=MIN(INDEX((B2:B10=D4)*A2:A10+((B2:B10<>D4)*9^99);))

Aufgelöst sieht das ganze dann so aus:
=MIN(INDEX({0;0;43024;0;43003;0;42970;42930;0}+({2,95126654306528E+94;2,95126654 306528E+94;0;2,95126654306528E+94;0;2,95126654306528E+94;0;0;2,95126654306528E+9 4});))

Danach werden die 2 Arrays einfach addiert. Dieser Schritt aufgelöst:
=MIN(INDEX({2,95126654306528E+94;2,95126654306528E+94;43024;2,95126654306528E+94 ;43003;2,95126654306528E+94;42970;42930;2,95126654306528E+94};))

Das ganze wird nur mit INDEX() umklammert, damit es matrixfähig ist. Setzt man den 2. Parameter von INDEX() auf 0 oder eben einfach ";", dann gibt INDEX() das ganze Array zurück. Sieht dann also ganz unspektakulär so aus:
=MIN({2,95126654306528E+94;2,95126654306528E+94;43024;2,95126654306528E+94;43003 ;2,95126654306528E+94;42970;42930;2,95126654306528E+94})

Aus diesem Array kann man dann das Minimum einfach mit MIN() bestimmen, da ja die ehemaligen 0-Werte jetzt 9^99 sind. Den letzten Schritt auflösen ergibt dann also:
42930

Was dann das Datum 14.07.2017 darstellt.


Ist es jetzt etwas klarer geworden?

lupo1
23.08.2017, 23:01
Ich vermute, WS-53 meinte vor allem, warum man den zweiten Term überhaupt behandelt. Man hebt ihn mit 9^99 in unerreichbare Höhen, damit er auf keinen Fall als Ergebnis zurückgegeben werden kann. Denn ungefragte Nullwerte würden trotzdem durch MIN bevorzugt. Daher schießt man sie in den Orbit, damit man sie auf der Erde gar nicht erst finden kann.

WS-53
24.08.2017, 09:48
Hallo Jonas,

vielen Dank, für die sehr gute und ausführliche Erläuterung!!!

Wenn man weiß, dass man mit INDEX ein virtuelles Array bilden kann, dann ist die Formel eigentlich auch ganz einfach und wenn ich anstatt MIN nach MAX suche, dann wird diese auch noch einmal einfacher: =MAX(INDEX((B2:B10=D4)*A2:A10;)).

Aber die Frage ist: woher weiß man, dass man INDEX() so anwenden kann?

Da Datumswerte, die ja ab 1900 anfangen noch lange 5-stellig sind, hätte natürlich auch *9^9 genügt.

Die nächste Frage ist, wie bekomme ich nun zu dem Ermittelten Datumswert die entsprechende Charge? Eine INDEX/VERGLEICHs-Kombination nur mit dem ermittelten Datum ist ja nicht zielführend, da das Datum bei anderen Artikeln ja auch vorkommen kann. Und wenn ich die komplette Formel in einen Vergleich einschließe.

=VERGLEICH(D4;MIN(INDEX((B2:B10=D4)*A2:A10+((B2:B10<>D4)*9^99);)))

Ist das Ergebnis #NV. Und aus deinem Post #15 geht auch nicht hervor, wie ich an die Charge komme.

Nun sehe ich aber, dass ich hierzu den VERWEIS aus Post #5

=VERWEIS(9;1/($A$2:$A$7=I2)/($B$2:$B$7=E2);$C$2:$C$7)

verwenden muss, der genauso verrückt aufgebaut ist, und ich nicht verstehe, warum du mit diesem den Wert 9 suchst.

Wobei ich auch keine Probleme damit habe, zuzugeben, dass ich den VERWEIS bisher noch nicht verstanden habe.

Jonas0806
24.08.2017, 10:29
Hallo WS-53,
Nun sehe ich aber, dass ich hierzu den VERWEIS aus Post #5

=VERWEIS(9;1/($A$2:$A$7=I2)/($B$2:$B$7=E2);$C$2:$C$7)

verwenden muss, der genauso verrückt aufgebaut ist, und ich nicht verstehe, warum du mit diesem den Wert 9 suchst.

VERWEIS() ist eine der wenigen Formeln, welche mit Fehlerwerten umgehen kann. Die 9 kann jede Zahl sein, die größer ist als 1.

In der Funktion wird also 1/((Datum=gesuchtesDatum)*(Art.Nr=gesuchteArt.Nr)) gerechnet. Das bedeutet, dass wir ein Array aus #DIV/0! und einer 1 bekommen. VERWEIS() gibt nun das x'te Element aus dem "Ergebnisvektor" zurück, wobei x der Position der 1 im Suchvektor entspricht.

lupo1
24.08.2017, 10:53
Mich wundert, dass WS-53 diese Formel noch nie gesehen hat. Sie wird seit 15 Jahren schon hier (http://www.excelformeln.de/formeln.html?welcher=48) beschrieben.

WS-53
24.08.2017, 11:13
Hallo Jonas,

vielen Dank für die zusätzlichen Erläuterungen. So ganz allmählich beginnt sich der Nebel aufzulösen,


Hallo Lupo,

wenn ich mir die Frank Kabel Lösung anschaue, dann komme ich zu dem Ergebnis, dass ich diese wohl schon gesehen habe.

Aber wenn ich eine Lösung sehe, die ich nicht verstehe, dann bleibt diese nicht im Gedächtnis haften. Und ich hatte ja geschrieben, dass ich den VERWEIS() bisher noch nicht verstanden habe. Ich hatte bisher auch noch keine eigene Aufgabenstellung, bei der ich den VERWEIS() einsetzten und dann wohl hätte auch verstehen können.

Jonas0806
24.08.2017, 11:26
hi lupo,
Mich wundert, dass WS-53 diese Formel noch nie gesehen hat. Sie wird seit 15 Jahren schon hier (http://www.excelformeln.de/formeln.html?welcher=48) beschrieben.

Interesse halber. Hast Du auch meine matrixfreie MIN() bzw. KKLEINSTE() Lösung für xl2007 schon irgendwo gesehen? Ich nämlich noch nicht...Kannte da bislang nur die MIN(WENN(...Matrixversion.