Excel中超強的offset函數你真會用?三個操作分分鐘提升工作效率

2021-02-20 EXCEL函數與VBA實例

Excel中除了常規的查詢匹配函數,還有一個80%的人基本沒有使用過的函數,那就是Offset函數,可能很多人沒有講過這個函數,更談不上如何去使用。今天我們就來用三個案例,來學習一下看看這個函數到底有多強大。

一、Offset函數基礎講解:

案例說明:如上圖黃色區域單元格B3,從當前行起向下4行,向右3列,引用4行,引用3列偏移後,從而形成了B3:D6單元格區域。

函數公式:=OFFSET(B3,4,3,4,3)

函數講解:

1、offset函數為偏移函數,它可以通過位置的偏移獲取一段單元格範圍區域;

2、總而言之offset函數偏移方向為,下—右—上—左。第一參數為起始位置;第二參數為向下偏移多少(正數為向下,負數為向上);第三參數為向右偏移多少(正數為向右,負數為向左);第四參數為引用多少行,第五參數為引用多少列。

二、Offset函數經典案例講解

案例1:通過數據偏移計算月份區間數據

案例說明:計算1-8月份總銷售額

函數公式:

=SUM(OFFSET(C2,0,0,MATCH(8,B2:B13,0)))

函數講解:

1、通過上述數據偏移我們可以得到一個數據區域;最後用sum函數進行求和計算;

2、offset函數偏移單元格從C2也就是1月銷售額開始;第二、三參數為0說明向下和向右位置不做偏移;

3、第三參match函數代表的是查詢出對應月份所在的位置,得到結果為8。在函數中也就代表引用8行數據。也就得到了C2:C9單元格區域。

案例2:計算後半年(7-12月)月平均銷售額

函數公式:

=AVERAGE(OFFSET(C1,COUNT(C:C),0,-6))

函數講解:

1、offset函數這裡從C1單元格進行開始偏移;

2、第二參數count(C:C)代表向下偏移多少位置,計算出對應C列函數字的單元格個數;

3、第三參數0代表向右不做偏移;

4、第四參數-6代表從最後一個單元格起,往上選擇6行,得到C8:C12單元格區域。最後用average函數進行計算平均值。

案例3:offset函數製作動態下拉菜單欄

案例說明:如上圖,當我們重新添加部門進去的時候,下拉菜單選項會自動進行更新。

函數公式:

=OFFSET(G$1,0,0,COUNTA(G:G))

函數講解:

1、offset函數這裡從G1單元格進行開始偏移;用$固定行,這樣往下拖動的時候就不會變化;

2、第四參選擇的範圍為counta函數計數文本單元格的個數得到選擇多少行;

【動態演示操作】


通過上面的案例,現在你知道在實際過程中如何運用offset函數了嗎?

喜歡的話轉發或掃描下方的二維碼關注一下唄!

相關焦點

  • 對Excel批量操作,提升你的工作效率提升8倍以上(一))
    辦公室最常用Excel函數公式大全,學會了工作得心應手Excel表格如何計算特定數值範圍個數我們在日常進行進行Excel表格計算工作中,遇到的最多就是涉及到的內容較多的查找、計算時,如果一個個的去查看,工作效率會非常的慢和容易出現差錯,這時進行Excel
  • 會計常用的excel技巧,讓你工作效率 提高
    4.自動填充先設置好第一個單元格的規則,即你想實現的效果,然後用Ctrl+E,萬能快捷鍵,分分鐘實現你的指令哈哈,這個一定要會!!VLOOKUP、IF、FIND等函數,會用數據透視表進行統計分析.」 ▷ 團隊合作上,熟練掌握職場必備技能Excel,自己的工作不求人還能幫助同事,職場小紅人get√ ▷會計人員面對一堆繁瑣的數字,再也不用熬夜加班,工作效率提升了,私人時間自然就多了。
  • if函數用法從簡單到高級,excel函數大全中好用的函數之一
    IF函數的功能:對值和期待值進行邏輯比較。IF 函數最簡單的形式表示:如果(內容為 True(真),則執行某些操作,否則就執行其他操作)。因此 IF 語句可能有兩個結果。第一個比較結果為 True(真),第二個比較結果為 False(假)。IF函數的使用方法if函數是excel函數中的其中之一,它的功能很強大,下面我來為大家演示如何使用IF函數。
  • OFFSET函數的4個用法,你會嗎?
    函數是Excel中一個很強大的函數。函數語法:OFFSET(refrence,rows,cols,height,width)用通俗地說講,上面的語法可以寫成:OFFSET(單元格或者區域,向下偏移幾行,向右偏移幾列,向下偏移的高度,向右偏移的寬度)下面是該函數的基本的工作原理。
  • 【Excel·VBA】VBA中的offset屬性
    夢想沒有高低之分,只看你是否為它去做了什麼你是一個年輕人,就應該野心勃勃首先,感謝各位後臺發消息的關心
  • 告訴你應該如何玩轉Offset函數,別怕,只需要1分鐘就學會啦
    ,語法如下:OFFSET(reference, rows, cols, [height], [width]),可以明顯看到前面三個參數是必須的,後面2個是可選的參數。COLUMN(A1),取得列號碼,在外層SMALL函數取出第COLUMN(A1)與第幾個較小值,然後利用外層TEXT函數進行格式轉換,組成一個數組列。怎麼樣,是不很好理解。剛剛我們說找出不重複的值,那麼反過來,如果想提取重複值,你應該如何處理呢?
  • 親測好用|那些讓你工作效率upup的「辦公室神器」
    excel做為職場大殺器,絕對是最給當代職場人錦上添花的辦公技能。當你面對一張巨大的表格,用各種VLOOK函數與公式三下兩下就完成了所有的數據匯總與分析計算時,一定會收穫身邊同事羨慕的眼神和嫉妒的掌聲。excel雖然好用,但學習門檻有多高相信用過的都知道,單在網絡上搜索「excel函數公式」你就會獲得超過7000萬條的搜索結果。
  • 高手必備的6個Excel小技巧,瞬間提升你的工作效率
    如果你的Excel水平足夠高,功力足夠強大,那麼恭喜你,你已經具備了一個在職場中令他人望塵莫及的競爭力了。今天,就來和大家分享6個Excel小技巧,學會了,您的工作效率定能會得到大大的提升。1、處理數字亂碼在Excel表格中,當你輸入一些數字時(如:電話號碼),往往會出現亂碼現象,如下:
  • offset函數的使用方法
    在Excel中,offset函數是一個引用函數,表示引用某一個單元格或者區域。其也是我們日常做一些組合函數中的一種,需要熟練掌握。
  • 這幾個實用功能讓你的Excel工作效率提升42%!
    其中介紹了我開發的一個簡單的Excel插件,這些功能都是只針對我們平常工作中很容易遇到的問題開發的,如果能適當的應用該插件,定可以提升工作效率,節省出不少時間。三、生成目錄:前面文章中的版本是這樣操作的:
  • 關於Excel換行操作,這些技巧你要會
    在實際工作中,經常需要在單元格內換行或取消單元格內存在換行符,你一般是如何操作呢?本期技巧妹與大家分享相關解決辦法。
  • 你加班兩小時做Excel表格,我只需一分鐘!
    德勤員工群發PPT事件,給多少審計人的反應會是「不愧是四大鍛鍊出來的PPT高手!」這也直觀體現出諮詢公司對於辦公軟體使用的硬性要求,不僅是PPT,作為財務人員,工作中最常用到的就是Excel。俗話說得好,Excel用得好,下班會很早。如何利用它提高自己的效率呢?
  • 在家辦公如何提高工作效率?給你這三個方法
    在家辦公最大的挑戰,就是遠程之下,管理和溝通的效率會大幅下降。接下來,我們就來拆解一下這個問題,先說管理和溝通效率為什麼下降,然後再講怎麼辦。為什麼在家辦公會導致管理效率下降?這是因為你見不到員工,沒辦法快速地調整節奏、安排工作。員工在做什麼工作,遇到什麼問題,也沒辦法及時地向你請示,得到你的指導,協同上就會遭遇挑戰。
  • Excel九大條件邏輯判斷函數公式,上班直接套用,職場工作效率高
    (ID:ExcelLiRui)按條件對數據進行各種判斷是工作中經常遇到的問題,可是很多人都不會自動計算,還在靠各種篩選、排序甚至肉眼識別等手動操作。Excel裡面準備好了各種各樣的邏輯判斷函數提供服務,即使遇到複雜的多條件判斷問題,使用函數組合也能順利解決。為了幫你最具性價比的提升Excel條件判斷技能,今天給你九大職場辦公最常用的邏輯判斷excel公式,上班時直接套用即可!
  • 我,25歲出納,被Excel打了3個耳光!
    幾乎每一個職場人的簡歷中,技能這一欄都會寫上「熟練掌握Office」,word和PPT還好說,Excel不會立刻就會在工作中暴露出來
  • Excel實用教學:你會用Ctrl+E嗎?
    重要的事情說三遍!Excel的快捷鍵有很多,但是這一對絕對是最神奇的。它是一對能夠「自我學習」的快捷鍵。不需要寫函數,不需要錄製宏。只要你先給它打個樣,手動處理完一條數據,它就可以按照你的方法完成其他剩下的事情。在處理表格的時候,有了它,效率絕對能夠提升百倍。
  • 會計工作中的常用函數和Excel小技巧匯總
    於是在工作之餘整理了會計工作中的一些常用函數和excel小技巧,希望能夠提供一些幫助(註:提供給EXCEL初學者)。這裡的「4+1」功能包括四個核心函數,vlookup、submit、if和sum以及一個核心功能數據透視表,這些函數學起來很簡單,基本上只要看一遍教程就會操作使用,但最終能夠提高效率的關鍵是熟練應用
  • Excel中那些你必學的Alt快捷鍵
    先告訴大家一個並不能稱作Excel專屬的快捷鍵,但是每個人都需要經常用的,那就是Alt Tab, 用於快速切換桌面窗口,比如你正在瀏覽網頁,你的上司或者老闆過來了,趕緊Alt Tab 切換到工作界面,是不是很有用呢!你需要做的是首先左手大拇指按著Alt鍵不放,無名指按Tab鍵任意選擇你要切換到的新界面。
  • excel下拉菜單製作的最高境界:聯想式輸入
    利用數據 - 有效性製作的excel下拉列表,如果選項過多,會給選擇輸入帶來不少的麻煩。
  • excel下拉菜單的最高級用法:聯想式輸入
    利用數據 - 有效性製作的excel下拉列表,如果選項過多,會給選擇輸入帶來不少的麻煩。