Az Excel-függvények nagyon hatékonyak lehetnek, ha megszokja a különböző képletek kombinálását.Ami egykor lehetetlennek tűnt, az hirtelen gyerekjátékká válhat.
Ilyen például a legközelebbi érték megtalálása egy Excel-adatkészletben.
Az Excelben számos hasznos keresőfunkció található (például VLOOKUP és INDEX MATCH), amelyek megtalálják a legközelebbi值(ahogy az alábbi példán látható).
De a legjobb az egészben az, hogy ezeket a keresési függvényeket kombinálhatja más Excel-függvényekkel, így sokkal többet érhet el (beleértve a keresési értékhez legközelebbi egyezés megtalálását egy rendezetlen listában).
Ebben az oktatóanyagban bemutatom, hogyan használhatja a keresési képletet az Excelben szereplő keresési értékhez legközelebbi egyezés megtalálásához.
Számos különböző forgatókönyv létezik, amikor meg kell találnia a legközelebbi egyezést (vagy a legközelebbi egyezési értéket).
Íme egy példa, amellyel ebben a cikkben foglalkozom:
- Keressen jutalékokat az eladások alapján
- Találja meg a legjobb jelöltet (a legközelebbi tapasztalat alapján)
- Keresse meg a következő esemény dátumát
Kezdjük!
tartalom
Jutalékkulcsok keresése (keresse a legközelebbi eladási értéket)
Tegyük fel, hogy az alábbihoz hasonló adatkészlettel rendelkezik, ahol meg szeretné keresni az összes értékesítő jutalékkulcsát.
A jutalékokat az eladás értéke alapján osztják fel.Ezt a jobb oldali táblázat segítségével számítjuk ki.
Például, ha az értékesítő összértékesítése 5000, akkor a jutalék 0%, ha pedig az összes eladása 15000, akkor a jutalék 5%.
A jutalék mértékének megszerzéséhez meg kell találnia a legközelebbi értékesítési tartományt, amely éppen az értékesítési érték alatt van.Például 15000 10,000 eladás esetén a jutalék 5 25000 (vagy 20,000%), 7 XNUMX eladás esetén pedig XNUMX XNUMX (vagy XNUMX%).
A legközelebbi eladási érték megtalálásához és a jutalék mértékének megállapításához használhat hozzávetőleges egyezést a VLOOKUP-ban.
A következő képlet képes erre:
= VLOOKUP (B2, $ E $ 2: $ F $ 6,2,1)
Vegye figyelembe, hogy ebben a képletben az utolsó paraméter 1, ami azt utasítja a képletnek, hogy közelítő keresést használjon.Ez azt jelenti, hogy a képlet végigmegy az E oszlopban található eladási értéken, és közvetlenül a keresési érték alatt találja meg az értéket.
A VLOOKUP képlet ezután megadja a jutalék mértékét az adott értékhez.
注意: Ehhez az adatokat növekvő sorrendbe kell rendezni.
Találja meg a legjobb jelöltet (a legközelebbi tapasztalat alapján)
A fenti példában az adatokat növekvő sorrendbe kell rendezni.De előfordulhatnak olyan esetek, amikor az adatok nincsenek rendezve.
Tehát vegyünk egy példát, és nézzük meg, hogyan használhatjuk képletkombinációkat a legközelebbi egyezés megtalálásához az Excelben.
Az alábbiakban egy példaadatkészlet található, ahol meg kell találnom azon alkalmazottak nevét, akiknek a munkatapasztalata a legközelebb áll a kívánt értékhez.A várható érték ebben az esetben 2.5 év.
Vegye figyelembe, hogy az adatok nincsenek rendezve.Ezenkívül a legközelebbi élmények kevesebbek vagy többek lehetnek, mint egy adott élmény.Például 2 év és 3 év egyformán közel van (0.5 év különbség).
Íme a képlet, amely megadja az eredményt:
=INDEX($A$2:$A$15,MATCH(MIN(ABS(D2-B2:B15)),ABS(D2-$B$2:$B$15),0))
Ebben a képletben a trükk az, hogy módosítsa a keresési tömböt és a keresési értékeket, hogy megtalálja a legkisebb tapasztalati különbséget a kívánt és a tényleges értékek között.
Először értsük meg, hogyan kell ezt manuálisan megtenni (majd elmagyarázom, hogyan működik ez a képlet).
Ha ezt manuálisan teszi, akkor végig kell mennie a B oszlop minden celláján, és meg kell találnia a különbséget a kívánt élmény és a meglévő tapasztalat között.Ha minden különbség megvan, megtalálja a legkisebbet, és megkapja a személy nevét.
Ezzel a képlettel pontosan ezt tesszük.
Hadd magyarázzam.
A MATCH képletben a keresési érték MIN(ABS(D2-B2:B15)).
Ez a rész megadja a minimális különbséget az adott tapasztalat (azaz 2.5 év) és az összes többi tapasztalat között.Ebben az esetben 0.3-at ad vissza
Megjegyzendő, hogy ABS-t használok, hogy megbizonyosodjak arról, hogy a legközelebbit keresem (valószínűleg többet vagy kevesebbet, mint az adott tapasztalat).
Most ez a minimális érték lesz a keresési értékünk.
A MATCH függvény keresési tömbje ABS(D2-$B$2:$B$15).
Ez egy olyan számtömböt ad, amelyből kivonjuk a 2.5-öt (tapasztalat szükséges).
所以現在我們有一個查找值 (0.3) 和一個查找數組 ({6.8;0.8;19.5;21.8;14.5;11.2;0.3;9.2;2;9.8;14.8;0.4;23.8;2.9})
A MATCH függvény ebben a tömbben a 0.3-as pozíciót találja meg, amely a legközelebbi tapasztalattal rendelkező személy nevének pozíciója.
Az INDEX funkció ezután a munkaszámot használja a személy nevének visszaadásához.
Megjegyzés: Ha több jelölt is van azonos minimális tapasztalattal, a fenti képlet az első megfelelő alkalmazott nevét adja meg.
Keresse meg a következő esemény dátumát
Íme egy másik példa, ahol egy keresési képlet segítségével megkeresheti egy esemény következő dátumát az aktuális dátum alapján.
Az alábbiakban látható az az adatkészlet, amelyben az események nevei és dátumai vannak.
Amit szeretnék, az a következő esemény neve és a közelgő esemény dátuma.
Íme a képlet, amely megadja a közelgő esemény nevét:
=INDEX($A$2:$A$11,MATCH(E1,$B$2:$B$11,1)+1)
A következő képlet adja meg a közelgő események dátumait:
=INDEX($B$2:$B$11,MATCH(E1,$B$2:$B$11,1)+1)
Hadd magyarázzam el, hogyan működik ez a képlet.
Az esemény dátumának lekéréséhez a MATCH függvény a B oszlopban az aktuális dátumot keresi.Ebben az esetben nem pontos, hanem hozzávetőleges egyezést keresünk.Ezért a MATCH függvény utolsó paramétere 1 (a keresési értéknél kisebb vagy azzal egyenlő legnagyobb értéket találja).
Tehát a MATCH függvény annak a cellának a pozícióját adja vissza, amelynek a dátuma kisebb vagy egyenlő, mint az aktuális dátum.Tehát ebben az esetben a következő esemény a következő cellában lesz (mivel a lista növekvő sorrendben van rendezve).
Tehát a közelgő esemény dátumának megtekintéséhez csak adjon hozzá egyet a MATCH függvény által visszaadott cellapozícióhoz, és ez megadja a következő esemény dátumának cellapozícióját.
Ezt az értéket ezután az INDEX függvény adja meg.
Az esemény nevének lekéréséhez használja ugyanazt a képletet, és módosítsa az INDEX függvény tartományát B oszlopról A oszlopra.
Ez a példa akkor jutott eszembe, amikor egy barátom kért.Egy oszlopban felsorolja az összes barátja/rokonának születésnapját, és tudni akarja, hogy közeleg a következő születésnap (és az illető neve).
A fenti három példa bemutatja, hogyan használhatja a keresési képletet a legközelebbi érték megtalálásához az Excelben.
