Ebben az oktatóanyagban megtudhatja, hogyan találhatja meg az elem utolsó előfordulását a listában Excel képletek segítségével.
Nemrég azon dolgoztam, hogy meghatározzam a találkozó napirendjét.
Volt egy listám az Excelben, ahol volt egy listám az emberekről és azokról a dátumokról, amikor „ülésvezetőként” tevékenykedtek.
Mivel a listában ismétlés volt (ami azt jelenti, hogy egy személy többször ülést tartott), azt is tudnom kellett, hogy mikor járt el utoljára valaki „ülésvezetőként”.
Ennek oka az volt, hogy gondoskodnom kellett arról, hogy valakit, aki nemrég elnökölt, ne rendeljenek újra.
Ezért úgy döntöttem, hogy valamilyen Excel Function varázslatot használok ennek megvalósításához.
Az alábbiakban látható a végeredmény, ahol kiválaszthatok egy nevet a legördülő menüből, és megadja a név utolsó előfordulásának dátumát a listában.
Ha jól ismeri az Excel funkcióit, akkor tudhatja, hogy egyetlen Excel funkció sem képes erre.
De Ön a Formula Hack szekcióban van, és itt megtörténik a varázslat.
Ebben az oktatóanyagban ennek három módját mutatom be.
Keresse meg az utolsó előfordulást - A MAX funkció használatával
Ennek a technikának köszönhető az Excel MVP Charley Kyd cikke.
Itt található az Excel képlet, amely a lista utolsó értékét adja vissza:
= INDEX ($ B $ 2: $ B $ 14, SUMPRODUCT (MAX (SOR ($ A $ 2: $ A $ 14)*($ D $ 3 = $ A $ 2: $ A $ 14))-1))
Ez a képlet így működik:
- A MAX függvény a vezetéknév sorszámának megkeresésére szolgál. Például, ha a név Glen, akkor 11 -et ad vissza, ahogy a 11. sorban is szerepel. Mivel a listánk a második sortól kezdődik, 1 -et kivonunk. Tehát a Glen utolsó előfordulásának helyzete a listánkon 10.
- A SUMPRODUCT biztosítja, hogy ne kelljen a Control + Shift + Enter billentyűkombinációt használni, mivel a SUMPRODUCT képes kezelni a tömbképleteket.
- Az INDEX függvény most megkeresi a vezetéknév dátumát.
Keresse meg az utolsó előfordulást - A KERESÉS funkció használatával
Itt van egy másik képlet ugyanazon feladat elvégzésére:
= KERESÉS (2,1/($ A $ 2: $ A $ 14 = $ D $ 3), $ B $ 2: $ B $ 14)
Ez a képlet így működik:
- A keresési érték 2 (meglátod, miért… olvass tovább)
- A keresési tartomány 1/($ A $ 2: $ A $ 14 = $ D $ 3) - Ez 1 -et ad vissza, ha megtalálja a megfelelő nevet, és hibát, ha nem. Tehát végül tömböt kap. Például a keresési érték Glen, a tömb {#DIV/0!;#DIV/0!; 1;#DIV/0!;#DIV/0!;#DIV/0!;#DIV/ 0!;#DIV/0!;#DIV/0!; 1;#DIV/0!;#DIV/0!;#DIV/0!}.
- A harmadik érv ([eredmény_vektor]) az a tartomány, amelytől az eredményt adja, amelyek ebben az esetben dátumok.
Ennek a képletnek az az oka, hogy a LOOKUP függvény a hozzávetőleges egyezési technikát használja. Ez azt jelenti, hogy ha megtalálja a pontos egyező értéket, akkor ezt visszaadja, de ha nem, akkor a teljes tömböt a végéig szkenneli, és a következő legnagyobb értéket adja vissza, amely alacsonyabb, mint a keresési érték.
Ebben az esetben a keresési érték 2, és tömbünkben csak 1 -et vagy hibát kapunk. Tehát beolvassa a teljes tömböt, és visszaadja az utolsó 1 pozícióját - ez a név utolsó egyező értéke.
Az utolsó előfordulás megtalálása - egyéni funkció (VBA) használata
Hadd mutassam meg ennek egy másik módját is.
A VBA segítségével létrehozhatunk egy egyéni funkciót (más néven felhasználó által definiált funkciót).
Az egyéni funkció létrehozásának előnye, hogy könnyen használható. Nem kell aggódnia minden alkalommal egy összetett képlet megalkotása miatt, mivel a legtöbb munka a VBA háttérben történik.
Létrehoztam egy egyszerű képletet (ami nagyon hasonlít a VLOOKUP képletre).
Egyéni funkció létrehozásához rendelkeznie kell a VBA kóddal a VB szerkesztőben. Megadom a kódot és a lépéseket a VB szerkesztőben való elhelyezéshez, de először hadd mutassam meg, hogyan működik:
Ez a képlet adja meg az eredményt:
= LastItemLookup ($ D $ 3, $ A $ 2: $ B $ 14,2)
A képlet három érvet tartalmaz:
- Keresési érték (ez lenne a név a D3 cellában)
- Keresési tartomány (ez lenne a név és dátumtartomány - A2: B14)
- Oszlopszám (ez az oszlop, amelyből az eredményt szeretnénk)
Miután létrehozta a képletet, és betette a kódot a VB Editorba, ugyanúgy használhatja, mint bármely más hagyományos Excel munkalap -funkciót.
Itt van a képlet kódja:
'Ez egy függvény kódja, amely megkeresi a keresési érték utolsó előfordulását, és a megfelelő értéket adja vissza a megadott oszlopból.' Sumit Bansal által létrehozott kód (https://trumpexcel.com) Funkció LastItemLookup (Lookupvalue as String, LookupRange Tartomány, Oszlopszám mint egész) Dim i Amig For i = LookupRange.Oszlopok (1). Cellák. Számlálás az 1. lépéshez -1 If Lookupvalue = LookupRange.Cells (i, 1) Majd LastItemLookup = LookupRange.Cells (i, ColumnNumber) Kilépés a funkció befejezéséből Ha a következő i vége funkció
Az alábbi lépésekkel helyezze el ezt a kódot a VB szerkesztőben:
- Lépjen a Fejlesztő fülre.
- Kattintson a Visual Basic lehetőségre. Ez megnyitja a VB szerkesztőt a háttérben.
- A VB szerkesztő Project Explorer ablaktáblájában kattintson jobb gombbal a munkafüzet bármely olyan objektumára, amelybe be szeretné illeszteni a kódot. Ha nem látja a Project Explorer programot, lépjen a Nézet fülre, és kattintson a Project Explorer elemre.
- Lépjen a Beszúrás elemre, és kattintson a Modul elemre. Ezzel beszúr egy modulobjektumot a munkafüzetébe.
- Másolja ki és illessze be a kódot a modul ablakába.
Most a képlet elérhető lenne a munkafüzet összes munkalapján.
Ne feledje, hogy a munkafüzetet .XLSM formátumban kell mentenie, mivel makró van benne. Továbbá, ha azt szeretné, hogy ez a képlet az összes használt munkafüzetben elérhető legyen, vagy mentheti a személyes makró munkafüzetbe, vagy létrehozhat belőle egy bővítményt.
