Relativ och Absolut Cellreferens och Formatering

I denna lektion diskuteras cellreferenser, hur man kopierar eller flyttar en formel och formaterar celler. Till att börja med, låt oss förtydliga vad vi menar med cellreferenser, vilket underbygger mycket av kraften och mångsidigheten hos formler och funktioner. En konkret förståelse för hur cellreferenser fungerar kan du få ut mesta möjliga av dina Excel-kalkylblad!

SCHOOL NAVIGATION

1. Varför behöver du formler och funktioner?
2. Definiera och skapa en formel
3. Relativ och Absolut Cellreferens och Formatering
4. Användbara funktioner du bör lära känna
5. Sökningar, diagram, statistik och pivottabeller

Notera: vi kommer bara att anta att du redan vet att en cell är en av rutorna i kalkylbladet, ordnade i kolumner och rader som refereras av bokstäver och siffror som löper horisontellt och vertikalt.

Vad är en cellreferens?

En "cellreferens" betyder cellen till vilken en annan cell hänvisar. Till exempel, om i cell A1 har du = A2. Då hänvisar A1 till A2.

Låt oss granska vad vi sa i lektion 2 om rader och kolumner så att vi kan utforska cellreferenser ytterligare.

Celler i kalkylbladet refereras till av rader och kolumner. Kolumnerna är vertikala och märkta med bokstäver. Raderna är horisontella och märkta med siffror.

Den första cellen i kalkylbladet är A1, vilket betyder kolumn A, rad 1, B3 avser cellen som ligger i den andra kolumnen, tredje raden och så vidare.

För lärande ändamål om cellreferenser, kommer vi ibland att skriva dem som rad, kolumn, detta är inte giltigt notation i kalkylbladet och är helt enkelt menat att göra saker tydligare.

Typer av cellreferenser

Det finns tre typer av cellreferenser.

Absolut - Det betyder att cellreferensen förblir densamma om du kopierar eller flyttar cellen till någon annan cell. Detta görs genom att förankra raden och kolumnen så att den inte ändras när den kopieras eller flyttas.

Relativ - Relativ referens innebär att celladressen ändras när du kopierar eller flyttar den; d.v.s. cellreferensen är i förhållande till dess plats.

Blandad - Det betyder att du kan välja att ankora antingen raden eller kolumnen när du kopierar eller flyttar cellen, så att den ändras och den andra inte gör det. Du kan till exempel förankra radreferensen och flytta sedan en cell ner två rader och över fyra kolumner och radreferensen förblir densamma. Vi kommer att förklara detta ytterligare nedan.

Relativa referenser

Låt oss hänvisa till det tidigare exemplet - anta att i cell A1 har vi en formel som helt enkelt säger = A2. Det betyder Excel-utgång i cell A1, vilken som helst inmatas i cell A2. I cell A2 har vi skrivit "A2" så Excel visar värdet "A2" i cell A1.

Anta nu att vi behöver göra plats i vårt kalkylblad för mer data. Vi måste lägga till kolumner ovanför och rader åt vänster, så vi måste flytta cellen ner och till höger för att göra rum.

När du flyttar cellen till höger ökar kolonnnumret. När du flyttar ner, ökar radnumret. Den cell som den pekar på, cellreferensen ändras också. Detta illustreras nedan:

Fortsätt med vårt exempel och titta på grafen nedan om du kopierar innehållet i cell A1 två till höger och fyra ner har du flyttat det till cell C5.

Vi kopierade cellens två kolumner till höger och fyra ner. Det betyder att vi har ändrat cellen den refererar två över och fyra ner. A1 = A2 är nu C5 = C6. I stället för att referera till A2 hänvisar nu cell C5 till cell C6.

Värdet som visas är 0 eftersom cellen C6 är tom. I cell C6 skriver vi "Jag är C6" och nu visar C5 "Jag är C6".

Exempel: Textformel

Låt oss försöka ett annat exempel. Kom ihåg från lektion 2 där vi var tvungna att dela upp ett fullständigt namn i för- och efternamn? Vad händer när vi kopierar denna formel?

Skriv formeln = RIGHT (A3, LEN (A3) - FIND (",", A3) - 1) eller kopiera texten till cell C3. Kopiera inte själva cellen, bara texten, kopiera texten, annars uppdateras referensen.

Du kan redigera innehållet i en cell längst upp i ett kalkylblad i rutan bredvid var det står "fx." Den rutan är längre än en cell är bred, så det är lättare att redigera.

Nu har vi:

Ingenting komplicerat, vi har just skrivit en ny formel i cell C3. Kopiera nu C3 till cellerna C2 och C4. Observera resultaten nedan:

Nu har vi Alexander Hamilton och Thomas Jeffersons förnamn.

Använd markören för att markera cellerna C2, C3 och C4. Peka markören på cell B2 och klistra in innehållet. Titta på vad som hände - vi får ett fel: "#REF." Varför är det här?

När vi kopierade cellerna från kolumn C till kolumn B uppdaterade vi referens en kolumn till vänster = RIGHT (A2, LEN (A2) - FIND (",", A2) - 1).

Det ändrade varje hänvisning till A2 till kolumnen till vänster om A, men det finns ingen kolumn till vänster om kolumn A. Så datorn vet inte vad du menar.

Den nya formeln i B2 är till exempel = RIGHT (#REF!, LEN (#REF!) - FIND (",", # REF!) - 1) och resultatet är #REF:

Kopiera en formel till ett antal celler

Kopiera celler är mycket praktiskt eftersom du kan skriva en formel och kopiera den till ett stort område och referensen uppdateras. Detta undviker att redigera varje cell för att säkerställa att den pekar på rätt plats.

Med "intervall" menar vi mer än en cell. Till exempel betyder (Cl: ClO) alla celler från cell Cl till cell C10. Så det är en kolonn av celler. Ett annat exempel (A1: AZ1) är den övre raden från kolumn A till kolumn AZ.

Om ett intervall överstiger fem kolumner och tio rader anger du intervallet genom att skriva den övre vänstra cellen och längst ned till höger, t.ex. A1: E10. Detta är ett kvadratiskt område som korsar rader och kolumner och inte bara en del av en kolumn eller en del av en rad.

Här är ett exempel som illustrerar hur man kopierar en cell till flera platser. Antag att vi vill visa våra prognostiserade utgifter för månaden i ett kalkylblad så att vi kan göra en budget. Vi gör ett kalkylblad så här:

Kopiera nu formuläret i cell C3 (= B3 + C2) till resten av kolumnen för att ge en löpande balans för vår budget. Excel uppdaterar cellreferensen när du kopierar den. Resultatet visas nedan:

Som du kan se uppdateras varje ny cell släkting till den nya platsen, så cell C4 uppdaterar sin formel till = B4 + C3:

Cell C5 uppdateringar till = B5 + C4, och så vidare:

Absoluta referenser

En absolut referens ändras inte när du flyttar eller kopierar en cell. Vi använder $ tecknet för att göra en absolut referens - för att komma ihåg det, tänk på ett dollartecken som ett ankare.

Ange till exempel formel = $ A $ 1 i vilken cell som helst. $ Framför kolumn A betyder ändrar inte kolumnen, $ före rad 1 betyder ändrar inte kolumnen när du kopierar eller flyttar cellen till någon annan cell.

Som du kan se i exemplet nedan, i cell B1 har vi en relativ referens = A1.När vi kopierar B1 till de fyra cellerna under den, ändras den relativa referensen = A1 till cellen till vänster, så B2 blir A2, B3 bli A3, etc. Dessa celler har uppenbarligen inget värde inmatat, så utsignalen är noll.

Om vi ​​använder = $ A1 $ 1, som i C1 och vi kopierar den till de fyra cellerna under den, är referensen absolut, så den ändras aldrig och utsignalen är alltid lika med värdet i cell A1.

Antag att du håller reda på ditt intresse, som i exemplet nedan. Formeln i C4 = B4 * B1 är "räntesatsen" * "balans" = "ränta per år".

Nu har du ändrat din budget och sparat ytterligare $ 2000 för att köpa en fond. Antag att det är en fast räntefond och den betalar samma ränta. Ange det nya kontot och balansera i kalkylbladet och kopiera sedan formeln = B4 * B1 från cell C4 till cell C5.

Den nya budgeten ser så här ut:

Den nya fonden tjänar $ 0 i ränta per år, vilket inte kan vara rätt eftersom räntan är klart 5 procent.

Excel belyser de celler som en formel refererar till. Du kan se ovan att referensen till räntan (B1) flyttas till den tomma cellen B2. Vi borde ha hänvisat till B1 absolut genom att skriva $ B $ 1 med dollarns tecken för att förankra raden och kolumnreferensen.

Skriv om den första beräkningen i C4 för att läsa = B4 * $ B $ 1 som visas nedan:

Kopiera sedan den här formeln från C4 till C5. Kalkylbladet ser nu ut så här:

Eftersom vi kopierade formeln en cell ner, dvs ökat raden med en, är den nya formeln = B5 * $ B $ 1. Fondräntan beräknas korrekt nu, eftersom räntan är förankrad i cell B1.

Detta är ett bra exempel på när du kan använda ett "namn" för att referera till en cell. Ett namn är en absolut referens. Till exempel, för att tilldela namnet "ränta" till cell B1, högerklicka på cellen och välj sedan "definiera namn".

Namnen kan referera till en cell eller ett intervall, och du kan använda ett namn i en formel, till exempel = interest_rate * 8 är samma sak som att skriva = $ B $ 1 * 8.

Blandade referenser

Blandade referenser är när antingen raden eller kolonnen är förankrad.

Antag att du är en bonde som gör en budget. Du äger också en matbutik och säljer frön. Du kommer att plantera majs, sojabönor och alfalfa. I kalkylbladet nedan visas kostnaden per hektar. "Kostnaden per acre" = "pris per kilo" * "pund av frön per acre" - det är vad det kommer att kosta dig att plantera en tunnland.

Ange kostnaden per acre som = $ B2 * C2 i cell D2. Du säger att du vill förankra priset per pund kolumn. Kopiera sedan den här formeln till de andra raderna i samma kolumn:

Nu vill du veta värdet av din inventering av frön. Du behöver priset per pund och antalet pund i lager för att veta värdet av inventeringen.

Vi lägger till två kolumner: "pund av frö i lager" och sedan "värde av inventering." Kopiera nu cellen D2 till F4 och observera att radreferensen i den första delen av den ursprungliga formeln ($ B2) uppdateras till rad 4 men kolumnen förblir fast eftersom $ ankrar den till "B."

Detta är en blandad referens eftersom kolumnen är absolut och raden är relativ.

Cirkulära referenser

En cirkulär referens är när en formel hänvisar till sig själv.

Till exempel kan du inte skriva c3 = c3 + 1. Denna typ av beräkning kallas "iteration" vilket betyder att det upprepar sig själv. Excel stöder inte iteration eftersom det beräknar allt bara en gång.

Om du försöker göra det genom att skriva SUM (B1: B5) i cell B5:

En varningsskärm dyker upp:

Excel berättar bara att du har en cirkulär referens längst ner på skärmen så att du kanske inte märker det. Om du har en cirkulär referens och stänger ett kalkylblad och öppnar det igen, kommer Excel att berätta i ett popup-fönster som du har en cirkulär referens.

Om du har en cirkulär referens kommer Excel varje gång du öppnar kalkylbladet att berätta med det popup-fönstret att du har en cirkulär referens.

Referenser till andra kalkylblad

En "arbetsbok" är en samling av "kalkylblad". Enkelt uttryckt betyder det att du kan ha flera kalkylblad (kalkylblad) i samma Excel-fil (arbetsbok). Som du kan se i exemplet nedan har vårt exempel arbetsbok många regneark (i rött).

Arbetsblad är som standard Sheet1, Sheet2 och så vidare. Du skapar en ny genom att klicka på "+" längst ner på Excel-skärmen.

Du kan ändra kalkylbladets namn till något användbart som "lån" eller "budget" genom att högerklicka på fliken kalkylblad som visas längst ned på Excel-programskärmen, välja omdöpning och skriva in ett nytt namn.

Eller du kan helt enkelt dubbelklicka på fliken och byta namn på den.

Syntaxen för en kalkylbladreferens är = arbetsblad! Cell. Du kan använda denna typ av referens när samma värde används i två arbetsblad, exempel på det kan vara:

• Dagens datum
• Valutaomräkningskurs från dollar till euro
• Något som är relevant för alla arbetsblad i arbetsboken

Nedan är ett exempel på kalkylblad "intresse" med hänvisning till kalkylblad "lån", cell B1.

Om vi ​​tittar på "lån" kalkylbladet kan vi se hänvisningen till lånebeloppet:

Kommer härnäst…

Vi hoppas att du nu har ett bestämt grepp om cellreferenser, inklusive relativ, absolut och blandad. Det är verkligen mycket.

Det är det för dagens lektion, i lektion 4 kommer vi att diskutera några användbara funktioner som du kanske vill veta för daglig Excel-användning.