Excel-Tutorial: So markieren Sie ungefähre Übereinstimmungssuchen

Inhaltsverzeichnis

Verwandte Verknüpfungen

In diesem Video sehen wir uns an, wie ungefähre Übereinstimmungssuchen mit bedingter Formatierung hervorgehoben werden.

Hier haben wir eine einfache Nachschlagetabelle, die die Materialkosten für verschiedene Höhen und Breiten zeigt. Die Formel in K8 verwendet die INDEX- und MATCH-Funktionen, um die korrekten Kosten basierend auf den in K6 und K7 eingegebenen Breiten- und Höhenwerten abzurufen.

Beachten Sie, dass die Suche auf einer ungefähren Übereinstimmung basiert. Da die Werte in aufsteigender Reihenfolge vorliegen, überprüft MATCH die Werte, bis ein größerer Wert erreicht ist, tritt dann zurück und gibt die vorherige Position zurück.

Erstellen wir eine bedingte Formatierungsregel, um die übereinstimmende Zeile und Spalte hervorzuheben.

Wie immer bei schwierigeren bedingten Formatierungen empfehle ich, zuerst mit Dummy-Formeln zu arbeiten und dann eine Arbeitsformel direkt in die bedingte Formatierungsregel zu übertragen. Auf diese Weise können Sie beim Debuggen der Formel alle Excel-Tools verwenden, wodurch Sie viel Zeit sparen.

Ich werde zuerst die Formel für die Breite einrichten. Wir müssen TRUE für jede Zelle in Zeile 7 zurückgeben, wobei die übereinstimmende Breite 200 beträgt.

Dies bedeutet, dass wir unsere Formel mit $ B5 = beginnen und die Spalte sperren müssen.

= $ B5 =

Jetzt können wir in der Spalte "Breiten" nicht nach 275 suchen, da es nicht vorhanden ist. Stattdessen benötigen wir eine ungefähre Übereinstimmung, die genau wie unsere Suchformel 200 ergibt.

Der einfachste Weg, dies zu tun, ist die Verwendung der LOOKUP-Funktion. LOOKUP führt automatisch eine ungefähre Übereinstimmung durch, und anstatt eine Position wie MATCH zurückzugeben, gibt LOOKUP den tatsächlichen Übereinstimmungswert zurück. Also können wir schreiben:

$ B5 = LOOKUP ($ K $ 6, $ B $ 6: $ B $ 12)

Mit unserer Eingabebreite für den Suchwert und allen Breiten in der Tabelle für den Ergebnisvektor.

Wenn ich F9 verwende, können Sie den Wert sehen, den LOOKUP zurückgibt.

Wenn ich nun eine Formel in die Tabelle eingebe, erhalten wir TRUE für jede Zelle in der Zeile mit 200 Breiten.

Jetzt müssen wir die Formel erweitern, um sie an die Höhenspalte anzupassen. Dazu füge ich die ODER-Funktion und dann eine zweite Formel hinzu, die der Höhe entspricht.

Wir werden die Formel auf die gleiche Weise starten, aber dieses Mal müssen wir die Zeile sperren:

= B $ 5

Dann verwenden wir die LOOKUP-Funktion erneut mit der Höhe für den Suchwert und allen Höhen in der Tabelle als Ergebnisvektor.

= ODER ($ B5 = LOOKUP ($ K $ 6, $ B $ 6: $ B $ 12), B $ 5 = LOOKUP ($ K $ 7, $ C $ 5: $ H $ 5))

Wenn ich die Formel über die Tabelle kopiere, erhalten wir TRUE für jede Zelle in der übereinstimmenden Spalte und jede Zelle in der übereinstimmenden Zeile - genau das, was wir für die bedingte Formatierung benötigen.

Ich kann einfach die Formel in der oberen linken Zelle genau kopieren und eine neue Regel erstellen.

Wenn ich jetzt die Breite oder Höhe ändere, funktioniert die Hervorhebung wie erwartet.

Wenn Sie nur den Suchwert selbst hervorheben möchten, ist dies eine einfache Änderung. Bearbeiten Sie einfach die Formel und ersetzen Sie die ODER-Funktion durch die UND-Funktion.

= AND ($ B5 = LOOKUP ($ K $ 6, $ B $ 6: $ B $ 12), B $ 5 = LOOKUP ($ K $ 7, $ C $ 5: $ H $ 5))