Ako kopírovať vzorce pomocou automatického dopĺňania v Exceli 2016

Ak potrebujete iba skopírovať jeden vzorec v Exceli 2016, použite funkciu Automatické dopĺňanie alebo príkazy Kopírovať a Prilepiť. Tento typ kópie vzorca, aj keď je celkom bežný, nie je možné vykonať pomocou drag and drop.

Nezabudnite na možnosť Súčty v nástroji Rýchla analýza. Môžete ho použiť na rýchle vytvorenie riadku alebo stĺpca súčtov na spodnom alebo pravom okraji tabuľky údajov. Jednoducho vyberte tabuľku ako rozsah buniek a kliknite na tlačidlo Rýchla analýza a potom na položku Súčty v palete. Potom kliknite na tlačidlo Súčet na začiatku palety, aby ste vytvorili vzorce, ktoré spočítajú stĺpce v novom riadku v spodnej časti tabuľky a/alebo na tlačidlo Súčet na konci palety, aby ste vytvorili vzorce, ktoré spočítajú riadky v novom riadku. stĺpec na pravom konci.

Tu je návod, ako môžete použiť funkciu Automatické dopĺňanie na skopírovanie jedného vzorca do rozsahu buniek. Na tomto obrázku môžete vidieť pracovný list Mother Goose Enterprises – 2016 Sales so všetkými spoločnosťami, ale tentoraz len s jedným mesačným súčtom v riadku 12, ktorý je v procese kopírovania cez bunku E12.

Kopírovanie vzorca do rozsahu buniek pomocou funkcie Automatické dopĺňanie.

Nasledujúci obrázok zobrazuje pracovný hárok po pretiahnutí rukoväte výplne v bunke B12, aby ste vybrali rozsah buniek C12:E12 (kam by sa mal tento vzorec skopírovať).

Pracovný list po skopírovaní vzorca súčtu mesačného (a štvrťročného) predaja.

Relatívne povedané

Obrázok ukazuje pracovný hárok po skopírovaní vzorca v bunke do rozsahu buniek C12:E12 a aktívnej bunke B12. Všimnite si, ako Excel spracováva kopírovanie vzorcov. Pôvodný vzorec v bunke B12 je nasledujúci:

=SUM(B3:B11)

Keď sa pôvodný vzorec skopíruje do bunky C12, Excel mierne zmení vzorec tak, aby vyzeral takto:

=SUM(C3:C11)

Excel upraví odkaz na stĺpec a zmení ho z B na C, pretože ste v riadkoch kopírovali zľava doprava.

Keď skopírujete vzorec do rozsahu buniek, ktorý sa rozprestiera nadol v riadkoch, Excel upraví čísla riadkov v skopírovaných vzorcoch namiesto písmen stĺpcov tak, aby vyhovovali pozícii každej kópie. Napríklad bunka E3 v pracovnom hárku Mother Goose Enterprises – 2016 Sales obsahuje nasledujúci vzorec:

=SUM(B3:D3)

Keď skopírujete tento vzorec do bunky E4, Excel zmení kópiu vzorca na nasledujúcu:

=SUM(B4:D4)

Excel upraví odkaz na riadok tak, aby bol aktuálny s novou pozíciou 4. riadku. Pretože Excel upravuje odkazy na bunky v kópiách vzorca vzhľadom na smer kopírovania, odkazy na bunky sú známe ako relatívne odkazy na bunky .

Niektoré veci sú absolútne!

Všetky nové vzorce, ktoré vytvoríte, prirodzene obsahujú relatívne odkazy na bunky, pokiaľ nepoviete inak. Pretože väčšina kópií vzorcov, ktoré vytvoríte, vyžaduje úpravu odkazov na bunky, zriedkakedy musíte nad týmto usporiadaním premýšľať. Potom raz za čas narazíte na výnimku, ktorá vyžaduje obmedzenie toho, kedy a ako sa upravujú odkazy na bunky v kópiách.

Jednou z najbežnejších z týchto výnimiek je situácia, keď chcete porovnať rozsah rôznych hodnôt s jednou hodnotou. Stáva sa to najčastejšie, keď chcete vypočítať, koľko percent je každá časť k súčtu. Napríklad v pracovnom liste Mother Goose Enterprises – 2016 Sales sa s touto situáciou stretnete pri vytváraní a kopírovaní vzorca, ktorý vypočítava, koľko percent tvorí každý mesačný súčet (v rozsahu buniek B14:D14) zo štvrťročného súčtu v bunke E12.

Predpokladajme, že chcete zadať tieto vzorce do riadku 14 pracovného hárka Mother Goose Enterprises – 2016 Sales, počnúc bunkou B14. Vzorec v bunke B14 na výpočet percenta z celkových predajov za január k prvému štvrťroku je veľmi jednoduchý:

=B12/E12

Tento vzorec vydelí celkový januárový predaj v bunke B12 štvrťročným súčtom v E12 (čo môže byť jednoduchšie?). Pozrite sa však, čo by sa stalo, keby ste potiahli rukoväť výplne o jednu bunku doprava, aby ste skopírovali tento vzorec do bunky C14:

= C12/F12

Úprava referencie prvej bunky z B12 na C12 je presne to, čo lekár nariadil. Úprava referencie druhej bunky z E12 na F12 je však katastrofa. Nielenže si nevypočítate, koľko percent predstavujú februárové tržby v bunke C12 z predaja za prvý štvrťrok v E12, ale navyše skončíte s jedným z tých hrozných #DIV/0! chybové veci v bunke C14.

Ak chcete zabrániť Excelu v úprave odkazu na bunku vo vzorci vo všetkých vytvorených kópiách, skonvertujte odkaz na bunku z relatívneho na absolútny. Urobíte to stlačením funkčného klávesu F4 po prepnutí Excelu do režimu úprav (F2). Excel naznačuje, že odkaz na bunku urobíte absolútnym umiestnením znakov dolára pred písmeno stĺpca a číslo riadku. Napríklad na tomto obrázku bunka B14 obsahuje správny vzorec na skopírovanie do rozsahu buniek C14:D14:

Kopírovanie vzorca na výpočet pomeru mesačného a štvrťročného predaja s absolútnym odkazom na bunku.

= B12/$E$12

Pozrite sa na pracovný hárok po skopírovaní tohto vzorca do rozsahu C14:D14 s rukoväťou výplne a vybratou bunkou C14 (pozri nasledujúci obrázok). Všimnite si, že riadok vzorcov ukazuje, že táto bunka obsahuje nasledujúci vzorec:

Pracovný hárok po skopírovaní vzorca s absolútnym odkazom na bunku.

=C12/$E$12

Pretože E12 bolo v pôvodnom vzorci zmenené na $E$12, všetky kópie majú rovnakú absolútnu (nemenícu sa) referenciu.

Ak sa pomýlite a skopírujete vzorec, v ktorom by jeden alebo viacero odkazov na bunky mali byť absolútne, ale všetky ste nechali relatívne, upravte pôvodný vzorec takto:

Dvakrát kliknite na bunku so vzorcom alebo ju upravte stlačením klávesu F2.

Umiestnite kurzor niekde na referenciu, ktorú chcete previesť na absolútnu.

Stlačte F4.

Po dokončení úprav kliknite na tlačidlo Enter na paneli vzorcov a potom skopírujte vzorec do nesprávneho rozsahu buniek pomocou rukoväte výplne.

Nezabudnite stlačiť F4 iba raz, aby ste zmenili odkaz na bunku na úplne absolútnu, ako som opísal vyššie. Ak stlačíte funkčný kláves F4 druhýkrát, skončíte s takzvanou zmiešanou referenciou, kde iba riadková časť je absolútna a stĺpcová časť je relatívna (ako v E$12). Ak potom znova stlačíte F4, Excel príde s iným typom zmiešaného odkazu, kde stĺpcová časť je absolútna a riadková časť je relatívna (ako v $E12). Ak budete pokračovať a znova stlačíte F4, Excel zmení odkaz na bunku späť na úplne relatívny (ako v E12).

Keď sa vrátite tam, kde ste začali, môžete pokračovať pomocou klávesu F4 na cyklické prechádzanie cez rovnakú sadu zmien odkazov na bunky.

Ak používate Excel 2016 na zariadení bez prístupu k fyzickej klávesnici s funkčnými klávesmi (ako je tablet s dotykovou obrazovkou), jediným spôsobom, ako previesť adresy buniek vo vzorcoch z relatívnej na absolútnu alebo nejakú formu zmiešanej adresy, je otvoriť dotykovej klávesnice a použite ju, pridajte znaky dolára pred písmeno stĺpca a/alebo číslo riadku v príslušnej adrese bunky na paneli vzorcov.