offset函數(shù)高級應(yīng)用

1、OFFSET引引用用函函數(shù)數(shù)OFFSET(reference,rows,cols,height,width)以指定的引用為參照系，通過給定偏移量得到新的引用。返回的引用可以為一個單元格或單元格區(qū)域。并可以指定返回的行數(shù)或列數(shù)?；颈驹砝砘creference公式：=OFFSET(A5,2,2,5,3)=$C$7:$E$11ROWS:2偏移后基點COLUMNS:2HEIGHT:5WIDTH:3以上圖示為OFFSET常規(guī)偏移示例，函數(shù)結(jié)果為引用（REF）。注注意意事事項項1、首先必須注意，reference必須為引用（包括函數(shù)產(chǎn)生的三維引用），不能為常量或數(shù)組。2、行偏移_rows、列偏移_

2、cols為必輸項，如省略必須用,逗號進行占位，缺省值為0(即不偏移)；行數(shù)_height和列數(shù)_width為可選項，可省略，缺省視為與基點行數(shù)和列數(shù)相同。常常規(guī)規(guī)知知識識點點一、基點與行數(shù)、列數(shù)的關(guān)系。通常情況下，如果基點以單元格區(qū)域作為引用范圍，如果不指定行數(shù)或列數(shù)，系統(tǒng)默認(rèn)是引用區(qū)域的行數(shù)和列數(shù)來確定結(jié)果。公式1=OFFSET(A5:C5,2,2)公式結(jié)果$C$7:$E$7公式2=OFFSET(A5,2,2,3)公式結(jié)果$C$7:$E$7由于引用基點為A5:C5 3列區(qū)域，同時公式1中未指定行數(shù)或列數(shù)，所以最終結(jié)果仍然為3列區(qū)域，公式1與公式2結(jié)果相同，兩者等價。公式3=OFFSET(A5

3、:C9,2,2)公式結(jié)果$C$7:$E$11公式4=OFFSET(A5,2,2,5,3)公式結(jié)果$C$7:$E$11同理，公式3與公式4結(jié)果相同，兩個公式等價。二、行數(shù)與列數(shù)對引用基點的影響。在上面的公式1和公式3中，都是引用基點為單元格區(qū)域引用，同時都未設(shè)置行數(shù)與列數(shù)的情況下的結(jié)果。但有時，基點為區(qū)域引用時，用戶同時又指定了行數(shù)或(和)列數(shù)，那么用戶必須清楚了解實際引用的基點區(qū)域，才能得出正確結(jié)果。例如已經(jīng)將整個P32:R36區(qū)域定義為名稱 DATA：=OFFSET應(yīng)用基礎(chǔ)!$P$32:$R$36例1：利用名稱作為基點，得到右圖的淺藍(lán)色子區(qū)域公式：=OFFSET(DATA,1,2)公式結(jié)果$

4、P$33:$R$34檢驗：6由于公式中指定了最終結(jié)果的行數(shù)(2)，未指定列數(shù)，那么公式實際上以P32:R32區(qū)域作為引用基點，公式等同于：公式1=OFFSET(P32:R32,1,2)公式2=OFFSET(P32,1,2,3)檢驗：6例2：利用名稱作為基點，得到右圖中紅色字體區(qū)域公式：=OFFSET(DATA,2,1,3,2)公式結(jié)果$Q$34:$R$36檢驗：6由于公式中指定了最終結(jié)果的行數(shù)(3)和列數(shù)(2)，那么系統(tǒng)只能將DATA中的左上角單元格(P32)作為引用基點，并不是整個DATA區(qū)域。公式等同于：=OFFSET(P32,2,1,3,2)公式結(jié)果$Q$34:$R$36檢驗：6序序號號

5、姓姓名名工工資資1A1002B2003C3004D4005E500以指定的引用為參照系，通過給定偏移量得到新的引用。返回的引用可以為一個單元格或單元格區(qū)域。并可以指定返回的行數(shù)或列數(shù)。2、行偏移_rows、列偏移_cols為必輸項，如省略必須用,逗號進行占位，缺省值為0(即不偏移)；行數(shù)_height和列數(shù)_width為可選項，可省略，缺省視為與基點行數(shù)和列數(shù)相同。通常情況下，如果基點以單元格區(qū)域作為引用范圍，如果不指定行數(shù)或列數(shù)，系統(tǒng)默認(rèn)是引用區(qū)域的行數(shù)和列數(shù)來確定結(jié)果。由于引用基點為A5:C5 3列區(qū)域，同時公式1中未指定行數(shù)或列數(shù)，所以最終結(jié)果仍然為3列區(qū)域，公式1與公式2結(jié)果相同，兩者

6、等價。但有時，基點為區(qū)域引用時，用戶同時又指定了行數(shù)或(和)列數(shù)，那么用戶必須清楚了解實際引用的基點區(qū)域，才能得出正確結(jié)果。由于公式中指定了最終結(jié)果的行數(shù)(3)和列數(shù)(2)，那么系統(tǒng)只能將DATA中的左上角單元格(P32)作為引用基點，并不是整個DATA區(qū)域。基基點點員員工工編編號號員員工工姓姓名名性性別別年年齡齡A0711張三男10A0795李四男20A0733王五女30B1234趙六男40A2345張大男50A0123劉二女60偏移B1123田七男50公式=offset(B1,7,2,-3,3)檢驗：9等價于：=OFFSET(B1,7-3+1,2,3,3)解解釋釋：1、通常情況下，用戶都是

7、輸入正數(shù)來控制產(chǎn)生引用的行數(shù)和列數(shù)，偏移方向為向下取行數(shù)或向右取得列數(shù)。2、值得提醒的是，EXCEL支持用戶在行數(shù)_height和列數(shù)_width參數(shù)中輸入負(fù)數(shù)來產(chǎn)生引用。其中：負(fù)行數(shù)向上取行數(shù)，負(fù)列數(shù)向左取列數(shù)。具體的應(yīng)用可以參見先進先出實例中的應(yīng)用進行了解：http:/ 2、值得提醒的是，EXCEL支持用戶在行數(shù)_height和列數(shù)_width參數(shù)中輸入負(fù)數(shù)來產(chǎn)生引用。其中：負(fù)行數(shù)向上取行數(shù)，負(fù)列數(shù)向左取列數(shù)。http:/ 1：學(xué)學(xué)號號1001某些時候，我們需要在公式中對姓名范圍進行條件判斷，如果直接引用姓名列(K2:K11)，1002可能會因為隨著學(xué)員數(shù)據(jù)的錄入，數(shù)據(jù)區(qū)域不能隨時變化。1

8、003因此，我們可以通過OFFSET函數(shù)產(chǎn)生動態(tài)引用來實現(xiàn)。1004如定義名稱NAMES：1005=OFFSET(動態(tài)引用!$K$2,0,0,COUNTA(動態(tài)引用!$K:$K)-1)1006當(dāng)數(shù)據(jù)添加或刪除時，NAMES名稱中的元素也會動態(tài)的擴大或減小。10071008注注解解：10091、通過前面的基礎(chǔ)知識的講解，用戶應(yīng)該了解了OFFSET的基本引用。10102、這里的公式主要使用了COUNTA()函數(shù)來進行實時計數(shù)，統(tǒng)計非空單元格的數(shù)量。3、由于基點選擇K2（不包含姓名字段），而且數(shù)據(jù)是連續(xù)輸入的，那么$K:$K非空數(shù)據(jù)的個數(shù)-1就是實際OFFSET的引用的行數(shù)。4、最后將結(jié)果返回OFF

9、SET，得：OFFSET(K2,0,0,10)，即可返回K2:K11的引用區(qū)域。動動態(tài)態(tài)應(yīng)應(yīng)用用實實例例2 2：按按照照各各部部門門明明細(xì)細(xì)表表，使使用用函函數(shù)數(shù)公公式式來來生生成成各各一一級級部部其其下下的的二二級級部部的的列列表表供供用用戶戶選選擇擇。一一級級部部二二級級部部一級部產(chǎn)品開發(fā)部項目管理部管理設(shè)計二級部平臺開發(fā)產(chǎn)品開發(fā)部開發(fā)管理平臺開發(fā)游戲開發(fā)技術(shù)支持部系統(tǒng)數(shù)據(jù)庫人力資源部人事部培訓(xùn)部辦公室定定義義名名稱稱MainDept=OFFSET($D$19,COUNTA($D$19:$D$29)Depts=OFFSET($D$18,MATCH($B$19,MainDept,0),1,C

10、OUNTA(OFFSET($E$18:$H$18,MATCH($B$19,MainDept,0),)注注解解：1、這是一個比較典型的OFFSET動態(tài)引用的實例。公式中主要通過對一級部名稱的MATCH定位，再根據(jù)COUNTA來求得實際二級部部門數(shù)，最后通過OFFSET得到結(jié)果。2、其中最主要公式就是DEPTS名稱的公式，上面通過顏色標(biāo)識的是公式最核心的部分，通過紅色部分得到行偏移，通過藍(lán)色部分得到引用列數(shù)。3、如“產(chǎn)品開發(fā)部”的二級部列表，則通過MATCH查找其位置(2)，通過內(nèi)嵌OFFSET的動態(tài)引用取得E20:H20區(qū)域，再通過COUNTA來得到具體部門數(shù)，最后通過： =OFFSET($D$

11、18,2,1,3)來得出最終二級部區(qū)域引用($E$20：$G$20)，提供給B20的有效性序列供選擇。姓姓名名成成績績A70B51C76D40E68F41G51H49I77J963、由于基點選擇K2（不包含姓名字段），而且數(shù)據(jù)是連續(xù)輸入的，那么$K:$K非空數(shù)據(jù)的個數(shù)-1就是實際OFFSET的引用的行數(shù)。=OFFSET($D$18,MATCH($B$19,MainDept,0),1,COUNTA(OFFSET($E$18:$H$18,MATCH($B$19,MainDept,0),)1、這是一個比較典型的OFFSET動態(tài)引用的實例。公式中主要通過對一級部名稱的MATCH定位，再根據(jù)COUNTA

12、來求得實際二級部部門數(shù)，最后通過OFFSET得到結(jié)果。2、其中最主要公式就是DEPTS名稱的公式，上面通過顏色標(biāo)識的是公式最核心的部分，通過紅色部分得到行偏移，通過藍(lán)色部分得到引用列數(shù)。3、如“產(chǎn)品開發(fā)部”的二級部列表，則通過MATCH查找其位置(2)，通過內(nèi)嵌OFFSET的動態(tài)引用取得E20:H20區(qū)域，再通過COUNTA來得到具體部門數(shù)，最后通過：編編號號姓姓名名工工資資獎獎金金實實發(fā)發(fā)工工資資編編號號姓姓名名工工資資獎獎金金實實發(fā)發(fā)工工資資輔輔助助檢檢查查0A0711張三301040A0711張三3010401A0795李四5020701A0733王五7030100編編號號姓姓名名工工資

13、資獎獎金金實實發(fā)發(fā)工工資資1B1234趙六9040130A0795李四5020702A2345張大80501302B1123田七6050110編編號號姓姓名名工工資資獎獎金金實實發(fā)發(fā)工工資資2A0733王五703010033G1公式： =IF(MOD(ROW(),3),OFFSET($A$1,(MOD(ROW()-1,3)0)*ROUND(ROW()/3,),COLUMN(A1)-1),)3本實例是OFFSET常用的動態(tài)偏移實例，主要通過MOD、ROW、COLUMN函數(shù)來產(chǎn)生動態(tài)偏移。核心公式主要是用MOD函數(shù)來產(chǎn)生循環(huán)序列：0;1;2;0;1;2;0;1;2；通過ROW()/3來產(chǎn)生固定行數(shù)

14、(3)的自然數(shù)序列，從而通過OFFSET來產(chǎn)生具體引用得結(jié)果。通常情況下，生成工資單只用于提供給員工了解各自工資明細(xì)，一般不需要再進行計算或統(tǒng)計，因此公式還可以用以下公式代替(注公式所有結(jié)果為文本)。G1公式：=OFFSET($A$1,CHOOSE(MOD(ROW(),3)+1,65535,0,ROUND(ROW()/3,),COLUMN(A1)-1)&另類的數(shù)據(jù)清單提取請參見一表拆分多表應(yīng)用：http:/ B3:F3、B4:F4、B5:F5、B6:F6、B7:F7、B8:F8、B9:F9、B10:F10、B11:F11、B12:F12J91837164853944、而通過OFFSET

15、函數(shù)產(chǎn)生的這10個區(qū)域，各自處在不同的平面中，相對獨立，這也是理解 三維引用的要點所在。5、因此，在10個區(qū)域中進行COUNTIF的條件統(tǒng)計結(jié)果就是獨立的，而與COUNTIF(B3:G12,60) 的結(jié)果是完全不同的。內(nèi)內(nèi)存存數(shù)數(shù)組組簡簡介介：三維引用的實例有很多，主要通過OFFSET或INDIRECT來產(chǎn)生新的引用，這里就不再贅述。通常情況下，有以下兩種常用方法可以實現(xiàn)生成內(nèi)存數(shù)組：1、如果數(shù)據(jù)源是單元格引用，通常使用N/T+OFFSET組合函數(shù)實現(xiàn)。2、如果數(shù)據(jù)源是常量數(shù)組（或內(nèi)存數(shù)組），通常使用LOOKUP函數(shù)來實現(xiàn)。在生成內(nèi)存數(shù)組的運用中，以下幾種情況往往需要生成內(nèi)存數(shù)組：1、需要產(chǎn)生

16、一組數(shù)據(jù)供其他公式在內(nèi)存中運用。2、根據(jù)源數(shù)據(jù)區(qū)域，生成動態(tài)圖表。內(nèi)內(nèi)存存數(shù)數(shù)組組應(yīng)應(yīng)用用：生生成成不不重重復(fù)復(fù)清清單單。員員工工編編號號 員員工工姓姓名名性性別別年年齡齡T+OFFSETT+OFFSET內(nèi)內(nèi)存存數(shù)數(shù)組組由于公式字符超長，特定義名稱如下：A0711張三男22張三6ROWS=ROW(INDIRECT(1:&ROWS(OFFSET高級應(yīng)用!$C$27:$C$33)A0795李四男32李四Data=T(OFFSET($C$26,SMALL(IF(MATCH($C$27:$C$33,$C$27:$C$33,0)=ROWS,ROWS),ROW(INDIRECT(1:&SU

17、M(1/COUNTIF($C$27:$C$33,$C$27:$C$33),)A0733王五男28王五B1234趙四女22趙四注解：A2345張大男24張大1、此類解法在論壇上經(jīng)常使用，這里只是為了說明OFFSET生成內(nèi)存數(shù)組的方法。A0795李四男32田七2、其實公式的核心段應(yīng)該是SMALL函數(shù)段，根據(jù)MATCH判斷非重復(fù)數(shù)據(jù)，得出各個B1123田七女21#N/A 滿足條件(不重復(fù)記錄)的相對于C26的位置號，并將位置號逐個取出。3、取出結(jié)果交OFFSET函數(shù)，得：=T(OFFSET($C$26,1;2;3;4;5;7,)4、以上則是通過OFFSET的三維引用來取值，最后通過T函數(shù)將三維引用轉(zhuǎn)

18、換為實際 員工姓名的內(nèi)存數(shù)組。小小結(jié)結(jié)：1、學(xué)習(xí)OFFSET函數(shù)的關(guān)鍵在于：用戶必須清楚地知道單元格偏移的基點(reference)，只有掌握好如何控制這個基本點來產(chǎn)生引用，才是學(xué)好該函數(shù)的根本。2、在常規(guī)的用法中，OFFSET函數(shù)通常根據(jù)用戶的要求返回一個連續(xù)區(qū)域，用戶再利用其他函數(shù)進行后續(xù)處理，如求和、求平均或排名等操作。3、如果用戶希望使用OFFSET來產(chǎn)生多個區(qū)域，那么這就涉及到多維引用的范疇，在多維引用的應(yīng)用中，用戶必須清楚多維引用實際上是非平面的單一引用，而是擴展到空間上， 在OFFSET產(chǎn)生的多維引用中，實際上各個引用相對獨立，外層函數(shù)只能分別對各個引用進行單獨處理，這也是理解多

19、維引用的關(guān)鍵。4、在上面的簡單介紹中，OFFSET特別在通過單元格引用來生成內(nèi)存數(shù)組這種應(yīng)用中有比較常用，但這種應(yīng)用往往與三維引用相關(guān)聯(lián)的，用戶盡可能地理解、掌握了多維引用后， 才能對生成內(nèi)存數(shù)組的理解有幫助。在論壇上已經(jīng)有很多實例都涉及到了OFFSET函數(shù)，這里也只是簡單地介紹了OFFSET的一些用法，希望對大家有幫助。By GdliyyE三維引用在論壇中經(jīng)常被使用，而且在許多精華帖中都有介紹，在這里，只進行舉例簡單說明。=SUMPRODUCT(N(COUNTIF(OFFSET(B2:F2,ROW(B3:F12)-ROW($B$2),0),=2)可以使用“工具-公式審核”進行查看運算過程。 B3:F3、B4:F4、B5:F5、B6:F6、B7:F7、B8:F8、B9:F9、B10:F10、B11:F11、B12:F124、而通過OFFSET函數(shù)產(chǎn)生的這10個區(qū)域，各自處在不同的平面中，相對獨立，這