Töltse le a példa munkafüzetet
Ez az oktatóanyag megtanítja, hogyan kell megkeresni az Excel oszlopában vagy sorában lévő utolsó értéket.
Az utolsó érték az oszlopban
A LOOKUP függvénnyel megkeresheti az oszlop utolsó nem üres celláját.
| 1 | = KERESÉS (2,1/(B: B ""), B: B) |
Nézzük végig ezt a képletet.
A B képlet része: B ”” egy tömböt ad vissza, amely igaz és hamis értékeket tartalmaz: {FALSE, TRUE, TRUE,…}, a B oszlop minden cellájának tesztelése üres (FALSE).
| 1 | = KERESÉS (2,1/({HAMIS; IGAZ; IGAZ; IGAZ; IGAZ; IGAZ; HAMIS;…), B: B) |
Ezek a logikai értékek 0 -ra vagy 1 -re konvertálódnak, és az 1 osztására szolgálnak.
| 1 | = KERESÉS (2, {#DIV/0!; 1; 1; 1; 1; 1; 1;#DIV/0!;, B: B) |
Ez a lookup_vector a LOOKUP függvényhez. Esetünkben a lookup_value értéke 2, de a lookup_vector legnagyobb értéke 1, így a LOOKUP függvény a tömb utolsó 1 -jével egyezik, és az eredmény_vector megfelelő értékét adja vissza.
Ha biztos abban, hogy csak numerikus értékek vannak az oszlopban, az adatok az 1. sorból indulnak ki, az adattartomány pedig folyamatosan, akkor egy kicsit egyszerűbb képletet használhat az INDEX és a COUNT függvényekkel.
| 1 | = INDEX (B: B, COUNT (B: B)) |
A COUNT függvény a (4) folytonos tartományban lévő adatokkal töltött cellák számát adja vissza, és az INDEX függvény megadja a cella értékét a megfelelő sorban (4.).
Az esetleges hibák elkerülése érdekében, ha az adattartomány numerikus és nem numerikus értékek keverékét, vagy akár néhány üres cellát tartalmaz, használhatja a LOOKUP függvényt az ISBLANK és NOT funkciókkal együtt.
| 1 | = KERESÉS (2,1/(NEM (ISBLANK (B: B)))), B: B) |
Az ISBLANK függvény egy tömböt ad vissza, amely igaz és hamis értékeket tartalmaz, amelyek 1 -nek és 0 -nak felelnek meg. A NOT függvény igaz (azaz 1) értékét hamisra és hamis (azaz 0) értéket igaz értékre változtatja. Ha megfordítjuk ezt az eredményül kapott tömböt (amikor az 1 -et elosztjuk ezzel a tömbrel), akkor egy #DIV/0 értéket tartalmazó eredménytömböt kapunk! hibák és 1 -ek, amelyek keresési tömbként (lookup_vector) használhatók a LOOKUP függvényben. A LOOKUP függvény funkcionalitása ekkor ugyanaz, mint az első példánkban: az eredményvektor értékét adja vissza a keresési tömb utolsó 1 -es pozíciójában.
Ha vissza kell adnia az utolsó bejegyzéssel ellátott sorszámot, módosíthatja az első példánkban használt képletet az eredmény_vektor ROW függvényével együtt.
| 1 | = KERESÉS (2,1/(B: B ""), SOR (B: B)) |
Utolsó érték a sorban
Ha meg szeretné kapni az utolsó nem üres cella értékét egy numerikus adatokkal teli sorban, akkor érdemes hasonló módszert használni, de különböző funkciókkal: az OFFSET függvényt a MATCH és MAX funkciókkal együtt.
| 1 | = OFFSET (referencia, sorok, oszlopok) |
| 1 | = OFFSET (B2,0, MATCH (MAX (B2: XFD2)+1, B2: XFD2,1) -1) |
Lássuk, hogyan működik ez a képlet.
MATCH funkció
A MATCH függvény segítségével „megszámoljuk”, hogy hány cellaérték van 1 alatt, vagy a maximális érték a 2. sorban a B2 -től kezdődően.
| 1 | = MATCH (lookup_value, lookup_array, [match_type]) |
| 1 | = MATCH (MAX (B2: XFD2)+1, B2: XFD2,1) |
A MATCH függvény lookup_value értéke a 2 + 1 sor összes értékének maximális értéke. Mivel ez az érték nyilvánvalóan nem létezik a 2. sorban, és a match_type értéke 1 (kisebb vagy egyenlő, mint lookup_value), a MATCH függvény visszaadja a az utolsó „ellenőrzött” cella pozíciója a tömbben, vagyis a B2: XFD2 tartományban lévő adatokkal töltött cellák száma (az XFD az Excel újabb verzióinak legutolsó oszlopa).
OFFSET funkció
Ezután az OFFSET függvény segítségével megkapjuk ennek a cellának az értékét, amelynek pozícióját a MATCH függvény adta vissza.
| 1 | = OFFSET (B2,0, C4-1) |
