Office: (Office 2013) Index, Aggregat bei mehreren möglichen Ergebnissen mit zusätzlichen Hindernissen

Einen wunderschönen guten Abend zusammen,

ich sitze nun seit einiger Zeit an dem folgenden Problem und bin nun leider an einer Stelle angelangt, an der ich selber nicht mehr weiter komme und auch nicht mehr sicher bin, ob dieses Problem überhaupt noch mit Excel zu lösen ist.

Ich habe eine vorgegebene Liste mit 4 Kriterien und einer dazugehörigen Bestellnummer: Kundennummer, Artikelnummer, Behälter (in dem der Artikel transportiert wird) und das geplante Lieferdatum.

Dann gibt es eine zweite Liste mit den 4 Kriterien: Kundennummer, Artikelnummer, Behälter (in dem der Artikel transportiert wird) und das tatsächliche Lieferdatum.

Ich versuche nun also die tatsächlichen Lieferungen mit den Bestellungen zu matchen und mir die passende Bestellnummer ausgeben zu lassen.
Oft passt jedoch das tatsächliche Lieferdatum nicht mit dem geplanten Lieferdatum überein.

Dazu habe ich mir folgendes überlegt:
1. Mit der Funktion AGGREGAT suche ich die KKLEINSTE Abweichung zwischen geplantem und tatsächlichem Datum die größer 0 ist und für die alle 3 anderen Kriterien gleich sind (SUMMENPRODUKT).
2. Mit der Funktion AGGREGAT suche ich die KGRÖSTE Abweichung zwischen geplantem und tatsächlichem Datum die kleiner 0 isst und für die alle 3 anderen Kriterien gleich sind (SUMMENPRODUKT).
3. Mit einer WENN Funktion vergleiche ich die beiden Ergebnisse und wähle jenes Datum für die die absolute Abweichung geringer ist.
4. Mit der INDEX Funktion lasse ich mir die passende Bestellung ausgeben.

Nun ergeben sich aber immer noch folgende Probleme:
1. Für zwei oder mehr verschiedene Lieferungen gibt es eine einzelne Bestellung welche die geringste Abweichung im Datum aufweist. Es darf aber jede Bestellung maximal einmal gewählt werden.
2. Für einen Tag kann es auch zwei oder mehr Bestellungen mit gleichen Kriterien geben. Derzeit ergibt die INDEX Funktion hier einen Fehler, da hier aufgrund mehrfacher korrekter Zeilen auch die Zeile der für das Ergebnis der INDEX Funktion nicht mehr korrekt gefunden wird. Hier war die Überlegung ggf. über ein weiteres "künstliches" Kriterium durch eine ZÄHLENWENN Funktion in einer Hilfsspalte wieder die korrekte Zeile ausgeben zu lassen.
3. In Kombination des ersten und zweiten Problems kann es vorkommen, dass es für einen Tag zwei Bestellungen und auch zwei Lieferungen gibt. Hier dürfte dann jede Bestellung aber nur einmal gefunden werden.

Eine Beispieldatei habe ich angehängt.
Da es sich normalerweise um größere Tabellen mit bis zu 10.000 Zeilen handelt habe ich ursprüngliche Ansätze mit Matrix-Funktionen verwerfen müssen.

Vielleicht könnt Ihr mir aber die nötigen Ansätze geben, um die Lösung zu finden.

Bereits vorab herzlichen Dank für Eure Hilfe!

:)

 

Hallo,

Wenn Du die beiden sowieso wieder "zusammenwirfst", kannst Du gleich die Abweichung mit dem Absolutwert ermitteln und den kleinsten Wert nehmen.

Es ist für mich nicht ersichtlich, was Du genau willst. Brauchst Du nur die Bestellnummer oder auch die anderen Werte (z.B. Abweichungen vom Datum)?
Wenn mehrere Bestellungen die gleichen Werte hätten, brauchst Du dann alle?

Ein Wunschergebnis wäre ganz hilfreich.

 

Für die Bestellnummer könnte es so aussehen:
=WENNFEHLER(INDEX(OWEs!E:E;AGGREGAT(15;6;ZEILE(Datum_OWEs)/((RV_OWEs=A2)*(Artikel_OWEs=B2)*(Behälter_OWEs=C2)*(ABS(Datum_OWEs-D2)=AGGREGAT(15;6;ABS(Datum_OWEs-D2);1)));1));999999999)

 

Hey,

erstmal vielen Dank für deine Rückmeldung!

Das Ziel ist es, den Lieferungen aus dem Tabellenblatt "Zum Einspielen mit B.-Nr." jeweils genau eine Bestellnummer aus dem Tabellenblatt "OWEs" zuzuordnen.
Dabei darf aber jede Bestellnummer immer nur einmal "gefunden" werden, obwohl es sein kann, dass zwei Lieferungen theoretisch zur selben Bestellnummer passen würden.

Die anderen Ergebnisse (Abweichung etc.) brauche ich grundsätzlich nicht.
Ich hatte diese nur in separaten Spalten um die Formeln erstmal kürzer und übersichtlicher zu halten.

Deine angepasste Formel ist also schon sehr nah am "Perfektzustand".

Ich habe diese mal im Anhang in die Datei "Bestellnummer_finden_2" eingefügt.

Es müsste jetzt noch einen Weg geben, um zu verhindern, das eine Bestellnummer doppelt ausgegeben wird.
z.B. in Spalte 7 und 8 wird beides mal die Bestellnummer "7" ausgegeben.
Da aber in Spalte 7 schon die Bestellnummer "7" ausgegeben wurde, darf in Spalte 8 diese nicht mehr ausgegeben werden, sondern da noch eine zweite passende Bestellnummer vorhanden ist, müsste die "8" ausgegeben werden.

Ähnlich verhält es sich mit den Ergebnissen in Spalte 3 und 4
In Spalte 4 ist die Abweichung zu Bestellnummer 3 gleich 0. Daher sollte hier auch die Bestellnummer "3" gefunden werden.
In Spalte 3 ist die Abweichung gleich 11, daher dürfte das Ergebnis hier nicht die Bestellnummer "3" sein, da es eine andere Lieferung gibt, bei der die Abweichung geringer ist und diese Bestellnummer bereits ausgibt.

Ich hoffe ich habe mich diesmal deutlicher ausgedrückt und danke vorab für alle Rückmeldungen und Hilfestellungen!

Gruß Ilkor

 

Schau mal, ob die Formel passt:
Code:

Achtung! Matrixformel: Die Klammern {} nicht mit eingeben, stattdessen die Formel mit Strg+Shift+Enter abschließen.

 

Hallo nochmals,

ich bin zwar noch dabei nachzuvollziehen, was die Formel genau macht und werde mal testen, ob diese Formel auch bei größeren Datensätzen noch funktioniert aber im ersten Moment sieht es so aus, als würde alles funktionieren!

Herzlichen Dank auf jeden Fall!

Wenn ich soweit bin gebe ich nochmal ein kurzes Feedback!

Gruß!