Med henvisning til den siste cellen

July 25  by Eliza

Det kan være ganger når du setter sammen en arbeidsbok og du ønsker å holde et sammendrag på ett regneark og detaljert informasjon på en annen. For eksempel, la oss anta Ark1 er din oppsummering regnearket, og du har detaljert informasjon om bankkontoer på Ark2. Når vi ser på detaljert informasjon, har du datoene i kolonne A, og balanserer for ulike kontoer i kolonner B, C og D. Dermed er detaljert informasjon en tabell som viser en løpende progresjon av bankinnskudd på forskjellige datoer.

I å sette sammen din oppsummering informasjon om Ark1, innser du at du trenger å referere til de siste tallene i kolonne B, C og D. Disse tallene representerer den seneste balanserer, og dermed er perfekt for din oppsummering. Hvordan gjør du det? Spesielt når du fortsetter å legge til informasjon i din detalj regneark over tid?

Egentlig er det flere måter å nærme seg problemet. (Det er vanligvis flere måter å løse eventuelle Excel problem.) En måte er å bruke FINN.RAD-funksjonen. På det punktet i sammendraget der du vil ha den nyeste balanse fra kolonne B i detalj (Ark2), ville du sette følgende formel:

= FINN.RAD (MAX (Ark2 $ A: $ A), Ark2 $ A: $ D, 2)

Å endre referanser for de andre to kontosaldoer, ville du bare endre det siste nummeret (2) til enten 3 (for kontoen i kolonne C) eller 4 (for kontoen i kolonne D). Funksjonen fungerer fordi den ser opp den maksimale verdien i kolonne A, som inneholder datoer. Det ser deretter i datatabellen (Sheet2 $ A:! $ D) og finner den riktige forskyvning for den ønskede kolonne.

Denne tilnærmingen fungerer fint, forutsatt at det ikke er noen datoer i kolonne A forbi de siste balanserer oppgitt. Hvis det er, så verdiene som returneres vil alltid være feil.

En annen måte å nærme seg problemet er å bruke INDEKS-funksjonen sammen med enten COUNT eller ANTALLA. Hvis detalj kolonner ikke inneholder noen tekst (selv i kolonneoverskriftene), så ville du bruke ANTALL-funksjonen. Hvis det er inkludert tekst, deretter COUNTA er foretrukket. På det punktet hvor du ønsker å inkludere den siste balanse fra kolonne B i detalj, vil du bruke følgende formel:

= INDEKS (Ark2 B: B, ANTALLA (Ark2 B: B))

Det ser ut til bordet, bestemmer antallet ikke-tomme celler i kolonne B, og deretter trekker figuren fra det siste ikke-tom celle. Å tilpasse formelen for kolonner C og D, bare endre B-referanser til den aktuelle C eller D.

Nok en måte å håndtere problemet på er å bruke OFFSET funksjon, som i følgende:

= FORSKYVNING (Ark2 B1, ANTALLA (Ark2 B:! B) -1,0)

Denne funksjonen returnerer verdien av en celle som er forskjøvet fra en basisreferansecelle. I dette tilfellet er base celle B1 Ark2!. Den COUNTA funksjon blir brukt til å bestemme hvor mange rader å forskjøvet fra bunnen, og 0 angir at forskyvningen skal være i den samme kolonne som basisreferanse. Å endre formelen for kolonner C og D, bare endre alle referanser til B til enten C eller D.

ExcelTips er din kilde for kostnadseffektiv Microsoft Excel trening. Dette tipset (12470) gjelder for Microsoft Excel 2007 og 2010. Du kan finne en versjon av dette tipset for den eldre menygrensesnittet av Excel her: Med henvisning til den siste cellen.