Zusammenfassung
Um eine bidirektionale ungefähre Übereinstimmungssuche mit mehreren Kriterien durchzuführen, können Sie mithilfe der IF-Funktion eine auf INDEX und MATCH basierende Arrayformel verwenden, um Kriterien anzuwenden. In dem gezeigten Beispiel lautet die Formel in K8:
=INDEX(data,MATCH(K6,IF(material=K5,hardness),1),MATCH(K7,diameter,1))
Dabei werden Daten (D6: H16), Durchmesser (D5: H5), Material (B6: B16) und Härte (C6: C16) als Bereiche bezeichnet, die nur der Einfachheit halber verwendet werden.
Hinweis: Dies ist eine Array-Formel und muss mit Strg + Umschalt + Eingabetaste eingegeben werden
Erläuterung
Ziel ist es, eine Vorschubgeschwindigkeit basierend auf Material, Härte und Bohrerdurchmesser zu ermitteln. Vorschubwerte sind in den genannten Bereich Daten (D6: H16).
Dies kann mit einer Zwei-Wege-INDEX- und MATCH-Formel erfolgen. Eine MATCH-Funktion berechnet die Zeilennummer (Material und Härte) und die andere MATCH-Funktion ermittelt die Spaltennummer (Durchmesser). Die INDEX-Funktion gibt das Endergebnis zurück.
In dem gezeigten Beispiel lautet die Formel in K8:
=INDEX(data, MATCH(K6,IF(material=K5,hardness),1), // get row MATCH(K7,diameter,1)) // get column
(Zeilenumbrüche werden nur zur besseren Lesbarkeit hinzugefügt).
Das Knifflige ist, dass Material und Härte zusammen gehandhabt werden müssen. Wir müssen MATCH auf die Härtewerte für ein bestimmtes Material beschränken (kohlenstoffarmer Stahl im gezeigten Beispiel).
Wir können dies mit der IF-Funktion tun. Im Wesentlichen verwenden wir IF, um irrelevante Werte "wegzuwerfen", bevor wir nach einer Übereinstimmung suchen.
Einzelheiten
Die Index - Funktion ist , die benannten Bereich gegebener Daten (: H16 D6) wie für Arrays. Die erste MATCH-Funktion berechnet die Zeilennummer:
MATCH(K6,IF(material=K5,hardness),1) // get row num
Um die richtige Reihe zu finden, müssen wir eine genaue Übereinstimmung mit dem Material und eine ungefähre Übereinstimmung mit der Härte vornehmen. Dazu verwenden wir die IF-Funktion, um zunächst die irrelevante Härte herauszufiltern:
IF(material=K5,hardness) // filter
Wir testen alle Werte im Material (B6: B16), um festzustellen, ob sie mit dem Wert in K5 ("Low Carbon Steel") übereinstimmen. Wenn ja, wird der Härtewert durchlaufen. Wenn nicht, gibt IF FALSE zurück. Das Ergebnis ist ein Array wie folgt:
(FALSE;FALSE;FALSE;85;125;175;225;FALSE;FALSE;FALSE;FALSE)
Beachten Sie, dass die einzigen überlebenden Werte die mit kohlenstoffarmem Stahl verbundenen sind. Die anderen Werte sind jetzt FALSE. Dieses Array wird direkt an die MATCH-Funktion als lookup_array zurückgegeben.
Der Suchwert für die Übereinstimmung stammt von K6, das die angegebene Härte 176 enthält. MATCH wird für die ungefähre Übereinstimmung konfiguriert, indem match_type auf 1 gesetzt wird. Mit diesen Einstellungen ignoriert MATCH FALSE-Werte und gibt die Position einer exakten Übereinstimmung oder den nächstkleineren Wert zurück .
Hinweis: Die Härtewerte müssen für jedes Material in aufsteigender Reihenfolge sortiert werden.
Bei einer Härte von 176 gibt MATCH 6 zurück und wird als Zeilennummer direkt an INDEX übergeben. Wir können jetzt die ursprüngliche Formel wie folgt umschreiben:
=INDEX(data,6,MATCH(K7,diameter,1))
Die zweite MATCH-Formel ermittelt die richtige Spaltennummer, indem eine ungefähre Übereinstimmung des Durchmessers durchgeführt wird:
MATCH(K7,diameter,1) // get column num
Hinweis: Werte im Durchmesser D5: H5 müssen in aufsteigender Reihenfolge sortiert werden.
Der Lookup - Wert kommt von K7 (0,75) und die Suchmatrix ist die benannte Bereich Durchmesser (D5: H5).
Wie zuvor wird MATCH auf die ungefähre Übereinstimmung eingestellt, indem match_type auf 1 gesetzt wird.
Mit einem Durchmesser von 0,75 gibt MATCH 3 zurück, das als Spaltennummer direkt an die INDEX-Funktion übergeben wird. Die ursprüngliche Formel lautet nun:
=INDEX(data,6,3) // returns 0.015
INDEX gibt ein Endergebnis von 0,015 zurück, den Wert von F11.
