Merwyn z Anglicka poslala tento problém.
Existuje v bunke B2 jediný vzorec, ktorý je možné kopírovať naprieč a nadol a vytvoriť multiplikačnú tabuľku?
Cieľom spoločnosti Merwyn je zadať do vzorca B2 jeden vzorec, ktorý je možné jednoducho skopírovať do všetkých 144 buniek a vytvoriť tak referenčnú tabuľku násobenia.
Poďme na to zaútočiť ako nováčik v exceli a pozrime sa, na aké úskalia narážame. Počiatočnou reakciou môže byť mať vzorec v B2 =A2*B1. Tento vzorec vedie k správnemu výsledku, ale nie je vhodný pre Merwynovo priradenie.
Keď skopírujete tento vzorec z bunky B2 do bunky C2, bunky uvedené vo vzorci sa tiež presunú cez jednu bunku. Vzorec, ktorý bol =A2*B1teraz, sa stáva =C1*B2. Toto je funkcia navrhnutá v programe Microsoft Excel a nazýva sa odkazom na relatívny vzorec. Keď kopírujete vzorec, odkazy na bunky vo vzorci tiež posúvajú zodpovedajúci počet buniek naprieč a dole.
Sú chvíle, kedy nechcete, aby program Excel prejavoval toto správanie, a aktuálny prípad je jedným z nich. Ak chcete zabrániť programu Excel v zmene referencie pri kopírovaní buniek, vložte pred časť referencie, ktorú chcete zmraziť, znak „$“. Príklady:
- $ A1 hovorí programu Excel, že sa vždy chcete odvolať na stĺpec A.
- B $ 1 hovorí programu Excel, že sa vždy chcete odvolať na riadok 1.
- $ B $ 1 informuje program Excel, že vždy chcete odkazovať na bunku B1.
Naučenie sa tohto kódu dramaticky zníži čas potrebný na zostavenie pracovných listov. Namiesto zadávania 144 vzorcov môže Merwyn pomocou tejto skratky zadať jeden jediný vzorec a skopírovať ho do všetkých buniek.
Pri pohľade na Merwynov vzorec =B1*A2si uvedomíme, že časť výrazu „B1“ by mala vždy smerovať k číslu v riadku 1. Toto by malo byť prepísané ako „B $ 1“. Časť „A2“ vzorca by mala vždy smerovať k číslu v stĺpci A. Toto by malo byť prepísané ako „$ A2“. Nový vzorec v bunke B2 teda je =B$1*$A2.
Po zadaní vzorca v B2 ho môžem skopírovať a prilepiť do príslušného rozsahu. Excel postupuje podľa mojich pokynov a po vykonaní kópie nájdem nasledujúce vzorce:
- Bunka M2 obsahuje
=M$1*$A2 - Bunka B13 obsahuje
=B$1*$A13 - Bunka M13 obsahuje
=M$1*$A13
Každý z týchto typov vzorcov má svoj názov. Vzorce bez znakov dolára sa nazývajú relatívne vzorce. Vzorce, v ktorých sú riadok aj stĺpec uzamknuté znakom dolára, sa nazývajú absolútne vzorce. Vzorce, v ktorých je riadok alebo stĺpec uzamknutý znakom dolára, sa nazývajú zmiešané vzorce.
Na zadávanie znakov dolára existuje skratková metóda. Počas písania vzorca a dokončenia referencie na bunku môžete stlačením klávesu prepínať medzi 4 typmi referencie. Povedzme, že ste začali písať vzorec a napísali ste =100*G87.
- Stlačte kláves F4 a váš vzorec sa zmení na
=100*$G$87 - Znova stlačte F4 a váš vzorec sa zmení na
=100*G$87 - Znova stlačte F4 a váš vzorec sa zmení na
=100*$G87 - Znova stlačte F4 a váš vzorec sa vráti k pôvodnému
=100*G87
Počas zadávania vzorca pri každom odkaze na bunku sa môžete pozastaviť a stlačiť kláves F4, kým nezískate správny typ referencie. Klávesové skratky na zadanie vyššie uvedeného Merwynovho vzorca sú =B1*A1.
Jedno z mojich obľúbených spôsobov použitia odkazov na zmiešané vzorce sa objaví, keď mám v stĺpci A dlhý zoznam položiek. Keď chcem rýchlu a špinavú metódu na vyhľadanie duplikátov, niekedy tento vzorec zadám do bunky B4 a potom ju skopírujem dole:
=VLOOKUP(A4,$A$2:$A3,1,FALSE)
Upozorňujeme, že druhý člen vzorca VLOOKUP používa na opísanie rozsahu vyhľadávania absolútny ($ A $ 2) aj zmiešaný ($ A3) odkaz. V angličtine hovorím programu Excel, aby prehľadával vždy od bunky $ A $ 2 do bunky v stĺpci A tesne nad aktuálnou bunkou. Akonáhle Excel zistí duplicitnú hodnotu, výsledok tohto vzorca sa zmení z # N / A! na hodnotu.
Vďaka kreatívnemu použitiu odkazu $ v bunke sa môžete ubezpečiť, že jeden vzorec je možné skopírovať do mnohých buniek so správnymi výsledkami.
