OFFSET函數與MATCH函數、COUNTIF函數相組合的實際應用

2020-12-15 VBA語言專家

今天繼續深入地講解OFFSET函數的應用，這個函數的參數較多，在實際應用中主要的是此函數可以返回一個區域，由於這個區域是可以靈活地定義的，所以這個函數的應用有著不可思議的用處。今天給大家講解的就是一個非常實際的例子。

某公司的產品系列及收益如下面的報表，要分析出每系列中各種產品的總收益，每系列的最高收益，及平均收益等數值。

大家看看上面的截圖，對於一個公司來說，這樣類似的報表再熟悉不過了，對於中層的領導來說，做出這類報表也是非常普遍的工作之一。如上面的黃色區域，如何能從源數據快速地產生準確的數值呢？

今天我們就講這個問題，同時還會講到公式名稱的自定義方法。

首先，我們要先建立一個自定義的公式，

單擊菜單「插入」》》「名稱」》》「定義」，在「名稱」框中輸入名稱「BB」，然後在下面的引用位置輸入：

=OFFSET(Sheet8!$A$1,MATCH(Sheet8!$E3, Sheet8!$A$2:$A$16,),2,COUNTIF(Sheet8!$A$2:$A$16, Sheet8!$E3))

如下面的截圖所示：

請注意這個自定義名稱的方法，這是在我的文章中的第一次出現，本來不用此方法也可以，但由於公式的反覆錄入實在是費時間，就應用這種簡單的方法了，大家要注意，這種名稱在07版本的EXCEL中，當移動和複製工作表時會有相應的提示，要注意留心處理。好了，那個話題和本文無關，只是提示。

下面我們對這個公式進行一下講解：

=OFFSET(Sheet8!$A$1,MATCH(Sheet8!$E3, Sheet8!$A$2:$A$16,),2,COUNTIF(Sheet8!$A$2:$A$16, Sheet8!$E3))

對於看過前兩篇講OFFSET函數的朋友，相信你自己能看懂了。

那麼是什麼意思呢：這個公式也是offset函數和match函數與counif函數的組合應用，其實，函數組合後，能解決的問題會成幾何級數的增長。

這個公式的第一部分：MATCH(Sheet8!$E3, Sheet8!$A$2:$A$16,)，這個值是OFFSET的第二個參數，使用MATCH來精確查找位置。代表偏移的行數就由MATCH部分的值來控制。這個公式的意思就是查找E3單元格的值在A2：A16區域中的位置。 MATCH函數也是一個查找函數。會返回中匹配值的位置,而不是匹配值本身。只查找第一次出現的，後來出現的它返回的也是第一次出現的位置。

第二部分：COUNTIF(Sheet8!$A$2:$A$16, Sheet8!$E3)，這個值是OFFSET的第四個參數，高度就由COUNTIF的值來控制。

好了，有了上面的公式講解後，我們再看黃色區域該錄入什麼公式？

如下面的截圖：

哈哈，是不是很簡單呢？公式就不用我再一一講解了，看下面的返回結果：

很準確地給出了我們需要的結果。大家跟著我一步一步的做到這裡，是不是覺得很有意思呢？學習就是這樣的，不知不覺中會積累下很多看似沒有，但到了不知是什麼時候的時候就會發揮它的用處，福不唐捐。要相信自己。

今日內容回向：

1 自定義名稱如何建立？

2 建立一個自己的報表，數據源自定，要利用到OFFSET函數，MATCH函數，COUNTIF函數等。

分享成果，隨喜正能量

相關焦點

  • Excel函數應用篇:match函數五種用法
    match函數，在EXCEL中廣泛運用於查找引用，但它自已本身的功能是查找，並不具備引用，所以常規操作中，它是需要和vlookup
  • Excel引用函數offset教程
    （ID：ExcelLiRui）微信個人號 | （ID：ExcelLiRui520）關鍵字：offsetExcel引用函數offset教程Excel中的引用函數offset是必會函數之一，無論是函數建模還是製作動態圖表都離不開offset的強大功能。
  • EXCEL中函數進階—那些複雜函數中經常用到的offset函數如何用
    四：offset函數與多個match函數進行求和。仍然是上面一組數據，那麼可不可以求任意兩個月份之間的累計銷量呢？我們考慮到既然match函數返回的值是所選單元格在區域中的位置，那麼就可以利用match函數嵌套來編制公式。
  • INDEX、MATCH、COUNTIF函數在實際工作中的具體應用講解
    今日要引入的函數較多，我們一個一個把要引入的函數講解完後，再講解我們的公式。今日要講的函數有INDEX（），MATCH（），COUNTIF（）。一 INDEX（）函數，返回表或區域中的值或對值的引用。函數INDEX()有兩種形式：數組形式和引用形式。
  • Excel中,OFFSET函數的使用方法
    ，因為引用區域不在工作表中具體應用：OFFSET函數經過偏移後返回的是一個區域，所以我們可以對這個區域求和、平均值、計數、最大最小值等。比如對下面的返回的區域求和，那麼在offset函數前加上sum函數，然後就可以計算出結果是36。
  • 函數index與函數column、match組合用法在實際操作中的應用
    excel在上一篇文章中，我們詳細介紹了函數left、函數right、函數if和函數mod組合用法在實際操作中的應用今天我們來介紹函數index和函數column、函數index和match這兩組組合在實際操作中的應用。（對於函數left、函數right、函數if和函數mod的組合用法感興趣的朋友可以看完該篇文章之後參考文章
  • 學會offset+match函數
    課程信息卡課程：《Excel天天訓練營》2.0圖文版章節：第2章-精通函數內容：定位查找（offset\match）根據已知條件如圖中案例表格，除了我們之前講的index+match這一組函數，用offset+match這一組函數也很容易實現。而且，offset+match還有更高級的用法（比如製作多級聯動下拉菜單），我們會在後面的課程講到。如果你沒有學會這些函數，那麼就無法應對大量數據的表格。
  • Match函數的高級玩法
    大家好，今天和大家分享「Match函數的高級玩法「，讓我們一起來提高自己的函數水平，Match函數是找位置，一般在函數嵌套中起到橋梁的作用一、根據日期返回第幾季度1、公式截圖2、公式="第"&MATCH(MONTH
  • excel怎樣運用VLOOKUP函數與INDEX-MATCH函數進行查找?
    在實際工作中，我們經常使用vlookup函數與index-match函數進行查找，這倆個函數都可以實現查找的功能，下面就基本查找，反向查找，多條件查找對比下這兩個函數的運用方式。index-match函數中先輸入match函數，match函數第一個參數表示查找依據，第二個參數表示查找依據所在的列，第三個參數表示精確匹配，此時顯示查找的數據所在單元格第幾行。外面嵌套index函數，第一個參數表示所在列，第二個參數match表示所在行，此時就實現了查找的目的。
  • offset函數公式的使用實例
    offset在excel表格中是一個非常厲害的函數，它在下拉菜單、動態圖表、動態引用操作中有不可替代的作用，今天我們就來學習一下offset函數公式的使用。　　excel中offset函數功能在excel中offse函數也同彙編語言一樣也表示地址偏移，offse函數的主要功能是返回對單元格或單元格區域中指定行數和列數的區域的引用。 返回的引用可以是單個單元格或單元格區域。 可以指定要返回的行數和列數。
  • MATCH函數使用終極帖
    > 來源：Excel
  • Excel函數應用篇:offset函數公式的使用實例
    offset在excel表格中是一個非常厲害的函數，它在下拉菜單、動態圖表、動態引用操作中有不可替代的作用，今天我們就來學習一下offset函數公式的使用
  • sumif和countif函數應用分享
    分享題目：sumif和countif函數的應用　　主講老師：簡單老師
  • 帶有if後綴的函數,都是有拳頭的函數,比如這位countif函數!
    今天分享的內容是條件計數函數：countif函數。有一部分函數是可以與if掛鈎，加上一個條件或多個條件再進行函數運算，如sumif，sumifs，countifs等。count是計算計數，if是條件前提，countif就是條件計算相應單元格的數量。運用也極其普遍，功能是十分強大，還是來看看它的"顏值"和"魅力"吧！
  • 數列在offset和indirect函數中的應用
    公眾號「Excel基礎學習園地」是一個免費發布Excel基礎知識、函數應用要用活函數，用好公式，學會數列的構造是必須要邁過的一道坎！之前也多次解讀過構造數列的一些套路，還沒學過的夥伴可以先去看一下：也有些已經了解數列構造方法的同學們提出疑問，學會這些到底有什麼用？今天就通過幾個例子來體驗一下數列的應用。
  • EXCEL中offset函數用法詳解
    offset函數和其他函數一樣，都有語法和解釋，但是offset和其他函數不一樣的地方在於：不通過實操即使你把語法和解釋都背會了都沒用，因為只有通過實操你才能發現offset的應用場景，而善於發現函數的應用場景（解決問題的思路）才是最重要的！
  • 設置動態求和效果其實並不難,使用sum+offset+match函數即可搞定
    Hello，大家好，今天跟大家分享下我們如何在excel中設置動態求和的效果，如下圖，當我們更改姓名和月份的時候會根據我們選擇的數據自動求和，比如：我們將名字設置為劉備，截止月份設置為12月，就會對劉備1月到12月的數據進行求和，這個的操作其實也並不難，我們使用offse，sum以及match
  • 可替代COUNTIF和SUMIF的函數,原來sumproduct函數這麼有用!
    很多朋友看到sumproduct函數都會想：看起來這個函數很複雜呀，我還是用簡單一點的函數吧。其實這個函數只是看起來比較複雜，但是只要你能熟練的應用它，就完全可以代替COUNTIF計數函數和sumif求和函數！
  • 詳解OFFSET函數的基本用法
    今天我們來學習下offset函數的使用方法，offset函數的用法十分廣泛，動態圖表，動態表格，以及動態區域的計算等。很多人都覺得offset函數十分難理解，但是今天我要帶你們吃透這個函數OFFSET函數及參數offset函數:offset是一個偏移函數，它以一個區域為原點進行偏移得到一個新的偏移區域第一參數：參照區域。
  • countif函數的使用方法
    Countif函數是一個統計函數，用於統計滿足某個條件的單元格的數量。　　函數語法：countif（range，criteria）　　參數1：range表示要計算其中非空單元格數目的區域　　參數2：criteria表示統計條件，條件的形式可以是數字、表達式或文本，甚至可以使用通配符。