Office: Sverweis mit mehreren Kriterien (auch größer und kleiner als)

Hallo,

ich möchte in einer Tabelle einen SVerweis bzw. eine ähnliche Funktion nutzen, die mir auf Basis von zwei Suchkriterien das korrekte Ergebnis anzeigt. Mein "Problem" dabei ist, dass einer der Werte nicht exakt in der Datenbasis gefunden werden kann und somit sämtliche im Netz befindlichen Hinweise nicht so ganz auf mein Anliegen passen *frown.gif* *frown.gif* *frown.gif*


Spalte A: ist mein Suchkriterium 1... welches auch klar in der Datenbasis wiedergefunden werden kann


Spalte B gibt die Länge an, welche in der Datenbasis automatisch ermittelt werden muss um dann die richtige Verpackungsgröße wiederzugeben.


Im Anhang findet Ihr ein einfaches Beispiel hierzu.

Der grüne Bereich spiegelt das Ergebnis wieder, welches ich aktuell von Hand eingetragen habe.

Ich hoffe Ihr könnt helfen.

Gruß Micha

:)

 

Hallo Micha,

zB so:
SVERWEIS, wie du ihn garantiert noch nicht kennst! | Der Tabellenexperte

Code:

Achtung! Matrixformel. Die geschweiften Klammern werden nicht mit eingegeben, sondern jede Bearbeitung mit Strg+Shift+Enter abgeschlossen.


solange die Länge ganzzahlig und >= 0 ist
und in der Datenbasis
1. die Länge von für jeder Höhe mit 0 beginnt und
2. keine Lücken in den Längen für eine Höhe vorhanden sind.

 

Hallo, ich würde so tun..:

PHP:

         =INDEX($H$3:$H$10;VERGLEICH(B2;MMULT(($E$3:$E$10=A2)*$F$3:$F$10;1);1))


 

Danke für die Tipps, das werde ich am Donnerstag direkt mal testen *Smilie

Gruß Micha

 

Hallo,

Achtung!

Da die Funktion "Vergleich" mit dem dritten Parameter "1" binär in der Liste sucht, funktioniert Franks Formel nur, wenn es (wie im Beispiel) in der Datenbasis nur zwei Höhen gibt und die zweite Höhe mindestens die Hälfte der Einträge hat. Die MMULT-Liste ist ansonsten nicht mehr sortiert (alle Einträge mit abweichender Höhe haben den Wert 0).

siehe Anlage


ps.
Wenn in der Tabelle eine Länge > 400 nicht schon abgefangen wird, würde ich in der Datenbasis für jede Höhe eine zusätzliche Zeile einfügen mit "Länge von " := 401 und "Stückzahl := =NV() .

 

C2: =INDEX(H:H;VERGLEICH(A2+B2%%;INDEX(E$1:E$10+F$1:F$10%%;)))

Bei genauigkeitsüberschreitenden Vergleichen (also mehr als 3 oder 4 Spalten) kann man TEXT() hierarchisch verketten, so dass viele Spalten für den Vergleich geprüft werden können:

C2: =INDEX(H$3:H$10;VERGLEICH(TEXT(A2;0)&TEXT(B2;"000");INDEX(TEXT(E$3:E$10;0)&TEXT(F$3:F$10;"000");)))

 

Guten Morgen *Smilie

vielen Dank für die vielen und auch unterschiedlichen Vorschläge *Smilie

aktuell favorisiere ich Lupos Vorschlag.

Ich habe mit der Formelauswertung "versucht" das Ganze nachzuvollziehen. Allerdings komme ich da leider überhaupt nicht mit *frown.gif*
Eine Umrechnung der Zahl in Prozent... diese dann auch noch mit dem ersten Wert addiert... Phänomenal was man mit Mathe anstellen kann *Smilie Verstehen tue ich es trotzdem nicht ganz *frown.gif*

Gruß Micha

 

Eine Umrechnung in Prozent (als irgendein betonierter Begriff) gibt es eigentlich gar nicht. Prozent ist ganz einfach Zahl durch Hundert. Genau das tut auch das Dahinterhängen eines %. Nimmste zwei, haste zwei: 1/10000.

Deine Daten(intervalle) sind aufsteigend sortiert (was auch eine Voraussetzung für die Formel ist). Je nachrangiger der Untersort, desto mehr %. Man muss darauf achten, dass die Zahlenbereiche den Raum, den das % bietet, nicht sprengen.

 

Danke für den Hinweis... in der Originaldatei funktierniert es dann mit %%% tadellos *Smilie

Hab einen tollen Tag!

Gruß Micha

 

Hallo nochmals,

leider funktioniert es doch nicht so ganz...

Ich habe das Ganze mit Lupos Formel aufgabaut und ab Zeile 51 gibt er mir keine passenden Daten mehr...

Danach habe ich Helmuts Version getestet und dies stößt ebenfalls an Grenzen. Und auch hier kann ich es mir nicht erklären, da die Ergebnisse Wahllos mal richtig und mal falsch sind.

Ich habe nochmals die Datei aktualisiert mit allen aktuellen "Basisdaten"... Schön wäre es natürlich, wenn ich diese auch beliebig erweitern kann und die Daten dann noch immer korrekt berechnet werden würden.

In der Tabelle habe ich einmal die Spalte mit Helmuts Berechnung und einmal die Spalte mit Lupos Berechnung nebeneinandergestellt...

 

wir haben es jetzt mit einem einfachen Summenprodukt umgesetzt... keine Ahnung ob die Tabelle dadurch irgendwann mal extrem langsam wird, aber es geht *Smilie

falls ihr noch bessere Ideen habt, wir würden sie auch gern hören.

hier die Formel:

=SUMMENPRODUKT(($I$3:$I$74=$A3)*1;($J$3:$J$74=B3)*1;$L$3:$L$74)

Gruß Micha

 

E3: =INDEX(L:L;VERGLEICH(A3+B3%%%;INDEX(I$1:I$74+J$1:J$74%%%;)))

Die Begründung ist wie folgt: Bis Zeile 50 (das ist die Hälfte der bisherigen Lookup 100!) klappt alles, weil VERGLEICH in seiner Binärsuche nur in der oberen Hälfte des Baumes 1:100 (=also 1:50) unterwegs ist. Bei Zeile 51 möchte es aber nach unten hüpfen, aber da ist nix, da die Zeile 75 leer ist (perfiderweise, zufälligerweise und glücklicherweise gehen Deine Daten nur bis Zeile 74!)

Ist mir in meinen 35 Jahren Tabellenkalkulation noch nicht untergekommen. Vielleicht habe ich den Lookup-Bereich auch immer bündig referenziert, wer weiß. Das war ja bei Deinem Ausgangsbeispiel auch so.

Also vielen Dank, dass Du uns allen über Dein voll aufgezogenes Beispiel (das hat nämlich noch niemand gemacht) hier die Augen geöffnet hast: Keine Leerzellen als Lookup-Bereich hinten angehängt bei Binärsuche mitberücksichtigen! Das darf man nur bei Exaktsuche.

Bereiche also am besten mit INDEX und ANZAHL2 benennen oder Strg-L-Tabellen verwenden.

 

Hi Lupo,

Danke für Deine ausführliche Antwort *Smilie

Den Hintergrund verstehe ich.

Falls Du Zeit und Lust hast:
kann es bei dem aktuell von uns gewähltem "Summenprodukt" Probleme geben? Performance oder ähnlich?

Gruß Micha

 

Binärsuche ist formelmäßig nicht zu toppen, nur über VBA, Pivot oder Power-Query.

SUMMENPRODUKT wirkt (bei Massendaten) verglichen damit wie eine Mistgabel im Milchzahn, da es immer alles durchsucht.