Relativ og Absolutt Cellreferanse, og Formatering

Innholdsfortegnelse:

Relativ og Absolutt Cellreferanse, og Formatering
Relativ og Absolutt Cellreferanse, og Formatering

Video: Relativ og Absolutt Cellreferanse, og Formatering

Video: Relativ og Absolutt Cellreferanse, og Formatering
Video: ✓ How to Share WIFI CONNECTED PHONE'S INTERNET to other Android Phone - YouTube 2024, Mars
Anonim
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 celle referanser arbeid vil tillate deg å få mest mulig ut av Excel regnearkene dine!
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 celle referanser arbeid vil tillate deg å få mest mulig 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 skal bare 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.
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 cellehenvisningen 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 rad eller kolonne 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å, 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 beveger den ned, øker radnummeret. Cellen som den peker på, referansen til cellen, endres også. Dette illustreres nedenfor:

Fortsetter 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.
Fortsetter 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å C5 viser "Jeg er C6."
Verdien som vises er 0 fordi celle C6 er tom. I celle C6 skriver vi "Jeg er C6" og nå C5 viser "Jeg er C6."
Image
Image

Eksempel: Tekstformel

La oss prøve et annet eksempel. Husk fra leksjon 2 hvor vi måtte dele et fullt navn til 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.
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:
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.
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?

Da vi kopierte cellene fra kolonne C til kolonne B, oppdaterte vi referanse en kolonne til venstre = RIGHT (A2, LEN (A2) - FIND (",", A2) - 1).
Da 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 henvisninger til A2 til kolonnen til venstre for A, men det er ingen kolonne til venstre for kolonne A. Så vet datamaskinen ikke hva du mener.

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

Image
Image

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:
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:
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:
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 kan se i eksemplet nedenfor, har vi i cell 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."
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.
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.
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:
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 i celle B1.
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 i 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.
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 gjør 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.
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 beholdning" og deretter "verdi av beholdning." Kopier kopien celle D2 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 rækken er relativ.
Dette er en blandet referanse fordi kolonnen er absolutt og rækken 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:

Et advarselsskjerm dukker opp:
Et 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.
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.
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.
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-programskjermen, velge omdøpe og skrive inn et nytt navn.
Du kan endre regnearknavnet til noe nyttig som "lån" eller "budsjett" ved å høyreklikke på fanen regneark vist nederst på Excel-programskjermen, velge omdøpe og skrive inn et nytt navn.
Eller du kan bare dobbeltklikke på fanen og endre navn på den.
Eller du kan bare dobbeltklikke på fanen og endre navn på den.

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:
Hvis vi ser på "lån" regnearket, kan vi se referansen til lånebeløpet:
Image
Image

Nestemann …

Vi håper du nå har en fast forståelse av celle referanser, inkludert relative, 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.

Anbefalt: