Prenesite primer delovnega zvezka
Ta vadnica vas bo naučila, kako poiskati zadnjo vrednost v stolpcu ali vrstici v Excelu.
Zadnja vrednost v stolpcu
S funkcijo LOOKUP lahko poiščete zadnjo prazno celico v stolpcu.
| 1 | = POGLED (2,1/(B: B ""), B: B) |
Pojdimo skozi to formulo.
Del formule B: B ”” vrne polje, ki vsebuje vrednosti True in False: {FALSE, TRUE, TRUE,…}, preskus vsake celice v stolpcu B je prazen (FALSE).
| 1 | = LOOKUP (2,1/({FALSE; TRUE; TRUE; TRUE; TRUE; TRUE; FALSE;…), B: B) |
Te logične vrednosti se pretvorijo v 0 ali 1 in se uporabljajo za deljenje 1.
| 1 | = POGLEDAJ (2, {#DIV/0!; 1; 1; 1; 1; 1;#DIV/0!;, B: B) |
To je lookup_vector za funkcijo LOOKUP. V našem primeru je lookup_value 2, vendar je največja vrednost v lookup_vector 1, zato se bo funkcija LOOKUP ujemala z zadnjo 1 v matriki in vrnila ustrezno vrednost v result_vector.
Če ste prepričani, da imate v stolpcu samo številske vrednosti, se vaši podatki začnejo od prve vrstice in obseg podatkov neprekinjeno, lahko uporabite nekoliko enostavnejšo formulo s funkcijami INDEX in COUNT.
| 1 | = INDEKS (B: B, COUNT (B: B)) |
Funkcija COUNT vrne število celic, napolnjenih s podatki v neprekinjenem obsegu (4), funkcija INDEX pa tako poda vrednost celice v tej ustrezni vrstici (4.).
Da bi se izognili morebitnim napakam, če obseg podatkov vsebuje mešanico številskih in neštevilskih vrednosti ali celo nekaj praznih celic, lahko uporabite funkcijo LOOKUP skupaj s funkcijami ISBLANK in NOT.
| 1 | = POGLED (2,1/(NE (ISBLANK (B: B))), B: B) |
Funkcija ISBLANK vrne polje, ki vsebuje vrednosti True in False, ki ustrezajo številkam 1 in 0. Funkcija NOT spremeni True (tj. 1) v False in False (tj. 0) v True. Če obrnemo to nastalo matriko (pri deljenju 1 s tem nizom), dobimo matriko rezultatov, ki spet vsebuje #DIV/0! napake in številke 1, ki jih je mogoče uporabiti kot iskalno polje (lookup_vector) v naši funkciji LOOKUP. Funkcionalnost funkcije LOOKUP je potem enaka kot v prvem primeru: vrne vrednost vektorja rezultata na položaju zadnje 1 v iskalnem nizu.
Ko potrebujete vrnitev številke vrstice z zadnjim vnosom, lahko spremenite formulo, uporabljeno v našem prvem primeru, skupaj s funkcijo ROW v vašem result_vector.
| 1 | = POGLED (2,1/(B: B ""), ROW (B: B)) |
Zadnja vrednost v vrstici
Če želite, da je vrednost zadnje prazne celice v vrstici napolnjena s številskimi podatki, boste morda želeli uporabiti podoben pristop, vendar z različnimi funkcijami: funkcijo OFFSET skupaj s funkcijami MATCH in MAX.
| 1 | = OFFSET (referenca, vrstice, stolpci) |
| 1 | = OFFSET (B2,0, MACCH (MAX (B2: XFD2)+1, B2: XFD2,1) -1) |
Poglejmo, kako deluje ta formula.
Funkcija MATCH
Funkcijo MATCH uporabljamo za "štetje", koliko vrednosti celic je pod 1 + največje vrednosti vseh vrednosti v vrstici 2, začenši od B2.
| 1 | = MATCH (iskalna_vrednost, lookup_array, [vrsta_vžiganja]) |
| 1 | = UJEMA (MAX (B2: XFD2)+1, B2: XFD2,1) |
Lookup_value funkcije MATCH je največja vrednost vseh vrednosti v vrstici2 + 1. Ker ta vrednost očitno ne obstaja v vrstici2 in je match_type nastavljen na 1 (manjši ali enak lookup_value), bo funkcija MATCH vrnila zadnji "preverjen" položaj celice v matriki, to je število celic, napolnjenih s podatki v območju B2: XFD2 (XFD je zadnji stolpec v novejših različicah Excela).
Funkcija OFFSET
Nato s funkcijo OFFSET dobimo vrednost te celice, katere položaj je vrnila funkcija MATCH.
| 1 | = OFFSET (B2,0, C4-1) |
