Kako kopirati formule s samodejnim izpolnjevanjem v Excelu 2016

Če morate kopirati samo eno formulo v Excelu 2016, uporabite funkcijo samodejnega izpolnjevanja ali ukaza Kopiraj in prilepi. Te vrste kopiranja formule, čeprav je precej pogosta, ni mogoče narediti s povleci in spusti.

Ne pozabite na možnost Totals v orodju za hitro analizo. Z njim lahko hitro ustvarite vrstico ali stolpec s seštevki na spodnjem ali desnem robu podatkovne tabele. Preprosto izberite tabelo kot obseg celic in kliknite gumb Hitra analiza, ki mu na paleti sledi Vsote. Nato kliknite gumb Vsota na začetku palete, da ustvarite formule, ki seštevajo stolpce v novi vrstici na dnu tabele in/ali gumb Vsota na koncu palete, da ustvarite formule, ki seštevajo vrstice v novi stolpec na desnem koncu.

Tukaj je opisano, kako lahko uporabite samodejno izpolnjevanje za kopiranje ene formule v vrsto celic. Na tej sliki lahko vidite delovni list Mother Goose Enterprises – 2016 Prodaja z vsemi podjetji, vendar tokrat le z enim mesečnim zneskom v vrstici 12, ki je v postopku kopiranja skozi celico E12.

Kopiranje formule v obseg celic s samodejnim izpolnjevanjem.

Naslednja slika prikazuje delovni list, potem ko povlečete ročico za polnjenje v celici B12, da izberete obseg celic C12:E12 (kjer je treba kopirati to formulo).

Delovni list po kopiranju formule s seštevanjem mesečne (in četrtletne) prodaje.

Relativno gledano

Slika prikazuje delovni list, potem ko je formula v celici kopirana v obseg celic C12:E12 in je celica B12 aktivna. Upoštevajte, kako Excel obravnava kopiranje formul. Prvotna formula v celici B12 je naslednja:

=SUM(B3:B11)

Ko je izvirna formula kopirana v celico C12, Excel rahlo spremeni formulo, tako da je videti tako:

=SUM(C3:C11)

Excel prilagodi referenco stolpca in jo spremeni iz B v C, ker ste kopirali od leve proti desni čez vrstice.

Ko kopirate formulo v obseg celic, ki se razteza po vrsticah navzdol, Excel prilagodi številke vrstic v kopiranih formulah in ne črke stolpcev, da ustrezajo položaju vsake kopije. Na primer, celica E3 v delovnem listu Mother Goose Enterprises – 2016 Sales vsebuje naslednjo formulo:

=SUM(B3:D3)

Ko kopirate to formulo v celico E4, Excel spremeni kopijo formule na naslednje:

=SUM(B4:D4)

Excel prilagodi referenco vrstice, da ostane aktualna z novim položajem vrstice 4. Ker Excel prilagodi sklice na celice v kopijah formule glede na smer kopiranja, so reference celic znane kot relativne reference celic .

Nekatere stvari so absolutne!

Vse nove formule, ki jih ustvarite, seveda vsebujejo relativne reference celic, razen če ne rečete drugače. Ker večina kopij formul, ki jih naredite, zahteva prilagoditev sklicevanj na celice, vam je le redko potrebno razmišljati o tej ureditvi. Nato občasno naletite na izjemo, ki zahteva omejitev, kdaj in kako se sklici na celice prilagajajo v kopijah.

Ena najpogostejših teh izjem je, ko želite primerjati obseg različnih vrednosti z eno samo vrednostjo. To se najpogosteje zgodi, ko želite izračunati, kolikšen odstotek je vsak del v skupnem znesku. Na primer, v delovnem listu Mother Goose Enterprises – 2016 Sales naletite na to situacijo pri ustvarjanju in kopiranju formule, ki izračuna, kolikšen odstotek vsake mesečne vsote (v območju celic B14:D14) je četrtletne vsote v celici E12.

Recimo, da želite te formule vnesti v 14. vrstico delovnega lista Mother Goose Enterprises – 2016 Prodaja, začenši v celici B14. Formula v celici B14 za izračun odstotka januarske prodaje do skupnega zneska prvega četrtletja je zelo preprosta:

=B12/E12

Ta formula deli januarsko skupno prodajo v celici B12 s četrtletno vsoto v E12 (kaj bi lahko bilo lažje?). Poglejte pa, kaj bi se zgodilo, če bi ročico za polnjenje potegnili eno celico v desno, da kopirate to formulo v celico C14:

=C12/F12

Prilagoditev prve reference celice z B12 na C12 je ravno tisto, kar je zdravnik naročil. Vendar je prilagoditev druge reference celice iz E12 v F12 katastrofa. Ne samo, da ne izračunate, kolikšen odstotek je februarska prodaja v celici C12 od prodaje v prvem četrtletju v E12, ampak tudi na koncu dobite enega od tistih groznih #DIV/0! napake v celici C14.

Če želite preprečiti, da bi Excel prilagodil sklic na celico v formuli v vseh kopijah, ki jih naredite, pretvorite referenco celice iz relativne v absolutno. To storite tako, da pritisnete funkcijsko tipko F4, potem ko Excel prestavite v način za urejanje (F2). Excel označuje, da referenco celice naredite absolutno tako, da pred črko stolpca in številko vrstice postavite znake za dolar. Na primer, na tej sliki celica B14 vsebuje pravilno formulo za kopiranje v obseg celic C14:D14:

Kopiranje formule za izračun razmerja med mesečno in četrtletno prodajo z absolutno referenco celice.

=B12/$E$12

Poglejte delovni list, potem ko je ta formula kopirana v obseg C14:D14 z ročico za polnjenje in izbrana celica C14 (glejte naslednjo sliko). Upoštevajte, da vrstica formule kaže, da ta celica vsebuje naslednjo formulo:

Delovni list po kopiranju formule z absolutno referenco celice.

=C12/$E$12

Ker je bil E12 v izvirni formuli spremenjen v $E$12, imajo vse kopije isto absolutno (nespremenljivo) referenco.

Če zgrešite in kopirate formulo, kjer bi morala biti ena ali več referenc celic absolutna, vendar ste jih vse pustili relativne, uredite izvirno formulo na naslednji način:

Dvokliknite celico s formulo ali pritisnite F2, da jo uredite.

Postavite točko vstavljanja nekje na referenco, ki jo želite pretvoriti v absolutno.

Pritisnite F4.

Ko končate z urejanjem, kliknite gumb Enter v vrstici Formula in nato z ročico za polnjenje kopirajte formulo v pomešani obseg celic.

Ne pozabite pritisniti F4 samo enkrat, da spremenite sklic celice v popolnoma absolutno, kot sem opisal prej. Če še enkrat pritisnete funkcijsko tipko F4, na koncu dobite tako imenovano mešano referenco, kjer je samo del vrstice absolutni, del stolpca pa relativni (kot v E$12). Če nato znova pritisnete F4, Excel prikaže drugo vrsto mešane reference, kjer je del stolpca absolutni, del vrstice pa relativni (kot v $E12). Če nadaljujete in znova pritisnete F4, Excel spremeni referenco celice nazaj v popolnoma relativno (kot v E12).

Ko se vrnete, kjer ste začeli, lahko še naprej uporabljate tipko F4 za ponovni kroženje po istem nizu sprememb referenc celic.

Če uporabljate Excel 2016 v napravi brez dostopa do fizične tipkovnice s funkcijskimi tipkami (kot je tablični računalnik z zaslonom na dotik), je edini način za pretvorbo naslovov celic v formulah iz relativnega v absolutni ali nekakšno obliko mešanega naslova, da odprete Dotaknite se tipkovnice in jo uporabite, dodajte znake za dolar pred črko stolpca in/ali številko vrstice v ustrezen naslov celice v vrstici s formulo.