Relativ og Absolutt Cellreferanse, og Formatering

I denne leksjonen diskuterer vi cellehenvisninger, hvordan du kopierer eller flytter en formel og formaterer celler. For å begynne med, la oss avklare hva vi mener med cellehenvisninger, som ligger til grunn for kraften og allsidigheten til formler og funksjoner. En konkret forståelse av hvordan cellehenvisninger fungerer, vil gi deg det beste ut av Excel-regnearkene dine!

SCHOOL NAVIGASJON

1. Hvorfor trenger du formler og funksjoner?
2. Definere og opprette en formel
3. Relativ og Absolutt Cellreferanse, og Formatering
4. Nyttige funksjoner du bør bli kjent med
5. Oppslag, diagrammer, statistikk og pivottabeller

Merk: vi kommer bare til å anta at du allerede vet at en celle er en av rutene i regnearket, ordnet i kolonner og rader som refereres av bokstaver og tall som løper horisontalt og vertikalt.

Hva er en cellehenvisning?

En "cellehenvisning" betyr cellen som en annen celle refererer til. For eksempel, hvis i celle A1 har du = A2. Da refererer A1 til A2.

La oss se gjennom hva vi sa i leksjon 2 om rader og kolonner, slik at vi kan utforske cellehenvisninger videre.

Celler i regnearket refereres til av rader og kolonner. Kolonner er vertikale og merket med bokstaver. Rader er horisontale og merket med tall.

Den første cellen i regnearket er A1, som betyr kolonne A, rad 1, B3 refererer til cellen som ligger i den andre kolonnen, tredje rad, og så videre.

For læringsformål om cellehenvisninger, vil vi til tider skrive dem som rad, kolonne, dette er ikke gyldig notasjon i regnearket og er bare ment å gjøre ting klarere.

Typer av cellehenvisninger

Det er tre typer cellehenvisninger.

Absolutt - Dette betyr at cellen referansen forblir den samme hvis du kopierer eller flytter cellen til en annen celle. Dette gjøres ved å forankre raden og kolonnen, slik at den ikke endres når den kopieres eller flyttes.

Relativ - Relativ referanse betyr at celleadressen endres når du kopierer eller flytter den; dvs. cellen referansen er i forhold til sin plassering.

Blandet - Dette betyr at du kan velge å forankre enten raden eller kolonnen når du kopierer eller flytter cellen, slik at den endres og den andre ikke gjør det. For eksempel kan du forankre radreferansen, deretter flytte en celle ned to rader og over fire kolonner, og radreferansen forblir den samme. Vi vil forklare dette nærmere nedenfor.

Relative Referanser

La oss referere til det tidligere eksemplet - antar at i celle A1 har vi en formel som bare sier = A2. Det betyr at Excel-utgang i celle A1 er innført i celle A2. I celle A2 har vi skrevet "A2" slik at Excel viser verdien "A2" i celle A1.

Nå, anta at vi må gjøre plass i regnearket vårt for mer data. Vi må legge til kolonner over og rader til venstre, så vi må flytte cellen ned og til høyre for å få plass.

Når du beveger cellen til høyre, øker kolonnnummeret. Når du flytter den ned, øker radnummeret. Cellen som den peker på, cellehenvisningen, endres også. Dette illustreres nedenfor:

Fortsett med vårt eksempel, og se på grafikken nedenfor, hvis du kopierer innholdet i celle A1 to til høyre og fire ned, har du flyttet det til celle C5.

Vi kopierte celle to kolonner til høyre og fire ned. Dette betyr at vi har endret cellen den refererer to på tvers og fire ned. A1 = A2 er nå C5 = C6. I stedet for å referere til A2, refererer cellen C5 nå til celle C6.

Verdien som vises er 0 fordi celle C6 er tom. I celle C6 skriver vi «Jeg er C6» og nå viser C5 "Jeg er C6."

Eksempel: Tekstformel

La oss prøve et annet eksempel. Husk fra leksjon 2 hvor vi måtte dele et fullt navn i for- og etternavn? Hva skjer når vi kopierer denne formelen?

Skriv formelen = RIGHT (A3, LEN (A3) - FIND (",", A3) - 1) eller kopier teksten til celle C3. Ikke kopier selve cellen, bare teksten, kopier teksten, ellers vil den oppdatere referansen.

Du kan redigere innholdet i en celle øverst i et regneark i boksen ved siden av hvor det står "fx." Den boksen er lengre enn en celle er bred, så det er lettere å redigere.

Nå har vi:

Ingenting komplisert, vi har nettopp skrevet en ny formel i celle C3. Kopier nå C3 til celler C2 og C4. Følg resultatene nedenfor:

Nå har vi Alexander Hamilton og Thomas Jefferson fornavn.

Bruk markøren til å markere celler C2, C3 og C4. Pek markøren til celle B2 og lim inn innholdet. Se på hva som skjedde - vi får en feil: "#REF." Hvorfor er dette?

Når vi kopierte cellene fra kolonne C til kolonne B, oppdaterte vi referanse en kolonne til venstre = RIGHT (A2, LEN (A2) - FIND (",", A2) - 1).

Det endret alle referanser til A2 til kolonnen til venstre for A, men det er ingen kolonne til venstre for kolonne A. Så datamaskinen vet ikke hva du mener.

Den nye formelen i B2 for eksempel er = RIGHT (#REF!, LEN (#REF!) - FIND (",", # REF!) - 1) og resultatet er #REF:

Kopiere en formel til et utvalg av celler

Kopiering av celler er veldig nyttig fordi du kan skrive en formel og kopiere den til et stort område, og referansen er oppdatert. Dette unngår å måtte redigere hver celle for å sikre at den peker til riktig sted.

Med "rekkevidde" menes mer enn en celle. For eksempel betyr (C1: C10) alle cellene fra celle C1 til celle C10. Så det er en kolonne av celler. Et annet eksempel (A1: AZ1) er den øverste raden fra kolonne A til kolonne AZ.

Hvis et område krysser fem kolonner og ti rader, angir du området ved å skrive den øverste venstre cellen og nederst til høyre, for eksempel A1: E10. Dette er et firkantet område som krysser rader og kolonner og ikke bare en del av en kolonne eller en del av en rad.

Her er et eksempel som illustrerer hvordan du kopierer en celle til flere steder. Anta at vi vil vise våre projiserte utgifter for måneden i et regneark slik at vi kan lage et budsjett. Vi lager et regneark slik:

Kopier nå formelen i celle C3 (= B3 + C2) til resten av kolonnen for å gi en løpende balanse for budsjettet. Excel oppdaterer cellehenvisningen når du kopierer den. Resultatet er vist nedenfor:

Som du kan se, oppdateres hver nye celle slektning til den nye plasseringen, slik at celle C4 oppdaterer sin formel til = B4 + C3:

Cell C5 oppdateringer til = B5 + C4, og så videre:

Absolute Referanser

En absolutt referanse endres ikke når du flytter eller kopierer en celle. Vi bruker $ -tegnet for å gjøre en absolutt referanse - å huske det, tenk på et dollarskilt som et anker.

For eksempel, skriv inn formelen = $ A $ 1 i en hvilken som helst celle. $ Foran kolonnen A betyr ikke endre kolonnen, $ foran rad 1 betyr ikke endre kolonnen når du kopierer eller flytter cellen til en annen celle.

Som du ser i eksemplet nedenfor, har vi i cellen B1 en relativ referanse = A1. Når vi kopierer B1 til de fire cellene under den, endres den relative referansen = A1 til cellen til venstre, så B2 blir A2, B3 bli A3 osv. Disse cellene har åpenbart ingen verdi innført, så utgangen er null.

Men hvis vi bruker = $ A1 $ 1, som i C1, og vi kopierer den til de fire cellene under den, er referansen absolutt, slik at den aldri endres, og utgangen er alltid lik verdien i celle A1.

Anta at du holder styr på interessen din, som i eksemplet nedenfor. Formelen i C4 = B4 * B1 er "renten" * "balanse" = "rente per år."

Nå har du endret budsjettet ditt og har lagret ytterligere $ 2000 for å kjøpe et fond. Anta at det er et fastrentefond, og det betaler samme rente. Skriv inn den nye kontoen og balanser inn i regnearket og kopier deretter formelen = B4 * B1 fra celle C4 til celle C5.

Det nye budsjettet ser slik ut:

Det nye fondet tjener $ 0 i renter per år, noe som ikke kan være riktig siden renten er klart 5 prosent.

Excel fremhever cellene som en formel refererer til. Du kan se over at referansen til renten (B1) er flyttet til den tomme cellen B2. Vi burde ha referert til absolutt B1 ved å skrive $ B $ 1 ved hjelp av dollar-tegnet for å forankre rad- og kolonnereferansen.

Skriv om den første beregningen i C4 for å lese = B4 * $ B $ 1 som vist nedenfor:

Kopier deretter denne formelen fra C4 til C5. Regnearket ser nå slik ut:

Siden vi kopierte formelen en celle ned, dvs. økt rekke for en, er den nye formelen = B5 * $ B $ 1. Fondets rentesats er beregnet riktig nå, fordi renten er forankret til celle B1.

Dette er et godt eksempel på når du kan bruke et "navn" for å referere til en celle. Et navn er en absolutt referanse. For eksempel, for å tildele navnet "rente" til celle B1, høyreklikk cellen og velg deretter "definer navn".

Navnene kan referere til en celle eller et område, og du kan bruke et navn i en formel, for eksempel = interest_rate * 8 er det samme som å skrive = $ B $ 1 * 8.

Blandede referanser

Blandede referanser er når enten rekken eller kolonnen er forankret.

For eksempel, anta at du er en bonde som lager et budsjett. Du eier også en matbutikk og selger frø. Du skal plante korn, soyabønner og alfalfa. Regnearket nedenfor viser prisen per acre. "Kostnaden per acre" = "pris per pund" * "pund av frø per acre" - det er det det vil koste deg å plante en acre.

Angi kostnaden per acre som = $ B2 * C2 i celle D2. Du sier at du vil forankre prisen per pund kolonne. Kopier deretter denne formelen til de andre radene i samme kolonne:

Nå vil du vite verdien av inventar av frø. Du trenger prisen per pund og antall pounds i lager for å vite verdien av beholdningen.

Vi legger til to kolonner: "pund av frø i lager" og deretter "verdi av lager". Kopier kopien C2 til F4 og merk at radreferansen i den første delen av den opprinnelige formelen ($ B2) er oppdatert til rad 4 men kolonnen forblir løst fordi $ forankrer den til "B."

Dette er en blandet referanse fordi kolonnen er absolutt og raden er relativ.

Sirkulære referanser

En sirkulær referanse er når en formel refererer til seg selv.

For eksempel kan du ikke skrive c3 = c3 + 1. Denne typen beregning kalles "iterasjon" som betyr at det gjentar seg selv. Excel støtter ikke iterasjon fordi det beregner alt bare én gang.

Hvis du prøver å gjøre dette ved å skrive SUM (B1: B5) i celle B5:

En advarselsskjerm dukker opp:

Excel forteller deg bare at du har en sirkulær referanse nederst på skjermen, slik at du kanskje ikke merker det. Hvis du har en sirkulær referanse og lukker et regneark og åpner det igjen, vil Excel fortelle deg i et popup-vindu som du har en sirkulær referanse.

Hvis du har en sirkulær referanse, vil hver gang du åpner regnearket, fortelle deg med det popup-vinduet at du har en sirkulær referanse.

Referanser til andre regneark

En "arbeidsbok" er en samling av "regneark." Enkelt sagt betyr dette at du kan ha flere regneark (regneark) i samme Excel-fil (arbeidsbok). Som du kan se i eksemplet nedenfor, har vårt eksempel arbeidsbok mange regneark (i rødt).

Regneark er som standard Sheet1, Sheet2 og så videre. Du lager en ny ved å klikke på "+" nederst på Excel-skjermen.

Du kan endre regnearknavnet til noe nyttig som "lån" eller "budsjett" ved å høyreklikke på fanen regneark vist nederst på Excel-programskjermbildet, velge omdøpe og skrive inn et nytt navn.

Eller du kan bare dobbeltklikke på fanen og gi den navnet på nytt.

Syntaxen for et regneark referanse er = regneark! Celle. Du kan bruke denne typen referanse når samme verdi brukes i to regneark, eksempler på det kan være:

• Dagens dato
• Valutakursen fra dollar til euro
• Alt som er relevant for alle regnearkene i arbeidsboken

Nedenfor er et eksempel på regneark "interesse" som refererer til regneark "lån", celle B1.

Hvis vi ser på "lån" regnearket, kan vi se referansen til lånebeløpet:

Nestemann…

Vi håper du nå har en fast forståelse av cellehenvisninger, inkludert relativ, absolutt og blandet. Det er absolutt mye.

Det er det for dagens leksjon, i leksjon 4 vil vi diskutere noen nyttige funksjoner du kanskje vil vite for daglig Excel-bruk.