月薪上萬必學10大excel函數之十:它身負5大絕技,是動態統計之王

excel教程2019-09-12 00:32:42

關注回覆[目錄]學習113篇Excel教程


全套Excel視頻教程,掃碼觀看

編按:
哈嘍,大家好!今天是部落窩函數課堂的第6課,我們將認識人送外號“動態統計之王”的OFFSET函數!OFFSET函數是一個非常實用的函數,它在下拉菜單、動態圖表、動態引用等操作中都具有不可替代的作用。毫不誇張的説Excel表格的高效,有相當一部分的功能來源於OFFSET。今天就跟着小編一起來認識一下它吧!(由於教程篇幅較長,將分為上下兩篇,本篇為上篇。)
 
【前言】


OFFSET函數是判斷Excel函數使用者是否進階的一個重要函數之一。在實際工作中,如果你需要對工作中的數據文件進行系統化、自動化的建模,那麼勢必會使用這個函數。
 
【功能及語法】


OFFSET函數的功能是,以指定的引用為參照系,通過給定的偏移量返回新的引用。
 

語法

OFFSET(reference,rows,cols,[height],[width])


reference   是原基礎點

rows          是要偏移的行數,正數向下負數向上零不變

cols           是要偏移的列數,正數向右負數向左零不變

[height]     是基礎點偏移後縱向擴展幾行,正數向下擴展,負數向上擴展。

[width]       是基礎點偏移後橫向擴展幾列,正數向右擴展,負數向左擴展。


如果不使用第四個和第五個參數(但不可以為零),則新引用的區域和原基礎點大小一致。

原基礎點可以是一個單元格,也可以是一個區域。
 
剛剛接觸OFFSET函數的同學,想要理解上面這些參數,可能存在一定的難度,那麼我們用一個圖解的方式來給大家説明一下吧。
 

 
相信大家看這個圖都花費了不少時間吧。我們可以先按照上圖的指引,將數據填入OFFSET函數中,實際操作一下,來看看是否和新區域的地址一致呢?

温馨提示:加入下面QQ羣:109723835,下載教程配套的課件練習操作。


先來測試下第一個例子,看看正數為參量的運行結果:
 

 
通過驗算,對黃色 “新區域”中的值進行求和,等於256,與單元格C15中的值一致,結果正確。如果同學們想模擬這個數據,也可以選中C15單元格,再通過工具欄中“公式——公式審核——公式求值”的功能,就能更加直觀的看到OFFSET的返回值。(在函數中使用F9也是可以的,選中公式中OFFSET的函數部分,再按F9即可,這裏就不多講了。)
 

 
再來測試下第二個例子,看看負數為參量的運行結果:
 

 
大家可以用“公式求值”的方式,自己測試一下,看看OFFSET函數區域的返回值。


那麼知道了OFFSET的基本運行原理之後,它在實際的工作中就可以幫助我們進行很多的操作和運算,而且有了這個函數的參與,可以實現excel中很多自動化的效果。下面讓我們一起來看看OFFSET函數在實際操作中起到的強大作用! 



1

初級常規用法



作為其他函數的區域引用,應該是OFFSET函數最基礎的用途了。OFFSET函數並不是移動了單元格區域,而是返回了一個偏移擴展後的區域地址。因此所有將引用區域作為參數的函數,都可以利用OFFSET函數的返回值。例如我們上面的例子SUM(OFFSET()),再比如下面這個例子:
 

 
函數原理和上面的用法相同,我們就不再贅述了,依然是利用OFFSET函數返回的區域作為MAX函數的參數。

 


2

進階常規用法


 
絕技①:模擬轉置TRANSPOSE函數
 

 
我們在使用TRANSPOSE函數前,需要先選擇相應大小的轉置區域,而且還需用CTRL+SHIFT+ENTER三鍵結束公式,比較繁瑣。

這裏我們可以使用OFFSET函數來模擬這個轉置的效果,如上圖所示。
 
A11單元格函數:
=OFFSET($A$1,COLUMN()-1,ROW()-11)
 
函數解析
 
轉置數據其實就是一個“行轉列”、“列轉行”的過程,再説具體點就是行號與列號互換的問題。在原數據中的第一列“姓名”列,轉置後變成了新區域中的第一行。同理“姓名”列中每行的行號,就成為了轉置後的列號。使用OFFSET的原理,就是偏移取值的時候,調換行列號的引值範圍。
 
 比如A11單元格,COLUMN()=1,1-1=0,那麼OFFSET的第二參數為0,説明原基礎點的行數不偏移(OFFSET的第二參數表示行偏移量,不熟悉的話看看前面的內容喲!)。ROW()=11,11-11=0,OFFSET的第三參數為0,説明列數也不偏移,所以引用的是原基礎點A1單元格的值。
 
★★ 把函數向右拉動填充,B11單元格,COLUMN()=2,2-1=1,那麼OFFSET的第二參數為1,説明原基礎點的行數向下偏移一個位置。ROW()=11,11-11=0,OFFSET的第三參數為0,説明列數不偏移,所以B11單元格引用的是基礎點A1向下偏移後的A2單元格的值。
 
★★★ 把A11單元格的函數向下拉動填充,A12單元格,COLUMN()=1,1-1=0,行數不偏移。ROW()=12,12-11=1,OFFSET的第三參數為1,説明列數從基礎點A1向右偏移一個位置,引用的是B1單元格的值(我們公式中的A1之所以使用絕對引用,是因為我們所有的單元格都是以A1為基礎點)。
 
以此類推,當我們使用鼠標下拉右拉填充公式之後,藉助COLUMN和ROW函數幫我們定位出各個單元格的偏移量,由此達到了轉置的效果。
 
絕技②:模擬VLOOKUP函數的反向查詢功能
 

 
VLOOKUP函數的反向查詢大多是藉助數組完成的,但因為數組的原因,在數據量較多的情況下,函數可能會卡頓,所以很多同學也會使用INDEX函數來代替。那麼今天就再豐富一下大家的知識量,我們用OFFSET函數來處理這類問題。
 
C12單元格函數:
=OFFSET($A$1,MATCH("D2568",$B$2:$B$7,0),)
 
函數解析
 
我們以單元格A1作為原基礎點,需要返回的值與原基礎點在同一列,所以我們只需要考慮OFFSET函數的行偏移量,不用考慮列偏移量。因為員工編號一般都是具有唯一性的值,所以我們採用MATCH函數得到編號“D2568”在區域B2:B7中的序號,返回值4作為OFFSET函數的行偏移量,帶入到OFFSET函數中,=OFFSET($A$1,4,)。列偏移省略默認為0,擴展寬度和擴展高度省略默認為1 (即一個單元格),是不是就是A5單元格啦!
 
絕技③:數據重置升級版——重排數據結構
 

 
在F2:H2區域輸入公式後,下拉填充數據,就得到了右面的一維數據表。這種重排數據的問題,在實際工作中應該不少見吧!那麼同學們會選擇什麼方法解決呢?作者反而覺得OFFSET函數的思路更加的簡潔清晰。
 
函數解析:
 
第一步:得到連續出現的姓名
 
F2單元格函數:
=OFFSET($A$1,INT((ROW(F1)-1)/3)+1,)
 
因為科目一共有三個,所以可以確定同一個姓名需要出現三次,那麼當我們下拉F2單元格填充函數的時候,就要保證OFFSET函數的行偏移量每3個單元格的參數值都是一樣的。這裏就需要有一個“除數取整”的數學思維了,我們列個圖來輔助説明:
 

 
從圖中我們可以看出一組序號,通過INT((序號-1)/3)+1的轉換後,就可以得到右側的序列(如果有4個科目,那就把3改成4,依此類推)。將這個序列號放入OFFSET函數的第二參數,作為行偏移的標準,就可以得到我們姓名列的效果了。
 
第二步:給同一個人分配不同的科目
 
G2單元格函數:
=OFFSET($A$1,,MOD(ROW(G1)-1,3)+1)
 
因為我們F列中的每個姓名都出現了三次,這就決定了語文、數學、英語這三個科目需要順序、循環地羅列出來,同第一步的思路一樣,用“除數求餘”的數學思維來達到效果。
 

 
如上圖所示,序號通過MOD函數的轉換,得到一個順序、循環羅列的序號。將該序號作為OFFSET函數的第三參數列偏移量,就可以順序、循環的引出原數據的科目內容。
 
第三步:通過姓名和科目,模擬INDEX函數,在原數據中引出成績
 
H2單元格函數:
=OFFSET($A$1,MATCH(F2,$A$2:$A$5,0),MATCH(G2,$B$1:$D$1,0))
 
分別用MATCH函數,得到數據在相關區域中所對應的序號,作為OFFSET的偏移量,分別放入第二、三參數中。從基準點A1單元格偏移後的單元格,就是我們需要的成績值。
 
通過上面的內容,我們不難發現OFFSET函數,往往都是和MATCH函數連用。因為MATCH函數可以找到關鍵字在一個數列中的序號,所以我們經常利用這個函數來確定OFFSET函數的偏移量。
 
【編後語】
 
如果你是剛學習OFFSET函數的同學,我相信這個函數對於你來説應該不好理解,可我還是建議你,一定要多練習,要學會它。不要怕出錯,從錯誤中可以發現很多的問題,也可以鞏固你對一個函數的認知。
 
下一篇OFFSET函數的文章我們將進行一些高級的用法説明,絕對是你工作中會使用到的,所以如果你今天沒有看懂,不要急,再看再體會,當然也可以來找老師,我們一起努力學會它。

掃一掃添加老師微信


在線諮詢Excel課程


Excel教程相關推薦



MATCH:函數哲學家,找巨人做伴。新出道必學!

VLOOKUP&LOOKUP;雙雄戰(四):在橫向和逆向查詢上的血拼!

沒有一維表的支撐,神也無法幫你成為Excel高手!

https://hk.wxwenku.com/d/201357649