Keresse meg a legközelebbi egyezést az Excelben képletek használatával

Az Excel funkciók rendkívül hatékonyak lehetnek, ha megragadja a különböző képletek kombinációját. Azok a dolgok, amelyek lehetetlennek tűntek, hirtelen gyerekjátéknak tűntek.

Ilyen például, hogy megkeresi a keresési érték legközelebbi egyezését az Excel adatkészletében.

Az Excelben van néhány hasznos keresési funkció (például VLOOKUP & INDEX MATCH), amely néhány egyszerű esetben megtalálja a legközelebbi egyezést (amint azt az alábbi példákkal mutatom be).

De a legjobb az egészben, hogy ezeket a keresési funkciókat más Excel -funkciókkal kombinálhatja, hogy sokkal többet tegyen (beleértve a keresési érték legközelebbi egyezésének megtalálását a nem rendezett listában).

Ebben az oktatóanyagban megmutatom, hogyan találja meg a keresési érték legközelebbi egyezését az Excelben keresési képletekkel.

Keresse meg a legközelebbi egyezést az Excelben

Sokféle forgatókönyv létezik, ahol meg kell keresnie a legközelebbi egyezést (vagy a legközelebbi megfelelő értéket).

Az alábbiakban példákat mutatok be ebben a cikkben:

1. Keresse meg a jutalék mértékét az eladások alapján
2. Keresse meg a legjobb jelöltet (a legközelebbi tapasztalatok alapján)
3. A következő esemény időpontjának megkeresése

Kezdjük el!

Kattintson ide a mintafájl letöltéséhez

Keressen jutalékot (a legközelebbi értékesítési értéket keresi)

Tegyük fel, hogy rendelkezik az alábbi ábrán látható adatkészlettel, ahol meg szeretné találni az összes értékesítési személyzet jutalékát.

A jutalékot az értékesítési érték alapján rendelik hozzá. És ezt a jobb oldali táblázat segítségével számítják ki.

Például, ha egy értékesítő végzi el az összes 5000 -es értékesítést, akkor a jutalék 0%, és ha ő/ő az összes 15 000 -es értékesítést, akkor a jutalék 5%.

Ahhoz, hogy megkapja a jutalék mértékét, meg kell találnia a legközelebbi értékesítési tartományt, amely alacsonyabb, mint az eladási érték. Például 15000 eladási érték esetén a jutalék 10 000 (ami 5%), az eladási érték 25 000, a jutalék mértéke 20 000 (ami 7%).

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álja a VLOOKUP hozzávetőleges egyezését.

Az alábbi képlet ezt tenné:

= VLOOKUP (B2, $ E $ 2: $ F $ 6,2,1)

Ne feledje, hogy ebben a képletben az utolsó argumentum az 1, amely azt mondja, hogy a képlet hozzávetőleges keresést használjon. Ez azt jelenti, hogy a képlet végigmegy az E oszlop értékesítési értékein, és megtalálja azt az értéket, amely éppen alacsonyabb, mint a keresési érték.

Ezután a VLOOKUP képlet megadja ennek az értéknek a jutalékát.

jegyzet: Ahhoz, hogy ez működjön, az adatokat növekvő sorrendbe kell rendezni.

Kattintson ide a mintafájl letöltéséhez

Keresse meg a legjobb jelöltet (a legközelebbi tapasztalatok alapján)

A fenti példában az adatokat növekvő sorrendben kellett rendezni. De előfordulhatnak olyan esetek, amikor az adatok nincsenek rendezve.

Lássunk tehát egy példát, és nézzük meg, hogyan találhatjuk meg a legközelebbi egyezést az Excelben képletek kombinációjával.

Az alábbiakban egy mintaadat -készletet találok, ahol meg kell találnom a kívánt értékhez legközelebb eső munkatapasztalattal rendelkező munkavállaló nevét. A kívánt érték ebben az esetben 2,5 év múlva.

Vegye figyelembe, hogy az adatok nincsenek rendezve. Ezenkívül a legközelebbi élmény lehet kevesebb vagy több, mint az adandó tapasztalat. Például a 2 év és a 3 év egyaránt közel vannak (0,5 év különbség).

Az alábbi képlet megadja az eredményt:

= INDEX ($ A $ 2: $ A $ 15, MATCH (MIN (ABS (D2-B2: B15))), ABS (D2- $ B $ 2: $ B $ 15), 0))

Ennek a képletnek az a trükkje, hogy módosítsa a keresési tömböt és a keresési értéket, hogy megtalálja a minimális tapasztalati különbséget a szükséges és a tényleges értékekben.

Először is értsük meg, hogyan tenné ezt manuálisan (és akkor elmagyarázom, hogyan működik ez a képlet).

Ha ezt manuálisan végzi, akkor végigmegy a B oszlop minden celláján, és megtalálja a különbséget a szükséges és az adott személy közötti tapasztalat között. Ha megvan az összes különbség, megtalálja a minimálisat, és lehívja az adott személy nevét.

Ezzel a formulával pontosan ezt tesszük.

Hadd magyarázzam.

A MATCH képlet keresési értéke MIN (ABS (D2-B2: B15)).

Ez a rész megadja a minimális különbséget az adott tapasztalat (ami 2,5 év) és az összes többi tapasztalat között. Ebben a példában 0,3 -at ad vissza

Ne feledje, hogy az ABS -t használtam annak biztosítására, hogy a legközelebbit keresem (ami lehet több vagy kevesebb, mint az adott tapasztalat).

Most ez a minimális érték lesz a keresési értékünk.

A MATCH funkció keresési tömbje ABS (D2- $ B $ 2: $ B $ 15).

Ezzel számokból álló tömböt kapunk, amelyekből 2,5 (a szükséges tapasztalat) kivonásra került.

Tehát most van egy keresési érték (0,3) és egy keresési tömb ({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 megtalálja a 0.3 pozíciót ebben a tömbben, ami egyben a legközelebbi tapasztalattal rendelkező személy neve is.

Ezt a pozíciószámot az INDEX függvény használja a személy nevének visszaadására.

Megjegyzés: Abban az esetben, ha több, azonos gyakorlattal rendelkező jelölt van, a fenti képlet megadja az első megfelelő alkalmazott nevét.

Keresse meg a következő esemény dátumát

Ez egy másik példa, ahol a keresési képletek segítségével megkeresheti az esemény következő dátumát az aktuális dátum alapján.

Az alábbiakban az az adatkészlet található, ahol az események nevei és az események dátumai vannak.

Szeretném a következő esemény nevét és a közelgő esemény dátumát.

Az alábbi képlet megadja a közelgő esemény nevét:

= INDEX ($ A $ 2: $ A $ 11, MATCH (E1, $ B $ 2: $ B $ 11,1) +1)

És az alábbi képlet megadja a közelgő esemény dátumát:

= INDEX ($ B $ 2: $ B $ 11, MATCH (E1, $ B $ 2: $ B $ 11,1) +1)

Hadd magyarázzam meg, hogyan működik ez a képlet.

Az esemény dátumának lekéréséhez a MATCH függvény az aktuális dátumot keresi a B oszlopban. Ebben az esetben nem pontos egyezést keresünk, hanem hozzávetőlegeset. Ezért a MATCH függvény utolsó argumentuma az 1 (amely megtalálja a legnagyobb értéket, amely kisebb vagy egyenlő a keresési értékkel).

Tehát a MATCH függvény visszaadja annak a cellának a pozícióját, amelynek dátuma éppen kisebb vagy egyenlő az aktuális dátummal. Tehát a következő esemény ebben az esetben a következő cellában lenne (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 cellahelyhez, és ez megadja a következő esemény dátumának cellapozícióját.

Ezt az értéket az INDEX függvény adja meg.

Az eseménynév lekéréséhez ugyanazt a képletet kell használni, és az INDEX függvény tartománya a B oszlopból A oszlopba változik.

Kattintson ide a mintafájl letöltéséhez

A példa ötlete akkor merült fel bennem, amikor egy barátom megkeresett. Egy oszlopban felsorolta az összes barátja/rokona születésnapját, és tudni akarta a következő születésnapot (és a személy nevét).

Ez a három példa bemutatja, hogyan lehet megkeresni a legközelebbi megfelelő értéket az Excelben a keresési képletek segítségével.

A következő Excel tippek/oktatóanyagok is tetszhetnek

• Szerezze be az utolsó számot egy listából a VLOOKUP funkció használatával.
• Több keresési értéket kaphat ismétlés nélkül egyetlen cellában.
• VLOOKUP vs. INDEX/MATCH
• Excel INDEX MATCH
• Keresse meg a keresés utolsó előfordulását Értékeljen egy listát az Excelben
• Ismétlődések keresése és eltávolítása az Excelben
• Feltételes formázás.