Stiahnite si ukážkový zošit
Tento tutoriál vás naučí, ako v programe Excel vyhľadať poslednú hodnotu v stĺpci alebo riadku.
Posledná hodnota v stĺpci
Na vyhľadanie poslednej neprázdnej bunky v stĺpci môžete použiť funkciu LOOKUP.
| 1 | = ZOBRAZIŤ (2,1/(B: B ""), B: B) |
Prejdeme si tento vzorec.
Časť vzorca B: B ”” vracia pole obsahujúce hodnoty True a False: {FALSE, TRUE, TRUE, …}, testovanie každej bunky v stĺpci B je prázdne (FALSE).
| 1 | = LOOKUP (2,1/({FALSE; TRUE; TRUE; TRUE; TRUE; TRUE; FALSE; …), B: B) |
Tieto booleovské hodnoty sa prevedú na 0 alebo 1 a použijú sa na delenie 1.
| 1 | = ZOBRAZIŤ (2, {#DIV/0!; 1; 1; 1; 1; 1;#DIV/0!; B: B) |
Toto je lookup_vector pre funkciu LOOKUP. V našom prípade je lookup_value 2, ale najväčšia hodnota v lookup_vector je 1, takže funkcia LOOKUP bude zodpovedať poslednej 1 v poli a vráti zodpovedajúcu hodnotu vo result_vector.
Ak ste si istí, že v stĺpci máte iba číselné hodnoty, údaje začínajú od riadka 1 a rozsah údajov je súvislý, môžete použiť o niečo jednoduchší vzorec s funkciami INDEX a COUNT.
| 1 | = INDEX (B: B, COUNT (B: B)) |
Funkcia COUNT vráti počet buniek naplnených údajmi v súvislom rozsahu (4) a funkcia INDEX teda poskytne hodnotu bunky v tomto zodpovedajúcom riadku (4.).
Aby ste sa vyhli možným chybám, keď váš rozsah údajov obsahuje zmes číselných a nečíselných hodnôt, alebo dokonca niektoré prázdne bunky, môžete použiť funkciu LOOKUP spolu s funkciami ISBLANK a NOT.
| 1 | = ZOBRAZIŤ (2,1/(NIE (ISBLANK (B: B))), B: B) |
Funkcia ISBLANK vracia pole obsahujúce hodnoty True a False zodpovedajúce 1 a 0. Funkcia NOT zmení True (t.j. 1) na False a False (t.j. 0) na True. Ak invertujeme toto výsledné pole (pri delení 1 týmto poľom), dostaneme výsledné pole obsahujúce opäť #DIV/0! chyby a 1, ktoré je možné použiť ako vyhľadávacie pole (lookup_vector) v našej funkcii LOOKUP. Funkčnosť funkcie LOOKUP je potom rovnaká ako v našom prvom príklade: vráti hodnotu vektora výsledkov na mieste poslednej 1 vo vyhľadávacom poli.
Keď potrebujete vrátiť číslo riadku s posledným záznamom, môžete vzorec použitý v našom prvom príklade spolu s funkciou ROW upraviť vo vašom result_vector.
| 1 | = ZOBRAZIŤ (2,1/(B: B ""), RADA (B: B)) |
Posledná hodnota v riadku
Ak chcete získať hodnotu poslednej prázdnej bunky vyplnenej číselnými údajmi, možno budete chcieť použiť podobný prístup, ale s inými funkciami: funkcia OFFSET spolu s funkciami MATCH a MAX.
| 1 | = OFFSET (referencia, riadky, stĺpce) |
| 1 | = OFFSET (B2,0, MATCH (MAX (B2: XFD2)+1, B2: XFD2,1) -1) -1) |
Pozrime sa, ako tento vzorec funguje.
Funkcia MATCH
Funkciu MATCH používame na „spočítanie“ toho, koľko hodnôt buniek je pod 1+ maxima všetkých hodnôt v riadku 2, začínajúc od B2.
| 1 | = MATCH (lookup_value, lookup_array, [match_type]) |
| 1 | = MATCH (MAX (B2: XFD2)+1, B2: XFD2,1) |
Lookup_value funkcie MATCH je maximum zo všetkých hodnôt v riadku 2 + 1. Pretože táto hodnota v riadku 2 zjavne neexistuje a typ_zhody je nastavený na 1 (menší alebo rovný lookup_value), funkcia MATCH vráti pozícia poslednej „začiarknutej“ bunky v poli, to znamená počet buniek naplnených údajmi v rozsahu B2: XFD2 (XFD je posledný stĺpec v novších verziách programu Excel).
Funkcia OFFSET
Potom pomocou funkcie OFFSET získame hodnotu tejto bunky, ktorej poloha bola vrátená funkciou MATCH.
| 1 | = OFFSET (B2,0, C4-1) |
