For at foretage nøjagtige beregninger i Excel er det vigtigt at forstå, hvordan de forskellige typer cellereferencer fungerer.
A1 vs. R1C1 Referencer
Excel -regneark indeholder mange celler, og hver celle er som standard identificeret med dens spaltebrev efterfulgt af dets rækkenummer. Dette er kendt som referencer i A1-stil. Eksempler: A1, B4, C6
A1 referencestil
Eventuelt kan du skifte til R1C1 Reference Mode at henvise til en celles række & kolonnenummer. I stedet for at henvise til celle A1 vil du henvise til R1C1 (række 1, kolonne 1). Celle C4 ville blive henvist til R4C3.
R1C1 referencestil
Referencer i R1C1-stil er ekstremt ualmindelige i Excel. Medmindre du har en god grund, skal du nok holde dig til standard A1-stil referencetilstand. Men hvis du bruger VBA, vil du sandsynligvis støde på denne referencestil.
Skift til R1C1 Reference Style
Gå til for at skifte referencestil File> Option> Formula. Marker feltet ud for R1C1 referencestil.
Navngivet Ranges
En af de mest underudnyttede funktioner i Excel er funktionen Named Ranges. I stedet for at henvise til en celle (eller gruppe af celler) efter dens celleplacering (ex B3 eller R3C2), kan du navngive dette område og blot referere til områdets navn i dine formler.
Sådan navngives et område:
- Vælg den eller de celler, du vil navngive
- Klik inde i feltet Navn på navne
- Indtast dit ønskede navn
- Hit Enter
Nu kan du henvise til celle A1 ved at skrive = range_name1 i stedet. Dette er meget nyttigt, når du arbejder med store projektmapper med flere regneark.
Rækkevidde af celler
Når du bruger Excel's indbyggede funktioner, skal du muligvis referere til celleområder. Celleområder ser sådan ud 'A1: B3'. Denne reference refererer til alle celler mellem A1 og B3: celler A1, A2, A3, B1, B2, B3.
Sådan vælger du et celleområde, når du indtaster en formel:
- Indtast området (adskil start- og slutområdet med et semikolon)
- Brug musen til at klikke på den første cellereference, hold museknappen nede, og træk det ønskede område.
- Hold skiftetasten nede, og naviger med piletasterne for at vælge dit område
Absolutte (frosne) og relative referencer
Når du indtaster cellereferencer inden for formler, kan du bruge relative eller absolutte (frosne) referencer. Relative referencer flyttes proportionelt, når du kopierer formlen til en ny celle. Absolutte referencer forbliver uændrede. Lad os se på nogle eksempler:
Relativ reference
En relativ reference i Excel ser sådan ud
= A1
Når du kopierer og indsætter en formel med relative referencer, bevæger de relative referencer sig proportionalt. Medmindre du angiver andet, er dine cellereferencer som standard relative (ufrosne).
Eksempel: Hvis du kopierer '= A1' ned ad en række, ændres referencen til '= A2'.
Absolutte (frosne) cellereferencer
Hvis du ikke vil have dine cellereferencer til at flytte, når du kopierer en formel, kan du "fryse" dine cellereferencer ved at tilføje dollartegn ($ s) foran den reference, du vil have frosset. Når du nu kopierer og indsætter formlen, forbliver cellereferencen uændret. Du kan vælge at fastfryse rækkehenvisningen, kolonnehenvisningen eller begge dele.
A1: Intet er frosset
$ A1: Kolonnen er frosset, men rækken er ikke frosset
A $ 1: Rækken er frosset, men kolonnen er ikke frosset
$ A $ 1: Både rækken og kolonnen er frosset
Absolut referencegenvej
Det er ikke særlig praktisk at tilføje dollartegn ($ s) manuelt til dine formler. I stedet, mens du opretter din formel, skal du bruge F4 -tasten til at skifte mellem absolutte/relative cellereferencer.
Eksempel på absolut cellereference
Hvornår skulle du egentlig fryse en cellereference? Et almindeligt eksempel er, når du har inputceller, der ofte refereres til. I eksemplet herunder vil vi beregne momsen for hver mængde menupunkter. Momsen er konstant på tværs af alle varer, så vi refererer til momscellen gentagne gange.
For at finde den samlede moms skal du indtaste formlen '= (B3*C3)*$ C $ 1' i kolonne D og kopiere formlen ned.
Blandet reference
Du har måske hørt om blandede cellereferencer. En blandet reference er, når enten række- eller kolonnehenvisningen er låst (men ikke begge dele).
Blandet reference
Husk, at du ved hjælp af "F4" -tasten kan bladre gennem dine relative, absolutte cellereferencer.
Cellereferencer - Indsætning og sletning af rækker/kolonner
Du undrer dig måske over, hvad der sker med dine cellereferencer, når du indsætter eller sletter rækker/kolonner?
Cellehenvisningen opdateres automatisk for at henvise til den originale celle. Dette er tilfældet, uanset om cellereferencen er frosset.
3D referencer
Nogle gange skal du muligvis arbejde med flere regneark med identiske datamønstre. Excel giver dig mulighed for at referere til flere ark på én gang uden at skulle indtaste hvert regneark manuelt. Du kan referere til et område af ark, der ligner, hvordan det ville referere til et område af celler. Eksempel 'Ark1: Ark5! A1' refererer til celler A1 på alle ark fra ark1 til ark5.
Lad os gå gennem et eksempel:
Du vil sammenlægge det samlede antal solgte enheder for hvert produkt på tværs af alle butikker. Hver butik har sit eget regneark, og alle regnearkene har et identisk format. Du kan oprette en formel, der ligner denne:
Dette er ikke for svært med kun fire regneark, men hvad nu hvis du havde 40 regneark? Vil du virkelig tilføje hver cellereference manuelt?
I stedet kan du nemt bruge en 3D -reference til at referere til flere ark på én gang (svarende til hvordan du kan referere til et område af celler).
Vær forsigtig! Rækkefølgen på dine regneark er vigtig. Hvis du flytter et andet ark ind mellem de refererede ark (StoreA og StoreD), vil det ark blive inkluderet. Omvendt, hvis du flytter et ark uden for arkområdet (før StoreA eller efter StoreD), vil det ikke længere blive inkluderet.
Cirkulær cellereference
En cirkulær cellereference er, når en celle refererer tilbage til sig selv. For eksempel, hvis resultatet af celle B1 bruges som input til celle B1, oprettes en cirkulær reference. Cellen behøver ikke direkte at referere til sig selv. Der kan være mellemliggende trin.
Eksempel:
I dette tilfælde er formlen for celle B2 "A2+A3+B2". Fordi du er i celle B2, må du ikke bruge B2 i ligningen. Dette ville udløse cirkulær reference, og værdien i celle "B2" sættes automatisk til "0".
Normalt er cirkulære referencer et resultat af brugerfejl, men under visse omstændigheder kan du bruge en cirkulær reference. Det primære eksempel på at bruge en cirkulær reference er at beregne værdier iterativt. For at gøre dette skal du gå til Fil> Indstillinger> Formler og Aktiveret iterativ beregning:
Eksterne referencer
Til tider når du beregner data, skal du muligvis henvise til data uden for din projektmappe. Dette kaldes en ekstern reference (link).
Hvis du vil vælge en ekstern reference, mens du opretter en formel, skal du navigere til den eksterne projektmappe og vælge din reference, som du normalt ville.
For at navigere til den anden projektmappe kan du bruge CTRL + TAB -genvejen eller gå til Vis> Skift Windows.
Når du har valgt cellen, ser du, at en ekstern reference, der ser sådan ud:
Bemærk, at projektmappens navn er omsluttet af parenteser [].
Når du lukker den refererede projektmappe, viser referencen filens placering:
Når du genåbner projektmappen, der indeholder det eksterne link, bliver du bedt om at aktivere automatisk opdatering af links. Hvis du gør det, åbner Excel referenceværdien med den aktuelle værdi i projektmappen. Selvom det er lukket! Vær forsigtig! Du vil måske eller måske ikke have dette.
Navngivne intervaller og eksterne referencer
Hvad sker der med din eksterne cellereference, når rækker / kolonner tilføjes eller slettes fra reference -projektmappen? Hvis begge projektmapper er åbne, opdateres cellereferencerne automatisk. Men hvis begge projektmapper ikke er åbne, opdateres cellehenvisningerne ikke og er ikke længere gyldige. Dette er en stor bekymring, når der linkes til eksterne projektmapper. Mange fejl skyldes dette.
Hvis du linker til eksterne projektmapper, skal du navngive cellereferencen med et navngivet område (se forrige afsnit for mere information). Nu vil din formel henvise til det navngivne område, uanset hvilke ændringer der sker på den eksterne projektmappe.
