Att få referensceller och intervall är det som får ett kalkylarkverktyg att fungera. Och Excel är den bästa och mest kraftfulla som finns.
I den här självstudien kommer jag att täcka allt du behöver veta om hur du refererar till celler och intervall i Excel. Bortsett från den grundläggande referensen på samma blad, skulle huvuddelen av denna handledning handla om hur man refererar till ett annat blad eller en arbetsbok i Excel.
Även om det inte är så stor skillnad i hur det fungerar, när du refererar till ett annat blad i samma fil eller refererar till en helt separat Excel -fil, ändras formatet för den referensen lite.
Det finns också några viktiga saker du måste tänka på när du refererar till ett annat blad eller andra externa filer.
Men oroa dig … inget för galet!
När du är klar med den här självstudien kommer du att veta allt du behöver veta om att referera till celler och intervall i Excel (vara i samma arbetsbok eller en annan arbetsbok).
Låt oss börja!
Hänvisning till en cell i samma blad
Detta är den mest grundläggande referensnivån där du refererar till en cell på samma blad.
Till exempel, om jag befinner mig i cell B1 och jag vill referera till cell A1, skulle formatet vara:
= A1
När du gör detta kommer värdet i cellen där du använder den här referensen att vara samma som i cell A1. Och om du gör några ändringar i cell A1, återspeglas dessa i cellen där du har använt den här referensen.
Hänvisning till en cell i det andra bladet
Om du måste referera till ett annat blad i samma arbetsbok måste du använda nedanstående format:
Arknamn! Celladress
Först har du bladnamnet följt av ett utropstecken som följs av cellreferensen.
Så om du behöver hänvisa till cell A1 i blad 1 måste du använda följande referens:
= Ark1! A1
Och om du vill referera till ett cellintervall i ett annat ark måste du använda följande format:
Arknamn! Första_cell: Sista_cell
Så om du vill hänvisa till intervallet A1: C10 i ett annat blad i samma arbetsbok måste du använda nedanstående referens:
= Sheet1! A1: C10
Observera att jag bara har visat dig referensen till cellen eller intervallet. I verkligheten skulle du använda dessa i formler. Men formatet på referenserna ovan kommer att förbli detsamma
I många fall skulle det kalkylblad du refererar till ha flera ord i namnet. Det kan till exempel vara Projektdata eller försäljningsdata.
Om du har mellanslag eller icke -alfabetiska tecken (som @,!, #, -, etc.), måste du använda namnet inom enstaka citattecken.
Om du till exempel vill referera till cell A1 i bladet Säljdata, använder du nedanstående referens:
= 'Försäljningsdata'! A1
Och om bladets namn är försäljningsdata, för att referera till cell A1 i det här bladet, måste du använda nedanstående referens:
= 'Försäljningsdata'! A1
När du hänvisar till ett blad i samma arbetsbok och sedan ändrar namnet på kalkylbladet behöver du inte oroa dig för att referensen bryts ner. Excel uppdaterar automatiskt dessa referenser åt dig.
Även om det är bra att veta formatet på dessa referenser, är det i praktiken inte så bra att manuellt skriver in dessa varje gång. Det skulle vara tidskrävande och mycket felaktigt.
Låt mig visa dig ett bättre sätt att skapa cellreferenser i Excel.
Skapa automatiskt referens till ett annat blad i samma arbetsbok
Ett mycket bättre sätt att skapa cellreferens till ett annat blad är att helt enkelt peka Excel på cellen/intervallet som du vill skapa referensen till och låta Excel skapa det själv.
Detta säkerställer att du inte behöver oroa dig för att utropstecknet eller citaten saknas eller att något annat format kommer upp. Excel skapar automatiskt rätt referens för dig.
Nedan följer stegen för att automatiskt skapa en referens till ett annat blad:
- Markera cellen i den aktuella arbetsboken där du behöver referensen
- Skriv formeln tills du behöver referensen (eller ett likhetstecken om du bara vill ha referensen)
- Välj det blad som du behöver referera till
- Markera cellen/intervallet som du vill referera till
- Tryck Enter för att få resultatet av formeln (eller fortsätt arbeta med formeln)
Stegen ovan skulle automatiskt skapa en referens till cellen/intervallet i ett annat blad. Du kommer också att kunna se dessa referenser i formelfältet. När du är klar kan du helt enkelt trycka på enter -knappen och det ger dig resultatet.
Om du till exempel har en del data i cell A1: A10 i ett blad som heter Försäljningsdata, och du vill få summan av dessa värden i det aktuella bladet, följer du stegen nedan:
- Skriv följande formel i det aktuella bladet (där du behöver resultatet): = Sum (
- Välj "Försäljningsdatablad".
- Välj det område som du vill lägga till (A1: A10). Så snart du gör detta skapar Excel automatiskt en referens till detta intervall (du kan se det i formelfältet)
- Tryck på enter -tangenten.
När du skapar en lång formel kan du behöva hänvisa till en cell eller ett intervall i ett annat blad och sedan behöva komma tillbaka till ursprungsarket och hänvisa till någon cell/intervall där.
När du gör detta kommer du att märka att Excel automatiskt infogar en bladreferens till det blad där du har formeln. Även om detta är okej och inte skadar, behövs det inte. I ett sådant fall kan du välja att behålla referensen eller ta bort den manuellt.
En annan sak du behöver veta när du skapar referenser genom att markera arket och sedan cellen/intervallet är att Excel alltid kommer att skapa en relativ referens (dvs. referenser med n0 $ -tecken). Det betyder att om jag kopierar och klistrar in formeln (en med hänvisning till ett annat blad) i någon annan cell, skulle den automatiskt justera referensen.
Här är ett exempel som förklarar relativa referenser.
Antag att jag använder följande formel i cell A1 i det aktuella bladet (för att referera till cell A1 i ett bladnamn SalesData)
= SalesData! A1
Om jag nu kopierar den här formeln och klistrar in i cell A2, kommer formeln att ändras till:
= SalesData! A1
Detta händer eftersom formeln är relativ och när jag kopierar och klistrar in den kommer referenserna automatiskt att justeras.
Om jag vill att den här referensen alltid ska referera till cell A1 i SalesData -bladet måste jag använda följande formel:
= SalesData! $ A $ 1
Dollartecknet före raden och kolumnnumret låser dessa referenser så att dessa inte ändras.
Här är en detaljerad handledning där du kan lära dig mer om absoluta, blandade och relativa referenser.
Nu när vi har täckt hur vi refererar till ett annat blad i samma arbetsbok, låt oss se hur vi kan hänvisa till en annan arbetsbok.
Hur man refererar till en annan arbetsbok i Excel
När du hänvisar till en cell eller ett intervall till en annan Excel -arbetsbok beror formatet på referensen på om arbetsboken är öppen eller stängd.
Och naturligtvis spelar namnet på arbetsboken och kalkylbladen också en roll för att bestämma formatet (beroende på om du har mellanrum eller icke-alfabetiska tecken i namnet eller inte).
Så låt oss se de olika formaten för externa referenser till en annan arbetsbok i olika scenarier.
Extern referens till en öppen arbetsbok
När det gäller att hänvisa till en extern öppen arbetsbok måste du ange arbetsbokens namn, kalkylbladets namn och cell-/intervalladressen.
Nedan visas formatet du behöver använda när du hänvisar till en extern öppen arbetsbok
= '[Filnamn] Arknamn! CellAddress
Anta att du har en arbetsbok ‘Exempelfil.xlsx’ och du vill hänvisa till cell A1 i blad 1 i den här arbetsboken.
Nedan finns referensen för detta:
= [ExempelFile.xlsx] SalesData! A1
Om det finns mellanslag i det externa arbetsbokens namn eller bladnamnet (eller båda), måste du lägga till filnamnet (inom hakparenteser) och bladnamnet i enkla citattecken.
Nedan är exemplen där du skulle behöva ha namnen i enskilda citattecken:
= '[Exempel File.xlsx] SalesData'! A1 = '[ExampleFile.xlsx] Försäljningsdata'! A1 = '[Exempel File.xlsx] Försäljningsdata'! A1
Hur man skapar referens till en annan arbetsbok (automatiskt)
Återigen, även om det är bra att känna till formatet, är det bäst att inte skriva det manuellt.
I stället pekar du bara Excel i rätt riktning så skapar de dessa referenser åt dig. Detta är mycket snabbare med mycket färre chanser till fel.
Om du till exempel har vissa data i cell A1: A10 i en arbetsbok med namnet "Exempelfil" i arket "Försäljningsdata" och du vill få summan av dessa värden i det aktuella bladet, följer stegen nedan :
- Skriv följande formel i det aktuella bladet (där du behöver resultatet): = Sum (
- Gå till arbetsboken "Exempelfil"
- Välj "Försäljningsdatablad".
- Välj det område som du vill lägga till (A1: A10). Så snart du gör detta skapar Excel automatiskt en referens till detta intervall (du kan se det i formelfältet)
- Tryck på enter -tangenten.
Detta skulle omedelbart skapa formeln med rätt referenser.
En sak du skulle märka när du skapar en referens till en extern arbetsbok är att den alltid kommer att skapa absoluta referenser. Det betyder att det finns ett $ -tecken före rad- och kolumnnumren. Det betyder att om du kopierar och klistrar in den här formeln till andra celler, kommer den att fortsätta att referera till samma intervall på grund av absolut referens.
Om du vill att detta ska ändras måste du ändra referenserna manuellt.
Extern referens till en stängd arbetsbok
När en extern arbetsbok är öppen och du hänvisar till den här arbetsboken behöver du bara ange filnamn, bladnamn och cell-/intervalladress.
Men när detta är stängt har Excel ingen aning om var du letar efter cellerna/intervallet du hänvisade till.
Det är därför du måste ange filvägen när du skapar en referens till en sluten arbetsbok.
Nedan finns en referens som refererar till cell A1 i Sheet1 -kalkylbladet i arbetsboken Exempelfil. Eftersom filen inte är öppen hänvisar den också till platsen där filen sparas.
= 'C: \ Users \ sumit \ Desktop \ [Exempel File.xlsx] Sheet1'! $ A $ 1
Ovanstående referens har följande delar:
- Filväg - platsen på ditt system eller nätverk där den externa filen finns
- Filnamn - namnet på den externa arbetsboken. Detta skulle också inkludera filtillägget.
- Arknamn - namnet på det blad där du refererar till cellerna/intervallerna
- Cell-/intervalladress - den exakta cell-/intervalladressen du refererar till
När du skapar en extern referens till en öppen arbetsbok och sedan stänger arbetsboken märker du att referensen automatiskt ändras. När den externa arbetsboken har stängts lägger Excel automatiskt in en referens till filvägen också.
Påverkan av att ändra filplats på referenser
När du skapar en referens till en cell/intervall i en extern Excel -fil och sedan stänger den, använder referensen nu också filsökvägen.
Men om du ändrar filplatsen kommer ingenting att ändras i din arbetsbok (där du skapar referensen). Men eftersom du har ändrat platsen har länken nu brutits.
Så om du stänger och öppnar den här arbetsboken kommer den att berätta att länken är trasig och du måste antingen uppdatera länken eller bryta den helt. Det kommer att visa dig en prompt som visas nedan:
När du klickar på Uppdatera kommer det att visa dig en annan prompt där du kan välja alternativen för att redigera länkarna (som visar dialogrutan nedan)
Om du behöver behålla dessa filer länkade kan du ange den nya platsen för filen genom att klicka på Uppdatera värden. Excel öppnar en dialogruta där du kan ange den nya filplatsen genom att navigera dit och välja den.
Hänvisning till ett definierat namn (i samma eller externa arbetsbok)
När du måste referera till celler och intervall är ett bättre sätt att skapa definierade namn för områdena.
Detta är användbart eftersom det gör det enkelt för dig att hänvisa till dessa intervall med ett namn istället för en lång och komplicerad referensadress.
Till exempel är det lättare att använda = SalesData istället för = [Exempel File.xlsx] Sheet1 ′! $ A $ 1: $ A $ 10
Och om du har använt den här definierade namnet i flera formler och du behöver ändra referensen behöver du bara göra det en gång.
Här är stegen för att skapa ett namngivet område för ett cellområde:
- Markera alla celler som du vill inkludera i det angivna intervallet
- Klicka på fliken Formler
- Klicka på alternativet Definiera namn (det finns i gruppen definierade namn)
- Ge dialogrutan ett namn i dialogrutan Nytt namn (jag använder namnet SalesData i det här exemplet). Kom ihåg att du inte kan ha mellanslag i namnet
- Behåll omfattningen som arbetsbok (om du inte har en stark anledning att göra den till arknivå)
- Se till att referensen till intervallet är korrekt.
- Klicka på OK.
Nu har ditt namngivna intervall skapats och du kan använda det istället för cellreferenser med celladresser.
Om jag till exempel vill få summan av alla dessa celler i SalesData -området kan du använda följande formel:
= SUMMA (SalesData)
Och vad händer om du vill använda det här namnet intervall är andra kalkylblad eller till och med andra arbetsböcker?
Du kan!
Du behöver bara följa samma format som vi har diskuterat i avsnittet ovan.
Du behöver inte gå tillbaka till början av denna artikel. Låt mig ge dig alla exemplen här själv så att du får idén.
Arbetsbok Vs Arbetsbladsnivå Namngivna intervall: När du skapar en arbetsboksnivå med namnet intervall kan det bara finnas ett område med det namnet. Så om du hänvisar till det från externa arbetsböcker eller kalkylblad i samma arbetsbok behöver du inte ange kalkylbladets namn (eftersom det är tillgängligt för användning i hela arbetsboken). Om du använder kalkylbladsnivån med namnet intervall kommer du bara att kunna använda det i kalkylbladet och medan du hänvisar till det måste du också ange kalkylbladets namn.Hänvisning till det definierade namnet i samma kalkylblad eller arbetsbok
Om du har skapat det definierade namnet för arbetsboksnivån kan du använda det var som helst i arbetsboken genom att bara använda det definierade namnet.
Om jag till exempel vill få summan av alla celler i det namngivna intervall vi skapade (SaledData) kan jag använda följande formel:
= SUMMA (SaledData)
Om du har skapat en kalkylbladsnivå med namnet intervall kan du bara använda den här formeln om det namngivna intervallet skapas i samma blad där du använder formeln.
Om du vill använda det på ett annat ark (säg Sheet2) måste du använda följande formel:
= SUMMA (Sheet1! $ A $ 1: $ A $ 10)
Och om det finns mellanslag eller alfanumeriska tecken i bladets namn måste du skriva bladnamnet i enstaka citattecken.
= SUMMA ('Ark 1'! $ A $ 1: $ A $ 10)
Hänvisning till det definierade namnet i en annan arbetsbok (öppen eller stängd)
När du vill referera till ett namngivet område i en annan arbetsbok måste du ange arbetsbokens namn och sedan namnet på intervallet.
Om du till exempel har en Excel -arbetsbok med namnet ExampleFile.xlsx och ett namngivet område med namnet SalesData, kan du använda formeln nedan för att få summan av detta intervall från en annan arbetsbok:
= SUMMA (ExempelFile.xlsx! SalesData)
Om det finns mellanslag i filnamnet måste du använda dessa i enskilda citattecken.
= SUMMA ('Exempelfil.xlsx'! SalesData)
Om du har namnnivåer på arknivå måste du ange både arbetsbokens namn och arbetsbladet när du refererar till det från en extern arbetsbok.
Nedan följer ett exempel på att referera till ett arknivå med namnet intervall:
= SUMMA ('[Exempel File.xlsx] Sheet1'! SalesData)
Som jag också nämnde ovan är det alltid bäst att skapa arbetsboksnivå med namnintervall, såvida du inte har en stark anledning att skapa ett kalkylblad på nivå ett.
Om du refererar till ett namngivet intervall i en sluten arbetsbok måste du också ange filvägen. Nedan är ett exempel på detta:
= SUMMA ('C: \ Users \ sumit \ Desktop \ Exempelfil.xlsx'! SalesData)
När du skapar en referens till ett namngivet område i en öppen arbetsbok och sedan stänger arbetsboken, ändrar Excel automatiskt referensen och lägger till sökvägen.
Hur man skapar referens till ett namngivet intervall
Om du skapar och arbetar med många namngivna intervall är det inte möjligt att komma ihåg var och ens namn.
Excel hjälper dig genom att visa dig en lista över alla namngivna intervall som du har skapat och låter dig infoga dessa i formler med ett enda klick.
Anta att du har skapat ett namngivet område SalesData som du vill använda i en formel för att SUMMA alla värden i det namngivna intervallet.
Här är stegen för att göra detta:
- Markera cellen där du vill ange formeln.
- Ange formeln till den punkt där du behöver infoga det namngivna intervallet
- Tryck på F3 -tangenten på tangentbordet. Detta öppnar dialogrutan Klistra in namn med listan över alla namn du har skapat
- Dubbelklicka på namnet du vill infoga.
Stegen ovan skulle infoga namnet i formeln och du kan fortsätta arbeta med formeln.
Obs! Den här tekniken fungerar bara för de namnområden som finns i samma arbetsbok. Om du vill få namnen i en extern arbetsbok måste du gå dit, kontrollera namnet med dialogrutan Klistra in namn och sedan komma tillbaka och använda det i din formel (i rätt format med filnamnet).Detta är allt du behöver veta om hur du refererar till andra blad eller arbetsböcker och hur du skapar en extern referens i Excel.
Hoppas du tyckte att denna handledning var användbar.
