算特定日期或其後第一個交易日的值
以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
https://blog.coupler.io/if-and-or-ifs-formulas/ IF+AND Google Sheets formula example
https://spreadsheetpoint.com/not-equal-google-sheets-symbol/ How to Use the Not Equal Google Sheets Symbol [Easy]
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
https://stackoverflow.com/a/42938567 How to do an If Else in google sheets?
=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
前往指定儲存格
https://spreadsheetpoint.com/google-sheets-jump-to-cell-range/ How to Jump to Specific Cell or Range in Google Sheets?
說明 => 搜尋選單 (Alt+/) => 前往指定範圍 => 輸入儲存格編號，如M6
計算平均睡覺時間
https://superuser.com/a/1060637 How to calculate average bed and wake time?
https://www.got-it.ai/solutions/excel-chat/excel-tutorial/average/ignore-zero-and-blank-cells How to Calculate an Average Without Zeros or Blank Cells
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
https://infoinspired.com/google-docs/spreadsheet/regexmatch-in-sumifs/ REGEXMATCH in SUMIFS and Multiple Criteria Columns in Google Sheets
計算台灣的銀行（台灣銀行、第一銀行、土地銀行）USD總金額
=ArrayFormula(sumifs(B2:B6,A2:A6,"USD",regexmatch(C2:C6,"^(台灣銀行|第一銀行|土地銀行)$"),TRUE))
=ArrayFormula(sumifs(B2:B6,A2:A6,"USD",regexmatch(C2:C6,"^(工商銀行|中國銀行)$"),FALSE))
INDIRECT - 下拉時固定值
https://www.exceldemy.com/insert-column-excel-without-affecting-formulas/ How to Insert Column without Affecting Formulas in Excel
預期結果
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)
沒有留言:
張貼留言