Hvordan du kopierer formler med autofyll i Excel 2016 - dummies

Hvis du bare trenger å kopiere en enkelt formel i Excel 2016, bruker du AutoFill-funksjonen eller Kopier og lim inn kommandoer. Denne typen formelkopi, selv om det er ganske vanlig, kan ikke gjøres med dra og slipp.

Ikke glem alternativet Totals i Quick Analysis-verktøyet. Du kan bruke den til å lage en rad eller en kolonne med totals nederst eller høyre kant av en datatabell i et blunk. Bare velg tabellen som et celleområde, og klikk på hurtiganalyseknappen etterfulgt av totaltall i paletten. Deretter klikker du Sum-knappen i begynnelsen av paletten for å lage formler som totalt kolonnene i en ny rad nederst i tabellen og / eller Sum-knappen i slutten av paletten for å lage formler som totalt rader i en ny kolonne i høyre ende.

Slik kan du bruke AutoFill til å kopiere en formel til en rekke celler. I denne figuren kan du se salgsregneet med Goose Enterprises - 2016 med alle selskapene, men denne gangen med bare en månedlig sum i rad 12, som er i ferd med å bli kopiert via celle E12.

Kopiere en formel til et celleområde med AutoFill.

Følgende figur viser regnearket etter å ha trukket på fyllhåndtaket i celle B12 for å velge celleområde C12: E12 (hvor denne formelen skal kopieres).

Regnearket etter å ha kopiert formelen i alt månedlig (og kvartalsvis) salg.

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 ned i rader, justerer Excel radnummerene i de kopierte formlene i stedet for kolonnebokstavene for å passe til plasseringen til hver kopi. Eksempelvis inneholder celle E3 i Sales Goes Enterprise-regnearket 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 den nåværende med den nye rad 4-posisjonen. Fordi Excel justerer cellehenvisninger i kopier av en formel i forhold til retningen for kopien, er cellehenvisninger kjent som relative cellehenvisninger .

Noen ting er absoluter!

Alle nye formler du lager naturlig inneholder relativ cellehenvisninger med mindre du sier noe annet. Fordi de fleste kopier du lager av formler krever justeringer av deres cellehenvisninger, trenger du sjelden å gi dette arrangementet en annen tanke. Deretter kommer du hvert eneste øyeblikk over et unntak som krever begrensning når og hvordan cellehenvisninger blir justert i kopier.

En av de vanligste av disse unntakene er når du vil sammenligne en rekke forskjellige verdier med en enkelt verdi. Dette skjer oftest når du vil beregne hvilken prosentandel hver del er til summen. Eksempelvis møter du denne situasjonen i salgsarket for Goose Enterprises - 2016, når du lager og kopierer en formel som beregner hvilken prosentandel hver månedlig total (i celleområdet B14: D14) er av kvartalsverdien i celle E12.

Anta at du vil legge inn disse formlene i rad 14 i salgskartet for Goose Enterprises - 2016, startet i celle B14. Formelen i celle B14 for å beregne prosentandelen i januar-salget til første kvartal er veldig grei:

= B12 / E12

Denne formelen deler salgsomsetningen i januar i celle B12 etter kvartalsresultatet i E12 (hva kan være lettere?). Se imidlertid hva som ville skje hvis du drev 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 bare hva legen bestilte. Imidlertid er justeringen av den andre cellereferansen fra E12 til F12 en katastrofe. Ikke bare beregner du hvilken prosentandel februar-salget i celle C12 er i første kvartalssalg i E12, men du ender også med en av de fryktelige # DIV / 0! feil ting i celle C14.

For å stoppe Excel fra å justere en cellereferanse i en formel i noen kopier du lager, konvertere cellehenvisningen fra relativt til absolutt. Du gjør dette ved å trykke på funksjonstasten F4, etter at du har lagt Excel i redigeringsmodus (F2). Excel indikerer at du gjør cellehenvisningen absolutt ved å plassere dollartegn foran kolonnebrevet og radnummeret. For eksempel inneholder celle B14 den riktige formelen for kopiering til celleområdet C14: D14:

Kopiering av formelen for beregning av forholdet mellom månedlig og kvartalsvis salg med absolutt cellereferanse.

= B12 / $ E $ 12

Se på regnearket etter at denne formelen er kopiert til rekkevidde C14: D14 med fyllehåndtaket og celle C14 er valgt (se følgende figur). Legg merke til at formellelinjen viser at denne cellen inneholder følgende formel:

Regnearket etter å ha kopiert formelen med absolutt cellereferanse.

= C12 / $ E $ 12

Fordi E12 ble endret til $ E $ 12 i den opprinnelige formelen, har alle kopiene samme samme (ikke-endrede) referanse.

Hvis du går og kopierer en formel der en eller flere av cellereferanser skal ha vært absolutte, men du forlot dem alle relative, rediger du den opprinnelige formelen som følger:

1. Dobbeltklikk cellen med formelen eller trykk på F2 for å redigere den.

2. Plasser innsettingspunktet et sted på referansen du vil konvertere til absolutt.

3. Trykk på F4.

4. Når du er ferdig med å redigere, klikker du Enter-knappen på Formula-feltet og kopierer deretter formelen til det splittede celleområdet med fyllehåndtaket.

Pass på å trykke F4 bare en gang for å endre en cellereferanse til helt absolutt som beskrevet tidligere. Hvis du trykker på F4-funksjonstasten en gang til, slutter du med en såkalt blandet referanse, hvor bare raddelen er absolutt og kolonnedelen er relativ (som i E $ 12). Hvis du trykker på F4 igjen, kommer Excel opp med en annen type blandet referanse, hvor kolonnedelen er absolutt og raddelen er relativ (som i $ E12). Hvis du fortsetter og trykker F4 igjen, endrer Excel cellehenvisningen tilbake til helt relativ (som i E12).

Når du er tilbake der du startet, kan du fortsette å bruke F4 til å sykle gjennom det samme settet med referanser for cellehenvisninger hele tiden.

Hvis du bruker Excel 2016 på en enhet uten tilgang til et fysisk tastatur med funksjonstaster (for eksempel en berøringsskjermtabletter), kan du bare konvertere celleadresser i formler fra forhold til absolutt eller noen form for blandet adresse er å åpne berørings-tastaturet og bruke det legge til dollarskilt før kolonnebrevet og / eller radnummeret i den aktuelle celleadressen på Formula-linjen.