Løse reelle oppslagsproblemer
Sveip for å vise menyen
Eksakte treff er nyttige for relasjonsmodeller, men mange forretningssystemer baserer seg på intervaller og terskler i stedet for faste verdier. I dette kapittelet brukes omtrentlig samsvar for å bygge trinnbasert prislogikk og dynamiske rabattberegninger.
Omtrentlig samsvar med XLOOKUP
=XLOOKUP(lookup_value, lookup_array, return_array, [if_not_found], -1)
lookup_value: verdien som evalueres;lookup_array: terskelverdier;return_array: returnert resultat;-1: returnerer den største verdien som er mindre enn eller lik oppslagsverdien.
Omtrentlig samsvar krever at oppslagsområdet er sortert i stigende rekkefølge.
Trinnbasert logikk
Trinntabeller definerer minimumsterskler i stedet for eksakte treff.
Eksempel:
| Min_Units | Discount_Rate |
|---|---|
| 1 | 0% |
| 5 | 5% |
| 10 | 10% |
| 20 | 15% |
Excel vurderer hvor Units-verdien passer inn i terskelstrukturen og returnerer tilsvarende rabatt.
Opprett et nytt regneark med navnet:
Pricing_Tiers
Legg til følgende datasett:
Min_Units;Discount_Rate
1;0%
5;5%
10;10%
20;15%
Konverter datasettet til en Excel-tabell.
Bekreft at Min_Units er sortert i stigende rekkefølge.
I Sales_Data, opprett en ny kolonne med navnet:
Discount_Rate
I første rad, skriv inn:
=XLOOKUP([@Units],Pricing_Tiers[Min_Units],Pricing_Tiers[Discount_Rate],0,-1)
[@Units]: Units-verdi i gjeldende rad;Pricing_Tiers[Min_Units]: terskelkolonne;Pricing_Tiers[Discount_Rate]: returnert rabattverdi;0: reserveverdi;-1: omtrentlig samsvarsmodus.
Formater resultatene som prosent.
Opprett en ny kolonne med navnet:
Discounted_Revenue
I første rad, skriv inn:
=[@Revenue]*(1-[@Discount_Rate])
[@Revenue]: opprinnelig inntektsverdi;[@Discount_Rate]: brukt rabattprosent.
Beregningen justeres nå dynamisk basert på antall enheter.
Endre verdiene i Pricing_Tiers.
Bekreft at alle avhengige beregninger oppdateres automatisk.
Bytt ut den forrige topproduktformelen med:
=XLOOKUP(MAX(Sales_Data[Discounted_Revenue]),Sales_Data[Discounted_Revenue],Sales_Data[Product],"Not found")
Oppslaget vurderer nå rabattert ytelse i stedet for brutto inntekt.
I arket Summary, opprett en inndataseksjon for:
Order_ID
I oppslagsresultatcellen, skriv inn:
=XLOOKUP(M2,Sales_Data[Order_ID],Sales_Data[Customer_ID],"Not found")
M2: Order_ID-inndata;Sales_Data[Order_ID]: oppslagskolonne;Sales_Data[Customer_ID]: returnert verdi.
I en annen resultatcelle, skriv inn:
=XLOOKUP(M2,Sales_Data[Order_ID],Sales_Data[Product],"Not found")
Formlene henter nå transaksjonsdetaljer dynamisk fra valgt Order_ID.
Endre verdien i M2.
Bekreft at:
- Kundeverdier oppdateres automatisk;
- Produktverdier oppdateres automatisk;
- Formlene alltid returnerer samsvarende transaksjon.
1. Hva gjør match_mode = -1 i XLOOKUP?
2. Hvorfor må oppslagsområdet være sortert ved bruk av omtrentlig samsvar?
3. Hva gjør XLOOKUP egnet for omvendte oppslag?
Takk for tilbakemeldingene dine!
Spør AI
Spør AI
Spør om hva du vil, eller prøv ett av de foreslåtte spørsmålene for å starte chatten vår
