Fannt déi nootste Nummer - Gesond Iessen bei mir

Inhalter

An der Praxis sinn et dacks Fäll, wou Dir an ech de nootste Wäert an engem Set (Tabelle) a Relatioun zu enger bestëmmter Zuel musse fannen. Et kéint zum Beispill sinn:

Berechnung vun Remise je Volume.
Berechnung vun der Quantitéit vun Bonus ofhängeg vun der Ëmsetzung vum Plang.
Berechnung vun Schëffer Tariffer ofhängeg vun der Distanz.
Auswiel vun passenden Container fir Wueren, etc.

Ausserdeem kann d'Ronnung souwuel no uewen wéi no ënnen erfuerderlech sinn, ofhängeg vun der Situatioun.

Et gi verschidde Weeër - evident an net sou evident - fir sou e Problem ze léisen. Loosst eis se sequentiell kucken.

Fir unzefänken, loosst eis e Fournisseur virstellen deen Remise op Grousshandel gëtt, an de Prozentsaz vun der Remise hänkt vun der Quantitéit u kaafte Wueren of. Zum Beispill, wann Dir méi wéi 5 Stécker kaaft, gëtt eng Remise vun 2% kritt, a wann Dir vun 20 Stéck kaaft - scho 6%, etc.

Wéi séier a schéin de Remise Prozentsaz ze berechnen wann Dir d'Quantitéit vun de kaaft Wueren aginn?

Method 1: Nested IFs

Eng Method aus der Serie "Wat ass do ze denken - Dir musst sprangen!". Mat nestéierte Funktiounen IF (WANN) fir sequenziell ze kontrolléieren ob den Zellwäert an jiddereng vun den Intervalle fällt an eng Remise fir de entspriechende Beräich ze weisen. Awer d'Formel an dësem Fall ka ganz ëmständlech sinn:

Fannen déi noosten Zuel

Ech mengen et ass evident datt esou eng "Monsterpopp" debuggen oder no enger Zäit e puer nei Konditiounen derbäi setzen ass Spaass.

Zousätzlech huet Microsoft Excel eng Nistlimit fir d'IF Funktioun - 7 Mol an eeler Versiounen a 64 Mol an méi nei Versiounen. Wat wann Dir méi braucht?

Method 2. VLOOKUP mat Intervall Vue

Dës Method ass vill méi kompakt. Fir de Remise Prozentsaz ze berechnen, benotzt d'legendär Funktioun VPR (VLOOKUP) am geschätzte Sichmodus:

Fannen déi noosten Zuel

wou

B4 – de Wäert vun der Quantitéit vu Wueren an der éischter Transaktioun fir déi mir no enger Remise sichen
$G$4:$H$8 - e Link op d'Reduktiounstabell - ouni "Header" a mat den Adressen fixéiert mam $ Zeechen.
2 - d'Uerdnungsnummer vun der Kolonn an der Rabatttabell, aus där mir de Remisewäert kréien
Richteg – hei läit den „Mupp“ begruewen. Wann als lescht Funktioun Argument VPR uginn LEIEN (FALSCH) oder 0, da wäert d'Funktioun sichen strikt Match an der Quantitéit Kolonn (an an eisem Fall gëtt et e #N / A Feeler, well et kee Wäert 49 an der Remise Dësch ass). Mä wann amplaz LEIEN schreiwen Richteg (WOUER) oder 1, da wäert d'Funktioun net no der genau kucken, awer noosten klengste Wäert a gëtt eis de Prozentsaz vun Remise mir brauchen.

Den Nodeel vun dëser Method ass d'Noutwendegkeet fir d'Reduktiounstabell an opsteigend Uerdnung no der éischter Kolonn ze sortéieren. Wann et keng esou Sortéierung gëtt (oder et gëtt an ëmgedréint Uerdnung gemaach), da funktionnéiert eis Formel net:

Fannen déi noosten Zuel

Deementspriechend kann dës Approche nëmme benotzt ginn fir de nooste klengste Wäert ze fannen. Wann Dir déi nooste gréisste muss fannen, da musst Dir eng aner Approche benotzen.

Method 3. Fannen déi noosten gréisste benotzt INDEX an MATCH Funktiounen

Loosst eis elo eise Problem vun der anerer Säit kucken. Ugeholl mir verkafen verschidde Modeller vun industrielle Pompelen vu verschiddene Kapazitéiten. D'Verkafstabell op der lénker Säit weist d'Kraaft déi vum Client erfuerderlech ass. Mir mussen eng Pompel vun der nooste maximaler oder gläicher Kraaft auswielen, awer net manner wéi dat wat vum Projet erfuerderlech ass.

D'VLOOKUP Funktioun hëlleft hei net, also musst Dir säin Analog benotzen - eng Rëtsch INDEX Funktiounen (INDEX) a MÉI ausgesat (MATCH):

Fannen déi noosten Zuel

Hei funktionnéiert d'MATCH Funktioun mam leschten Argument -1 am Modus fir den nootste gréisste Wäert ze fannen, an d'INDEX Funktioun extrahéiert dann de Modellnumm dee mir brauchen aus der niewender Kolonn.

Method 4. Nei Funktioun VIEW (XLOOKUP)

Wann Dir eng Versioun vum Office 365 hutt mat all Updates installéiert, dann amplaz VLOOKUP (VLOOKUP) Dir kënnt säin Analog benotzen - d'VIEW Funktioun (XLOOKUP), déi ech schonn am Detail analyséiert hunn:

Fannen déi noosten Zuel

Hei:

B4 - den initialen Wäert vun der Quantitéit vum Produkt fir deen mir no enger Remise sichen
$G$4:$G$8 - der Gamme wou mir fir Mätscher sichen
$H$4:$H$8 - d'Gamme vu Resultater aus deenen Dir de Remise zréck wëllt
véiert Argument (-1) enthält d'Sich no der nooste klengster Zuel déi mir wëllen amplaz vun engem genaue Match.

D'Virdeeler vun dëser Methode sinn datt et net néideg ass d'Reduktiounstabell ze sortéieren an d'Fäegkeet ze sichen, wann néideg, net nëmmen de nooste klengste, awer och de nooste gréisste Wäert. Dat lescht Argument an dësem Fall wäert den 1.

Awer leider huet net jiddereen dës Feature nach - nëmme glécklech Besëtzer vum Office 365.

Method 5. Power Query

Wann Dir nach net mat dem mächtegen a komplett gratis Power Query Add-in fir Excel vertraut sidd, da sidd Dir hei. Wann Dir scho vertraut sidd, da loosst eis probéieren et ze benotzen fir eise Problem ze léisen.

Loosst eis als éischt e puer Virbereedungsaarbechte maachen:

Loosst eis eis Quelltabellen op dynamesch (Smart) konvertéieren mat enger Tastatur Ofkiirzung Ctrl+T oder Team Home - Format als Dësch (Home - Format als Table).
Fir Kloerheet, loosst eis hinnen Nimm ginn. Sales и Reduktiounen Reiter Builder (Design).
Lued jiddereng vun den Dëscher am Tour an Power Query mat de Knäppchen Vun Dësch / Gamme Reiter Daten (Daten - Vun Dësch / Gamme). An de leschte Versioune vun Excel ass dëse Knäppchen ëmbenannt ginn Mat Blieder (vum Blat).
Wann d'Dëscher verschidde Kolonnennimm mat Quantitéiten hunn, wéi an eisem Beispill ("Quantitéit vu Wueren" an "Quantitéit vu ..."), da musse se an der Power Query ëmbenannt ginn an d'selwecht benannt ginn.
Duerno kënnt Dir zréck op Excel zréckkommen andeems Dir de Kommando an der Power Query Editor Fënster auswielt Home - Zoumaachen a Lueden - Zoumaachen a Lueden an ... (Home — Close&Load — Close&load to...) an dann Optioun Schafen einfach eng Verbindung (Erstellt nëmmen eng Verbindung).
Da fänkt déi interessantst un. Wann Dir Erfahrung an Power Query hutt, dann huelen ech un datt déi weider Gedankelinn an d'Richtung sollt sinn fir dës zwee Dëscher ze fusionéieren mat enger Joint Query (fusionéieren) a la VLOOKUP, sou wéi et an der viregter Method de Fall war. Tatsächlech musse mir am Add-Modus fusionéieren, wat op den éischte Bléck guer net offensichtlech ass. Wielt an Excel Tab Daten - Kritt Daten - Kombinéiert Ufroen - Füügt (Daten - Daten kréien - Ufroen kombinéieren - Anhang) an dann eis Dëscher Sales и Reduktiounen an der Fënster déi erschéngt:
Nodeems Dir op OK eis Dëscher ginn an een eenzegt Ganzt gepecht - ënnereneen. Weg drun, datt d'Saile mat der Quantitéit vun Wueren an dësen Dëscher ënnert all aner gefall, well. si hunn de selwechten Numm:
Wann déi ursprénglech Sequenz vun de Reihen an der Verkafstabelle fir Iech wichteg ass, da fir datt Dir no all spéider Transformatiounen et dann restauréiere kënnt, füügt eng nummeréiert Kolonn un eis Tabelle mat dem Kommando Eng Kolonn derbäisetzen - Index Kolonn (Säit Kolonn - Index Kolonn). Wann d'Sequenz vun de Linnen Iech net wichteg ass, da kënnt Dir dëse Schrëtt iwwersprangen.
Elo, mat der Dropdown-Lëscht am Header vun der Tabell, sortéiert se no Kolonn Quantitéit Opsteigend:
An den Haapttrick: Riets-klickt op de Kolonn Header Discount wielt eng Equipe Fëllt - Down (Fëllt - Down). Eidel Zellen mat wäert automatesch mat de fréiere Remisewäerter ausgefëllt:
Et bleift fir d'ursprénglech Sequenz vun de Reihen ze restauréieren andeems Dir no Kolonn sortéiert Index (Dir kënnt et méi spéit sécher läschen) an onnéideg Linnen mat engem Filter lass wäert duerch Kolonn Transaktioun Code: