Merwyn iz Anglije je poslal to težavo.
Ali obstaja ena celica v celici B2, ki jo je mogoče kopirati čez in navzdol, da se ustvari tabela množenja?
Cilj Merwyna je v B2 vnesti eno samo formulo, ki jo je mogoče enostavno kopirati v vseh 144 celic in tako ustvariti referenčno tabelo množenja.
Napadimo to kot novinec v Excelu in si oglejmo, s katerimi pasti smo naleteli. Začetna reakcija bi lahko bila, da imamo formulo v B2 =A2*B1. Ta formula daje pravi rezultat, vendar ni primerna za Merwynovo nalogo.
Ko kopirate to formulo iz celice B2 v celico C2, se celice, navedene v formuli, premaknejo tudi nad eno celico. Formula, ki je bila =A2*B1zdaj, postane =C1*B2. To je funkcija, zasnovana v programu Microsoft Excel, in se imenuje relativna referenca formule. Ko kopirate formulo, sklici na celice v formuli premaknejo tudi ustrezno število celic navzgor in navzdol.
Včasih ne želite, da Excel pokaže takšno vedenje, in trenutni primer je eden takih. Če želite preprečiti, da bi Excel med kopiranjem celic spremenil referenco, pred del sklica, ki ga želite zamrzniti, vstavite znak "$". Primeri:
- $ A1 pove Excelu, da se vedno želite sklicevati na stolpec A.
- B $ 1 pove Excelu, da se vedno želite sklicevati na 1. vrstico.
- $ B $ 1 pove Excelu, da se vedno želite sklicevati na celico B1.
Če se naučite te kode, se bo čas za izdelavo delovnih listov močno zmanjšal. Namesto da bi moral vnesti 144 formul, lahko Merwyn s to kratico vnese eno samo formulo in jo kopira v vse celice.
Če pogledamo Merwynovo formulo, =B1*A2ugotovimo, da mora del izraza "B1" vedno kazati na številko v vrstici 1. To je treba prepisati kot "B $ 1". Odsek "A2" formule mora vedno kazati na številko v stolpcu A. To je treba prepisati kot "$ A2". Torej, nova formula v celici B2 je =B$1*$A2.
Po vnosu formule v B2 jo lahko kopiram in prilepim v ustrezen obseg. Excel sledi mojim navodilom in po kopiranju najdem naslednje formule:
- Celica M2 vsebuje
=M$1*$A2 - Celica B13 vsebuje
=B$1*$A13 - Celica M13 vsebuje
=M$1*$A13
Vsaka od teh vrst formul ima svoje ime. Formule brez znakov za dolar se imenujejo relativne formule. Formule, pri katerih sta vrstica in stolpec zaklenjeni z znakom za dolar, se imenujejo absolutne formule. Formule, pri katerih je vrstica ali stolpec zaklenjena z znakom za dolar, se imenujejo mešane formule.
Obstaja okrajšava za vnos znakov za dolar. Ko vnašate formulo in končate sklic na celico, lahko pritisnete tipko za preklop med 4 vrstami sklicev. Recimo, da ste začeli vnašati formulo in ste vtipkali =100*G87.
- Pritisnite F4 in vaša formula se spremeni v
=100*$G$87 - Znova pritisnite F4 in vaša formula se spremeni v
=100*G$87 - Znova pritisnite F4 in vaša formula se spremeni v
=100*$G87 - Znova pritisnite F4 in vaša formula se vrne v izvirnik
=100*G87
Med vnosom formule pri vsaki sklici na celico lahko ustavite, da zadenete F4, dokler ne dobite prave vrste reference. Pritisnite tipko za vnos Merwynove formule zgoraj =B1*A1.
Eden mojih najljubših načinov uporabe mešanih referenc formule se prikaže, ko imam v stolpcu dolg seznam vnosov. Ko želim hitro in umazano metodo najti dvojnike, včasih vnesem to formulo v celico B4 in jo nato kopiram:
=VLOOKUP(A4,$A$2:$A3,1,FALSE)
Upoštevajte, da drugi izraz formule VLOOKUP za opis obsega iskanja uporablja absolutni ($ A $ 2) in mešani ($ A3) sklic. V angleščini povem Excelu, naj vedno išče od celice $ A $ 2 do celice v stolpcu A tik nad trenutno celico. Takoj, ko Excel naleti na podvojeno vrednost, se rezultat te formule spremeni iz # N / A! na vrednost.
Z ustvarjalno uporabo referenc celic $ in lahko zagotovite, da je mogoče eno samo formulo kopirati v številne celice s pravilnimi rezultati.
