Stáhněte si ukázkový sešit
Tento tutoriál vás naučí, jak vyhledat poslední hodnotu ve sloupci nebo řádku v aplikaci Excel.
Poslední hodnota ve sloupci
Pomocí funkce LOOKUP můžete najít poslední neprázdnou buňku ve sloupci.
| 1 | = HLEDAT (2,1/(B: B ""), B: B) |
Pojďme si projít tento vzorec.
Část vzorce B: B ”” vrací pole obsahující hodnoty True a False: {FALSE, TRUE, TRUE,…}, testování každé buňky ve sloupci B je prázdné (FALSE).
| 1 | = LOOKUP (2,1/({FALSE; TRUE; TRUE; TRUE; TRUE; TRUE; FALSE;…), B: B) |
Tyto booleovské hodnoty se převádějí na 0 nebo 1 a slouží k dělení 1.
| 1 | = HLEDAT (2, {#DIV/0!; 1; 1; 1; 1; 1;#DIV/0!; B: B) |
Toto je lookup_vector pro funkci LOOKUP. V našem případě je lookup_value 2, ale největší hodnota v lookup_vector je 1, takže funkce LOOKUP bude odpovídat poslední 1 v poli a vrátí odpovídající hodnotu ve result_vector.
Pokud jste si jisti, že ve sloupci máte pouze číselné hodnoty, data začínají od řádku 1 a rozsah dat je souvislý, můžete použít o něco jednodušší vzorec s funkcemi INDEX a COUNT.
| 1 | = INDEX (B: B, COUNT (B: B)) |
Funkce COUNT vrací počet buněk naplněných daty v souvislém rozsahu (4) a funkce INDEX tedy udává hodnotu buňky v tomto odpovídajícím řádku (4.).
Abyste se vyhnuli možným chybám, když váš rozsah dat obsahuje směs číselných a nečíselných hodnot, nebo dokonce některé prázdné buňky, můžete použít funkci LOOKUP společně s funkcemi ISBLANK a NOT.
| 1 | = LOOKUP (2,1/(NOT (ISBLANK (B: B))), B: B) |
Funkce ISBLANK vrací pole obsahující hodnoty True a False, odpovídající 1 a 0. Funkce NOT změní True (tj. 1) na False a False (tj. 0) na True. Obrátíme -li toto výsledné pole (při dělení 1 tímto polem), dostaneme výsledkové pole obsahující opět #DIV/0! chyby a 1, které lze použít jako vyhledávací pole (lookup_vector) v naší funkci LOOKUP. Funkčnost funkce LOOKUP je pak stejná jako v našem prvním příkladu: vrací hodnotu vektoru výsledků na pozici poslední 1 ve vyhledávacím poli.
Když potřebujete vrátit číslo řádku s posledním záznamem, můžete upravit vzorec použitý v našem prvním příkladu společně s funkcí ROW ve vašem result_vector.
| 1 | = HLEDAT (2,1/(B: B ""), ŘADA (B: B)) |
Poslední hodnota v řadě
Chcete-li získat hodnotu poslední neprázdné buňky v řádku naplněném číselnými údaji, můžete použít podobný přístup, ale s jinými funkcemi: funkce OFFSET společně s funkcemi MATCH a MAX.
| 1 | = OFFSET (reference, řádky, sloupce) |
| 1 | = OFFSET (B2,0, MATCH (MAX (B2: XFD2)+1, B2: XFD2,1) -1) -1) |
Podívejme se, jak tento vzorec funguje.
Funkce MATCH
Pomocí funkce MATCH „spočítáme“, kolik hodnot buněk je pod 1 + maximum všech hodnot v řádku 2 počínaje od B2.
| 1 | = MATCH (lookup_value, lookup_array, [match_type]) |
| 1 | = MATCH (MAX (B2: XFD2)+1, B2: XFD2,1) |
Lookup_value of the MATCH Function is the maximum of all values in the row2 + 1. Vzhledem k tomu, že tato hodnota evidentně neexistuje v row2 a typ_shody je nastaven na 1 (menší nebo roven lookup_value), funkce MATCH vrátí pozice poslední „zkontrolované“ buňky v poli, tj. počet buněk naplněných daty v rozsahu B2: XFD2 (XFD je poslední sloupec v novějších verzích aplikace Excel).
Funkce OFFSET
Poté pomocí funkce OFFSET získáme hodnotu této buňky, jejíž pozice byla vrácena funkcí MATCH.
| 1 | = OFFSET (B2,0, C4-1) |
