Live-Forum - Die aktuellen Beiträge
Datum
Titel
28.03.2024 21:12:36
28.03.2024 18:31:49
Anzeige
Archiv - Navigation
1484to1488
Aktuelles Verzeichnis
Verzeichnis Index
Übersicht Verzeichnisse
Vorheriger Thread
Rückwärts Blättern
Nächster Thread
Vorwärts blättern
Anzeige
HERBERS
Excel-Forum (Archiv)
20+ Jahre Excel-Kompetenz: Von Anwendern, für Anwender
Inhaltsverzeichnis

Doppelter Vergleich / Index

Doppelter Vergleich / Index
05.04.2016 16:18:15
Michael
Guten Tag,
ich überlege gerade an eine Lösung zu kommen einen doppelten Vergleich() hinzubekommen. Dabei sollen zwei Spalten nach einem passenden Datensatz durchsucht werden und ein dritter Datensatz aus der Spalte ausgegeben werden.
Folgende Situation:
Menge KM Fracht
500 --- 10 - 1
500 --- 20 - 2
500 --- 30 - 3
500 --- 40 - 4
500 --- 50 - 5
1000 - 10 - 0,5
1000 - 20 - 1
1000 - 30 - 1,5
1000 - 40 - 2
1000 - 50 - 2,5
Ich möchte jetzt eine Formel haben, die mir z.B. "5" ausgibt, wenn ich nach 500 und 50km suche.
Bisher war mein Ansatz ein SVerweis dem per Matix-Formel eine "gefilterte Liste" übergeben wird. Das klappt auch, wenn man nach den exakten Werten sucht. Nun gibt es aber auch die Möglichkteit, dass nach Menge = 800, Entfernung = 17km gesucht wird und dann soll der nächstkleinere Wert gefunden werden, also in diesem Fall das Tupel (500, 20, 2).
Hat jemand eine Idee für eine Lösung?

12
Beiträge zum Forumthread
Beiträge zu diesem Forumthread

Betreff
Datum
Anwender
Anzeige
AW: 20 ist aber größer als 17 und nicht kleiner...
05.04.2016 16:21:44
...
Hallo Michael,
... welche eindeutigere Vorgabe Deinerseits muss also eingehalten werden?
Gruß Werner
.. , - ...

Doppelter Vergleich / Index
05.04.2016 16:24:51
Michael
Hi,
bei der Entfernung sollte der nächstgrößere Wert genommen werden, bei der Menge der nächstkleinere...
Sorry, ist mir auch kurz nach dem Absenden aufgefallen.

AW: dann mit AGGREGAT() ...
05.04.2016 16:29:00
...
Hallo Michael,
... so:
 ABCDE
1MengeKMFrachtMenge:800
2500101KM:17
3500202Fracht:2
4500303  
5500404  
6500505  
71000100,5  
81000201  
91000301,5  
101000402  
111000502,5  
12     

Formeln der Tabelle
ZelleFormel
E3=AGGREGAT(15;6;C2:C11/(A2:A11<=E1)/(B2:B11>E2); 1)


Excel Tabellen im Web darstellen >> Excel Jeanie HTML 4
Gruß Werner
.. , - ...

Anzeige
Doppelter Vergleich / Index
05.04.2016 16:35:48
Michael
Also Werner, vielen Dank, das klappt echt super. Aggregat kannte ich noch nicht, also wieder was gelernt.
Hast du zufällig einen Link parat der erklärt was genau bei der Übergabe des Arrays mit den "/" passiert? Das habe ich nämlich noch nicht gesehen bisher.

AW: dazu ...
05.04.2016 16:48:48
...
Hallo Michael,
... soweit mir bekannt gibt es noch keinen Link mit ausführlicheren Erklärungen zu der spezif. Funktionsweise, die ich hier eingesetzt habe. Es ist noch geplant, dass ich Ende des Jahres mal in http://www.online-excel.de/ dazu was schreibe.
Doch eine analoge Wirkungsweise des "/" gibt es in anderen Formelkonstrukten wie z.B. VERWEIS(9;1/(....);...) schon länger und diesbzgl. habe ich im dortigen Forum http://www.online-excel.de/fom/fo_na.php?f=1 auch schon öfters erklärt.
Kurzfassung: der Quotient ergibt eine Matrix aus WAHR und FALSCH -Werten die durch "/" als 1 oder 0 ausgewertet werden und somit im dritten Argument der Funktion sich eine Matrix aus Werten und Fehlerwerten ergibt. Auf Grund des zweiten Arguments der Funktion in der Formel (6) werden nur die Zahlenwerte ausgewertet.
Gruß Werner
.. , - ...

Anzeige
Doppelter Vergleich / Index
05.04.2016 17:39:57
Michael
Okay, danke =)

verständlicher ohne Aggregat / ohne Division
05.04.2016 17:12:51
WF
Hi,
folgende Arrayformel:
{=MIN(WENN((A2:A11E2);C2:C11))}
Salut WF

Doppelter Vergleich / Index
05.04.2016 17:38:56
Michael
Hi,
die Formel ist auf den ersten Blick wirklich verständlicher, aber die mit dem Aggregat finde ich besser, weil Matrix-Formeln volatil sind. Wenn man viele davon verwendet wird Excel ganz schnell extrem langsam, deswegen versuche ich immer Alternativen zu finden.
Trotzdem vielen Dank WF!

AW: dazu jedoch noch folgendes ...
05.04.2016 17:59:57
...
Hallo Michael,
... Matrixformeln sind mW nicht per se volatil (werden sie nur durch entsprechende eingesetzte volatile Funktionen). Die Formel die WF hier alternativ aufgezeigt hat, basiert auf keiner volatilen Funktion.
Im konkreten Fall mag die WF-Formel einfacher verständlich sein. Das aber oft einfach nur der Macht der Gewohnheit an. Für mich sind mittlerweile die spez. AGGREGAT()-Formeln - ich nenne diese Matrixfunktion(alitäts)formeln - mindestens genau so einfach verständlich.
AGGREGAT()-Formeln sind bei größeren auszuwertenden Datenmengen meist wirklich schneller. Übrigens, da wo SUMMENPRODUKT()-Formeln einsetzbar sind, zieht diese auch fast jeder einer {=SUMME(...)}-Formel vor.
Gruß Werner
.. , - ...

Anzeige
Matrix-Formeln sind NICHT volatil
05.04.2016 18:04:33
WF
.

Doppelter Vergleich / Index
05.04.2016 16:23:11
Michael
Zusätzlicher Hinweis:
Die Formel sollte etwa so aussehen wie
=INDEX(A:D;VERGLEICH(1200;B:B;1);1) nur eben, dass es 2 Vergleiche gibt statt einer!

AW: geht zwar auch, aber mE umständlicher owT
05.04.2016 16:30:14
...
Gruß Werner
.. , - ...

Beliebteste Forumthreads (12 Monate)

Anzeige

Beliebteste Forumthreads (12 Monate)

Anzeige
Anzeige
Anzeige