Last ned eksemplet på arbeidsbok
Denne opplæringen lærer deg hvordan du slår opp den siste verdien i kolonne eller rad i Excel.
Siste verdi i kolonne
Du kan bruke LOOKUP-funksjonen til å finne den siste ikke-tomme cellen i en kolonne.
| 1 | = OPLETNING (2,1/(B: B ""), B: B) |
La oss gå gjennom denne formelen.
Delen av formelen B: B ”” returnerer en matrise som inneholder True og False -verdier: {FALSE, TRUE, TRUE,…}. Å teste hver celle i kolonne B er tom (FALSE).
| 1 | = OPLETNING (2,1/({FALSK; SANN; SANN; SANN; SANN; FALSK; …), B: B) |
Disse boolske verdiene konverteres til 0 eller 1 og brukes til å dele 1.
| 1 | = OPLETNING (2, {#DIV/0!; 1; 1; 1; 1; 1;#DIV/0!;, B: B) |
Dette er oppslagsvektoren for LOOKUP -funksjonen. I vårt tilfelle er oppslagsverdi 2, men den største verdien i oppslagsvektoren er 1, så LOOKUP -funksjonen vil matche den siste 1 i rekken og returnere den tilsvarende verdien i result_vector.
Hvis du er sikker på at du bare har numeriske verdier i kolonnen din, starter dataene fra rad 1 og dataområdet er kontinuerlig, kan du bruke en litt enklere formel med INDEKS og TELL -funksjonene.
| 1 | = INDEKS (B: B, TELLING (B: B)) |
TELLING -funksjonen returnerer antall celler fylt med data i det kontinuerlige området (4), og INDEKS -funksjonen gir dermed verdien til cellen i den tilsvarende raden (4.).
For å unngå mulige feil når dataområdet ditt inneholder en blanding av numeriske og ikke-numeriske verdier, eller til og med noen tomme celler, kan du bruke LOOKUP-funksjonen sammen med ISBLANK- og NOT-funksjonene.
| 1 | = LOOKUP (2,1/(IKKE (ISBLANK (B: B))), B: B) |
ISBLANK -funksjonen returnerer en matrise som inneholder sanne og usanne verdier, tilsvarende 1 og 0. NOT -funksjonen endrer True (dvs. 1) til False og False (dvs. 0) til True. Hvis vi inverterer dette resulterende arrayet (når vi deler 1 med dette arrayet), får vi et resultatarray som inneholder igjen #DIV/0! feil og 1’er, som kan brukes som oppslagsmatrise (oppslag_vektor) i vår OPPFØLGINGSfunksjon. Funksjonaliteten til LOOKUP -funksjonen er da den samme som den var i vårt første eksempel: den returnerer verdien til resultatvektoren ved posisjonen til den siste 1 i oppslagsarrayen.
Når du trenger at radnummeret med den siste oppføringen skal returneres, kan du endre formelen som ble brukt i vårt første eksempel sammen med RAD -funksjonen i result_vector.
| 1 | = LOOKUP (2,1/(B: B ""), RAD (B: B)) |
Siste verdi i rad
For å få den siste ikke-tomme cellens verdi på rad fylt med numeriske data, vil du kanskje bruke en lignende tilnærming, men med forskjellige funksjoner: OFFSET-funksjonen sammen med MATCH- og MAX-funksjonene.
| 1 | = FORSKYTT (referanse, rader, kolonner) |
| 1 | = OFFSET (B2,0, MATCH (MAX (B2: XFD2)+1, B2: XFD2,1) -1) |
La oss se hvordan denne formelen fungerer.
MATCH -funksjon
Vi bruker MATCH -funksjonen til å "telle" hvor mange celleverdier som er under 1 + maksimumet for alle verdier i rad2 som starter fra B2.
| 1 | = MATCH (oppslagsverdi, oppslag_array, [match_type]) |
| 1 | = MATCH (MAX (B2: XFD2)+1, B2: XFD2,1) |
Oppslagsverdi for MATCH -funksjonen er maksimum for alle verdier i rad2 + 1. Siden denne verdien åpenbart ikke eksisterer i rad2 og match_type er satt til 1 (mindre enn eller lik oppslag_verdi), returnerer MATCH -funksjonen siste "kontrollerte" cellens posisjon i matrisen, det vil si antall celler fylt med data i området B2: XFD2 (XFD er den aller siste kolonnen i de nyere versjonene av Excel).
OFFSET -funksjon
Deretter bruker vi OFFSET -funksjonen for å få verdien av denne cellen, hvis posisjon ble returnert av MATCH -funksjonen.
| 1 | = OFFSET (B2,0, C4-1) |
