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函數有了數據就可以很輕鬆的把區域選擇出來了。