Inglismaa Merwyn saatis selle probleemi.
Kas lahtris B2 on üks valem, mida saab korrutustabeli loomiseks üle ja alla kopeerida?
Merwyni eesmärk on sisestada B2-s üks valem, mida saab korrutamise võrdlustabeli loomiseks hõlpsasti kopeerida kõikidesse 144 lahtrisse.
Ründame seda Exceli algajana ja vaatame, millistesse lõkse satume. Esialgne reaktsioon võib olla valemi sisestamine B2-sse =A2*B1. See valem annab õige tulemuse, kuid see ei sobi Merwyni määramiseks.
Kui kopeerite selle valemi lahtrist B2 lahtrisse C2, liiguvad valemis viidatud lahtrid ka ühe lahtri kohale. Valem, mis =A2*B1nüüd oli, saab =C1*B2. See on funktsioon, mis on loodud Microsoft Excelis ja mida nimetatakse suhteliseks valemiviideks. Valemi kopeerimisel liigutavad valemis olevad lahtriviited ka vastava arvu lahtreid üle ja alla.
Mõnikord ei soovi te, et Excel seda käitumist näitaks, ja praegune juhtum on üks neist. Selleks, et Excel ei saaks lahtrite kopeerimisel viidet muuta, sisestage "$" viite selle osa ette, mille soovite külmutada. Näited:
- $ A1 ütleb Excelile, et soovite alati viidata veergule A
- B $ 1 ütleb Exceli jaoks, et soovite alati viidata 1. reale.
- $ B $ 1 ütleb Excelile, et soovite alati viidata lahtrile B1.
Selle koodi õppimine vähendab dramaatiliselt töölehtede koostamiseks kuluvat aega. Selle asemel, et peaks sisestama 144 valemit, saab Merwyn selle lühikese käsu abil sisestada ühe valemi ja kopeerida selle kõikidesse lahtritesse.
Merwyni valemit vaadates =B1*A2mõistame, et avaldise osa "B1" peaks alati osutama numbrile 1. real. See tuleks ümber kirjutada kui "B $ 1". Valemi jaotis "A2" peaks alati osutama veerus A olevale numbrile. See tuleks ümber kirjutada kui "$ A2". Seega on lahtris B2 uus valem =B$1*$A2.
Pärast valemi sisestamist B2-s saan selle kopeerida ja kleepida sobivasse vahemikku. Excel järgib minu juhiseid ja pärast koopia tegemist leian järgmised valemid:
- Lahter M2 sisaldab
=M$1*$A2 - Lahter B13 sisaldab
=B$1*$A13 - Lahter M13 sisaldab
=M$1*$A13
Kõigil seda tüüpi valemitel on oma nimi. Ilma ühegi dollarimärgita valemeid nimetatakse suhteliseks valemiks. Valemeid, kus nii rida kui veerg on lukustatud dollarimärgiga, nimetatakse absoluutvalemiteks. Valemeid, kus kas rida või veerg on lukustatud dollarimärgiga, nimetatakse segavalemiteks.
Dollarimärkide sisestamiseks on olemas lühike meetod. Valemi tippimise ja lahtriviite lõpuleviimise ajal võite 4 viitetüübi vahel vahetamiseks vajutada klahvi. Oletame, et hakkasite valemit tippima ja tippisite =100*G87.
- Vajutage klahvi F4 ja valemiks saab
=100*$G$87 - Vajutage uuesti klahvi F4 ja teie valem muutub väärtuseks
=100*G$87 - Vajutage uuesti klahvi F4 ja teie valem muutub väärtuseks
=100*$G87 - Vajutage uuesti klahvi F4 ja teie valem naaseb algsele
=100*G87
Valemi sisestamise ajal saate igas lahtriviites peatada, et tabada F4, kuni saate õige viite tüübi. Klahvivajutused Merwyni valemi sisestamiseks on =B1*A1.
Üks minu lemmikvalemitest segavalemiviidete kohta ilmub siis, kui mul on veerus A pikk kirjete loend. Kui soovin duplikaatide leidmiseks kiiret ja määrdunud meetodit, sisestan selle valemi mõnikord lahtrisse B4 ja seejärel kopeerin selle alla:
=VLOOKUP(A4,$A$2:$A3,1,FALSE)
Pange tähele, et valemi VLOOKUP teine termin kasutab otsimisvahemiku kirjeldamiseks nii absoluutset ($ A $ 2) kui ka segatud ($ A3) viidet. Inglise keeles ütlen, et käskin Excelil otsida alati lahtrist $ A $ 2 lahtrisse A veerus otse praeguse lahtri kohal. Niipea kui Excel leiab duplikaadiväärtuse, muutub selle valemi tulemus # N / A-st! väärtuseni.
Lahtriviidete $ loomingulise kasutamisega saate veenduda, et ühe valemi saab korrektsete tulemustega paljudesse lahtritesse kopeerida.
