Merwyn z Anglie poslal tento problém.
Existuje v buňce B2 jediný vzorec, který lze kopírovat napříč a dolů a vytvořit multiplikační tabulku?
Cílem společnosti Merwyn je zadat do vzorce B2 jediný vzorec, který lze snadno zkopírovat do všech 144 buněk a vytvořit referenční tabulku násobení.
Pojďme na to zaútočit jako nováček v Excelu a podívejme se, na jaké úskalí narazíme. Počáteční reakcí člověka může být vzorec v B2 =A2*B1. Tento vzorec vytváří správný výsledek, ale není vhodný pro přiřazení Merwyn.
Když zkopírujete tento vzorec z buňky B2 do buňky C2, buňky uvedené ve vzorci se také přesunou přes jednu buňku. Vzorec, který =A2*B1nyní byl, se stává =C1*B2. Toto je funkce navržená do aplikace Microsoft Excel a nazývá se relativní odkaz na vzorec. Při kopírování vzorce odkazy na buňky ve vzorci také přesouvají odpovídající počet buněk napříč a dolů.
Jsou chvíle, kdy nechcete, aby Excel projevoval toto chování, a aktuální případ je jedním z nich. Chcete-li zabránit aplikaci Excel ve změně odkazu při kopírování buněk, vložte před část odkazu, kterou chcete zmrazit, znak „$“. Příklady:
- $ A1 říká Excelu, že vždy chcete odkazovat na sloupec A.
- B $ 1 říká aplikaci Excel, že vždy chcete odkazovat na řádek 1.
- $ B $ 1 říká aplikaci Excel, že vždy chcete odkazovat na buňku B1.
Naučení tohoto kódu dramaticky sníží čas potřebný k vytvoření pracovních listů. Namísto nutnosti zadávat 144 vzorců může Merwyn pomocí této zkratky zadat jeden jediný vzorec a zkopírovat jej do všech buněk.
Podíváme-li se na Merwynův vzorec, =B1*A2uvědomíme si, že část výrazu „B1“ by měla vždy ukazovat na číslo v řádku 1. Toto by mělo být přepsáno jako „B $ 1“. Část vzorce „A2“ by měla vždy ukazovat na číslo ve sloupci A. Toto by mělo být přepsáno jako „$ A2“. Nový vzorec v buňce B2 tedy je =B$1*$A2.
Po zadání vzorce v B2 jej mohu zkopírovat a vložit do příslušného rozsahu. Excel postupuje podle mých pokynů a po provedení kopie najdu následující vzorce:
- Buňka M2 obsahuje
=M$1*$A2 - Buňka B13 obsahuje
=B$1*$A13 - Buňka M13 obsahuje
=M$1*$A13
Každý z těchto typů vzorců má svůj název. Vzorce bez znaků dolaru se nazývají relativní vzorce. Vzorce, kde jsou řádek i sloupec uzamčeny znakem dolaru, se nazývají absolutní vzorce. Vzorce, kde je řádek nebo sloupec uzamčen znakem dolaru, se nazývají smíšené vzorce.
Existuje metoda zkratky pro zadávání znaků dolaru. Během psaní vzorce a dokončení odkazu na buňku můžete stisknutím klávesy přepínat mezi 4 typy odkazů. Řekněme, že jste začali psát vzorec a psali jste =100*G87.
- Stiskněte klávesu F4 a váš vzorec se změní na
=100*$G$87 - Stiskněte znovu F4 a váš vzorec se změní na
=100*G$87 - Stiskněte znovu F4 a váš vzorec se změní na
=100*$G87 - Stiskněte znovu F4 a váš vzorec se vrátí k původnímu
=100*G87
Při zadávání vzorce u každého odkazu na buňku můžete pozastavit a stisknout F4, dokud nezískáte správný typ odkazu. Klávesové zkratky pro zadání výše uvedeného Merwynova vzorce jsou =B1*A1.
Jedno z mých oblíbených použití odkazů na smíšené vzorce se objeví, když mám ve sloupci A dlouhý seznam položek. Když chci rychlou a špinavou metodu k vyhledání duplikátů, někdy zadám tento vzorec do buňky B4 a poté jej zkopíruji dolů:
=VLOOKUP(A4,$A$2:$A3,1,FALSE)
Všimněte si, že druhý člen vzorce VLOOKUP používá k popisu rozsahu vyhledávání absolutní ($ A $ 2) a smíšený ($ A3) odkaz. V angličtině říkám Excelu, aby vyhledával vždy z buňky $ A $ 2 do buňky ve sloupci A těsně nad aktuální buňkou. Jakmile Excel narazí na duplicitní hodnotu, změní se výsledek tohoto vzorce z # N / A! na hodnotu.
Díky kreativnímu použití odkazu $ in cell se můžete ujistit, že jeden vzorec lze zkopírovat do mnoha buněk se správnými výsledky.
