V tomto tutoriále sa naučíte, ako nájsť posledný výskyt položky v zozname pomocou vzorcov programu Excel.
Nedávno som pracoval na nastavení programu schôdze.
V Exceli som mal zoznam, kde som mal zoznam ľudí a dátumy, kedy pôsobili ako „predseda schôdze“.
Keďže v zozname bolo opakovanie (čo znamená, že osoba bola predsedom schôdze viackrát), potreboval som tiež vedieť, kedy naposledy osoba konala ako „predseda schôdze“.
Dôvodom bolo, že som musel zaistiť, aby niekto, kto nedávno predsedal, nebol znova pridelený.
Preto som sa rozhodol použiť na to nejaké kúzlo funkcií Excelu.
Nasleduje konečný výsledok, v ktorom môžem vybrať meno z rozbaľovacej ponuky a ktoré mi dáva dátum posledného výskytu tohto mena v zozname.
Ak dobre rozumiete funkciám programu Excel, vedeli by ste, že neexistuje žiadna funkcia programu Excel, ktorá by to dokázala.
Ale ste v sekcii Formula Hack a my tu robíme kúzlo.
V tomto návode vám ukážem tri spôsoby, ako to urobiť.
Nájdite posledný výskyt - pomocou funkcie MAX
Zásluhu na tejto technike má článok Excel MVP Charley Kyd.
Tu je vzorec programu Excel, ktorý vráti poslednú hodnotu zo zoznamu:
= INDEX ($ B $ 2: $ B $ 14, SUMPRODUCT (MAX (ROW ($ A $ 2: $ A $ 14)*($ D $ 3 = $ A $ 2: $ A $ 14))-1))
Tento vzorec funguje takto:
- Na nájdenie čísla riadka posledného zodpovedajúceho mena sa používa funkcia MAX. Ak je napríklad názov Glen, vráti 11, pretože je v riadku 11. Pretože náš zoznam začína od druhého radu, 1 bol odpočítaný. Pozícia posledného výskytu Glena je teda 10 v našom zozname.
- SUMPRODUCT sa používa na zaistenie toho, že nemusíte používať kombináciu klávesov Ctrl + Shift + Enter, pretože SUMPRODUCT zvládne vzorce polí.
- Na nájdenie dátumu pre zodpovedajúci názov sa teraz používa funkcia INDEX.
Nájdite posledný výskyt - pomocou funkcie LOOKUP
Tu je ďalší vzorec na vykonanie rovnakej práce:
= ZOBRAZIŤ (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 … pokračujte v čítaní)
- Rozsah vyhľadávania je 1/($ A $ 2: $ A $ 14 = $ D $ 3) - vráti hodnotu 1, keď nájde zodpovedajúci názov, a chybu, ak nie. Takže nakoniec získate pole. Napríklad z vyhľadávacej hodnoty je Glen, pole bude {#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í argument ([result_vector]) je rozsah, z ktorého dáva výsledok, čo sú v tomto prípade dátumy.
Dôvod, prečo tento vzorec funguje, je ten, že funkcia LOOKUP používa techniku približnej zhody. To znamená, že ak dokáže nájsť presne zodpovedajúcu hodnotu, vráti to, ale ak nie, naskenuje celé pole až do konca a vráti nasledujúcu najväčšiu hodnotu, ktorá je nižšia ako hodnota vyhľadávania.
V tomto prípade je hodnota vyhľadávania 2 a v našom poli dostaneme iba 1 alebo chyby. Skenuje teda celé pole a vráti pozíciu poslednej 1 - čo je posledná zhodná hodnota názvu.
Nájdite posledný výskyt - pomocou vlastnej funkcie (VBA)
Ukážem vám aj iný spôsob, ako to urobiť.
Pomocou VBA môžeme vytvoriť vlastnú funkciu (nazývanú aj funkcia definovaná používateľom).
Výhodou vytvorenia vlastnej funkcie je, že sa ľahko používa. Nemusíte sa starať o vytvorenie komplexného vzorca zakaždým, pretože väčšina práce sa deje v backende VBA.
Vytvoril som jednoduchý vzorec (ktorý je veľmi podobný vzorcu VLOOKUP).
Na vytvorenie vlastnej funkcie potrebujete mať kód VBA v editore VB. O chvíľu vám poskytnem kód a kroky na jeho umiestnenie do editora VB, ale najskôr vám ukážem, ako funguje:
Toto je vzorec, ktorý vám poskytne výsledok:
= LastItemLookup ($ D $ 3, $ A $ 2: $ B $ 14,2)
Vzorec má tri argumenty:
- Hodnota vyhľadávania (toto by bol názov v bunke D3)
- Rozsah vyhľadávania (toto by bol rozsah s názvami a dátumami - A2: B14)
- Číslo stĺpca (toto je stĺpec, z ktorého chceme získať výsledok)
Keď vytvoríte vzorec a vložíte kód do editora VB, môžete ho používať rovnako ako ostatné bežné funkcie pracovného hárka programu Excel.
Tu je kód vzorca:
„Toto je kód pre funkciu, ktorá nájde posledný výskyt vyhľadávanej hodnoty a vráti zodpovedajúcu hodnotu zo zadaného stĺpca“ Kód vytvorený spoločnosťou Sumit Bansal (https://trumpexcel.com) Funkcia LastItemLookup (Lookupvalue As String, LookupRange As Rozsah, číslo stĺpca ako celé číslo) Dim i ako dlhý pre i = LookupRange.Columns (1) .Cells.Count to 1 Step -1 If Lookupvalue = LookupRange.Cells (i, 1) Then LastItemLookup = LookupRange.Cells (i, ColumnNumber) Ukončiť funkciu Ukončiť Ak Ďalej i Ukončiť funkciu
Tu sú kroky na vloženie tohto kódu do editora VB:
- Prejdite na kartu Vývojár.
- Kliknite na možnosť Visual Basic. Tým sa otvorí editor VB v backende.
- Na table Project Explorer v editore VB kliknite pravým tlačidlom myši na ľubovoľný objekt zošita, do ktorého chcete vložiť kód. Ak sa Project Explorer nezobrazuje, prejdite na kartu Zobraziť a kliknite na položku Project Explorer.
- Prejdite na položku Vložiť a kliknite na modul. Tým sa vloží objekt modulu do vášho zošita.
- Skopírujte a prilepte kód do okna modulu.
Teraz by bol vzorec k dispozícii vo všetkých pracovných listoch zošita.
Zošit musíte uložiť vo formáte .XLSM, pretože obsahuje makro. Ak chcete, aby bol tento vzorec k dispozícii vo všetkých zošitoch, ktoré používate, môžete ho buď uložiť do osobného zošita makier, alebo z neho vytvoriť doplnok.
