Når du oppretter en Excel-tabell, tilordner Excel et navn til tabellen, og til hver kolonneoverskrift i tabellen. Når du legger til formler i en Excel-tabell, vises navnene automatisk når du skriver inn formelen og velger cellereferansene i tabellen i stedet for at de må angis manuelt. Her er et eksempel på hva Excel gjør:
I stedet for å bruke eksplisitte cellereferanser |
bruker Excel tabell- og kolonnenavn |
---|---|
=Summer(C2:C7) |
=SUM(AvdSalg[Salgsbeløp]) |
Denne kombinasjonen av tabell- og kolonnenavn kalles en strukturert referanse. Navnene i strukturerte referanser justeres hver gang du legger til eller fjerner data fra tabellen.
Strukturerte referanser vises også når du oppretter en formel utenfor en Excel-tabell som refererer til tabelldataene. Referansene kan gjøre det enklere å finne tabeller i en stor arbeidsbok.
Hvis du skal inkludere strukturerte referanser i en formel, klikker du tabellcellene du vil referere til, i stedet for å skrive inn cellereferansen i formelen. La oss bruke følgende eksempeldata til å skrive inn en formel som automatisk bruker strukturerte referanser til å beregne mengden av en salgsprovisjon.
Selger |
Region |
Salgsbeløp |
Provisjon i % |
Provisjonsbeløp |
---|---|---|---|---|
Joe |
Nord |
260 |
10 % |
|
Robert |
Sør |
660 |
15 % |
|
Michelle |
Øst |
940 |
15 % |
|
Erich |
Vest |
410 |
12 % |
|
Dafna |
Nord |
800 |
15 % |
|
Rob |
Sør |
900 |
15 % |
-
Kopier eksempeldataene i tabellen ovenfor, inkludert kolonneoverskriftene, og lim dem inn i celle A1 i et nytt Excel-regneark.
-
Hvis du vil opprette tabellen, merker du en celle i dataområdet og trykker CTRL+T.
-
Kontroller at det er merket av for Tabellen har overskrifter , og klikk OK.
-
Skriv inn et likhetstegn (=) i celle E2, og klikk celle C2.
I formellinjen vises den strukturerte referansen [@[Salgsbeløp]] etter likhetstegnet.
-
Skriv inn en stjerne (*) rett etter høyre hakeparentes, og klikk celle D2.
I formellinjen vises den strukturerte referansen [@[Provisjon i %]] etter stjernen.
-
Trykk på Enter.
Excel oppretter automatisk en beregnet kolonne og kopierer formelen nedover hele kolonnen for deg, og justerer den for hver rad.
Hva skjer når jeg bruker eksplisitte cellereferanser?
Hvis du skriver inn eksplisitte cellereferanser i en beregnet kolonne, kan det være vanskeligere å se hva formelen beregner.
-
Klikk celle E2 i eksempelregnearket
-
Skriv inn =C2*D2 på formellinjen, og trykk enter.
Legg merke til at når Excel kopierer formlene nedover i kolonnen, brukes det ikke strukturerte referanser. Hvis du for eksempel legger til en kolonne mellom de eksisterende kolonnene C og D, må du revidere formelen.
Hvordan endrer jeg et tabellnavn?
Når du setter inn en Excel-tabell, opprettes det et standard tabellnavn (Tabell1, Tabell2 og så videre), men du kan endre tabellnavnet til noe mer beskrivende.
-
Merk en celle i tabellen for å vise fanen Tabellverktøy > Utforming på båndet.
-
Skriv inn navnet du vil bruke, i Tabellnavn-boksen , og trykk enter.
I våre eksempeldata brukte vi navnet AvdSalg.
Bruk disse reglene for tabellnavn:
-
Bruke gyldige tegn Start alltid et navn med en bokstav, et understrekingstegn (_) eller en omvendt skråstrek (\). Bruk bokstaver, tall, punktum eller understrekingstegn for resten av navnet. Du kan ikke bruke "C", "c", "R", eller "r" for navnet. De er allerede angitt som snarveier for å velge kolonnen eller raden for den aktive cellen når du skriver dem i Navn- eller Gå til-boksen.
-
Ikke bruk cellereferanser Navn kan ikke være identiske med en cellereferanse, for eksempel Z$100 eller R1C1.
-
Ikke bruk mellomrom for å skille ord Mellomrom kan ikke brukes i navnet. Du kan bruke understrekingstegnet (_) og punktum (.) som ordskilletegn. For eksempel AvdSalg, Avgift_ved_salg eller Første.kvartal.
-
Ikke bruk mer enn 255 tegn Et tabellnavn kan ha opptil 255 tegn.
-
Bruk unike tabellnavn Like navn er ikke tillatt. Excel skiller ikke mellom store og små bokstaver i navn, slik at hvis du skriver inn «Salg», men allerede har et annet navn som kalles “ALG i samme arbeidsbok, blir du bedt om å angi et unikt navn.
-
Bruke en objektidentifikator Hvis du har tenkt å ha en blanding av tabeller, pivottabeller og diagrammer, er det lurt å prefikse navnene dine med objekttypen. Eksempel: tbl_Sales for en salgstabell, pt_Sales for en salgspivottabell og chrt_Sales for et salgsdiagram, eller ptchrt_Sales for et salgspivotdiagram. Dette beholder alle navnene dine i en sortert liste i Navnebehandling.
Syntaksregler for strukturerte referanser
Du kan også angi eller endre strukturerte referanser manuelt i formelen, men for å gjøre dette vil det bidra til å forstå strukturert referansesyntaks. La oss gå gjennom følgende formeleksempel:
=SUM(DeptSales[[#Totals],[Sales Amount]],DeptSales[[#Data],[Commission Amount]])
Denne formelen har følgende strukturerte referansekomponenter:
-
Tabellnavn: AvdSalg er et egendefinert tabellnavn. Det refererer til tabelldataene, uten overskrifts- eller totalrader. Du kan bruke et standard tabellnavn, for eksempel Tabell1, eller endre det og bruke et egendefinert navn.
-
Kolonneantall: [Salgsbeløp] og [Provisjonsbeløp] er kolonneangittere som bruker navnene på kolonnene de representerer. De refererer til kolonnedataene, uten noen kolonneoverskrift eller totalrad. Omslutt alltid angivelser med hakeparenteser som vist.
-
Elementangitt: [#Totals] og [#Data] er spesielle elementangittere som refererer til bestemte deler av tabellen, for eksempel totalraden.
-
Tabellangivelsen: [[#Totals],[Salgsbeløp]] og [[#Data],[Provisjonsbeløp]] er tabellangivelser som representerer de ytre delene av den strukturerte referansen. Ytre referanser kommer etter tabellnavnet, og du omslutter dem med hakeparenteser.
-
Strukturert referanse: (AvdSalg[[#Totals],[Salgsbeløp]] og AvdSalg[[#Data],[Provisjonsbeløp]] er strukturerte referanser, representert av en streng som begynner med tabellnavnet og slutter med kolonneantallet.
Bruk følgende syntaksregler når du oppretter eller redigerer strukturerte referanser manuelt:
-
Bruk hakeparenteser rundt angivelser Alle tabellangivelser, kolonneangivelser og spesielle elementangivelser må være omsluttet av hakeparenteser ([ ]). En angivelse som inneholder andre angivelser, må ha ytterparenteser rundt innerparentesene for de andre angivelsene. Eksempel: =AvdSalg[[Selger]:[Område]]
-
Alle kolonneoverskrifter er tekststrenger Men de krever ikke anførselstegn når de brukes i en strukturert referanse. Tall eller datoer, for eksempel 2014 eller 1.1.2014, regnes også som tekststrenger. Du kan ikke bruke uttrykk med kolonneoverskrifter. Uttrykket AvdSalgÅrSammendrag[[2014]:[2012]] vil for eksempel ikke fungere.
Bruk hakeparenteser rundt kolonneoverskrifter med spesialtegn Hvis det er spesialtegn, må hele kolonneoverskriften være omsluttet av hakeparenteser, som betyr at doble hakeparenteser er obligatoriske i en kolonneangivelse. Eksempel: =AvdSalgÅrSammendrag[[TotalBelKr]]
Her er listen over spesialtegn som trenger ekstra parenteser i formelen:
-
TAB
-
Linjefeed
-
Linjeskift
-
Komma (,)
-
Kolon (:)
-
Punktum (.)
-
Venstre hakeparentes ([)
-
Høyre hakeparentes (])
-
Nummertegn (#)
-
Enkelt anførselstegn (')
-
Dobbelt anførselstegn (")
-
Venstre klammeparentes ({)
-
Høyre klammeparentes (})
-
Dollartegn ($)
-
Cirkumførtegn (^)
-
Ampersand (&)
-
Stjerne (*)
-
Plusstegn (+)
-
Likhetstegn (=)
-
Minustegn (-)
-
Større enn-symbol (>)
-
Mindre enn symbol (<)
-
Divisjonstegn (/)
-
Ved tegn (@)
-
Omvendt skråstrek (\)
-
Utropstegn (!)
-
Venstre parentes (()
-
Høyreparentes ())
-
Prosenttegn (%)
-
Spørsmålstegn (?)
-
Backtick (')
-
Semikolon (;)
-
Tilde (~)
-
Understrekingstegn (_)
-
Bruk escape-tegn for noen spesialtegn i kolonneoverskrifter Noen tegn har en spesiell betydning og krever at et enkelt anførselstegn (') brukes som escape-tegn. Eksempel: =AvdSalgÅrSammendrag['#AntElementer]
Her er listen over spesialtegn som trenger escape-tegn (') i formelen:
-
Venstre hakeparentes ([)
-
Høyre hakeparentes (])
-
Nummertegn(#)
-
Enkelt anførselstegn (')
-
Ved tegn (@)
Bruk mellomromstegn for å gjøre strukturerte referanser lettere å lese Du kan bruke mellomromstegn for å gjøre det lettere å lese en strukturert referanse. Eksempel: =AvdSalg[ [Selger]:[Distrikt] ] eller =AvdSalg[[#Headers], [#Data], [Provisjon i %]]
Det anbefales å bruke ett mellomrom:
-
Etter den første venstre hakeparentesen ([)
-
Foran den siste høyre klammeparentesen (]).
-
Etter et komma.
Referanseoperatorer
For å kunne angi celleområder på en mer fleksibel måte kan du bruke referanseoperatorene nedenfor til å kombinere kolonneangivelser.
Denne strukturerte referansen: |
Refererer til: |
Ved å bruke: |
Som er celleområdet: |
---|---|---|---|
=AvdSalg[[Selger]:[Distrikt]] |
Alle cellene i to eller flere tilstøtende kolonner |
Områdeoperatoren : (kolon) |
A2:B7 |
=AvdSalg[Salgsbeløp],AvdSalg[Provisjonsbeløp] |
En kombinasjon av to eller flere kolonner |
Unionoperatoren , (komma) |
C2:C7, E2:E7 |
=AvdSalg[[Selger]:[Salgsbeløp]] AvdSalg[[Distrikt]:[Provisjon i %]] |
Skjæringspunktet mellom to eller flere kolonner |
Skjæringspunktoperatoren (mellomrom) |
B2:C7 |
Spesielle elementangivelser
Hvis du skal referere til bestemte deler av en tabell, for eksempel bare til Total-raden, kan du bruke disse spesielle elementangivelsene i de strukturerte referansene.
Denne spesielle elementangivelsen: |
Refererer til: |
---|---|
#All |
Hele tabellen, inkludert kolonneoverskrifter, data og eventuelle totaler. |
#Data |
Bare dataradene. |
#Headers |
Bare overskriftsraden. |
#Totals |
Bare totalraden. Hvis denne ikke finnes, returneres null. |
#This Row eller @ eller @[Kolonnenavn] |
Bare cellene i den samme raden som formelen. Disse angivelsene kan ikke kombineres med andre spesielle elementangivelser. Bruk dem til å fremtvinge virkemåten implisitt skjæringspunkt for referansen, eller til å overstyre virkemåten implisitt skjæringspunkt og referere til enkeltverdier fra en kolonne. Excel endrer automatisk #This Row-angivelser til de kortere @-angivelsene i tabeller som har mer enn én rad med data. Men hvis tabellen bare har én rad, erstatter ikke Excel #This Row-angivelsen, som kan forårsake uventede beregningsresultater når du legger til flere rader. For å unngå beregningsproblemer må du passe på at du angir flere rader i tabellen før du skriver inn strukturerte referanseformler. |
Kvalifiser strukturerte referanser i beregnede kolonner
Når du oppretter en beregnet kolonne, bruker du som regel en strukturert referanse til å lage formelen. Denne strukturerte referansen kan være ukvalifisert eller fullt kvalifisert. Hvis du for eksempel vil opprette den beregnede kolonnen, kalt Provisjonsbeløp, som beregner provisjonsbeløpet i dollar, kan du bruke følgende formler:
Type strukturert referanse |
Eksempel |
Kommentar |
---|---|---|
Ukvalifisert |
=[Salgsbeløp]*[Provisjon i %] |
Multipliserer de tilsvarende verdiene fra gjeldende rad. |
Fullt kvalifisert |
=AvdSalg[Salgsbeløp]*AvdSalg[Provisjon i %] |
Multipliserer de tilsvarende verdiene for hver rad for begge kolonner. |
Den generelle regelen er som følger: Hvis du bruker strukturerte referanser i en tabell, for eksempel når du oppretter en beregnet kolonne, kan du bruke en ukvalifisert strukturert referanse. Hvis du derimot bruker den strukturerte referansen utenfor tabellen, må du bruke en fullt kvalifisert strukturert referanse.
Eksempler på bruk av strukturerte referanser
Her er noen måter du kan bruke strukturerte referanser på:
Denne strukturerte referansen: |
Refererer til: |
Som er celleområdet: |
---|---|---|
=AvdSalg[[#All],[Salgsbeløp]] |
Alle cellene i Salgsbeløp-kolonnen. |
C1:C8 |
=AvdSalg[[#Headers],[Provisjon i %]] |
Overskriften i kolonnen Provisjon i %. |
D1 |
=AvdSalg[[#Totals],[Distrikt]] |
Totalen for Distrikt-kolonnen. Hvis det ikke finnes noen Total-rad, returneres null. |
B8 |
=AvdSalg[[#All],[Salgsbeløp]:[Provisjon i %]] |
Alle cellene i Salgsbeløp og Provisjon i %. |
C1:D8 |
=AvdSalg[[#Data],[Provisjon i %]:[Provisjonsbeløp]] |
Bare dataene i kolonnene Provisjon i % og Provisjonsbeløp. |
D2:E7 |
=AvdSalg[[#Headers],[Distrikt]:[Provisjonsbeløp]] |
Bare overskriftene i kolonnene mellom Distrikt og Provisjonsbeløp. |
B1:E1 |
=AvdSalg[[#Totals],[Salgsbeløp]:[Provisjonsbeløp]] |
Totalene for kolonnene Salgsbeløp til og med Provisjonsbeløp. Hvis det ikke finnes noen Total-rad, returneres null. |
C8:E8 |
=AvdSalg[[#Headers],[#Data],[Provisjon i %]] |
Bare overskriften og dataene for Provisjon i %. |
D1:D7 |
=AvdSalg[[#This Row], [Provisjonsbeløp]] eller =AvdSalg[@Provisjonsbeløp] |
Cellen i skjæringspunktet mellom gjeldende rad og Provisjonsbeløp-kolonnen. Hvis den brukes i samme rad som en overskrift eller totalrad, returnerer dette en #VALUE! -feil. Hvis du skriver inn den lengre formen av denne strukturerte referansen (#This Row) i en tabell med flere rader med data, erstatter Excel den automatisk med den kortere formen (@). De fungerer på samme måte. |
E5 (hvis gjeldende rad er 5) |
Strategier for å arbeide med strukturerte referanser
Vurder følgende når du arbeider med strukturerte referanser.
-
Bruk Autofullfør formel Autofullfør formel kan være svært nyttig når du skriver inn strukturerte referanser og vil være sikker på at syntaksen blir riktig. Hvis du vil ha mer informasjon, kan du se Bruke Autofullfør formel.
-
Bestem om du vil generere strukturerte referanser for tabeller i semi-valg Når du oppretter en formel, merkes cellene som standard når du klikker et celleområde i en tabell, og automatisk angis en strukturert referanse i stedet for celleområdet i formelen. Denne delvise merkingen gjør det mye enklere å angi en strukturert referanse. Du kan aktivere eller deaktivere denne virkemåten ved å merke av for eller fjerne merket for Bruk tabellnavn i formler i dialogboksen Fil > Alternativer > formler > Arbeide med formler .
-
Bruk arbeidsbøker med eksterne koblinger til Excel-tabeller i andre arbeidsbøker Hvis en arbeidsbok inneholder en ekstern kobling til en Excel-tabell i en annen arbeidsbok, må den koblede kildearbeidsboken være åpen i Excel for å unngå #REF! -feil i målarbeidsboken som inneholder koblingene. Hvis du åpner målarbeidsboken først og #REF! vises, vil de bli løst hvis du deretter åpner kildearbeidsboken. Hvis du åpner kildearbeidsboken først, skal det ikke vises feilkoder.
-
Konverter et område til en tabell, eller en tabell til et område Når du konverterer en tabell til et område, endres alle cellereferansene til tilsvarende absolutte A1-stilreferanser. Når du konverterer et område til en tabell, endrer ikke Excel automatisk noen cellereferanser i dette området til tilsvarende strukturerte referanser.
-
Slå av kolonneoverskrifter Du kan slå tabellkolonneoverskrifter på og av fra tabellutformingsfanen > overskriftsrad. Hvis du deaktiverer kolonneoverskrifter i tabeller, påvirkes ikke strukturerte referanser som bruker kolonnenavn, og du kan fortsatt bruke dem i formler. Strukturerte referanser som refererer direkte til tabelloverskriftene (f.eks. =AvdSalg[[#Headers],[%Provisjon]]) vil resultere i #REF.
-
Legg til eller slett kolonner og rader i tabellen Fordi tabelldataområder ofte endres, justeres cellereferansene for strukturerte referanser automatisk. Hvis du for eksempel bruker et tabellnavn i en formel til å telle alle dataene i en tabell, og hvis du deretter legger til en rad med data, justeres cellereferansen automatisk.
-
Endre navn på en tabell eller kolonne Hvis du endrer navnet på en kolonne eller tabell, endrer Excel automatisk bruken av tabell- og kolonneoverskriften i alle strukturerte referanser som brukes i arbeidsboken.
-
Flytt, kopier og fyll ut strukturerte referanser Strukturerte referanser endres ikke når du kopierer eller flytter en formel der en strukturert referanse brukes.
Obs!: Kopiering av en strukturert referanse og fyll av en strukturert referanse er ikke det samme. Når du kopierer, forblir alle de strukturerte referansene de samme, mens når du fyller ut en formel, justerer fullstendige strukturerte referanser kolonneangitterne som en serie som summert i tabellen nedenfor.
Hvis utfyllingsretningen er: |
Og mens du fyller, trykker du : |
Skjer dette: |
---|---|---|
Opp eller ned |
Ingenting |
Det er ikke angitt noen justering for kolonneangivelse. |
Opp eller ned |
CTRL |
Kolonneangivelser justeres som en serie. |
Høyre eller venstre |
Ingen |
Kolonneangivelser justeres som en serie. |
Opp, ned, høyre eller venstre |
SKIFT |
I stedet for at verdier i gjeldende celler overskrives, flyttes gjeldende celleverdier, og kolonneangivelser settes inn. |
Trenger du mer hjelp?
Du kan alltid spørre en ekspert i det tekniske fellesskapet for Excel eller få støtte i Fellesskap.
Beslektede emner
Oversikt over Excel-tabeller Video: Opprette og formatere en Excel-tabell Summere dataene i en Excel-tabell Formatere en Excel-tabell Endre størrelsen på en tabell ved å legge til eller fjerne rader og kolonner Filtrere data i et område eller en tabell Konvertere en tabell til et område Kompatibilitetsproblemer med Excel-tabeller Eksportere en Excel-tabell til SharePoint Oversikt over formler i Excel