tartalom
A gyakorlatban nagyon gyakran vannak olyan esetek, amikor Önnek és nekem meg kell találnunk a legközelebbi értéket egy halmazban (táblázatban) egy adott számhoz képest. Ez lehet például:
- Kedvezmény számítása mennyiségtől függően.
- A bónuszok összegének kiszámítása a terv végrehajtásától függően.
- Szállítási díjak számítása a távolságtól függően.
- Az áruknak megfelelő konténerek kiválasztása stb.
Ezenkívül a helyzettől függően felfelé és lefelé kerekítésre is szükség lehet.
Egy ilyen probléma megoldására többféle – nyilvánvaló és kevésbé nyilvánvaló – mód is létezik. Nézzük őket egymás után.
Először képzeljünk el egy beszállítót, aki nagykereskedelmi árengedményt ad, és a kedvezmény százaléka a vásárolt áruk mennyiségétől függ. Például 5 db-nál több vásárlás esetén 2% kedvezmény jár, 20 db-tól pedig már 6% stb.
Hogyan lehet gyorsan és szépen kiszámolni a kedvezmény százalékát a vásárolt áru mennyiségének megadásakor?
1. módszer: Beágyazott IF-ek
Egy módszer a „mit kell gondolni – ugrani kell!” sorozatból. Beágyazott függvények használata IF (HA) hogy szekvenciálisan ellenőrizze, hogy a cellaérték beleesik-e az egyes intervallumokba, és megjelenítse a megfelelő tartományra vonatkozó engedményt. De a képlet ebben az esetben nagyon nehézkesnek bizonyulhat:
Azt hiszem, nyilvánvaló, hogy egy ilyen „szörnybaba” hibakeresése, vagy egy-két új feltétel hozzáadása egy idő után szórakoztató.
Ezenkívül a Microsoft Excel beágyazási korláttal rendelkezik az IF funkcióhoz – a régebbi verziókban 7-szer, az újabb verziókban pedig 64-szer. Mi van, ha többre van szüksége?
2. módszer. VLOOKUP intervallumnézettel
Ez a módszer sokkal kompaktabb. A kedvezmény százalékának kiszámításához használja a legendás függvényt VPR (VLOOKUP) hozzávetőleges keresési módban:
ahol
- B4 – az első tranzakcióban szereplő árumennyiség értéke, amelyre kedvezményt keresünk
- 4 USD: 8 USD – hivatkozás a kedvezménytáblázatra – „fejléc” nélkül, és a címek $ jellel rögzítve.
- 2 — a diszkonttáblázat azon oszlopának sorszáma, amelyből a diszkontértéket szeretnénk megkapni
- TRUE – ide van elásva a „kutya”. Ha a függvény utolsó argumentumaként VPR meghatározza FEKVŐ (HAMIS) vagy 0, akkor a függvény megkeresi szigorú egyezés mennyiség oszlopban (és esetünkben #N/A hibát ad, mivel a diszkonttáblázatban nincs 49-es érték). De ha ahelyett FEKVŐ ír TRUE (IGAZ) vagy 1, akkor a függvény nem a pontos, hanem legközelebbi legkisebb értékét, és megadja nekünk a szükséges százalékos kedvezményt.
Ennek a módszernek a hátránya, hogy a diszkonttáblázatot az első oszlop szerint növekvő sorrendbe kell rendezni. Ha nincs ilyen rendezés (vagy fordított sorrendben történik), akkor a képletünk nem fog működni:
Ennek megfelelően ez a megközelítés csak a legközelebbi legkisebb érték meghatározására használható. Ha meg kell találnia a legközelebbi legnagyobbat, akkor más megközelítést kell alkalmaznia.
3. módszer. A legközelebbi legnagyobb megkeresése az INDEX és a MATCH függvények segítségével
Most nézzük a problémánkat a másik oldalról. Tegyük fel, hogy több, különböző teljesítményű ipari szivattyúmodellt árulunk. A bal oldali értékesítési táblázat mutatja a vevő által igényelt teljesítményt. Ki kell választanunk a legközelebbi maximális vagy azonos teljesítményű szivattyút, de nem kevesebbet, mint amit a projekt megkövetel.
A VLOOKUP funkció itt nem segít, ezért az analógját kell használnia - egy csomó INDEX függvény (INDEX) és KIÉPÍTETTebb (MÉRKŐZÉS):
Itt a MATCH függvény az utolsó -1 argumentummal a legközelebbi legnagyobb érték megtalálásának módjában működik, majd az INDEX függvény a szomszédos oszlopból kivonja a szükséges modellnevet.
4. módszer: Új funkció VIEW (XLOOKUP)
Ha az Office 365 olyan verziójával rendelkezik, amelyen minden frissítés telepítve van, akkor a VLOOKUP helyett (VLOOKUP) használhatja analógját – a VIEW funkciót (XLOOKUP), amelyet már részletesen elemeztem:
Itt:
- B4 – a termék mennyiségének kezdeti értéke, amelyre kedvezményt keresünk
- 4 G$: 8 G$ – a tartomány, ahol gyufát keresünk
- 4 USD: 8 USD – az eredmények köre, amelyből a kedvezményt vissza kívánja adni
- negyedik érv (-1) tartalmazza a pontos egyezés helyett a legközelebbi legkisebb szám keresését.
Ennek a módszernek az az előnye, hogy nincs szükség a diszkonttáblázat rendezésére, és szükség esetén nem csak a legközelebbi legkisebb, hanem a legközelebbi legnagyobb érték is megkereshető. Az utolsó argumentum ebben az esetben az 1.
De sajnos még nem mindenki rendelkezik ezzel a funkcióval – csak az Office 365 boldog tulajdonosai.
5. módszer: Power Query
Ha még nem ismeri a hatékony és teljesen ingyenes Power Query bővítményt az Excelhez, akkor itt van. Ha már ismerős, akkor próbáljuk meg használni a problémánk megoldására.
Először végezzünk néhány előkészítő munkát:
- Alakítsuk át forrástábláinkat dinamikussá (intelligenssé) egy billentyűparancs segítségével Ctrl+T vagy csapat Kezdőlap – Táblázat formázása (Főoldal — Táblázat formázása).
- Az érthetőség kedvéért adjunk nevet nekik. Értékesítés и Kedvezmények lap építész (Tervezés).
- A gombbal töltse be az egyes táblákat a Power Querybe Táblázatból/Tartományból lap dátum (Adatok – táblázatból/tartományból). Az Excel legutóbbi verzióiban ezt a gombot átnevezték erre Levélekkel (lapról).
- Ha a tábláknak különböző oszlopnevük van a mennyiségekkel, mint a példánkban („áruk mennyisége” és „mennyiség innen…”), akkor át kell nevezni őket a Power Queryben, és ugyanazt kell elnevezni.
- Ezt követően a Power Query szerkesztő ablakában a parancs kiválasztásával visszatérhet az Excelhez Kezdőlap — Bezárás és betöltés — Bezárás és betöltés… (Kezdőlap – Bezárás&Betöltés – Bezárás&Betöltés…) majd opció Csak hozzon létre egy kapcsolatot (Csak kapcsolat létrehozása).
- Aztán kezdődik a legérdekesebb. Ha van tapasztalatod a Power Query-ben, akkor feltételezem, hogy a további gondolatmenet a két tábla összevonása egy join lekérdezéssel (merge) a la VLOOKUP-pal kell, hogy legyen, ahogy az előző módszernél is történt. Valójában hozzáadás módban kell egyesülnünk, ami első pillantásra egyáltalán nem nyilvánvaló. Válassza ki az Excel lapon Adatok – Adatok lekérése – Kérelmek egyesítése – Hozzáadás (Adatok – Adatok lekérése – Lekérdezések kombinálása – Hozzáfűzés) majd az asztalainkat Értékesítés и Kedvezmények a megjelenő ablakban:
- Miután rákattintott OK asztalaink egyetlen egésszé lesznek ragasztva – egymás alá. Felhívjuk figyelmét, hogy ezekben a táblázatokban az áruk mennyiségét tartalmazó oszlopok egymás alá kerültek, mert. ugyanaz a nevük:
- Ha az értékesítési tábla eredeti sorsorrendje fontos Önnek, akkor annak érdekében, hogy az összes későbbi átalakítás után vissza tudja állítani, adjon hozzá egy számozott oszlopot a táblázatunkhoz a paranccsal Oszlop hozzáadása – Index oszlop (Oszlop hozzáadása — Index oszlop). Ha a sorok sorrendje nem számít Önnek, akkor ezt a lépést kihagyhatja.
- Most a táblázat fejlécében található legördülő lista segítségével rendezze oszlopok szerint Mennyiség Növekvő:
- És a fő trükk: kattintson a jobb gombbal az oszlop fejlécére Kedvezmény válassz csapatot Kitöltés – Le (Kitöltés - Le). Ürítse ki a cellákat ezzel null automatikusan kitöltve a korábbi kedvezményértékekkel:
- Marad a sorok eredeti sorrendjének visszaállítása oszlopok szerinti rendezéssel index (később nyugodtan törölheted) és szűrővel szabadulj meg a felesleges soroktól null oszlop szerint Tranzakciós kód:
- A VLOOKUP funkció használata adatok keresésére és lekérdezésére
- A VLOOKUP (VLOOKUP) használata megkülönbözteti a kis- és nagybetűket
- XNUMXD VLOOKUP (VLOOKUP)