應用技巧十三:OFFSET函數和INDEX函數的應用

2020-12-11 陶澤昱

OFFSET函數和INDEX函數在日常工作中相對來說應用較多的函數，下面將介紹兩種函數的用法。

一、認識OFFSET函數

OFFSET函數是Excel引用類函數中非常實用的函數之一，無論在數據動態引用還是在數據位置變換，甚至在函數高級應用的三維引用中，該函數的使用頻率也非常高。由於此函數的功能強大，使用靈活，在很多綜合應用示例中都能見到它的身影。

OFFSET函數的語法如下：

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

參數reference為函數引用基點，它必須是單元格引用，而不能是常量數組。

注意：OFFSET函數的首參數reference為函數引用基點，它必須是單元格引用（可為公式生成的單一單元格區域引用），而不能是常量數組。

函數返回的結果是以制定的引用為參數，最終通過給定偏移量得到一個新的單元格引用或單元格區域引用。

在使用OFFSET函數時，如果省略參數height或參數width，則系統視為其高度或寬度與引用基點相同。如果引用基點所指定的基點是一個區域引用，當用戶指定了參數height或參數width，則函數將以引用區域的左上角單元格為基點來進行區域偏移運算。

為了便於更直觀地理解，使用下面兩個實例進行說明，如圖1所示。

例1：以下兩個公式的作用完全相同，都返回區域$D$4:$E$8:

=OFFSET(B2,2,2,5,2) (公式1)

=OFFSET(G11,-3,-2,-5,-2) (公式2)

由於公式2的行偏移、列偏移為負數，表明從基點向上、向左產生偏移，而引用行高height為-5，列寬width為-2，則表明從偏移點向上擴展5行，並向左擴展2列來取得結果，因此其返回的引用區域等價於公式1.

例2：以下3個公式的作用也完全相同，都返回區域$D$4:$E$8:

=OFFSET(B2,2,2,5,2) (公式1)

=OFFSET(B2:C6,2,2) (公式2)

=OFFSET(F10:G11,-2,-2,-5) (公式3)

在公式2中的引用基點為B2:C6單元格區域（5行2列），而函數中只制定的行、列偏移量，在這種情況下，公式將保持源區域大小不變按行列偏移量來生成新的引用區域，返回的結果等價於公式1。

另外，在公式3中引用基點為F10:G11區域（2行2列），但在行高和列寬中僅制定了height為5行，因此在忽略列寬的情況下，引用源區域將忽略行高保留列寬（變為1行2列），再按指定行高 參數向上擴展5行生成新的引用區域，返回的結果仍然等價於公式1。

提示：通常來說，OFFSET函數的行數height或列數width都使用正數，表明從引用基點向下或向右偏移來生成新的引用。這兩個參數也支持使用負數做為參數值，表明偏移方向相反，即從基點向上或向左偏移來生產引用。

二、理解INDEX函數

INDEX函數做為Excel常用的引用函數之一，主要根據用戶在一個範圍（區域引用或數組）中指定的行號和列號來返回一個值，如果源範圍時區域則返回單元格引用，如果源範圍是數組，則返回數組中的某個值。

例如下面的公式返回A1:C10區域中第5行第2列的單元格引用B5:

=INDEX(A1:C10,5,2)

1. 根據指定條件提取數據

例3：利用INDEX函數隔行提取數據

如圖2所示，左側為一張數據表，其右側表格的F3:G7區域利用INDEX函數來實現從左表中隔行提取數據。

由於原表中的品名都在奇數行，F3單元格公式如下：

=INDEX($C$3:$C$12,ROW(A1)*2-1)

G3單元格公式如下：

=INDEX($C$3:$C$12,ROW(A1)*2)

公式主要利用ROW函數來生成公差為2的自然數序列，再利用INDEX函數取出數據。另外，還可以藉助行列函數組合應用將其合併為一個公式，如：

=INDEX($C$3:$C$12,(ROW(A1)*2-1)*2+COLUMN(A1))

這個公式對於需要轉換多行多列的數據（如每隔4行取出一組數據），就顯得非常有用，在轉換區域中只需要一個公式（將公式中*2修改為*4即可），無需每列單獨編寫公式。

2. 在二維數據表中進行查找

在Excel中，MATCH函數可以用於查找數據的位置，而INDEX函數則可以根據查找到的位置返回為實際的單元格引用。因此，在實際工作中常常可以使用二者的組合來完成條件查詢計算。

例4：多條件組合查詢資料

如圖3所示，展示了某公司的產品價格表，要求根據B2單元格和B3單元格中的產品型號和規格條件，從價格表中查詢產品價格。

B4單元格的查詢公式如下：

=INDEX($E$2:$G$10,MATCH($B$2,$D$2:$D$10,0),MATCH($B$3,$E$1:$G$1,0))

在公式中則直接使用兩個MATCH函數分別針對產品型號和產品規格進行獨立查找，最終將行號和列號返回給INDEX函數來返回查詢結果。

另外，B4單元格也可以使用vlookup函數來構造公式達到同樣的目的，如：

=VLOOKUP(B2,$D$2:$G$10,MATCH(B3,$D$1:$G$1,),)

相關焦點

  • 數列在offset和indirect函數中的應用
    要用活函數，用好公式，學會數列的構造是必須要邁過的一道坎！之前也多次解讀過構造數列的一些套路，還沒學過的夥伴可以先去看一下：也有些已經了解數列構造方法的同學們提出疑問，學會這些到底有什麼用？今天就通過幾個例子來體驗一下數列的應用。
  • OFFSET函數與MATCH函數、COUNTIF函數相組合的實際應用
    今天繼續深入地講解OFFSET函數的應用，這個函數的參數較多，在實際應用中主要的是此函數可以返回一個區域，由於這個區域是可以靈活地定義的，所以這個函數的應用有著不可思議的用處。今天給大家講解的就是一個非常實際的例子。某公司的產品系列及收益如下面的報表，要分析出每系列中各種產品的總收益，每系列的最高收益，及平均收益等數值。
  • Excel引用函數offset教程
    （ID：ExcelLiRui）微信個人號 | （ID：ExcelLiRui520）關鍵字：offsetExcel引用函數offset教程Excel中的引用函數offset是必會函數之一，無論是函數建模還是製作動態圖表都離不開offset的強大功能。
  • 函數index與函數column、match組合用法在實際操作中的應用
    excel在上一篇文章中，我們詳細介紹了函數left、函數right、函數if和函數mod組合用法在實際操作中的應用，具體解決的問題是提取身份證號碼中的性別位號碼，並根據性別位號碼判斷性別今天我們來介紹函數index和函數column、函數index和match這兩組組合在實際操作中的應用。（對於函數left、函數right、函數if和函數mod的組合用法感興趣的朋友可以看完該篇文章之後參考文章
  • Excel函數應用篇:offset函數公式的使用實例
    offset在excel表格中是一個非常厲害的函數，它在下拉菜單、動態圖表、動態引用操作中有不可替代的作用，今天我們就來學習一下offset函數公式的使用
  • Excel函數公式教程:INDEX和MATCH函數應用
    如果同時使用參數行號和列號，函數INDEX返回 行號和列號交叉處的單元格中的值。　　INDEX函數語法：INDEX(單元格區域，行號，列號)　　可以通過下圖來認識INDEX函數的用法：　　第二部分：INDEX和MATCH函數應用案例介紹　　下圖工作表所示的是一個產品的型號和規格的價格明細表。通過這個表的數據，進行一些對應的查詢操作。
  • Excel函數應用篇:INDEX函數
    Excel中INDEX函數是很常用的查找引用函數。通常情況下，嵌套其餘函數一起使用，比如index+match。
  • Excel教程:INDEX和MATCH函數嵌套應用分享
    分享題目：INDEX和MATCH函數嵌套應用　　主講老師：簡單老師
  • Excel應用技巧:組合函數index+match
    上一篇，我們講到了index函數的語法和基本用法，今天我們再來說說match函數以及組合函數index+match的用法。
  • if函數的嵌套使用技巧,sumif函數和averageif函數的應用
    sumif函數和averageif函數的應用今天我們要分享的是if函數嵌套的使用技巧，我們以兩個比較經典的函數與if函數進行嵌套使用。sumif函數和averageif函數的應用我們今天講解的是sumif函數和averageif函數的具體應用，我們以實例結合視頻的形式將
  • 最全的OFFSET函數應用集合,收藏這一篇就夠了
    我之前有一篇帖子offset函數的鬥轉星移神功簡單介紹了這個函數的語法結構和基本用法，大家可以參考。在實際工作中，OFFSET函數的應用主要在以下幾個方面：基本運算和多個函數配合的拓展應用製作動態圖表我們將按以上由簡到繁的思路來想大家介紹一下這個函數強大的功能。
  • EXCEL教程:INDEX和MATCH函數實戰應用分享
    分享題目：INDEX和MATCH函數嵌套應用　　主講老師：簡單老師
  • Excel函數應用篇:match函數五種用法
    match函數，在EXCEL中廣泛運用於查找引用，但它自已本身的功能是查找，並不具備引用，所以常規操作中，它是需要和vlookup
  • Excel中的最佳函數組合:INDEX-MATCH應用實例解讀
    公眾號「Excel基礎學習園地」是一個免費發布Excel基礎知識、函數應用、操作技巧、學習方法等資訊的公眾號，請點擊上方
  • offset函數的使用方法
    在Excel中，offset函數是一個引用函數，表示引用某一個單元格或者區域。其也是我們日常做一些組合函數中的一種，需要熟練掌握。
  • 實例解析Perl substr函數應用
    實例解析Perl substr函數應用 本文向大家簡單介紹一下Perl substr函數應用，在編寫perl程序的時候，我們有時需要截取一個字符串中的部分內容，Perl substr函數就可以實現此功能。
  • EXCEL中offset函數用法詳解
    offset函數和其他函數一樣，都有語法和解釋，但是offset和其他函數不一樣的地方在於：不通過實操即使你把語法和解釋都背會了都沒用，因為只有通過實操你才能發現offset的應用場景，而善於發現函數的應用場景（解決問題的思路）才是最重要的！見下圖：
  • Char函數和Code函數的應用技巧實例
    我之前的一篇文章中講過，如何利用CHAR函數實現字符「世界盃」的展現，今天仍是這個話題，還將引入CODE函數，並著重講解這兩個函數的利用實例及技巧。以後還會有篇文章講一下它們的綜合利用。這篇文章將會給不熟悉正則運算的朋友解決一大類的問題。
  • index函數
    index翻譯成中文是"索引"的意思，index函數也正是返回指定區域、指定位置的數值。下面我們看看index函數的應用。index函數和match函數配合，常用來查詢。比如下面的例子，查詢張三的成績。我們可以使用vlookup來查詢，現在展示index和match的配合。
  • Excel函數應用篇:CHOOSE函數
    CHOOSE 函數語法具有以下參數：index_num    必需。用於指定所選定的數值參數。index_num 必須是介於 1 到 254 之間的數字，或是包含 1 到 254 之間的數字的公式或單元格引用。如果 index_num 為 1，則 CHOOSE 返回 value1；如果為 2，則 CHOOSE 返回 value2，以此類推。如果 index_num 小於 1 或大於列表中最後一個值的索引號，則 CHOOSE 返回 #VALUE! 錯誤值。