Stáhněte si ukázkový sešit
Tento tutoriál předvede, jak najít poslední neprázdný řádek v datové sadě v Excelu a Tabulkách Google.
Najděte poslední řádek s daty
Často je užitečné vědět, na kterém řádku vaše data končí. Pokud váš rozsah obsahuje nebo může mít prázdné buňky, můžete poslední neprázdný řádek najít pomocí jedné z níže uvedených metod.
Univerzální metoda
První metoda používá funkce ROW a MAX a lze ji použít s jakýmkoli druhem dat:
| 1 | = MAX ((B: B "")*(ŘADA (B: B))) |
Pojďme analyzovat tento vzorec.
Začneme pomocí logického testu ve sloupci dat. Náš vzorec se dívá na celý sloupec (B: B) a výstupy PRAVDA pro prázdné buňky a NEPRAVDA pro prázdné buňky.
| 1 | = B: B "" |
Funkce ROW vytváří číslo řádku dané buňky. Pokud mu nedáme konkrétní vstup do buňky, poskytne nám číslo řádku buňky, ve které je.
| 1 | = ŘÁDEK () |
Vynásobením každého čísla řádku odpovídajícími hodnotami TRUE (= 1) nebo FALSE (= 0) vrátí číslo řádku pro naplněnou buňku a nulu pro prázdnou buňku.
| 1 | = E3*F3 |
Funkce MAX udává maximum ze sady čísel. V tomto případě, protože všechny prázdné buňky vytvářejí nulovou hodnotu, maximum je nejvyšší číslo řádku.
| 1 | = MAX (G3: G12) |
Kombinací těchto kroků získáme původní vzorec:
| 1 | = MAX ((B: B "")*(ŘADA (B: B))) |
Pamatujte, že toto je maticový vzorec, takže pokud používáte Excel 2022 nebo starší, musíte jej spustit stisknutím CTRL + SHIFT + ENTER.
Metoda pro rozsah textu
Pokud váš (nespojitý) rozsah obsahuje pouze textové hodnoty a prázdné buňky, můžete použít méně komplikovaný vzorec obsahující funkce REPT a MATCH:
| 1 | = MATCH (REPT ("z", 50), B: B) |
Podívejme se, jak tento vzorec funguje.
Funkce REPT
Funkce REPT opakuje textový řetězec daný početkrát. V tomto případě jej můžeme použít k vytvoření textového řetězce, který by byl posledním v libovolném abecedně seřazeném seznamu. Pokud opakujeme „z“ 50krát, mělo by to fungovat pro téměř jakýkoli sloupec textových dat; před „zzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzzz“.
| 1 | = REPT ("z", 50) |
Funkce MATCH
Funkce MATCH najde v poli danou vyhledávací hodnotu.
Provádíme vyhledávání celého sloupce dat pro náš textový řetězec 50-z. Vynechání vstupu typu shody ve funkci MATCH říká, že má najít spíše přibližnou než přesnou shodu.
| 1 | = MATCH (REPT ("z", 50), B: B) |
Funkce MATCH prohledává sloupec B a hledá náš textový řetězec 50 „z“ s. Protože to nenajde, vzorec vrátí pozici poslední neprázdné buňky. Tato buňka obsahuje poslední hodnotu ve vyhledávacím poli, která je menší než (nebo rovna) vyhledávací hodnotě.
Mějte na paměti, že tento vzorec funguje pouze v případě, že váš rozsah obsahuje výhradně text a prázdné buňky (nebo alespoň hodnota poslední buňky je nečíselná).
Najděte poslední řádek s daty v Tabulkách Google
Tyto vzorce fungují v Tabulkách Google úplně stejně jako v Excelu.
