編按：

公式中有單元格的引用才能使公式具有更強的可變性，如果引用的單元格可以隨著某些條件的變化而變化，就會使公式的功能更加強大，這就需要使用引用類函數(shù)來實現(xiàn)。下面一起來學習下幾種常用的引用類函數(shù)。

一、CHOOSE()函數(shù)—根據(jù)情況選擇值或單元格區(qū)域

在某些公式中，由于條件不同，需要使用不同的值，或者引用不同的單元格區(qū)域，這時候就可以使用CHOOSE()函數(shù)來實現(xiàn)選擇。其語法格式為CHOOSE（index_num,value1,[value2],…）。從語法結(jié)構(gòu)中可以看出，該函數(shù)包含兩個必選參數(shù)，各參數(shù)意義為：

index_num：指定要選擇的參數(shù)的序號，必須為1-254之間的整數(shù)。

value1、value2…：返回值列表，可以是單元格引用或名稱、公式等，CHOOSE()函數(shù)將根據(jù)其排序，選擇第index_num個參數(shù)作為函數(shù)返回值。

例子：客戶消費評級

某公司要根據(jù)客戶的消費金額將客戶劃分為5個等級。要按小于1000、1000-3000、3000-10000、10000-50000和大于50000的區(qū)間，分別劃分為鐵牌、銅牌、銀牌、金牌、鉆石5個等級。

【分析】可以使用CHOOSE()函數(shù)根據(jù)一個索引號在參數(shù)列表中選取一個值，這個參數(shù)列表就可以定義這5個等級的文本。然后使用MATCH()函數(shù)進行模糊匹配，得到代表這5個區(qū)間的序號。再用CHOOSE()函數(shù)返回對應的等級文本。

【具體步驟】

在D2單元格中輸入公式“=CHOOSE(MATCH(C2,{0,1000,3000,10000,50000},1),"鐵牌","銅牌","銀牌","金牌","鉆石")”，摁“Enter”鍵完成公式輸入，然后向下填充，判定等級。

本例公式，是由MATCH()函數(shù)和CHOOSE()函數(shù)嵌套組成。首先通過MATCH()函數(shù)將D列單元格中具體數(shù)值轉(zhuǎn)換為每個區(qū)間代表的序號，然后將這個序號作為CHOOSE()函數(shù)的index_num參數(shù)，用于從后面的參數(shù)列表中選擇輸出符合條件的文本。

二、HYPERLINK()函數(shù)——利用超鏈接快速跳轉(zhuǎn)到其他位置

超鏈接在網(wǎng)頁中是非常常見的，它在Excel表格中的用途也非常廣泛。尤其是在數(shù)據(jù)非常多的表格中，用超鏈接導航可以省去很多數(shù)據(jù)查找的時間。這就需要HYPERLINK函數(shù)來實現(xiàn)。該函數(shù)的語法結(jié)構(gòu)為：HYPERLINK（LINK_LOCATION，[FRIENDLY_NAME]）。

該函數(shù)一共就兩個參數(shù)：

LINK_LOCATION代表鏈接的地址；FRIENDLY_NAME代表顯示的標題。

例子：鏈接到當前工作表的指定區(qū)域

要求點擊A3單元格能夠跳轉(zhuǎn)到D3單元格。

在A3單元格輸入“=HYPERLINK("#D3","D3單元格")”，其中“D3”代表鏈接的地址，“D3單元格”代表顯示的標題。第一個參數(shù)加“#”這是固定用法，朋友們記住就行。摁“Enter”鍵完成輸入。單擊A3單元格，就會跳轉(zhuǎn)到D3單元格。

其實，HYPERLINK函數(shù)還有鏈接到文件夾、鏈接到文件、鏈接到當前工作薄中其他工作表的指定區(qū)域、鏈接到網(wǎng)頁等功能，非常簡單，朋友們可以自己練習。

三、INDIRECT()函數(shù)——文本表示的引用也能返回正確的值

在某些公式中需要引用的單元格并不確定，或者這些單元格較為特殊，無法直接引用其地址。此時也可使用文本來“描述”單元格，并最終將以文本表示的單元格地址轉(zhuǎn)換為公式可引用的單元格地址。這種情況可以使用INDIRECT()函數(shù)來完成。其語法結(jié)構(gòu)：INDIRECT(ref_text,[a1])。

ref_text：以文本形式表示的對單元格的引用。

a1：指定包含在單元格ref_text中引用樣式的邏輯值。當參數(shù)取值TURE或省略時，默認采用A1引用樣式，當參數(shù)取值為FALSE時，采用R1C1引用樣式。

例子：數(shù)據(jù)匯總

某公司每月都會制作一張月度銷售表，年終需要將這些表格中數(shù)據(jù)匯總到一張表中，并從12張表中引用數(shù)據(jù)。觀察表格的結(jié)構(gòu)可以發(fā)現(xiàn)，每張工作表的名稱都在匯總的表格中列舉了出來。此時，我們可以使用INDIRECT()函數(shù)和ROW()函數(shù)完成數(shù)據(jù)引用。

【具體步驟】

在“匯總”工作表中的C4單元格輸入公式“=INDIRECT(C$3&"!C"&ROW()-1,TRUE)”，摁“Enter”鍵完成輸入。向右拖動單元格右下角的自動填充柄至N4單元格，分別引用了12張工作表中C3單元格的值。保持選中狀態(tài)，向下填充公式至11行，引用所有數(shù)據(jù)。

公式先獲取C3單元格的值“1月”，這個1月和工作表的名稱“1月”相同，即要引用的工作表名稱。然后通過連接符連接“！C”文本，引用工作表通常使用感嘆號連接，并且引用的單元格需要加上雙引號,表示引用C3單元格所在的工作表中C列內(nèi)容。接著通過“ROW()-1”取得行號，即目標工作表中當前行號上一行的內(nèi)容，最后通過INDIRECT()函數(shù)將連接起來的文本轉(zhuǎn)換為A1引用樣式引用的單元格。

四、OFFSET()函數(shù)——根據(jù)指定偏移量選擇單元格區(qū)域

如果要以某個單元格為基準，引用與之距離指定行列數(shù)以后的單元格或單元格區(qū)域，則可以利用OFFSET()函數(shù)來完成。其語法格式：OFFSET(reference,rows,cols,[height],[width])。

各參數(shù)意義：

reference：作為偏移量參照系的引用。也稱為基準單元格，必須為對單元格或相連單元格區(qū)域的引用。

rows：相對于基準單元格向上（Rows取負值）或向下（Rows取正值）偏移的行數(shù)。

cols：相對于基準單元格向左（cols取負值）或向右（cols取正值）偏移的列數(shù)。

height：要返回的引用區(qū)域的行數(shù)，必須是正整數(shù)。

width：要返回的引用區(qū)域的列數(shù)，必須是正整數(shù)。

OFFSET()函數(shù)并不會改變?nèi)魏螁卧窕蚋倪x定區(qū)域，它只是返回一個引用。函數(shù)參數(shù)中，除第一個參數(shù)外，其它4個參數(shù)都必須是整數(shù)。OFFSET()函數(shù)是比較難理解的函數(shù)，下面就詳細介紹這個函數(shù)。

1.將A1單元格內(nèi)容克隆到D1單元格，在D1單元格輸入=OFFSET(A1,0,0)，D1單元格顯示“A”。

2.將OFFSET(A1,0,0)第二個參數(shù)0改成1，即改成=OFFSET(A1,1,0)，寫入D1單元格中，D1單元格顯示“C”。

3.將OFFSET(A1,0,0)第二個參數(shù)0改成2，即改成=OFFSET(A1,2,0)，寫入D1單元格中，D1單元格顯示“E”。

相信大家已經(jīng)看明白了，OFFSET(A1,2,0)的第二個參數(shù)，是以A1為基準，向下移動幾個單元格。而第一個參數(shù)就是基準單元格。另外，第三個參數(shù)寫幾，以基準單元格向右移動幾個單元格。

4.以A1為基準，在D1單元格中克隆B3的內(nèi)容，應該怎么寫？答案是在D1單元格輸入“=OFFSET(A1,2,1)”。

但是，OFFSET一共有5個參數(shù)，我們剛才只用了前3個參數(shù)，下面來說說剩下兩個的用法。

5.要求，使用OFFSET函數(shù)一次性克隆A1和B1到D1和E1。

選擇D3和E3單元格，在公式欄寫入“=OFFSET(A1,0,0,1,2)”，按下快捷鍵變成數(shù)組形式，這就得到所需要的結(jié)果。

到這里，大家應該領(lǐng)悟到第五個參數(shù)的真諦了吧。第五個參數(shù)是2，就是返回以第一個參數(shù)A2單元格為基準，橫向兩個單元格的內(nèi)容，輸出單元格也要同時選擇橫向兩個單元格。不然，輸出只選一個單元格會放不下，它就會報錯。

6.如何利用OFFSET一次性克隆A1:B3區(qū)域到D1:E3區(qū)域？選中D1:E3區(qū)域，在公式欄輸入“=OFFSET(A1,0,0,3,2)”, 按下快捷鍵變成數(shù)組形式，這就得到所需要的結(jié)果。

OFFSET（以A1單元格為基準，0，0，返回橫向2個單元格區(qū)域，返回縱向3個單元格區(qū)域）。第四個參數(shù)是克隆顯示縱向的單元格數(shù)量。這就是OFFSET()函數(shù)中5個參數(shù)的原理。

OK，今天我們學習了很多引用函數(shù)，包括：CHOOSE()函數(shù)、HYPERLINK()函數(shù)、INDIRECT()函數(shù)、OFFSET()函數(shù)，這些函數(shù)相對來說較難理解，但在EXCEL工作中都會用到，小伙伴們，請一定要掌握。

本文配套的練習課件請加入QQ群：902294808下載。

做Excel高手，快速提升工作效率，部落窩教育《一周Excel直通車》視頻和《Excel極速貫通班》直播課全心為你！

掃下方二維碼關(guān)注公眾號，可隨時隨地學習Excel：

相關(guān)推薦：

將excel中的數(shù)據(jù)按照指定數(shù)量進行重復，非得用VBA才能實現(xiàn)嗎？

如何在特定位置批量插入空行等12種實用辦公技巧

4種刪除excel重復值的小妙招，速收藏

Power Query的數(shù)據(jù)替換技巧比Excel函數(shù)更萬能！

版權(quán)申明：

本文作者賦春風；同時部落窩教育享有專有使用權(quán)。若需轉(zhuǎn)載請聯(lián)系部落窩教育。