For at kunne referere til celler og områder er det, der får ethvert regnearksværktøj til at fungere. Og Excel er den bedste og mest kraftfulde derude.
I denne vejledning dækker jeg alt det, du har brug for at vide om, hvordan du refererer til celler og områder i Excel. Bortset fra den grundlæggende henvisning på det samme ark, ville hoveddelen af denne vejledning handle om hvordan man henviser til et andet ark eller en projektmappe i Excel.
Selvom der ikke er stor forskel på, hvordan det fungerer, ændrer formatet for denne reference en smule, når du refererer til et andet ark i den samme fil eller refererer til en helt separat Excel -fil.
Der er også nogle vigtige ting, du skal huske på, når du refererer til et andet ark eller andre eksterne filer.
Men bekymre dig … intet er for tosset!
Når du er færdig med denne vejledning, ved du alt, hvad der er at vide om at henvise til celler og områder i Excel (være i den samme projektmappe eller en anden projektmappe).
Lad os komme igang!
Henvisning til en celle i samme ark
Dette er det mest grundlæggende referenceniveau, hvor du refererer til en celle på det samme ark.
For eksempel, hvis jeg er i celle B1, og jeg vil henvise til celle A1, ville formatet være:
= A1
Når du gør dette, er værdien i cellen, hvor du bruger denne reference, den samme som i celle A1. Og hvis du foretager ændringer i celle A1, afspejles disse i cellen, hvor du har brugt denne reference.
Henvisning til en celle i det andet ark
Hvis du skal henvise til et andet ark i den samme projektmappe, skal du bruge nedenstående format:
Arknavn! Celleadresse
Først har du arknavnet efterfulgt af et udråbstegn, som efterfølges af cellereferencen.
Så hvis du skal henvise til celle A1 i ark 1, skal du bruge følgende reference:
= Ark1! A1
Og hvis du vil henvise til en række celler i et andet ark, skal du bruge følgende format:
Arknavn! Første_celle: Sidste_celle
Så hvis du vil henvise til området A1: C10 i et andet ark i den samme projektmappe, skal du bruge nedenstående reference:
= Ark1! A1: C10
Bemærk, at jeg kun har vist dig referencen til cellen eller området. I virkeligheden ville du bruge disse i formler. Men formatet på ovennævnte referencer forbliver det samme
I mange tilfælde vil det regneark, du refererer til, have flere ord i navnet. Det kan for eksempel være Projektdata eller salgsdata.
Hvis du har mellemrum eller ikke -alfabetiske tegn (f.eks. @,!, #, -, osv.), Skal du bruge navnet inden for enkelte anførselstegn.
Hvis du f.eks. Vil henvise til celle A1 i arket Salgsdata, bruger du nedenstående reference:
= 'Salgsdata'! A1
Og hvis bladets navn er salgsdata, skal du bruge nedenstående reference for at henvise til celle A1 i dette ark:
= 'Salgsdata'! A1
Når du refererer til et ark i den samme projektmappe og senere ændrer navnet på regnearket, behøver du ikke bekymre dig om, at referencen bryder sammen. Excel opdaterer automatisk disse referencer for dig.
Selvom det er fantastisk at kende formatet på disse referencer, er det i praksis ikke en god idé at skrive disse manuelt hver gang. Det ville være tidskrævende og meget fejlbehæftet.
Lad mig vise dig en bedre måde at oprette cellereferencer i Excel.
Opretter automatisk reference til et andet ark i den samme projektmappe
En meget bedre måde at oprette cellereference til et andet ark på er blot at pege Excel på cellen/området, som du vil oprette referencen til, og lade Excel oprette det selv.
Dette sikrer, at du ikke behøver at bekymre dig om, at udråbstegnet eller citater mangler, eller at et andet formatproblem dukker op. Excel opretter automatisk den korrekte reference til dig.
Nedenfor er trinene til automatisk at oprette en reference til et andet ark:
- Vælg cellen i den aktuelle projektmappe, hvor du har brug for referencen
- Indtast formlen, indtil du har brug for referencen (eller et lighedstegn, hvis du bare vil have referencen)
- Vælg det ark, du skal henvise til
- Vælg den celle/det område, du vil henvise til
- Tryk på Enter for at få resultatet af formlen (eller fortsæt med at arbejde på formlen)
Ovenstående trin ville automatisk oprette en reference til cellen/området i et andet ark. Du vil også kunne se disse referencer i formellinjen. Når du er færdig, kan du blot trykke på enter -tasten, og det vil give dig resultatet.
Hvis du f.eks. Har nogle data i celle A1: A10 i et ark med navnet Salgsdata, og du ønsker at få summen af disse værdier i det aktuelle ark, er trinene følgende:
- Indtast følgende formel i det aktuelle ark (hvor du har brug for resultatet): = Sum (
- Vælg "Salgsdata" -arket.
- Vælg det område, du vil tilføje (A1: A10). Så snart du gør dette, opretter Excel automatisk en reference til dette område (du kan se det i formellinjen)
- Tryk på enter -tasten.
Når du opretter en lang formel, skal du muligvis henvise til en celle eller et område i et andet ark og derefter have et behov for at vende tilbage til oprindelsesarket og henvise til en celle/område der.
Når du gør dette, vil du bemærke, at Excel automatisk indsætter en arkreference til det ark, hvor du har formlen. Selvom dette er i orden og ikke skader, er det ikke nødvendigt. I et sådant tilfælde kan du vælge at beholde referencen eller fjerne den manuelt.
En anden ting, du skal vide, når du opretter referencer ved at markere arket og derefter cellen/området er, at Excel altid vil oprette en relativ reference (dvs. referencer med n0 $ tegn). Dette betyder, at hvis jeg kopierer og indsætter formlen (en med henvisning til et andet ark) i en anden celle, ville den automatisk justere referencen.
Her er et eksempel, der forklarer relative referencer.
Antag, at jeg bruger følgende formel i celle A1 i det aktuelle ark (for at henvise til celle A1 i et arknavn SalesData)
= Salgsdata! A1
Hvis jeg nu kopierer denne formel og indsætter i celle A2, ændres formlen til:
= Salgsdata! A1
Dette sker, fordi formlen er relativ, og når jeg kopierer og indsætter den, justeres referencerne automatisk.
Hvis jeg ønsker, at denne reference altid skal referere til celle A1 i SalesData -arket, bliver jeg nødt til at bruge nedenstående formel:
= SalesData! $ A $ 1
Dollartegnet før række- og kolonnenummer låser disse referencer, så disse ikke ændres.
Her er en detaljeret vejledning, hvor du kan lære mere om absolutte, blandede og relative referencer.
Nu hvor vi har dækket, hvordan vi refererer til et andet ark i den samme projektmappe, lad os se, hvordan vi kan henvise til en anden projektmappe.
Sådan henvises der til en anden projektmappe i Excel
Når du henviser til en celle eller et område til en anden Excel -projektmappe, afhænger formatet af denne reference, om projektmappen er åben eller lukket.
Og selvfølgelig spiller navnet på projektmappen og regnearkene også en rolle i bestemmelsen af formatet (afhængigt af om du har mellemrum eller ikke-alfabetiske tegn i navnet eller ej).
Så lad os se de forskellige formater for eksterne referencer til en anden projektmappe i forskellige scenarier.
Ekstern reference til en åben projektmappe
Når det kommer til at henvise til en ekstern åben projektmappe, skal du angive projektmappens navn, regnearksnavnet og celle-/områdeadressen.
Nedenfor er det format, du skal bruge, når du henviser til en ekstern åben projektmappe
= '[Filnavn] Arknavn! CellAddress
Antag, at du har en projektmappe 'Eksempelfil.xlsx', og du vil henvise til celle A1 i Ark1 i denne projektmappe.
Nedenfor er referencen til dette:
= [ExampleFile.xlsx] SalesData! A1
Hvis der er mellemrum i det eksterne projektmappes navn eller arknavnet (eller begge dele), skal du tilføje filnavnet (i firkantede parenteser) og arknavnet i enkelte anførselstegn.
Nedenfor er eksemplerne, hvor du skulle have navnene i enkelte citater:
= '[Eksempel File.xlsx] SalesData'! A1 = '[ExampleFile.xlsx] Salgsdata'! A1 = '[Eksempel File.xlsx] Salgsdata'! A1
Sådan oprettes reference til en anden projektmappe (automatisk)
Igen, selvom det er godt at kende formatet, er det bedst ikke at skrive det manuelt.
I stedet skal du bare pege Excel i den rigtige retning, og det vil oprette disse referencer for dig. Dette er meget hurtigere med langt færre chancer for fejl.
Hvis du f.eks. Har nogle data i celle A1: A10 i en projektmappe med navnet 'Eksempelfil' i arket med navnet 'Salgsdata', og du ønsker at få summen af disse værdier i det aktuelle ark, er følgende trin :
- Indtast følgende formel i det aktuelle ark (hvor du har brug for resultatet): = Sum (
- Gå til projektbogen 'Eksempelfil'
- Vælg "Salgsdata" -arket.
- Vælg det område, du vil tilføje (A1: A10). Så snart du gør dette, opretter Excel automatisk en reference til dette område (du kan se det i formellinjen)
- Tryk på enter -tasten.
Dette ville øjeblikkeligt skabe formlen med de korrekte referencer.
En ting, du vil bemærke, når du opretter en reference til en ekstern projektmappe, er, at den altid vil skabe absolutte referencer. Det betyder, at der er et $ -tegn før række- og kolonnenumre. Dette betyder, at hvis du kopierer og indsætter denne formel i andre celler, vil den blive ved med at henvise til det samme område på grund af absolut reference.
Hvis du vil have dette ændret, skal du ændre referencerne manuelt.
Ekstern reference til en lukket projektmappe
Når en ekstern projektmappe er åben, og du henviser til denne projektmappe, skal du bare angive filnavnet, arknavnet og celle-/områdeadressen.
Men når dette er lukket, har Excel ingen anelse om, hvor du leder efter de celler/område, du refererede til.
Derfor skal du også angive filstien, når du opretter en reference til en lukket projektmappe.
Nedenfor er en reference, der refererer til celle A1 i regnearket Sheet1 i projektmappen Eksempelfil. Da denne fil ikke er åben, refererer den også til det sted, hvor filen er gemt.
= 'C: \ Brugere \ sumit \ Desktop \ [Eksempel File.xlsx] Ark1'! $ A $ 1
Ovenstående reference har følgende dele:
- Filsti - placeringen på dit system eller netværk, hvor den eksterne fil er placeret
- Filnavn - navnet på den eksterne projektmappe. Dette vil også omfatte filtypen.
- Arknavn - navnet på det ark, du refererer til cellerne/områderne i
- Celle-/områdeadresse - den nøjagtige celle-/områdeadresse, som du henviser til
Når du opretter en ekstern reference til en åben projektmappe og derefter lukker projektmappen, vil du bemærke, at referencen automatisk ændres. Når den eksterne projektmappe er lukket, indsætter Excel automatisk også en reference til filstien.
Virkning af ændring af filplacering på referencer
Når du opretter en reference til en celle/område i en ekstern Excel -fil og derefter lukker den, bruger referencen nu også filstien.
Men hvis du ændrer filplaceringen, ændres der intet i din projektmappe (hvor du opretter referencen). Men da du har ændret placeringen, er linket nu brudt.
Så hvis du lukker og åbner denne projektmappe, vil den fortælle dig, at linket er brudt, og du skal enten opdatere linket eller bryde det helt. Det viser dig en prompt som vist herunder:
Når du klikker på Opdater, viser det dig en anden prompt, hvor du kan vælge mulighederne for at redigere linkene (som viser dig nedenstående dialogboks)
Hvis du skal beholde disse filer forbundet, kan du angive filens nye placering ved at klikke på Opdater værdier. Excel åbner en dialogboks for dig, hvor du kan angive den nye filplacering ved at navigere dertil og vælge den.
Henvisning til et defineret navn (i samme eller eksterne projektmappe)
Når du skal henvise til celler og områder, er en bedre måde at oprette definerede navne til områderne.
Dette er nyttigt, da det gør det let for dig at henvise til disse intervaller ved hjælp af et navn i stedet for en lang og kompliceret referenceadresse.
For eksempel er det lettere at bruge = SalesData i stedet for = [Eksempel File.xlsx] Ark1 ′! $ A $ 1: $ A $ 10
Og hvis du har brugt denne definerede navngivne i flere formler, og du skal ændre referencen, skal du kun gøre det én gang.
Her er trinene til at oprette et navngivet område for en række celler:
- Vælg alle de celler, du vil inkludere i det navngivne område
- Klik på fanen Formler
- Klik på indstillingen Definer navn (den er i gruppen Definerede navne)
- I dialogboksen Nyt navn skal du give dette område et navn (jeg bruger navnet SalesData i dette eksempel). Husk, at du ikke kan have mellemrum i navnet
- Behold omfanget som projektmappe (medmindre du har en stærk grund til at gøre det til arkniveau)
- Sørg for, at Referencer til området er korrekt.
- Klik på OK.
Nu er dit navngivne område blevet oprettet, og du kan bruge det i stedet for cellereferencer med celleadresser.
For eksempel, hvis jeg vil få summen af alle disse celler i SalesData -området, kan du bruge nedenstående formel:
= SUM (salgsdata)
Og hvad hvis du vil bruge dette navngivne område, er andre regneark eller endda andre projektmapper?
Du kan!
Du skal bare følge det samme format, som vi har diskuteret i ovenstående afsnit.
Ingen grund til at gå tilbage til begyndelsen af denne artikel. Lad mig give dig alle eksemplerne her selv, så du får ideen.
Workbook Vs Worksheet Level Navngivne intervaller: Når du opretter et projektmappeniveau med navnet område, kan der kun være ét område med det navn. Så hvis du henviser til det fra eksterne projektmapper eller regneark i den samme projektmappe, behøver du ikke at angive regnearksnavnet (da det er tilgængeligt til brug i hele projektmappen). Hvis du bruger regnearksniveauet navngivet område, vil du kun kunne bruge det i regnearket, og mens du henviser til det, skal du også angive regnearksnavnet.Henvisning til det definerede navn i det samme regneark eller projektmappe
Hvis du har oprettet det definerede navn til projektmappeniveau, kan du bruge det overalt i projektmappen ved blot at bruge det definerede navn selv.
For eksempel, hvis jeg vil få summen af alle cellerne i det navngivne område, vi oprettede (SaledData), kan jeg bruge nedenstående formel:
= SUM (SaledData)
Hvis du har oprettet et regnearksniveau med navnet interval, kan du kun bruge denne formel, hvis det navngivne område er oprettet i det samme ark, hvor du bruger formlen.
Hvis du vil bruge det på et andet ark (f.eks. Sheet2), skal du bruge følgende formel:
= SUM (Sheet1! $ A $ 1: $ A $ 10)
Og hvis der er mellemrum eller alfanumeriske tegn i arknavnet, skal du skrive arknavnet i enkelte anførselstegn.
= SUM ('Ark 1'! $ A $ 1: $ A $ 10)
Henvisning til det definerede navn i en anden projektmappe (åben eller lukket)
Når du vil henvise til et navngivet område i en anden projektmappe, skal du angive projektmappens navn og derefter navnet på området.
Hvis du f.eks. Har en Excel -projektmappe med navnet ExampleFile.xlsx og et navngivet område med navnet SalesData, kan du bruge nedenstående formel til at få summen af dette interval fra en anden projektmappe:
= SUM (EksempelFile.xlsx! SalesData)
Hvis der er mellemrum i filnavnet, skal du bruge disse i enkelte citater.
= SUM ('Eksempelfil.xlsx'! SalesData)
Hvis du har navngivne områder på arkniveau, skal du angive navnet på projektmappen såvel som regnearket, når der henvises til det fra en ekstern projektmappe.
Nedenfor er et eksempel på at referere til et ark-niveau navngivet område:
= SUM ('[Eksempel File.xlsx] Ark1'! SalesData)
Som jeg også nævnte ovenfor, er det altid bedst at oprette navneområder i projektmappeniveau, medmindre du har en stærk grund til at oprette et regneark niveau et.
Hvis du refererer til et navngivet område i en lukket projektmappe, skal du også angive filstien. Nedenfor er et eksempel på dette:
= SUM ('C: \ Brugere \ sumit \ Desktop \ Eksempel File.xlsx'! SalesData)
Når du opretter en reference til et navngivet område i en åben projektmappe og derefter lukker projektmappen, ændrer Excel automatisk referencen og tilføjer filstien.
Sådan oprettes reference til et navngivet område
Hvis du opretter og arbejder med mange navngivne områder, er det ikke muligt at huske hver enkelt navn.
Excel hjælper dig med at vise dig en liste over alle de navngivne områder, du har oprettet, og giver dig mulighed for at indsætte disse i formler med et enkelt klik.
Antag, at du har oprettet et navngivet interval SalesData, som du vil bruge i en formel til at SUMME alle værdierne i det navngivne område.
Her er trinene til at gøre dette:
- Vælg den celle, hvor du vil indtaste formlen.
- Indtast formlen til det punkt, hvor du skal indsætte det navngivne område
- Tryk på F3 -tasten på dit tastatur. Dette åbner dialogboksen Indsæt navn med listen over alle de navne, du har oprettet
- Dobbeltklik på det navn, du vil indsætte.
Ovenstående trin indsætter navnet i formlen, og du kan fortsætte med at arbejde på formlen.
Bemærk: Denne teknik fungerer kun for de navneområder, der er i den samme projektmappe. Hvis du vil have navnene i en ekstern projektmappe, skal du gå derhen, kontrollere navnet ved hjælp af dialogboksen Indsæt navn og derefter vende tilbage og bruge det i din formel (i det korrekte format ved hjælp af filnavnet).Dette er alt hvad du behøver at vide om, hvordan du refererer til andre ark eller projektmapper, og hvordan du opretter en ekstern reference i Excel.
Håber du fandt denne vejledning nyttig.
