Összegzés: ebből az oktatóanyagból megtudhatja, hogyan kell Excel-képletet használni egy elem utolsó előfordulásának megkeresésére a listában.
Nemrég az ülés napirendjén dolgoztam.
Van egy listám az Excelben, amely tartalmazza az emberek listáját és az időpontokat, amikor „találkozó elnökök” voltak.
Azt is tudnom kell, hogy egy személy mikor volt utoljára „ülésvezető” a lista ismétlődése miatt (ami azt jelenti, hogy egy személy többször is volt az ülés elnöke).
Ez azért van így, mert gondoskodnom kell arról, hogy a legutóbbi elnököt ne nevezzék át.
Ezért úgy döntöttem, hogy valamilyen Excel függvényvarázslatot használok a munkához.
Lent látható a végeredmény, a legördülő menüből kiválaszthatok egy nevet, és ez adja meg a név utolsó előfordulásának dátumát a listában.
Ha jól ismeri az Excel függvényeit, tudja, hogy nincs olyan Excel-függvény, amely ezt meg tudná tenni.Itt csodákat teszünk.
Ebben az oktatóanyagban ennek három módját mutatom be.
tartalom
Keresse meg az utolsó előfordulást - a MAX funkció segítségével
Itt van az Excel képlet, amely a lista utolsó értékét adja vissza:
=INDEX($B$2:$B$14,SUMPRODUCT(MAX(行($A$2:$A$14)*($D$3=$A$2:$A$14))-1))
Így működik ez a képlet:
- A MAX függvény az utolsó egyező név sorszámának megkeresésére szolgál.Például, ha a név Glen, akkor a 11-et adja vissza, mert a 11. sorban van.Mivel listánk a második sorban kezdődik, az 1-et kivonjuk.Ezért Glen utolsó előfordulása 10 a listánkban.
- A SUMPRODUCT annak biztosítására szolgál, hogy ne kelljen a Control + Shift + Enter billentyűkombinációt használnia, mert a SUMPRODUCT képes kezelni a tömbképleteket.
- Az INDEX funkció most az utolsó egyező név dátumának megkeresésére szolgál.
Keresse meg az utolsó előfordulást - a KERESÉS funkció segítségével
Íme egy másik képlet, amely ugyanazt a munkát végzi:
=LOOKUP(2,1/($A$2:$A$14=$D$3),$B$2:$B$14)
Így működik ez a képlet:
- 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) – 1-et ad vissza, ha egyező nevet talál, egyébként hibát.Így a végén egy tömböt kapsz.例如，查找值為 Glen，數組將為 {#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 paraméter ([eredményvektor]) az a tartomány, amelyben az eredményt adja, jelen esetben a dátumot.
Ez a képlet azért működik, mert a LOOKUP függvény közelítő illesztési technikát használ.Ez azt jelenti, hogy ha megtalálja a pontos egyezést, akkor visszatér, de ha nem, akkor a teljes tömböt végigvizsgálja a végéig, és a következő legnagyobb értéket adja vissza, amely kisebb, mint a keresési érték.
Ebben az esetben a keresési érték 2, és a tömbünkben csak 1-et vagy hibát kapunk.Tehát átvizsgálja a teljes tömböt, és visszaadja az utolsó 1 pozícióját - a név utolsó egyező értékét.
Utolsó előfordulás keresése – Egyéni függvény (VBA) használata
Hadd mutassak egy másik utat is.
Egyéni függvényeket (más néven felhasználó által definiált függvényeket) készíthetünk VBA segítségével.
Az egyéni funkciók létrehozásának előnye a könnyű használat.Nem kell minden alkalommal aggódnia bonyolult képletek létrehozása miatt, mert a munka nagy része a VBA-háttérrendszerben történik.
Létrehoztam egy egyszerű képletet (hasonlóan a VLOOKUP képlethez).
Egyéni függvények létrehozásához VBA-kódra van szüksége a VB-szerkesztőben.Később megadom a kódot és a lépéseket, hogy bejusson a VB szerkesztőbe, de először hadd mutassam meg, hogyan működik:
Íme a képlet, amely megadja az eredményt:
=Utolsó elem kikeresése($D$3,$A$2:$B$14,2)
A képlet három paramétert vesz fel:
- Keresse meg az értéket (ez lesz a név a D3 cellában)
- Keresse meg a tartományt (ez lesz a tartomány névvel és dátummal – A2:B14)
- oszlop száma (ez az az oszlop, amelyre az eredményt szeretnénk)
Miután létrehozta a képletet és behelyezte a kódot a VB-szerkesztőbe, használhatja, mint bármely más szokásos Excel-munkalapfüggvényt.
Íme a képlet kódja:
Függvény LastItemLookup(Keresésiérték karakterláncként, keresési tartomány tartományként, oszlopszám egész szám) Dim i As Long For i = LookupRange.Columns(1).Cells.Count to 1 Step -1 Ha Lookupvalue = LookupRange.Cells(i, 1) = LookupRange.Cells(i, ColumnNumber) Kilépési függvény vége Ha Következő i Funkció befejezése
A kód VB-szerkesztőbe való beillesztésének lépései:
- Lépjen a fejlesztői lapra.
- Kattintson a Visual Basic Options elemre.Ez megnyitja a VB-szerkesztőt a háttérben.
- A VB-szerkesztő Project Explorer ablaktáblájában kattintson a jobb gombbal a munkafüzet bármely olyan objektumára, amelybe kódot szeretne beszúrni.Ha nem látja a Project Explorer elemet, lépjen a Nézet fülre, és kattintson a Project Explorer elemre.
- Lépjen a Beszúrás elemre, és kattintson a Modulok elemre.Ezzel beszúr egy modulobjektumot a munkafüzetbe.
- Másolja ki és illessze be a kódot a modul ablakába.
A képlet mostantól a munkafüzet összes lapján elérhető lesz.
Vegye figyelembe, hogy a munkafüzetet .XLSM formátumban kell mentenie, mivel makrókat tartalmaz.Továbbá, ha azt szeretné, hogy ez a képlet minden használt munkafüzetben elérhető legyen, mentheti a személyes makró-munkafüzetébe, vagy létrehozhat belőle bővítményt.
