För att kunna göra exakta beräkningar i Excel är det viktigt att förstå hur de olika typerna av cellreferenser fungerar.
A1 mot R1C1 Referenser
Excel -kalkylblad innehåller många celler och (som standard) identifieras varje cell med dess kolumnbokstav följt av dess radnummer. Detta är känt som referens i A1-stil. Exempel: A1, B4, C6
A1 referensstil
Alternativt kan du växla till R1C1 referensläge att hänvisa till en cellrad & kolumnnummer. Istället för att hänvisa till cell A1 skulle du hänvisa till R1C1 (rad 1, kolumn 1). Cell C4 skulle hänvisas till R4C3.
R1C1 referensstil
Hänvisning i R1C1-stil är extremt ovanligt i Excel. Om du inte har en bra anledning bör du förmodligen hålla dig till standardreferensläget i A1-stil. Men om du använder VBA kommer du sannolikt att stöta på denna referensstil.
Byt till R1C1 referensstil
För att byta referensstil, gå till Arkiv> Alternativ> Formel. Markera rutan bredvid R1C1 referensstil.
Namngivna områden
En av de mest underutnyttjade funktionerna i Excel är Named Ranges-funktionen. Istället för att hänvisa till en cell (eller grupp av celler) efter dess cellplats (ex B3 eller R3C2) kan du namnge det området och helt enkelt referera till intervallnamnet i dina formler.
För att namnge ett intervall:
- Markera den eller de celler som du vill namnge
- Klicka inuti rutan för intervallnamn
- Ange önskat namn
- Tryck Enter
Nu kan du referera till cell A1 genom att skriva = range_name1 istället. Detta är mycket användbart när du arbetar med stora arbetsböcker med flera kalkylblad.
Utbud av celler
När du använder Excel: s inbyggda funktioner kan du behöva referera till cellområden. Cellintervall ser ut så här 'A1: B3'. Denna referens avser alla celler mellan A1 och B3: cellerna A1, A2, A3, B1, B2, B3.
Så här väljer du ett cellintervall när du anger en formel:
- Skriv in intervallet (separera start- och slutintervallet med ett semikolon)
- Använd musen för att klicka på den första cellreferensen, håll ned musknappen och dra önskat område.
- Håll ned shift -knappen och navigera med piltangenterna för att välja ditt intervall
Absoluta (frysta) och relativa referenser
När du anger cellreferenser i formler kan du använda relativa eller absoluta (frysta) referenser. Relativa referenser flyttas proportionellt när du kopierar formeln till en ny cell. Absoluta referenser förblir oförändrade. Låt oss titta på några exempel:
Relativ referens
En relativ referens i Excel ser ut så här
= A1
När du kopierar och klistrar in en formel med relativa referenser flyttas de relativa referenserna proportionellt. Om du inte anger något annat kommer dina cellreferenser att vara relativa (ofrysta) som standard.
Exempel: Om du kopierar ‘= A1’ ner en rad ändras referensen till ‘= A2’.
Absoluta (frysta) cellreferenser
Om du inte vill att dina cellreferenser ska flyttas när du kopierar en formel kan du "frysa" dina cellreferenser genom att lägga till dollarstecken ($ s) framför referensen som du vill frysa. När du nu kopierar och klistrar in formeln förblir cellreferensen oförändrad. Du kan välja att frysa radreferensen, kolumnreferensen eller båda.
A1: Ingenting är fruset
$ A1: Kolumnen är frusen, men raden är inte frusen
A $ 1: Raden är frusen, men kolumnen är inte frusen
$ A $ 1: Både raden och kolumnen är frusna
Absolut referensgenväg
Att manuellt lägga in dollarstecken ($ s) i dina formler är inte särskilt praktiskt. Istället, medan du skapar din formel, använder du F4 -tangenten för att växla mellan absoluta/relativa cellreferenser.
Exempel på absolut cellreferens
När skulle du egentligen behöva frysa en cellreferens? Ett vanligt exempel är när du har inmatningsceller som det ofta refereras till. I exemplet nedan vill vi beräkna momsen för varje mängd menyalternativ. Momsen är konstant för alla artiklar, så vi kommer att referera till momscellen upprepade gånger.
För att hitta den totala momsen, ange formeln '= (B3*C3)*$ C $ 1' i kolumn D och kopiera formeln ner.
Blandad referens
Du kanske har hört talas om blandade cellreferenser. En blandad referens är när antingen rad- eller kolumnreferensen är låst (men inte båda).
Blandad referens
Kom ihåg att genom att använda "F4" -tangenten kan du bläddra igenom dina relativa, absoluta cellreferenser.
Cellreferenser - Infoga och ta bort rader/kolumner
Du kanske undrar vad som händer med dina cellreferenser när du sätter in eller tar bort rader/kolumner?
Cellreferensen uppdateras automatiskt för att referera till den ursprungliga cellen. Detta är fallet oavsett om cellreferensen är frusen.
3D -referenser
Ibland kan du behöva arbeta med flera kalkylblad med identiska datamönster. Excel låter dig referera till flera ark samtidigt utan att behöva ange varje kalkylblad manuellt. Du kan referera till ett arkområde som liknar hur det skulle referera till ett cellområde. Exempel "Sheet1: Sheet5! A1" skulle referera till celler A1 på alla blad från Sheet1 till Sheet5.
Låt oss gå igenom ett exempel:
Du vill lägga till det totala antalet enheter som säljs för varje produkt i alla butiker. Varje butik har ett eget kalkylblad och alla kalkylblad har ett identiskt format. Du kan skapa en formel som liknar detta:
Detta är inte för svårt med bara fyra kalkylblad, men tänk om du hade 40 kalkylblad? Vill du verkligen lägga till varje cellreferens manuellt?
Istället kan du enkelt använda en 3D -referens för att referera till flera ark samtidigt (ungefär som hur du kan referera till ett cellområde).
Var försiktig! Ordningen på dina kalkylblad har betydelse. Om du flyttar ett annat ark mellan de refererade arken (StoreA och StoreD) kommer det arket att inkluderas. Om du däremot flyttar ett ark utanför arket (före StoreA eller efter StoreD) kommer det inte längre att inkluderas.
Cirkulär cellreferens
En cirkulär cellreferens är när en cell refererar tillbaka till sig själv. Till exempel, om resultatet av cell B1 används som ingång för cell B1 skapas en cirkulär referens. Cellen behöver inte direkt hänvisa till sig själv Det kan finnas mellansteg.
Exempel:
I detta fall är formeln för cell B2 "A2+A3+B2". Eftersom du är i cell B2 får du inte använda B2 i ekvationen. Detta skulle utlösa cirkulär referens och värdet i cell "B2" kommer automatiskt att sättas till "0".
Vanligtvis är cirkulära referenser ett resultat av användarfel, men under vissa omständigheter kan du använda en cirkulär referens. Det primära exemplet på att använda en cirkulär referens är att beräkna värden iterativt. För att göra detta måste du gå till Arkiv> Alternativ> Formler och Aktiverad iterativ beräkning:
Externa referenser
Ibland när du beräknar data kan du behöva hänvisa till data utanför din arbetsbok. Detta kallas en extern referens (länk).
Om du vill välja en extern referens medan du skapar en formel navigerar du till den externa arbetsboken och väljer din referens som du normalt skulle göra.
För att navigera till den andra arbetsboken kan du använda genvägen CTRL + TAB eller gå till Visa> Byt Windows.
När du väl har markerat cellen ser du att en extern referens som ser ut så här:
Lägg märke till att arbetsbokens namn omges av parenteser [].
När du stänger den refererade arbetsboken visar referensen filplatsen:
När du öppnar arbetsboken som innehåller den externa länken ombeds du att aktivera automatisk uppdatering av länkar. Om du gör det öppnar Excel referensvärdet med det aktuella värdet för arbetsboken. Även om det är stängt! Var försiktig! Du kanske vill eller inte vill detta.
Namngivna områden och externa referenser
Vad händer med din externa cellreferens när rader / kolumner läggs till eller tas bort från referensarboken? Om båda arbetsböckerna är öppna uppdateras cellreferenserna automatiskt. Men om båda arbetsböckerna inte är öppna kommer cellreferenserna inte att uppdateras och är inte längre giltiga. Detta är ett stort bekymmer när du länkar till externa arbetsböcker. Många fel orsakas av detta.
Om du länkar till externa arbetsböcker bör du ge cellreferensen ett namn med ett namn (se föregående avsnitt för mer information). Nu kommer din formel att hänvisa till det angivna intervallet oavsett vilka ändringar som sker på den externa arbetsboken.
