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 logikk for prisnivåer 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.
Logikk for prisnivåer
Nivåtabeller 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 inn 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: modus for omtrentlig samsvar.
Formater resultatene som prosenter.
Opprett en ny kolonne med navnet:
Discounted_Revenue
I første rad, skriv inn:
=[@Revenue]*(1-[@Discount_Rate])
[@Revenue]: opprinnelig Revenue-verdi;[@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 rå omsetning.
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 returnerer alltid den samsvarende transaksjonen.
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
