談一談函數(shù)中的極品――OFFSET
發(fā)布時(shí)間:2022-11-07 10:36 [ 我要自學(xué)網(wǎng)原創(chuàng) ] 發(fā)布人: 小劉2175

說(shuō)起OFFSET函數(shù),真的是像霧像雨又像風(fēng),從復(fù)雜的數(shù)據(jù)匯總、到數(shù)據(jù)透視表再到高級(jí)動(dòng)態(tài)圖表,都離不開(kāi)OFFSET函數(shù)的默默付出。

通過(guò)OFFSET函數(shù),可以生成數(shù)據(jù)區(qū)域的動(dòng)態(tài)引用,這個(gè)引用再作為半成品,通過(guò)后續(xù)的處理加工,就可以為圖表和透視表提供動(dòng)態(tài)的數(shù)據(jù)源、為其他函數(shù)生成特定的引用了。

首先,咱們說(shuō)說(shuō)這個(gè)函數(shù)的作用,微軟的幫助文件是這樣描述的:

以指定的引用為參照系,通過(guò)給定偏移量返回新的引用。

瞧瞧,這么簡(jiǎn)單的幾句話,讓人家怎么能猜透你的心思嘛。

這個(gè)函數(shù)有5個(gè)參數(shù):

第一個(gè)參數(shù)是基點(diǎn)

第二個(gè)參數(shù)是要偏移幾行,正數(shù)向下,負(fù)數(shù)向上。

第三個(gè)參數(shù)是要偏移幾列,正數(shù)向右,負(fù)數(shù)向左。

第四個(gè)參數(shù)是新引用幾行。

第五個(gè)參數(shù)是新引用幾列。

如果不使用第四個(gè)和第五個(gè)參數(shù),新引用的區(qū)域就是和基點(diǎn)一樣的大小。

如果使用下面這個(gè)公式:


=OFFSET(C3,4,2,4,3)

就是以C3為基點(diǎn),向下偏移4行,向右偏移2列,新引用的行數(shù)是4行,新引用的列數(shù)是3列,最終得到對(duì)E7:G10單元格區(qū)域的引用。





當(dāng)然,僅僅得到引用是沒(méi)啥用處的,咱們的目的是把OFFSET函數(shù)得到的引用作為一個(gè)半成品,再通過(guò)其他方法進(jìn)行再加工。

這么說(shuō)還是有點(diǎn)抽象啊,再來(lái)一個(gè)形象一點(diǎn)的:

OFFSET函數(shù)就像是一個(gè)鬼子小分隊(duì),從據(jù)點(diǎn)董家莊(C3)出動(dòng),順著大路向南走4里(C7)






拐彎兒再向東2里,這時(shí)候就到馬家河子(E7)了






鬼子隊(duì)長(zhǎng)說(shuō)了,我要以馬家河子(E7)這個(gè)地方開(kāi)始,再占領(lǐng)一片地盤。有多大呢?向南4里,向東3里。





吆西,結(jié)果就是E7:G10單元格區(qū)域了。

簡(jiǎn)單認(rèn)識(shí)了OFFSET函數(shù),咱們?cè)儆靡粋(gè)動(dòng)態(tài)圖表的制作,來(lái)說(shuō)說(shuō)OFFSET函數(shù)的實(shí)際應(yīng)用。

OFFSET函數(shù)和動(dòng)態(tài)圖表之間,屬于是“見(jiàn)不得人”的關(guān)系。

所謂動(dòng)態(tài)圖表,就是能根據(jù)指定的條件,自動(dòng)變化圖表數(shù)據(jù)源,使圖表能夠按照我們指定的規(guī)則,動(dòng)態(tài)顯示數(shù)據(jù)中的重點(diǎn)關(guān)注部分。

說(shuō)“見(jiàn)不得人”,是指不能在圖表中直接使用OFFSET函數(shù),而是要將OFFSET函數(shù)先定義成名稱,然后在圖表中使用自定義名稱作為數(shù)據(jù)源。

看下面這個(gè)題目:

這個(gè)銷售流水記錄中,每天都要不斷的添加數(shù)據(jù),F(xiàn)在要制作一個(gè)圖表,僅展示最近7天的銷售狀況。





首先,在【公式】選項(xiàng)卡下,單擊【定義名稱】,分別定義兩個(gè)名稱:


日期=OFFSET($A$1,COUNT($A:$A),0,-7)
銷售額=OFFSET($B$1,COUNT($A:$A),0,-7)






說(shuō)說(shuō)定義名稱日期這個(gè)公式的意思:

COUNT函數(shù)對(duì)A列數(shù)值計(jì)數(shù),結(jié)果作為OFFSET函數(shù)的行偏移參數(shù)。

OFFSET函數(shù)以A1為基點(diǎn),向下偏移的行數(shù)是COUNT的結(jié)果,也就是A列有多少個(gè)數(shù)值,就向下偏移多少行。

這時(shí)候就相當(dāng)于到了A列數(shù)值的最后一行,給定的偏移列數(shù)是0,新引用的行數(shù)是-7,得到從A列數(shù)值的最后一行開(kāi)始,向上7行這樣一個(gè)動(dòng)態(tài)的區(qū)域。

如果A列的數(shù)值增加了,COUNT函數(shù)的計(jì)數(shù)結(jié)果就增加了,OFFSET函數(shù)的行偏移參數(shù)也就隨之變化。

就相當(dāng)于一竿子捅到底,然后來(lái)個(gè)燒雞大窩脖兒,向上引用7行,所以得到的始終是最后7行的引用。

接下來(lái),按下圖步驟插入一個(gè)柱形圖。





右鍵單擊數(shù)據(jù)系列,選擇數(shù)據(jù)源





設(shè)置數(shù)據(jù)系列的系列值為:

=Sheet2!銷售額

這里的Sheet2,是數(shù)據(jù)源工作表的名稱。銷售額,就是定義的名稱。

設(shè)置軸標(biāo)簽的區(qū)域?yàn)椋?br style="box-sizing: border-box;">
=Sheet2!日期

OK了,以后我們只要不斷的在數(shù)據(jù)源表格中添加數(shù)據(jù),這個(gè)圖表就始終顯示的是最近7天的數(shù)據(jù)變化了。

最后別忘了,再對(duì)圖表進(jìn)行簡(jiǎn)單的美化,收工了——

今天只是講了OFFSET函數(shù)的基本用法,其實(shí)她還有很多更加復(fù)雜的引用方式,需要咱們一點(diǎn)點(diǎn)的學(xué)習(xí)領(lǐng)悟。只要功夫深,金箍棒也能磨成針。

Office365 Excel函數(shù)教程
我要自學(xué)網(wǎng)商城 ¥75 元
進(jìn)入購(gòu)買
文章評(píng)論
0 條評(píng)論 按熱度排序 按時(shí)間排序 /350
添加表情
遵守中華人民共和國(guó)的各項(xiàng)道德法規(guī),
承擔(dān)因您的行為而導(dǎo)致的法律責(zé)任,
本站有權(quán)保留或刪除有爭(zhēng)議評(píng)論。
參與本評(píng)論即表明您已經(jīng)閱讀并接受
上述條款。
V
特惠充值
聯(lián)系客服
APP下載
官方微信
返回頂部
分類選擇:
電腦辦公 平面設(shè)計(jì) 室內(nèi)設(shè)計(jì) 室外設(shè)計(jì) 機(jī)械設(shè)計(jì) 工業(yè)自動(dòng)化 影視動(dòng)畫 程序開(kāi)發(fā) 網(wǎng)頁(yè)設(shè)計(jì) 會(huì)計(jì)課程 興趣成長(zhǎng) AIGC