含金量超高的4個引用函數(shù),一次性給你說透
?
作者:賦春風來源:部落窩教育發(fā)布時間:2021-08-10 17:19:14點擊:7956
編按:
公式中有單元格的引用才能使公式具有更強的可變性,如果引用的單元格可以隨著某些條件的變化而變化,就會使公式的功能更加強大,這就需要使用引用類函數(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)”,按下快捷鍵
到這里,大家應該領(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)”, 按下快捷鍵
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)嗎?
Power Query的數(shù)據(jù)替換技巧比Excel函數(shù)更萬能!
版權(quán)申明:
本文作者賦春風;同時部落窩教育享有專有使用權(quán)。若需轉(zhuǎn)載請聯(lián)系部落窩教育。
最熱教程
- 像綠皮火車一樣長像珠穆拉瑪峰一樣高的Excel表怎么操作才方便?
- Power Query實戰(zhàn):按指定次數(shù)遞增數(shù)據(jù)
- 2019年全網(wǎng)最全—excel提取身份證信息合集?。ńㄗh收藏)-下篇
- 明明沒有重復,Excel卻判定數(shù)據(jù)重復,這是怎么回事?
- 文本格式的求和,及求和中最容易出現(xiàn)的問題解疑
- 致命缺陷:不懂一維表!
- 函數(shù)組合思維,你有嗎?
- 學會這2個公式,整理考勤數(shù)據(jù)只要一分鐘
- 就算被說是拍馬屁也成,今天你應該這樣發(fā)Excel報表……
- 如何計算Excel單元格中的算式,四種求和方法請收好!