Jak kopírovat vzorce pomocí automatického vyplňování v Excelu 2016

Pokud potřebujete pouze zkopírovat jeden vzorec v Excelu 2016, použijte funkci Automatické vyplňování nebo příkazy Kopírovat a Vložit. Tento typ kopírování vzorce, i když je docela běžný, nelze provést přetažením.

Nezapomeňte na možnost Součty v nástroji Rychlá analýza. Můžete jej použít k rychlému vytvoření řádku nebo sloupce součtů na spodním nebo pravém okraji datové tabulky. Jednoduše vyberte tabulku jako oblast buněk a klikněte na tlačítko Rychlá analýza a poté na Součty v paletě. Poté klikněte na tlačítko Součet na začátku palety a vytvořte vzorce, které sečtou sloupce v novém řádku v dolní části tabulky, nebo na tlačítko Součet na konci palety, chcete-li vytvořit vzorce, které sečtou řádky v novém řádku. sloupec na pravém konci.

Zde je návod, jak můžete pomocí funkce Automatické vyplňování zkopírovat jeden vzorec do rozsahu buněk. Na tomto obrázku můžete vidět pracovní list Mother Goose Enterprises – 2016 Sales se všemi společnostmi, ale tentokrát pouze s jedním měsíčním celkem na řádku 12, který se právě kopíruje přes buňku E12.

Kopírování vzorce do oblasti buněk pomocí automatického vyplňování.

Následující obrázek ukazuje list po přetažení úchytu výplně v buňce B12 pro výběr oblasti buněk C12:E12 (kam by měl být tento vzorec zkopírován).

List po zkopírování vzorce sčítajícího měsíční (a čtvrtletní) tržby.

Relativně řečeno

Obrázek ukazuje list poté, co je vzorec v buňce zkopírován do oblasti buněk C12:E12 a buňka B12 je aktivní. Všimněte si, jak Excel zpracovává kopírování vzorců. Původní vzorec v buňce B12 je následující:

=SUM(B3:B11)

Když je původní vzorec zkopírován do buňky C12, Excel mírně změní vzorec tak, aby vypadal takto:

=SUM(C3:C11)

Excel upraví odkaz na sloupec a změní ho z B na C, protože jste přes řádky kopírovali zleva doprava.

Když zkopírujete vzorec do oblasti buněk, která přesahuje řádky dolů, Excel upraví čísla řádků ve zkopírovaných vzorcích, nikoli písmena sloupců, aby vyhovovala pozici každé kopie. Například buňka E3 v listu Mother Goose Enterprises – 2016 Sales obsahuje následující vzorec:

=SUM(B3:D3)

Když zkopírujete tento vzorec do buňky E4, Excel změní kopii vzorce na následující:

=SUM(B4:D4)

Excel upraví odkaz na řádek tak, aby byl aktuální s novou pozicí řádku 4. Protože Excel upravuje odkazy na buňky v kopiích vzorce vzhledem ke směru kopírování, jsou odkazy na buňky známé jako relativní odkazy na buňky .

Některé věci jsou absolutní!

Všechny nové vzorce, které vytvoříte, přirozeně obsahují relativní odkazy na buňky, pokud neřeknete jinak. Protože většina kopií vzorců, které vytváříte, vyžaduje úpravy jejich odkazů na buňky, zřídkakdy musíte toto uspořádání promyslet. Pak jednou za čas narazíte na výjimku, která vyžaduje omezení toho, kdy a jak jsou odkazy na buňky v kopiích upravovány.

Jednou z nejběžnějších z těchto výjimek je situace, kdy chcete porovnat rozsah různých hodnot s jednou hodnotou. To se stává nejčastěji, když chcete spočítat, jaké procento je každá část k součtu. Například v listu Mother Goose Enterprises – 2016 Sales se s touto situací setkáte při vytváření a kopírování vzorce, který počítá, jaké procento tvoří každý měsíční součet (v oblasti buněk B14:D14) ze čtvrtletního součtu v buňce E12.

Předpokládejme, že chcete zadat tyto vzorce do řádku 14 listu Mother Goose Enterprises – 2016 Sales, počínaje buňkou B14. Vzorec v buňce B14 pro výpočet procenta tržeb od ledna k celkovému objemu za první čtvrtletí je velmi jednoduchý:

=B12/E12

Tento vzorec vydělí celkový lednový prodej v buňce B12 čtvrtletním součtem v E12 (co by mohlo být jednodušší?). Podívejte se však, co by se stalo, kdybyste přetáhli úchyt výplně o jednu buňku doprava, abyste zkopírovali tento vzorec do buňky C14:

=C12/F12

Lékař nařídil úpravu první referenční buňky z B12 na C12. Úprava odkazu na druhou buňku z E12 na F12 je však katastrofa. Nejen, že si nespočítáte, jaké procento jsou únorové tržby v buňce C12 z tržeb za první čtvrtletí v E12, ale navíc skončíte s jedním z těch příšerných #DIV/0! chybové věci v buňce C14.

Chcete-li aplikaci Excel zabránit v úpravě odkazu na buňku ve vzorci ve všech vytvořených kopiích, převeďte odkaz na buňku z relativního na absolutní. To provedete stisknutím funkční klávesy F4 poté, co přepnete Excel do režimu úprav (F2). Excel označuje, že odkaz na buňku nastavíte jako absolutní umístěním znaků dolaru před písmeno sloupce a číslo řádku. Například na tomto obrázku buňka B14 obsahuje správný vzorec pro zkopírování do oblasti buněk C14:D14:

Kopírování vzorce pro výpočet poměru měsíčního a čtvrtletního prodeje s absolutním odkazem na buňku.

= B12/$E$12

Po zkopírování tohoto vzorce do rozsahu C14:D14 s úchytem výplně a vybráním buňky C14 se podívejte na list (viz následující obrázek). Všimněte si, že řádek vzorců ukazuje, že tato buňka obsahuje následující vzorec:

List po zkopírování vzorce s absolutním odkazem na buňku.

=C12/$E$12

Protože E12 bylo v původním vzorci změněno na $E$12, mají všechny kopie stejný absolutní (neměnný) odkaz.

Pokud se popletete a zkopírujete vzorec, kde jeden nebo více odkazů na buňky měly být absolutní, ale všechny jste je nechali relativní, upravte původní vzorec takto:

Poklepejte na buňku se vzorcem nebo jej upravte stisknutím klávesy F2.

Umístěte textový kurzor někam na referenci, kterou chcete převést na absolutní.

Stiskněte F4.

Po dokončení úprav klepněte na tlačítko Enter na řádku vzorců a poté zkopírujte vzorec do zaneřáděné oblasti buněk pomocí úchytu výplně.

Nezapomeňte stisknout F4 pouze jednou, abyste změnili odkaz na buňku na zcela absolutní, jak jsem popsal dříve. Pokud stisknete funkční klávesu F4 podruhé, skončíte s tzv. smíšenou referencí, kde pouze řádková část je absolutní a sloupcová část je relativní (jako v E$12). Pokud poté znovu stisknete F4, Excel přijde s dalším typem smíšené reference, kde sloupcová část je absolutní a řádková část je relativní (jako v $E12). Pokud budete pokračovat a znovu stisknete F4, Excel změní odkaz na buňku zpět na zcela relativní (jako v E12).

Poté, co se vrátíte tam, kde jste začali, můžete pokračovat pomocí F4 k opakovanému procházení stejné sady změn odkazů na buňky.

Pokud používáte Excel 2016 na zařízení bez přístupu k fyzické klávesnici s funkčními klávesami (jako je tablet s dotykovou obrazovkou), jediným způsobem, jak převést adresy buněk ve vzorcích z relativní na absolutní nebo nějakou formu smíšené adresy, je otevřít dotykovou klávesnici a použijte ji, přidejte znaky dolaru před písmeno sloupce a/nebo číslo řádku v příslušné adrese buňky na řádku vzorců.