算特定日期或其後第一個交易日的值
|
資料日期(A)
|
幣別(B)
|
匯率(C)
|
現金(D)
|
|
2021/2/3
|
USD
|
本行買入
|
27.95
|
|
2021/2/2
|
USD
|
本行買入
|
27.96
|
|
2021/2/1
|
USD
|
本行買入
|
27.96
|
|
2021/1/29
|
USD
|
本行買入
|
27.99
|
|
2021/1/28
|
USD
|
本行買入
|
28
|
|
2021/1/5
|
USD
|
本行買入
|
27.98
|
|
2021/1/4
|
USD
|
本行買入
|
28
|
|
2020/12/31
|
USD
|
本行買入
|
28.08
|
|
|
|
|
|
|
|
|
|
|
|
|
2021/1/1
|
28
|
|
|
|
|
27.96
|
以2021年1月新台幣對美元的匯率為例，假設我們要取該月第一個和最後一個交易日匯率，如果當天沒匯率，則取之後第一個交易日匯率
因為1/1和1/31放假沒有匯率，則取1/4和2/1匯率
B12：2021/1/1
C12：=INDEX(D:D,MATCH(B12,A:A,-1))
=> C12=
28
（1/4匯率）
C13：=INDEX(D:D,MATCH(EOMONTH(B12, 0),A:A,-1))
=> C13=
27.96
（2/1匯率）
IF多條件 IF + AND
7/29顯示USD現金匯率=TRUE=D2
=IF(AND(A2=DATE(2022,7,29),B2="USD"),D2,E2)
29.54
7/28顯示USD即期匯率=FALSE=E3
=IF(AND(A3<>DATE(2022,7,29),B3="TWD"),D3,E3)
29.88
B3="TWD" - 必須雙引號 => FALSE
A3<>DATE(2022,7,29) - 判斷日期必須用DATE()，<> 不等於 => TRUE
IF嵌套 => IF else-if else
=IF(AND(A3<>DATE(2022,7,29),B3="TWD"),D3,IF(E3<30,E3,F3))
AND(A3<>DATE(2022,7,29),B3="TWD") - FALSE
IF(E3<30,E3,F3) - TRUE = E3
in_array
使用 REGEXMATCH
=IF(REGEXMATCH(I2, "^(firstrade|支付寶|美金現金)$"),G2/L2,L2/G2)
參照其他工作表的資料
可以在sublime裡面用列模式編輯好再批量貼上
='test'!B2
='test'!B3
='test'!B4
='test'!B5
前往指定儲存格
說明 => 搜尋選單 (Alt+/) => 前往指定範圍 => 輸入儲存格編號，如M6
計算平均睡覺時間
B2： 0:46
C2： 5:30
D2： =mod(C2-B2,1)
D14（不計算0）： =AVERAGEIF(D2:D11,"<>0")
D14算出來是0.3079166667， 一天24小時，24*0.3079166667 = 7.39 => 不到7個半小時。只要修改格式就行。格式=>數值=>持續時間 = 7:23:24 = D12
SUMIFS 多條件AND + OR
計算台灣的銀行（台灣銀行、第一銀行、土地銀行）USD總金額
=ArrayFormula(sumifs(B2:B6,A2:A6,"USD",regexmatch(C2:C6,"^(台灣銀行|第一銀行|土地銀行)$"),TRUE))
因為只有台灣和大陸的銀行，所以也可以是計算非大陸銀行USD總金額
=ArrayFormula(sumifs(B2:B6,A2:A6,"USD",regexmatch(C2:C6,"^(工商銀行|中國銀行)$"),FALSE))
INDIRECT - 下拉時固定值
預期結果
F3設定公式-2026年（
A38）以前上班收入100,000（
F38），2027-2031年（
H38）半退休收入50,000（
G38），2032後退休收入0。2024年（A3）收入為100,000
=IFS(A3>INDIRECT("H38"),0,A3>INDIRECT("A38"),INDIRECT("G38"),A3>2020,INDIRECT("F38"))
往下拉
F4自動套入公式
=IFS(A4>INDIRECT("H38"),0,A4>INDIRECT("A38"),INDIRECT("G38"),A4>2020,INDIRECT("F38"))
錯誤結果（如果不使用INDIRECT）
G3設定公式
=IFS(A3>H38,0,A3>A38,G38,A3>2020,F38)
往下拉
G4自動套入公式（錯誤結果）
=IFS(A4>H39,0,A4>A39,G39,A4>2020,F39)
