Hvis du bare trenger å kopiere en enkelt formel i Excel 2016, bruk Autofyll-funksjonen eller Kopier og Lim inn-kommandoene. Denne typen formelkopiering, selv om den er ganske vanlig, kan ikke gjøres med dra og slipp.
Ikke glem alternativet Totals på hurtiganalyseverktøyet. Du kan bruke den til å lage en rad eller en kolonne med totaler i bunnen eller høyre kant av en datatabell på et blunk. Bare velg tabellen som et celleområde og klikk på Hurtiganalyse-knappen etterfulgt av Totals i paletten. Klikk deretter Sum-knappen på begynnelsen av paletten for å lage formler som summerer kolonnene i en ny rad nederst i tabellen og/eller Sum-knappen på slutten av paletten for å lage formler som summerer radene i en ny kolonne i høyre ende.
Slik kan du bruke Autofyll til å kopiere én formel til en rekke celler. I denne figuren kan du se Mother Goose Enterprises – 2016 Sales-regnearket med alle selskapene, men denne gangen med kun én månedlig totalsum i rad 12, som er i ferd med å bli kopiert gjennom celle E12.
Kopiere en formel til et celleområde med Autofyll.
Følgende figur viser regnearket etter å ha trukket fyllhåndtaket i celle B12 for å velge celleområdet C12:E12 (hvor denne formelen skal kopieres).
Regnearket etter å ha kopiert formelen for det månedlige (og kvartalsvise) salget.
Relativt sett
Figuren viser regnearket etter at formelen i en celle er kopiert til celleområdet C12:E12 og celle B12 er aktiv. Legg merke til hvordan Excel håndterer kopiering av formler. Den opprinnelige formelen i celle B12 er som følger:
=SUM(B3:B11)
Når den opprinnelige formelen kopieres til celle C12, endrer Excel formelen litt slik at den ser slik ut:
=SUM(C3:C11)
Excel justerer kolonnereferansen, endrer den fra B til C, fordi du kopierte fra venstre til høyre over radene.
Når du kopierer en formel til et celleområde som strekker seg nedover radene, justerer Excel radnumrene i de kopierte formlene i stedet for kolonnebokstavene for å passe til plasseringen til hver kopi. Celle E3 i Mother Goose Enterprises – 2016 Sales-regnearket inneholder for eksempel følgende formel:
=SUM(B3:D3)
Når du kopierer denne formelen til celle E4, endrer Excel kopien av formelen til følgende:
=SUM(B4:D4)
Excel justerer radreferansen for å holde seg oppdatert med den nye rad 4-posisjonen. Fordi Excel justerer cellereferansene i kopier av en formel i forhold til retningen for kopieringen, er cellereferansene kjent som relative cellereferanser .
Noen ting er absolutte!
Alle nye formler du oppretter inneholder naturlig relative cellereferanser med mindre du sier noe annet. Fordi de fleste kopier du lager av formler krever justeringer av cellereferansene deres, trenger du sjelden å tenke over dette arrangementet. Så, innimellom, kommer du over et unntak som krever å begrense når og hvordan cellereferanser justeres i kopier.
Et av de vanligste av disse unntakene er når du ønsker å sammenligne en rekke forskjellige verdier med en enkelt verdi. Dette skjer oftest når du vil beregne hvor stor prosentandel hver del er av totalen. For eksempel, i Mother Goose Enterprises – 2016 Sales-regnearket, møter du denne situasjonen når du oppretter og kopierer en formel som beregner hvor stor prosentandel hver månedlig totalsum (i celleområdet B14:D14) er av den kvartalsvise summen i celle E12.
Anta at du vil angi disse formlene i rad 14 i Mother Goose Enterprises – 2016 Sales-regnearket, med start i celle B14. Formelen i celle B14 for å beregne prosentandelen av januar-salget-til-første kvartal-total er veldig enkel:
=B12/E12
Denne formelen deler salgssummen for januar i celle B12 med den kvartalsvise summen i E12 (hva kan være enklere?). Se imidlertid på hva som ville skje hvis du dro fyllhåndtaket en celle til høyre for å kopiere denne formelen til celle C14:
=C12/F12
Justeringen av den første cellereferansen fra B12 til C12 er akkurat det legen bestilte. Imidlertid er justeringen av den andre cellereferansen fra E12 til F12 en katastrofe. Ikke bare beregner du ikke hvor stor prosentandel februarsalget i celle C12 er av salget i første kvartal i E12, men du ender også opp med en av de grusomme #DIV/0! feil ting i celle C14.
For å hindre Excel i å justere en cellereferanse i en formel i alle kopier du lager, konverter cellereferansen fra relativ til absolutt. Dette gjør du ved å trykke på funksjonstasten F4, etter at du har satt Excel i redigeringsmodus (F2). Excel indikerer at du gjør cellereferansen absolutt ved å plassere dollartegn foran kolonnebokstaven og radnummeret. For eksempel, i denne figuren, inneholder celle B14 den riktige formelen for å kopiere til celleområdet C14:D14:
Kopier formelen for å beregne forholdet mellom månedlig og kvartalsvis salg med en absolutt cellereferanse.
=B12/$E$12
Se på regnearket etter at denne formelen er kopiert til området C14:D14 med fyllhåndtaket og celle C14 er valgt (se følgende figur). Legg merke til at formellinjen viser at denne cellen inneholder følgende formel:
Regnearket etter kopiering av formelen med den absolutte cellereferansen.
=C12/$E$12
Fordi E12 ble endret til $E$12 i den opprinnelige formelen, har alle kopiene den samme absolutte (ikke-endrende) referansen.
Hvis du tuller og kopierer en formel der en eller flere av cellereferansene burde vært absolutte, men du lot dem alle være relative, rediger den opprinnelige formelen som følger:
Dobbeltklikk på cellen med formelen eller trykk på F2 for å redigere den.
Plasser innsettingspunktet et sted på referansen du vil konvertere til absolutt.
Trykk på F4.
Når du er ferdig med å redigere, klikker du på Enter-knappen på formellinjen og kopierer deretter formelen til det rotete celleområdet med fyllhåndtaket.
Pass på å trykke F4 bare én gang for å endre en cellereferanse til fullstendig absolutt som jeg beskriver tidligere. Trykker du funksjonstasten F4 en gang til, ender du opp med en såkalt blandet referanse, hvor kun raddelen er absolutt og kolonnedelen relativ (som i E$12). Trykker du så F4 igjen, kommer Excel med en annen type blandet referanse, der kolonnedelen er absolutt og raddelen relativ (som i $E12). Hvis du fortsetter og trykker F4 igjen, endrer Excel cellereferansen tilbake til fullstendig relativ (som i E12).
Etter at du er tilbake der du startet, kan du fortsette å bruke F4 for å gå gjennom det samme settet med cellereferanseendringer på nytt.
Hvis du bruker Excel 2016 på en enhet uten tilgang til et fysisk tastatur med funksjonstaster (for eksempel et nettbrett med berøringsskjerm), er den eneste måten å konvertere celleadresser i formlene dine fra relativ til absolutt eller en form for blandet adresse på å åpne berøringstastaturet og bruk det legg til dollartegn før kolonnebokstaven og/eller radnummeret i den aktuelle celleadressen på formellinjen.