Ungefähre Übereinstimmungen für Stufentabellen

Eine Stufentabelle ordnet kontinuierliche Werte Bereichen zu. Beispiele sind Steuerklassen, Versandkosten nach Gewicht, Mengenrabatte und Buchstabennoten nach Punktzahl.

Sie benötigen keine Zeile für jeden möglichen Wert, sondern nur den Startschwellenwert jeder Stufe. Eine Punktzahl von 87 hat keinen exakten Eintrag, fällt aber in den Bereich, der bei 80 beginnt.

Hier spielt die ungefähre Übereinstimmung ihre Stärke aus: Sie findet den passenden Bereich, statt eine exakte Übereinstimmung zu verlangen.

Bisher haben wir MATCH(value, range, 0) für eine exakte Übereinstimmung verwendet. Das dritte Argument 0 bedeutet: „Diesen Wert genau finden oder #N/A zurückgeben“.

Für Stufen verwenden wir stattdessen den Übereinstimmungstyp 1. Er findet den größten Wert, der kleiner oder gleich dem Suchwert ist. Genau so sollte eine Bereichssuche funktionieren.

Eine wichtige Regel: Beim Übereinstimmungstyp 1 muss die Liste der Schwellenwerte aufsteigend sortiert sein.

Stellen Sie sich eine Notentabelle vor. Spalte E enthält die aufsteigend sortierten unteren Schwellenwerte: 0, 60, 70, 80, 90. Spalte F enthält die Bezeichnungen: F, D, C, B, A.

Eine Punktzahl von 0 bis 59 ergibt F, 60 bis 69 ergibt D und so weiter. Wir speichern nur den Start jedes Bereichs, nicht jede einzelne Punktzahl.

Unser Ziel: Für eine Punktzahl in G1 soll die entsprechende Buchstabennote zurückgegeben werden.

Verwenden Sie MATCH mit ungefährer Übereinstimmung, um den Bereich zu finden, in den eine Punktzahl fällt. MATCH(G1, E2:E6, 1) sucht bei einer Punktzahl von 87 nach dem größten Schwellenwert, der höchstens 87 beträgt.

Die Schwellenwerte sind 0, 60, 70, 80, 90. Der größte Wert, der 87 nicht überschreitet, ist 80 und steht an Position 4. Daher gibt MATCH 4 zurück.

Diese Position verweist auf den richtigen Bereich, obwohl 87 selbst nicht in der Liste enthalten ist.

Übergeben Sie nun die Position an INDEX für die Bezeichnungsspalte F2:F6.

INDEX(F2:F6, MATCH(G1, E2:E6, 1)) verwendet Position 4 und gibt die vierte Bezeichnung, „B“, zurück.

Damit wird eine Punktzahl von 87 korrekt der Note B zugeordnet. Ändern Sie G1 in 95, gibt MATCH 5 zurück und liefert „A“; ändern Sie den Wert in 55, gibt MATCH 1 zurück und liefert „F“.

MATCH mit ungefährer Übereinstimmung (Typ 1) erfordert eine aufsteigende Reihenfolge im Suchbereich. Die Funktion geht davon aus, dass die Daten ansteigen, und stoppt, sobald sie den Suchwert überschreitet.

Wenn Ihre Schwellenwerte nicht sortiert sind, kann MATCH zu früh stoppen und eine falsche, unbemerkt falsche Position zurückgeben, ohne Sie durch einen Fehler zu warnen. Sortieren Sie die Schwellenwertspalte immer vom kleinsten zum größten Wert, bevor Sie sich auf eine Stufensuche verlassen.

XLOOKUP kann ebenfalls ungefähr übereinstimmen. Das fünfte Argument, der Übereinstimmungsmodus, akzeptiert -1 für „exakte Übereinstimmung oder nächstkleineres Element“ – ideal für Stufentabellen.

Damit wird der größte Schwellenwert gefunden, der höchstens G1 beträgt, und die zugehörige Bezeichnung zurückgegeben, ohne INDEX zu benötigen. Für Bereichssuchen ist das oft leichter zu lesen als INDEX-MATCH.

Betrachten wir nun einen Mengenrabatt. Die Schwellenwerte in E (bestellte Menge) sind: 0, 10, 50, 100. Die Rabatte in F: 0 %, 5 %, 10 %, 15 %.

• Bestellmenge 7: Der größte Schwellenwert bis einschließlich 7 ist 0, Position 1, daher werden 0 % zurückgegeben.
• Bestellmenge 60: Der größte Schwellenwert bis einschließlich 60 ist 50, Position 3, daher werden 10 % zurückgegeben.
• Bestellmenge 200: Der größte Schwellenwert bis einschließlich 200 ist 100, Position 4, daher werden 15 % zurückgegeben.

Eine Formel verarbeitet jede Menge.

Was passiert, wenn ein Wert kleiner als jeder Schwellenwert ist? Bei MATCH mit ungefährer Übereinstimmung gibt es keinen Wert, der höchstens so groß ist. Daher gibt MATCH #N/A zurück.

Um dies zu vermeiden, stellen Sie sicher, dass der erste Schwellenwert die Untergrenze abdeckt (oft 0), oder schließen Sie die Formel in IFERROR ein, um eine klare Meldung anzuzeigen, wenn eine Eingabe außerhalb des gültigen Bereichs liegt.

Achten Sie auf diese typischen Fehler bei Stufentabellen:

• Unsortierte Schwellenwerte: die häufigste Ursache für falsche, aber unbemerkte Ergebnisse.
• Übereinstimmungstyp 0 verwenden: erzwingt eine exakte Übereinstimmung und gibt bei jedem Zwischenwert #N/A zurück.
• Bereichsenden statt -anfängen speichern: MATCH vom Typ 1 erwartet die Untergrenze jedes Bereichs, nicht die Obergrenze.
• Schwellenwerte als Text: Als Text gespeicherte Zahlen verhindern den korrekten Vergleich. Speichern Sie sie als Zahlen.

Sie können die ungefähre Übereinstimmung mit der zweidimensionalen Technik kombinieren. Stellen Sie sich Versandkosten vor, die sowohl von der Gewichtsstufe (Zeilen) als auch von der Zonenstufe (Spalten) abhängen.

Verwenden Sie eine MATCH-Funktion mit ungefährer Übereinstimmung (Typ 1), um die Gewichtszeile zu finden, und eine weitere, um die Zonenspalte zu finden. Übergeben Sie anschließend beide Positionen an INDEX. Da beide Achsen aus sortierten Schwellenwerten bestehen, findet jede MATCH-Funktion den richtigen Bereich.

So verbinden Sie INDEX-MATCH-MATCH mit Stufenlogik für umfangreiche Tariftabellen.

Überprüfen Sie Ihr Verständnis von ungefähren Stufensuchen.

Für Suchen in Stufen- und Bereichstabellen:

• Speichern Sie den unteren Schwellenwert jedes Bereichs und sortieren Sie ihn aufsteigend.
• Verwenden Sie MATCH(value, thresholds, 1), um die Position des Bereichs zu finden (größter Wert, der höchstens der Eingabe entspricht).
• Schließen Sie die Funktion in INDEX(labels, ...) ein, um den Bereich zurückzugeben, oder verwenden Sie XLOOKUP(..., -1) für dasselbe Ergebnis.

Decken Sie die Untergrenze mit einem Schwellenwert von 0 ab oder verwenden Sie IFERROR für Eingaben außerhalb des gültigen Bereichs. Lassen Sie die Schwellenwerte niemals unsortiert.