[KDJ分析一] : Google Sheets 2條方程計算出任意時間範圍之KD 值

本篇教學主要是設計出如何用最簡短的一條方程計算出任意時間範圍之KD 值及其背後之原理,

K值公式為: 

[Close(today) - Min (low 9 day)] / [Max (high 9 day) - Min (low 9 day)] *100/ 3 + K(Yesterday) 2/3

F2方程為:  (E2-min(D2:D10))/(max(C2:C10)-min(D2:D10))*100/3+F3*2/3

注意: 此方程需要手動往下拉, 如要實現全自動溢位, 請繼續看以下教學.

由於Max, Min 不能用於Arrayformula, 所以這情況要用更強大的Query.

步驟一: K值計算

1. 在H1輸入方程: Query(C2:C,"Select *")

2.在I1輸入方程: Query(C2:C,"Select * offset 1"), 在J1輸入方程: Query(C2:C,"Select * offset 2"),重復8次

3. 這樣單一橫行便有9天數據:

4. 但這樣每一直欄的長度都不同, 所以要加入Limit, 而H1方程為: 

H1 = QUERY(C2:C,"Select * limit "&Count(C2:C)-8&" offset 0")

5. 9天陣列為:

 H1 = {QUERY(C2:C,"Select * limit "&Count(C2:C)-8&" offset 0"),QUERY(C2:C,"Select * limit "&Count(C2:C)-8&" offset 1"),QUERY(C2:C,"Select * limit "&Count(C2:C)-8&" offset 2"),QUERY(C2:C,"Select * limit "&Count(C2:C)-8&" offset 3"),QUERY(C2:C,"Select * limit "&Count(C2:C)-8&" offset 4"),QUERY(C2:C,"Select * limit "&Count(C2:C)-8&" offset 5"),QUERY(C2:C,"Select * limit "&Count(C2:C)-8&" offset 6"),QUERY(C2:C,"Select * limit "&Count(C2:C)-8&" offset 7"),QUERY(C2:C,"Select * limit "&Count(C2:C)-8&" offset 8")}

6. 此時只要利用transpose 便可得以下陣列:

 

7. 只要利用 Query 的 Max(Col1),Max(Col2),Max(Col3).... 就可找是每天對應的Max (high 9 day), 問題是要重複多少個Col...

8.  要找出多少個Col 便要找出High 有多少列數據, 在H10 輸入方程:

H10=ArrayFormula(if(len(C10:C),ROW(C10:C)-Row(C10)+1,""))

9. 然後組合成Query 的查詢語法, H10方程改成:

H10=ArrayFormula(if(len(C10:C),"Max(Col"&ROW(C10:C)-Row(C10)+1&"),",""))

10. 然後用Join 變為字串, H10方程改成:

 H10=Join(,ArrayFormula(if(len(C10:C),"Max(Col"&ROW(C10:C)-Row(C10)+1&"),","")))

11. 由於字串最後會多出一個逗號, 所以要除去, H10方程改成:

H10=REGEXREPLACE(join("",ArrayFormula(if(len(C10:C),"Max(Col"&ROW(C10:C)-ROW(C10)+1&"),",""))), ".\z","")

其中:  REGEXREPLACE(join("",ArrayFormula(if(len(C10:C),"Max(Col"&ROW(C10:C)-ROW(C10)+1&"),",""))), ".\z","") 為Query 查詢字串

 

12.把兩者組合, 在G2輸入方程即可得到每天對應的Max (high 9 day) : 

G2=query(transpose(query(transpose(Query({ARRAY_CONSTRAIN(QUERY(C2:C,"Select *"),Count(C2:C)-8,1),ARRAY_CONSTRAIN( QUERY(C2:C,"Select * offset 1"),Count(C2:C)-8,1),ARRAY_CONSTRAIN( QUERY(C2:C,"Select * offset 2"),Count(C2:C)-8,1),ARRAY_CONSTRAIN( QUERY(C2:C,"Select * offset 3"),Count(C2:C)-8,1),ARRAY_CONSTRAIN( QUERY(C2:C,"Select * offset 4"),Count(C2:C)-8,1),ARRAY_CONSTRAIN( QUERY(C2:C,"Select * offset 5"),Count(C2:C)-8,1),ARRAY_CONSTRAIN( QUERY(C2:C,"Select * offset 6"),Count(C2:C)-8,1),ARRAY_CONSTRAIN( QUERY(C2:C,"Select * offset 7"),Count(C2:C)-8,1),ARRAY_CONSTRAIN( QUERY(C2:C,"Select * offset 8"),Count(C2:C)-8,1)},"Select* ")),"Select "&REGEXREPLACE(join("",ArrayFormula(if(len(C10:C),"Max(Col"&ROW(C10:C)-ROW(C10)+1&"),",""))), ".\z","")&"")),"Select Col2")

13. Min (low 9 day)亦是以同樣方法找出, 而最後K值公式為: 

 G2=ArrayFormula(iferror((100*(E2:E-query(transpose(query(transpose(Query({ARRAY_CONSTRAIN(QUERY(D2:D,"Select *"),Count(D2:D)-8,1),ARRAY_CONSTRAIN( QUERY(D2:D,"Select * offset 1"),Count(D2:D)-8,1),ARRAY_CONSTRAIN( QUERY(D2:D,"Select * offset 2"),Count(D2:D)-8,1),ARRAY_CONSTRAIN( QUERY(D2:D,"Select * offset 3"),Count(D2:D)-8,1),ARRAY_CONSTRAIN( QUERY(D2:D,"Select * offset 4"),Count(D2:D)-8,1),ARRAY_CONSTRAIN( QUERY(D2:D,"Select * offset 5"),Count(D2:D)-8,1),ARRAY_CONSTRAIN( QUERY(D2:D,"Select * offset 6"),Count(D2:D)-8,1),ARRAY_CONSTRAIN( QUERY(D2:D,"Select * offset 7"),Count(D2:D)-8,1),ARRAY_CONSTRAIN( QUERY(D2:D,"Select * offset 8"),Count(D2:D)-8,1)},"Select* ")),"Select "&REGEXREPLACE(join("",ArrayFormula(if(len(C10:C),"Min(Col"&ROW(C10:C)-ROW(C10)+1&"),",""))), ".\z","")&"")),"Select Col2"))/(query(transpose(query(transpose(Query({ARRAY_CONSTRAIN(QUERY(C2:C,"Select *"),Count(C2:C)-8,1),ARRAY_CONSTRAIN( QUERY(C2:C,"Select * offset 1"),Count(C2:C)-8,1),ARRAY_CONSTRAIN( QUERY(C2:C,"Select * offset 2"),Count(C2:C)-8,1),ARRAY_CONSTRAIN( QUERY(C2:C,"Select * offset 3"),Count(C2:C)-8,1),ARRAY_CONSTRAIN( QUERY(C2:C,"Select * offset 4"),Count(C2:C)-8,1),ARRAY_CONSTRAIN( QUERY(C2:C,"Select * offset 5"),Count(C2:C)-8,1),ARRAY_CONSTRAIN( QUERY(C2:C,"Select * offset 6"),Count(C2:C)-8,1),ARRAY_CONSTRAIN( QUERY(C2:C,"Select * offset 7"),Count(C2:C)-8,1),ARRAY_CONSTRAIN( QUERY(C2:C,"Select * offset 8"),Count(C2:C)-8,1)},"Select* ")),"Select "&REGEXREPLACE(join("",ArrayFormula(if(len(C10:C),"Max(Col"&ROW(C10:C)-ROW(C10)+1&"),",""))), ".\z","")&"")),"Select Col2")-query(transpose(query(transpose(Query({ARRAY_CONSTRAIN(QUERY(D2:D,"Select *"),Count(D2:D)-8,1),ARRAY_CONSTRAIN( QUERY(D2:D,"Select * offset 1"),Count(D2:D)-8,1),ARRAY_CONSTRAIN( QUERY(D2:D,"Select * offset 2"),Count(D2:D)-8,1),ARRAY_CONSTRAIN( QUERY(D2:D,"Select * offset 3"),Count(D2:D)-8,1),ARRAY_CONSTRAIN( QUERY(D2:D,"Select * offset 4"),Count(D2:D)-8,1),ARRAY_CONSTRAIN( QUERY(D2:D,"Select * offset 5"),Count(D2:D)-8,1),ARRAY_CONSTRAIN( QUERY(D2:D,"Select * offset 6"),Count(D2:D)-8,1),ARRAY_CONSTRAIN( QUERY(D2:D,"Select * offset 7"),Count(D2:D)-8,1),ARRAY_CONSTRAIN( QUERY(D2:D,"Select * offset 8"),Count(D2:D)-8,1)},"Select* ")),"Select "&REGEXREPLACE(join("",ArrayFormula(if(len(C10:C),"Min(Col"&ROW(C10:C)-ROW(C10)+1&"),",""))), ".\z","")&"")),"Select Col2"))/3+F3:F*2/3),""))

 

兩種算法結果一致

步驟二: D值計算

1.  D值計算簡單很多了,在H2 輸入方程:

H2=ArrayFormula(if(G2:G="","",G2:G/3+H3:H*2/3)) 

以下為示範,可比較兩種K值計算之分別:

https://docs.google.com/spreadsheets/d/1WaK6lOPyO83eKQoJ8lL6NdWdegaAAXhZ9t_Uez6tZtc/edit#gid=674994167