Excel中單元格的引用

如果你問我，“Excel中最重要的問題是什么？”我會(huì)毫不猶豫地告訴你：“公式。”然后你又追問我，“那么，公式中最重要的又是什么？”于是我再次毫不猶豫地告訴你：“單元格的引用”?？陀^地講，公式的運(yùn)用是Excel區(qū)別于Word和Access的重要特征，而公式又是由引用的單元格和運(yùn)算符號或函數(shù)構(gòu)成，因此，單元格的引用就成為Excel中最基本和最重要的問題。不懂得怎樣引用單元格，就無法利用公式對數(shù)據(jù)進(jìn)行操作，不懂得不同引用樣式間的區(qū)別，也就無法根據(jù)不同的情況使用不同的引用樣式來正確、便捷地處理公式和數(shù)據(jù)。怎么樣，這么重要的內(nèi)容你不想看看嗎？

    一、A1和R1C1引用樣式

    引用的作用在于標(biāo)識工作表上的單元格或單元格區(qū)域，并指明公式中所使用的數(shù)據(jù)的位置。通過引用，可以在公式中使用工作表不同部分的數(shù)據(jù)，或者在多個(gè)公式中使用同一單元格的數(shù)值。還可以引用同一工作簿不同工作表的單元格、不同工作簿的單元格、甚至其它應(yīng)用程序中的數(shù)據(jù)。Excel支持兩種單元格的引用樣式，即A1方式和R1C1樣式。

        1．A1引用樣式

    這是Excel默認(rèn)的引用樣式。列以大寫英文字母表示，從A開始到IV結(jié)束，共計(jì)256列。行以阿拉伯?dāng)?shù)字表示，從1開始到65536結(jié)束，共計(jì)65536行。由于每個(gè)單元格都是行和列的交叉點(diǎn)，所以其位置完全可以由所在的行和列來決定，因此，通過該單元格所在的行號和列標(biāo)就可以準(zhǔn)確地定位一個(gè)單元格。描述某單元格時(shí)，應(yīng)當(dāng)順序輸入列字母和行數(shù)據(jù)，列標(biāo)在前行號在后。例如，A1即指該單元格位于A列1行，是A列和1行交叉處的單元格。如果要引用單元格區(qū)域，應(yīng)當(dāng)順序輸入?yún)^(qū)域左上角單元格的引用、冒號（：）和區(qū)域右下角單元格的引用。以下是引用的示例。

        2．R1C1引用樣式

    在R1C1引用樣式中，Excel使用“R”加行數(shù)字和“C”加列數(shù)字來指示單元格的位置。例如，R1C1即指該單元格位于第1行第1列。在宏中計(jì)算行和列的位置時(shí)，或者需要顯示單元格相對引用時(shí)，R1C1 樣式是很有用的。如果要引用單元格區(qū)域，應(yīng)當(dāng)順序輸入?yún)^(qū)域左上角單元格的引用、冒號（：）和區(qū)域右下角單元格的引用。以下是引用的示例。

        1．單元格的絕對引用

    不論包含公式的單元格處在什么位置，公式中所引用的單元格位置都是其工作表的確切位置。在街道地址比喻中就像一個(gè)特定的住址，如“人民路32號”。單元格的絕對引用通過在行號和列標(biāo)前加一個(gè)美元符號“$”來表示，如$A$1、$B$2，以此類推。

        2．單元格相對引用

    相對引用是像A1這樣的單元格引用，該引用指引Excel從公式單元格出發(fā)如何找到引用的單元格。在街道比喻中類似于指路，即告之從出發(fā)地如何走到目的地，如“往前走三個(gè)路口。”

        3．單元格的混合引用

    混合引用是指包含一個(gè)絕對引用坐標(biāo)和一個(gè)相對引用坐標(biāo)的單元格引用，或者絕對引用行相對引用列如B$5，或者絕對引用列相對引用行如$B5。

        4．R1C1 引用樣式

    同A1引用樣式一樣，R1C1引用樣式也可以分為單元格的相對引用和單元格的絕對引用。R1C1格式是絕對引用，如R3C5是指該單元格位于第3行第5列。R[1]C[1]格式是相對引用，其中“[]”中的數(shù)值標(biāo)明引用的單元格的相對位置，如果引用的是左面列或上面行中的單元格還應(yīng)當(dāng)在數(shù)值前添加“-”。如引用下面一行右面兩列的單元格時(shí)表示為“R[1]C[2]”，引用上面一行左面兩列的單元格時(shí)表示為“R[-1]C[-2]”， 而引用上面一行右面兩列的單元格時(shí)則表示為“R[-1]C[2]”。

        5．絕對引用與相對引用的區(qū)別

    （1）復(fù)制粘貼公式時(shí)

    使用單元格的相對引用復(fù)制粘貼公式時(shí)，粘貼后公式的引用將被更新。例如，單元格C6中包含公式“=B6+C5”，是指C6單元格中的數(shù)值為其左側(cè)單元格和上方單元格數(shù)值的和，這是單元格的相對引用。當(dāng)復(fù)制C6單元格中的公式并將其粘貼到D8時(shí)，粘貼后公式中已不再是“=B6+C5”，而成為“=C8+D7”，即單元格的引用被更新，并指向與當(dāng)前公式位置相對應(yīng)的單元格，數(shù)值仍為其左側(cè)單元格和上方單元格數(shù)值的和。

    使用單元格的絕對引用復(fù)制粘貼公式時(shí)，粘貼后公式的引用不發(fā)生改變。例如，單元格C6中包含公式“=$B$6+$C$5”，這是單元格的絕對引用。當(dāng)復(fù)制C6單元格中的公式并將其粘貼到D8時(shí)，粘貼后的公式仍舊為“=$B$6+$C$5”。

    當(dāng)然，一個(gè)公式中也允許同時(shí)使用絕對引用和相對引用。例如，單元格C6中包含公式“=$B$6+C5”，當(dāng)復(fù)制C6單元格中的公式并將其粘貼到D8時(shí)，粘貼后的公式則為“=$B$6+D7”。

    示例一（如圖1）：單元格的絕對引用和相對引用

該表為一個(gè)工資發(fā)放表。每個(gè)職工工資的實(shí)發(fā)金額均應(yīng)為“基本工資+職務(wù)工資+獎(jiǎng)金-水電費(fèi)”，如果我們在F2單元格中鍵入相對引用單元格的公式“=B2+C2+D2-E2”，回車之后即可得出高平原的工資金額。然后，再將該公式復(fù)制并粘貼到F3、F4、F5和F6單元格，則F3中的公式改變?yōu)?#8220;=B3+C3+D3-E3”，F4中的公式改變?yōu)?#8220;=B4+C4+D4-E4”，依次類推，而這個(gè)結(jié)果正是我們所需要的。如果我們在F2中鍵入絕對引用單元格的公式“=$B$2+$C$2+$D$2-$E$2”會(huì)怎么樣呢？ 回車之后當(dāng)然也可得出高平原的工資金額，然而當(dāng)我們再將該公式復(fù)制并粘貼到F3、F4、F5和F6單元格時(shí)，F3、F4、F5和F6中的公式均為“=$B$2+$C$2+$D$2-$E$2”，而這個(gè)結(jié)果卻不是我們所需要的。

    如果單位決定給每個(gè)職工上浮工資30%，那么F2單元格中的公式則應(yīng)為“=（B2+C2+D2）*$C$8-E2”。 然后，再將該公式復(fù)制并粘貼到F3、F4、F5和F6單元格，則F3中的公式改變?yōu)?#8220;=（B3+C3+D3）*$C$8-E3”，F4中的公式改變?yōu)?#8220;=（B4+C4+D4）*$C$8-E4”，依次類推，而這個(gè)結(jié)果正是我們所需要的。在這里由于對單元格$C$8采用的是絕對引用樣式，所以復(fù)制粘貼時(shí)它并不改變。如果我們將F2中的公式寫為“=（B2+C2+D2）*C8-E2”會(huì)怎么樣呢？當(dāng)將其復(fù)制粘貼到F3、F4時(shí)，F3中的公式將改變?yōu)?#8220;=（B3+C3+D3）*C9-E3”，F4中的公式將改變?yōu)?#8220;=（B4+C4+D4）*C10-E4”，而這個(gè)結(jié)果卻不是我們所需要的。由此可見，當(dāng)我們不希望改變對某個(gè)單元格引用時(shí)，應(yīng)當(dāng)對其采用絕對引用樣式。

    示例二（如圖2）：單元格的混合引用

在該圖中我們要分別計(jì)算一分公司和二分公司的銷售額。計(jì)算方法有兩種。

    在單元格D3中鍵入公式“=B3*C3”，然后將該公式復(fù)制并粘貼到單元格D4到D7。再在單元格F3中鍵入公式“=B3*E3”，然后將該公式復(fù)制并粘貼到單元格F4到F7。該方式采用的是單元格的相對引用。

    在單元格D3中鍵入公式“=$B3*C3”，然后將該公式復(fù)制并粘貼到單元格D4到D7，再粘貼到單元格F3到F7。該方式采用的是單元格的混合引用，即對B列采取絕對引用的樣式，而對其它的列和行采取相對引用的樣式。結(jié)果如何？D4中的公式為“=$B4*C4”、D5中的公式為“=$B5*C5”，依次類推。F3中的公式為“=$B3*E3”、F4中的公式為“=$B4*E4”，依次類推。

    （2）剪切粘貼公式時(shí)

    （3）自動(dòng)填充公式時(shí)

    使用單元格的相對引用時(shí)，例如單元格C6中包含公式“=B6+C5”，那么當(dāng)向下拖曳該單元格的自動(dòng)填充鈕時(shí)，在C7單元格中將顯示公式“=B7+C6”，在C8單元格中將顯示公式“=B8+C7”，依次類推。

    使用單元格的絕對引用時(shí)，例如單元格C6中包含公式“= $B$6+$C$5”，那么當(dāng)向下拖曳該單元格的自動(dòng)填充鈕時(shí)，在C7、C8、C9……等單元格中都將顯示公式“=$B$6+$C$5”。

    當(dāng)公式中同時(shí)使用絕對引用和相對引用時(shí)，例如單元格C6中包含公式“=$B$6+C5”，那么當(dāng)向下拖曳該單元格的自動(dòng)填充鈕時(shí)，在C7單元格中將顯示公式“=$B$6+C6”， 在C8單元格中將顯示公式“=$B$6+C7”，依次類推。

    （4）錄制宏時(shí)

    如果在錄制宏時(shí)選中了某些單元格，則該宏在每次運(yùn)行時(shí)都將選中原來的這些單元格而無論事先是否選中了其他單元格，因?yàn)楹暝谶@時(shí)記錄的是單元格絕對引用。如果要讓宏在選擇單元格時(shí)不考慮活動(dòng)單元格的位置，則需要將宏設(shè)置成為記錄單元格相對引用。單擊“停止錄制”工具欄上的“相對引用”按鈕，Microsoft Excel將轉(zhuǎn)而按照相對引用格式繼續(xù)記錄宏，直到退出 Microsoft Excel 或再次單擊“相對引用”按鈕為止。

        1．引用同一工作簿中的其它工作表

    引用同一工作簿中的其它工作表時(shí)格式如下：被引用的工作表！被引用的單元格。例如，我們欲引用Sheet8工作表中的F18單元格，表達(dá)式為“Sheet8!F18”。

    （1）打開目的工作表并選取目的單元格。

    （2）鍵入“=”。

    （3）單擊Sheet8工作表標(biāo)簽。

    （4）單擊F18單元格。

    （5）按回車鍵完成鍵入，此時(shí)編輯欄中將顯示“=Sheet8!F18”。

    一般來講，使用鼠標(biāo)選取引用方式時(shí)，Excel均默認(rèn)為是單元格的相對引用。

        2．引用其它工作簿中的工作表

    引用同一工作簿中的其它工作表時(shí)格式如下：[被引用的工作簿名稱]被引用的工作表！被引用的單元格。例如，欲在Book1工作簿Sheet3工作表的E8單元格中引用Book2工作簿Sheet2工作表中的F9 單元格，表達(dá)式為“[Book1]Sheet3!$F$9”。

    （1）同時(shí)打開目的工作簿和源工作簿。

    （2）在“窗口”菜單中選中“重排窗口”命令，在彈出的“重排窗口”對話框中，選中“水平平鋪”或“垂直平鋪”選項(xiàng)按鈕，然后單擊“確定”按鈕，使兩個(gè)窗口同時(shí)可見。

    （3）單擊Book1工作簿中的Sheet3工作表標(biāo)簽，單擊E8單元格。

    （4）單擊Book2工作簿中的任一點(diǎn)激活該工作簿，單擊Sheet2工作表標(biāo)簽，單擊F9單元格。

    （5）按回車鍵，此時(shí)編輯欄中顯示為“[Book1]Sheet3!$F$9”。

    一般來講，使用鼠標(biāo)選取引用方式時(shí)，Excel均默認(rèn)為是單元格的絕對引用。

    當(dāng)生成的公式需要引用工作表中的數(shù)據(jù)時(shí)，可以使用其中的行、列標(biāo)志來引用數(shù)據(jù)。例如，數(shù)據(jù)表中標(biāo)志為“銷售量”的數(shù)據(jù)列包含銷售量，標(biāo)志為“一分公司”的數(shù)據(jù)行包含與某一部門相關(guān)的各類數(shù)據(jù)，通過輸入公式“=一分公司 銷售量”，可以查看“一公司”的銷售量。標(biāo)志之間的空格是交叉引用運(yùn)算符，它將使公式返回標(biāo)志為“一公司”的行和標(biāo)志為 “銷售量”的列交叉處的單元格的值。如果待操作的數(shù)據(jù)沒有標(biāo)志，或者如果需要使用存儲(chǔ)在同一工作簿不同工作表中的數(shù)據(jù)，可以創(chuàng)建名稱來描述單元格或區(qū)域，然后使用時(shí)只需引用名稱即可。在默認(rèn)狀態(tài)下，名稱使用單元格絕對引用。

        1．名稱引用的優(yōu)點(diǎn)

    （1）使用名字可減少在公式或命令中發(fā)生錯(cuò)誤。引用“實(shí)發(fā)金額”肯定要比引用“F2:F6”的出錯(cuò)機(jī)會(huì)少許多。

    （2）可以重新定義名稱代表的單元格，而所有公式中該名稱所代表的單元格將隨著新的定義而更新。

    （3）使用名稱可以更容易地辨識該單元格的內(nèi)容和含義，公式中的描述性名稱也使人們更容易理解公式的含義。“基本工資”要比“B2:B6”的含義清楚明白的多。

    （4）在不同工作表中可以使用相同的名稱。Sheet1中可以定義“實(shí)發(fā)金額”，Sheet2中也可以定義“實(shí)發(fā)金額”，這在三維引用中可以大顯身手。

    （5）在同一工作簿中，既使是不同工作表間也可以直接調(diào)用名稱，而勿需貫以工作表名稱。當(dāng)然，在不同工作表中定義相同名稱的情況除外。

        2．定義名稱的規(guī)則

    （1）名稱中只能包含下列字符：漢字、A-Z、0-9、小數(shù)點(diǎn)和下劃線。

    （2）名稱的第一個(gè)字符必須是字母、文字或小數(shù)點(diǎn)。除第一個(gè)字符外，其他字符可以使用符號。

    （3）名稱中不能有空格。小數(shù)點(diǎn)和下劃線可以用作分字符，例如，First.Quarter 或 Sales_Tax。

    （4）名稱可以包含大、小寫字符。Microsoft Excel 在名稱中不區(qū)分大小寫。例如，如果已經(jīng)創(chuàng)建了名稱 Sales，接著又在同一工作簿中創(chuàng)建了名稱 SALES，則第二個(gè)名稱將替換第一個(gè)。

    （5）每個(gè)名稱最多不能超過255個(gè)字符。

    （6）名稱不能與單元格引用相同。如B1998、$K$6、R3C8等。

    （7）避免使用Excel中的固定詞匯。如DATABASE或AUTO-OPEN。

        3．為單元格或單元格區(qū)域命名

    （1）選定需要命名的單元格、單元格區(qū)域或非相鄰選定區(qū)域。

    （2）單擊編輯欄左端的名稱框。

    （3）為單元格鍵入名稱。

    （4）按回車鍵。

        4．使用現(xiàn)有的行列標(biāo)志為單元格命名

    （1）選定需要命名的區(qū)域，把行列標(biāo)志也包含進(jìn)去。

    （2）在“插入”菜單中，指向“名稱”，再單擊“指定”命令。

    （3）在“名稱在”選項(xiàng)框中，通過選定“首行”、“最左列”、“尾行”或“最右列”復(fù)選框來指定包含標(biāo)志的位置。

        5．修改名稱

    （1）在“插入”菜單“名稱”子菜單中選中“定義”命令，顯示“定義名稱”對話框。

    （2）在“當(dāng)前工作簿中名稱”列表框中，選中欲更改的名稱。

    （3）在“當(dāng)前工作簿中名稱”文本框或“引用位置”文本框中，編輯、修改欲更改的名稱或引用的單元格位置。

    （4）單元“確定”按鈕。

        6．刪除名稱

    （1）在“插入”菜單“名稱”子菜單中選中“定義”命令，顯示“定義名稱”對話框。

    （2）在“當(dāng)前工作簿中名稱”列表框中，選中欲刪除的名稱。

    （3）單擊“刪除”按鈕。

        7．引用名稱

    （1）當(dāng)名稱在同一工作簿時(shí)

    選中“插入”菜單“名稱”子菜單中的“粘貼”命令，在“粘貼名稱”列表框中選中欲插入的引用名稱，單擊“確定”按鈕，則該名稱被插入到當(dāng)前位置。當(dāng)然，也可以在插入點(diǎn)直接鍵入欲引用的單元格或單元格范圍的名稱，如“=銷售總額-成本總額”。

    （2）當(dāng)名稱不在同一工作簿時(shí)

    打開源工作簿和目的工作簿，并使其水平平鋪或垂直平鋪，然后在目的工作簿插入點(diǎn)直接鍵入，格式為：“欲引用單元格所在的工作簿名稱！引用名稱”，如“=衡水師專98年5月份工資表！工資總額*120%+100”。在這里，“衡水師專98年5月份工資表”是引用的工作簿名稱，“工資總額”是引用的單元格名稱。

    注意：如果源工作簿保存在My Documents文件夾中，則可以不打開源工作簿，引用時(shí)也勿需使用路徑。如果源工作簿沒有保存在My Documents文件夾中，則必須打開源工作簿，或者在引用時(shí)需添加該工作簿所在的路徑。

    如果需要分析某一工作簿中多張工作表的相同位置處的單元格或單元格區(qū)域中的數(shù)據(jù)，最快捷的方法就是使用三維引用。三維引用包含一系列工作表名稱和單元格或單元格區(qū)域引用。Microsoft Excel 將使用存儲(chǔ)在起始引用名稱和結(jié)束引用名稱之間的所有工作表。使用三維引用來引用多個(gè)工作表上的同一單元格或區(qū)域工作簿必須包含多張工作表。

        1．三維引用

    （1）單擊需要輸入公式的單元格。

    （2）鍵入“＝”等號），再輸入函數(shù)名稱，接著再鍵入左圓括號。

    （3）單擊需要引用的第一個(gè)工作表標(biāo)簽。

    （4）按住“Shift”鍵，單擊需要引用的最后一個(gè)工作表標(biāo)簽。

    （5）選定需要引用的單元格或單元格區(qū)域。

    （6）完成公式。

        2．使用三維引用為多個(gè)工作表上的單元格命名

    （1）在“插入”菜單中，指向“名稱”，再單擊“定義”命令。

    （2）在“在當(dāng)前工作簿的名稱”編輯框中，鍵入名稱。

    （3）如果“引用位置”編輯框中包含引用，請選定等號（＝）和這個(gè)引用，再按“Backspace”鍵。

    （4）在“引用位置”框中，鍵入“＝”（等號）。

    （5）單擊需要引用的第一個(gè)工作表的標(biāo)簽。

    （6）按住Shift鍵，再單擊需要引用的最后一個(gè)工作表的標(biāo)簽。

    （7）選定需要引用的單元格或單元格區(qū)域。