你一定不知道OFFSET函數和Counta函數在一起能擦出什麼樣的火花

2020-12-11 初壹筆記

offset函數和counta函數是兩個有趣的函數,把它們單一的拿出來好像並沒有什麼出彩的地方,也不會像SUM函數那樣光芒萬丈,但如果把它們組合在一起又何止光芒萬丈!

01OFFSET函數

OFFSET函數是從指定的基準位置按行列偏移後返回指定的引用。

語法:=OFFSET(基準位置,向上,下偏移行數,向左,右偏移列數,引用區域的高度,引用區域的寬度)

說明:第一個參數基準位置可以是一個單元格或是一個區域;第二個參數可以是正數也可以是負數,正數是向下偏移,負數則向上偏移;第三個參數一樣可是正數或負數,正數向右偏移,負數向左偏移;第四、第五個可能省略,默認返回和第一個參數一致。

示例:在D1單元格輸入公式【=offset(A2,1,,)】,第一個參數A2為基準位置,第個二參數向下偏移1個單元格,第三個參數是向右偏移,這裡需要注意,我們不需要向右偏移,但參數不能省略,只需要給它一個空值,輸入一個逗號就可以了。輸入完成後按下回車鍵,這時軟體給我們返回了【A3】單元格的內容。如果把公式改為【=offset(A2,,3,2)】,這時的返回值是一個區域,但你無論按回車鍵還是Ctrl+Shift+回車都無法看到正確的返回值。我們可以選中公式按【F9】,它會以一個數組的形式向我們展示返回值,這樣我們就可以驗證公式的正確性了。

02COUNTA函數

COUNTA函數比較簡單就是返回指定區域的非空單元格,可以是數值、文本、錯誤值、邏輯值。

語法:=COUNTA(區域1,區域2……)

說明:第一個參數為必選參數,後面的為可選參數,最多可以有255個參數。

示例:返回一個以及多個區域非空單元格數量。

03把它們組合起來,做一個聰明的下拉菜單

讓我回憶一下普通下拉菜單的製作方法,首先我們選擇單元格,點擊【數據】【數據驗證】,然後選擇【序列】,再然後在數據源框選需要的單元格,再然後點確定,大功告成!

普通的下拉菜單作起來很容易,但是如果我們對源數據內容進行刪減或是添加後就需要重新設置數據源,這樣的下拉菜單顯然不夠聰明。接下來我們看OFFSET和COUNTA兩兄弟如何讓它聰明起來!前面的方法都是一樣的,只是設置數據源的時候我們不能再用框選的方法了。我們直接輸入公式【=offset($A$2,,,counta($A:$A)-1)】(這裡我們省略了第五個參數),OK大功告成!我們已經利用OFFSET和COUNTA兩兄弟成功打造出了一個動態的下拉菜單,讓我們看下它聰明在哪吧!例如我們在源數據列表中新增了【張三丰】之後,下拉菜單中立刻會自動新增,同樣刪除了也會自動清除項目。怎麼樣,有點小聰明吧

思路:我們給了counta函數一個整列的參數,它會為我們統計有多少有內容的單元格,最終給我們返回一個數值。這個數值是整列所有非空單元格的總數,包括我們表單中的標題【姓名】,但這個不是我們需要的,所以要用【-1】給它去掉,這時候的數值才是所有下拉列表項的個數。餘下的工作就很簡單了,offset函數有了數據就可以很輕鬆的把區域選擇出來了。

相關焦點

  • 用excel把一列數據對應地複製到已進行了合併單元格操作的另一列中應該如何操作(index函數和counta函數)
    操作解釋:index函數是以第一個參數中的內容為基礎,顯示出第二個參數所對應的行數的值。如:index(c:c,3)就是C列中第3行的值,即上圖中的107而公式中=index(C:C,1+counta(A$1:A3))的重點就變成了,紅色部分如何理解的問題了。而前面的1也容易,那counta(A$1:A3)是何意?
  • Excel常用函數:count函數、counta函數、countblank函數
    Hello,大家好,今天為大家分享excel常用函數:count家族函數。即count函數、counta函數、countblank函數。其實還有兩個成員:countif函數和countifs函數,這兩類函數都是按照條件格式進行計數,等介紹完if函數(即條件判斷函數)之後,我們將和sumif函數,sumifs函數放在一塊進行分享。
  • Excel中超強的offset函數你真會用?三個操作分分鐘提升工作效率
    函數公式:=OFFSET(B3,4,3,4,3)函數講解:1、offset函數為偏移函數,它可以通過位置的偏移獲取一段單元格範圍區域;2、總而言之offset函數偏移方向為,下—右—上—左。函數講解:1、通過上述數據偏移我們可以得到一個數據區域;最後用sum函數進行求和計算;2、offset函數偏移單元格從C2也就是1月銷售額開始;第二、三參數為0說明向下和向右位置不做偏移;3、第三參match函數代表的是查詢出對應月份所在的位置,得到結果為8。
  • Excel count及counta、countif計數函數
    Excel三種計數函數你都會麼,今天跟小編一起學習一下三種計數函數吧。一、count函數,僅對數值型數據計數。B列數據除開標題行成績有44個,計數結果為44。圖1 count僅對數值型數據計數二、counta函數,對所有非空單元格計數,包含任意類型數據單元格。B列含標題共45個。
  • offset函數在製作下拉菜單欄中的作用到底有多大?
    之前的課程中我們有講解過如何製作級數量的下拉菜單欄,今天我們來看一下數據有效性設置中一個非常關鍵的函數offset,來看一下下拉菜單欄中這個函數到的作用到底有多強大。下面我們來看一個例子,了解一下加了offset函數後的數據菜單欄和一般的下拉菜單欄有什麼不同,如下圖:
  • offset函數的使用實例:利用offset函數計算動態區域的和?
    offset函數的使用實例:利用offset函數計算動態區域的和?12-06 13:43 來源:成都路凡教育 原標題:offset
  • Excel函數:count()與counta()有什麼區別?
    #哈嘍,今天寫一下Excel中count()和counta()函數的區別~~counta()語法及應用COUNTA(value1,value2,...)參數值可以是任何類型,它們可以包括空字符 (""),但不包括空白單元格。如果參數是數組或單元格引用,則數組或引用中的空白單元格將被忽略。--360百科。
  • Excel函數應用篇:offset函數公式的使用實例
    offset發音彙編語言指令offsetoffset在計算機中領域最早出現在彙編語言中,它是一個offset指令,offset指令可以取得標號處的偏移地址。如指令MOVBX, OFFSET BUFFER ,在編譯時將已經計算出buffer的地址為賦值給bx(假設為4300),然後將上句替換為:mov bx,4300。
  • offset函數公式的使用實例
    offset在excel表格中是一個非常厲害的函數,它在下拉菜單、動態圖表、動態引用操作中有不可替代的作用,今天我們就來學習一下offset函數公式的使用。  彙編語言指令offsetoffset在計算機中領域最早出現在彙編語言中,它是一個offset指令,offset指令可以取得標號處的偏移地址。如指令MOV BX, OFFSET BUFFER ,在編譯時將已經計算出buffer的地址為賦值給bx(假設為4300),然後將上句替換為: mov bx,4300。
  • 數列在offset和indirect函數中的應用
    如果你能耐下性子仔細研究這些描述,會發現涉及到的術語非常多,簡單一點來說:1、  五個參數,最後兩個可以省略,前面三個不能省略;(實際上後面四個參數都可以省略,只是最後兩個參數可以連同逗號一起省略,這一點現在不用糾結)2、 
  • Offset函數的基本用法
    大家好,今天和大家分享的「Offset函數的基本用法",這個函數的作用是引用,它的參數比較多,而且參數還分正負。具體用法請往下面看一、參數講解1、Offset函數有5個參數第1參數:參照單元格第2參數:偏移行第3參數:偏移列第4參數:行高第5參數:列寬2、如果引用的是一個單元格,那麼後面兩個參數可以不寫,只寫前面的3個參數3、除第1參數之處,其餘的
  • EXCEL中offset函數用法詳解
    文章發出後已經有人詢問這個函數的用法,本來學習EXCEL函數是要循序漸進的,不應該開始就講,但是為了真的能為大家的面試加分,今天就來說說offset函數的用法。offset函數和其他函數一樣,都有語法和解釋,但是offset和其他函數不一樣的地方在於:不通過實操即使你把語法和解釋都背會了都沒用,因為只有通過實操你才能發現offset的應用場景,而善於發現函數的應用場景(解決問題的思路)才是最重要的!
  • 詳解OFFSET函數的基本用法
    今天我們來學習下offset函數的使用方法,offset函數的用法十分廣泛,動態圖表,動態表格,以及動態區域的計算等。很多人都覺得offset函數十分難理解,但是今天我要帶你們吃透這個函數OFFSET函數及參數offset函數:offset是一個偏移函數,它以一個區域為原點進行偏移得到一個新的偏移區域第一參數:參照區域。
  • 【Excel函數】Count、Counta和Countblank
    周二 · 函數    關鍵詞:count、counta、countblank1語法
  • Excel中count和counta函數的用法
    在日常工作中,經常需要對含有數值或者內容的單元格數量進行統計,會用到統計函數。這裡,要區分常見的統計函數COUNT和COUNTA的使用。
  • Excel引用函數offset教程
    (ID:ExcelLiRui)微信個人號 | (ID:ExcelLiRui520)關鍵字:offsetExcel引用函數offset教程Excel中的引用函數offset是必會函數之一,無論是函數建模還是製作動態圖表都離不開offset的強大功能。
  • EXECL中COUNT和COUNTA實例進階之一COUNT函數的精心講解
    一個簡單的操作,一份真誠的分享,現在分享下EXECL中COUNT和COUNTA實例進階之一的操作過程和技巧分享給大家,過程簡單看圖就會做,教程是自己原創的,其他分享平臺估計也能看到。第一步:COUNT和COUNTA實例進階,我們先簡單看下要求:用counta和count求出張無忌出現的次數?
  • 用這2個函數:counta+countifs
    excel單元格的計數統計,最常用的就是counta(統計非空單元格)和countifs(多條件統計)這兩個函數。如果你沒有學會這些函數,那麼就無法應對大量數據的表格。現在,我們就來用函數公式實現excel自動化辦公。
  • 非空單元格計數,只需要一個counta函數就足夠了
    我們上次已經講解了有關計數函數count的相關內容,不知道大家有沒有注意到,count函數是對excel含有數字的單元格進行計數,就說明計數隻針對含有數字的單元格計數,如果單元格裡是文本或者其他因素,count函數就不能準確計數了。
  • OFFSET函數與MATCH函數、COUNTIF函數相組合的實際應用
    今天繼續深入地講解OFFSET函數的應用,這個函數的參數較多,在實際應用中主要的是此函數可以返回一個區域,由於這個區域是可以靈活地定義的,所以這個函數的應用有著不可思議的用處。今天給大家講解的就是一個非常實際的例子。某公司的產品系列及收益如下面的報表,要分析出每系列中各種產品的總收益,每系列的最高收益,及平均收益等數值。