V předchozím díle jste se seznámili se základy programování vlastních funkcí pro využití v listu. Dnes se dozvíte, jak pracovat s celými oblastmi buněk a procvičíte si algoritmizaci praktických úloh.
Následující funkce jsou výsledkem požadavků uživatelů na praktické řešení různých problémů a úkolů, které se objevují ve fóru Calcu. Máte-li vlastní požadavky a tipy, napište do fóra.
REVERSE
Požadavkem uživatele byla možnost řadit seznam podle abecedy, ovšem podle posledního písmene. To se dá snadno zařídit pomocným sloupcem se vzorcem =RIGHT(odkaz;1), avšak řazení nebude přesné v případě shody posledního znaku. Dal by se zde použít komplikovanější vzorec =MID(A1;LEN(A1);1)&MID(A1;LEN(A1)-1;1)&MID(A1;LEN(A1)-2;1), který vrací pozpátku poslední tři znaky, ale ani to nemusí být dostačující. Řešením je tedy naprogramovat si vlastní funkci, která zadaný text obrátí pozpátku. V editoru Basicu to vyřešíte pomocí několika řádků (číslování řádků je pouze pro lepší popis v textu):
-
Function reverse(retezec as string) as string
-
reverse = ""
-
if len(retezec) > 0 then
-
for i=len(retezec) to 1 step -1
-
reverse = reverse & mid(retezec, i, 1)
-
next i
-
else
-
reverse = ""
-
endif
-
End Function
Funkce má pouze jeden vstupní parametr, tj. text, který má být vrácen pozpátku (řádek 1). Na řádku 2 je výsledek funkce pro jistotu resetován. Řádek 3 kontroluje, zda vstupní řetězec má větší délku než nula. Pokud ano, provedou se řádky 4 až 6, jinak se provede řádek 8.
Na řádcích 4-6 probíhá samotné otočení řetězce v cyklu For-Next, kdy jsou postupně od konce řetězce jednotlivé znaky (pomocí příkazu Mid) připojovány do výsledku funkce reverse.
Problémem je zde odkaz na prázdnou buňku, kdy výsledkem funkce REVERSE je nula namísto prázdného řetězce. Je to způsobeno tím, že prázdná buňka je do parametru předána jako nula, nikoli jako prázdný řetězec, takže test na řádku 3 vyhodnotí délku řetězce „0“ jako větší než jedna. Řešením je rozšířit volání funkce REVERSE v listu na vzorec =IF(LEN(A1)>0;REVERSE(A1);"").
INSTRCOUNT
Calc disponuje funkcí listu COUNTIF, která umí počítat, kolik buněk v dané oblasti obsahuje zadaný řetězec. Neumí však již určit počet výskytů tohoto řetězce ve všech buňkách oblasti (pro případ že se řetězec v rámci jedné buňky vyskytuje vícekrát). Tento výpočet zajistí následující funkce INSTRCOUNT:
-
Function instrcount(oblast as object, podretezec as string) as integer
-
instrcount = 0
-
for radek = 1 to ubound(oblast, 1)
-
for sloupec = 1 to ubound(oblast, 2)
-
hlavni = oblast(radek, sloupec)
-
zacatek = 1
-
do while instr(zacatek, hlavni, podretezec)
-
instrcount = instrcount + 1
-
zacatek = instr(zacatek, hlavni, podretezec) + len(podretezec)
-
loop
-
next sloupec
-
next radek
-
End Function
Funkce má dva vstupní parametry – prohledávanou oblast a vyhledávaný podřetězec (vizte řádek 1). Na řádku dvě je vyresetován výsledek funkce. Řádky 3 a 4 pomocí cyklu zajistí, že budou postupně prohledány všechny buňky oblasti (funkce Uboud vrací horní hranici pole v definovaném rozměru, zde tedy počet řádků či sloupců oblasti).
Na řádku 5 je do proměnné hlavni načten obsah buňky daného řádku a sloupce oblasti. Pomocná proměnná zacatek je na řádku 6 nastavena na 1. Na řádcích 7-10 probíhá cyklus vyhledávání podřetězce v obsahu buňky pomocí funkce Basicu Insrt, jejímž výsledkem je pořadí výskytu tohoto podřetězce, tak dlouho, dokud je podřetězec nacházen. Důležitým faktem je, že se v obsahu buňky hledá podřetězec od pozice zacatek jeho posledního výskytu zvětšené o délku podřetězce (řádek 9), takže v řetězci „bbb“ je vyhledán podřetězec „bb“ pouze jednou a to na pozici 1. Další hledání od pozice 3 již není úspěšné. Na řádku 8 se pak načítá počet úspěšných vyhledání do výsledku funkce.
LASTINRANGE
Účelem této funkce je vrátit obsah poslední vyplněné buňky v zadané oblasti. Jelikož se stanovení poslední vyplněné buňky v oblasti může lišit v závislosti na způsobu jejího prohledávání (po řádcích, nebo po sloupcích), bude jedním z parametrů metoda prohledávání oblasti. Následující obrázek názorně ukazuje rozdíl.
Rozdíl v prohledávání dle řádků či sloupců
Jestliže zadanou oblast A1:D8 procházíme po řádcích (A1, B1, C1, D1, A2, B2, ....), pak poslední vyplněnou buňkou v této oblasti je C8. Jestliže však stejnou oblast procházíme po sloupcích (A1, A2, ..., A8, B1, B2, ..., B8, C1, ...), je to buňka D6.
Funkce bude mít tedy syntaxi LASTINRANGE(oblast; metoda prohledávání), kdy LASTINRANGE(A1:D8;1) vrátí výsledek „konec-řádky“ a LASTINRANGE(A1:D8;2) pak „konec-sloupce“.
-
Function lastinrange(oblast as object, metoda as integer) as variant
-
'metoda: 1-prohledává se po řádcích; 2-prohledává se po sloupcích
-
lastinrange = ""
-
if metoda = 1 then
-
rozmer1 = 1
-
rozmer2 = 2
-
elseif metoda = 2 then
-
rozmer1 = 2
-
rozmer2 = 1
-
endif
-
for rada1 = ubound(oblast, rozmer1) to 1 step -1
-
for rada2 = ubound(oblast, rozmer2) to 1 step -1
-
if metoda = 1 then
-
obsah = oblast (rada1, rada2)
-
elseif metoda = 2 then
-
obsah = oblast (rada2, rada1)
-
endif
-
if len(obsah) > 0 and obsah <> 0 then
-
lastinrange = obsah
-
exit function
-
endif
-
next rada2
-
next rada1
-
End function
Na prvním řádku jsou standardně načteny parametry funkce a definován typ výsledku. Druhý řádek je jen komentář, aby bylo jasné, jaký význam mají hodnoty 1 a 2 u proměnné metoda. Na řádcích 4-10 je v závislosti na zvolené metodě nastaveno, jestli hlavní prohledávaný rozměr oblasti jsou řádky (hodnota 1 v proměnné rozmer1 na ř.5), nebo sloupce (hodnota 2 v rozmer1 na ř.8). Druhý rozměr je pak doplňkem prvního.
Cyklus For na ř.11 řídí primární směr prohledávání určený parametrem metody, cyklus na ř.12 pak prochází jednotlivé buňky v příslušném řádku, resp. sloupci. Oba cykly probíhají pozpátku (step -1), protože hledáme poslední vyplněnou buňku oblasti. Jestliže je tedy metoda prohledávání nastavena na 2 (po sloupcích), pak proměnná rozmer1 má hodnotu 2 a funkce Ubound(oblast,rozmer1) na řádku 11 vrací počet sloupců v oblasti.
Na řádcích 13-17 je do proměnné obsah načítán obsah aktuálně kontrolované buňky – v závislosti na zvolené metodě je potřeba správně načíst řádek a sloupec: jestliže medota=1, pak rada1 jsou řádky a rada2 jsou sloupce, jestliže metoda=2, pak je to naopak a pořadí parametrů na řádku 16 musí být prohozeno (oblast buněk se vždy indexuje jako řádek, sloupec).
Na řádku 18 je vlastní test, zda je buňka vyplněná (obsah buňky má větší délku než 0) a pokud ano, pak je na ř.19 do výsledku funkce načten obsah buňky a na řádku 20 se funkce ihned ukončí, protože bylo dosaženo cíle. Test na ř.18 je navíc rozšířen o podmínku, že obsah buňky musí být různý od nuly, protože při použitém způsobu odkazování na obsah jsou prázdné buňky do makra/funkce předány s hodnotou „0“. To s sebou přináší jednu nepříjemnou vlastnost této funkce a sice, že buňky, v nichž je vložena nula, budou podmínkou na ř.18 ignorovány – vyhodnoceny jako prázdné.
