Hitta närmaste nummer

Innehåll

I praktiken finns det väldigt ofta fall då du och jag behöver hitta det närmaste värdet i en uppsättning (tabell) i förhållande till ett givet tal. Det kan till exempel vara:

Beräkning av rabatt beroende på volym.
Beräkning av bonusbeloppet beroende på genomförandet av planen.
Beräkning av fraktpriser beroende på avstånd.
Val av lämpliga containrar för varor m.m.

Dessutom kan avrundning krävas både uppåt och nedåt, beroende på situationen.

Det finns flera sätt – självklart och inte så självklart – att lösa ett sådant problem. Låt oss titta på dem i följd.

Till att börja med, låt oss föreställa oss en leverantör som ger rabatter på grossist, och procentandelen av rabatten beror på mängden varor som köps. Till exempel, vid köp av fler än 5 stycken ges 2% rabatt, och vid köp från 20 stycken - redan 6%, etc.

Hur beräknar man snabbt och vackert rabattprocenten när man anger kvantiteten av de köpta varorna?

Metod 1: Kapslade IFs

En metod från serien ”vad finns det att tänka – du måste hoppa!”. Använda kapslade funktioner IF (OM) för att sekventiellt kontrollera om cellvärdet faller inom vart och ett av intervallen och visa en rabatt för motsvarande intervall. Men formeln i det här fallet kan visa sig vara väldigt besvärlig:

Hitta närmaste nummer

Jag tycker att det är uppenbart att det är roligt att felsöka en sådan "monsterdocka" eller försöka lägga till ett par nya villkor till den efter en tid.

Dessutom har Microsoft Excel en kapslingsgräns för IF-funktionen – 7 gånger i äldre versioner och 64 gånger i nyare versioner. Vad händer om du behöver mer?

Metod 2. VLOOKUP med intervallvy

Denna metod är mycket mer kompakt. För att beräkna rabattprocenten, använd den legendariska funktionen VPR (VISNING) i ungefärligt sökläge:

Hitta närmaste nummer

var

B4 – värdet av kvantiteten varor i den första transaktionen som vi letar efter rabatt för
$G$4:$H$8 – en länk till rabatttabellen – utan "header" och med adresserna fixerade med $-tecknet.
2 — ordningsnumret på den kolumn i rabatttabellen som vi vill få rabattvärdet från
SANN – det är här "hunden" ligger begravd. If som det sista funktionsargumentet VPR specificera LIGGANDE (FALSK) eller 0, då kommer funktionen att leta efter strikt match i kvantitetskolumnen (och i vårt fall kommer det att ge ett #N/A-fel, eftersom det inte finns något värde 49 i rabatttabellen). Men om istället LIGGANDE skriva SANN (SANN) eller 1, då kommer funktionen inte att leta efter det exakta, men närmast minsta värde och ger oss den procentuella rabatten vi behöver.

Nackdelen med denna metod är behovet av att sortera rabatttabellen i stigande ordning efter den första kolumnen. Om det inte finns någon sådan sortering (eller det görs i omvänd ordning), kommer vår formel inte att fungera:

Hitta närmaste nummer

Följaktligen kan detta tillvägagångssätt endast användas för att hitta närmaste minsta värde. Om du behöver hitta den närmaste största, måste du använda ett annat tillvägagångssätt.

Metod 3. Hitta den närmaste största med hjälp av funktionerna INDEX och MATCH

Låt oss nu titta på vårt problem från andra sidan. Anta att vi säljer flera modeller av industripumpar med olika kapacitet. Försäljningstabellen till vänster visar den effekt som kunden behöver. Vi måste välja en pump med närmaste maximala eller lika effekt, men inte mindre än vad som krävs av projektet.

VLOOKUP-funktionen hjälper inte här, så du måste använda dess analoga - en massa INDEX-funktioner (INDEX) och MER EXPONERAD (MATCH):

Hitta närmaste nummer

Här fungerar MATCH-funktionen med det sista argumentet -1 i läget för att hitta det närmaste största värdet, och INDEX-funktionen extraherar sedan modellnamnet vi behöver från den intilliggande kolumnen.

Metod 4. Ny funktion VIEW (XLOOKUP)

Om du har en version av Office 365 med alla uppdateringar installerade, då istället för VLOOKUP (VISNING) du kan använda dess analoga – VIEW-funktionen (XLOOKUP), som jag redan har analyserat i detalj:

Hitta närmaste nummer

Här:

B4 – initialvärdet för kvantiteten av produkten som vi letar efter rabatt för
$G$4:$G$8 – sortimentet där vi letar efter matchningar
$H$4:$H$8 – utbudet av resultat som du vill återbetala rabatten från
fjärde argumentet (-1) inkluderar sökningen efter det närmaste minsta nummer som vi vill ha istället för en exakt matchning.

Fördelarna med denna metod är att det inte finns något behov av att sortera rabatttabellen och möjligheten att vid behov söka inte bara det närmaste minsta utan även det närmaste största värdet. Det sista argumentet i detta fall kommer att vara 1.

Men tyvärr har inte alla den här funktionen ännu - bara nöjda ägare av Office 365.

Metod 5. Power Query

Om du ännu inte är bekant med det kraftfulla och helt gratis Power Query-tillägget för Excel, då är du här. Om du redan är bekant, låt oss försöka använda den för att lösa vårt problem.

Låt oss göra lite förberedande arbete först:

Låt oss konvertera våra källtabeller till dynamiska (smarta) med ett kortkommando ctrl+T eller team Hem – Formatera som en tabell (Hem — Formatera som tabell).
För tydlighetens skull, låt oss ge dem namn. Försäljning и Rabatter fliken Konstruktör (Design).
Ladda var och en av tabellerna i tur och ordning till Power Query med knappen Från tabell/sortiment fliken Data (Data — från tabell/intervall). I de senaste versionerna av Excel har den här knappen bytt namn till Med löv (Från ark).
Om tabellerna har olika kolumnnamn med kvantiteter, som i vårt exempel ("Antal varor" och "Antal från ..."), måste de döpas om i Power Query och namnges likadant.
Efter det kan du gå tillbaka till Excel genom att välja kommandot i Power Query-redigeringsfönstret Hem — Stäng och ladda — Stäng och ladda in... (Hem — Stäng&Ladda — Stäng&Ladda till...) och sedan alternativ Skapa bara en anslutning (Skapa bara anslutning).
Sedan börjar det mest intressanta. Om du har erfarenhet av Power Query, så antar jag att den vidare tankegången bör gå i riktning mot att slå samman dessa två tabeller med en join-fråga (merge) a la VLOOKUP, som var fallet i den tidigare metoden. Faktum är att vi kommer att behöva slå samman i add-läge, vilket inte alls är uppenbart vid första anblicken. Välj i Excel-fliken Data – Hämta data – Kombinera förfrågningar – Lägg till (Data — Hämta data — Kombinera frågor — Lägg till) och sedan våra bord Försäljning и Rabatter i fönstret som visas:
Efter att ha klickat på OK våra bord kommer att limmas till en helhet – under varandra. Observera att kolumnerna med mängden varor i dessa tabeller faller under varandra, eftersom. de har samma namn:
Om den ursprungliga sekvensen av rader i försäljningstabellen är viktig för dig, så att du efter alla efterföljande transformationer kan återställa den, lägg till en numrerad kolumn i vår tabell med kommandot Lägga till en kolumn – Indexkolumn (Lägg till kolumn — Indexkolumn). Om sekvensen av rader inte spelar någon roll för dig kan du hoppa över det här steget.
Använd nu rullgardinsmenyn i rubriken i tabellen och sortera den efter kolumn KVANTITET Stigande:
Och huvudtricket: högerklicka på kolumnrubriken Rabatt välja ett lag Fyll ner (Fyll ner). Tomma celler med null automatiskt ifylld med tidigare rabattvärden:
Det återstår att återställa den ursprungliga sekvensen av rader genom att sortera efter kolumn index (du kan säkert ta bort det senare) och bli av med onödiga linjer med ett filter null efter kolumn Transaktion kod: