For å kunne referere til celler og områder er det som får et regnearkverktøy til å fungere. Og Excel er den beste og kraftigste der ute.
I denne opplæringen vil jeg dekke alt du trenger å vite om hvordan du refererer til celler og områder i Excel. Bortsett fra den grunnleggende referansen på det samme arket, vil hoveddelen av denne opplæringen handle om hvordan referere til et annet ark eller en arbeidsbok i Excel.
Selv om det ikke er så stor forskjell på hvordan det fungerer, endres formatet til den referansen litt når du refererer til et annet ark i samme fil eller refererer til en helt egen Excel -fil.
Det er også noen viktige ting du må huske på når du refererer til et annet ark eller andre eksterne filer.
Men bekymre deg … ingenting for gal!
Når du er ferdig med denne opplæringen, vet du alt du trenger å vite om referanse til celler og områder i Excel (vær i den samme arbeidsboken eller en annen arbeidsbok).
La oss komme i gang!
Refererer til en celle i samme ark
Dette er det mest grunnleggende referansenivået der du refererer til en celle på samme ark.
For eksempel, hvis jeg er i celle B1 og jeg vil referere til celle A1, vil formatet være:
= A1
Når du gjør dette, vil verdien i cellen der du bruker denne referansen være den samme som i celle A1. Og hvis du gjør noen endringer i celle A1, vil disse gjenspeiles i cellen der du har brukt denne referansen.
Refererer til en celle i det andre arket
Hvis du må referere til et annet ark i den samme arbeidsboken, må du bruke formatet nedenfor:
Arknavn! Celleadresse
Først har du arknavnet etterfulgt av et utropstegn som blir fulgt av cellereferansen.
Så hvis du trenger å referere til celle A1 i ark 1, må du bruke følgende referanse:
= Ark1! A1
Og hvis du vil referere til en rekke celler i et annet ark, må du bruke følgende format:
Sheet_name! First_cell: Last_cell
Så hvis du vil referere til området A1: C10 i et annet ark i den samme arbeidsboken, må du bruke referansen nedenfor:
= Ark1! A1: C10
Vær oppmerksom på at jeg bare har vist deg referansen til cellen eller området. I virkeligheten vil du bruke disse i formler. Men formatet på referansene nevnt ovenfor kommer til å forbli det samme
I mange tilfeller vil regnearket du refererer til ha flere ord i navnet. Det kan for eksempel være Prosjektdata eller salgsdata.
Hvis du har mellomrom eller ikke -alfabetiske tegn (for eksempel @,!, #, -, osv.), Må du bruke navnet i enkle anførselstegn.
Hvis du for eksempel vil referere til celle A1 i arket Salgsdata, bruker du referansen nedenfor:
= 'Salgsdata'! A1
Og hvis navnet på arket er salgsdata, må du bruke referansen nedenfor for å referere til celle A1 i dette arket:
= 'Salgsdata'! A1
Når du refererer til et ark i den samme arbeidsboken, og senere endrer navnet på regnearket, trenger du ikke å bekymre deg for at referansen brytes ned. Excel oppdaterer disse referansene automatisk for deg.
Selv om det er flott å vite formatet til disse referansene, er det i praksis ikke så lurt å skrive disse manuelt hver gang. Det vil være tidkrevende og svært utsatt for feil.
La meg vise deg en bedre måte å lage cellereferanser i Excel.
Oppretter automatisk referanse til et annet ark i samme arbeidsbok
En mye bedre måte å opprette cellereferanse til et annet ark, er å ganske enkelt peke Excel på cellen/området du vil opprette referansen til, og la Excel lage den selv.
Dette vil sikre at du ikke trenger å bekymre deg for at utropstegnet eller anførselstegn mangler eller at andre formatering dukker opp. Excel vil automatisk lage den riktige referansen for deg.
Nedenfor er trinnene for automatisk å opprette en referanse til et annet ark:
- Velg cellen i gjeldende arbeidsbok der du trenger referansen
- Skriv inn formelen til du trenger referansen (eller et likhetstegn hvis du bare vil ha referansen)
- Velg arket du trenger å referere til
- Velg cellen/området du vil referere til
- Trykk Enter for å få resultatet av formelen (eller fortsett å jobbe med formelen)
De ovennevnte trinnene ville automatisk opprette en referanse til cellen/området i et annet ark. Du vil også kunne se disse referansene i formellinjen. Når du er ferdig, kan du bare trykke på enter -tasten, og det vil gi deg resultatet.
For eksempel, hvis du har noen data i celle A1: A10 i et ark som heter Salgsdata, og du vil få summen av disse verdiene i det gjeldende arket, følger trinnene:
- Skriv inn følgende formel i gjeldende ark (der du trenger resultatet): = Sum (
- Velg "Salgsdata" -arket.
- Velg området du vil legge til (A1: A10). Så snart du gjør dette, oppretter Excel automatisk en referanse til dette området (du kan se det i formellinjen)
- Trykk enter -tasten.
Når du oppretter en lang formel, må du kanskje referere til en celle eller et område i et annet ark, og deretter ha et behov for å komme tilbake til opprinnelsesarket og referere til en celle/område der.
Når du gjør dette, vil du legge merke til at Excel automatisk setter inn en arkreferanse til arket der du har formelen. Selv om dette er greit og ikke skader, er det ikke nødvendig. I et slikt tilfelle kan du velge å beholde referansen eller fjerne den manuelt.
En annen ting du trenger å vite når du oppretter referanser ved å velge arket og deretter cellen/området er at Excel alltid vil opprette en relativ referanse (dvs. referanser med n0 $ -tegn). Dette betyr at hvis jeg kopierer og limer inn formelen (en med referanse til et annet ark) i en annen celle, vil den automatisk justere referansen.
Her er et eksempel som forklarer relative referanser.
Anta at jeg bruker følgende formel i celle A1 i gjeldende ark (for å referere til celle A1 i et arknavn SalesData)
= SalesData! A1
Hvis jeg kopierer denne formelen og limer inn i celle A2, vil formelen endres til:
= SalesData! A1
Dette skjer fordi formelen er relativ, og når jeg kopierer og limer den inn, vil referansene automatisk justeres.
Hvis jeg vil at denne referansen alltid skal referere til celle A1 i SalesData -arket, må jeg bruke formelen nedenfor:
= SalesData! $ A $ 1
Dollartegnet før rad- og kolonnenummer låser disse referansene slik at disse ikke endres.
Her er en detaljert opplæring der du kan lære mer om absolutte, blandede og relative referanser.
Nå som vi har dekket hvordan vi refererer til et annet ark i den samme arbeidsboken, la oss se hvordan vi kan referere til en annen arbeidsbok.
Hvordan referere til en annen arbeidsbok i Excel
Når du refererer til en celle eller et område til en annen Excel -arbeidsbok, vil formatet til referansen avhenge av om arbeidsboken er åpen eller lukket.
Og selvfølgelig spiller navnet på arbeidsboken og regnearkene også en rolle for å bestemme formatet (avhengig av om du har mellomrom eller ikke-alfabetiske tegn i navnet eller ikke).
Så la oss se de forskjellige formatene for eksterne referanser til en annen arbeidsbok i forskjellige scenarier.
Ekstern referanse til en åpen arbeidsbok
Når det gjelder å referere til en ekstern åpen arbeidsbok, må du spesifisere arbeidsboknavnet, regnearknavnet og celle-/områdeadressen.
Nedenfor er formatet du må bruke når du refererer til en ekstern åpen arbeidsbok
= '[Filnavn] Arknavn! CellAddress
Anta at du har en arbeidsbok 'Eksempelfil.xlsx', og du vil referere til celle A1 i Ark1 i denne arbeidsboken.
Nedenfor er referansen for dette:
= [ExampleFile.xlsx] SalesData! A1
Hvis det er mellomrom i det eksterne arbeidsboknavnet eller arkenavnet (eller begge deler), må du legge til filnavnet (i firkantede parenteser) og arknavnet i enkle anførselstegn.
Nedenfor er eksemplene der du trenger å ha navnene i enkle anførselstegn:
= '[Eksempel File.xlsx] SalesData'! A1 = '[ExampleFile.xlsx] Salgsdata'! A1 = '[Eksempel File.xlsx] Salgsdata'! A1
Hvordan lage referanse til en annen arbeidsbok (automatisk)
Igjen, selv om det er godt å kjenne formatet, er det best å ikke skrive det manuelt.
I stedet peker du Excel i riktig retning, og det vil lage disse referansene for deg. Dette er mye raskere med langt færre sjanser for feil.
Hvis du for eksempel har noen data i celle A1: A10 i en arbeidsbok som heter 'Eksempelfil' i arket 'Salgsdata', og du vil få summen av disse verdiene i det gjeldende arket, følger trinnene :
- Skriv inn følgende formel i gjeldende ark (der du trenger resultatet): = Sum (
- Gå til arbeidsboken "Eksempelfil"
- Velg "Salgsdata" -arket.
- Velg området du vil legge til (A1: A10). Så snart du gjør dette, oppretter Excel automatisk en referanse til dette området (du kan se det i formellinjen)
- Trykk enter -tasten.
Dette ville umiddelbart opprette formelen med riktige referanser.
En ting du vil legge merke til når du oppretter en referanse til en ekstern arbeidsbok, er at den alltid vil skape absolutte referanser. Dette betyr at det er et $ -tegn før rad- og kolonnetallene. Dette betyr at hvis du kopierer og limer inn denne formelen til andre celler, vil den fortsette å referere til det samme området på grunn av absolutt referanse.
Hvis du vil at dette skal endres, må du endre referansene manuelt.
Ekstern referanse til en lukket arbeidsbok
Når en ekstern arbeidsbok er åpen og du refererer til denne arbeidsboken, trenger du bare å angi filnavn, arknavn og celle-/områdeadresse.
Men når dette er lukket, har Excel ingen anelse om hvor du ser etter cellene/området du refererte til.
Dette er grunnen til at når du oppretter en referanse til en lukket arbeidsbok, må du også spesifisere filbanen.
Nedenfor er en referanse som refererer til celle A1 i Sheet1 -regnearket i Eksempelfil -arbeidsbok. Siden denne filen ikke er åpen, refererer den også til stedet der filen er lagret.
= 'C: \ Users \ sumit \ Desktop \ [Eksempel File.xlsx] Ark1'! $ A $ 1
Referansen ovenfor har følgende deler:
- Filbane - plasseringen på systemet eller nettverket der den eksterne filen er plassert
- Filnavn - navnet på den eksterne arbeidsboken. Dette vil også inkludere filtypen.
- Arknavn - navnet på arket du refererer til cellene/områdene i
- Celle-/områdeadresse - den nøyaktige celle-/områdeadressen du refererer til
Når du oppretter en ekstern referanse til en åpen arbeidsbok og deretter lukker arbeidsboken, vil du legge merke til at referansen automatisk endres. Etter at den eksterne arbeidsboken er lukket, setter Excel automatisk inn en referanse til filbanen også.
Virkningen av å endre filplassering på referanser
Når du oppretter en referanse til en celle/område i en ekstern Excel -fil og deretter lukker den, bruker referansen nå også filbanen.
Men så hvis du endrer filplasseringen, kommer ingenting til å endre seg i arbeidsboken din (der du oppretter referansen). Men siden du har endret plassering, er koblingen nå brutt.
Så hvis du lukker og åpner denne arbeidsboken, vil den fortelle deg at koblingen er ødelagt, og du må enten oppdatere koblingen eller bryte den helt. Det vil vise deg en melding som vist nedenfor:
Når du klikker på Oppdater, vil det vise deg en annen melding der du kan velge alternativene for å redigere koblingene (som viser deg dialogboksen nedenfor)
Hvis du trenger å beholde disse filene knyttet, kan du angi den nye plasseringen av filen ved å klikke på Oppdater verdier. Excel åpner en dialogboks for deg der du kan angi den nye filplasseringen ved å navigere dit og velge den.
Henvisning til et definert navn (i samme eller eksterne arbeidsbok)
Når du må referere til celler og områder, er en bedre måte å lage definerte navn for områdene.
Dette er nyttig, da det gjør det enkelt for deg å referere til disse områdene ved å bruke et navn i stedet for en lang og komplisert referanseadresse.
For eksempel er det enklere å bruke = SalesData i stedet for = [Eksempel File.xlsx] Ark1 ′! $ A $ 1: $ A $ 10
Og hvis du har brukt denne definerte navngitte i flere formler og du må endre referansen, trenger du bare å gjøre det én gang.
Her er trinnene for å lage et navngitt område for en rekke celler:
- Velg alle cellene du vil inkludere i det navngitte området
- Klikk kategorien Formler
- Klikk på alternativet Definer navn (det er i gruppen Definerte navn)
- I dialogboksen Nytt navn, gi dette området et navn (jeg bruker navnet SalesData i dette eksemplet). Husk at du ikke kan ha mellomrom i navnet
- Behold omfanget som arbeidsbok (med mindre du har en sterk grunn til å gjøre det til arknivå)
- Kontroller at referansen til området er riktig.
- Klikk OK.
Nå er det navngitte området ditt opprettet, og du kan bruke det i stedet for cellereferansene med celleadresser.
For eksempel, hvis jeg vil få summen av alle disse cellene i SalesData -området, kan du bruke formelen nedenfor:
= SUMME (salgsdata)
Og hva om du vil bruke dette navngitte området, er andre regneark eller andre arbeidsbøker?
Du kan!
Du trenger bare å følge det samme formatet som vi har diskutert i delen ovenfor.
Du trenger ikke gå tilbake til begynnelsen av denne artikkelen. La meg gi deg alle eksemplene her selv, slik at du får ideen.
Workbook Vs Worksheet Level Navngitte områder: Når du oppretter et arbeidsboknivå som heter område, kan det bare være ett område med det navnet. Så hvis du refererer til det fra eksterne arbeidsbøker eller regneark i samme arbeidsbok, trenger du ikke å spesifisere regnearknavnet (ettersom det er tilgjengelig for bruk i hele arbeidsboken). Hvis du bruker området med regnearknivå med navn, vil du bare kunne bruke det i regnearket, og mens du refererer til det, må du også spesifisere regnearkets navn.Refererer til det definerte navnet i samme regneark eller arbeidsbok
Hvis du har opprettet det definerte navnet for arbeidsboknivået, kan du bruke det hvor som helst i arbeidsboken ved å bare bruke det definerte navnet selv.
For eksempel, hvis jeg vil få summen av alle cellene i det navngitte området vi opprettet (SaledData), kan jeg bruke formelen nedenfor:
= SUM (SaledData)
Hvis du har opprettet et regnearknivå med navnet område, kan du bare bruke denne formelen hvis det navngitte området er opprettet i det samme arket der du bruker formelen.
Hvis du vil bruke det på et annet ark (si Sheet2), må du bruke følgende formel:
= SUMME (Sheet1! $ A $ 1: $ A $ 10)
Og i tilfelle det er mellomrom eller alfanumeriske tegn i arknavnet, må du sette arknavnet i enkelt anførselstegn.
= SUMME ('Ark 1'! $ A $ 1: $ A $ 10)
Refererer til det definerte navnet i en annen arbeidsbok (åpen eller lukket)
Når du vil referere til et navngitt område i en annen arbeidsbok, må du spesifisere arbeidsboknavnet og deretter navnet på området.
For eksempel, hvis du har en Excel -arbeidsbok med navnet ExampleFile.xlsx og et navngitt område med navnet SalesData, kan du bruke formelen nedenfor for å få summen av dette området fra en annen arbeidsbok:
= SUMME (eksempelFile.xlsx! SalesData)
Hvis det er mellomrom i filnavnet, må du bruke disse i enkle anførselstegn.
= SUMME ('Eksempelfil.xlsx'! SalesData)
Hvis du har navngitte områder på arknivå, må du spesifisere navnet på arbeidsboken så vel som regnearket når du refererer til den fra en ekstern arbeidsbok.
Nedenfor er et eksempel på referanse til et arkivert navnområde:
= SUMME ('[Eksempel File.xlsx] Ark1'! SalesData)
Som jeg også nevnte ovenfor, er det alltid best å lage arbeidsbokenivå med navnområder, med mindre du har en sterk grunn til å lage et regneark nivå ett.
Hvis du refererer til et navngitt område i en lukket arbeidsbok, må du også spesifisere filbanen. Nedenfor er et eksempel på dette:
= SUMMER ('C: \ Brukere \ sumit \ Desktop \ Eksempelfil.xlsx'! SalesData)
Når du oppretter en referanse til et navngitt område i en åpen arbeidsbok og deretter lukker arbeidsboken, endrer Excel automatisk referansen og legger til filbanen.
Hvordan lage referanse til et navngitt område
Hvis du oppretter og jobber med mange navngitte områder, er det ikke mulig å huske hver enkelt navn.
Excel hjelper deg med å vise deg en liste over alle de navngitte områdene du har opprettet, og lar deg sette inn disse i formler med et enkelt klikk.
Anta at du har opprettet et navngitt område SalesData som du vil bruke i en formel for å SUMME alle verdiene i det navngitte området.
Her er trinnene for å gjøre dette:
- Velg cellen du vil angi formelen i.
- Skriv inn formelen til det punktet der du trenger å sette inn det navngitte området
- Trykk på F3 -tasten på tastaturet. Dette åpner dialogboksen Lim inn navn med listen over alle navnene du har opprettet
- Dobbeltklikk på navnet du vil sette inn.
Trinnene ovenfor vil sette inn navnet i formelen, og du kan fortsette å jobbe med formelen.
Merk: Denne teknikken fungerer bare for navneområdene som er i samme arbeidsbok. Hvis du vil få navnene i en ekstern arbeidsbok, må du gå dit, sjekke navnet ved hjelp av dialogboksen Lim inn navn og deretter komme tilbake og bruke det i formelen din (i riktig format ved hjelp av filnavnet).Dette er alt du trenger å vite om hvordan du refererer til andre ark eller arbeidsbøker og hvordan du oppretter en ekstern referanse i Excel.
Håper du synes denne opplæringen var nyttig.
