Sådan kopieres formler med autofyldning i Excel 2016 - dummies

Hvis du bare skal kopiere en enkelt formel i Excel 2016, skal du bruge funktionen AutoFill eller kommandoen Kopier og Indsæt. Denne type formel kopi, selvom det er ret almindeligt, kan ikke gøres med træk og slip.

Glem ikke Totals-indstillingen i værktøjet Quick Analysis. Du kan bruge den til at oprette en række eller en kolonne med totals nederst eller højre kant af en datatabel i en flash. Vælg blot tabellen som et celleområde, og klik på knappen Hurtig analyse efterfulgt af Totals i paletten. Klik derefter på Sum-knappen i begyndelsen af ​​paletten for at oprette formler, der samlede kolonnerne i en ny række nederst i tabellen og / eller Sum-knappen i slutningen af ​​paletten for at oprette formler, der rækker rækkerne i en ny kolonne i højre ende.

Sådan kan du bruge AutoFill til at kopiere en formel til en række celler. I denne figur kan du se Moderne Goose Enterprises - 2016 Sales regneark med alle virksomhederne, men denne gang med kun en månedlig total i række 12, som er ved at blive kopieret via celle E12.

Kopiering af en formel til et celleområde med AutoFill.

Følgende figur viser regnearket, når du har trukket påfyldningshåndtaget i celle B12 for at vælge celleområde C12: E12 (hvor denne formel skal kopieres).

Arbejdsarket efter kopiering af formlen i alt det månedlige (og kvartalsvise) salg.

Relativt tal

Figuren viser regnearket efter at formlen i en celle er kopieret til celleintervallet C12: E12 og celle B12 er aktiv. Bemærk, hvordan Excel håndterer kopiering af formler. Den oprindelige formel i celle B12 er som følger:

= SUM (B3: B11)

Når den oprindelige formel kopieres til celle C12, ændrer Excel formlen lidt, så den ser ud som dette:

= SUM (C3: C11)

Excel justerer kolonnehenvisningen, ændrer den fra B til C, fordi du kopierede fra venstre mod højre over rækkerne.

Når du kopierer en formel til et celleområde, der strækker sig ned ad rækkerne, tilpasser Excel rækkens tal i de kopierede formler frem for søjlebogstaverne for at passe til hver kopi. Eksempelvis indeholder celle E3 i Sales Goes Enterprises-2016-regneark følgende formel:

= SUM (B3: D3)

Når du kopierer denne formel til celle E4, ændrer Excel kopien af ​​formlen til Følgende:

= SUM (B4: D4)

Excel justerer rækkereferencen for at holde den aktuelle position i den nye række 4. Fordi Excel justerer cellehenvisningerne i kopier af en formel i forhold til kopieringens retning, er cellehenvisninger kendt som relative cellehenvisninger .

Nogle ting er absolutter!

Alle nye formler, du opretter naturligt, indeholder relative cellehenvisninger, medmindre du siger noget andet. Fordi de fleste kopier, du laver med formler, kræver justeringer af deres cellehenvisninger, skal du sjældent give denne ordning en anden tanke. Derefter kommer du en gang imellem over en undtagelse, der kræver begrænsning, hvornår og hvordan cellehenvisninger justeres i kopier.

En af de mest almindelige af disse undtagelser er, når du vil sammenligne en række forskellige værdier med en enkelt værdi. Dette sker hyppigst, når du vil beregne, hvilken procentdel hver del er til den samlede. Eksempelvis møder du i Sales Googles Forhandler regneark i denne situation ved at oprette og kopiere en formel, der beregner, hvilken procentdel af hver månedlig total (i celleintervallet B14: D14) er den kvartalsvise total i celle E12.

Antag, at du vil indtaste disse formler i række 14 i Sales Goes Enterprise-2016 Sales-regnearket, der starter i celle B14. Formlen i celle B14 til beregning af procentdelen af ​​januar-salget til første kvartal er meget ligetil:

= B12 / E12

Denne formel opdeler januaromsummen i celle B12 efter kvartalsoversigten i E12 (hvad kunne det være lettere?). Se dog på, hvad der ville ske, hvis du trækkede fyldhåndtaget en celle til højre for at kopiere denne formel til celle C14:

= C12 / F12

Justeringen af ​​den første cellereference fra B12 til C12 er lige hvad lægen bestilte. Justeringen af ​​den anden cellereference fra E12 til F12 er imidlertid en katastrofe. Ikke alene beregner du, hvilken procentdel februar-salget i celle C12 er af første kvartals salg i E12, men du ender også med en af ​​de forfærdelige # DIV / 0! fejl ting i celle C14.

For at stoppe Excel fra at justere en cellehenvisning i en formel i alle kopier, du laver, konvertere cellehenvisningen fra forhold til absolutte. Dette gøres ved at trykke på funktionstasten F4, efter du har lagt Excel i Redigeringsfunktion (F2). Excel angiver, at du gør cellehenvisningen absolut ved at placere dollartegn foran kolonnebogstavet og rækkenummeret. For eksempel indeholder celle B14 den korrekte formel til kopiering til celleområdet C14: D14:

Kopiering af formlen til beregning af forholdet mellem månedlig og kvartalsvis salg med en absolut cellereference.

= B12 / $ E $ 12

Se på regnearket efter at denne formel er kopieret til rækkevidde C14: D14 med påfyldningshåndtaget og celle C14 er valgt (se nedenstående figur). Bemærk, at formelbaren viser, at denne celle indeholder følgende formel:

Arbejdsarket efter kopiering af formlen med den absolutte cellehenvisning.

= C12 / $ E $ 12

Da E12 blev ændret til $ E $ 12 i den oprindelige formel, har alle kopierne samme absolutte (ikke-skiftende) reference.

Hvis du vælger og kopierer en formel, hvor en eller flere af cellereferencerne skulle have været absolutte, men du forlod dem alle relative, rediger du den oprindelige formel som følger:

1. Dobbeltklik på cellen med formlen eller tryk på F2 for at redigere det.

2. Placer indsættelsespunktet et sted på den reference, du vil konvertere til absolutte.

3. Tryk på F4.

4. Når du er færdig med at redigere, skal du klikke på Enter-knappen på Formula-linjen og derefter kopiere formlen til det forstyrrede celleområde med fyldhåndtaget.

Sørg for kun at trykke F4 en gang for at ændre en cellereference til helt absolut som beskrevet tidligere. Hvis du trykker på funktionstasten F4 en anden gang, slutter du med en såkaldt blandet reference, hvor kun rækkedelen er absolut og kolonnedelen er relativ (som i E $ 12). Hvis du derefter trykker på F4 igen, kommer Excel op med en anden type blandet reference, hvor kolonnedelen er absolut og rækkedelen er relativ (som i $ E12). Hvis du fortsætter og trykker på F4 igen, ændrer Excel cellehenvisningen tilbage til helt relativ (som i E12).

Når du er tilbage, hvor du startede, kan du fortsætte med at bruge F4 til at cykle gennem det samme sæt af cellereferencesændringer igen.

Hvis du bruger Excel 2016 på en enhed uden adgang til et fysisk tastatur med funktionstaster (f.eks. En touchscreen-tablet), kan du kun konvertere celleadresser i dine formler fra i forhold til absolut eller en eller anden form for blandet adresse er at åbne Touch-tastaturet og bruge det tilføje dollar tegnene foran kolonnebrevet og / eller række nummeret i den relevante celleadresse på Formula baren.