Zhrnutie: v tomto návode sa naučíte, ako použiť vzorec Excelu na nájdenie posledného výskytu položky v zozname.
Nedávno som pracoval na programe stretnutia.
Mám zoznam v Exceli so zoznamom ľudí a dátumov, kedy boli "stretnutí predsedov".
Potrebujem tiež vedieť, kedy bola osoba naposledy „predsedom schôdze“ kvôli duplicite v zozname (čo znamená, že osoba predsedala schôdzi viackrát).
Je to preto, že sa musím uistiť, že posledný predseda nebude zmenený.
Rozhodol som sa teda použiť na vykonanie úlohy nejaké kúzlo funkcie Excel.
Nižšie je konečný výsledok, môžem vybrať meno z rozbaľovacej ponuky a zobrazí mi dátum posledného výskytu tohto mena v zozname.
Ak dobre rozumiete funkciám Excelu, viete, že neexistuje žiadna funkcia Excelu, ktorá by to dokázala.Tu robíme zázraky.
V tomto návode vám ukážem tri spôsoby, ako to urobiť.
Obsah
Nájdite posledný výskyt - pomocou funkcie MAX
Tu je vzorec Excel, ktorý vráti poslednú hodnotu v zozname:
=INDEX($B$2:$B$14,SUMPRODUCT(MAX(行($A$2:$A$14)*($D$3=$A$2:$A$14))-1))
Tento vzorec funguje takto:
- Funkcia MAX sa používa na nájdenie čísla riadku posledného zhodného názvu.Napríklad, ak je meno Glen, vráti 11, pretože je na riadku 11.Keďže náš zoznam začína na druhom riadku, 1 sa odpočíta.Preto je posledný výskyt Glen 10 v našom zozname.
- SUMPRODUCT sa používa na to, aby ste sa uistili, že nemusíte používať Control + Shift + Enter, pretože SUMPRODUCT dokáže spracovať vzorce poľa.
- Funkcia INDEX sa teraz používa na nájdenie dátumu posledného zhodného mena.
Nájdite posledný výskyt - pomocou funkcie VYHĽADAŤ
Tu je ďalší vzorec, ktorý robí rovnakú prácu:
=LOOKUP(2,1/($A$2:$A$14=$D$3),$B$2:$B$14)
Tento vzorec funguje takto:
- Hodnota vyhľadávania je 2 (uvidíte prečo.. čítajte ďalej)
- Rozsah vyhľadávania je 1/($A$2:$A$14=$D$3) – keď nájde zhodný názov, vráti 1 a v opačnom prípade chybu.Takže skončíte s poľom.例如，查找值為 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!}。
- Tretí parameter ([result_vector]) je rozsah, v ktorom dáva výsledok, v tomto prípade dátum.
Dôvod, prečo tento vzorec funguje, je ten, že funkcia VYHĽADAŤ používa techniku približného párovania.To znamená, že ak dokáže nájsť presnú zhodu, vráti sa, ale ak nie, prehľadá celé pole až do konca a vráti ďalšiu najväčšiu hodnotu, ktorá je menšia ako vyhľadávaná hodnota.
V tomto prípade je vyhľadávaná hodnota 2 a v našom poli dostaneme iba 1 alebo chybu.Skenuje teda celé pole a vráti pozíciu poslednej 1 - poslednej zodpovedajúcej hodnoty názvu.
Nájsť posledný výskyt – pomocou vlastnej funkcie (VBA)
Dovoľte mi ukázať vám aj iný spôsob.
Pomocou VBA môžeme vytvárať vlastné funkcie (známe aj ako funkcie definované používateľom).
Výhodou vytvárania vlastných funkcií je jednoduchosť používania.Nemusíte si robiť starosti s vytváraním zložitých vzorcov zakaždým, pretože väčšina práce sa deje v backende VBA.
Vytvoril som jednoduchý vzorec (podobne ako vzorec VLOOKUP).
Na vytvorenie vlastných funkcií potrebujete kód VBA v editore VB.Neskôr vám poskytnem kód a kroky, ako ho dostať do editora VB, ale najprv vám ukážem, ako to funguje:
Tu je vzorec, ktorý vám dá výsledok:
=LastItemLookup($D$3,$A$2:$B$14,2)
Vzorec má tri parametre:
- Nájdite hodnotu (toto bude názov v bunke D3)
- Nájdite rozsah (toto bude rozsah s názvom a dátumom – A2:B14)
- číslo stĺpca (toto je stĺpec, pre ktorý chceme výsledok)
Po vytvorení vzorca a umiestnení kódu do editora VB ho môžete použiť ako akúkoľvek inú bežnú funkciu pracovného hárka programu Excel.
Tu je kód pre vzorec:
Funkcia LastItemLookup(Lookupvalue As String, LookupRange As Range, ColumnNumber As Integer) Dim i as Long For i = LookupRange.Columns(1).Cells.Count To 1 Step -1 If Lookupvalue = LookupRange.Cells(i, 1) Then LastItemLookup = LookupRange.Cells(i, ColumnNumber) Koniec funkcie End If Next i End Function
Tu sú kroky na vloženie tohto kódu do editora VB:
- Prejdite na kartu vývojár.
- Kliknite na položku Možnosti jazyka Visual Basic.Tým sa otvorí editor VB v backende.
- Na table Project Explorer editora VB kliknite pravým tlačidlom myši na ľubovoľný objekt v zošite, do ktorého chcete vložiť kód.Ak nevidíte Project Explorer, prejdite na kartu View a kliknite na Project Explorer.
- Prejdite na položku Vložiť a kliknite na položku Moduly.Tým sa vloží objekt modulu pre váš zošit.
- Skopírujte a prilepte kód do okna modulu.
Teraz bude vzorec dostupný vo všetkých hárkoch zošita.
Upozorňujeme, že zošit budete musieť uložiť vo formáte .XLSM, pretože obsahuje makrá.Ak chcete, aby bol tento vzorec dostupný vo všetkých zošitoch, ktoré používate, môžete si ho uložiť do osobného zošita makier alebo z neho vytvoriť doplnok.
