Obecný vzorec
=MATCH(bigtext,range)
souhrn
Chcete-li získat poslední relativní pozici (tj. Poslední řádek, poslední sloupec) pro textová data (s prázdnými buňkami nebo bez nich), můžete použít funkci MATCH. V zobrazeném příkladu je vzorec v D5:
=MATCH(REPT("z",255),B4:B11)
Vysvětlení
Tento vzorec používá funkci MATCH v režimu přibližné shody k vyhledání poslední textové hodnoty v rozsahu. Přibližná shoda povolena nastavením 3. argumentem v MATCH na 1 nebo vynecháním tohoto argumentu, který je výchozí 1.
Vyhledávací hodnotou je takzvaný „velký text“ (někdy zkráceně „bigtext“), který je záměrně hodnotou „větší“ než jakákoli hodnota, která se objeví v rozsahu. Při práci s textem, který se seřadí podle abecedy, to znamená textovou hodnotu, která se vždy zobrazí na konci abecedního pořadí řazení.
Vzhledem k tomu, že tento vzorec odpovídá textu, je myšlenkou vytvořit vyhledávací hodnotu, která se ve skutečném textu nikdy neobjeví, ale vždy bude poslední. K tomu použijeme funkci REPT, abychom písmeno „z“ opakovali 255krát. Číslo 255 představuje největší počet znaků, které MATCH umožňuje ve vyhledávací hodnotě.
Když MATCH nemůže tuto hodnotu najít, bude „krok zpět“ k poslední textové hodnotě v rozsahu a vrátí pozici této hodnoty.
Poznámka: Tento přístup funguje dobře s prázdnými buňkami v rozsahu, ale není spolehlivý se smíšenými daty, která zahrnují jak čísla, tak text.
Poslední relativní pozice vs číslo poslední řady
Při vytváření pokročilých vzorců, které vytvářejí dynamické rozsahy, je často nutné zjistit poslední umístění dat v seznamu. V závislosti na datech to může být poslední řádek s daty, poslední sloupec s daty nebo jejich průnik. Poznámka: chceme poslední relativní pozici uvnitř daného rozsahu, nikoli číslo řádku v listu:
Dynamický rozsah
Tento vzorec můžete použít k vytvoření dynamického rozsahu s dalšími funkcemi, jako jsou INDEX a OFFSET. Příklady a vysvětlení viz odkazy níže:
- Dynamický rozsah s INDEX a COUNTA
- Dynamický rozsah s OFFSET a COUNTA
Inspirací pro tento článek byla vynikající kniha Mika Girvina Control + Shift + Enter, kde Mike vysvětluje koncept „poslední relativní polohy“.
