Excel中單元格的引用

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

    一、A1和R1C1引用樣式

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

        1．A1引用樣式

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

        2．R1C1引用樣式

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

        1．單元格的絕對引用

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

        2．單元格相對引用

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

        3．單元格的混合引用

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

        4．R1C1 引用樣式

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

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

    （1）復制粘貼公式時

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

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

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

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

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

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

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

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

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

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

    （2）剪切粘貼公式時

    （3）自動填充公式時

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

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

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

    （4）錄制宏時

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

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

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

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

    （2）鍵入“=”。

    （3）單擊Sheet8工作表標簽。

    （4）單擊F18單元格。

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

    一般來講，使用鼠標選取引用方式時，Excel均默認為是單元格的相對引用。

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

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

    （1）同時打開目的工作簿和源工作簿。

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

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

    （4）單擊Book2工作簿中的任一點激活該工作簿，單擊Sheet2工作表標簽，單擊F9單元格。

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

    一般來講，使用鼠標選取引用方式時，Excel均默認為是單元格的絕對引用。

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

    （4）按回車鍵。

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

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

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

    （3）在“名稱在”選項框中，通過選定“首行”、“最左列”、“尾行”或“最右列”復選框來指定包含標志的位置。

        5．修改名稱

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

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

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

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

        6．刪除名稱

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

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

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

        7．引用名稱

    （1）當名稱在同一工作簿時

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

    （2）當名稱不在同一工作簿時

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

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

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

        1．三維引用

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

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

    （3）單擊需要引用的第一個工作表標簽。

    （4）按住“Shift”鍵，單擊需要引用的最后一個工作表標簽。

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

    （6）完成公式。

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

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

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

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

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

    （5）單擊需要引用的第一個工作表的標簽。

    （6）按住Shift鍵，再單擊需要引用的最后一個工作表的標簽。

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