相對引用與絕對引用是Excel公式中既非常基礎(chǔ)又非常重要的內(nèi)容。我們將從這里開始,帶領(lǐng)大家學(xué)習(xí)如何編寫公式,總體思路是先有思路,后有動(dòng)作,然后調(diào)整細(xì)節(jié)。
相對引用與絕對引用的4種狀態(tài)展示
我們打開Excel表格,在單元格中使用公式來解決問題。下面以圖1-1所示的A7:E10單元格區(qū)域的數(shù)據(jù)為例,講解如何引用單元格。
在H7單元格中輸入“=”,然后單擊A7單元格,形成公式“=A7”。再將H7單元格向右向下復(fù)制,形成“相對引用”,如圖1-2所示。橫向復(fù)制后,A變?yōu)榱薆、C、D;縱向復(fù)制后,7變?yōu)榱?、9、10。這就是相對引用,引用的單元格會隨著單元格的變化而變化。
如果公式無論是橫向復(fù)制還是縱向復(fù)制,始終都要引用A7單元格,該怎么辦呢?
在N7單元格中輸入“=”,然后單擊A7單元格,形成公式“=A7”。然后按【F4】鍵,就會看到公式變成“=$A$7”,字母A和數(shù)字7前都增加了美元符號“$”,我們把它稱為“圖釘”,如圖1-3所示。這時(shí)將N7單元格向右向下復(fù)制,它的公式始終是“=$A$7”?!?”就像圖釘一樣把列號和行號牢牢地固定在那里。
上述情況就是絕對引用。這種引用方式常常用于某個(gè)單元格值被多個(gè)單元格使用的情況。例如,在A7單元格中輸入數(shù)字6.9(假定6.9為此時(shí)美元兌換人民幣的匯率),然后在其他位置引用A7單元格進(jìn)行換算,就可以將人民幣換算成美元。
對于初學(xué)者來說,混合引用似乎是一個(gè)難點(diǎn),很多書都會“專業(yè)”地進(jìn)行闡述:行絕對、列相對,是在數(shù)字的前面加上美元符號;行相對、列絕對,是在字母的前面加上美元符號。
如果函數(shù)的每一部分都需要死記硬背,那學(xué)習(xí)起來就太難了。Excel的學(xué)習(xí)如果依靠死記硬背,那么學(xué)習(xí)最終只能成為一個(gè)負(fù)擔(dān),我們要根據(jù)需求選擇相應(yīng)的方法。下面具體介紹混合應(yīng)用如何操作。
先引用需要的單元格,在單元格中輸入公式“=A7”。例如,需要引用的數(shù)據(jù)始終在表格的第7行,說明始終要將行號固定住,無論怎樣復(fù)制,行號都不會變,那我們就在公式數(shù)字7前面按上圖釘“$”,變成“=A$7”,如圖1-4(a)所示。再向下復(fù)制,公式始終為“=A$7”。但是,如果此時(shí)向右復(fù)制,由于列號A前面沒有圖釘,就會變成“=B$7”“=C$7”“=D$7”。
同理,當(dāng)想要始終引用第一列數(shù)據(jù)時(shí),可以在列號A前面按上圖釘,使行號不受限制,公式為“=$A7”,向下向右復(fù)制后,結(jié)果如圖1-4(b)所示。
圖1-4混合引用
注意:
(1)切換引用方式時(shí),可以手動(dòng)輸入圖釘“$”,或者多次按【F4】鍵,就可以在幾種引用方式之間切換。
(2)一些筆記本電腦單獨(dú)按【F4】鍵沒有效果,需要結(jié)合功能鍵【Fn】,按【Fn+F4】組合鍵才有效。如果感覺結(jié)合功能鍵【Fn】操作比較麻煩,可以修改筆記本電腦的BIOS,就可以不用結(jié)合【Fn】鍵來使用了。由于篇幅所限,具體操作不在此闡述,詳見網(wǎng)上相關(guān)教程。
案例:九九乘法表
根據(jù)1.1節(jié)所學(xué)知識,我們來做一個(gè)經(jīng)典案例,制作“九九乘法表”,最終效果如圖1-5所示。
圖1-5九九乘法表
首先觀察表格的特點(diǎn)與規(guī)律:第19行和A列都是數(shù)字1~9,在B20:B28單元格區(qū)域全都是數(shù)字1連接“*”,然后連接A列的數(shù)字1~9。下面構(gòu)造等號前面的部分,在B20單元格中寫下公式:
=B19&”*”&A20
其中,“&”是一個(gè)連接符,這里把它稱為“膠水”。例如,在單元格中輸入公式“=”a”&”b”&1”,那么結(jié)果就是ab1,“&”能把不同的部分粘在一起。
將B20單元格中的公式向下復(fù)制到B28單元格,如圖1-6所示。結(jié)果得到的并不是我們設(shè)想中的1*1,1*2,…,1*9,而是一個(gè)越來越長的序列。
圖1-6九九乘法表填充B列
下面來看一下問題出在哪里。選擇B22單元格,這時(shí)看到的公式為:=B21&”*”&A22
B21并不是所需要的參數(shù),我們需要始終引用第19行的數(shù)據(jù),那該怎么做呢?修改B20單元格中的公式,在行號19前面按上圖釘“$”,即將B20單元格的公式修改為:
=B$19&”*”&A20
修改公式后,再次向下復(fù)制單元格,結(jié)果如圖1-7所示。
圖1-7九九乘法表修改B列
再將B列的公式向右復(fù)制,如圖1-8所示,執(zhí)行命令后每一列的效果變長了。來看一下問題出在哪里。任選一個(gè)單元格,如E23,此單元格的公式為:
=E$19&”*”&D23
圖1-8九九乘法表橫向填充
由此可以發(fā)現(xiàn),E$19是沒有問題的,但其中的D23不是需要的參數(shù),我們需要的始終是A列的數(shù)字。我們返回到B20單元格中的公式,在列號A前面按上圖釘“$”,即將B20單元格公式修改為:
=B$19&”*”&$A20
修改后,再次將公式橫向、縱向復(fù)制,得到的效果如圖1-9所示,每個(gè)單元格都被引用到了準(zhǔn)確的位置。
圖1-9九九乘法表完善
最后,將整體公式完善,補(bǔ)充等號及乘法計(jì)算的結(jié)果,得到最終公式:=B$19&”*”&$A20&”=”&B$19*$A20
輸入上述公式,即可得到前面圖1-5展示的最終效果。
經(jīng)過本章的學(xué)習(xí),你會發(fā)現(xiàn)復(fù)雜的混合引用也不過如此。根據(jù)自己的需求,找出哪個(gè)點(diǎn)是不能動(dòng)的,然后把“圖釘”按在那里,就可以自如應(yīng)用了。
注意:在寫公式時(shí),所有的文本字符要用英文狀態(tài)的雙引號引起來,而數(shù)字、單元格或區(qū)域的引用是不需要雙引號的。
承擔(dān)因您的行為而導(dǎo)致的法律責(zé)任,
本站有權(quán)保留或刪除有爭議評論。
參與本評論即表明您已經(jīng)閱讀并接受
上述條款。