V tomto kurzu se naučíte, jak najít poslední výskyt položky v seznamu pomocí vzorců aplikace Excel.
Nedávno jsem pracoval na nastavení programu schůzky.
Měl jsem v Excelu seznam, kde jsem měl seznam lidí a data, kdy působili jako „předseda schůze“.
Vzhledem k tomu, že v seznamu bylo opakování (což znamená, že osoba byla předsedou schůzky vícekrát), potřeboval jsem také vědět, kdy naposledy osoba působila jako „předseda schůze“.
Bylo to proto, že jsem musel zajistit, aby někdo, kdo nedávno předsedal, nebyl znovu přidělen.
Rozhodl jsem se tedy použít nějaké kouzlo funkce Excel, abych to udělal.
Níže je konečný výsledek, kde mohu vybrat jméno z rozevíracího seznamu, a to mi dává datum posledního výskytu tohoto jména v seznamu.
Pokud dobře rozumíte funkcím aplikace Excel, věděli byste, že neexistuje žádná funkce aplikace Excel, která by to dokázala.
Ale vy jste v sekci Formula Hack a tady se postaráme o to, aby se stalo kouzlo.
V tomto tutoriálu vám ukážu tři způsoby, jak toho dosáhnout.
Najděte poslední výskyt - pomocí funkce MAX
Zásluhu na této technice má článek Excel MVP Charley Kyd.
Zde je vzorec aplikace Excel, který vrátí poslední hodnotu ze seznamu:
= INDEX ($ B $ 2: $ B $ 14, SUMPRODUCT (MAX (ŘADA ($ A $ 2: $ A $ 14)*($ D $ 3 = $ A $ 2: $ A $ 14))-1))
Tento vzorec funguje takto:
- Funkce MAX slouží k vyhledání čísla řádku posledního shodného jména. Pokud je například název Glen, vrátí 11, protože je v řádku 11. Protože náš seznam začíná od druhé řady, 1 byl odečten. Pozice posledního výskytu Glen je tedy na našem seznamu 10.
- SUMPRODUCT slouží k zajištění toho, že nemusíte používat Ctrl + Shift + Enter, protože SUMPRODUCT zvládne vzorce pole.
- Funkce INDEX se nyní používá k vyhledání data pro shodné příjmení.
Najděte poslední výskyt - pomocí funkce LOOKUP
Zde je další vzorec pro stejnou práci:
= LOOKUP (2,1/($ A $ 2: $ A $ 14 = $ D $ 3), $ B $ 2: $ B $ 14)
Tento vzorec funguje takto:
- Vyhledávací hodnota je 2 (uvidíte proč … pokračujte ve čtení)
- Rozsah vyhledávání je 1/($ A $ 2: $ A $ 14 = $ D $ 3) - Vrátí 1, když najde odpovídající jméno, a chybu, když ne. Takže nakonec získáte pole. Například z vyhledávací 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!}.
- Třetí argument ([result_vector]) je rozsah, ze kterého dává výsledek, což jsou v tomto případě data.
Důvod, proč tento vzorec funguje, je ten, že funkce LOOKUP používá techniku přibližné shody. To znamená, že pokud dokáže najít přesně odpovídající hodnotu, vrátí to, ale pokud to nedokáže, prohledá celé pole až do konce a vrátí další největší hodnotu, která je nižší než vyhledávací hodnota.
V tomto případě je vyhledávací hodnota 2 a v našem poli získáme pouze 1 nebo chyby. Oskenuje tedy celé pole a vrátí pozici poslední 1 - což je poslední odpovídající hodnota názvu.
Najít poslední výskyt - pomocí vlastní funkce (VBA)
Ukážu vám také další způsob, jak toho dosáhnout.
Můžeme vytvořit vlastní funkci (také nazývanou uživatelem definovaná funkce) pomocí VBA.
Výhodou vytvoření vlastní funkce je, že se snadno používá. Nemusíte se starat o vytvoření složitého vzorce pokaždé, protože většina práce se odehrává v backendu VBA.
Vytvořil jsem jednoduchý vzorec (který je hodně podobný vzorci VLOOKUP).
Chcete -li vytvořit vlastní funkci, musíte mít kód VBA v editoru VB. Za chvíli vám dám kód a kroky k jeho umístění do editoru VB, ale nejprve vám ukážu, jak to funguje:
Toto je vzorec, který vám poskytne výsledek:
= LastItemLookup ($ D $ 3, $ A $ 2: $ B $ 14,2)
Vzorec má tři argumenty:
- Vyhledávací hodnota (toto by byl název v buňce D3)
- Rozsah vyhledávání (toto by byl rozsah se jmény a daty - A2: B14)
- Číslo sloupce (toto je sloupec, ze kterého chceme výsledek)
Jakmile vytvoříte vzorec a vložíte kód do editoru VB, můžete jej použít stejně jako všechny ostatní běžné funkce listu aplikace Excel.
Zde je kód vzorce:
„Toto je kód pro funkci, která najde poslední výskyt vyhledávané hodnoty a vrátí odpovídající hodnotu ze zadaného sloupce“ Kód vytvořený Sumit Bansal (https://trumpexcel.com) Funkce 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) Ukončit funkci Konec Pokud Další i Ukončit funkci
Zde jsou kroky k umístění tohoto kódu do editoru VB:
- Přejděte na kartu Vývojář.
- Klikněte na možnost Visual Basic. Tím se otevře editor VB v backendu.
- V podokně Průzkumník projektů v editoru VB klikněte pravým tlačítkem na libovolný objekt sešitu, do kterého chcete vložit kód. Pokud aplikaci Project Explorer nevidíte, přejděte na kartu Zobrazit a klikněte na Průzkumník projektů.
- Přejděte na Vložit a klikněte na Modul. Tím se vloží objekt modulu pro váš sešit.
- Zkopírujte a vložte kód do okna modulu.
Nyní by byl vzorec k dispozici ve všech listech sešitu.
Sešit je třeba uložit ve formátu .XLSM, protože obsahuje makro. Pokud chcete, aby byl tento vzorec k dispozici ve všech sešitech, které používáte, můžete jej buď uložit do osobního sešitu maker, nebo z něj vytvořit doplněk.
