• Home
  • Market
  • Finance
  • Running
  • Reading
  • Research
  • Travel
  • Lifestyle
  • About Me
No Result
View All Result
  • Home
  • Market
  • Finance
  • Running
  • Reading
  • Research
  • Travel
  • Lifestyle
  • About Me
No Result
View All Result
Charliechacha ,Everything about Dividend 關於股息率投資的方法

一個強大的Excel函數OFFSET,動態選取資料範圍

2022 年 7 月 15 日
Home Reading

內容目錄

  • Excel Offset函數的基本功能
  • Excel Offset函數的構成
  • Offset的簡單例子
  • Offset的正式例子

Excel Offset函數的基本功能

Offset是一個不容易解釋的函數,初學者可能不太需要知道,不過隨著你需要處理的資料越來越多的時候,也許你需要知道有這麼一個函數-Offset,從功能來看,它就是一個選取範圍的函數。

Excel Offset函數的構成

Offset函數的構成是=(參考基點, ROWs, COLs, HEIGHT, WIDTH)

Offset函數

我們要先定一個參考基點,然後可以輸入參數向下及向右偏移,然後再設定這個範圍的大小,不輸入數值的話,預設值是1。

Offset的簡單例子

Offset函數

為什麼這個函數難以解釋,因為單單選取一個範圍沒有發生作用,你需要配合其它函數才能發揮它的作用,我們看H4、I4、J4的儲存格,我們用=SUM(OFFSET….)為例子。

H4例子,=SUM(OFFSET(B3,2,2)),我們要OFFSET來SUM數值,B3為基點,往下偏移2格,往右偏移2格,沒有輸入HEIGHT及WIDTH,預設範圍1X1,結果是1。

I5例子,=SUM(OFFSET(B3,2,2,3,3)),我們要OFFSET來SUM數值,B3為基點,往下偏移2格,往右偏移2格,HEIGHT=3、WIDTH=3,所以結果是18。

J5例子,=SUM(OFFSET(B3,2,2,-2,3)),我們要OFFSET來SUM數值,B3為基點,往下偏移2格,往右偏移2格,HEIGHT=-2、WIDTH=3,因為是HEIGHT是負數,所以向上一行,結果是33。

有人又會覺得直接SUM不就好了嗎?為什麼要多此一舉做偏移的動作呢?我們下面再看一個例子,你就會知道它的作用有多厲害了。

Offset的正式例子

Offset函數

我們有日期及銷售額,可以用來畫一個7日的棒形圖,滑鼠選一下範圍,再產生圖形,很簡單是吧。可是如果我們要建立一個最近7日的棒形圖呢?當我們輸入了6月1日的數據,最近7日的數據就會改變,棒形圖的範圍也就會隨著數據的增加而改變,當然你也可以選擇每次添加數據重新做一次圖表。

到這裡,Offset終於有機會登場了,我們可以透過Offset做一個動態的最近7日棒形圖。

在Excel的頂端點選,公式–>名稱管理員–>新增,兩個項目,日期及銷售額。

Offset函數
Offset函數

日期,參照到=Offset(工作表1!$L$1,COUNT(工作表1!$L:$L),0,-7)

意思是設定基點為L1,ROW的偏移為COUNT L欄的資料數,基點會偏移到最後一筆資料,COLS的偏移為0,HEIGHT為-7,選取範圍為向上數的7X1範圍。

再新增銷售頧,參照到=Offset(工作表1!$M$1,COUNT(工作表1!$L:$L),0,-7)

然後用日期及銷售額隨便製作一個棒形圖。

Offset函數

點擊棒形圖,選取資料,編輯數列,在數列值輸入:

=活頁簿1!銷售額,確定。

再更改座標軸標籤範圍=活頁簿1!日期,確定。

這樣,動態的最近7日的銷售額棒形圖完成,是不是很厲害呢。

每次加減資料,它都會自己改變,不用每次都動手重新製作新圖表。

雖然用VBA都可以一鍵做到相同效果,不過函數處理資料的速度會快很多,如果你能夠用函數處理的話就不要寫VBA,

這是我在處理十年交易數據裡面得出的心得,<以常態分配曲線,探討股息率均值迴歸的可能>。

Tags: Excel
Share445Tweet278Share111
Charlie chacha

Charlie chacha

Related Posts

自學Excel VBA系列-如何用VBA控制OutLook?
Lifestyle

Excel強大的組合函數練習,資料庫關鍵字搜索,Address + Match + Index + Find 組合拳

2023 年 3 月 18 日

想要把Excel的功能變得強大,函數組合使用必不可少,例如之前介紹的Offset函數,《一個強大的Excel函數OFFSET,動態選取資料範圍》,非常好用。本文介紹另外一個經常會使用到的組合拳:Add...

OpenAI 技术 GPT-3 语言生成模型 AI 研究 机器学习应用 人工智能技术 自然语言处理 数据分析 机器人技术 智能语音识别
Market

探索人工智能,了解OpenAI的未來與潛力–ChatGPT使用心得 ChatGPT可以幫到你做什麼?人工智能 語言生成 數據分析

2023 年 3 月 18 日

OpenAI ChatGPT ChatGPT的名號,大家都應該非常熟悉。地區破解什麼就不討論,兩個條件,地區VPN,以及SMS認證服務,還不能是虛擬號碼,我花了幾天時間也找不到方便安全的省錢方法,建議...

vba 自學
Programming

自學VBA系列,網頁抓取的按鈕選擇及下拉式選單問題

2022 年 12 月 29 日

網頁抓取的過程中經常會出現各種各樣的問題,今天就按鈕選擇及下拉式選單碰到的問題略作解釋。 按鈕選擇問題: Sub NewUpdatedata() 'Dim ur As String, b, ie As...

自學Excel VBA系列-如何用VBA控制OutLook?
Lifestyle

自學Excel VBA系列-如何用VBA控制OutLook?

2022 年 7 月 18 日

前言: 上次講解過如何用VBA控制Word,希望大家都能實際運用,節省時間。 我相信很多朋友對於如何提升生產力,優化時間管理的效益有很大需求,那麼我們今天再來講解一下VBA對於Outlook的操作。 ...

發佈留言 取消回覆

發佈留言必須填寫的電子郵件地址不會公開。 必填欄位標示為 *

About Me

Charliechacha ,Everything about Dividend 關於股息率投資的方法

Charlie Chacha

Blogger and Knowledge Seeker

Welcome to my blog! My name is Charlie chacha, I share practical knowledge, whick is different from theoretical. Focus on your life, how you spend your time . Learn hard , live well and make money while you sleep.

Categories

  • Finance (31)
  • Lifestyle (7)
  • Market (16)
  • Programming (12)
  • Reading (39)
  • Research (21)
  • Running (39)
  • Travel (2)

Popular

  • JAVA學習筆記 ArrayList

    一個強大的Excel函數OFFSET,動態選取資料範圍

    1112 shares
    Share 445 Tweet 278
  • Excel VBA的自學心得分享

    1026 shares
    Share 410 Tweet 257
  • 自學Excel VBA系列-如何用VBA控制WORD?

    611 shares
    Share 244 Tweet 153
  • 自學Excel VBA系列-如何用VBA控制OutLook?

    548 shares
    Share 219 Tweet 137
  • 日元貶值,日本央行為什麼不加息?

    458 shares
    Share 183 Tweet 115

Instagram

    Go to the Customizer > JNews : Social, Like & View > Instagram Feed Setting, to connect your Instagram account.
No Result
View All Result
  • Home
  • Market
  • Finance
  • Running
  • Reading
  • Research
  • Travel
  • Lifestyle
  • About Me