Relativ og Absolut Cell Reference og Formatering

I denne lektion diskuteres cellehenvisninger, hvordan man kopierer eller flytter en formel og formaterer celler. Til at begynde med, lad os præcisere, hvad vi mener ved cellehenvisninger, der understøtter en stor del af magt og alsidighed af formler og funktioner. En konkret forståelse for, hvordan cellehenvisninger arbejde giver dig mulighed for at få mest muligt ud af dine Excel-regneark!

SCHOOL NAVIGATION

1. Hvorfor har du brug for formler og funktioner?
2. Definere og oprette en formel
3. Relativ og Absolut Cell Reference og Formatering
4. Nyttige funktioner, du bør lære at vide
5. Opslag, diagrammer, statistikker og drejeborde

Bemærk: vi antager bare, at du allerede ved, at en celle er et af kvadraterne i regnearket, indrettet i kolonner og rækker, der henvises af bogstaver og tal, der løber vandret og lodret.

Hvad er en cellehenvisning?

En "cellehenvisning" betyder den celle, hvortil en anden celle refererer. For eksempel, hvis i celle A1 du har = A2. Så henviser A1 til A2.

Lad os se, hvad vi sagde i lektion 2 om rækker og kolonner, så vi kan udforske cellehenvisninger yderligere.

Celler i regnearket omtales af rækker og kolonner. Kolonner er lodrette og mærket med bogstaver. Rækker er vandrette og mærket med tal.

Den første celle i regnearket er A1, hvilket betyder kolonne A, række 1, B3 henviser til cellen placeret i den anden kolonne, tredje række og så videre.

Til læringsformål om cellehenvisninger vil vi til tider skrive dem som række, kolonne, dette er ikke gyldigt notation i regnearket og er simpelthen beregnet til at gøre tingene klarere.

Typer af cellehenvisninger

Der er tre typer cellehenvisninger.

Absolut - Det betyder, at cellehenvisningen forbliver den samme, hvis du kopierer eller flytter cellen til en anden celle. Dette gøres ved at forankre rækken og kolonnen, så den ændres ikke, når den kopieres eller flyttes.

Relativ - Relativ henvisning betyder at celleadressen ændres, når du kopierer eller flytter den; dvs. cellehenvisningen er i forhold til dens placering.

Blandet - Det betyder, at du kan vælge at forankre enten rækken eller kolonnen, når du kopierer eller flytter cellen, så den ene ændres og den anden ikke. For eksempel kan du forankre rækken reference, derefter flytte en celle ned to rækker og over fire kolonner og rækken reference forbliver den samme. Vi vil forklare dette nærmere nedenfor.

Relative References

Lad os henvise til det tidligere eksempel - formoder i celle A1, at vi har en formel, der simpelthen siger = A2. Det betyder Excel-output i celle A1, hvad der er indlæst i celle A2. I celle A2 har vi skrevet "A2", så Excel viser værdien "A2" i celle A1.

Antag nu, at vi skal gøre plads i vores regneark for flere data. Vi skal tilføje kolonner ovenover og rækker til venstre, så vi skal flytte cellen ned og til højre for at gøre plads.

Når du flytter cellen til højre, øges kolonneantalet. Når du flytter den ned, stiger rækkenummeret. Cellen, som den peger på, cellehenvisningen, ændres også. Dette illustreres nedenfor:

Fortsæt med vores eksempel og se på nedenstående grafik, hvis du kopierer indholdet af celle A1 to til højre og fire ned, har du flyttet det til celle C5.

Vi kopierede celle to kolonner til højre og fire ned. Det betyder, at vi har ændret cellen, den refererer til to på tværs og fire ned. A1 = A2 er nu C5 = C6. I stedet for at henvise til A2 henviser cellen C5 nu til celle C6.

Den viste værdi er 0, fordi celle C6 er tom. I celle C6 skriver vi "Jeg er C6" og nu viser C5 "Jeg er C6".

Eksempel: Tekstformel

Lad os prøve et andet eksempel. Husk fra lektion 2, hvor vi var nødt til at opdele et fuldt navn i for- og efternavn? Hvad sker der, når vi kopierer denne formel?

Skriv formlen = RIGHT (A3, LEN (A3) - FIND (",", A3) - 1) eller kopier teksten til celle C3. Du må ikke kopiere den faktiske celle, kun teksten, kopiere teksten, ellers vil den opdatere referencen.

Du kan redigere indholdet af en celle øverst i et regneark i feltet ud for, hvor der står "fx." Denne boks er længere end en celle er bred, så det er lettere at redigere.

Nu har vi:

Intet kompliceret, vi har lige skrevet en ny formel til celle C3. Kopier nu C3 til cellerne C2 og C4. Overhold nedenstående resultater:

Nu har vi Alexander Hamilton og Thomas Jefferson's fornavne.

Brug markøren til at markere cellerne C2, C3 og C4. Peg markøren til celle B2 og indsæt indholdet. Se på, hvad der skete - vi får en fejl: "#REF." Hvorfor er dette?

Da vi kopierede cellerne fra kolonne C til kolonne B, opdaterede vi den reference en kolonne til venstre = RIGHT (A2, LEN (A2) - FIND (",", A2) - 1).

Det ændrede enhver henvisning til A2 til kolonnen til venstre for A, men der er ingen kolonne til venstre for kolonne A. Så computeren ved ikke, hvad du mener.

Den nye formel i B2 er f.eks. = RIGHT (#REF!, LEN (#REF!) - FIND (",", # REF!) - 1) og resultatet er #REF:

Kopiering af en formel til et udvalg af celler

Kopiering af celler er meget praktisk, fordi du kan skrive en formel og kopiere den til et stort område, og referencen opdateres. Dette undgår at skulle redigere hver celle for at sikre, at den peger på det rigtige sted.

Med "interval" menes mere end en celle. For eksempel betyder (C1: C10) alle celler fra celle C1 til celle C10. Så det er en kolonne af celler. Et andet eksempel (A1: AZ1) er den øverste række fra kolonne A til kolonne AZ.

Hvis et interval krydser fem kolonner og ti rækker, angiver du området ved at skrive den øverste venstre celle og bunden til højre, for eksempel A1: E10. Dette er et firkantet område, der krydser rækker og kolonner og ikke kun en del af en kolonne eller en del af en række.

Her er et eksempel, der illustrerer, hvordan man kopierer en celle til flere steder. Antag, at vi vil vise vores forventede udgifter til måneden i et regneark, så vi kan lave et budget. Vi laver et regneark som dette:

Kopier nu formlen i celle C3 (= B3 + C2) til resten af ​​kolonnen for at give en løbende balance for vores budget. Excel opdaterer cellehenvisningen, som du kopierer den. Resultatet er vist nedenfor:

Som du kan se, opdateres hver nye celle i forhold til den nye placering, så C4 opdaterer sin formel til = B4 + C3:

Celle C5 opdateringer til = B5 + C4, og så videre:

Absolutte Referencer

En absolut reference ændres ikke, når du flytter eller kopierer en celle. Vi bruger $ tegnet til at gøre en absolut reference - at huske det, tænk på et dollar tegn som et anker.

For eksempel indtast formlen = $ A $ 1 i en celle. $ Foran kolonnen A betyder, at kolonnen ikke ændres, $ foran rækken 1 betyder, at kolonnen ikke ændres, når du kopierer eller flytter cellen til en anden celle.

Som du kan se i eksemplet nedenfor, i cellen B1 har vi en relativ reference = A1.Når vi kopierer B1 til de fire celler under den, ændres den relative reference = A1 til cellen til venstre, så B2 bliver A2, B3 bliver A3 osv. Disse celler har tydeligvis ingen værdi input, så output er nul.

Men hvis vi bruger = $ A1 $ 1, som i C1, og vi kopierer det til de fire celler under det, er referencen absolut, så den ændres aldrig, og outputen er altid lig med værdien i celle A1.

Antag at du holder øje med din interesse, som i eksemplet nedenfor. Formlen i C4 = B4 * B1 er "rente" * "balance" = "rente pr. År."

Nu har du ændret dit budget og har sparet yderligere $ 2000 for at købe en fond. Antag, at det er en fast rentefond, og den betaler den samme rentesats. Indtast den nye konto og balance i regnearket og kopier derefter formlen = B4 * B1 fra celle C4 til celle C5.

Det nye budget ser sådan ud:

Den nye investeringsfond tjener $ 0 i renter pr. År, hvilket ikke kan være rigtigt, da renten er klart 5 procent.

Excel fremhæver de celler, som en formel refererer til. Du kan se ovenfor, at henvisningen til renten (B1) flyttes til den tomme celle B2. Vi skulle have henvist til B1 absolut ved at skrive $ B $ 1 ved hjælp af dollarsignalet for at forankre rækken og kolonnehenvisningen.

Omskriv den første beregning i C4 for at læse = B4 * $ B $ 1 som vist nedenfor:

Kopier derefter denne formel fra C4 til C5. Regnearket ser nu sådan ud:

Da vi kopierede formlen en celle ned, dvs. forøget rækken med en, er den nye formel = B5 * $ B $ 1. Fondets rentesats beregnes korrekt nu, fordi renten er forankret i celle B1.

Dette er et godt eksempel på, hvornår du kan bruge et "navn" til at referere til en celle. Et navn er en absolut reference. Hvis du f.eks. Skal tildele navnet "rentesats" til celle B1, højreklik på cellen og vælg derefter "definer navn".

Navne kan referere til en celle eller en rækkevidde, og du kan bruge et navn i en formel, for eksempel = interest_rate * 8 er det samme som at skrive = $ B $ 1 * 8.

Blandede referencer

Blandede referencer er hvornår enten rækken eller søjlen er forankret.

Antag for eksempel, at du er en landmand, der laver et budget. Du ejer også en foderbutik og sælger frø. Du skal plante majs, sojabønner og lucerne. Regnearket nedenfor viser prisen pr. Acre. "Pris pr. Acre" = "pris pr. Pund" * "pund frø pr. Acre" - det er det, det koster dig at plante en acre.

Indtast prisen pr. Acre som = $ B2 * C2 i celle D2. Du siger at du vil forankre prisen pr. Pund kolonne. Kopier derefter denne formel til de andre rækker i samme kolonne:

Nu vil du gerne vide værdien af ​​din fortegnelse over frø. Du har brug for prisen pr. Pund og antallet af pund i beholdningen for at kende værdien af ​​opgørelsen.

Vi tilføjer to kolonner: "pund frø i lager" og derefter "værdi af lager". Kopier nu cellen D2 til F4 og bemærk at rækken reference i den første del af den oprindelige formel ($ B2) opdateres til række 4 men kolonnen forbliver løst, fordi $ forankrer den til "B."

Dette er en blandet reference, fordi kolonnen er absolut og rækken er relativ.

Cirkulære Referencer

En cirkulær reference er, når en formel refererer til sig selv.

For eksempel kan du ikke skrive c3 = c3 + 1. Denne form for beregning hedder "iteration", hvilket betyder at det gentager sig selv. Excel understøtter ikke iteration, fordi det kun beregner alt kun én gang.

Hvis du forsøger at gøre dette, skal du skrive SUM (B1: B5) i celle B5:

Der vises en advarselsskærm:

Excel fortæller kun, at du har en cirkulær reference nederst på skærmen, så du måske ikke bemærker det. Hvis du har en cirkulær reference og lukker et regneark og åbner det igen, vil Excel fortælle dig i et pop op-vindue, at du har en cirkulær reference.

Hvis du har en cirkulær reference, hver gang du åbner regnearket, fortæller Excel dig med det pop op-vindue, at du har en cirkulær reference.

Referencer til andre regneark

En "workbook" er en samling af "regneark." Det betyder simpelthen, at du kan have flere regneark (regneark) i samme Excel-fil (workbook). Som du kan se i eksemplet nedenfor har vores eksempel arbejdsbog mange regneark (i rødt).

Regneark som standard hedder Sheet1, Sheet2 og så videre. Du opretter en ny ved at klikke på "+" nederst på Excel-skærmen.

Du kan ændre regnearkets navn til noget nyttigt som "lån" eller "budget" ved at højreklikke på fanen regneark vist nederst på Excel-programskærmen, vælge omdøb og skriv et nyt navn.

Eller du kan simpelthen dobbeltklikke på fanen og omdøbe den.

Syntaxen for en regnearkreference er = regneark! Celle. Du kan bruge denne form for reference, når samme værdi bruges i to regneark, eksempler på det kan være:

• Dags dato
• Valutaomregningskurs fra dollar til euro
• Alt der er relevant for alle regnearkene i projektmappen

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

Hvis vi ser på "lån" regneark, kan vi se referencen til lånebeløbet:

Kommer op næste ...

Vi håber, at du nu har en fast forståelse af cellehenvisninger, herunder relative, absolutte og blandede. Der er helt sikkert meget.

Det er det til dagens lektion, i lektion 4 vil vi diskutere nogle nyttige funktioner, du måtte ønske at vide for daglig Excel brug.