Atsisiųskite darbaknygės pavyzdį
Ši pamoka išmokys jus, kaip ieškoti paskutinės „Excel“ stulpelio ar eilutės vertės.
Paskutinė vertė stulpelyje
Norėdami rasti paskutinį tuščią langelį stulpelyje, galite naudoti funkciją LOOKUP.
| 1 | = ŽIŪRĖTI (2,1/(B: B ""), B: B) |
Eikime per šią formulę.
Formulės B dalis: B ““ grąžina masyvą, kuriame yra teisingos ir klaidingos reikšmės: {FALSE, TRUE, TRUE,…}, bandant kiekvieną B stulpelio langelį (FALSE).
| 1 | = IEŠKOTIS (2,1/({FALSE; TRUE; TRUE; TRUE; TRUE; TRUE; FALSE;…), B: B) |
Šios loginės vertės konvertuojamos į 0 arba 1 ir naudojamos dalinti 1.
| 1 | = ŽIŪRĖTI (2, {#DIV/0!; 1; 1; 1; 1; 1; 1;#DIV/0!; B: B) |
Tai yra funkcijos LOOKUP lookup_vector. Mūsų atveju „lookup_value“ yra 2, tačiau didžiausia „lookup_vector“ reikšmė yra 1, todėl funkcija „LOOKUP“ atitiks paskutinį 1 masyve ir grąžins atitinkamą „value_vector“ reikšmę.
Jei esate tikri, kad stulpelyje yra tik skaitinės vertės, jūsų duomenys prasideda nuo 1 eilutės, o duomenų diapazonas - nepertraukiamas, galite naudoti šiek tiek paprastesnę formulę su funkcijomis INDEX ir COUNT.
| 1 | = INDEKSAS (B: B, COUNT (B: B)) |
Funkcija COUNT grąžina langelių, užpildytų duomenimis ištisinio diapazono (4), skaičių, o INDEX funkcija pateikia šios atitinkamos eilutės (4) langelio vertę.
Kad išvengtumėte galimų klaidų, kai jūsų duomenų diapazone yra skaitinių ir ne skaitinių reikšmių mišinys arba net kai kurie tušti langeliai, galite naudoti funkciją LOOKUP kartu su funkcijomis ISBLANK ir NOT.
| 1 | = IEŠKOTIS (2,1/(NE (ISBLANK (B: B))), B: B) |
Funkcija ISBLANK grąžina masyvą, kuriame yra teisingų ir klaidingų reikšmių, atitinkančių 1 ir 0. Funkcija NOT keičia teisingą (t. Y. 1) į klaidingą, o klaidingą (t. Y. 0) į teisingą. Jei apverstume šį gautą masyvą (dalijant 1 iš šio masyvo), gauname rezultatų masyvą, kuriame vėl yra #DIV/0! klaidų ir 1, kurie gali būti naudojami kaip paieškos masyvas (lookup_vector) mūsų LOOKUP funkcijoje. Funkcijos LOOKUP funkcionalumas yra toks pat, koks buvo mūsų pirmojo pavyzdžio atveju: jis grąžina rezultatų vektoriaus vertę paskutinės 1 paieškos masyvo padėtyje.
Kai reikia grąžinti eilutės numerį su paskutiniu įrašu, galite pakeisti pirmame pavyzdyje naudotą formulę kartu su ROW funkcija eilutėje „result_vector“.
| 1 | = ŽIŪRĖTI (2,1/(B: B ""), EILUTĖ (B: B)) |
Paskutinė vertė eilutėje
Norėdami gauti paskutinės tuščios ląstelės reikšmę eilutėje, užpildytoje skaitmeniniais duomenimis, galbūt norėsite naudoti panašų metodą, tačiau turėsite skirtingas funkcijas: funkciją OFFSET kartu su MATCH ir MAX funkcijomis.
| 1 | = OFFSET (nuoroda, eilutės, stulpeliai) |
| 1 | = OFFSET (B2,0, MATCH (MAX (B2: XFD2)+1, B2: XFD2,1) -1) |
Pažiūrėkime, kaip veikia ši formulė.
Funkcija MATCH
Mes naudojame funkciją „MATCH“, kad „suskaičiuotume“, kiek langelių reikšmių yra mažesnės nei 1 + visų 2 eilutės verčių, pradedant B2.
| 1 | = MATCH (lookup_value, lookup_array, [match_type]) |
| 1 | = MATCH (MAX (B2: XFD2)+1, B2: XFD2,1) |
Funkcijos MATCH_value vertė yra didžiausia iš visų 2 + 1 eilutės verčių. Kadangi šios reikšmės akivaizdžiai nėra 2 eilutėje, o match_type nustatyta 1 (mažesnė arba lygi lookup_value), funkcija MATCH grąžins paskutinė „patikrinta“ langelio padėtis masyve, tai yra ląstelių, užpildytų duomenimis B2: XFD2 diapazone, skaičius (XFD yra paskutinis naujesnių „Excel“ versijų stulpelis).
OFFSET funkcija
Tada mes naudojame funkciją OFFSET, kad gautume šios ląstelės, kurios padėtį grąžino funkcija MATCH, vertę.
| 1 | = OFFSET (B2,0, C4-1) |
