Solving Real Lookup Problems
Swipe um das Menü anzuzeigen
Exakte Übereinstimmungen sind für relationale Modelle nützlich, aber viele Geschäftssysteme basieren auf Bereichen und Schwellenwerten statt auf festen Werten. In diesem Kapitel wird die ungefähre Übereinstimmung verwendet, um stufenbasierte Preislogik und dynamische Rabattberechnungen zu erstellen.
Ungefähre Übereinstimmung mit XLOOKUP
=XLOOKUP(lookup_value, lookup_array, return_array, [if_not_found], -1)
lookup_value: zu bewertender Wert;lookup_array: Schwellenwerte;return_array: zurückgegebener Wert;-1: gibt den größten Wert zurück, der kleiner oder gleich dem Suchwert ist.
Für ungefähre Übereinstimmungen muss das Sucharray aufsteigend sortiert sein.
Stufenbasierte Logik
Stufentabellen definieren Mindestschwellenwerte anstelle von exakten Übereinstimmungen.
Beispiel:
| Min_Units | Discount_Rate |
|---|---|
| 1 | 0% |
| 5 | 5% |
| 10 | 10% |
| 20 | 15% |
Excel prüft, wo der Wert für Units innerhalb der Schwellenstruktur liegt, und gibt den entsprechenden Rabatt zurück.
Ein neues Arbeitsblatt mit dem Namen erstellen:
Pricing_Tiers
Den folgenden Datensatz hinzufügen:
Min_Units;Discount_Rate
1;0%
5;5%
10;10%
20;15%
Den Datensatz in eine Excel-Tabelle umwandeln.
Sicherstellen, dass Min_Units aufsteigend sortiert ist.
In Sales_Data eine neue Spalte mit dem Namen erstellen:
Discount_Rate
In der ersten Zeile eingeben:
=XLOOKUP([@Units],Pricing_Tiers[Min_Units],Pricing_Tiers[Discount_Rate],0,-1)
[@Units]: Wert der aktuellen Zeile in Units;Pricing_Tiers[Min_Units]: Schwellenwert-Spalte;Pricing_Tiers[Discount_Rate]: zurückgegebener Rabattwert;0: Rückgabewert bei Nichtfinden;-1: Modus für ungefähre Übereinstimmung.
Die Ergebnisse als Prozentsätze formatieren.
Eine neue Spalte mit dem Namen erstellen:
Discounted_Revenue
In der ersten Zeile eingeben:
=[@Revenue]*(1-[@Discount_Rate])
[@Revenue]: ursprünglicher Umsatzwert;[@Discount_Rate]: angewendeter Rabattprozentsatz.
Die Berechnung passt sich nun dynamisch an das Absatzvolumen an.
Die Werte in Pricing_Tiers ändern.
Sicherstellen, dass alle abhängigen Berechnungen automatisch aktualisiert werden.
Die bisherige Formel für das Top-Produkt ersetzen durch:
=XLOOKUP(MAX(Sales_Data[Discounted_Revenue]),Sales_Data[Discounted_Revenue],Sales_Data[Product],"Not found")
Die Suche bewertet nun die rabattierte Leistung statt des Rohumsatzes.
Im Blatt Summary einen Eingabebereich für:
Order_ID
anlegen.
In die Ergebniszelle eingeben:
=XLOOKUP(M2,Sales_Data[Order_ID],Sales_Data[Customer_ID],"Not found")
M2: Order_ID-Eingabe;Sales_Data[Order_ID]: Suchspalte;Sales_Data[Customer_ID]: zurückgegebener Wert.
In eine weitere Ergebniszelle eingeben:
=XLOOKUP(M2,Sales_Data[Order_ID],Sales_Data[Product],"Not found")
Die Formeln rufen nun Transaktionsdetails dynamisch anhand der ausgewählten Order_ID ab.
Den Wert in M2 ändern.
Sicherstellen, dass:
- Kundendaten automatisch aktualisiert werden;
- Produktdaten automatisch aktualisiert werden;
- Die Formeln immer die passende Transaktion zurückgeben.
1. Was bewirkt match_mode = -1 in XLOOKUP?
2. Warum muss das Sucharray bei der ungefähren Übereinstimmung sortiert sein?
3. Was macht XLOOKUP für Rückwärtssuchen geeignet?
Danke für Ihr Feedback!
Fragen Sie AI
Fragen Sie AI
Fragen Sie alles oder probieren Sie eine der vorgeschlagenen Fragen, um unser Gespräch zu beginnen
